數(shù)據(jù)庫SQL命令測試題2

---

10-1 查詢“李琳”老師所授課程的課程名稱

作者 邵煜

單位 寧波財經(jīng)學(xué)院

本題目要求編寫SQL語句，檢索出teachers、teaching、course表中“李琳”老師所授課程的課程名稱。

提示：請使用join連接查詢語句作答。

表結(jié)構(gòu):

請在這里寫定義表結(jié)構(gòu)的SQL語句。例如：

CREATE TABLE teachers (tno char(3) ,tname char(8),ps char(10),tbirthday date ,tdept char(16) ,tsex char(2),PRIMARY KEY (tno)
) ;CREATE TABLE teaching (sid int ,cterm int,class char(10) DEFAULT NULL,cno char(7) NOT NULL,tno char(3) DEFAULT NULL,period int DEFAULT NULL,PRIMARY KEY (sid)
) ;CREATE TABLE course (cno char(7) ,cname varchar(20) NOT NULL,cpno char(7),ccredit int NOT NULL,PRIMARY KEY (cno)
) ;

表樣例

請在這里給出上述表結(jié)構(gòu)對應(yīng)的表樣例。例如

teachers表：

tno	tname	ps	tbirthday	tdept	tsex
001	譚浩強(qiáng)	教授	1958-01-01	計科	男
002	王珊	教授	1962-02-13	計科	女
003	薩師煊	教授	1953-05-01	計科	男
004	嚴(yán)蔚敏	副教授	1968-07-02	軟工	女
005	李琳	講師	1988-11-15	軟工	女
006	韓萬江	助教	1992-10-17	信管	男

teaching表：

sid	cterm	class	cno	tno	period
1	1	17 物流 1	0000011	001	36
2	1	17 物流 1	0000034	002	72
3	3	17 物流 1	0000052	003	60
4	1	17 物流 1	0000027	004	108
5	2	17 物流 1	0000039	005	36
6	6	17 物流 1	0000005	006	72

course表：

cno	cname	cpno	ccredit
0000001	數(shù)據(jù)庫 OCP 考證	NULL	4
0000002	C 語言基礎(chǔ)	0000027	9
0000003	Linux 操作系統(tǒng)	0000013	5
0000010	數(shù)據(jù)結(jié)構(gòu)	0000002	4
0000039	基礎(chǔ)會計	NULL	2

輸出樣例:

請在這里給出輸出樣例。例如：

cname
基礎(chǔ)會計

提交代碼：

select c.cname
from course c
join teaching t1 on c.cno = t1.cno
join teachers t2 on t1.tno = t2.tno
where t2.tname = "李琳";

---

10-2 查詢成績比所有課程的平均成績高的學(xué)生的學(xué)號及成績

作者 邵煜

單位 寧波財經(jīng)學(xué)院

本題目要求編寫SQL語句，檢索出sc表中，課程成績比所有課程的平均成績高的學(xué)生的學(xué)號及成績。

提示：請使用嵌套查詢語句作答。

表結(jié)構(gòu):

請在這里寫定義表結(jié)構(gòu)的SQL語句。例如：

CREATE TABLE sc (sno char(7) ,cno char(7) ,score decimal(4,1),point decimal(2,1),PRIMARY KEY (sno,cno)
) ;

表樣例

請在這里給出上述表結(jié)構(gòu)對應(yīng)的表樣例。例如

sc表：

sno	cno	score	point
1311104	0000011	53.0	0.0
1311104	0000027	80.0	1.0
1311105	0000027	84.0	1.0
1711101	0000052	71.0	2.0

輸出樣例:

請在這里給出輸出樣例。例如：

sno	score
1311104	80.0
1311105	84.0

提交代碼：

select sno,score
from sc
where score > (select avg(score)from sc
);

---

10-3 創(chuàng)建帶表達(dá)式的視圖StuView

作者 李翔坤

單位 大連東軟信息學(xué)院

已知學(xué)生表Student，創(chuàng)建學(xué)生信息的視圖StuView，包括學(xué)生學(xué)號、姓名和年齡，在視圖中的列名分別為No，Name和Age。

Student表結(jié)構(gòu):

create table  Student(sno char(8)  primary key,sname varchar(10) not null,gender char(2) check(gender='男' or gender='女'),birthdate  date,major varchar(20)  default '軟件工程');

Student 表 數(shù)據(jù)樣例：

Student表：

sno	sname	gender	birthdate	major
21012101	李勇	男	2005-10-20	計算機(jī)科學(xué)
21012102	劉晨	男	2006-5-5	計算機(jī)科學(xué)
21012103	王曉敏	女	2005-10-6	計算機(jī)科學(xué)
21021101	李佳睿	男	2006-3-30	軟件工程
21021102	吳賓	男	2005-9-21	軟件工程
21021103	張海	男	2005-10-20	軟件工程
21031101	錢曉萍	女	2006-6-1	網(wǎng)絡(luò)工程
21031102	王大力	男	2005-11-15	網(wǎng)絡(luò)工程
21041101	于洋	男	2006-3-15	數(shù)據(jù)科學(xué)
21041102	郭霖	男	2006-3-2	數(shù)據(jù)科學(xué)

