SQL Query Exercises for Practice

The assignment provides a set of 18 SQL query exercises with their expected outputs. These queries cover various concepts like joining tables, filtering data, using aggregate functions, and working with date ranges. The goal is to practice writing SQL queries to retrieve specific information from databases.

0
ADVANCED DATA MANAGEMENT SYSTEM

Table of Contents
1. ER Diagram.................................................................................................................................2
2. Tables............................................................................................................................................2
3. Sample Data.................................................................................................................................5
4. Queries........................................................................................................................................10

1. ER Diagram
The Entity Relationship Diagram is shown in below.
2. Tables
The tables are listed below.

3. Sample Data
The Sample data’s are inserted in the each table. It is shown in below.
tblCinema
CinemaID Address Date HallID
CI100 USA 08-09-
2011
HL100
CI101 US 17-11- HL101

2014
CI102 USA 18-10-
2013
HL102
CI103 Chennai 25-10-
2015
HL103
CI104 Sydney 04-11-
2015
HL104
CI105 North Sydney 19-11-
2015
HL105
tblCustomer
CustomerI
D
Name DOB Address Gende
r
TransactionID
CU100 Sam Joel 12-08-2014 USA Male TR100
CU101 John Sam 17-04-2013 US Male TR101
CU102 Joseph Raj 15-01-2014 Sydney Male TR102
CU103 Jenniffer 22-01-2015 USA Female TR103
CU104 Mirachlin 18-02-2015 Sydney Female TR104
CU105 Mickle 17-07-2014 US Male TR105
tblDirector
DirectorID Name DOB Gender
DI100 Sam Joel 12-06-1960 Male
DI101 Jennifer 13-04-1980 Female
DI102 Mackle 03-07-1981 Male
DI103 Joseph 23-04-1978 Male
DI104 John 16-04-1972 Male
DI105 Maclin 13-07-1980 Female
tblHall
HallID Size ShowingID

HL100 100 SH100
HL101 100 SH101
HL102 100 SH102
HL103 100 SH103
HL104 100 SH104
HL105 100 SH105
tblMovie
MovieID ReleaseDate Languag
e
Cost Country Price StarID DirectorID
MV100 02-12-2011 English ₹
100.00
US ₹
300.00
ST100 DI100
MV101 03-12-2013 English ₹
180.00
USA ₹
500.00
ST101 DI101
MV102 18-11-2013 Hindi ₹
200.00
Sydney ₹
400.00
ST102 DI102
MV103 30-11-2015 English ₹
250.00
US ₹
500.00
ST103 DI103
MV104 22-11-2014 Hindi ₹
300.00
USA ₹
600.00
ST104 DI104
MV105 23-11-2012 English ₹
500.00
US ₹
800.00
ST105 DI105
MV106 30-11-2015 English ₹
300.00
Sydney ₹
500.00
ST105 DI101
tblOfflineTransaction
OfflineTransactionID CinemaID
OF100 CI100
OF101 CI101
OF102 CI102
OF103 CI103

OF104 CI104
OF105 CI105
tblOnlineTransaction
OnlineTransactionI
D
System Browser
ON100 Yes No
ON101 No Yes
ON102 Yes No
ON103 No Yes
ON104 Yes No
ON105 Yes No
tblPromotion
PromotionID Description Discount StartDate EndDate
PR100 Satisfied 40% 05-10-2013 05-11-2013
PR101 Good 40% 12-10-2011 10-11-2011
PR102 Not Bad 40% 11-10-2012 11-11-2012
PR104 Best 40% 18-10-2014 19-11-2015
PR105 Satisfied 40% 16-11-2012 19-11-2013
tblShowing
ShowingID Date Time MovieID
SH100 05-11-
2013
11:30:00 MV100
SH101 11-11-
2011
10:30:00 MV101
SH102 18-11-
2012
09:30:00 MV102
SH103 24-11-
2013
08:30:00 MV103

