資料庫實驗三（單表查詢）

標籤：des   ar   資料   ad   as   資料庫   har   table   c   

實驗三:

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）尋找沒有先修課的課程情況

資料庫實驗三（單表查詢）