Chapter 4

Practice Exercises

1. Consider the following SQL query that seeks to find a list of titles of all courses
taught in Spring 2017 along with the name of the instructor.
Ans :
select name, title from instructor natural join teaches natural join section natural join course
where semester ='Spring' and year = 2017

What is wrong with this query?

Ans:
The problem with this query is that both instructor and course have a column named
dept_name. Since NATURAL JOIN automatically joins on columns with the same name, it
only includes matches where dept_name is the same in both tables. This means instructors
will only be listed for courses in their own department.

2.Write the following queries in SQL:

a. Display a list of all instructors, showing each instructor's ID and the number
of sections taught. Make sure to show the number of sections as 0 for
instructors who have not taught any section. Your query should use an outer
join, and should not use subqueries.

Ans:
select ID, count(sec_id) as Number_of_sections
from instructor natural left outer join teaches
group by ID;

b. Write the same query as in part a, but using a scalar subquery and not using
outer join.

Ans:
select ID,
(select count(*) as Number_of_sections
from teaches T where T.id = I.id)
from instructor I;

c. Display the list of all course sections offered in Spring 2018, along with the
ID and name of each instructor teaching the section. If a section has more than
one instructor, that section should appear as many times in the result as it has
instructors. If a section does not have any instructor, it should still appear in
the result with the instructor name set to "-".
Ans:
select course_id, sec_id, ID,
coalesce(name,'--') as name
from (section natural left outer join teaches)
natural left outer join instructor
where semester='Spring' and year = 2018;
d. Display the list of all departments, with the total number of instructors in
each department, without using subqueries. Make sure to show departments
that have no instructors, and list those departments with an instructor count
of zero.
Ans:

SELECT d.dept_name, COUNT(i.ID) AS num_instructors

FROM department d
LEFT OUTER JOIN instructor i ON d.dept_name = i.dept_name
GROUP BY d.dept_name;

3.Outer join expressions can be computed in SQL without using

the SQL outer join operation. To illustrate this fact, show how to
rewrite each of the following SQL queries without using the outer
join expression.
a. select * from student natural left outer join takes
Ans:
SELECT *
FROM student
NATURAL JOIN takes
UNION
SELECT student.ID, name, dept_name, tot_cred,
NULL, NULL, NULL, NULL, NULL
FROM student
WHERE student.ID NOT IN (SELECT ID FROM takes);

b. select * from student natural full outer join takes

Ans:

SELECT *
FROM student
NATURAL JOIN takes
UNION
SELECT student.ID, name, dept_name, tot_cred,
NULL, NULL, NULL, NULL, NULL
FROM student
WHERE ID NOT IN (SELECT ID FROM takes)
UNION
SELECT NULL, NULL, NULL, NULL,
takes.ID, course_id, sec_id, semester, year
FROM takes
WHERE ID NOT IN (SELECT ID FROM student);
4. Suppose we have three relations r(A, B), s(B, C), and t(B, D),
with all attributes declared as not null.
a. Give instances of relations r, s, and t such that in the result of (r natural left
outer join s) natural left outer join t attribute C has a null value but attribute D
has a non-null value.
Ans:
Yes, it's possible for C to be null while D is not null. Example:
r = {(1,1)}, s = {(2,1)}, t = {(1,3)}
Result: {(1,1,Null,3)}
b. Are there instances of r, s, and t such that the result of r natural left outer
join (s natural left outer join t) has a null value for C but a non-null value for D?
Explain why or why not.
Ans:
No, this isn't possible because joining s and t first requires matching B values, so if D is not
null, C must also have a value.

5. Testing SQL queries: To test if a query specified in English has been correctly
written in SQL, the SQL query is typically executed on multiple test databases, and a
human checks if the SQL query result on each test database matches the intention of
the specification in English.
a. In Section 4.1.1 we saw an example of an erroneous SQL query which was intended
to find which courses had been taught by each instructor; the query computed the
natural join of instructor, teaches, and course, and as a result it unintentionally
equated the dept name attribute of instructor and course. Give an example of a dataset
that would help catch this particular error.
Answer:
Example dataset where instructor and course have different dept_names would catch the
error.
b. When creating test databases, it is important to create tuples in referenced relations
that do not have any matching tuple in the referencing relation for each foreign key.
Explain why, using an example query on the university database.
Answer:
Creating tuples in referenced relations without matching tuples helps test foreign key
constraints and join operations.
c. When creating test databases, it is important to create tuples with null values for
foreign-key attributes, provided the attribute is nullable (SQL allows foreign-key
attributes to take on null values, as long as they are not part of the primary key and
have not been declared as not null). Explain why, using an example query on the
university database.
Answer:
Null values in foreign keys test how queries handle missing data since NULL ≠ NULL in SQL.

