create table Student(sid varchar(10),sname varchar(10),sage datetime,ssex char(2));

insert into Student values('01' , '趙雷' , '1990-01-01' , '男');

insert into Student values('02' , '錢電' , '1990-12-21' , '男');

insert into Student values('03' , '孫風(fēng)' , '1990-05-20' , '男');

insert into Student values('04' , '李云' , '1990-08-06' , '男');

insert into Student values('05' , '周梅' , '1991-12-01' , '女');

insert into Student values('06' , '吳蘭' , '1992-03-01' , '女');

insert into Student values('07' , '鄭竹' , '1989-07-01' , '女');

insert into Student values('08' , '王菊' , '1990-01-20' , '女');

create table Course(cid varchar(10),cname varchar(10),tid varchar(10));

insert into Course values('01' , '語(yǔ)文' , '02');

insert into Course values('02' , '數(shù)學(xué)' , '01');

insert into Course values('03' , '英語(yǔ)' , '03');

create table Teacher(tid varchar(10),tname varchar(10));

insert into Teacher values('01' , '張三');

insert into Teacher values('02' , '李四');

insert into Teacher values('03' , '王五');

create table SC(sid varchar(10),cid varchar(10),score int);

insert into SC values('01' , '01' , 80);

insert into SC values('01' , '02' , 90);

insert into SC values('01' , '03' , 99);

insert into SC values('02' , '01' , 70);

insert into SC values('02' , '02' , 60);

insert into SC values('02' , '03' , 80);

insert into SC values('03' , '01' , 80);

insert into SC values('03' , '02' , 80);

insert into SC values('03' , '03' , 80);

insert into SC values('04' , '01' , 50);

insert into SC values('04' , '02' , 30);

insert into SC values('04' , '03' , 20);

insert into SC values('05' , '01' , 76);

insert into SC values('05' , '02' , 87);

insert into SC values('06' , '01' , 31);

insert into SC values('06' , '03' , 34);

insert into SC values('07' , '02' , 89);

insert into SC values('07' , '03' , 98);

--drop table SC

select * from SC

select * from Student

select * from Teacher

2，3，4，6，13，16，20，28，29，30，31，47，49（簡(jiǎn)單題）

【我做了1-20，28，29，30，31，47-50共28題，以后有空再做別的題】

1.查詢“某1”（比如01）課程比“某2”（比如02）課程成績(jī)高的所有學(xué)生的學(xué)號(hào)；

--【因?yàn)檫@里的有些表的內(nèi)容不完整，所以最好不要用inner。

--同時(shí)兩表查詢查詢的列名要明確Student.sid，不然會(huì)報(bào)錯(cuò)。這里沒(méi)必要用外鍵，用也可以。

--下面的語(yǔ)法也是可以用來(lái)多表查詢。

--對(duì)于復(fù)雜的SQL的子查詢的題目，就是要?jiǎng)?chuàng)建視圖】

【下面的語(yǔ)句要一條一條地執(zhí)行】

--1

create view view1 as (select Student.sid,cid, SC.score from? Student left? ?join? SC? on? ?Student.sid=SC.sid? where? cid? = '01'? group by? Student.sid )

--2

create view view2 as (select? Student.sid,cid, SC.score from? Student left? ?join? SC? on? ?Student.sid=SC.sid? where? cid? = '02' )?

--【個(gè)人的理解和解析：下面的別名可以用英文，也可用中文，可以在視圖查詢中直接當(dāng)列名調(diào)用，有時(shí)不用加單引號(hào)

--創(chuàng)建視圖時(shí)，取得別名就成了視圖的列名，要調(diào)用列名。個(gè)人的思路：創(chuàng)建兩個(gè)視圖，利用inner只會(huì)查出兩張表的特性，創(chuàng)建新的我想要的視圖】

--3

create view view3 as (select view1.sid as '學(xué)生學(xué)號(hào)' ,view1.sid as '課程編號(hào)為01',? view1.score as'課程編號(hào)為01的成績(jī)',

view2.sid as '課程編號(hào)為02',? view2.score as'課程編號(hào)為02的成績(jī)'

? from view1 inner? join view2 on? view1.sid =view2.sid)

