数据库第一次作业

数据库第一次作业

3.1 使用大学模式，用SQL写出如下查询。

a.找出Comp.Sci系开设的具有三个学分的课程名称。

    select title
      from course
     where dept_name = 'Comp. Sci.' and course.credits = 3;

b. 找出名叫Einstein的教师所教的所有学生的标识，保证结果中没有重复。

    select distinct takes.id
      from instructor natural join teaches 
           join takes using (course_id,sec_id,semester,year)
     where name = 'Einstein'; 

c. 找出教师的最高工资

    select max(salary) 
      from instructor

d. 找出工资最高的所有教师

    select name 
    from   instructor
    where salary=(select Max(salary) from instructor)

e.找出2009年秋季开设的每个课程段的选课人数

    select course_id, sec_id, count(ID) 
    from   section natural join takes 
    where semester = 'Fall' and year = 2009 
    group by course_id, sec_id

f.从2009年秋季开设的每个课程段中，找出最多的选课人数

    select Max(cnt) 
    from ( 
        select Count(ID) as cnt
        from   section natural join takes 
        where semester = 'Fall' and year = 2009 group by course_id, sec_id 
    )

g.找出在2009年秋季拥有最多选课人数的课程段。

    with Fall2009 as (
        select course_id, sec_id, count(ID) as cnt
        from   section natural join takes 
        where  semester = 'Fall' and year = 2009 
        group  by course_id, sec_id
    )

    select course_id,sec_id
    from   Fall2009
    where cnt  = (select max(cnt) from Fall2009)

转载于:https://www.cnblogs.com/wlxtuacm/p/6493246.html