CREATE DATABASE MIS602;

USE MIS602;

/*Task 1: Create three tables with relevant keys as suggested in the above diagram and the sample data tables. */

DROP TABLE IF EXISTS EMPLOYEE;

DROP TABLE IF EXISTS DEPARTMENT;

DROP TABLE IF EXISTS SALARY;

CREATE TABLE DIRECTOR(

Director_Id INTEGER PRIMARY KEY,

    Director_Fname VARCHAR(20),

    Director_Lname VARCHAR(20)

);

CREATE TABLE GENRE(

Genre_Id INTEGER PRIMARY KEY,

    Genre_Class VARCHAR(30)

);

CREATE TABLE MOVIE(

Movie_Id INTEGER PRIMARY KEY,

    Movie_Name VARCHAR(30),

    Release_Year INTEGER,

    Movie_Time INTEGER,

    Planned_Budget DECIMAL(10,2),

Director_Id INTEGER,

    Genre_Id INTEGER,

CONSTRAINT FK_Movie_Director FOREIGN KEY (Director_Id)

    references DIRECTOR(Director_Id),

CONSTRAINT FK_Movie_Genre FOREIGN KEY (Genre_Id)

    references Genre(Genre_Id)

);

/**

 * Task 3: Insert 5 records to Director table

 */

INSERT INTO DIRECTOR VALUES(3001, 'John', 'Lasseter');

INSERT INTO DIRECTOR VALUES(3002, 'Pete', 'Dcoter');

INSERT INTO DIRECTOR VALUES(3003, 'Andrew', 'Stanton');

INSERT INTO DIRECTOR VALUES(3004, 'Brad', 'Bird');

INSERT INTO DIRECTOR VALUES(3005, 'James', 'Cameroon');

INSERT INTO DIRECTOR VALUES(3006, 'James', 'Alex');

INSERT INTO GENRE VALUES(2006, 'Action');

INSERT INTO GENRE VALUES(2007, 'Comedy');

INSERT INTO GENRE VALUES(2008, 'Adventure');

INSERT INTO GENRE VALUES(2009, 'Animated');

INSERT INTO GENRE VALUES(2005, 'Science Fiction');

INSERT INTO GENRE VALUES(2004, 'Historical');

/**

 * Task 2: Insert 10 records to Movies table.

 */

INSERT INTO MOVIE VALUES(1001, 'Finding Nemo', 2003, 107, 3.5, 3002, 2009); 

INSERT INTO MOVIE VALUES(1002, 'The Incredibles', 2004, 116, 0.5, 3003, 2009); 

INSERT INTO MOVIE VALUES(1003, 'Beyond The Sea', 2004, 118, 3, 3001, 2006); 

INSERT INTO MOVIE VALUES(1004, 'Avatar', 2009, 116, 10, 3005, 2008); 

INSERT INTO MOVIE VALUES(1005, 'The Seven Samurai', 2003, 127, 4.5, 3004, 2005); 

INSERT INTO MOVIE VALUES(1006, 'Reservior Dogs', 2008, 136, 5.5, 3003, 2007); 

INSERT INTO MOVIE VALUES(1007, 'Airplane!', 2011, 98, 3.9, 3001, 2006); 

INSERT INTO MOVIE VALUES(1008, 'Titanic', 2015, 145, 10, 3005, 2009);  

INSERT INTO MOVIE VALUES(1009, 'The Twilight Saga', 1995, 117, 9.5, 3004, 2007); 

INSERT INTO MOVIE VALUES(1010, 'The Pirates of the Carabian', 2006, 110, 10.5, 3003, 2009);

/**

 * Task 4: Write a query to display all the information about the Movies.

 */

 SELECT * FROM MOVIE;

/**

 * Task 5: Write a query to display the Movie_Names of all the movies.

 */

SELECT Movie_Name FROM MOVIE;

/**

 * Task 6: Write a query to display all the Movie_Names and their Planned_Budget

 */

SELECT Movie_Name, Planned_Budget FROM MOVIE;

/**

 * Task 7:  Write a query to update the Movie_Time of ‘Finding Nemo’ to 120 minutes. Make sure to insert some data that satisfy the criteria before executing the query

 */

UPDATE MOVIE set Movie_Time=120 where Movie_Name = 'Finding Nemo';

/**

 * Task 8: Write a query to display the Movie_Id, Movie_Name of all the movies with a planned budget above 3 million. Make sure to insert some data that satisfy the criteria before executing the query.

 */

SELECT Movie_Id, Movie_Name FROM MOVIE where Planned_Budget > 3;

/**

 * Task 9: Write a query to increase the planned budget of all Movies by 5% for all the movies with a Planned_Budget less than 5 million. 

 */

