ICS311-Database Management Systems Solved

Part 1:

In this part you are required to implement your database on MySQL. Your implementation should include the following:

1-                 Create all the tables that you specified in your relational schema (Part 2, of Step 2). Make sure to include primary keys and foreign keys.

2-                 Populate all tables with data of your choice. Make sure that each table includes at least 10 rows.

.SQL file was submitted for this part


Part 2:

Design and clearly explain at least ten query scenarios that may be useful on your database.

Write SQL syntax to answer each query.

A query like this: select count(*) from tableOne, can only be used as one of the ten.

Strongly suggested to include some queries using Table Joins, Aggregates, Group  By, Order By, and Having.

At least one query must use a view (that you created from your application tables) in the FROM clause.

Note: the SELECT used for in the creation of the view, does not count as one of the 10 queries.

Query 1
USE OF COUNT(*)

SELECT COUNT(*) FROM Movie;

Query-2
USE OF VIEW-

View-A View is a temporary table and it is same as the main table but we can perform any operation on it without modifying main table.

CREATE VIEW Count_Movie AS(SELECT MovieID,Genre FROM Movie);

Now Display data of view-

SELECT * FROM Count_Movie;

Query-4
USE OF AGGREGATE FUNCTION-

SELECT SUM(Show.ShowID) AS SUM FROM Movie

INNER JOIN Show ON Show.MovieID=Movie.MovieID;

Query-5
USE OF GROUP BYSELECT Show.ShowID FROM Show

GROUP BY Show.MovieID,Show.ShowID;

Query-6
USE OF ORDER BY

SELECT Show.ShowID FROM Show

ORDER BY Show.ShowID DESC;

Query-7
USE OF HAVING

SELECT Show.ShowID FROM Show GROUP BY Show.ShowID

HAVING Show.ShowID>1;

Query-9
USE of GROUP BY AND ORDER BY TOGETHER-

SELECT Movie.MovieID FROM Movie

GROUP BY Movie.MovieID

ORDER BY Movie.MovieID DESC;

Query-10
Use of Above all functions-

SELECT Movie.Genre,Show.ShowID FROM Movie

INNER JOIN Show ON Show.MovieID=Movie.MovieID

WHERE Movie.MovieID>1

GROUP BY Movie.Genre,Show.ShowID

HAVING COUNT(Show.ShowID)>2

ORDER BY Show.ShowID DESC;

ELECT is used to display all(*) or specified column/columns COUNT( ) is used to count number of rows inside specified column.

table_name1.column_name1,Here it specifies that column_name1 is of table table_name1

INNER JOIN is used to join two tables based on a common column.

WHERE is used to specify condition.

We cannot use aggregate function directly inside where so HAVING is used.

GROUP BY is used to group result as per the column specified.

ORDER BY is used to sort result either in ascending(ASC)increasing or descending(DESC)decreasing order.