--【下面的別名可以用英文，也可用中文，可以在視圖查詢中直接當(dāng)列名調(diào)用，有時(shí)不用加單引號(hào)。select 學(xué)生學(xué)號(hào)? from view3，創(chuàng)建視圖時(shí)，取得別名就成了視圖的列名，要調(diào)用列名。】

--4

select 學(xué)生學(xué)號(hào) ,課程編號(hào)為01的成績(jī),課程編號(hào)為02的成績(jī) from view3? where? 課程編號(hào)為01的成績(jī)>課程編號(hào)為02的成績(jī)

--【或簡(jiǎn)寫(xiě)成select 學(xué)生學(xué)號(hào)? from view3? where? 課程編號(hào)為01的成績(jī)>課程編號(hào)為02的成績(jī)

或?qū)懗蓅elect *? from view3? where? 課程編號(hào)為01的成績(jī)>課程編號(hào)為02的成績(jī)，更加直觀，具體全面，方便檢查對(duì)錯(cuò)】

2.查詢平均成績(jī)大于60分的同學(xué)的學(xué)號(hào)和平均成績(jī)；

select? Student.sid as '學(xué)號(hào)',sname as '姓名' ,avg(score) as '平均成績(jī)'? from Student? left? join? SC? on??

?Student.sid=SC.sid? ?group by sname? having? avg(score)>60

3.查詢所有同學(xué)的學(xué)號(hào)、姓名、選課數(shù)、總成績(jī)

select Student.sid as ' 學(xué)號(hào)',sname as '姓名' ,count(cid) as '選課數(shù)',sum(score) as '總成績(jī)'??

from Student? left? join? SC? on? ?Student.sid=SC.sid? ?group by sname??

--【因?yàn)橛幸幻瑢W(xué)沒(méi)參加考試所以Student.sid不可以寫(xiě)成SC.sid，不然會(huì)查出Null的內(nèi)容】

--【用上inner和下面的語(yǔ)句的話，只會(huì)查出參加考試的。有時(shí)要用查詢單表的語(yǔ)句來(lái)檢查對(duì)錯(cuò)等。select SC.sid as ' 學(xué)號(hào)',sname as '姓名' ,count(cid) as '選課數(shù)',sum(score) as '總成績(jī)'??

--from Student? , SC where? ? Student.sid=SC.sid? ?group by sname? 】

--select * from Student

4.查詢姓“李”的老師的個(gè)數(shù)；

select count(tname) as '姓“李”的老師的個(gè)數(shù)'

from? Teacher where tname like '李%'

--select * from Teacher

5.查詢沒(méi)學(xué)過(guò)“張三”老師課的同學(xué)的學(xué)號(hào)、姓名；

--create? view viewZ? as

【下面是嘗試的過(guò)程，有些語(yǔ)句有用，有些對(duì)，有些錯(cuò)?？傊畤L試后基本都容易離正確，最優(yōu)等更進(jìn)一步，可以獲得很多有用的東西和內(nèi)容。

select distinct Student.sid as ' 學(xué)號(hào)',sname as '姓名' ,tname

where Student.sid != (

select distinct Student.sid as ' 學(xué)號(hào)',sname as '姓名' ,tname

from Student? left? join? SC? on? ?Student.sid=SC.sid? left

join Course? on Course.cid=SC.cid left? join Teacher? on Teacher.tid=Course.tid?

where tname='張三')? and select distinct Student.sid as ' 學(xué)號(hào)',sname as '姓名' ,concat(tname) from Student? left? join? SC? on? ?Student.sid=SC.sid? left

join Course? on Course.cid=SC.cid left? join Teacher? on Teacher.tid=Course.tid? group by sname】

【個(gè)人的總結(jié)：要取反的范圍查詢（即XXX不在YYY的情況），一般都會(huì)用 where? 列名? not? in(SQL多表查詢語(yǔ)句等)】

select Student.sid as ' 學(xué)號(hào)',sname as '姓名' from Student?

where sid not in?

(

select distinct Student.sid?

from Student? left? join? SC? on? ?Student.sid=SC.sid? left

join Course? on Course.cid=SC.cid left? join Teacher? on Teacher.tid=Course.tid?

where tname='張三'

)

