Mis 630

Individual HW 1

1. Select the title of all movies.

SELECT Title

FROM Movies;

2. Show all the distinct ratings in the database.

SELECT DISTINCT Rating

FROM Movies;

3. Show all unrated movies.

SELECT *

FROM Movies

WHERE Rating IS NULL;

4. Select all movie theaters that are not currently showing a movie.

SELECT *

FROM MovieTheaters

WHERE Movie IS NULL;

5. Show the titles of movies not currently being shown in any theaters.

SELECT Title FROM Movies

WHERE Code NOT IN

(

SELECT Movie FROM MovieTheaters

WHERE Movie IS NOT NULL

);

6. Add the unrated movie "One, Two, Three".

INSERT INTO Movies(Title,Rating) Values(‘One,Two,Three’,NULL);

7. Set the rating of all unrated movies to "G".

UPDATE Movies SET Rating=’G’

WHERE Rating IS NULL;

8. Remove movie theaters projecting movies rated "NC-17".

DELETE FROM MovieTheaters WHERE Movie IN

(SELECT Code FROM Movies WHERE Rating = ‘NC-17’);

Individual HW 2

1. Select the name of all the pieces.

SELECT Name

FROM Pieces;

2. Select all the providers' data.

SELECT *

FROM Providers;

3. Obtain the average price of each piece (show only the piece code and the average price).

SELECT Piece, AVG(Price)

FROM Provides

GROUP BY Piece;

4. Obtain the names of all providers who supply piece 1.

SELECT Name

FROM Providers

WHERE Code IN

(SELECT Provider FROM Provides WHERE Piece = 1);

5. Select the name of pieces provided by provider with code "HAL".

SELECT Name

FROM Pieces

WHERE EXISTS

(

SELECT * FROM Provides

WHERE Provider = ‘HAL’

AND PIECE = Pieces.Code

);

6. Add an entry to the database to indicate that "Skellington Supplies" (code "TNBC") will provide sprockets (code "1") for 7 cents each.

INSERT INTO Provides

VALUES ( 1,’TNBC’,7);

7. Increase all prices by one cent.

UPDATE Provides SET Price = Price +1;

8. Update the database to reflect that "Susan Calvin Corp." (code "RBT") will not supply bolts (code 4)....