10-145 查詢S001學(xué)生選修而S003學(xué)生未選修的課程（MSSQL）

-- 在S001選修的課程中，但S003未選的課程
select cno 課程號(hào)
from sc
where sno='S001'
and cno not in (select cno        -- 1.S003選修的課程from scwhere sno='S003'
)

10-146 檢索出 sc表中至少選修了’C001’與’C002’課程的學(xué)生學(xué)號(hào)

select sno 學(xué)號(hào)
from sc
where cno in('C001','C002')  -- 1.至少選C001、C002
group by sno
having count(cno) >=2        -- 2.至少選修兩門

10-147 查詢平均分高于60分的課程（MSSQL）

select cou.cno 課程號(hào),cname 課程名
from cou,sc
where cou.cno=sc.cno
group by cou.cno,cname
having avg(grade)>60-- 避免 ambiguous 錯(cuò)誤

10-148 檢索C002號(hào)課程的成績最高的二人學(xué)號(hào)，姓名與成績（——建議二刷）

select top 2sc.sno,sname,grade
from sc,stu
where cno='C002'
and sc.sno=stu.sno
order by grade desc-- limit num 不行就使用 top num
-- 建議二刷

10-149 統(tǒng)計(jì)每個(gè)民族的學(xué)生總?cè)藬?shù)

select nation 民族,count(*) 總?cè)藬?shù)
from student
group by nation

10-150 統(tǒng)計(jì)每種商品的銷售數(shù)量

select gid 商品編號(hào),sum(quantity) 銷售總數(shù)量
from recorder
group by gid

10-151 將student表中的數(shù)計(jì)學(xué)院的學(xué)生信息插入到stu表中。

insert into stu
select *
from student
where dept = '數(shù)計(jì)學(xué)院'

10-152 按城市所屬的省份統(tǒng)計(jì)省份下所有城市的人口（——建議二刷）

浙江省：杭州，寧波，溫州
江蘇省：蘇州，南京，無錫
請(qǐng)寫sql統(tǒng)計(jì)出浙江省和江蘇省所有人口

select name,sum(population) population
from (select case when name in('杭州','寧波','溫州') then '浙江' when name in('蘇州','南京','無錫') then '江蘇'end name,populationfrom city
) temp
group by name-- 派生表查詢 + case when 條件 then value
--                  end 取別名

10-153 查詢各廠商生產(chǎn)的PC的最高價(jià)格

select maker,max(price) max_price
from product,pc
where product.model=pc.model
and type='個(gè)人電腦'
group by maker

10-154 查詢至少生產(chǎn)三種不同速度PC的廠商（——建議二刷）

-- 至少生產(chǎn) 三種不同速度PC的 廠商select maker
from (select distinct maker,speed   -- 1.一定要去重from pc,productwhere pc.model=product.modeland type='個(gè)人電腦'
) temp
group by maker
having count(speed)>=3            -- 2.為了使用count()函數(shù)

10-155 查詢生產(chǎn)三種不同型號(hào)的PC的廠商

-- select maker
-- from product
-- where type='個(gè)人電腦'
-- group by maker
-- having count(model) >= 3select maker
from product
where model in (select modelfrom pc
)
group by maker
having count(model) >= 3

10-156 查詢速度低于任何PC的便攜式電腦

-- 速度 低于任何PC 的便攜式電腦
-- laptop速度比最小的pc的速度還小
select model
from laptop
where speed < (select min(speed)from pc
)

10-157 查詢?cè)趦煞N或兩種以上PC機(jī)上出現(xiàn)的硬盤容量

select hd
from pc
group by hd
having count(*) >= 2

10-158 查詢擁有相同速度和內(nèi)存的PC機(jī)的成對(duì)的型號(hào)，輸出結(jié)果屬性名分別為model1,model2（———建議二刷）

-- 擁有 相同速度和內(nèi)存 的PC機(jī) 的成對(duì) 的型號(hào)
select a.model model1,b.model model2
from pc a,pc b
where a.speed=b.speed
and a.ram=b.ram
and a.model < b.model   -- 避免重疊計(jì)算-- ！！！建議二刷

10-159 查詢選修張老師講授所有課程的學(xué)生（——建議二刷）

-- 張老師 所講授的(所有課程) 的學(xué)生-- 查詢張老師所教課程的課程號(hào)
select sname 
from stu
where sno in (select sno             -- 2.查詢滿足條件的人 的學(xué)號(hào)from scwhere cno in (select cno         -- 1.張老師所教課程 的課程號(hào)from couwhere teacher='張老師')group by sno           -- 3.通過學(xué)號(hào)分組having count(cno) = (  -- 4.每個(gè)人選課數(shù)量select count(*)    -- 2.張老師所教課程 的課程號(hào) 的數(shù)量from couwhere teacher='張老師')
)-- 多表嵌套查詢
-- 建議二刷

10-160 計(jì)算每位同學(xué)獲得的總學(xué)分，并將所有學(xué)生的總學(xué)分按學(xué)號(hào)升序排序后一起插入到totalcredit表中（——建議二刷）

注意：
1）當(dāng)某門課程成績?cè)?0分以上時(shí)才能合計(jì)計(jì)入總學(xué)分
2）如果某學(xué)生尚未選修任何課程時(shí)，總學(xué)分計(jì)為0，并插入到totalcredit表中

-- 當(dāng)某門課程成績?cè)?0分以上時(shí) 才能合計(jì)計(jì)入總學(xué)分
-- case when grade>=60 then credit
--      else 0 end 取別名insert into totalcredit
select sno,sum(credit)
from (select stu.sno,case when grade>=60 then creditelse 0 end creditfrom stuleft join sc on stu.sno=sc.snoleft join cou on sc.cno=cou.cno
) temp
group by sno 
order by sno asc-- 多表連接
-- 左連接 stu 表
-- 建議二刷