--select viewZ.姓名 , count(viewZ.tname)? from? viewZ? group by 學(xué)號(hào)

--create? view viewZS? as

--select distinct Student.sid as ' 學(xué)號(hào)',sname as '姓名' ,tname

--from Student? left? join? SC? on? ?Student.sid=SC.sid? left

--join Course? on Course.cid=SC.cid left? join Teacher? on Teacher.tid=Course.tid

--select viewNZS.學(xué)號(hào),viewNZS.姓名 ,viewNZS.tname ,viewNZS.tname from viewNZS right? join viewZS on viewNZS.學(xué)號(hào)=viewZS.學(xué)號(hào)

6.查詢學(xué)過(guò)“語(yǔ)文”并且也學(xué)過(guò)編號(hào)“數(shù)學(xué)”課程的同學(xué)的學(xué)號(hào)、姓名；

【select *? from Student? left? join? SC? on? ?Student.sid=SC.sid? ? left

join Course? on Course.cid=SC.cid left? join Teacher? on Teacher.tid=Course.tid

--為四表查詢，功能多其常用，更廣用，所以創(chuàng)建為視圖 view4Table，會(huì)少很多的代碼，

--但最好重命名列名等，復(fù)制列名重復(fù)。但一般來(lái)說(shuō)還時(shí)建有當(dāng)前要用的列名的視圖

】

--drop view view1;

--drop view view2;

--drop view view3

--【- 下面的語(yǔ)句會(huì)報(bào)的錯(cuò)：Duplicate column name 'sid',重復(fù)列名“sid”】

--create? view view4Table? as (select? distinct *? from Student? left? join? SC? on? ?Student.sid=SC.sid? ?left

--join Course? on Course.cid=SC.cid left? join Teacher? on Teacher.tid=Course.tid)

--1

create? view viewYuWen? as

select Student.sid as ' 學(xué)號(hào)',sname as '姓名'?

from Student? left? join? SC? on? ?Student.sid=SC.sid? left

join Course? on Course.cid=SC.cid left? join Teacher? on Teacher.tid=Course.tid

where? cname='語(yǔ)文'

--2

create? view viewMath? as

select Student.sid as ' 學(xué)號(hào)',sname as '姓名'?

from Student? left? join? SC? on? ?Student.sid=SC.sid? left

join Course? on Course.cid=SC.cid left? join Teacher? on Teacher.tid=Course.tid

where? cname='數(shù)學(xué)'

--3

select viewMath.學(xué)號(hào) as '學(xué)號(hào)',viewMath.姓名 as'姓名'? ?from viewYuWen? inner? ?join viewMath on viewMath.學(xué)號(hào)=viewYuWen.學(xué)號(hào)

7.查詢學(xué)過(guò)“張三”老師所教的所有課的同學(xué)的學(xué)號(hào)、姓名；

【個(gè)人的總結(jié)：SQL語(yǔ)句的查詢等最重要的一點(diǎn)等就是分析數(shù)據(jù)的規(guī)律等

，簡(jiǎn)單規(guī)律的數(shù)據(jù)，有時(shí)可以用很簡(jiǎn)單的語(yǔ)句，但有時(shí)最好是些更加通用更多種情況，

考慮情況等可能更全面的語(yǔ)句。這里數(shù)據(jù)特殊（一門老師教一們）所以只要寫(xiě)

select Student.sid as ' 學(xué)號(hào)',sname as '姓名'?

from Student? left? join? SC? on? ?Student.sid=SC.sid? left

join Course? on Course.cid=SC.cid left? join Teacher? on Teacher.tid=Course.tid

where? tname='張三'?

就可以了】

--1

create view viewTC? as

select cname,Teacher.tname as '老師名字'

from Student? inner? join? SC? on? ?Student.sid=SC.sid? inner

join Course? on Course.cid=SC.cid inner? join Teacher? on Teacher.tid=Course.tid? group by cname

--2

【下面重命名的cname（可改為'張三教過(guò)的所有課的名字'）為張三教過(guò)的所有課的名字】

