drop database mytest
create database MyTest
use Mytest
--1、問(wèn)題描述：
--已知關(guān)系模式：
--S (SNO,SNAME）學(xué)生關(guān)系。SNO 為學(xué)號(hào)，SNAME 為姓名
--C (CNO,CNAME,CTEACHER)? 課程關(guān)系。CNO 為課程號(hào)，CNAME 為課程名，CTEACHER 為任課教師
--SC(SNO,CNO,SCGRADE)? ? ? ? 選課關(guān)系。SCGRADE 為成績(jī)
create table s(sno int, sname varchar(10))
insert into s values(100,'張三')
insert into s values(101,'李四')
insert into s values(102,'王五')
insert into s values(103,'趙六')
insert into s values(104,'田七')
insert into s values(105,'王八')
insert into s values(106,'王八2')
go
create table c(cno int, cname varchar(10),cteacher varchar(10))
insert into c values(1,'c語(yǔ)言','李明')
insert into c values(2,'sql','李果')
go
create table sc(sno int,cno int,scgrade int)
insert into sc values(100,1,50)
insert into sc values(100,2,55)
insert into sc values(101,2,87)
insert into sc values(102,1,50)
insert into sc values(102,2,55)
insert into sc values(103,1,80)
insert into sc values(103,2,55)
insert into sc values(104,1,90)
insert into sc values(105,1,85)
insert into sc values(106,1,85)
insert into sc values(106,2,20)
go
select * from s
select * from c
select * from sc
--1.找出沒(méi)有選修過(guò)“李明”老師講授
--課程的所有學(xué)生姓名
--分析
--1)先查詢(xún)出李明老師的編號(hào)
--2)在查詢(xún)出學(xué)過(guò)李明老師的學(xué)生編號(hào)
--3)在查詢(xún)出沒(méi)有學(xué)過(guò)李明老師的學(xué)生
select sname from s where sno not in(
select sno from sc where cno in(
select cno from c where cteacher = '李明'))
--2.列出有二門(mén)以上（含兩門(mén)）
--不及格課程的學(xué)生姓名及其平均成績(jī)
--分析
--1)先查詢(xún)出兩門(mén)不及格課程以上的學(xué)生編號(hào)
--2)在做連接查詢(xún)
--3)在根據(jù)學(xué)生編號(hào)查詢(xún)出學(xué)生姓名用avg聚合函數(shù)統(tǒng)計(jì)出平均成績(jī) 使用group by 進(jìn)行分組
select sname,avg(sc.scgrade) from s s inner join sc
on s.sno = sc.sno
where sc.sno in (select sno from sc
where scgrade <60
group by sno
having count(*)>=2)
group by sname
--3.列出既學(xué)過(guò)“1”號(hào)課程，又學(xué)過(guò)“2”號(hào)課程
--的所有學(xué)生姓名
--分析
--1)先根據(jù) 課程編號(hào)1 和課程編號(hào)2查詢(xún)學(xué)過(guò)1號(hào)和2號(hào)的學(xué)生編號(hào)
--2)在根據(jù) 學(xué)生編號(hào)查詢(xún)學(xué)生姓名
select sname from s where sno in(
select sno from sc where cno = 1 and sno in(
select sno from sc where cno = 2))
--4.列出“1”號(hào)課成績(jī)比
--“2”號(hào)同學(xué)該門(mén)課成績(jī)高的所有學(xué)生的學(xué)號(hào)
--分析
--1)先查詢(xún)出2號(hào)同學(xué)的該門(mén)的學(xué)生成績(jī)
--2)2號(hào)同學(xué)的成績(jī)對(duì) 所有學(xué)生的一號(hào)成績(jī)對(duì)比進(jìn)行篩選
select * from sc where cno = 1 and scgrade >(
select scgrade from sc where sno = 101and cno = 1)
--5.列出“1”號(hào)課成績(jī)比“2”號(hào)課成績(jī)
--高的所有學(xué)生的學(xué)號(hào)? ?
--及其“1”號(hào)課和“2”號(hào)課的成績(jī)
--分析
--1)先查詢(xún)出同一名“2”號(hào)課成績(jī)
--2)在查詢(xún)出一號(hào)成績(jī)進(jìn)行對(duì)比
select sc1.scgrade,(select scgrade? from sc sc where sc.sno = sc1.sno and sc.cno = 2
)
from sc sc1 where cno = 1 and scgrade >(
select scgrade from sc sc2 where cno = 2 and sc1.sno = sc2.sno
)
--2、問(wèn)題描述：
--為管理崗位業(yè)務(wù)培訓(xùn)信息，建立3個(gè)表:
--S (Sno,SName,SDept,SAge)? S#,SN,SD,SA 分別代表學(xué)號(hào)、學(xué)員姓名、所屬單位、學(xué)員年齡
--C (Cno,CName )? ? ? ? C#,CN? ? ? 分別代表課程編號(hào)、課程名稱(chēng)
--SC ( Sno,Cno,Grade )? ? S#,C#,G? ? 分別代表學(xué)號(hào)、所選修的課程編號(hào)、學(xué)習(xí)成績(jī)
-- 學(xué)生信息表
--字段分別代表:學(xué)號(hào)、學(xué)員姓名、所屬單位、學(xué)員年齡
create table stu(Sno int,SName varchar(10), SDept varchar(50) ,SAge int )
insert into stu values(1,'小明','稅務(wù)局',26)
insert into stu values(2,'小王','鐵路局',27)
insert into stu values(3,'小和','電力局',28)
insert into stu values(4,'小寶','文化局',29)
go
--課程信息表
--字段分別代表:課程編號(hào)、課程名稱(chēng)
create table cc(Cno char(2), CName varchar(20))
insert into cc values('c1','計(jì)算機(jī)原理')
insert into cc values('c2','經(jīng)濟(jì)學(xué)原理')
insert into cc values('c3','稅收基礎(chǔ)')
insert into cc values('c4','C語(yǔ)言基礎(chǔ)')
insert into cc values('c5','中國(guó)刑法')
insert into cc values('c6','心理學(xué)')
insert into cc values('c7','會(huì)計(jì)學(xué)')
go
--選課信息表
--字段分別代表代表學(xué)號(hào)、所選修的課程編號(hào)、學(xué)習(xí)成績(jī)
create table scc(Sno int, Cno char(2) , Grade int)
insert into scc values(1,'c1',60)
insert into scc values(1,'c2',70)
insert into scc values(1,'c3',80)
insert into scc values(1,'c4',90)
insert into scc values(1,'c5',100)
insert into scc values(1,'c6',65)
insert into scc values(2,'c2',65)
insert into scc values(3,'c1',60)
insert into scc values(3,'c2',70)
insert into scc values(3,'c3',80)
insert into scc values(3,'c4',90)
insert into scc values(3,'c5',100)
insert into scc values(3,'c6',65)
insert into scc values(3,'c7',70)
go
select * from stu
select * from cc
select * from scc
go
--要求實(shí)現(xiàn)如下5個(gè)處理：
? --1. 使用標(biāo)準(zhǔn)SQL嵌套語(yǔ)句查詢(xún)選
--修了課程名稱(chēng)為’稅收基礎(chǔ)’的學(xué)員學(xué)號(hào)和姓名
--分析
--1)先查詢(xún)稅收基礎(chǔ)的編號(hào)
--2)在根據(jù)該編號(hào)查詢(xún)出修過(guò)的學(xué)員編號(hào)
--3)在根據(jù)該學(xué)員編號(hào)查詢(xún)到學(xué)員信息
select
from stu where Sno in(
select Sno from scc where Cno = (
select Cno from cc where CName = '稅收基礎(chǔ)'
))
-- 2. 使用標(biāo)準(zhǔn)SQL嵌套語(yǔ)句
--查詢(xún)選修課程編號(hào)為’C2’的學(xué)員姓名和所屬單位
--分析
--1）根據(jù)課程名查詢(xún)到修改c2的學(xué)生編號(hào)
--2)在根據(jù)該學(xué)員編號(hào)查詢(xún)到學(xué)生的對(duì)應(yīng)信息
select SName,SDept from stu where Sno in(
select Sno from scc where Cno = 'C2')
--3. 使用標(biāo)準(zhǔn)SQL嵌套語(yǔ)句
--查詢(xún)不選修課程編號(hào)為’C5’的學(xué)員姓名和所屬單位
--分析
--1）根據(jù)課程查出對(duì)應(yīng)修過(guò)C5的學(xué)生編號(hào)
--2)在根據(jù)該學(xué)生編號(hào) 查詢(xún)沒(méi)有修改過(guò)的學(xué)生
select SName,SDept from stu where Sno != all(
select Sno from scc where cno = 'C5')
-- 4. 使用標(biāo)準(zhǔn)SQL嵌套語(yǔ)句
--查詢(xún)選修全部課程的學(xué)員姓名和所屬單位
--分析
--1）根據(jù) 分組 使用聚合函數(shù)統(tǒng)計(jì)出 每個(gè)學(xué)生的次數(shù)
--2）在根據(jù) 課程統(tǒng)計(jì)出課程的門(mén)數(shù)，然后進(jìn)行對(duì)比查詢(xún)到學(xué)生編號(hào)
--3)在根據(jù)該學(xué)生編號(hào) 獲取對(duì)應(yīng)信息
select SName,SDept from stu where sno in(
select Sno? from scc
group by Sno
having count(*) = (select count(*) from cc))
-- 5. 查詢(xún)選修了課程的學(xué)員人數(shù)
--分析
--1）根據(jù) 去重復(fù)編號(hào) 查詢(xún)出學(xué)過(guò)課程的人數(shù)
select count(distinct sno) from scc
--6. 查詢(xún)選修課程超過(guò)5門(mén)的學(xué)員學(xué)號(hào)和所屬單位
--分析
--1）根據(jù) 分組 使用聚合函數(shù)統(tǒng)計(jì)出 每個(gè)學(xué)生的次數(shù)
--2）在根據(jù) 課程統(tǒng)計(jì)出課程的門(mén)數(shù)，然后進(jìn)行對(duì)比查詢(xún)到學(xué)生編號(hào)
--3)在根據(jù)該學(xué)生編號(hào) 獲取對(duì)應(yīng)信息
select SName,SDept from stu where sno in(
select Sno? from scc
group by Sno
having count(*) >5)
