SQL Exercises: CREATE and INSERT
Create Exercises
These exercises require you to create a table in the database, and insert records into it. To do this you will need to get familiar with the following:
- Reading 📖: The CREATE Keyword
- Reading 📖: PostgreSQL Data Types
- Reading 📖: PostgreSQL Constraints
Some tips before you start
If at any point you need to reset the database, you can do so by finding the movies container id with docker container ls
.
You can then stop the container with docker stop CONTAINER_ID
.
Once the container is stopped you can remove it with docker container rm CONTAINER_ID
. After this you can simply re-run the run command and create a new movies docker container.
You may also need to delete your local connection in pgAdmin, then create the connection again.
Let's write some SQL!
Create and Insert Exercises
-
Write a statement to create a simple table called
rating
. It should include the columnsrating_id
,rating_value
,title
andcontent
. -
Write a statement to insert a record into your
rating
table. Confirm that the record exists withSELECT * FROM rating
. -
Remove the
rating
table withDROP TABLE rating
. Now write a statement to create the same table again, none of the fields should be able to be null. Confirm that this is the case by attempting to insert a record with null values. -
Remove the
rating
table and create it again. This time make sure that only values from0
to10
can be inserted for therating_value
field. -
Delete and recreate this table. This time, in addition to previous valid
rating_value
requirement, make sure that no duplicate data against columnrating_id
will be allowed at the time of insertion. -
Now recreate the table, but this time make the
title
aunique
field. This can be used to prevent duplicate data. -
Finally, recreate the table with the
rating_id
as aprimary key
with auto increment. Insert a few records into the table to confirm that it is auto-incrementing this column. -
Write a statement to create a table named
reviewer
. It should have the columnsreviewer_id
,reviewer_name
,registered_date
,points
. You should make sure thatpoints
that a reviewer can have is a value between 0 and can't exceed 10000. -
Write a SQL statement to change (or ALTER) the rating table and add a new Foreign Key Column called
reviewer_id
to theratings
table. Set the default toNULL
. -
Drop the
rating
table altogether and this time ensure thereviewer_id
foreign key is part of the initial create statement.
Insert Exercises
- Reading 📖: PostgreSQL INSERT keyword
Create the following table in your database:
-
Write a SQL statement to insert a record with your own values into the
ratings
table against each column. -
Write a SQL statement to insert 3 rows by using a single INSERT statement.