SQL Refresher with Cheat sheet—Basic DDL Queries

Are you looking to brush up your SQL skills? If so, you’ve come to the right place! There are five categories of SQL queries — DDL, DML, DQL,DCL and TCL.

DDL (Data Definition Language) is a subset of SQL commands that are used to define or modify the structure of a database. Most common DDL commands are CREATE, ALTER, DROP, RENAME and TRUNCATE. Let’s quickly review these commands with a fun exercise in Microsoft SQL Server. So, let’s get started!

We need to first create an empty database.

CREATE DATABASE tempdb;

Now let’s craft some queries to add tables to this newly created database! Create two tables- MOVIES and DIRECTORS with constraints shown below.

CREATE TABLE movies_T1 (
 movieId int PRIMARY KEY, --PK = movieid. Each value in this column is unique as each value uniquely identifies the entire row. 
 title varchar(35) NOT NULL, --all values in title column are strings of no more than 35 characters. This columns will not accept Null values. 
 releaseDate date NOT NULL, --releaseDate values should not be null
 director int NOT NULL, --director values should not be null
 score decimal(3,2), --movie score is a decimal value of 3 digits in total and 2 digits after decimal point
 CONSTRAINT chk_score_movie CHECK (score BETWEEN 0 AND 5) --movie score values in this column should be between 0 to 5
);

CREATE TABLE directors_T2 (
 directorId int PRIMARY KEY,
 firstName varchar(20) NOT NULL,
 lastName varchar(30) NOT NULL
);

If you noticed we missed defining some constraints, so we use ALTER command to do the following:

• Modify the movies table to create a foreign key constraint that refers to table directors
• Modify the movies table to create a new constraint so the uniqueness of the movie title is guaranteed.

--FK contraint will ensure values in director column in Movies table should be present in directorId in Directors table
--One to Many relation between the two tables as one director in directors table can direct many movies 
ALTER TABLE movies_T1 
 ADD CONSTRAINT fk_movies_directors FOREIGN KEY (director) REFERENCES directors_T2(directorId);

--constraint to ensure all movies have a unique title
ALTER TABLE movies_T1
 ADD CONSTRAINT uq_movies_title UNIQUE (title);

Now, let’s fill both the tables with some values. Due to foreign key constraint you won’t be able to fill movies table first as the director table is empty. So, populate the director table and then populate movies table.

INSERT INTO directors_T2 VALUES
 (1010, 'Bob', 'Doe'),
 (1020, 'Jill', 'Condon'),
 (1050, 'Josh', 'Fooley'),
 (2010, 'Ted', 'Mckenzie'),
 (3020, 'Lucy', 'Bell');

--Note: when entering date literals in SQL it is a good practice to put the year first, then month, then day. 
INSERT INTO movies_t1 VALUES 
(110, 'The Lion King 2', '2022-12-02', 3020, 3.50),
(200, 'Beauty and the Beast', '2017-03-12', 1050, 4.20),
(300, 'Toy Story 4', '2019-04-25', 1020, 4.50),
(400, 'Mission Impossible', '2018-06-27', 2010, 5.00),
(500, 'The Secret Life of Pets', '2016-07-02', 1010, 3.90);

--run select statement to verify that all above data was populated
SELECT * FROM movies_T1;
SELECT * FROM directors_T2;

Create a new empty table tempMovies with the same data as table movies. Use a single statement to create the table and insert the data at the same time.

SELECT * INTO tempMovies FROM movies_T1;

--Note: The above query does not apply all constraints like PK or FK to the new table.
--When we run below queries, a record with duplicate movieId=110 along with the same movie title (which is unique in movies table) gets populated in this table.
SELECT * from tempMovies;
INSERT INTO tempMovies VALUES 
(110, 'The Lion King 2', '12-02-2022', 3020, 3.50);
SELECT * from tempMovies;

Now let’s delete all the records in table tempMovies using TRUNCATE command. You may remember using DELETE command to do the same thing but there are a lot of differences between TRUNCATE and DELETE.

TRUNCATE TABLE tempMovies;

DROP command is used to delete tables but we need to ensure referential integrity rules are followed. directors table cannot be dropped first because a column in that table acts as a foreign key for another table movies.

--we have to delete movies table first and then dircetors table
DROP TABLE directors_T2;
DROP TABLE movies_T1;

Here is a cheat sheet of this exercise for your future reference. Hope it helps!

If you found this article useful….

Please do like and share your feedback in the comments.