6. Show how to define the view student grades (ID, GPA) giving the grade-point
average of each student, based on the query in Exercise 3.2; recall that we used a
relation grade points(grade, points) to get the numeric points associated with a letter
grade. Make sure your view definition correctly handles the case of null values for the
grade attribute of the takes relation.

Answer:
create view student_grades(ID, GPA) as
select ID, sum(credits*points)/sum(credits) as GPA
from takes, course, grade_points
where takes.grade = grade_points.grade
and takes.course_id = course.course_id
group by ID;

7. Consider the employee database of Figure 4.12. Give an SQL DDL definition of this
database. Identify referential-integrity onstraints that should hold, and include them in
the DDL definition.
Answer:

CREATE TABLE employee (

ID NUMERIC(5,0),
person_name VARCHAR(255) NOT NULL,
street VARCHAR(255),
city VARCHAR(255),
PRIMARY KEY (ID)
);

CREATE TABLE company (

company_name VARCHAR(255) NOT NULL,
city VARCHAR(255),
PRIMARY KEY (company_name)
);
CREATE TABLE works (
ID NUMERIC(5,0),
company_name VARCHAR(255),
salary DECIMAL(10,2),
PRIMARY KEY (ID),
FOREIGN KEY (ID) REFERENCES employee(ID),
FOREIGN KEY (company_name) REFERENCES company(company_name)
);

CREATE TABLE manages (

ID NUMERIC(5,0),
manager_id NUMERIC(5,0),
PRIMARY KEY (ID),
FOREIGN KEY (ID) REFERENCES employee(ID),
FOREIGN KEY (manager_id) REFERENCES employee(ID)
);

8. As discussed in Section 4.4.8, we expect the constraint "an instructor cannot teach
sections in two different classrooms in a semester in the same time slot" to hold.
a. Write an SQL query that returns all (instructor, section) combinations that violate
this constraint.
Answer:
SELECT instructor.ID, instructor.name, teaches.sec_id, teaches.semester, teaches.year,
section.time_slot_id, COUNT(DISTINCT section.building, section.room_number) AS
location_count
FROM instructor NATURAL JOIN teaches NATURAL JOIN section
GROUP BY instructor.ID, instructor.name, teaches.sec_id, teaches.semester, teaches.year,
section.time_slot_id
HAVING COUNT(DISTINCT section.building, section.room_number) > 1;

b. Write an SQL assertion to enforce this constraint.

Answer:
create assertion check not exists
(SELECT instructor.ID, instructor.name, teaches.sec_id, teaches.semester, teaches.year,
section.time_slot_id, COUNT(DISTINCT section.building, section.room_number) AS
location_count
FROM instructor NATURAL JOIN teaches NATURAL JOIN section
GROUP BY instructor.ID, instructor.name, teaches.sec_id, teaches.semester, teaches.year,
section.time_slot_id
HAVING COUNT(DISTINCT section.building, section.room_number) > 1);
9. QL allows a foreign-key dependency to refer to the same relation, as in the following
example:
create table manager
(employee ID char(20),
manager ID char(20), primary key employee ID,
foreign key (manager ID) references manager(employee ID)
on delete cascade )
Here, employee ID is a key to the table manager, meaning that each employee has at
most one manager. The foreign-key clause requires that every manager also be an
employee. Explain exactly what happens when a tuple in the relation manager is
deleted.
Answer:
When a manager tuple is deleted, all employees who had that manager get deleted too,
recursively.

