Insert Commands

Let's assume that we want to insert a new movie into our database. We do this by saying we want to INSERT INTO Movie, we use the keyword values and we simply list the values we want to insert.

Like so:

INSERT INTO Movie VALUES(109, 'Cinderella', 1950);

In this query we are inserting values for the attributes of the Movie table. We included a mID, Title, and Year. We did not include a Director, so thus there will be a null value in the database for that attribute of Cinderella.

Now let's do a little more complicated insert command. Now that we have Cinderella in our database, lets add a few users into the Review table and have them rate the new movie. We're going to start by inserting two new users into the User table.

Like so:

INSERT INTO Review VALUES(209, 'Jonathan Murleau')
INSERT INTO Review VALUES(210, 'Barabara Vance');

We now have two new users but they have not yet rated on any movies, so let's have them rate on our new movie Cinderella.

We now want to return all users where there mID does not appear in the Review table. Like so:

SELECT *
FROM User
WHERE uID NOT IN (SELECT uID FROM Review);

Our query would be the result of the two users that we just inserted: Jonathan and Barbara. Well now that we have singled out the users who have not rated yet, lets insert those users into the Review table with the correct schema.

Like this:

INSERT INTO Review
SELECT uID, 109, 3, null
FROM User
WHERE uID NOT IN (SELECT uID FROM Review);

This query is saying to select all users who do not already appear in the Review table. We're going to take these users and we're going to select their uID, then we are going to assign them the correct values for all of the Review attributes. As a reminder, the Review table has the attributes: uID, mID, Rating, and ratingDate. So in our query we are selecting their uID, then having the rate the mID of 109 (which is the movie Cinderella), they both are going to rate the movie with a 3, and we inserted a null value for the ratingDate. Lastly, we are going to take this end result, and INSERT INTO the Review table.