数据库第一次作业(第三章)

数据库第一次作业

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)

3.2 假设给你一个关系grade_points(grad_e,points),他提供从takes关系中用字母表示的成绩等级到数字表示的得分之间的转换。例如，“A”等级可指定为对应于4分，“A-”对应于3.7分，“B+”对应于3.3分，“B”对应于3分，等等。学生在某门课程（课程段）上所获取的等级分值被定义为该课程段的学分乘以该生得到的成绩等级所对应的数字表示的得分。

/根据题意创建表/

    /*创建表关系*/
    create table Grade_points
    (
       grade  varchar (2),
       Points  numeric(3, 1)
    );


    /*插入值*/
    insert into grade_points values ('A',4.0);
    insert into grade_points values ('A-',3.7);
    insert into grade_points values ('B+',3.3);
    insert into grade_points values ('B',3.0);
    insert into grade_points values ('B-',2.7);
    insert into grade_points values ('C+',2.3);
    insert into grade_points values ('C',2.0);
    insert into grade_points values ('C-',1.7);

a.根据ID为12345的学生所选修的所有课程，找出该生所获得等级分值的总和。

    select distinct sum(credits * points) 
      from takes natural join course natural join grade_points
     where ID = 12345;

b. 找出上述学生等级分值的平均值(GPA),即用改等级分值的总和除以相关课程学分的总和。

    select distinct sum(credits * points) /sum(credits) AS res
      from takes natural join course natural join grade_points
     where ID = 12345;

c.找出每个学生的ID和等级分值得平均值

    select distinct sum(credits * points) /sum(credits) AS GPA
      from takes natural join course natural join grade_points
     group by ID;

3.3 使用大学模式，用SQL写出如下出入、删除和更新语句。

a.给 Comp.Sci 系老师涨10%的工资。

    update instructor
       set instructor.salary = instructor.salary * 1.10
     where instructor.dept_name = 'Comp. Sci.';

b.删除所有未开设过的课程。

    delete from course
     where course_id not in (select distinct course_id from section);

c.把每一个在tot_cread属性上取值超过100的学生作为同系的教师插入，工资为10000美元。

    insert into instructor
    select ID,name,dept_name,10000
      from student
     where tot_cred >= 100;

3.4 考虑图3-18中的保险公司数据，其中加下划线的是主码，为这个数据库构造出如下SQL查询。

    person( driver_id, name, address)
    ar( license, model, year)
    ccident( report_number, date, location)
    wns (driver_id, license)
    artcipated ( report_number, license, driver_id, damage_amount)

a.找出2009年其车辆出现过交通事故的人员总数。

    select count(distinct driver_id) AS num_of_person
      from person natural join participated natural join accident
     where date between date ’1989-00-00’ and date ’1989-12-31’ 

b.向数据库中增加一个新的事故，对每个必须的属性可以设定任意值。

    insert into accident
    values (1239401,'1991-01-01','Wl');

c.删除“John Smith”拥有的马自达车（Mazda）。

    delete frrm car 
     where Model = 'Mazda' and license in (
           select license 
           from person p, owns o 
           where p.name = ’John Smith’ and p.driver id = o.driver id
     );

3.5 假设有关系marks(ID, score), 我们希望基于如下标准为学生评定等级：如果 score < 40 得 F； 如果 40 <= score < 60 得 C； 如果 60 <= score < 80 得 B；如果 80 <= score 得A。 写出SQL查询完成下列操作。

a.基于marks关系显示每个学生的等级。

    select ID,
           case 
               when score < 40 then 'F'
               when score < 60 then 'C'
               when score < 80 then 'B'
               else 'A'
           end 
               as rank
    from marks;

b.找出各等级的学生数

    select count(ID) as cnt 
    from
    (
        select ID,
               case 
                   when score < 40 then 'F'
                   when score < 60 then 'C'
                   when score < 80 then 'B'
                   else 'A'
               end 
                   as rank
        from marks;
    )group  by rank;