create view viewTAll? as

select? cname as? cname? ?from? viewTC? where? viewTC.老師名字='張三'

--3

select Student.sid as ' 學(xué)號(hào)',sname as '姓名'? ?

from Student? left? join? SC? on? ?Student.sid=SC.sid? left

join Course? on Course.cid=SC.cid left? join Teacher? on Teacher.tid=Course.tid? where? cname in(select? ?cname? from viewTAll)

8.查詢課程編號(hào)“01”的成績(jī)比課程編號(hào)“02”課程低的所有同學(xué)的學(xué)號(hào)、姓名；

【下面的視圖之前創(chuàng)建了就直接調(diào)用視圖，沒(méi)有就創(chuàng)建】

【這里的每個(gè)老師只教一們課】

【個(gè)人總結(jié)：有時(shí)多表查詢的子查詢太復(fù)雜時(shí)要用視圖才有用，不報(bào)錯(cuò)和簡(jiǎn)單。查出為空的可能原因是數(shù)據(jù)的成績(jī)被之前的

Update語(yǔ)句改成一樣了。這里可以刪除表，建表，插入語(yǔ)句，來(lái)檢驗(yàn)一下】

--1

create view view1 as (select Student.sid,cid, SC.score from? Student left? ?join? SC? on? ?Student.sid=SC.sid? where? cid? = '01'? group by? Student.sid )

--2

create view view2 as (select? Student.sid,cid, SC.score from? Student left? ?join? SC? on? ?Student.sid=SC.sid? where? cid? = '02' )?

--3

create view view3 as (select view1.sid as '學(xué)生學(xué)號(hào)' ,view1.sid as '課程編號(hào)為01',? view1.score as'課程編號(hào)為01的成績(jī)',

view2.sid as '課程編號(hào)為02',? view2.score as'課程編號(hào)為02的成績(jī)'

? from view1 inner? join view2 on? view1.sid =view2.sid)

--【下面的別名可以用英文，也可用中文，可以在視圖查詢中直接當(dāng)列名調(diào)用，有時(shí)不用加單引號(hào)。

--select 學(xué)生學(xué)號(hào)? from view3，創(chuàng)建視圖時(shí)，取得別名就成了視圖的列名，要調(diào)用列名。

--】

--4

select 學(xué)生學(xué)號(hào) ,課程編號(hào)為01的成績(jī),課程編號(hào)為02的成績(jī) from view3? where? 課程編號(hào)為01的成績(jī)<課程編號(hào)為02的成績(jī)

9.查詢所有課程成績(jī)小于60分的同學(xué)的學(xué)號(hào)、姓名；

【所有課程成績(jī)小于60分等價(jià)于所有課程成績(jī)平均分小于60分,用視圖可以少寫(xiě)很多代碼，

并且邏輯更明確清晰等】

【create? view viewSCavg? as

select? Student.sid as '學(xué)號(hào)',sname as '姓名' ,avg(score) as '平均成績(jī)'? from Student? left? join? SC? on??

?Student.sid=SC.sid? ?group by sname? 】

select Student.sid as ' 學(xué)號(hào)',sname as '姓名' , avg(score)?

from Student? left? join? SC? on? ?Student.sid=SC.sid? left

join Course? on Course.cid=SC.cid left? join Teacher? on Teacher.tid=Course.tid??

? group by sname?

having? avg(score) <60

10.查詢沒(méi)有學(xué)全所有課的同學(xué)的學(xué)號(hào)、姓名；

【自己的轉(zhuǎn)換的思路：每個(gè)同學(xué)上過(guò)的課程數(shù)<所有課的數(shù)目】

【很多時(shí)候要看成一個(gè)整體，更便于更深程度的思考】

--【drop view viewSCOne】

--1

create? view viewSCOne? as

(select Student.sid as ' 學(xué)號(hào)',sname as '姓名' , count(cname) as '每個(gè)同學(xué)上過(guò)的課程數(shù)' from Student? left? join? SC? on? ?Student.sid=SC.sid? left

join Course? on Course.cid=SC.cid left? join Teacher? on Teacher.tid=Course.tid??

group by sname)

