Database Systems 2017/18
Solutions for Exercises of week 4
Write the SQL Queries for retrieving the right data from the DVD club database. For the creation of the DVD club database you need to download the DDL code from the pdf file with the solutions for exercise week 3.
Queries in joined Tables
Q11. Provide the DVD disc ids sorted in descending order based on their rental price. In case of equality of rental prices, the ranking should be in ascending order based on their id number.
Select Price, ID
From DISC
Order by Price desc, ID asc
Price ID
------
3.00 2
2.00 1
2.00 3
Q17. Provide for each customer his name, the DVD id and the price of DVD disc he has rented.
Select CUSTOMER.Name, DVD.ID, DVD.Price
From CUSTOMER inner join RENTAL on
CUSTOMER.ID =RENTAL.IDCustomer inner join DVD on
RENTAL.IDDisc = DVD.ID
Name ID Price
------
Perkins 1 2,00
Perkins 2 3,00
Κantakouzinos 1 2,00
Q18. Provide for each customer his name, the DVD id and the price of DVD disc he has rented. Show also the customers’ name who have not rented a DVD disc.
Select CUSTOMER.Name, DVD.ID, DVD.Price
From CUSTOMER left outer join RENTAL on
CUSTOMER.ID =RENTAL.IDCustomer left outer join DVD on
RENTAL.IDDisc =DISC.ID
Name ID Price
------
Perkins 1 2,00
Perkins 2 3,00
Κantakouzinos 1 2,00
Πalaiologos NULL NULL
Q19. Provide for each customer his name, the DVD id and the price of DVD disc he has rented. Show also the DVDs ids that have not been rented at all.
Select CUSTOMER.Name, DVD.ID, DVD.Price
From CUSTOMER right outer join RENTAL
on CUSTOMER.ID = RENTAL.IDcustomer
right outer join DVD
on RENTAL.IDDisc = DVD.ID
Name ID Price
------
Perkins 1 2,00
Κantakouzinos 1 2,00
Perkins 2 3,00
NULL 3 2,00
Q20. Provide for each customer his name, the DVD id and the price of disc he has rented. Show also the customers’ name who have not rented a DVD disc. Show also the DVDs ids that have not been rented at all.
Select Customer.Name, DVD.ID, DVD.Price
From CUSTOMER full outer join
RENTAL on CUSTOMER.ID = RENTAL.IDcustomer
full outer join DVD on
RENTAL.IDDisc = DVD.ID
Name ID Price
------
Perkins 1 2,00
Perkins 2 3,00
Κantakouzinos 1 2,00
Palaiologos NULL NULL
NULL 3 2,00
Q22. Find the names of customers who have the same telephone number as that of Mr. Perkins (without showing Mr. Perkins himself).
Select B.name
From Customer as A, Customer as B
Where A.phone = B.phone
And A.name = 'Perkins' and B.Name > 'Perkins'
Name
------
Κantakouzinos
Q23. Find the id for each Movie for which the BRAY disc price is smaller than the corresponding DVD disc price.
Select Α.IDMovie
From DVD as Α, DVD as Β
Where Α.IDMovie = Β.IDMovie
and Α.Τype = 'BRAY' and
Β.Type = 'DVD'
and Α.Price < Β.Price
IDMovie
------
1
Aggregated Queries
Q28. Determine the average rental price per disk type (BRAY or DVD).
Select Type, avg(Price) as 'Average Price'
From DISC
Group by Type
d
Type Average Price
------
DVD 3,000000
BLU-RAY 2,000000
Q29. For each customer id, find the number of times he has rented each DVD id.
Select IDCustomer, IDdisc, count(IDdisc) as 'Number of Rental'
From RENTAL
Group by IDcustomer, IDdisc
IDCustomer IDDisc Number of Rental
------
1 1 1
1 2 1
2 1 1
Q30. Find the average rental price per DVD disc type and order the results in ascending order of its average rental price.
Select Type, avg(Price) as 'Average Price'
From DVD
Group by Type
Order by 'Average Type'
Type Average Price
------
BRAY 2,000000
DVD 3,000000
Q31. Find the total number of rentals per customer id. In the results show also the customers who have not made a rental (i.e., their number of rentals is equal to 0).
Select CUSTOMER.ID,count(IDdisc) as 'Number of rentals'
From CUSTOMER left outer join RENTAL on
CUSTOMER.ID = RENTAL.IDcustomer
Group by CUSTOMER.ID
ID 'Number of rentals'
------
1 2
2 1
3 0
Q32. Find the type of DVD disc for which the average rental price is greater than 2.
Select Type, avg(Price) as 'Average Rental Price'
From DISC
Group by Type
Having avg(Price) > 2
Type 'Average Rental Price'
------
DVD 3,000000
Queries with Set operations
Q34. Find the titles of movies that were produced in 1959 or the movies with DVD disc type 'BRAY'.
(Select Title
From Movie
Where Year = 1959)
union
(Select Title
From MOVIE, DVD
Where MOVIE.ID = DVD.IDmovie and Type = ‘BRAY’)
Movie.title
------
Ben-Hur
Psycho
Rear Window
Q35. Find the movie titles that their second letter is "e" and were produced in 1954. (intersect)
Select Title
From MOVIE
Where Τitle like ‘_e%’
except
Select Τitle
From MOVIE
Where YEAR = ‘1954’
Movie.title
------
Rear Window
Q36. Find the titles of films that their second letter is "e", except those produced in 1954.
Select Title
From Movie
Where Title like ‘_e%’
except
Select Title
From Movie
Where Year = ‘1954’
Title
------
Ben-Hur
Nested Queries
Q37. Find the customers that have rented at least one DVD disc. (in)
Select Name
From CUSTOMER
Where ID in
(Select IDCustomer From RENTAL)
Name
------
Perkins
Kantakouzinos
Q38. Find all participant not named Alfred Hitchcock and Grace Kelly. (not in)
Select Name
From Participant
Where Name not in ('Alfred Hitchcock', 'Grace Kelly')
Name
------
Anthony Perkins
Q39. Find the movies id in which participated Alfred Hitchcock and rented more than twice.
Select DVD.IDMovie
From DVD inner join RENTAL on
RENTAL.IDDisc = DVD.ID Where DVD.IDMovie IN
(Select IDMovie From M_ Ρ_R inner join PARTICIPANT on
M_P_R.IDParticipant = PARTICIPANT.ID
where PARTICIPANT.Name = 'Alfred Hitchcock')
Group by DVD.IDMovie
Having count(*) > 2
IDDisc
------
1
Q40. Find the customers (Name) who have rented a disc with no fixed return date.
Select Name
From CUSTOMER
Where ID = some
(Select IDCustomer
From RENTAL
where Return_day IS NULL)
Name
------
Kantakouzinos
Q41. Find the customer id with the greater number of rentals.
Select IDCustomer
from Rental
group by IDCustomer
having count(*) >= all
( Select count(*)
from Rental
group by IDCustomer )
IDCustomer
------
1
Q42. Find the customers names that have made at least one rental. (exists)
Select Name
from CUSTOMER
where exists
(select IDcustomer
from Rental
where IDcustomer = CUSTOMER.ID)
Name
------
Perkins
Kantakouzinosa