一、实验目的
二、实验环境
Win11 SSMS sql server
三、实验内容
实验内容:(1)在实验三创建的大学数据库的基础上使用Transact-SQL语言练习单表查询、连接查询、嵌套查询和集合查询;(2)针对具体的查询问题,分别用多种查询方法实现,并进行比较。
实验要求:(1)熟练掌握Transact-SQL语言查询语句的使用;(2)熟练掌握单表查询和连接查询的操作方法。(3)熟练掌握查询中的分组、统计、计算和集合的操作方法。
实验报告书写要求: (1) 按照实验报告模板填写实验报告;(2) 按照教材验证第三章实例,写清楚每个查询、更新实例及其所在的小节,将Transact-SQL语言命令的运行结果截图;(3)验证第三章课后练习 Practice Exercises 3.1, 3.2, 3.3, 3.5, 3.6,将Transact-SQL语言命令的运行结果截图 。
提交截止时间11月5日晚11:50
三、实验步骤
第一部分
-
单表查询
use 大学数据库
select * from class_room
-
连接查询
{自然连接}
insert into student values(01,'xiaoming','school of computer science',100,18545623)
insert into takes values(01,'Java',2501,'second',2,'second')
select student.*,dept_name,sec_id
from student ,takes
-
嵌套连接
select name from student
where dept_name in(
select dept_name from student
where ID=01)
-
集合查询(包括和运算,与运算,差运算)
select name from student
where dept_name in(
select dept_name from student
where ID=01)
except //差运算
select name from student
where student.ID=01
第二部分
3.1 aselect *from course
where dept_name='Comp. Sci.'and credits=3
3.1b
select distinct student.name from student,takes,course,instructor
where instructor.name='Einstein'
3.1c
select max(salary) as maxsalary from instructor
3.1 dselect name,salary as max_salary_teacher from instructor
where salary in(select max(salary) from instructor)
3.1 e
SELECT c.course_id, COUNT(t.course_id) as student_count
FROM takes t
JOIN course c ON t.course_id = c.course_id
WHERE t.year = '2009'and t.semester='Fall'
GROUP BY c.course_id;
3.1 f
SELECT MAX(student_count) as maxstudent_count
FROM (
SELECT COUNT(t.course_id) as student_count
FROM takes t
JOIN course c ON t.course_id = c.course_id
WHERE t.year = '2009' and t.semester = 'Fall'
GROUP BY c.course_id
) AS subquery//子查询
WHERE student_count = (
SELECT MAX(student_count)
FROM (
SELECT COUNT(t.course_id) as student_count
FROM takes t
JOIN course c ON t.course_id = c.course_id
WHERE t.year = '2009' and t.semester = 'Fall'
GROUP BY c.course_id
) AS subquery
)
;
3.1g
SELECT courseID
FROM (
SELECT COUNT(t.course_id) as student_count, t.course_id as courseID
FROM takes t
JOIN course c ON t.course_id = c.course_id
WHERE t.year = '2009' and t.semester = 'Fall'
GROUP BY t.course_id
) AS subquery
WHERE student_count = (
SELECT MAX(student_count)
FROM (
SELECT COUNT(t.course_id) as student_count
FROM takes t
JOIN course c ON t.course_id = c.course_id
WHERE t.year = '2009' and t.semester = 'Fall'
GROUP BY t.course_id
) AS subquery
);
3.3a
update instructor set salary=salary*1.1 where dept_name='Comp. Sci';
select * from instructor
有误。Salary=salary*1.1好像是不对的。
3.3b
delete from course
WHERE course_id NOT IN (SELECT course_id FROM section);
六、实验总结
通过实验练习了很多sql的技巧和功能,进一步理解了里面数据的关系。