--【drop view viewSCAll

--create? view viewSCAll? as

--(select count(cname) as '所有課的數(shù)目' from Course )】

--2

select? 學(xué)號(hào),姓名 as '姓名'? , 每個(gè)同學(xué)上過(guò)的課程數(shù) from viewSCOne?

where? ?每個(gè)同學(xué)上過(guò)的課程數(shù)? ?<(select count(cname) as '所有課的數(shù)目' from Course )

11.查詢至少有一門課與學(xué)號(hào)為“01”的同學(xué)所學(xué)相同的同學(xué)的學(xué)號(hào)和姓名；

【select? ?Student.sid,cname from Student? left? join? SC? on? ?Student.sid=SC.sid? left

join Course? on Course.cid=SC.cid left? join Teacher? on Teacher.tid=Course.tid??

where? Student.sid=01】

【個(gè)人總結(jié)：having后面的列名要在select 后面出現(xiàn)】

——————

select distinct Student.sid as ' 學(xué)號(hào)',sname as '姓名' ,cname as '其中的相同的一門課' from Student? left? join? SC? on? ?Student.sid=SC.sid? left

join Course? on Course.cid=SC.cid group by Student.sid

having 其中的相同的一門課? in (select cname from Student? left? join? SC? on? ?Student.sid=SC.sid? left

join Course? on Course.cid=SC.cid left? join Teacher? on Teacher.tid=Course.tid??

where? Student.sid=01)

12.查詢和"01"號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的學(xué)號(hào)和姓名

?【自己的思路：先用group by sid

分出所有的sid，之后用

having count(*)=(select count(*) from SC where sid='01') and sid != '01'

判斷出除了sid為01，并且課程數(shù)目和sid為01的課程數(shù)目一致的sid。

之后用select sid from SC where cid not in (select cid from SC where sid='01')

判斷出不包括sid為01的課程cid的id。

之后又用一個(gè)select sid from SC where sid not in，雙重否定為肯定，

綜上select Student.sid as ' 學(xué)號(hào)',sname as '姓名' from Student where sid in?

得到課程數(shù)目一致，并且cid有相同的sid，加上數(shù)據(jù)的特殊性，每個(gè)人同一門不能報(bào)兩次，

最終得到的就是和"01"號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的學(xué)號(hào)和姓名。

這其實(shí)是一個(gè)邏輯題，理解后，找到可以確定范圍的限制條件就很容易，可以類比地去做?！?/p>

select Student.sid as ' 學(xué)號(hào)',sname as '姓名' from Student where sid in?

(select sid from SC where sid not in

(select sid from SC where cid not in (select cid from SC where sid='01'))

group by sid?

having count(*)=(select count(*) from SC where sid='01') and sid != '01');

? --select? concat(cname)? from? viewTT? ?group? by sname

--select? ?Student.sid as ' 學(xué)號(hào)',sname as '姓名',

【

下面是我嘗試過(guò)的目前不可行的方案：

select distinct Student.sid as ' 學(xué)號(hào)',sname ,cname?

from Student? left? join? SC? on? ?Student.sid=SC.sid? left

join Course? on Course.cid=SC.cid group by Student.sid 】

? 【create? view viewALLSC as

select cname? from Student? left? join? SC? on? ?Student.sid=SC.sid? left

join Course? on Course.cid=SC.cid group by Student.sid

having? cname? ?not in? (select cname from Student? left? join? SC? on? ?Student.sid=SC.sid? left

join Course? on Course.cid=SC.cid left? join Teacher? on Teacher.tid=Course.tid??

where? Student.sid=01)

select distinct Student.sid as ' 學(xué)號(hào)',sname ,cname?

from Student? left? join? SC? on? ?Student.sid=SC.sid? left

join Course? on Course.cid=SC.cid group by Student.sid??

having cname not? in( select? cname? from

viewALLSC)】

13.把“SC”表中“張三”老師教的課的成績(jī)都更改為此課程的平均成績(jī)；

--1

create? view? ?viewSC? as

select avg(score)? as? score? from Student? left? join? SC? on? ?Student.sid=SC.sid? left

