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.

  1. 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.

SelectID, Price/1.23

as ‘Net Price without Tax’

fromDVD

ID Net Price without tax

------

1 1.626016

2 2.439024

3 1.62601

  1. Show the participants’ id who have participated in at least one movie. Each participant id should be shown only once (remove duplicates).

Selectdistinct IDParticipant

fromM_P_R

IDParticipant

------

1

2

3

  1. Show all the characteristics of movies, for which either their title includes character "-" or have been produced before 1955.

select*

fromMovies

whereTitlelike '%-%' orYear < 1955

Id TitleYear

------

1 Rear Window 1954

3 Ben-Hur 1959

  1. Find all the characteristics of DVD disc rentals that their rental day is between 15 Sep 2006 and 30 Sep 2006.

Select*

fromRENTAL

whereStart_daybetween '2006-09-15' and '2006-09-30'

IDCustomerIDdvdStart_dayReturn_day

------

1 2 2006-09-20 2006-11-20

  1. Find the DVD discs ids, which are rented and are returned back.

Select IDDisc

from RENTAL

whereReturn_day IS NOT NULL

IDDisc

------

1

Queries for retrieving data from many joined tables.

  1. Find the customer names, who have rented at least one DVD disc.

SelectdistinctCUSTOMER.Name

FromCUSTOMERinner joinRENTALon

RENTAL.IDCustomer = CUSTOMER.ID

Customer Name

------

Perkins

Κantakouzinos

  1. Find the customer names, who have rented no DVD disc at all (do not use the NOT IN operator).

FromCUSTOMERleft outer joinRENTALon

RENTAL.IDCustomer =CUSTOMER.ID

WhereRENTAL.IDCustomerIS NULL

Customer Name

------

Palaiologos

  1. Find the participants id that have participated in at least two movies (do not use the COUNT operator).

SelectdistinctMPR1.IDParticipant

FromM_P_RasMPR1, M_P_RasMPR2

WhereMPR1.IDParticipant = MPR2.IDParticipantand

MPR1.IDMovieMPR2.IDMovie

IDParticipant

------

1

  1. 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).

SelectTitle

FromMOVIEleftouterjoinDVD

onDVD.IDMovie = MOVIE.ID

leftouterjoinRENTALonRENTAL.IDDisc =DVD.ID

WhereRENTAL.IDDiscISNULL

Τitle

------

Psycho

Ben-Hur

  1. Find the customer names that have the same surname with the name of a movie participant.

SelectCUSTOMER.Name

FromCUSTOMER, PARTICIPANT

WherePARTICIPANT.Name

like ('%' ||CUSTOMER.Name|| '%')

Name

------

Perkins

Aggregated Queries.

  1. 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'

FromM_P_RinnerjoinPARTICIPANTon

M_P_R.IDParticipant =PARTICIPANT.ID

WherePARTICIPANT.Name = 'AlfredHitchcock'

Number of Movies

------

2

  1. 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

GroupByMOVIE.Title

TitleNumber

------

RearWindow2

Psycho1

Ben-Hur 0

  1. Find the DVD disc ids that are BRAY and have been rented more than once.

SelectDVD.ID

From DVDinner join RENTALon DVD.ID = RENTAL.IDdisc

Where DVD.Type = 'BLU-RAY'

Groupby DVD.ID

Havingcount(*) > 1

Disc ID

------

1

Nested Queries.

  1. Find the movie titles that have never been rented. (do not use the outer join operator).

SelectTitle

FromMOVIE

wherenot exists (selectDVD.IDMovie

fromRENTALinner joinDVD onRENTAL.IDdvd = DVD.ID

where IDMovie = MOVIE.ID)

Title

------

Psycho

Ben-Hur

  1. Find the participant name who have participated in most movies (use the all operator).

Select Name

FromPARTICIPANT

where ID in

(Select IDParticipant

fromM_P_R

Group by IDParticipant

Having count(IDMovie) >= all

(Selectcount(*)

FromM_P_R

GroupbyIDParticipant

)

)

Name

------

AlfredHitchcock

  1. 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).

SelectdistinctΕ1.IDCustomer

fromRENTALasΕ1

where not exists

(select Ε2.IDDisc

from RENTALas Ε2

where Ε2.IDCustomer = 2 and

Ε2.IDDiscnot in

(select Ε3.IDDisc

from RENTALas Ε3

where Ε3.IDCustomer = Ε1.IDCustomer))

andΕ1.IDCustomer > 2

IDCustomer

------

1