10-161 檢索出學(xué)生‘張三’選修的所有及格的課程及成績，最后計(jì)算他所獲得的總學(xué)分。輸出成績結(jié)果集按課程號(hào)升序排序（——建議二刷）

注意：選課成績?cè)?0分以上才能獲得相應(yīng)的學(xué)分。cou表中credit列為某課程的學(xué)分值 。假定學(xué)生姓名沒有重名的情

-- select 
--     cno 課程號(hào),
--     cname 課程名,
--     grade 成績,
--     credit 學(xué)分
-- from -- 方法一
-- select 
--     cou.cno 課程號(hào),
--     cname 課程名,
--     grade 成績,
--     credit 學(xué)分
-- from cou
-- join (
--     select cno,grade   -- 查詢張三選修的課程和成績
--     from sc
--     where grade > 60
--     and sno in (
--         select sno-- 1.查詢張三的學(xué)號(hào)
--         from stu
--         where sname = '張三'
--     )
-- ) temp
-- on cou.cno=temp.cno-- union
-- select 
--     stu.sname,
--     '所有及格課程',
--     '合計(jì)總學(xué)分',
--     sum(credit)
-- from stu,cou,sc
-- where stu.sno=sc.sno
-- and cou.cno=sc.cno
-- and stu.sname='張三'
-- and grade > 60-- order by 課程號(hào)-- -- 子查詢、派生表查詢、union
-- -- order by 不能寫在union之前-- 方法二
select cou.cno 課程號(hào),cname 課程名,grade 成績,credit 學(xué)分
from stu,cou,sc
where stu.sno=sc.sno
and cou.cno=sc.cno
and grade >= 60
and sname='張三'unionselect sname,'所有及格課程','合計(jì)總學(xué)分',sum(credit) 學(xué)分
from stu,cou,sc
where stu.sno=sc.sno
and cou.cno=sc.cno
and grade >= 60
and sname='張三'
-- order by cou.cno -- err
-- -- Table 'cou' from one of the SELECTs cannot be used in field list
order by 課程號(hào)-- 多表等值連接
-- 建議二刷

10-162 查詢平均成績最高的前3名同學(xué)的學(xué)號(hào)，姓名，性別及年齡。假設(shè)當(dāng)前為2020年,年齡僅按年計(jì)算.

selecttemp.sno 學(xué)號(hào),sname 姓名,sex 性別,2020 - year(birdate) 年齡,平均成績
from stu
join ( select sno,avg(grade) 平均成績  -- 1.from scgroup by sno
) temp 
on stu.sno=temp.sno
order by temp.平均成績 desc
limit 3-- 1.在sc表中查詢各個(gè)同學(xué)的平均成績
-- 2.多表連接stu

10-163 顯示已修數(shù)據(jù)庫的同學(xué)信息，包括學(xué)號(hào)、姓名、班級(jí)名稱

-- select SId,SName,GName
-- from select temp.SId,temp.SName,grade.GName
from grade
join (select SId,GId,SNamefrom studentwhere SId in (select SId        -- 2.查詢選數(shù)據(jù)庫學(xué)生的學(xué)號(hào)from scwhere CId = (select CId    -- 1.查詢數(shù)據(jù)庫的課程序號(hào)CIdfrom coursewhere CName='數(shù)據(jù)庫'))
) temp
on grade.GId=temp.GId-- 派生表查詢

10-164 請(qǐng)?jiān)O(shè)計(jì)一個(gè)視圖V_average_point，計(jì)算學(xué)生平均績點(diǎn)（——建議二刷）

成績轉(zhuǎn)換成績點(diǎn)規(guī)則

create view V_average_point as
select Sdept,temp.Sno,avg(point) Average_point
from (select Sdept,SC.Sno,case when Grade>=60 then (Grade-50)/10when Grade<60 then 0end pointfrom SC,Student,Coursewhere SC.Sno=Student.Snoand SC.Cno=Course.Cno
) temp
group by temp.Sno-- 先多表連接查詢學(xué)生的績點(diǎn)
-- 使用派生表查詢
-- 建議二刷！！！

10-165 建立’天津’的供應(yīng)商視圖vSup（創(chuàng)建視圖）

create view vSup as
select *
from supplier
where City = '天津'

10-166 刪除沒有銷售過的產(chǎn)品

delete
from product
where Pid not in (select Pid    -- 1.查詢有銷售記錄的商品from orders
)

10-167 查詢’A01’倉庫中的職工中比’A02’任意一個(gè)職工薪水少的職工編號(hào)與姓名

select Eid,EName
from employee
where Salary < any(select Salaryfrom employeewhere Wno = 'A02'
)
and Wno = 'A01'-- any函數(shù)
-- 查詢比任意一個(gè)員工薪水少的人

10-168 創(chuàng)建一個(gè)每種貨物的銷售數(shù)量的視圖good_total，要求是在2010年04月01日到2010年7月31日之間銷售的貨品，字段包括（gid,total）（創(chuàng)建視圖）

create view good_total as
select gid,sum(quantity) total
from sale_recorder
where sale_date >= '2010-04-01' 
and sale_date <= '2010-07-31'
group by gid-- create view good_total as
-- select gid,sum(quantity) total
-- from sale_recorder
-- where sale_date between '2010-04-01' 
-- and '2010-07-31'
-- group by gid-- 創(chuàng)建視圖
-- create view 視圖名 as

10-169 檢索李玉敏選修的課程編號(hào)及成績

select cno,grade
from student
join score on student.sno=score.sno
where sname='李玉敏'