3.6 SQL的like运算符是大小写敏感的，但字符串上的lower()函数可用来实现大小写不敏感的匹配。为了说明是怎么实现的，写出这样一个插叙：找出名称中包含了“sci”子串的系， 忽略大小写。

    select dept_name
      from department
     where lower(dept_name) like '%sci%';

3.7 考虑以下SQL查询在什么条件下这个查询选择的p.al值要么在r1中，要么在r2中？仔细考察r1或r2位空的情况。

    select distinct p.al
      from p,r1,r2
     where p.al = r1.al or p.a1 = r2.al;

解:  当且仅当r1,r2非空时，p.a1要么在r1中要么在r2中。
     当r1或r2空时,r1 X  r2为空。
     当p是空时，显然查询结果是为空的正解

3.8 考虑图3-19中的银行数据库，期中加下划线的是主码。为这个关系数据库构造出如下SQL查询：

银行数据库
branch(branch name, branch city, assets)
customer (customer name, customer street, customer city)
loan (loan number, branch name, amount)
borrower (customer name, loan number)
account (account number, branch name, balance )
depositor (customer name, account number)

a.找出银行中所有有账号但无贷款的客户

    (select customer_name from desositor)
    from 
    (select customer_name from borrower)

b.找出与Smith居住在同一城市、同一接到的所有客户的名字

    select F.customer_name
      from customer F join customer S using (customer_street,customer_city)
     where S.customer_name = 'Smith';

c.找出所有支行的名称，在这些支行中都有居住在Harrison的客户所开的账户

    select branch_name
      from acoount natural join depositor natural join customer
     where branch_city = 'Harrison';

3.9 考虑图3.20的雇员数据库，其中加下划线的是主码。为下面每个查询写出SQL表达式：

    图3.20 雇员数据库
    employee (employee name, street, city)
    works (employee name, company name, salary)
    company (company name, city)
    manages (employee name, manager name)

a.找出所有为First Bank Corporation工作的雇员机器居住城市。

    select employee_name,city
      from employee
     where employee_name in (
           select employee_name 
             from works 
            where company_name = 'First Bank Corporation'
     );

b.找出所有为First Bank Corporation工作且薪金超过10000美元的雇员名字、居住的接到和城市。

    select employee_name,street,city
      from employee natural join works
     where company_name = 'First Bank Corporation' and salary > 10000;

c.找出数据库中所有不为First Bank Corporation工作的雇员。

    select employee_name
      from works
     where employee_name not in (
           select employee_name
             from works
            where company_name = 'First Bank Corporation'
           );

d.找出数据库中所有工资不高于Small Bank Corporation

    select employee_name
      from works
     where salary > (
           select max(salary)
             from works
            where company_name = 'Small Bank Corporation'
           );

e. 假设一个公司可以在好几个城市有分部。找出位于Small Bank Corporation所在城市的所有公司。

    select C.company_name
      from company C join company P using (city) 
     where P.company_name = 'Small Bank Corporation';

f.找出雇员最多的公司

    select company_name
      from works
     group by company_name
    having count (distinct employee_name) >=
                all(  select count (distinct  Employee_name)
                        from works
                       group by company_name);l

g.找出平均工资高于First Bank Corporation的那些公司。

    select company_name
      from works
     group by company_name
    having avg(salary) > (
           select avg(salary)
             from works
            where company_name = 'First Bank Name');

3.10 考虑图3.20的关系数据库，给出下面每个查询的sql表达式：

a. 修改数据库使John现在居住在Newton。

    update employee 
       set city = 'Newtown'
     where employee_name = 'Johns';

b. 为First Bank Corporation所有工资不超过100000美元的经理增长10%的工资，对工资超过100000美元的只增长3%。

    update works
       set salary = 
           case
               when salary * 1.10 <= 100000 then salary * 1.10
               else salary * 1.03
           end
     where company_name = 'First Bank Corporation' and employee_name in (select employee_name from managers);

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

a.找出至少选修了一门Comp. Sci. 课程的学生姓名，保证结果中没有重复的姓名

    select distinct student.name
      from student natural join takes join course using (course_id)
     where course.dept_name = 'Comp. Sci.';

