数据库实验七

--l  针对bookshop数据库，完成以下操作：

--1、  查找和“周小梅”来自同一部门且工资比“周小梅”高的员工的姓名、性别、职称、薪水、住址。

select emp_name 姓名, sex 性别,title 职称,salary 薪水,addr 住址

from employee

where emp_name in 

(

select emp_name

from employee

where dept in(select dept from employee where emp_name='周小梅')

intersect 

select emp_name

from employee

where salary > (select salary from employee where emp_name='周小梅')

)



--2、  查找各个部门薪资最高的员工信息。提示：需要用到分组查询。



select *

from employee a

where salary >=(

select max(salary)

from employee b

group by dept

having a.dept=b.dept

);





--3、  查询在每张订单中订购金额超过240元的客户名及其收货地址。



-- 只需查询超过订单金额240的客户信息；



select distinct cust_name 客户名,cust_addr 收货地址

from customer

where cust_name in(

select cust_name

from sales

where total_amt>240

)



--侧重每一客户的每一笔订单的金额都得超过240

select cust_name 客户名,cust_addr 收货地址

from customer

where cust_name in(

select cust_name

from sales

where total_amt>240  and 

total_amt in 

(select min(total_amt) from sales group by cust_name ))





--4、  每位客户订购的每种图书的总数量及平均单价，并按客户名，图书号从小到大排列。



select cust_name 客户名,book_no 图书号,sum(qty) 总数量, sum(qty*unit_price)/sum(qty) 平均单价

from sale_item a,sales b

where a.order_no=b.order_no

group by book_no,cust_name

order by cust_name,book_no;





--5、  查找在2015年中有订购记录的客户名称，电话和订单总额。

select a.cust_name 客户名称 ,tel_no 电话, sum(total_amt) 订单总额

from sales a,customer b

where a.cust_name=b.cust_name

group by a.cust_name,tel_no;



--l  针对S_T数据库，完成以下操作：



--1、  列出所有课程被选修的详细情况，包括课程号、课程名、学号、姓名及成绩。



select sc.cno 课程号,course.cname 课程名,student.sno 学号, sname 姓名,grade 成绩

from student,sc,course

where sc.sno=student.sno and  sc.cno=course.cno

order by sc.cno,student.sname asc;



--2、  查询名字中第2个字为‘明’的学生姓名和学号及选修的课程号、课程名。

select sname 学生姓名,sc.sno 学号,sc.cno 选修的课程号, course.cname 课程名

from student,sc,course

where sc.sno=student.sno and sc.cno=course.cno and student.sno in(

select sno

from student 

where sname like '_明%')

--3、  列出选修了‘数学’或者‘大学英语’的学生学号、姓名、所在院系、选修课程号及成绩。

select  distinct a.sno 学生学号,sname 姓名,sdept 所在院系,cno 选修课程号,grade 成绩

from student a ,sc b

where a.sno=b.sno and a.sno in(

select sno 

from sc

where cno in (select cno from course where cname='数学'))

or b.sno in(

select sno 

from sc

where cno in(select cno from course where cname='大学英语'));



--4、  查询选修了以‘DB_’开头,且倒数第3个字符为‘s’的课程的学生的详细信息。

select *

from student 

where sno in 

(

select sno 

from sc 

where cno in

(

select cno

from course

where cname like 'DB\_%s__' escape '\')

);



--5、  找出既选修了“数据库”又选修了“信息系统”课程的学生姓名。

select sname

from student

where sno in

(

select sno

from sc

where cno in (select cno from course where cname='数据库')

)and sno in (select sno

from sc

where cno in (select cno from course where cname='信息系统'));