TUR02 tutorial_02

3.11> Write the following queries in SQL, using the university schema.

a. Find the names of all students who have taken at least one Comp. Sci. course; make sure there are no duplicate names in the reuslt.

select distinct name
from student natural join takes natural join course
where dept_name = ‘Comp. Sci.’;

b. Find the IDs and names of all studnets who have not taken any course offering before Spring 2009.

(select ID, name
from student natural join takes)
minus
(select ID, name
from student natural join takes
where year < 2009
);

c. For each department, find the maximum salary of instructors in that department. You may assume that every departmenet has at least one instructor

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

d. Find the lowest, across all departments, of the per-department maxium salary computed by the preceding query.

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

3.12> Write the following queries in SQL, using the university schema.

a. Create a new course “CS-001”, titled “Weekly Seminar”, with 0 credits

insert into course(course_id, title, dept_name, credits) values (‘CS-001’, ‘Weekly Seminar’, ‘Comp. Sci.’, 0);
— insert into course(course_id, title, dept_name, credits) values(‘CS-001’, ‘Weekly Seminar’, ‘Comp. Sci.’, 1);

b. Create a section of this course in Fall 2009, with sec_id of 1.

insert into section(course_id, sec_id, semester, year) values (‘CS-001’, ‘1’, ‘Fall’, 2009);

c. Enroll every student in the Comp. Sci. department in the above section.

insert into takes(ID, course_id, sec_id, semester, year)
select ID, ‘CS-001’, ‘1’, ‘Fall’, 2009
from student
where dept_name = ‘Comp. Sci.’;

d. Delete enrollments in the above section where the student’s name is Chavez

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

e. Delete the course CS-001. What will happen if you run this delete statement without first deleting offerings(sections) of this course?

delete from course
where course_id = ‘CS-001’;
/
no error
section has foreign key, course_id, but that foreign key has ‘on delete cascade’ constraints.
so when deleting CS-001 from course which is referenced by section, tuples in section that have course_id as ‘CS-001’ will be automatically deleted.
/

f. Delete all takes tuples corresponding to any section of any course with the word “database” as a part of the title; ignore case when matching the word with the title

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

3.14> Consider the insurance database of Figure 3.18, where the primary keys are underlined. Construct the following SQL queries for this relational database.

/*
    person(_driver_id_, name, address)
    car(_license_, model, year)
    accident(_report_number_, date, location)
    owns(_driver_id_, _license_)
    participated(_report_number_, _license_, driver_id, damage_amount)
*/

a. Find the number of accidents in which the cars belonging to “John Smith” were involved

select count(report_number)
from accident natural join participated
where driver_id in (
select driver_id
from person
where name = ‘John Smith’
);

b. Update the damage amount for the car with the license number ‘AABB2000’in the accident with report number ‘AR2197’ to $3000

update participated
set damage_amount = 3000
where (report_number = ‘AR2197’) and (license = ‘AABB2000’)

3.16> Consider the employee database of Figure 3.20, where the primar keys are underlined. Give and expression in SQL for each of the following queries.

/*
    employee(_employee_name_, street, city)
    works(_employee_name_, company_name, salary)
    company(_company_name_, city)
    manages(_employee_name_, manager_name)
*/

a. Find the names of all employees who work for “First Bank Corporation”

select employee_name
from works
where company_name = ‘First Bank Corporation’;

b. Find all employees in the database who live in the same cities as the companies for which they work.

select employee_name
from employee natural join works natural join company;

c. Find all employees in the database who live in the same cities and on the same streets as do their managers.

select e.employee_name
from employee m, manages, employee e
where (manages.employee_name = e.employee_name) and (m.employee_name = manages.manager_name)
and (m.street = e.street) and (m.city == e.city);

d. Find all employees who earn more than the average salary of all employees of their company

select employee_name
from works p
where salary > (
select avg(salary)
from works q
where p.company_name = q.company_name
)

e. Find the company that has the smallest payroll.

select company_name
from works
group by company_name
having sum(salary) <= all (
select sum(salary)
from works
group by company_name
)

3.20> Given an SQL schema definition for the employee database of Figure 3.20. Choose an appropriate domiain for each attribute and an appropriate primary key for each relation schema.

/*
    employee(_employee_name_, street, city)
    works(_employee_name_, company_name, salary)
    company(_company_name_, city)
    manages(_employee_name_, manager_name)
*/

create table employee (
employee_name varchar(20) primary key,
street varchar(20),
city varchar(20)
);

create table works (
employee_name varchar(20) primary key,
company_name varchar(50),
salary numeric(8, 2),
foreign key (employee_name) references employee,
foreign key (company_name) references company
);

create table company (
company_name varchar(50) primary key,
city varchar(20)
);

create table manages(
employee_name varchar(20) primary key,
manager_name varchar(20),
foreign key (employee_name) references employee
);

3.21> Consider the library database of Figure 3.21. Write the following queries in SQL.

/*
    member(_memb_no_, name, age)
    book(_isbn_, title, authors, publisher)
    borrowed(_memb_no_, _isbn_, date)
*/

a. Print the names of members who have borrowed any book published by “McGraw-Hill”

select name
from member natural join borrowed
where isbn in (
select isbn
from book
where publisher = ‘McGraw-Hill’
);

b. Print the names of members who have borrowed all books published by “McGraw-Hill”

select name
from member m
where not exists (
(select isbn from book where publisher = ‘McGraw-Hill’)
minus / oracle version of except /
(select isbn from member natural join borrowed where m.memb_no = memb_no)
);

c. For each publisher, print the names of members who have borrowed more than five books of that publisher.

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

d. Print the average number of books borrowed per member. Take into account that if an member does not borrow any books, then that member dos not apper in the borrwed relation at all.

select sum(num_of_book) / count(memb_no)
from (
select memb_no, (
select count(*)
from borrowed
where member.memb_no = borrowed.memb_no
) num_of_book
from member
);

3.24> Consider the query:

/*
    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;
*/

Rewrite this query without using the with consturct

select dept_name
from (
select dept_name, sum(salary) dept_total
from instructor
group by dept_name
), (
select avg(dept_total) dept_total_avg
from (
select dept_name, sum(salary) dept_total
from instructor
group by dept_name
)
) where dept_total >= dept_total_avg;