drop database mytest
create database MyTest
use Mytest
--1、問題描述：
--已知關(guān)系模式：
--S (SNO,SNAME）學(xué)生關(guān)系捺弦。SNO 為學(xué)號括袒，SNAME 為姓名
--C (CNO,CNAME,CTEACHER)? 課程關(guān)系壁肋。CNO 為課程號变逃，CNAME 為課程名振愿，CTEACHER 為任課教師
--SC(SNO,CNO,SCGRADE)? ? ? ? 選課關(guān)系侍咱。SCGRADE 為成績
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語言','李明')
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.找出沒有選修過“李明”老師講授
--課程的所有學(xué)生姓名
--分析
--1)先查詢出李明老師的編號
--2)在查詢出學(xué)過李明老師的學(xué)生編號
--3)在查詢出沒有學(xué)過李明老師的學(xué)生
select sname from s where sno not in(
select sno from sc where cno in(
select cno from c where cteacher = '李明'))
--2.列出有二門以上（含兩門）
--不及格課程的學(xué)生姓名及其平均成績
--分析
--1)先查詢出兩門不及格課程以上的學(xué)生編號
--2)在做連接查詢
--3)在根據(jù)學(xué)生編號查詢出學(xué)生姓名用avg聚合函數(shù)統(tǒng)計出平均成績 使用group by 進行分組
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é)過“1”號課程柠并，又學(xué)過“2”號課程
--的所有學(xué)生姓名
--分析
--1)先根據(jù) 課程編號1 和課程編號2查詢學(xué)過1號和2號的學(xué)生編號
--2)在根據(jù) 學(xué)生編號查詢學(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”號課成績比
--“2”號同學(xué)該門課成績高的所有學(xué)生的學(xué)號
--分析
--1)先查詢出2號同學(xué)的該門的學(xué)生成績
--2)2號同學(xué)的成績對 所有學(xué)生的一號成績對比進行篩選
select * from sc where cno = 1 and scgrade >(
select scgrade from sc where sno = 101and cno = 1)
--5.列出“1”號課成績比“2”號課成績
--高的所有學(xué)生的學(xué)號? ?
--及其“1”號課和“2”號課的成績
--分析
--1)先查詢出同一名“2”號課成績
--2)在查詢出一號成績進行對比
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岭接、問題描述：
--為管理崗位業(yè)務(wù)培訓(xùn)信息富拗，建立3個表:
--S (Sno,SName,SDept,SAge)? S#,SN,SD,SA 分別代表學(xué)號、學(xué)員姓名鸣戴、所屬單位啃沪、學(xué)員年齡
--C (Cno,CName )? ? ? ? C#,CN? ? ? 分別代表課程編號、課程名稱
--SC ( Sno,Cno,Grade )? ? S#,C#,G? ? 分別代表學(xué)號葵擎、所選修的課程編號谅阿、學(xué)習成績
-- 學(xué)生信息表
--字段分別代表:學(xué)號、學(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
--課程信息表
--字段分別代表:課程編號、課程名稱
create table cc(Cno char(2), CName varchar(20))
insert into cc values('c1','計算機原理')
insert into cc values('c2','經(jīng)濟學(xué)原理')
insert into cc values('c3','稅收基礎(chǔ)')
insert into cc values('c4','C語言基礎(chǔ)')
insert into cc values('c5','中國刑法')
insert into cc values('c6','心理學(xué)')
insert into cc values('c7','會計學(xué)')
go
--選課信息表
--字段分別代表代表學(xué)號盯串、所選修的課程編號氯檐、學(xué)習成績
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
--要求實現(xiàn)如下5個處理：
? --1. 使用標準SQL嵌套語句查詢選
--修了課程名稱為’稅收基礎(chǔ)’的學(xué)員學(xué)號和姓名
--分析
--1)先查詢稅收基礎(chǔ)的編號
--2)在根據(jù)該編號查詢出修過的學(xué)員編號
--3)在根據(jù)該學(xué)員編號查詢到學(xué)員信息
select
from stu where Sno in(
select Sno from scc where Cno = (
select Cno from cc where CName = '稅收基礎(chǔ)'
))
-- 2. 使用標準SQL嵌套語句
--查詢選修課程編號為’C2’的學(xué)員姓名和所屬單位
--分析
--1）根據(jù)課程名查詢到修改c2的學(xué)生編號
--2)在根據(jù)該學(xué)員編號查詢到學(xué)生的對應(yīng)信息
select SName,SDept from stu where Sno in(
select Sno from scc where Cno = 'C2')
--3. 使用標準SQL嵌套語句
--查詢不選修課程編號為’C5’的學(xué)員姓名和所屬單位
--分析
--1）根據(jù)課程查出對應(yīng)修過C5的學(xué)生編號
--2)在根據(jù)該學(xué)生編號 查詢沒有修改過的學(xué)生
select SName,SDept from stu where Sno != all(
select Sno from scc where cno = 'C5')
-- 4. 使用標準SQL嵌套語句
--查詢選修全部課程的學(xué)員姓名和所屬單位
--分析
--1）根據(jù) 分組 使用聚合函數(shù)統(tǒng)計出 每個學(xué)生的次數(shù)
--2）在根據(jù) 課程統(tǒng)計出課程的門數(shù)，然后進行對比查詢到學(xué)生編號
--3)在根據(jù)該學(xué)生編號 獲取對應(yīng)信息
select SName,SDept from stu where sno in(
select Sno? from scc
group by Sno
having count(*) = (select count(*) from cc))
-- 5. 查詢選修了課程的學(xué)員人數(shù)
--分析
--1）根據(jù) 去重復(fù)編號 查詢出學(xué)過課程的人數(shù)
select count(distinct sno) from scc
--6. 查詢選修課程超過5門的學(xué)員學(xué)號和所屬單位
--分析
--1）根據(jù) 分組 使用聚合函數(shù)統(tǒng)計出 每個學(xué)生的次數(shù)
--2）在根據(jù) 課程統(tǒng)計出課程的門數(shù)体捏，然后進行對比查詢到學(xué)生編號
--3)在根據(jù)該學(xué)生編號 獲取對應(yīng)信息
select SName,SDept from stu where sno in(
select Sno? from scc
group by Sno
having count(*) >5)
