首页 新闻 会员 周边 捐助

数据库语句(oracle sql)

0
悬赏园豆:40 [已解决问题] 解决于 2020-09-19 14:04

需要一个比较准确的答案,希望您不吝赐教,主要是题目的第一部分!
因为自己数据库知识有限,苦想2天也没有找到准确的答案,所以来求助
其余部分如果能给出答案最好。题目PDF格式如下(最好您能在机器上试验过,确保逻辑没有问题):
https://www.lanzoux.com/irp1bgqvhxi

头发都掉没了。

YJLAugus的主页 YJLAugus | 菜鸟二级 | 园豆:236
提问于:2020-09-18 23:41
< >
分享
最佳答案
0

我的浏览器提示你的文件中有病毒,没敢打开

收获园豆:40
会长 | 专家六级 |园豆:12461 | 2020-09-19 10:34

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

YJLAugus | 园豆:236 (菜鸟二级) | 2020-09-19 10:36

@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 
	)

这表设计的像一坨屎

会长 | 园豆:12461 (专家六级) | 2020-09-19 12:42

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

YJLAugus | 园豆:236 (菜鸟二级) | 2020-09-19 14:04
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册