东北大学数据库实验报告2016

实验(二): 熟练掌握SQL语言

insert into Students values ('20133901','赵一',21,'计算机') insert into Students values ('95133902','赵二',18,'计算机') insert into Students values ('20133903','赵三',23,'通信') insert into Students values ('20133904','赵四',24,'数学') insert into Students values ('95133905','赵五',25,'软件') insert into Students values ('20133906','赵六',26,'数学') insert into Students values ('20133907','赵七',27,'物联网') insert into Students values ('95133908','赵八',28,'数学') insert into Students values ('20133909','老九',19,'计算机') insert into Students values ('95133910','老十',17,'计算机') insert into Students values ('95133941','老一',17,'物联网') insert into Students values ('95133943','老二',15,'物联网')

insert into Courses values ('0000','C1',4,60,null) insert into Courses values ('0001','C语言',4,60,null) insert into Courses values ('0002','C++',4,60,null) insert into Courses values ('0003','JAVA',4,60,null) insert into Courses values ('0004','数据结构',4,60,null) insert into Courses values ('0005','高数',4,60,null) insert into Courses values ('0006','离散',4,60,null) insert into Courses values ('0007','数据库',4,60,'0005') insert into Courses values ('0008','算法设计',4,60,null) insert into Courses values ('0009','文本智能处理',4,60,null) insert into Courses values ('0010','信息安全',4,60,'0009') insert into Courses values ('0011','C2',4,60,'null')

insert into SC values ('95133910','0000',70) insert into SC values ('95133910','0001',1) insert into SC values ('20133941','0001',2) insert into SC values ('95133902','0001',3) insert into SC values ('20133903','0001',4) insert into SC values ('20133904','0003',3) insert into SC values ('95133905','0001',2) insert into SC values ('20133906','0001',1) insert into SC values ('20133907','0001',2) insert into SC values ('95133908','0001',82) insert into SC values ('20133909','0000',90) insert into SC values ('20133909','0002',3) insert into SC values ('95133901','0011',3) insert into SC values ('95133901','0002',3) insert into SC values ('95133903','0000',3)

insert into SC values ('95133903','0001',3) insert into SC values ('95133903','0002',3) insert into SC values ('95133903','0003',3) insert into SC values ('95133903','0004',3) insert into SC values ('95133903','0005',3) insert into SC values ('95133843','0006',3) insert into SC values ('95133903','0007',3) insert into SC values ('95133903','0008',3) insert into SC values ('95133903','0009',3) insert into SC values ('95133903','0010',3) insert into SC values ('95133903','0011',3) 用SQL完成下列查询

1. 求选修了课程的学生的学号，要求：

(1) 不使用distinct语句，消除重复元组，写出其SQL语句

Select Sno from SC

(2) 使用distinct语句，消除重复元组，写出其SQL语句 select distinct Sno from SC

(3) 如果该查询写成：

select Students.Sno from Students, SC where Students.Sno=SC.Sno 请问该查询结果和上面哪个结果是相同的？

和1相同

2. 求开设的课程号、课程名、学时和学分，要求对查询结果的列重新命名。 select Cno MyCno ,Cname MyCname,Ctime MyCtime,CCredit MyCredict from Courses

3. 求计算机系和数学系的学生学号、姓名和年龄。

select Sno,Sname,Sage from Students where Sdept='计算机' or Sdept ='数学'