10. Given the relations a(name, address, title) and b(name, address, salary), show how
to express a natural full outer join b using the full outer-join operation with an on
condition rather than using the natural join syntax. This can be done using the
coalesce operation. Make sure that the result relation does not contain two copies of
the attributes name and address and that the solution is correct even if some tuples in
a and b have null values for attributes name or address.
Answer:
SELECT COALESCE(a.name, b.name) AS name,
COALESCE(a.address, b.address) AS address,
a.title,
b.salary
FROM a FULL OUTER JOIN b
ON a.name = b.name AND a.address = b.address;

11. Operating systems usually offer only two types of authorization control for data
files: read access and write access. Why do database systems offer so many kinds of
authorization?
Answer:
Databases need more control than simple read/write because they handle complex
operations, require granular security, and need to manage schema changes separately from
data access.
12. Suppose a user wants to grant select access on a relation to another user. Why
should the user include (or not include) the clause granted by current role in the grant
statement?
Answer:
Including this clause makes the permission persist even if the granting user leaves, as it's tied
to the role rather than the individual.

13. Consider a view v whose definition references only relation r.

Ans:
• Select on view: No need for select on base table
• Update on view: Need update on base table too
• Insert example: Inserting data that doesn't meet view's filter conditions won't appear in
the view

Exercises

14. Consider the query:

select course_id, semester, year, sec_id, avg(tot_cred)
from takes natural join student
where year = 2017
group by course_id, semester, year, sec_id
having count(ID) >= 2;
Explain why appending natural join section in the from clause would not change the
result.
Answer:
The query already groups by section's key attributes, so joining with section adds no new
information that affects the average.

15. Rewrite the query:

Select * from section natural join classroom
without using a natural join but instead using an inner join with a using condition.
Answer:
select * from section join classroom using (building, room_number);
16. Write an SQL query using the university schema to find the ID of each student who
has never taken a course at the university. Do this using no subqueries and no set
operations (use an outer join).
Answer:
select s.ID
from student s left outer join takes t on s.ID = t.ID
where t.ID is null;

17. Express the following query in SQL using no subqueries and no set operations:
Answer:
select s.ID
from student s left outer join advisor a on s.ID = a.s_id
where a.s_id is null or a.i_ID is null;

18.For the database of Figure 4.12, write a query to find the ID of each employee with
no manager. Note that an employee may simply have no manager listed or may have a
null manager. Write your query using an outer join and then write it again using no
outer join at all.
Ans:
With outer join:
select e.person_name
from employee e left outer join manages m on e.ID = m.ID
where m.manager_id is null;

Without outer join:

SELECT e.person_name
FROM employee e
WHERE NOT EXISTS (SELECT 1 FROM manages m WHERE e.ID = m.ID);

19. When would title be null?

Answer:
When there are students who haven't taken any courses or courses no students have taken,
and when student and course department names don't match.
20. Show how to define a view tot_credits (year, num_credits), giving the total number
of credits taken in each year.
Answer:
create view tot_credits (year, num_credits) as
select year, sum(credits)
from takes natural join course
where grade is not null and grade <> 'F'
group by year;

21. For the view of Exercise 4.20, explain why the database system would not allow a
tuple to be inserted into the database through this view.
Answer:
The view uses aggregation and joins, making it non-updatable.

22. Show how to express the coalesce function using the case construct.
Answer:
case when a is not null then a
when b is not null then b
else c
end;

23. Explain why, when a manager, say Satoshi, grants an authorization, the grant
should be done by the manager role, rather than by the user Satoshi.
Answer:
Granting by role ensures permissions persist if the user leaves and maintains proper privilege
management.

24. Suppose user A, who has all authorization privileges on a relation r, grants select
on relation r to public with grant option. Suppose user B then grants select on r to A.
Does this cause a cycle in the authorization graph? Explain why.
Answer:
No cycle occurs because user A already has all privileges, so B can't grant anything new to A.

25. Suppose a user creates a new relation r1 with a foreign key referencing another
relation r2. What authorization privilege does the user need on r2? Why should this not
simply be allowed without any such authorization?
Answer:
User needs REFERENCES privilege on r2 to prevent unauthorized constraints on other users'
data
26. Explain the difference between integrity constraints and authorization constraints.
Ans:

Feature Integrity Constraints Authorization Constraints

Purpose Ensure data correctness Control access to data
Examples Primary keys, foreign keys GRANT, REVOKE statements
Enforcement Automatic by database Based on user
roles/privileges
Focus Data quality Security and access control