实验六 连接查询实验报告

实验六连接查询试验报告

市场营销152 08刘运良1620153968前提：还原数据库sales，student-mis

针对sales数据库

1）查询所有员工的姓名，部门名称，及工资。

select

employee.Employee_Name,employee.Wages,department.Department_Name from employee

inner join department

on employee.Department_ID=department.Department_ID

2）查询公司员工销售记录，并列出员工姓名及客户姓名。

select employee.Employee_Name,customer.Customer_Name

from sell_order

inner join employee

on sell_order.Employee_ID=employee.Employee_ID

inner join customer

on sell_order.Custom_ID=customer.Customer_ID

3）查询所有产品的产品编号，名称，类别名称。

select

goods.Goods_ID,goods.Goods_Name,goods_classification.Classification_N ame

from goods

inner join goods_classification

on goods.Classification_ID=goods_classification.Classification_ID

4）查询所有订单的产品名称，客户姓名，员工姓名及运输公司名称。

select

goods.Goods_Name,customer.Customer_Name,employee.Employee_Name,tra nsporter.Transport_Name

from goods,customer,employee,transporter,sell_order

where employee.Employee_ID=sell_order.Employee_ID

and customer.Customer_ID=sell_order.Custom_ID

and goods.Goods_ID=sell_order.Goods_ID

and transporter.Transporter_ID=sell_order.Transporter_ID

针对student-mis数据库

1）查询出成绩表中不及格学生的学号，姓名，成绩。

select

student_info.Student_ID,student_info.Student_Name,student_course_i nfo.Score

from student_info,student_course_info

where student_course_info.Score<60

and student_course_info.Student_ID=student_info.Student_ID

2）显示所有学生的姓名，选修课程名。

select student_info.Student_Name,course_info.Course_Name

from student_info,course_info,student_course_info

where student_info.Student_ID=student_course_info.Student_ID

and course_info.Course_No=student_course_info.Course_No

3）显示所有选修了计算机文化基础的学生姓名及成绩。

select student_course_info.*,

student_info.Student_Name,student_course_info.Score

from student_info,student_course_info,course_info

where student_info.Student_ID=student_course_info.Student_ID

and student_course_info.Course_No=course_info.Course_No

and course_info.Course_Name='计算机文化基础'

4）找出数据结构课程成绩在90分以上的学生姓名及成绩。

select student_course_info.*,

student_info.Student_Name,student_course_info.Score

from student_info,student_course_info,course_info

where student_course_info.Score>90

and course_info.Course_Name='数据结构'

and course_info.Course_No=student_course_info.Course_No and student_info.Student_ID=student_course_info.Student_ID