-
Notifications
You must be signed in to change notification settings - Fork 2
/
Dbmsday5.txt
87 lines (23 loc) · 1.86 KB
/
Dbmsday5.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
Queries on above 3 tables:-
1)SELECT SNAME || ' ' || MNAME || ' ' || SURNAME AS FULLNAME,AGE FROM SAILOR;
2)SELECT SNAME || ' ' || MNAME || ' ' || SURNAME AS NAMES_OF_SAILORS,AGE+2 FROM SAILOR;
3)SELECT * FROM SAILOR ORDER BY SNAME ASC;
4)SELECT SNAME || ' ' || MNAME || ' ' || SURNAME AS NAMES_OF_SAILORS FROM SAILOR;
5)SELECT DISTINCT(SNAME) FROM SAILOR;
6)SELECT DISTINCT(SNAME),RATING FROM SAILOR WHERE RATING BETWEEN 5 AND 10;
--FOR 7 FIRST ASC SORT RATING WISE AND THEN THOSE SORT BY AGE DESC
7)SELECT * FROM SAILOR ORDER BY RATING ASC,AGE DESC;
8)SELECT * FROM SAILOR WHERE RATING>7;
9)SELECT * FROM SAILOR WHERE SNAME='Horatio' AND AGE=35.4;
10)SELECT * FROM SAILOR WHERE SNAME='Horatio' OR AGE=35.4;
11)SELECT SNAME || ' ' || MNAME || ' ' || SURNAME AS NAMES_OF_SAILORS FROM SAILOR S NATURAL JOIN RESERVE R WHERE R.BID=104;
12)SELECT SID FROM RESERVE R NATURAL JOIN BOAT B WHERE B.COLOR='red';
13)SELECT * FROM SAILOR WHERE SNAME LIKE 'B%';
14)SELECT * FROM SAILOR WHERE SNAME LIKE '%B%' OR SNAME LIKE '%b%';
15)SELECT SNAME || ' ' || MNAME || ' ' || SURNAME AS NAMES_OF_SAILORS FROM SAILOR WHERE MNAME IS NOT NULL;
16)SELECT SNAME || ' ' || SURNAME AS NAMES_OF_SAILORS FROM SAILOR WHERE MNAME IS NULL;
17)SELECT COLOR FROM BOAT B NATURAL JOIN RESERVE R NATURAL JOIN SAILOR S WHERE S.SNAME='Lubber';
18)SELECT DISTINCT(SNAME) FROM SAILOR S INNER JOIN RESERVE R ON S.SID=R.SID;//OR CAN USE NATURAL JOIN
20)SELECT DISTINCT S.SID,SNAME,RATING+1 FROM SAILOR S,RESERVE R1,RESERVE R2 WHERE S.SID=R1.SID AND S.SID=R2.SID AND R1.DAY=R2.DAY AND R1.BID<>R2.BID;
21)SELECT DISTINCT(SNAME) FROM SAILOR WHERE SNAME LIKE 'B_%b' OR SNAME LIKE 'b_%B' OR SNAME LIKE 'B_%B' OR SNAME LIKE 'b_%b';
22)SELECT DISTINCT(SNAME) FROM SAILOR WHERE SNAME LIKE 'B_b';