SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE Sailors (sid integer PRIMARY KEY, sname varchar(20), rating integer, age real); CREATE TABLE Boats (bid integer PRIMARY KEY, bname varchar(20), color varchar(20)); CREATE TABLE Reserves(sid integer, bid integer, day integer, PRIMARY KEY (sid, bid, day), FOREIGN KEY (sid) references Sailors (sid), FOREIGN KEY (bid) references Boats (bid)); INSERT INTO Boats (bid, bname, color) VALUES (1, 'none', 'red'); INSERT INTO Boats (bid, bname, color) VALUES (2, '2', 'red'); INSERT INTO Sailors (sid, sname, rating, age) VALUES (1, 'name', 1, 2.5); INSERT INTO Reserves (sid, bid, day) VALUES (1, 1, 1); INSERT INTO Reserves (sid, bid, day) VALUES (1, 1, 2); --SELECT S.sid, (SELECT COUNT(*) FROM Reserves R WHERE R.sid = S.sid AND R.color = 'red') FROM Sailors S; SELECT * FROM Boats B left join Reserves R on B.bid = R.bid;