join Course? on Course.cid=SC.cid left? join Teacher? on Teacher.tid=Course.tid where tname ='張三'

--2

create view? viewT as

select? distinct? Course.cid? as cid? from Student? left? join? SC? on? ?Student.sid=SC.sid? left

join Course? on Course.cid=SC.cid left? join Teacher? on Teacher.tid=Course.tid where tname ='張三'

--3

update SC set? score? = (select score from viewSC)

where? ?SC.cid=(select cid from? viewT)

14、查詢沒(méi)學(xué)過(guò)"張三"老師講授的任一門課程的學(xué)生姓名

select Student.sid as ' 學(xué)號(hào)',sname as '姓名' , count(cname) ,tname from Student? left? join? SC? on? ?Student.sid=SC.sid? left

join Course? on Course.cid=SC.cid left? join Teacher? on Teacher.tid=Course.tid??

group by sname? having? tname!='張三' or count(cname)=0

15、查詢兩門及其以上不及格課程的同學(xué)的學(xué)號(hào)，姓名及其平均成績(jī)?

select Student.sid, Student.sname, AVG(SC.score) from Student,SC

where?

? ? ?student.sid = SC.sid and SC.score<60

group by SC.sid

having count(*)>=2;

【

我對(duì)

having count(*)>=2;的翻譯：前面的多表表的查詢出的不及格的課程數(shù)為2，或大于2】

16、檢索"01"課程分?jǐn)?shù)小于60，按分?jǐn)?shù)降序排列的學(xué)生信息

select * from Student,SC,Course

where?

? ? ?student.sid = SC.sid? and SC.score<60??

group by SC.sid

having cid='01'? order by score desc ;

17、按平均成績(jī)從高到低顯示所有學(xué)生的所有課程的成績(jī)以及每個(gè)學(xué)生的所有課程的平均成績(jī)

【個(gè)人的總結(jié)：having后面跟著的列名等，select的后面等，基本也要有等。

想要把創(chuàng)建的兩個(gè)視圖連起來(lái)，最好是兩個(gè)視圖的列名和數(shù)量一樣。

】

--1

create view avgViewA? as

select Student.sid as '學(xué)號(hào)',sname as '姓名' ,cname,score, avg(score) as? ?'avg'? ?from Student left join SC on?

Student.sid=SC.sid? left join Course? on Course.cid=SC.cid? ?group by? ?Student.sid

--2

create view avgCA? as

select? Student.sid as '學(xué)號(hào)',sname as '姓名' ,cname ,score, avg(score) as? ?'avg'? ?from Student left join SC on?

Student.sid=SC.sid? left join Course? on Course.cid=SC.cid? ?group by score

--3

select avgCA.學(xué)號(hào) as '學(xué)號(hào)',avgCA.姓名 as '姓名' ,avgCA.cname ,avgCA.score, avgViewA.avg? from? avgCA? ?left? ?join? avgViewA? on? avgCA.學(xué)號(hào)=avgViewA.學(xué)號(hào)??

order by? avgViewA.avg desc

18.查詢各科成績(jī)最高分、最低分和平均分：以如下形式顯示：課程ID，課程name，最高分，最低分，平均分，及格率(scor>=60)，

中等率(score>=80)，優(yōu)良率(score>=90)，優(yōu)秀率(score>=95)

——————

--及格率(score>=60)部分：

create? view viewJG? as

select cname,score? ?from? ?Course left join SC? on? SC.cid=Course.cid where score>=60

--select * from ViewJG

--【select cname, count(cname) ,score from Student? left? join? SC? on? ?Student.sid=SC.sid? left

--join Course? on Course.cid=SC.cid left? join Teacher? on Teacher.tid=Course.tid??

?--group by score? having score>=60】

? ?--drop view viewYJG?

create? view viewYJG? as

select? cname,count(cname)? as c? from viewJG? ? group by cname

create? view viewCJG as

select cname,score ,count(cname) as c? from? ?Course? left join SC? on? SC.cid=Course.cid group by cname

--drop? view? viewJGL

create? view? viewJGL? as

