DML-and-DDL (SQLite)

Question 1

SPECIAL ANNOUNCEMENT

Don’t feel like you are being tricked - you are NOT. These flash cards contain SQL queries that are very legitimate, so that any person learning SQL would be expected to answer these. Some people can do this faster, some people need a cup of coffee and more time - but please do not feel like you are being tricked. Practice, practice, practice!

Answer 1

Please use these flashcards in good health and good happiness!

Question 2

Create a table of actors that looks like this:

actors( [PK] actor_movie_id INT, firstname TEXT NOT NULL, lastname TEXT, moviename TEXT NOT NULL, salary INTEGER)

and fill it with one entry where you specify all the attributes.

Then, fill it with one entry where you do only specify the required attributes (firstname, moviename) and leave all non-required attributes blank.

Answer 2

To create the table we use this:

CREATE TABLE IF NOT EXISTS
actors
(
    actor_movie_id INTEGER PRIMARY KEY,
    firstname TEXT NOT NULL,
    lastname TEXT,
    moviename TEXT NOT NULL,
    salary INTEGER
);

To fill it with an actor using all the attributes, we use this:

INSERT INTO 
    actors
VALUES
( 
    42, 
    "Wayne",
    "John",
    "Rio Grande",
    100000
 );

To fill it with one row where we only give the needed (NOT NULL) attributes we use this:

INSERT INTO 
    actors
    (firstname, moviename)
VALUES
( 
    "Madonna",
    "Desparately Seeking Susan"
);

Notes:

(1) You will see John Wayne has a PK of 42 created by us.
(2) Madonna has a PK of 43, created by SQL because the PRIMARY KEY label tells SQLite to “autoincrement” the PK
(3) It was not necessary to use IF NOT EXISTS but it never really hurts so it is very good practice!

Question 3

Some actors are very famous like Tom Cruise who starred in “Top Gun;” we say that Tom Cruise is an “A-List” actor. Some actors and not so famous like John McAvoy who starred in “X-Men;” we say that John MyAvoy is a “B-List” actor.

Create a table of actors with 4 attributes where all attributes (actor_id, firstname, lastname, list_status) are mandatory, and where list_status defaults automatically to “B-List” if it is not specified.

Then, add Tom Cruise to this name with all this attributes. Then, add John McAvoy to this table but do not specify list_status.

Answer 3

If the table already exists from a previous question, we have to eliminate it:

DROP TABLE actors;

Now we create the needed table:

CREATE TABLE IF NOT EXISTS
actors
(
    actor_movie_id INTEGER PRIMARY KEY,
    firstname TEXT NOT NULL,
    lastname TEXT,
    list_status TEXT DEFAULT "B-List"
);

To add in Tom Cruise as an A-List actor we use

INSERT INTO 
    actors
VALUES
( 
    4, 
    "Tom",
    "Cruise",
    "A-List"
 );

Now to add in John McAvoy without specifiying the details we use

INSERT INTO 
    actors
    (firstname, lastname)
VALUES
( 
    "John",
    "McAvoy"
);

Notes:

(1) When we added Tom, he had a primary key of 4. When John was added without specifying a primary key, SQL automatically auto-incremented the value and assigned him a primary key of 5
(2) When we added Tom, we set his status to “A-List.” When we added John we did not specify a list_status, but SQLite defaulted (DEFAULT) to the value of “B-List.”

Question 4

You are a database expert and you think that your software developer friends are sloppy, careless people who make lots of mistakes who abuse and ruin your beautiful databases that took you so long to create!

Some actors are very famous like Tom Cruise who starred in “Top Gun;” we say that Tom Cruise is an “A-List” actor. Some actors and not so famous like John McAvoy who starred in “X-Men;” we say that John MyAvoy is a “B-List” actor.

But, those careless software developers are writing applications that put GARBAGE into your beautiful database. In particular, instead of writing “A-List” or “B-List” they sometimes write “A-Liste” or “D-List”, even though the only two valid terms are “A-List” and “B-List.”

But as a database person, you have POWER!

Create a table of actors with 4 attributes where all attributes (actor_id, firstname, lastname, list_status) are mandatory, and where list_status defaults automatically to “B-List” if it is not specified.

But . . . write your query so that the ONLY two values are “A-List” and “B-List” and all other values will result in an error!

Then, add Tom Cruise to this name with all this attributes. Then, add John McAvoy to this table but do not specify list_status. Then, add Jason Mamoa but try to add him with “C-List” and see what happens!

Answer 4

This is similar to a previous exercise.

If we want a missing value to have a default, we use the keyword DEFAULT; DEFAULT is what we call a “constraint.” If we want to ensure that a database attribute has certain properties or values, we use a different constrained, called CHECK.

First, we create the table - be sure to look at how we are using the CHECK keyword:

CREATE TABLE IF NOT EXISTS
actors
(
    actor_movie_id INTEGER PRIMARY KEY,
    firstname TEXT NOT NULL,
    lastname TEXT NOT NULL,
    list_status TEXT DEFAULT "B-List" 
        CHECK (list_status = "A-List" OR list_status = "B-List")
);

Now we insert Tom with all his attributes:

INSERT INTO 
    actors
VALUES
( 
    42, 
    "Tom",
    "Cruise",
    "A-List"
 );

Now we insert John, leaving out a few attributes:

INSERT INTO 
    actors
    (firstname, lastname)
VALUES
( 
    "John",
    "McAvoy"
);

Finally, we “pretend” we are as sloppy and careless as our software development friends, and we intentionally try to label Jason with “C-List,” because this is a mistake our sloppy, careless friends could easily make:

INSERT INTO 
    actors
    (firstname, lastname, list_status)
VALUES
( 
    "Jason",
    "Mamoa",
    "C-List"
);

Notes;

(1) Tom, no issues.
(2) John, no issues. He should have an autoincremented primary key, and by default his list status should be “B-List” even though we did not provide it.
(3) Jason. Yes, Jason. Jason should NOT be in the database. in fact, SQLite should have given us an error message that looked like this:

Error: CHECK constraint failed: list_status = “A-List” OR list_status = “B-List”

This gives us a good feeling. We have protected the high quality of our database, and our software developer friends who are sloppy and careless will not be able to corrupt our beautiful database with garbage!