Exercising the SELECT Statement
CSCI 2910-001 In-Class Exercise

You should be able to...

After today's lecture and this in-class exercise, you should have a better understanding of the power of the select statement. This includes WHERE, BETWEEN, IN, LIKE, NOT, arithmetic operators, LIMIT, and joining tables.

Creating the Relational Database for this Exercise

Let's begin this set of exercises by creating a set of related tables. The set of commands below will delete the "mylibrary" table you created during our last exercise and replace it with a larger table with slightly different fields. Two other tables will also be created: "genre" and "status". The fields of the tables are as follows:

"mylibrary" Table

Field Name Data Type
TITLE VARCHAR(50)
AUTHOR VARCHAR(35)
PUB_YR YEAR(4)
PRICE DECIMAL(6,2)
GENRE CHAR(1)
INDX CHAR(5)
STATUS TINYINT

"genre" Table

Field Name Data Type
ID CHAR(1)
GENRE_DESC VARCHAR(30)
LOCATION CHAR(3)

"status" Table

Field Name Data Type
ID TINYINT
STATUS_DESC VARCHAR(30)

Copy the code shown below into your favorite text editor, then save it as an SQL script with the file extension ".SQL".

DROP TABLE mylibrary;
CREATE TABLE mylibrary (TITLE VARCHAR(50), AUTHOR VARCHAR(35), PUB_YR YEAR(4), PRICE DECIMAL(6,2), GENRE CHAR(1), INDX CHAR(5), STATUS TINYINT);
INSERT INTO mylibrary VALUES ('Catcher in the Rye, The', 'J.D. Salinger', 1951, 6.99, 'F', 'SAL00', 1);
INSERT INTO mylibrary VALUES ('Animal Farm', 'George Orwell', 1946, 7.95, 'F', 'ORW00', 1);
INSERT INTO mylibrary VALUES ('To Kill a Mockingbird', 'Harper Lee', 1960, 6.99, 'F', 'LEE00', 3);
INSERT INTO mylibrary VALUES ('Lord of the Flies', 'William Golding', 1954, 7.95, 'F', 'GOL00', 1);
INSERT INTO mylibrary VALUES ('Of Mice and Men', 'John Steinbeck', 1939, 8.34, 'F', 'STE00', 2);
INSERT INTO mylibrary VALUES ('One Flew Over the Cuckoos Nest', 'Ken Kesey', 1963, 7.99, 'F', 'KES00', 0);
INSERT INTO mylibrary VALUES ('Fahrenheit 451', 'Ray Bradbury', 1953, 6.99, 'F', 'BRA00', 2);
INSERT INTO mylibrary VALUES ('Road Ahead, The', 'Bill Gates', 1996, 14.99, 'T', 'GAT00', 2);
INSERT INTO mylibrary VALUES ('Computer Organization', 'Carl Hamacher', 2001, 132.81, 'T', 'HAM00', 1);
INSERT INTO mylibrary VALUES ('7 Habits of Highly Eff. People', 'Stephen R. Covey', 1989, 10.20, 'P', 'COV00', 1);
INSERT INTO mylibrary VALUES ('How to Win Friends & Infl. People', 'Dale Carnegie', 1990, 7.99, 'P', 'CAR00', 3);
INSERT INTO mylibrary VALUES ('Linux Bible, 2005 Edition', 'Christopher Negus', 2005, 26.39, 'T', 'NEG00', 0);
INSERT INTO mylibrary VALUES ('Linux For Dummies, 6th Edition', 'Dee-Ann LeBlanc', 2005, 20.79, 'T', 'LEB00', 0);
INSERT INTO mylibrary VALUES ('Carrie', 'Stephen King', 1974, 6.95, 'F', 'KIN00', 1);
INSERT INTO mylibrary VALUES ('Jaws', 'Peter Benchley', 1974, 6.99, 'F', 'BEN00', 2);
INSERT INTO mylibrary VALUES ('158-Pound Marriage, The', 'John Irving', 1973, 6.99, 'F', 'IRV00', 1);
INSERT INTO mylibrary VALUES ('World According to Garp, The', 'John Irving', 1978, 7.99, 'F', 'IRV01', 3);
INSERT INTO mylibrary VALUES ('Hobbit, The', 'J.R.R. Tolkien', 1965, 7.99, 'F', 'TOL00', 0);
INSERT INTO mylibrary VALUES ('Bag of Bones', 'Stephen King', 1999, 7.99, 'F', 'KIN01', 0);
INSERT INTO mylibrary VALUES ('Cat in the Hat, The', 'Dr. Seuss', 1957, 8.99, 'C', 'SEU00', 1);
INSERT INTO mylibrary VALUES ('Oh, the Places You''ll Go!', 'Dr. Seuss', 1990, 16.99, 'C', 'SEU01', 0);
INSERT INTO mylibrary VALUES ('If You Give a Mouse a Cookie', 'L. Joffe Numerof', 1985, 10.39, 'C', 'NUM00', 1);
INSERT INTO mylibrary VALUES ('If You Give a Moose a Muffin', 'L. Joffe Numerof', 1991, 11.95, 'C', 'NUM01', 2);
INSERT INTO mylibrary VALUES ('If You Give a Pig a Pancake', 'L. Joffe Numerof', 1998, 12.95, 'C', 'NUM02', 1);
INSERT INTO mylibrary VALUES ('Cell', 'Stephen King', 2006, 12.95, 'A', 'KIN02', 4);
INSERT INTO mylibrary VALUES ('On Writing', 'Stephen King', 2000, 12.95, 'A', 'KIN03', 2);
INSERT INTO mylibrary VALUES ('Terms of Endearment', 'Larry Mcmurtry', 1987, 13.95, 'A', 'MCM00', 1);

DROP TABLE genres;
CREATE TABLE genres (ID CHAR(1), GENRE_DESC VARCHAR(30), LOCATION CHAR(3));
INSERT INTO genres VALUES ('F', 'Fiction', 'B-2');
INSERT INTO genres VALUES ('T', 'Technical', 'A-3');
INSERT INTO genres VALUES ('P', 'Personal Growth', 'B-1');
INSERT INTO genres VALUES ('C', 'Childrens', 'C-1');
INSERT INTO genres VALUES ('A', 'Audio tapes/CDs', 'A-4');

DROP TABLE status;
CREATE TABLE status (ID TINYINT, STATUS_DESC VARCHAR(30));
INSERT INTO status VALUES (0, 'On shelf');
INSERT INTO status VALUES (1, 'Checked out');
INSERT INTO status VALUES (2, 'Renewed');
INSERT INTO status VALUES (3, 'Overdue');
INSERT INTO status VALUES (4, 'Processing');

Using FTP, upload it to the Einstein server, then from the MySQL command line, execute the script using the command:

\. mysqlscript.sql

where "mysqlscript.sql" is the filename you used to save your script under.

Creating SELECT Queries

The sole purpose of this exercise is to create select statements to perform specified tasks. For each of the queries described below, create the SQL statement. You may test them from the command line of MySQL to determine their validity. In the end, however, you will be including all five statements you develop in a single script to be uploaded to me using the Blackboard Drop Box.

Once you have tested your select statements, put all five statements in order in a single script. Save the script as "zabc123.sql" where zabc123 is your user id and upload them to my to my Blackboard account using the drop box.

Developed by David Tarnoff for his students in CSCI 2910 at ETSU