数据库原理的英文试题，求解

(1) select name，phone from employee where address like '%Tianjing%'

这里用like是把所有address中含有tianjing字段的数据都找出来

(2) select c-name,c-phone from customer,having,employee where employee.employee-id=having.employee-id and customer.cust-no=having.cust-no and employee.employee-id=3312

把customer,having,employee 3张表联起来，并用雇员id是3312的条件来找出其负责客户的名字电话

1.

select 英语.学号

from Score 英语

left join

(

select 学号,课程代码,成绩

from Score where 课程代码='数学课程代码'

) as 数学

on 英语.学号=数学.学号

where 英语.课程代码='英语课程代码' and 英语.成绩>数学.成绩

2

select Student.学号,Student.姓名,AVG(成绩) as 平均成绩

from Score

left join Student on Student.学号 =Score.学号

group by Student.学号,Student.姓名

having AVG(Score.成绩)>30

3

select Student.学号,Student.姓名, ISNULL(选课_成绩.选课数,0) as 选课数,ISNULL(选课_成绩.总成绩,0) as 总成绩

from Student

left join

(

select 学号, COUNT(Score.课程代码) as 选课数,SUM(Score.成绩) as 总成绩

from Score group by 学号

) as 选课_成绩

on Student.学号= 选课_成绩.学号

4

select a.学号,a.姓名 from Student as a

where a.学号 not in(

select distinct(Student.学号) as 学号

from Student

left join Score on Score.课程代码 =

(

select Course.课程代码 from Course

where Course.教师编号 =

(

select Teacher.教师编号 from Teacher

where Teacher.教师姓名='王军'

)

)

)

仅供学习参考

---