(select? ? ?viewYJG .cname as? cname , viewYJG.c/viewCJG.c? ?as'各科及格率'? ??

from? viewYJG? ?left join viewCJG on? viewCJG.cname=viewYJG.cname)

——————

--中等率(score>=80)：

create? view viewJG2? as

select cname,score? ?from? ?Course left join SC? on? SC.cid=Course.cid where score>=80

create? view viewYJG2? as

select? cname,count(cname)? as c? from viewJG2? ? group by cname

create? view viewCJG2 as

select cname,score ,count(cname) as c? from? ?Course? left join SC? on? SC.cid=Course.cid group by cname

create? view? viewJGL2? as

(select? ? ?viewYJG2 .cname as? cname , viewYJG2.c/viewCJG2.c? ?as'各科中等率'? ??

from? viewYJG2? ?left join viewCJG2 on? viewCJG2.cname=viewYJG2.cname)

--優(yōu)良率(score>=90)：

create? view viewJG3? as

select cname,score? ?from? ?Course left join SC? on? SC.cid=Course.cid where score>=90

? ?--drop view viewYJG?

create? view viewYJG3? as

select? cname,count(cname)? as c? from viewJG3? ? group by cname

create? view viewCJG3 as

select cname,score ,count(cname) as c? from? ?Course? left join SC? on? SC.cid=Course.cid group by cname

--drop? view? viewJGL3

create? view? viewJGL3? as

(select? ? ?viewYJG3 .cname as? cname , viewYJG3.c/viewCJG3.c? ?as'各科優(yōu)良率'? ??

from? viewYJG3? ?left join viewCJG3 on? viewCJG3.cname=viewYJG3.cname)

?

優(yōu)秀率(score>=95)：

create? view viewJG4? as

select cname,score? ?from? ?Course left join SC? on? SC.cid=Course.cid where score>=95

? ?--drop view viewYJG?

create? view viewYJG4? as

select? cname,count(cname)? as c? from viewJG4? ? group by cname

create? view viewCJG4 as

select cname,score ,count(cname) as c? from? ?Course? left join SC? on? SC.cid=Course.cid group by cname

--drop? view? viewJGL

create? view? viewJGL4? as

(select? ? ?viewYJG4 .cname as? cname , viewYJG4.c/viewCJG4.c? ?as'各科優(yōu)秀率'? ??

from? viewYJG4? ?left join viewCJG4 on? viewCJG4.cname=viewYJG4.cname)

?

--select cname, 各科及格率 from? ?viewJGL

--drop view? viewZB

create? ?view? viewZB? as?

select? Course.cid,cname,max(score),min(score),avg(score)

?from Student? inner? join? SC? on? ?Student.sid=SC.sid? inner

join Course? on Course.cid=SC.cid inner? join Teacher? on Teacher.tid=Course.tid??

group by Course.cid

--【查不出的表示沒(méi)有達(dá)標(biāo)的科目】

select? ? *? from viewZB? ?left? ?join viewJGL? on? ?viewJGL.cname=viewZB .cname

left? ?join viewJGL2 on? ?viewJGL2.cname=viewZB .cname

left? ?join viewJGL3? on? ?viewJGL3.cname=viewZB .cname

left? ?join viewJGL4? on? ?viewJGL.cname=viewZB .cname

19.按各科平均成績(jī)從低到高和及格率的百分?jǐn)?shù)從高到低順序

--1按各科平均成績(jī)從低到高:

select? cname,avg(score)? from Student? inner? join SC on SC.sid=Student.sid

inner

join Course? on Course.cid=SC.cid? group? by? cname? order? ?by avg(score)??

--2及格率的百分?jǐn)?shù)從高到低順序:

create? view viewJG7? as

select cname,score? ?from? ?Course left join SC? on? SC.cid=Course.cid where score>=60

--

? ?--drop view viewYJG?

create? view viewYJG7? as

select? cname,count(cname)? as c? from viewJG? ? group by cname

--

create? view viewCJG7 as

select cname,score ,count(cname) as c? from? ?Course? left join SC? on? SC.cid=Course.cid group by cname

--drop? view? viewJGL

create? view? viewJGL7? as

