SQL Queries with Output
Table Creation Statements
CREATE TABLE Sailors (
sid INTEGER PRIMARY KEY,
sname VARCHAR(50),
rating INTEGER,
age REAL
);
CREATE TABLE Boats (
bid INTEGER PRIMARY KEY,
bname VARCHAR(50),
color VARCHAR(20)
);
CREATE TABLE Reserves (
sid INTEGER,
bid INTEGER,
day DATE,
FOREIGN KEY (sid) REFERENCES Sailors(sid),
FOREIGN KEY (bid) REFERENCES Boats(bid)
);
Sample Data Insertion (Few Records)
INSERT INTO Sailors VALUES (22, 'Dustin', 7, 45.0);
INSERT INTO Sailors VALUES (29, 'Brutus', 1, 33.0);
INSERT INTO Sailors VALUES (31, 'Lubber', 8, 55.5);
INSERT INTO Sailors VALUES (32, 'Andy', 8, 25.5);
INSERT INTO Sailors VALUES (64, 'Horatio', 7, 35.0);
SQL Queries with Output
INSERT INTO Boats VALUES (101, 'Interlake', 'blue');
INSERT INTO Boats VALUES (102, 'Interlake', 'red');
INSERT INTO Boats VALUES (103, 'Clipper', 'green');
INSERT INTO Boats VALUES (104, 'Marine', 'red');
INSERT INTO Reserves VALUES (22, 101, '1998-10-10');
INSERT INTO Reserves VALUES (22, 102, '1998-10-10');
INSERT INTO Reserves VALUES (31, 103, '1998-11-10');
INSERT INTO Reserves VALUES (64, 101, '1998-09-05');
INSERT INTO Reserves VALUES (64, 102, '1998-09-08');
SQL Queries with Output
WHERE Clause Examples
Query:
SELECT * FROM Sailors WHERE rating > 5;
Output:
22 | Dustin | 7 | 45.0
31 | Lubber | 8 | 55.5
32 | Andy | 8 | 25.5
64 | Horatio | 7 | 35.0
Query:
SELECT * FROM Sailors WHERE age < 30;
Output:
32 | Andy | 8 | 25.5
Query:
SELECT * FROM Boats WHERE color = 'red';
Output:
102 | Interlake | red
104 | Marine | red
Query:
SELECT * FROM Reserves WHERE sid = 22;
Output:
22 | 101 | 1998-10-10
22 | 102 | 1998-10-10
Query:
SQL Queries with Output
SELECT * FROM Sailors WHERE sname LIKE 'H%';
Output:
64 | Horatio | 7 | 35.0
SQL Queries with Output
ORDER BY Clause Examples
Query:
SELECT * FROM Sailors ORDER BY age ASC;
Output:
32 | Andy | 8 | 25.5
64 | Horatio | 7 | 35.0
22 | Dustin | 7 | 45.0
31 | Lubber | 8 | 55.5
Query:
SELECT * FROM Sailors ORDER BY rating DESC;
Output:
31 | Lubber | 8 | 55.5
32 | Andy | 8 | 25.5
22 | Dustin | 7 | 45.0
64 | Horatio | 7 | 35.0
Query:
SELECT * FROM Boats ORDER BY color ASC;
Output:
101 | Interlake | blue
103 | Clipper | green
102 | Interlake | red
104 | Marine | red
Query:
SELECT * FROM Boats ORDER BY bname DESC;
SQL Queries with Output
Output:
104 | Marine | red
103 | Clipper | green
102 | Interlake | red
101 | Interlake | blue
Query:
SELECT * FROM Reserves ORDER BY day ASC;
Output:
64 | 101 | 1998-09-05
64 | 102 | 1998-09-08
22 | 101 | 1998-10-10
22 | 102 | 1998-10-10
31 | 103 | 1998-11-10
SQL Queries with Output
GROUP BY Clause Examples
Query:
SELECT rating, COUNT(*) FROM Sailors GROUP BY rating;
Output:
7|2
8|2
1|1
Query:
SELECT age, COUNT(*) FROM Sailors GROUP BY age;
Output:
25.5 | 1
35.0 | 1
45.0 | 1
55.5 | 1
33.0 | 1
Query:
SELECT color, COUNT(*) FROM Boats GROUP BY color;
Output:
blue | 1
green | 1
red | 2
Query:
SELECT sid, COUNT(*) FROM Reserves GROUP BY sid;
Output:
SQL Queries with Output
22 | 2
31 | 1
64 | 2
Query:
SELECT bid, COUNT(*) FROM Reserves GROUP BY bid;
Output:
101 | 2
102 | 2
103 | 1
SQL Queries with Output
HAVING Clause Examples
Query:
SELECT rating, COUNT(*) FROM Sailors GROUP BY rating HAVING COUNT(*) > 1;
Output:
7|2
8|2
Query:
SELECT age, COUNT(*) FROM Sailors GROUP BY age HAVING COUNT(*) = 1;
Output:
25.5 | 1
35.0 | 1
45.0 | 1
55.5 | 1
33.0 | 1
Query:
SELECT color, COUNT(*) FROM Boats GROUP BY color HAVING COUNT(*) >= 2;
Output:
red | 2
Query:
SELECT sid, COUNT(*) FROM Reserves GROUP BY sid HAVING COUNT(*) > 1;
Output:
22 | 2
64 | 2
SQL Queries with Output
Query:
SELECT bid, COUNT(*) FROM Reserves GROUP BY bid HAVING COUNT(*) = 1;
Output:
103 | 1