輸出樣例:

StuView視圖：

提交代碼：

create view StuView(No,Name,Age)
as select sno,sname,YEAR(CURRENT_DATE) - YEAR(birthdate)
from Student;

---

10-4 從視圖PerView中查詢數(shù)據(jù)

作者 李翔坤

單位 大連東軟信息學(xué)院

從上題中創(chuàng)建的視圖PerView中查詢平均成績超過75分的專業(yè)有哪些。

PerView視圖結(jié)構(gòu):

 Create view PerView(專業(yè)名, 修課人數(shù), 平均成績)
AS Select major, count(distinct sc.sno), avg(grade) from student join sc on student.sno=sc.sno group by major;

PerView視圖數(shù)據(jù)樣例

PerView視圖：

專業(yè)名	修課人數(shù)	平均成績
數(shù)據(jù)科學(xué)	1	72.0
網(wǎng)絡(luò)工程	1	87.5
計算機(jī)科學(xué)	2	80.0
軟件工程	2	73.4

輸出樣例:

專業(yè)名	平均成績
網(wǎng)絡(luò)工程	87.5
計算機(jī)科學(xué)	80.0

提價代碼：

select 專業(yè)名,平均成績
from PerView
where 平均成績 > 75;

---

10-5 查詢工資高于在“HR”部門工作的所有員工的工資的員工信息

作者 李翔坤

單位 大連東軟信息學(xué)院

查詢工資高于在“HR”部門工作的所有員工的工資的員工信息

提示：請使用SELECT語句作答。

表結(jié)構(gòu):

請在這里寫定義表結(jié)構(gòu)的SQL語句。例如：

CREATE TABLE employees (  employee_id INT PRIMARY KEY,  first_name VARCHAR(50),  last_name VARCHAR(50),  department VARCHAR(50),  salary INT  
);

表樣例

employees表：

輸出樣例:

提交代碼：

select *
from employees 
where salary > (select max(salary)from employeeswhere department = "HR"
);

---

10-6 查詢選修的各課程平均成績高于85分的學(xué)生學(xué)號、姓名和專業(yè)

作者 李翔坤

單位 大連東軟信息學(xué)院

查詢選修的各課程平均成績高于85分的學(xué)生學(xué)號、姓名和專業(yè)

提示：請使用SELECT語句作答。

表結(jié)構(gòu):

create table if not exists Student(sno char(8)  primary key,sname varchar(10) not null,gender char(2) check(gender='男' or gender='女'),birthdate  date,major varchar(20)  default '軟件工程');
create table if not exists SC(    -- 選課成績單表scid  int auto_increment  primary key,sno char(8)   references Student(sno),cno char(10)  references Course(cno),tno char(15)  references Teacher(tno),grade int check(grade>=0 and grade<=100),gpoint  decimal(2,1),     -- 學(xué)生得到的課程績點(diǎn)memo  text(100)    --  備注);

表樣例

請在這里給出上述表結(jié)構(gòu)對應(yīng)的表樣例。例如

Student表：

SC表：

輸出樣例:

提交代碼：

SELECT s.sno, s.sname, s.major
FROM Student s
JOIN (SELECT sno, AVG(grade) AS avg_gradeFROM SCGROUP BY snoHAVING AVG(grade) > 85
) AS high_avg ON s.sno = high_avg.sno;

---

10-7 查詢修課平均成績最高的學(xué)生學(xué)號

作者 李翔坤

單位 大連東軟信息學(xué)院

查詢修課平均成績最高的學(xué)生學(xué)號

提示：請使用SELECT語句作答。

表結(jié)構(gòu):

請在這里寫定義表結(jié)構(gòu)的SQL語句。例如：

create table if not exists Student(sno char(8)  primary key,sname varchar(10) not null,gender char(2) check(gender='男' or gender='女'),birthdate  date,major varchar(20)  default '軟件工程');select * from student;
create table if not exists Course(cno char(10) primary key,cname varchar(20) not null,ccredit int check(ccredit>0), -- 課程學(xué)分semester int check(semester>0),  -- 學(xué)期period int  check(period>0)     -- 總學(xué)時);select * from course;
create table if not exists Teacher(Tno char(15) primary key,Tname varchar(10) not null,gender char(2),deptname varchar(50) ,  -- 所屬系部title varchar(20)       -- 職稱
);
create table if not exists SC(    -- 選課成績單表scid  int auto_increment  primary key,sno char(8)   references Student(sno),cno char(10)  references Course(cno),tno char(15)  references Teacher(tno),grade int check(grade>=0 and grade<=100),gpoint  decimal(2,1),     -- 學(xué)生得到的課程績點(diǎn)memo  text(100)    --  備注);