(select? ? ?viewYJG7 .cname as? cname , viewYJG7.c/viewCJG7.c? ?as'各科及格率'? ??

from? viewYJG7? ?left join viewCJG7 on? viewCJG7.cname=viewYJG7.cname)? ?order? ?by 各科及格率? desc

--

select? * from? viewJGL7

20、查詢學(xué)生的總成績(jī)并進(jìn)行排名

select? sname,sum(score)? as'總成績(jī)' from Student? left? join SC on SC.sid=Student.sid? group? by? sname? order? ?by sum(score)? desc

21、查詢不同老師所教不同課程平均分從高到低顯示?

22、查詢所有課程的成績(jī)第2名到第3名的學(xué)生信息及該課程成績(jī)

24、查詢學(xué)生平均成績(jī)及其名次?

25、查詢各科成績(jī)前三名的記錄

26.查詢每門課程被選修的學(xué)生數(shù)

27.查詢出只選修了一門課程的全部學(xué)生的學(xué)號(hào)和姓名

28、查詢男生、女生人數(shù)

select? ssex ,count(sname)? from Student? ? group? by? ssex?

29、查詢名字中含有"風(fēng)"字的學(xué)生信息

select * from Student where sname like'%風(fēng)%'

30、查詢同名同性學(xué)生名單，并統(tǒng)計(jì)同名人數(shù)?

--drop view? viewN

--drop view? viewV

----

create? view? viewN? as

select sname ,count(sname)-1? as? s ,ssex from Student group by sname? ?having? ssex='男'

-----

create? view? viewV? as

select sname ,count(sname)-1? as? s,ssex from Student group by sname? ?having? ssex='女'

----

select? viewV.sname? as '同名同性學(xué)生名單',count( viewV .s ) as '同名人數(shù)'? ?from? viewN? inner? join? ?viewV? on viewV.sname=viewN.sname

31、查詢1990年出生的學(xué)生名單(注：Student表中Sage列的類型是datetime)?

select sname ,Sage from Student? where? Sage like'1990%'

32.查詢每門課程的平均成績(jī)，結(jié)果按平均成績(jī)升序排列，平均成績(jī)相同時(shí)，按課程號(hào)降序排列

37.查詢不及格的課程，并按課程號(hào)從大到小排列?

38.查詢課程編號(hào)為"01"且課程成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)和姓名；

40.查詢選修“張三”老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生姓名及其成績(jī)

42、查詢每門功課成績(jī)最好的前兩名?

43、統(tǒng)計(jì)每門課程的學(xué)生選修人數(shù)（超過(guò)5人的課程才統(tǒng)計(jì)）。要求輸出課程號(hào)和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號(hào)升序排列??

44、檢索至少選修兩門課程的學(xué)生學(xué)號(hào)

45、查詢選修了全部課程的學(xué)生信息?

46、查詢各學(xué)生的年齡

47、查詢本周過(guò)生日的學(xué)生

【YEARWEEK 用法 用于返回該時(shí)間是這一年的第幾周。

DATE_FORMAT 用法 用于以不同的格式顯示日期時(shí)間數(shù)據(jù)】

--select? sname ,Sage,now()? from Student? where? Month(Sage)=Month(now())? and? ? ? Day(Sage)= Day(now())

select

sname

from

Student?

WHERE

YEARWEEK( DATE_FORMAT( sage, '%Y %m %d' ) ) = YEARWEEK( NOW( ) )

48、查詢下周過(guò)生日的學(xué)生

select

sname

from

Student?

WHERE

YEARWEEK( DATE_FORMAT( sage, '%Y %m %d' ) ) = YEARWEEK( NOW( ) )+1

49、查詢本月過(guò)生日的學(xué)生

【MONTH 函數(shù) 用于返回該時(shí)間的月份】

select

sname

from

Student?

WHERE

MONTH( DATE_FORMAT( sage, '%Y %m %d' ) ) = MONTH( NOW( ) )

50、查詢下月過(guò)生日的學(xué)生

SELECT

sname

FROM

student?

WHERE

MONTH( DATE_FORMAT( sage, '%Y %m %d' ) ) = MONTH( NOW( ) )+1

https://www.cnblogs.com/wobu/p/9645203.html