b.找出所有没有选修在2009年春季之前开设的任何课程的学生的ID和姓名。

    select distinct ID,name
      from student
     where id not in((select distinct ID
                        from takes
                       where year < 2009 or(year = 2009 and semester ='Spring')));

c.找出每个系教师的最高工资。可以假设每个系至少有一位老师。

    select dept_name,max(salary)
      from instructor
     group by dept_name;

d.从前述查询所计算出的每个系最高工资中选出最低值。

    select min(MAX_SALARY)
      from(select dept_name,max(salary) as MAX_SALARY
             from instructor
            group by dept_name);

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

a.创建按一门课程CS- 001，其名称为Weekly Seminar，学分为0。

    insert into course values
                ('CS-001',
                 'Weekly Seminar',
                 '',
                 0);

b.创建该课程在2009年秋季的一个课程段，sec_id 为1。

    insert into section
         values ('CS - 001',
                 1,
                 'Fall',
                 2009,
                 NULL,
                 NULL,
                 NULL);

c.让Comp. Sic.系的每一个学生都选修上述课程段。

    insert into takes 
           select ID, 
                  'CS - 001',
                  1,
                  'Fall',
                  2009,
                  NULL
             from student
            where Dept_name = 'Comp. Sci.';

d. 删除名为Chavez的学生选修上述课程段的信息。

    delete from takes 
     where course_id = 'CS-001'
       and sec_id = 1
       and year = 2009
       and semester = 'Fall'
       and ID in(
               select ID 
                 from student 
                where name = 'Chavez'
                );

e.删除课程CS - 001如果在运行此删除语句之前，没有先删除这门课程的授课信息(课程段)，会发生什么事情。

    delete from course
     where course_id = 'CS-001';
    如果在删除语句之前，没有先删除课程段，则其对应课程段和学生的选课信息一起被删。因为在建表的DDL语句中加入了级联删除的设置。

f.删除课程名称中包含datebase的任意课程的任意课程段所对应的所有take元组，在课程名的匹配中忽略大小写。

    delete from takes
     where course_id not in (select course_id
                               from course
                              where lower(title) like '%database%');

3.13写出图3-18中模式的SQL DDL。在数据类型上做合理的假设，确保申明主码和外码。

    create table person
    (
         driver_id   varchar(50),
         name        varchar(50),
         address     varchar(50),
         primary key(driver_id)
    );

    create table car
    (
         license     varchar(50),
         model       varchar(50),
         year        numeric(5,0),
         primary key (license)
    );

    create table accident
    (
         report_number varchar(10),
         dateion       date,
         loaction      varchar(50);
         primary key (report_number);
    );

    create table owns
    (
         driver_id   varchar(50),
         license     varchar(50),
         primary key(driver_id),
         foreign key (driver_id) references person,
         foreign key (license) references car
    );

    create table participated
    (
         report_num    varchar(10),
         license       varchar(50),
         driver_id     varchar(50),
         damage_amount numeric(10,0),
         primary key (report_number,license),
         foreign key (report_number) references accident,
         foreign key (license) references car,
         foreign key (driver_id) references owns
    );

3.14考虑图3-18中的保险公司数据库，其中加下划线的是主码。对这个关系数据库构造如下的SQL查询。

a.找出和John Smith的车有关的交通事故数量。

    select count(report_number)
      from participated
     where license in (select license 
                         from person natural join owns
                        where name = 'John Smith');

b.对事故报告编号为AR2197中的车牌是AABB2000的车辆损坏保险费用更新到3000美元。

    update participated
       set damage_amount
     where report_number = 'AR2197' and license = 'AABB2000';

3.15 考虑图3-19中的银行数据库，其中加下划线的是主码。为这个关系数据库构造出如下的SQL查询。

a.找出在Brooklyn的所有支行都有账户的所有客户。

    with branchcount as
    (
         select count(*)
           from Branch
           where branch_city = 'Brooklyn'
    )
    select custumer_name 
      from customer c
     where branchcount = (select count(distinct branch_name)
                            from (customer natural join depositor natural join account natural join branch) as d
                           where d.customer_name = c.customer_name);

