written 8.4 years ago by | • modified 2.9 years ago |
written 8.4 years ago by |
Consider the following tables:
Sailor
sid | sname | rating | age |
---|---|---|---|
1 | Alan | 8 | 26 |
2 | Arina | 7 | 25 |
3 | Roone | 9 | 30 |
4 | Jeet | 9 | 22 |
5 | Rakesh | 6 | 40 |
Boat
bid | bname | color |
---|---|---|
1 | Alpha Ship | White |
2 | Beta Ship | Black |
3 | Gamma Ship | Yellow |
4 | Delta Ship | Red |
Reserves
sid | bid | date |
---|---|---|
1 | 3 | 24/01/2015 |
2 | 4 | 25/01/2015 |
3 | 4 | 30/12/2014 |
SELECT sname FROM
boat
b1,reserves
r1,sailor
s1 WHERE b1.bid = r1.bid AND s1.sid = r1.sid AND b1.color = 'red' Output:
Arina |
---|
Roone |
[Note: Above query uses concept of sub-query. Here we first find maximum rating of sailor in a sub-query and then use the result to gets the sailor’s name]
2.
SELECT sname
FROM `sailor`
WHERE rating IN (
SELECT max(rating) FROM `sailor`
)
Output:
Roone |
---|
Jeet |
[Note: Above query uses concept of sub-query. Here we first find maximum rating of sailor in a sub-query and then use the result to gets the sailor’s name]
3.
SELECT avg(age) AS 'Average Age'
FROM `sailor`
Output:
Average Age |
---|
28.6000 |
[Note: avg() is an aggregate function in sql. ‘AS’ keyword used to assign an alias to the column or a table]
4.
SELECT min(age), rating
FROM `sailor`
GROUP BY rating
Output:
min(age) | rating |
---|---|
40 | 6 |
25 | 7 |
22 | 9 |
[Note: To get result according to each rating level, we use GROUP BY clause and then use min() aggregate function on age]
5.
INSERT INTO `boat` (bid, bname, color)
VALUES(5, ‘Pecca Ship’, ‘Blue’)
Output:
Boat
bid | bname | color |
---|---|---|
1 | Alpha Ship | White |
2 | Beta Ship | Black |
3 | Gamma Ship | Yellow |
4 | Delta Ship | Red |
5 | Pecca Ship | Blue |