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