b.找出银行的所有贷款额的总和。

    select sum(amount) 
      from loan;

c.找出总资产至少比位于Brooklyn的所有支行的某一家支行要多的所欲支行的名字

    select branch_name
      from branch
     where assets > some(select assets
                           from branch
                          where branch_city = 'Brooklyn');

3.16 考虑图3-20中的雇员数据库，其中加下划线的是主码。给出下面每个查询对应的SQL查询式。

a.找出所有为First Bank Corporation工作的雇员的名字。

    select employee_name
      from works
     where company_name = 'First Bank Corporation':

b.找出数据库中所有居住城市和公司所在城市相同的雇员。

    select employee_name
      from employee E join works using (employee_name) join company C using (company_name)
     where E.city = C.city;

c.找出数据库中所有居住城市和接到与其经理相同的雇员。

select P.employee_name
  from employee P,employee R,managers M
 where P.employee_name = M.employee_name
   and R.employee_name = M.manger_name
   and P.street = R.street
   and P.city = R.city;

d. 找出工资高于其所在公司雇员平均水平的所以雇员。

    with avg_salary
         as (select company_name,avg(salary) as val
               from works
              group by company_name)
    select employee_name 
      from works natural join avg_salary
     where salary > val;

e.找出工资总和最小的公司。

    select  company_name
      from  works
     group  by company_name
     having sum(salary) = (select Min(Sum(salary))
                             from works 
                            group by company_name);

3.17 考虑图3-20中的关系数据库。给出下面每个查询对应的SQL表达式。

a.为First Bank Corporation的所有雇员增长10%的工资。

    update works
       set salary = salary * 1.10;
     where company_name = 'First Bank Corporation';

b.为First Bank Corporation的所有经理增长10%的工资。

    update works
       set salary = salary * 1.10;
     where company_name = 'First Bank Corporation' and employee_name in (select manager_name from managers);l

c.删除Small Bank Corporation的雇员在Works关系中的所有元组。

    delete from works
     where company_name = 'First Bank Corporation';

3.21 考虑图3-21中的图书馆数据库。用SQL写出如下查询。

member(memb no, name, age)
book(isbn, title, authors, publisher)
borrowed(memb no, isbn, date)

a.打印借阅了任意由McGraw-Hill出版的书的会员的名字。

    select distinct name
      from member natural join book natural join borrowed
     where publisher = 'McGraw-Hill';

b.打印借阅了所有由McGraw-Hill出版的书的会员的名字。

    select name 
      from member 
     where memb_no in (select memb_no 
                         from borrowed 
                        group by memb_no 
                        having count(*) = (select count(*) 
                        from book 
                        where publisher='McGraw-Hill'));

c.对于每个出版商，打印借阅了多余五本由该出版社出版的书的会员名字。

    select name,publisher
      from member natural join borrowed natural join book
     group by name,publisher
     having count(isbn) > 5;

d. 打印每一位会员借阅书籍数量的平均值。考虑这样的情况：如果某会员没有借阅任何书籍，那么该会员根本不会出现在borrowed关系中。

    select(select count(*) from borrowed)/ (select count(*)from member)

3.23考虑以下查询，解释为什么在from子句中还加上与section的连接不会改变查询结果。

select course id, semester, year, section id, avg (credits earned)
from takes natural join student
where year = 2009
group by course id, semester, year, section id
having count (ID) >= 2;

解：takes 和 section 是通过一些共同的外键相连系，每一个takes的某一个元组不会因为增加额外的元祖。

3.24考虑以下查询，不使用with结构，重写此查询。

with dept total (dept name, value) as
(select dept name, sum(salary)
from instructor
group by dept name),
dept total avg(value) as
(select avg(value)
from dept total)
select dept name
from dept total, dept total avg
where dept total.value >= dept total avg.value;

解:答案如下:
    select dept_name
     from (select dept name, sum(salary)
             from instructor
            group by dept name) P 
     where P.val >= (select avg(sum(salary))
                       from instructor
                      group by dept_name);

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