Answer to Question #164267 in Databases | SQL | Oracle | MS Access for SP

Question

III. Query questions. (COVERING 11 ITEM, 67 marks IN TOTAL)

Help me

Please Read All Questions And Answer All 11 Question for me.

Table: Movies

Id   Title           Director         Year            Length_minutes
1   Toy Story        John Lasseter    1995                  81
2   A Bug's Life     John             1998                  95
3   Toy Story 2      John Lasseter    1999                  93
4   Monsters, Inc.   Pete Docter      2001                  92
5   Finding Nemo     Andrew Stanto    2003                  107
7   Cars             John Lasseter    2006                  117
9   WALL-E           Andrew Stanton   2008                  104
10  Up               Pete Docter      2009                  101
11  Toy Story 3      Lee Unkrich      2010                  103
12  Cars 2           John Lasseter    2011                  120
13  Brave            Brenda Chapman   2012                  102
87  WALL-G           Brenda Chapman   2042                  97

Table: Boxoffice

Movie_id    Rating    Domestic_sales        International_sales
5             8.2        380843261                555900000
12            6.4        191452396                368400000
3             7.9        245852179                239163000
9             8.5        223808164                297503696
11            8.4        415004880                648167031
1             8.3        191796233                170162503
7             7.2        244082982                217900167
10            8.3        293004164                438338580
4             8.1        289916256                272900000
2             7.2        162798565                200600000
13            7.2        237283207                301700000

1.      Create table Movies. It includes the domain of values associated with each attribute and integrity constraints. (7 marks)

2.      Write an SQL query that finds the title of each film. (5 marks)

3.      Write an SQL query that finds the movies released in the years between 2000 and 2010. (5 marks)

4.      Find all the WALL-* movies. (5 marks)

5.      List the last four Pixar movies released (ordered from most recent to least). (5 marks)

6.      Find the domestic and international sales for each movie. (6 marks)

7.      List all movies that were released in even number of years. (5 marks)

8.      Add the studio's new production, Toy Story 4 to the list of movies (you can use any director). (5 marks)

9.      The director for A Bug's Life is incorrect, it was actually directed by John Lasseter. (5 marks)

10.  This database is getting too big, let's remove all movies that were released before 2005. (5 marks)

11.  SELECT title, year FROM movies WHERE year < 2000 OR year > 2010; (6 marks)

Write the result:

Answer 1

1. Create table Movies. It includes the domain of values associated with each attribute and integrity constraints

create table Movies(
Id int not null primary key,
Title varchar(50) unique not null,
Director varchar(50),
Year int,
Length_Minutes int
);
-- insert given values into Movies table
insert into Movies values (1, 'Toy Story', 'John Lasseter', 1995, 81);
insert into Movies values (2, 'A Bug''s Life', 'John', 1998, 95);
insert into Movies values (3, 'Toy Story 2', 'John Lasseter', 1999, 93);
insert into Movies values (4, 'Monsters, Inc.', 'Pete Docter', 2001, 92);
insert into Movies values (5, 'Finding Nemo', 'Andrew Stanto', 2003, 107);
insert into Movies values (7, 'Cars', 'John Lasseter', 2006, 117);
insert into Movies values (9, 'WALL-E', 'Andrew Stanto', 2008, 104);
insert into Movies values (10, 'Up', 'Pete Docter', 2009, 101);
insert into Movies values (11, 'Toy Story 3', 'Lee Unkrich', 2010, 103);
insert into Movies values (12, 'Cars 2', 'John Lasseter', 2011, 120);
insert into Movies values (13, 'Brave', 'Brenda Chapman', 2012, 102);
insert into Movies values (87, 'WALL-G', 'Brenda Chapman', 2042, 97);

2. Write an SQL query that finds the title of each film.

select Title  -- select column Title
from Movies;   -- from Movies table

3. Write an SQL query that finds the movies released in the years between 2000 and 2010.

Select statement with *

select *   -- select statement with * to get all columns
from Movies  -- from Movies table
where year between 2000 and 2010; -- where condition to get the release year between 2000 and 2010

Select statement with column

select Title -- select column Title
from Movies  -- from Movies table
where year between 2000 and 2010; -- where condition to get the release year between 2000 and 2010

4. Find all the WALL-* movies.

Select statement with *

select * -- select statement with * to get all columns
from Movies -- from Movies table
where Title like 'WALL-%'; -- where condition to get all the WALL-* movies

Select statement with column

select Title  -- select column Title
from Movies -- from Movies table
where Title like 'WALL-%'; -- where condition to get all the WALL-* movies

5: List the four Pixar movies released(ordered from most recent to least)

   Title           Director         Year            Length_minutes
10  Up               Pete Docter      2009                  101
11  Toy Story 3      Lee Unkrich      2010                  103
12  Cars 2           John Lasseter    2011                  120
13  Brave            Brenda Chapman   2012                  102

6: Find the domestic and international sales for each movie.

Id   Title           Domestic_sales        International_sales
5   Finding Nemo      380843261                555900000
12  Cars 2            191452396                368400000
3   Toy Story 2       245852179                239163000        
9   WALL-E            223808164                297503696
11  Toy Story         415004880                648167031
1   Toy Story         191796233                170162503
7   Cars              244082982                217900167
10  Up                293004164                438338580
4   Monsters, Inc.    289916256                272900000
2   A Bug's Life      162798565                20060000
13  Brave             237283207                301700000

7: List all movies that were released in an even number of years.

Id   Title           Director         Year            Length_minutes
2   A Bug's Life     John             1998                  95
7   Cars             John Lasseter    2006                  117
9   WALL-E           Andrew Stanton   2008                  104
11  Toy Story 3      Lee Unkrich      2010                  103
13  Brave            Brenda Chapman   2012                  102

8: Add the studio's new production, Toy Story 4 to the list of movies

  Title           Director          
 Toy Story        John Lasseter    
 Toy Story 2      John Lasseter          
 Toy Story 3      Lee Unkrich         

Toy Story 4 John Lasseter

9: The director for A Bug's Life is incorrect, it was actually directed by John Lasseter.

Answer: yes, it is true it was actually directed by John Lasseter.

10.  This database is getting too big, let's remove all movies that were released before 2005.

Id   Title           Director         Year            Length_minutes
7   Cars             John Lasseter    2006                  117
9   WALL-E           Andrew Stanton   2008                  104
10  Up               Pete Docter      2009                  101
11  Toy Story 3      Lee Unkrich      2010                  103
12  Cars 2           John Lasseter    2011                  120
13  Brave            Brenda Chapman   2012                  102
87  WALL-G           Brenda Chapman   2042                  97

11.  SELECT title, year FROM movies WHERE year < 2000 OR year > 2010

Title Year
Toy Story 1995
A Bug's Life 1998
Toy Story 2 1999
Cars 2  2011
Brave 2012