首先谢谢您~会长，这是蓝奏云盘的地址我也不知道为什么会提示报毒？

@YJLAugus:

Give all types of planes that are used on a connection from BRU.

SELECT DISTINCT
	type.type 
FROM
	type
	JOIN plane ON type.plane_nr = plane.plane_nr
	JOIN flight ON flight.flight_nr = plane.flight_nr 
WHERE
	flight.dept = 'BRU'

Give all planes that are not involved in any flight

SELECT
	plane.plane_nr 
FROM
	plane 
WHERE
	flight_nr IS NULL

Give all types of planes that are involved in all flights.

SELECT
	type.type 
FROM
	type 
WHERE
	ptyoe.plane_nr IN (
	SELECT
		plane_nr 
	FROM
		( SELECT plane_nr, count( flight_nr ) AS flight_count FROM plane GROUP BY plane_nr ) t 
	WHERE
	t.flight_count = ( SELECT count( flight_nr ) FROM flight ) 
	)

Give all connections (i.e. departure-destination pairs) for which not all plane types are used.

SELECT
	t.flight.dept,
	flight.dest 
FROM
	(
	SELECT
		flight.dept,
		flight.dest,
		count( DISTINCT type.type ) AS type_count 
	FROM
		flight
		LEFT JOIN plane ON flight.flight_nr = plane.flight_nr
		JOIN type ON plane.plane_nr = type.plane_nr 
	GROUP BY
		flight.dept,
		flight.dest 
	) t 
WHERE
	t.type_count < ( SELECT count( type.type ) FROM type )

Give all flight numbers that are used multiple times (e.g., (AA89,BRU,LAX) and(AA89,BRU,JFK) results in (AA89))

SELECT DISTINCT
	t1.flight_nr 
FROM
	flight t1
	JOIN flight t2 ON t1.flight_nr = t2.flight_nr 
WHERE
	t1.dept != t2.dept 
	OR t1.dest != t2.dest

Give all pairs of different flight numbers that are used on the same connection (e.g.,(AA89,BRU,LAX) and (UA04,BRU,LAX) results in (AA89,UA04))

SELECT DISTINCT
	t1.flight_nr 
FROM
	flight t1
	JOIN flight t2 ON t1.dept = t2.detp 
	AND t1.dest = t2.dest 
WHERE
	t1.flight_nr != t2._flight_nr

Give all planes that have a plane type that is used at least once for every connection.

SELECT
	plane_nr 
FROM
	type 
WHERE
	type.type IN (
	SELECT
		type.type 
	FROM
		type
		LEFT JOIN plane ON type.plane_nr = plane.plane_nr
		LEFT JOIN flight ON plane.flight_nr = flight.flight_nr 
	WHERE
	flight.flight_nr IS NOT NULL 
	)

这表设计的像一坨屎

@会长: 嗯hhh 谢谢 会长_{我慢慢研究} 就是感觉就是未了为难人设计的