實驗三:
select sno,sname
from student;//(1)查詢全體學生的學号和姓名
select *
from student;//(2)查詢全體學生的詳細記錄
select sname,sage,sdept
from student where sdept='MA';//(3)查詢軟體學院的學生姓名、年齡、系别
select distinct sno
from sc;//(4)查詢所有選修過課程的學生學号(不重複)
select distinct sno
from sc
where grade<60;//(5)查詢考試不及格的學生學号(不重複)
select ssex,sage,sdept
from student
where sdept not in('CS','MA');//(6)查詢不是軟體學院、計算機系的學生性别、年齡、系别
select sno,sname,sdept,sage
from student
where sage>=18 and sage<=20;//(7)查詢年齡18－20歲的學生學号、姓名、系别、年齡；
select *
from student
where sname like '劉%';//(8)查詢姓劉的學生情況
select *
from student
where sname like '劉%'or sname like '李%';//(9)查詢姓劉或姓李的學生情況
select *
from student
where sname like '劉_';//(10)查詢姓劉且名字為兩個字的學生情況
select sname
from student
where sage<31;//(11)查詢1983年以後出生的學生姓名。
create table studentgrade
(
sno char(8) PRimary key,
mathgrade tinyint,
englishgrade tinyint,
chinesegrade tinyint
)
go
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)
insert into studentgrade(sno,mathgrade,englishgrade,chinesegrade) values( '95006',81,97,70)
insert into studentgrade(sno,mathgrade,englishgrade,chinesegrade) values( '95007',85,95,70)
select sum(mathgrade+englishgrade+chinesegrade) as sumgrades
from studentgrade group by sno;(12)建立表 studentgrade(sno,mathgrade,englishigrade,chinesegrade)
計算學生各科總成績并賦予别名
select (year(getdate())-student.sage+1)
from student
where sdept='MA';//(13)利用内部函數 year()查找軟體學院學生的出生年份
select sname + '年齡為'+cast(sage as char(2))+'歲'
from student;
Select sname + ‘年齡為’+cast(sage as char(2))+’歲’
From student//(14)利用字元轉換函數實作字元聯接。
select*
from student order by sdept,sage desc;//(15)查詢全體學生情況，查詢結果按所在系升序排列，對同一系中的學生按年齡降序排列。
select count(*)
from student;//查詢總人數
select count(distinct sno)
from sc;//查詢選修了課程的學生人數
select count(*),avg(grade)as avggrade
from student ,sc
where student.sno=sc.sno and sc.cno='1';//(18)查詢選修了7号課程的學生總人數和平均成績
select max(grade) as maxgrade
from sc
where cno='2';//(19)查詢選修6号課程學生的最好成績
select sdept,count(*)
from student group by sdept;//(20)查詢每個系的系名及學生人數。
select cno,count(*),avg(grade) as avggrade
from sc group by cno;//(21)查找每門課的選修人數及平均成績
select *
from course
where cpno is null;//(22)查找沒有先修課的課程情況