UPDATE MOVIE set Planned_Budget= Planned_Budget + (Planned_Budget *0.05) where Planned_Budget < 5;

/**

 * Task 10: Write a query to display the all the details of the Movies directed by Director_Id ‘3001’. Make sure to insert some data that satisfy the criteria before executing the query.

 */

SELECT * FROM MOVIE where Director_id = 3001;

/**

 * Task 11: Write a query to display all the unique Director_Fname.

 */

Select DISTINCT DIRECTOR_Fname from DIRECTOR;

/**

 * Task 12: Write a query to display Movie_Name, Movie_Duration for all movies released in 2001

 */

INSERT INTO MOVIE VALUES(1011, 'The Test', 2001, 110, 10.5, 3003, 2009);

SELECT Movie_Name, Movie_Time FROM MOVIE where Release_Year = 2001;

/**

 * Task 13: Write a query to display the list of all the Movie_Names with Movie_Time in the range of 100 – 200 minutes in Descending order.

 */

 SELECT Movie_Name, Movie_Time FROM MOVIE where Movie_Time  BETWEEN 100 and 200 order by Movie_Time desc;/**

 * Task 14: Write a query to count the total number of movies in the Movies table.

 */

 SELECT COUNT(*) FROM MOVIE;

/**

 * Task 15: Write a query to display the Director_Name and the total number of Movies produced by each Director in ascending order.

 */

 Select CONCAT(CONCAT(d.Director_FName, ' '),d.Director_Lname) as Name, count(m.Director_Id) from movie m, Director d

 where d.Director_Id = m.Director_Id  group by d.director_id order by Name;

/**

 * Task 16: Write a query to delete the record of the Director whose firstvname is ‘James’ and last name is ‘Alex’. Make sure to insert some data that satisfy the criteria before executing the query

 */

 delete from DIRECTOR where DIRECTOR_Fname = 'James' and Director_Lname = 'Alex';

/**

 * Task 17: Write a query to display all the movies written by Director “James Cameroon”. Make sure to insert some data that satisfy the criteria before executing the query. 

 */

 SELECT m.* FROM MOVIE m, DIRECTOR d where m.director_id = d.Director_id and d.DIRECTOR_Fname = 'James' and d.Director_Lname = 'Cameroon';

/**

 * Task 18: Write a query to display each Genre_Class and the total number of movies belonging to each category. 

 */

 SELECT g.Genre_Class, Count(m.Genre_Id) FROM GENRE g, Movie m where g.genre_id=m.genre_id group by g.genre_id; 

/**

 * Task 19: Write a query to display all the Movie Names with ‘Animated’ as the Genre. 

 */

 Select m.Movie_Name FROM MOVIE m, GENRE g 

 WHERE g.genre_id = m.genre_id and g.Genre_Class = 'Animated';

/**

 * Task 20: Write a query to display all the Movie Names and the first name of the director who directed the movie in ascending order by Director_Fname. 

 */

 SELECT m.Movie_Name, d.DIRECTOR_Fname from MOVIE m, DIRECTOR d

 WHERE d.director_id = m.director_id ORDER BY d.DIRECTOR_Fname;

/**

 * Task 21: Write a query to display all the Genre_Class with no Movie name associated

 */

 Select g.Genre_Class from GENRE G

 WHERE genre_id NOT IN (SELECT m.genre_id from MOVIE m);

/**

 * Task 22: Write a query to display the movies with minimum and maximum Planned_Budget.

 */

 SELECT * FROM MOVIE

WHERE Planned_Budget = (SELECT MAX(e2.Planned_Budget) FROM MOVIE e2) OR

      Planned_Budget = (SELECT MIN(e2.Planned_Budget) FROM MOVIE e2);

/**

 * Task 23: Write a query to display all the Movie_Names and their director names.

 */

 SELECT m.Movie_Name, CONCAT(CONCAT(d.Director_FName, ' '),d.Director_Lname) as Director_Name

 FROM MOVIE m, DIRECTOR d where m.director_id = d.director_id;

/**

 * Task 24: Write a query to display the details of the movie including, the Movie_Name, Planned_Budget, Movie_Genre and the Director details. 

 */

 Select m.*, CONCAT(CONCAT(d.Director_FName, ' '),d.Director_Lname) as Director_Name, g.Genre_Class 

 FROM MOVIE m, DIRECTOR d, GENRE g where m.director_id = d.director_id

 AND g.genre_id = m.genre_id;

Summary

While working on this assignment, I learnt a lot of things. I learnt how to create a database and use MYSQL Workbench for handling databases. We have created a database using MYSQL workbench, and created related tables. At last, we have also created various queries to fetch the data according to various conditions from the tables.