表樣例

Student表：

Course表：

Teacher表：

SC表：

輸出樣例:

提交代碼：

select sno
from SC
GROUP BY sno
HAVING AVG(grade) = (SELECT MAX(avg_grade)FROM (SELECT AVG(grade) AS avg_gradeFROM SCGROUP BY sno) AS avg_grades
);

---

10-8 SQL除法查詢1

作者 沈煒

單位 浙江理工大學(xué)

本題目要求編寫SQL語句，
檢索出movies表中拍攝了所有Fox公司拍攝的所有電影類型的電影公司。

提示：本題意思就是找這樣的電影公司，只要是Fox拍了某個電影類型的電影，那么這個公司也拍過這樣類型的電影。

表結(jié)構(gòu):

請在這里寫定義表結(jié)構(gòu)的SQL語句。例如：

CREATE TABLE movies (title char(100) NOT NULL DEFAULT '',year int(11) NOT NULL DEFAULT '0',length int(11) DEFAULT NULL,movieType char(10) DEFAULT NULL,studioName char(30) DEFAULT NULL,producerC int(11) DEFAULT NULL,PRIMARY KEY (title,year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表樣例

請在這里給出上述表結(jié)構(gòu)對應(yīng)的表樣例。例如

movies表：

title	year	length	movieType	studioName	producerC
Empire Strikes Back	1980	111	drama	Fox	555
Gone With the Wind	1938	238	drama	MGM	123
Logan’s run	1977	120	drama	MGM	888
Pretty Woman	1990	119	drama	Disney	999
Star Trek	1979	132	sciFic	Paramount	444
Star Trek: Nemesis	2002	116	sciFic	Paramount	321
Star Wars	1977	124	sciFic	Fox	555
Star Wars	2015		sciFic	FOX
Star Wars	2017		sciFic
Terms of Endearment	1983	132	drama	MGM	123
The Man Who Wasn’t There	2001	116	comedy	USA Entertainm.	777
The Usual Suspects	1995	106	drama	MGM	999

輸出樣例:

請在這里給出輸出樣例。例如：

studioName
Fox
MGM

注意：返回的公司名稱必須唯一。

提交代碼：

SELECT DISTINCT studioName
FROM movies m1
WHERE NOT EXISTS (SELECT movieTypeFROM moviesWHERE studioName = 'Fox'GROUP BY movieTypeHAVING NOT EXISTS (SELECT *FROM movies m2WHERE m2.studioName = m1.studioNameAND m2.movieType = movies.movieType)
);

---

10-9 SQL除法查詢2

作者 沈煒

單位 浙江理工大學(xué)

查詢出演了演員Carrie Fisher出演的所有電影的演員

提示：滿足題目條件的演員，應(yīng)該是只要Carrie Fisher出演了一本電影，這個演員也演了；不同年份，名稱相同的電影認(rèn)為是同一本電影。

表結(jié)構(gòu):

CREATE TABLE starsin (movieTitle char(100) NOT NULL DEFAULT '',movieYear int(11) NOT NULL DEFAULT '0',starName char(30) NOT NULL DEFAULT '',PRIMARY KEY (movieTitle,movieYear,starName)
);

表樣例

starsin表：

movieTitle	movieYear	starName
Empire Strikes Back	1980	Harrison Ford
Star Wars	1977	Carrie Fisher
Star Wars	1977	Harrison Ford
Star Wars	1977	Mark Hamill
Star Wars	2017	Carrie Fisher
Terms of Endearment	1983	Debra Winger
Terms of Endearment	1983	Jack Nicholson
The Usual Suspects	1995	Kevin Spacey

輸出樣例:

starName
Harrison Ford
Carrie Fisher
Mark Hamill

提交代碼：

select distinct(starName)
from starsin s1
where not exists(select *from starsin s2where s2.starName = "Carrie Fisher" and not exists(select *from starsin s3where s3.starName = s1.starName and s3.movieTitle = s2.movieTitle)
);

---

10-10 創(chuàng)建分組統(tǒng)計視圖

作者 李翔坤

單位 大連東軟信息學(xué)院

創(chuàng)建每個專業(yè)學(xué)生修課信息的視圖PerView，包括每個專業(yè)的專業(yè)名稱、修課的學(xué)生人數(shù)、平均成績。

表結(jié)構(gòu):

create table Student(sno char(8)  primary key,sname varchar(10) not null,gender char(2) check(gender='男' or gender='女'),birthdate  date,major varchar(20)  default '軟件工程');create table SC(  scid  int auto_increment  primary key,sno char(8)   references Student(sno),cno char(10)  references Course(cno),tno char(15)  references Teacher(tno),grade int check(grade>=0 and grade<=100),gpoint  decimal(2,1),   memo  text(100)  );

表樣例

Student表：

sno	sname	gender	birthdate	major
21012101	李勇	男	2005-10-20	計算機(jī)科學(xué)
21012102	劉晨	男	2006-5-5	計算機(jī)科學(xué)
21012103	王曉敏	女	2005-10-6	計算機(jī)科學(xué)
21021101	李佳睿	男	2006-3-30	軟件工程
21021102	吳賓	男	2005-9-21	軟件工程
21021103	張海	男	2005-10-20	軟件工程
21031101	錢曉萍	女	2006-6-1	網(wǎng)絡(luò)工程
21031102	王大力	男	2005-11-15	網(wǎng)絡(luò)工程
21041101	于洋	男	2006-3-15	數(shù)據(jù)科學(xué)
21041102	郭霖	男	2006-3-2	數(shù)據(jù)科學(xué)

SC表：

scid	sno	cno	tno	grade	gpoint	memo
null	21012101	c01	t200306m12132	90	null	null
null	21012101	c02	t200703m12218	86	null	null
null	21012101	c03	t200703m12218	null	null	缺考
null	21012102	c02	t200703m12218	78	null	null
null	21012102	c03	t200703m12218	66	null	null
null	21021102	c01	t200306m12132	82	null	null
null	21021102	c02	t200608f12205	75	null	null
null	21021102	c03	t200306m12132	null	null	緩考
null	21021102	c05	t201803f12405	50	null	null
null	21021103	c02	t200703m12218	68	null	null
null	21021103	c04	t201208m12308	92	null	null
null	21031101	c01	t200306m12132	80	null	null
null	21031101	c02	t200608f12205	95	null	null
null	21041102	c02	t200608f12205	56	null	null
null	21041102	c05	t201803f12405	88	null	null

輸出樣例:

PerView視圖：

提交代碼：

CREATE VIEW PerView AS
SELECT s.major AS 專業(yè)名,COUNT(DISTINCT s.sno) AS 修課人數(shù),AVG(sc.grade) AS 平均成績
FROM Student s
JOIN SC sc ON s.sno = sc.sno
GROUP BY s.major;

---

10-11 查詢教授多門課程的教師編號及教授的課程門數(shù)

作者 馬豐媛

單位 大連東軟信息學(xué)院

題目描述：查詢教授多門課程的教師編號及教授的課程門數(shù)。

提示：請使用SELECT語句作答。

表結(jié)構(gòu):

SC表結(jié)構(gòu)：

create table  SC( scid  int auto_increment  primary key,sno char(8)   references Student(sno),cno char(10)  references Course(cno),tno char(15)  references Teacher(tno),grade int check(grade>=0 and grade<=100),gpoint  decimal(2,1), memo  text(100) );

表樣例

請在這里給出上述表結(jié)構(gòu)對應(yīng)的表樣例。例如

SC表：

輸出樣例:

請在這里給出輸出樣例。例如：

提交代碼：

SELECT tno AS tno,COUNT(DISTINCT cno) AS 門數(shù)
FROM SC
GROUP BY tno
HAVING COUNT(DISTINCT cno) > 1;

---

10-12 查詢選修了3門及3門以上課程的學(xué)生學(xué)號、平均成績和修課門數(shù)，并用中文顯示列名

作者 馬豐媛

單位 大連東軟信息學(xué)院

題目描述：查詢選修了3門及3門以上課程的學(xué)生學(xué)號、平均成績和修課門數(shù)，并用中文顯示列名。

提示：請使用SELECT語句作答。

表結(jié)構(gòu):

SC表結(jié)構(gòu)的SQL語句：

create table  SC( scid  int auto_increment  primary key,sno char(8)   references Student(sno),cno char(10)  references Course(cno),tno char(15)  references Teacher(tno),grade int check(grade>=0 and grade<=100),gpoint  decimal(2,1), memo  text(100) );

表樣例

請在這里給出上述表結(jié)構(gòu)對應(yīng)的表樣例。例如

SC表：

輸出樣例:

請在這里給出輸出樣例。例如：

select sno as "學(xué)號",avg(grade) as "平均成績",count(cno) as "修課門數(shù)"
from SC
group by sno
having count(cno) >= 3;

---

本文作者： 鴻·蒙

撰寫工具： Typora

內(nèi)容反饋： 若發(fā)現(xiàn)本文內(nèi)容有誤或有任何意見，歡迎向作者鴻·蒙反饋或評論區(qū)留言。

版權(quán)聲明： 本博客所有文章除特別聲明外，均采用 CC BY-NC-SA 4.0 許可協(xié)議。轉(zhuǎn)載請注明來自 鴻·蒙 ！