SH104 25-11-
2014
11:30:00 MV104
SH105 30-11-
2015
10:30:00 MV105
tblStar
StarID Name DOB Gender
ST100 Sam Joel 12-05-1980 Male
ST101 John Sam 12-04-1967 Male
ST102 Joseph Raj 09-03-1970 Male
ST103 Jenniffer 05-04-1965 Female
ST104 Mirachlin 09-08-1980 Female
ST105 Mickle 09-03-1967 Male
tblTicket
TicketID Ro
w
Seat Price ShowingID
TC100 H 15 ₹
500.00
SH100
TC101 G 23 ₹
300.00
SH101
TC102 K 20 ₹
200.00
SH102
TC103 A 19 ₹
250.00
SH103
TC104 B 21 ₹
300.00
SH104
TC105 C 20 ₹
450.00
SH105
tblTransaction

Transac
tionID
D
at
e
Tim
e
Total
Price
PayM
ethod
OnlineTrans
actionID
OfflineTran
sactionID
Tick
etID
Promot
ionID
TR100 05
-
10
-
20
17
11:5
0:00
₹
800.0
0
Online ON100 OF100 TC1
00
PR100
TR101 12
-
11
-
20
17
10:3
0:00
₹
500.0
0
Offline ON101 OF101 TC1
01
PR101
TR102 19
-
11
-
20
17
12:4
5:00
₹
300.0
0
Online ON102 OF102 TC1
02
PR102
TR103 16
-
11
-
20
17
03:4
5:00
₹
900.0
0
Online ON103 OF103 TC1
03
PR104
TR104 23
-
11
-
20
17
12:5
0:00
₹
500.0
0
Offline ON104 OF104 TC1
04
PR105
TR105 30
-
11
-
20
17
12:1
0:00
₹
400.0
0
Offline ON105 OF105 TC1
05
PR102

tblGenre
GenreI
D
GenreName MovieID
GR100 Comedy MV100
GR101 Adventure MV101
GR102 Action MV102
GR103 Crime MV103
GR104 Drama MV104
GR105 Fantasy MV105
4. Queries
The Queries are listed below.
Query – 1
Pivot Table
Query- 1
Date Gender SumOfTotalPrice
05-10-2017 Male 800.00₹
12-11-2017 Male 500.00₹
16-11-2017 Female 900.00₹
19-11-2017 Male 300.00₹
23-11-2017 Female 500.00₹
30-11-2017 Male 400.00₹
3,400.00₹
Output

SQL Queries
SELECT tblTransaction.Date, tblCustomer.Gender, Sum(tblTransaction.TotalPrice)
AS SumOfTotalPrice
FROM tblTransaction INNER JOIN tblCustomer ON tblTransaction.TransactionID =
tblCustomer.TransactionID
GROUP BY tblTransaction.Date, tblCustomer.Gender;
Query – 2
Pivot Table
Query1
Date PayMethod SumOfTotalPrice
05-10-
2017
Online 800.00₹
12-11-
2017
Offline 500.00₹
16-11-
2017
Online 900.00₹

19-11-
2017
Online 300.00₹
23-11-
2017
Offline 500.00₹
30-11-
2017
Offline 400.00₹
3,400.00₹
Output
SQL Queries
SELECT tblTransaction.Date, tblTransaction.PayMethod,
Sum(tblTransaction.TotalPrice) AS SumOfTotalPrice
FROM tblTransaction
GROUP BY tblTransaction.Date, tblTransaction.PayMethod;
Query – 3
Pivot Table
Query-3

GenreNam
e
ReleaseDate SumOfPrice
Action 18-11-2017 400.00₹
Adventure 03-12-2017 500.00₹
Comedy 02-12-2017 300.00₹
Crime 30-11-2017 500.00₹
Drama 22-11-2017 600.00₹
Fantasy 23-11-2017 800.00₹
3,100.00₹
Output
SQL Queries
SELECT tblGenre.GenreName, tblMovie.ReleaseDate, Sum(tblMovie.Price) AS
SumOfPrice
FROM tblMovie INNER JOIN tblGenre ON tblMovie.MovieID = tblGenre.MovieID
GROUP BY tblGenre.GenreName, tblMovie.ReleaseDate;
Query – 4
Pivot Table

