sql数据库系统原理上机实验(综合版)(3)

create unique index student1_index on student1(sname desc) drop index student1_index on student1

create table teacher (tno char(4) primary key, tname char(8) not null, title char(5) , )

drop table teacher

--sql查询实验二的代码

SELECT sno,sname from student

select * ------------2 from student

select sname,sage,sdept -----3

--1 11

from student

where student.sdept='IS'

select distinct student.sno -----4 from student,sc

where sc.sno=student.sno

select distinct student.sno ----5 from student,sc

where sc.sno=student.sno and grade<60

select ssex,sage,sdept -----------6 from student where sno not in (select sno from student where sdept='IS')

/* 这是exists的用法 select ssex,sage,sdept from student as y where not exists(select *

12

from student where Y.sdept='CS') */

select sno,sname,sage,sdept -------7 from student

where sage between 18 and 20;

select * ---------------8 from student

where sname like'刘%'

select * -----------------9 from student

where sname like'[刘李]%'

select sname ----------------10 from student

where sname like'刘_ _'--中文占用两个字符，所以这里用两个

select sname -------------------11 from student

_ 13

where 2013-sage>1983

-- select year(sage) 这是取单个人的出生年份的用法，调用year函数

select year(getdate())-s.sage+1

from student as s -------12 where sdept='CS'

Select sname + '年龄为'+cast(sage as char(2))+'岁' From student ---------13 select *

from student ---------------------14 order by sdept,sage desc

select COUNT(sno)----------------15 from student

select COUNT(distinct sno) from sc --------------16 /*

14

SELECT COUNT(*)

FROM student ----------16题解法二

WHERE EXISTS( SELECT * FROM SC WHERE sc.sno = student.sno ) */

select COUNT(student.sno),avg(sc.grade) from student,sc ------------17 where student.sno=sc.sno and cno='7'

select max(grade)

from sc -------------------18 where cno='6'

select sdept,COUNT(sno) from student -------------------19 group by sdept

select COUNT(student.sno),AVG(grade) from student,sc ----------20 where student.sno=sc.sno group by cno

15

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库sql数据库系统原理上机实验(综合版)(3)在线全文阅读。