--1.查詢全體學生的學號和姓名select sno,sname from student
--2.查詢全體學生的詳細記錄select * from student
--3.查詢軟體學院的學生姓名，年齡，系別select sname,sage,sdept from studentwhere sdept='CS'
--4.查詢所有選修過課程的學生學號（不重複）select distinct sno from sc
--5.查詢考試不及格的學生學號（不重複）select distinct sno from scwhere grade<60
--6.查詢不是軟體學院、計算機系的學生性別、年齡、系別 select ssex,sage,sdeptfrom student where sdept not in('CS','IS')
--7.查詢年齡18－20歲的學生學號、姓名、系別、年齡；select sno,sname,sdept,sage from studentwhere sage between 18 and 20/*select sno,sname,sdept,sage from studentwhere sage>=18 and sage<=20;*/
--8.查詢姓劉的學生情況select * from studentwhere sname like '劉%'
--9.查詢姓劉或姓李的學生情況select * from studentwhere sname like '劉%'or sname like '李%' --多字元，單字元通配
--10.查詢姓劉且名字為兩個字的學生情況select * from studentwhere sname like '劉_'
--11.查詢1983年以後出生的學生姓名select sname,sage from studentwhere sage<getdate()-1983
--getdate()獲取系統當前時間
--12.建立表 studentgrad(sno,mathgrade,englishigrade,chinesegrade)
計算學生各科總成績並賦予別名create table studentgrade(sno char(8) primary key,mathgrade tinyint,englishgrade tinyint,chinesegrade tinyint)insert into studentgrade(sno,mathgrade,englishgrade,chinesegrade) values( '95001',85,95,74)insert into studentgrade(sno,mathgrade,englishgrade,chinesegrade) values( '95002',86,91,70)insert into studentgrade(sno,mathgrade,englishgrade,chinesegrade) values( '95003',80,92,71)insert into studentgrade(sno,mathgrade,englishgrade,chinesegrade) values( '95004',81,91,75)insert into studentgrade(sno,mathgrade,englishgrade,chinesegrade) values( '95005',87,97,78)select sno,sum(mathgrade+englishgrade+chinesegrade) as sumgradesfrom studentgrade group by sno
--13.利用內部函式 year()查詢軟體學院學生的出生年份select sname,(year(getdate())-student.sage )from student where sdept='CS'
--14.利用字元轉換函式實現字元聯接select sname + '年齡為'+cast(sage as char(2))+'歲'
--字元轉換函式cast(),sage後必須要加上as 字元型from student
--15.學生情況，查詢結果按所在系升序排列，對同一系中的學生按年齡降序排列select * from student order by sdept ,sage DESC --order by asc升序 desc降序
--16.查詢學生總人數select count(*) from student
--17.查詢選修了課程的學生人數select count(distinct sno) from sc
--18.查詢選修了1號課程的學生總人數和平均成績select count(sno),avg(grade)as avggrade from scwhere cno=1/*select count(*),avg(grade)as avggrade from student ,sc where student.sno=sc.sno and sc.cno='1'*/ --two
--19.查詢選修2號課程學生的最好成績select max(grade)as maxgrade from scwhere cno=2
--20.查詢每個系的系名及學生人數select sdept,count(*) from student group by sdept/*select sdept,count(sno) from student group by sdept*/ --two--
21.查詢每門課的選修人數及平均成績select cno,count(*)as '選修人數',avg(grade)as avggrade from scgroup by cno
--22.查詢沒有先修課的課程情況select * from course where cpno is null
————————————————
版權宣告：本文為CSDN博主「Black博士」的原創文章，遵循CC 4.0 BY-SA版權協議，轉載請附上原文出處連結及本宣告。
原文連結：https://blog.csdn.net/qq_42294230/article/details/80426142