Query - 4
SumOfTotalPric
e
Date Gender PromotionID
500.00₹ 12-11-2015 Male PR101
300.00₹ 19-11-2015 Male PR102
500.00₹ 23-11-2015 Female PR105
1,300.00₹
Output
SQL Queries
SELECT Sum(tblTransaction.TotalPrice) AS SumOfTotalPrice, tblTransaction.Date,
tblCustomer.Gender, tblPromotion.PromotionID
FROM tblPromotion INNER JOIN (tblTransaction INNER JOIN tblCustomer ON
tblTransaction.TransactionID = tblCustomer.TransactionID) ON
tblPromotion.PromotionID = tblTransaction.PromotionID
GROUP BY tblTransaction.Date, tblCustomer.Gender, tblPromotion.PromotionID
HAVING (((tblTransaction.Date) Between #1/1/2015# And #12/31/2015#))
Query – 5

Pivot Table
Query - 5
Gende
r
TotalPrice Date SumOfSeat
Female 500.00₹ 23-11-2015 21
Male 300.00₹ 19-11-2015 20
Male 500.00₹ 12-11-2015 23
64
Output
SQL Queries
SELECT tblCustomer.Gender, tblTransaction.TotalPrice, tblTransaction.Date,
Sum(tblTicket.Seat) AS SumOfSeat
FROM tblTicket INNER JOIN (tblTransaction INNER JOIN tblCustomer ON
tblTransaction.TransactionID = tblCustomer.TransactionID) ON tblTicket.TicketID =
tblTransaction.TicketID
GROUP BY tblCustomer.Gender, tblTransaction.TotalPrice, tblTransaction.Date
HAVING (((tblTransaction.Date) Between #1/1/2015# And #12/31/2015#));

Query – 6
Pivot Table
Query - 6
Name Date ReleaseDate Address
Mohamed Khan 05-10-
2011
03-12-2011 USA
Mohamed Khan 05-10-
2011
30-11-2012 USA
Mohamed Khan 05-10-
2011
30-11-2014 USA
Mohamed Khan 12-11-
2015
03-12-2011 US
Mohamed Khan 12-11-
2015
30-11-2012 US
Mohamed Khan 12-11-
2015
30-11-2014 US
Mohamed Khan 19-11-
2015
03-12-2011 USA
Mohamed Khan 19-11-
2015
30-11-2012 USA
Mohamed Khan 19-11-
2015
30-11-2014 USA
Mohamed Khan 16-11-
2012
03-12-2011 Chennai
Mohamed Khan 16-11-
2012
30-11-2012 Chennai
Mohamed Khan 16-11-
2012
30-11-2014 Chennai
Mohamed Khan 23-11-
2015
03-12-2011 Sydney
Mohamed Khan 23-11-
2015
30-11-2012 Sydney
Mohamed Khan 23-11- 30-11-2014 Sydney

2015
Mohamed Khan 30-11-
2010
03-12-2011 North Sydney
Mohamed Khan 30-11-
2010
30-11-2012 North Sydney
Mohamed Khan 30-11-
2010
30-11-2014 North Sydney
Output

SQL Queries
SELECT tblDirector.Name, tblTransaction.Date, tblMovie.ReleaseDate, tblCinema.Address
FROM tblDirector INNER JOIN tblMovie ON tblDirector.DirectorID =
tblMovie.DirectorID, (tblCinema INNER JOIN tblOfflineTransaction ON
tblCinema.CinemaID = tblOfflineTransaction.CinemaID) INNER JOIN tblTransaction ON
tblOfflineTransaction.OfflineTransactionID = tblTransaction.OfflineTransactionID
WHERE (((tblMovie.ReleaseDate) Between #1/1/2010# And #12/31/2015#));
Query – 8

Pivot Table
Query - 8
Name GenreName Gender
Omar
Sharif
Comedy Male
Omar
Sharif
Drama Female
Output
SQL Queries
SELECT tblDirector.Name, tblGenre.GenreName, tblCustomer.Gender
FROM tblDirector INNER JOIN (((tblMovie INNER JOIN tblGenre ON tblMovie.MovieID
= tblGenre.MovieID) INNER JOIN tblShowing ON tblMovie.MovieID =
tblShowing.MovieID) INNER JOIN (tblTicket INNER JOIN ((tblCinema INNER JOIN
tblOfflineTransaction ON tblCinema.CinemaID = tblOfflineTransaction.CinemaID) INNER
JOIN (tblTransaction INNER JOIN tblCustomer ON tblTransaction.TransactionID =
tblCustomer.TransactionID) ON tblOfflineTransaction.OfflineTransactionID =
tblTransaction.OfflineTransactionID) ON tblTicket.TicketID = tblTransaction.TicketID) ON

tblShowing.ShowingID = tblTicket.ShowingID) ON tblDirector.DirectorID =
tblMovie.DirectorID
WHERE (((tblDirector.Name)="Omar Sharif"));
Query – 9
Pivot Table
Query - 9
PayMethod SumOfTotalPrice Address Size
Offline 400.00₹ North Sydney Mid-Size
Offline 500.00₹ Sydney Small-Size
Offline 500.00₹ US Small-Size
1,400.00₹
Output
SQL Queries
SELECT tblTransaction.PayMethod, Sum(tblTransaction.TotalPrice) AS SumOfTotalPrice,
tblCinema.Address, tblHall.Size
FROM (tblHall INNER JOIN (tblCinema INNER JOIN tblOfflineTransaction ON
tblCinema.CinemaID = tblOfflineTransaction.CinemaID) ON tblHall.HallID =
tblCinema.HallID) INNER JOIN tblTransaction ON
tblOfflineTransaction.OfflineTransactionID = tblTransaction.OfflineTransactionID
GROUP BY tblTransaction.PayMethod, tblCinema.Address, tblHall.Size

HAVING (((tblTransaction.PayMethod)="Offline"));
Query – 10
Pivot Table
Query - 10
Gender Age SumOfTotalPrice
Male 10 800.00₹
Output
SQL Queries
SELECT tblCustomer.Gender, tblCustomer.Age, Sum(tblTransaction.TotalPrice) AS
SumOfTotalPrice
FROM tblTransaction INNER JOIN tblCustomer ON tblTransaction.TransactionID =
tblCustomer.TransactionID
GROUP BY tblCustomer.Gender, tblCustomer.Age
HAVING (((tblCustomer.Age) Between 1 And 10));
Query – 11
Pivot Table
Query - 11
SumOfTotalPrice Date Address
300.00₹ 19-11-
2015
USA
500.00₹ 12-11- US

2015
500.00₹ 23-11-
2015
Sydney
1,300.00₹
Output
SQL Queries
SELECT Sum(tblTransaction.TotalPrice) AS SumOfTotalPrice, tblTransaction.Date,
tblCinema.Address
FROM (tblCinema INNER JOIN tblOfflineTransaction ON tblCinema.CinemaID =
tblOfflineTransaction.CinemaID) INNER JOIN tblTransaction ON
tblOfflineTransaction.OfflineTransactionID = tblTransaction.OfflineTransactionID
GROUP BY tblTransaction.Date, tblCinema.Address
HAVING (((tblTransaction.Date) Between #1/1/2015# And #12/31/2015#))
ORDER BY tblCinema.Address DESC;
Query – 12
Pivot Table
Query - 12
tblCustomer.Nam
e
Age tblDirector.Name MovieID SumOfTotalPrice

Mickle 19 Miraclin MV105 400.00₹
Mirachlin 29 Omar Sharif MV104 500.00₹
Jenniffer 20 Mohamed Khan MV103 900.00₹
Joseph Raj 13 Mickle MV102 300.00₹
John Sam 11 Mohamed Khan MV101 500.00₹
Sam Joel 29 Omar Sharif MV100 800.00₹
3,400.00₹
Output
SQL Queries
SELECT tblCustomer.Name, tblCustomer.Age, tblDirector.Name, tblMovie.MovieID,
Sum(tblTransaction.TotalPrice) AS SumOfTotalPrice
FROM (tblDirector INNER JOIN tblMovie ON tblDirector.DirectorID =
tblMovie.DirectorID) INNER JOIN (tblShowing INNER JOIN (tblTicket INNER JOIN
(tblOnlineTransaction INNER JOIN (tblTransaction INNER JOIN tblCustomer ON
tblTransaction.TransactionID = tblCustomer.TransactionID) ON
tblOnlineTransaction.OnlineTransactionID = tblTransaction.OnlineTransactionID) ON
tblTicket.TicketID = tblTransaction.TicketID) ON tblShowing.ShowingID =
tblTicket.ShowingID) ON tblMovie.MovieID = tblShowing.MovieID
GROUP BY tblCustomer.Name, tblCustomer.Age, tblDirector.Name, tblMovie.MovieID

HAVING (((tblCustomer.Age) Between 1 And 30))
ORDER BY tblMovie.MovieID DESC;
Query – 13
Pivot Table
Query - 13
Browser Address
Yes US
Yes Chennai
Output
SQL Queries
SELECT tblOnlineTransaction.Browser, tblCinema.Address
FROM (tblCinema INNER JOIN tblOfflineTransaction ON tblCinema.CinemaID =
tblOfflineTransaction.CinemaID) INNER JOIN (tblOnlineTransaction INNER JOIN
tblTransaction ON tblOnlineTransaction.OnlineTransactionID =

tblTransaction.OnlineTransactionID) ON tblOfflineTransaction.OfflineTransactionID =
tblTransaction.OfflineTransactionID
WHERE (((tblOnlineTransaction.Browser)="Yes"))
ORDER BY tblCinema.Address DESC;
Query – 14
Pivot Table
Query - 14
ReleaseDate Languag
e
Cost Gender
02-12-2010 English ₹
100.00
Male
03-12-2011 English ₹
180.00
Male
18-11-2013 Hindi ₹
200.00
Male
30-11-2014 English ₹
250.00
Female
22-11-2011 Hindi ₹
300.00
Female
23-11-2010 English ₹
500.00
Male
Output

SQL Queries
SELECT tblMovie.ReleaseDate, tblMovie.Language, tblMovie.Cost, tblCustomer.Gender
FROM (tblMovie INNER JOIN tblShowing ON tblMovie.MovieID = tblShowing.MovieID)
INNER JOIN (tblTicket INNER JOIN (((tblCinema INNER JOIN tblOfflineTransaction ON
tblCinema.CinemaID = tblOfflineTransaction.CinemaID) INNER JOIN tblTransaction ON
tblOfflineTransaction.OfflineTransactionID = tblTransaction.OfflineTransactionID) INNER
JOIN tblCustomer ON tblTransaction.TransactionID = tblCustomer.TransactionID) ON
tblTicket.TicketID = tblTransaction.TicketID) ON tblShowing.ShowingID =
tblTicket.ShowingID;
Query – 15
Pivot Table
Query - 15
CinemaI
D
Seat Price Date
CI100 15 ₹
500.00
08-09-2015
CI101 23 ₹ 17-11-2013

300.00
CI102 20 ₹
200.00
18-10-2012
CI103 19 ₹
250.00
25-10-2015
CI104 21 ₹
300.00
04-11-2012
CI105 20 ₹
450.00
19-11-2011
Output
SQL Queries
SELECT tblCinema.CinemaID, tblTicket.Seat, tblTicket.Price, tblCinema.Date
FROM tblTicket INNER JOIN ((tblCinema INNER JOIN tblOfflineTransaction ON
tblCinema.CinemaID = tblOfflineTransaction.CinemaID) INNER JOIN tblTransaction ON
tblOfflineTransaction.OfflineTransactionID = tblTransaction.OfflineTransactionID) ON
tblTicket.TicketID = tblTransaction.TicketID;
Query – 16
Pivot Table
Query - 16

Date TotalPrice PayMethod
12-11-
2015
500.00₹ Offline
19-11-
2015
300.00₹ Online
23-11-
2015
500.00₹ Offline
1,300.00₹
Output
SQL Queries
SQL Queries
SELECT tblTransaction.Date, tblTransaction.TotalPrice, tblTransaction.PayMethod
FROM tblTransaction
WHERE (((tblTransaction.Date) Between #1/1/2015# And #12/31/2015#));
Query – 17
Pivot Table
Query - 17