5
C H A P T E R 3
SQL
(
C
h
a
p
t
e
r
s
3 and 4)
Questions on SQL covering Chapters 3 and 4 are provided here.
E
x
e
r
c
i
s
e
s
3.1 Given the schema
item(itemid, name, category, price)
itemsale(transid, itemid, qty)
transaction(transid, custid, date)
customer(custid, name, street–addr, city)
where primary keys are underlined, write the following queries in SQL:
a. Find the name and price of the most expensive item (if more than one
item
is the most expensive, print them all).
. . .5
3.2 Suppose you are given a relation
r(R,
M ), where R indicates roll number
and
M the marks scored.
3.3 Consider a system to store the marks of students on various exams, for several
courses.
6 Chapter 3 SQL (Chapters 3 and 4)
a.
Define
a view totalmarks(course, rollno,
marks)
to get total marks
for students of each course, given a table: weights(course, exam,
3.4 I want to design a system to automate project groups signing up for project
demo slots.
(Note: formal coverage of schema design is in Chapter 8, but this question
can still be asked of smart students)
a. Design a relational schema for this task. You should record group members
for groups. Enforce the following constraints: every student is in at most
one group, each slot has at most one group, each group has at most one slot.
. .
.4
3.5 Given a relation
i
n
co
m
e
(
n
a
m
e
,
v
a
l
u
e
)
write an SQL query to find the first 10
3.6 Write an SQL query to list the name of each student and the total number of
3.7 Write SQL expressions to do the following. Assume you are given two relations,
student(name, rollno) and marks(rollno, exam, mark)
a. Show names of all students who have got marks in at least two exams.
. . .2
b. Find the names of the students with highest total marks (summed across
all
exams for each student).
. . .3
3.8 Given a table
r
with a foreign key referencing s, what is the effect of adding the
clause on delete cascade to the foreign key declaration?
. .
.2
3.9 Give an example of a pair of relations with integrity constraints, and inserts
into both of them such that, regardless of whichever insert is first, the integrity