SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
CREATE TABLE Pilot ( pilot_id SERIAL PRIMARY KEY, name_ text, age integer, rank_ integer, educational_level integer ); CREATE TABLE Plane( plane_id SERIAL PRIMARY KEY, capacity integer, cargo_flg integer ); CREATE TABLE Flight ( flight_id SERIAL, flight_dt date, plane_id SERIAL, first_pilot_id SERIAL, second_pilot_id SERIAL, destination text, quantity integer, PRIMARY KEY(flight_id, flight_dt), FOREIGN KEY(plane_id) REFERENCES Plane(plane_id), FOREIGN KEY(first_pilot_id) REFERENCES Pilot(pilot_id), FOREIGN KEY(second_pilot_id) REFERENCES Pilot(pilot_id) ); SELECT pl.name_ FROM Pilot pl RIGHT JOIN Flight fl ON (pl.pilot_id = fl.second_pilot_id) WHERE destination = 'Шереметьево' GROUP BY pilot_id HAVING count(*) = 3; WITH tmp AS ( SELECT * FROM Pilot pl RIGHT JOIN Flight fl ON (pl.pilot_id = fl.first_pilot_id) LEFT JOIN PLANE USING(plane_id) UNION SELECT * FROM Pilot pl RIGHT JOIN Flight fl ON (pl.pilot_id = fl.second_pilot_id) LEFT JOIN PLANE USING(plane_id) ) SELECT name_ FROM tmp WHERE age > 45 AND cargo_flg = 0 AND capacity > 30; WITH pl_id AS (SELECT first_pilot_id FROM Pilot pl RIGHT JOIN Flight fl ON (pl.pilot_id = fl.first_pilot_id) LEFT JOIN Plane USING(plane_id) WHERE cargo_flg = 1 GROUP BY first_pilot_id ORDER BY count(flight_id) DESC limit(10)) SELECT name_ FROM Pilot RIGHT JOIN pl_id ON Pilot.pilot_id = pl_id.first_pilot_id;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear