Database Systems 2017/18
Exercises (week 5)
Write the SQL Queries for retrieving the right data from the DVDclub database.
Queries for retrieving data from one table.
- For each DVD disc, show its id and price. The price should be shown without the taxation (VAT) that is applied, which is 23% and it is now included in the final price of a DVD disc.
as ‘Net Price without Tax’
ID Net Price without tax
- Show the participants’ id who have participated in at least one movie. Each participant id should be shown only once (remove duplicates).
- Show all the characteristics of movies, for which either their title includes character "-" or have been produced before 1955.
whereTitlelike '%-%' orYear < 1955
1 Rear Window 1954
3 Ben-Hur 1959
- Find all the characteristics of DVD disc rentals that their rental day is between 15 Sep 2006 and 30 Sep 2006.
whereStart_daybetween '2006-09-15' and '2006-09-30'
1 2 2006-09-20 2006-11-20
- Find the DVD discs ids, which are rented and are returned back.
whereReturn_day IS NOT NULL
Queries for retrieving data from many joined tables.
- Find the customer names, who have rented at least one DVD disc.
RENTAL.IDCustomer = CUSTOMER.ID
- Find the customer names, who have rented no DVD disc at all (do not use the NOT IN operator).
FromCUSTOMERleft outer joinRENTALon
- Find the participants id that have participated in at least two movies (do not use the COUNT operator).
WhereMPR1.IDParticipant = MPR2.IDParticipantand
- Find the movie titles for which either there is no DVD disc copy or there is a DVD disc but it has never been rented (do not use NOT IN operator).
onDVD.IDMovie = MOVIE.ID
- Find the customer names that have the same surname with the name of a movie participant.
like ('%' ||CUSTOMER.Name|| '%')
- Find the number of movies that Alfred Hitchcock has participated (Movies that he participated with more than one roles should be counted only once).
Selectcount(distinctM_P_R.IDMovie) as 'Number of Movies'
WherePARTICIPANT.Name = 'AlfredHitchcock'
Number of Movies
- For each movie title, find its total number of DVD discs copies (DVD and BRAY). In the results should also be shown movies that have no DVD disc copies at all.
SelectMOVIE.Title, count(DVD.Type) as 'Number'
FromMOVIEleftjoinDVDonDVD.IDMovie = MOVIE.ID
- Find the DVD disc ids that are BRAY and have been rented more than once.
From DVDinner join RENTALon DVD.ID = RENTAL.IDdisc
Where DVD.Type = 'BLU-RAY'
Havingcount(*) > 1
- Find the movie titles that have never been rented. (do not use the outer join operator).
wherenot exists (selectDVD.IDMovie
fromRENTALinner joinDVD onRENTAL.IDdvd = DVD.ID
where IDMovie = MOVIE.ID)
- Find the participant name who have participated in most movies (use the all operator).
where ID in
Group by IDParticipant
Having count(IDMovie) >= all
- Find the customer ids who have rented at least all the movies that customer with id 2 has rented (customer with id 2 should not be shown in the results).
where not exists
from RENTALas Ε2
where Ε2.IDCustomer = 2 and
from RENTALas Ε3
where Ε3.IDCustomer = Ε1.IDCustomer))
andΕ1.IDCustomer > 2