MySQL:DDL & DML:Tasks

1. Start up MySQL (see )

'''2. Before we start using the DDL & DML commands we need to see what databases and tables exist and look at the structure of existing tables. Technically these are Database Administrative tasks which do not fit into either the DDL or the DML categories. Try the following:'''

a. SHOW TABLES; what happens and why?

b. SHOW DATABASES; Record the result.

c. USE mysql; what happens this time? (Note that the mysql database is a special one and MUST NOT be tampered with, or you may stop MySQL working!!)

d. SHOW TABLES; Record the result (just first two tables).

e. To show the structure of a tables we use the DESCRIBE command. Using the names of the tables displayed view the structure of some of the tables. e.g DESCRIBE func;

Creating a test database
'''3. Lets try some DDL commands (remember these are for creating, modifying and deleting databases and tables). Try the following:'''

a. CREATE DATABASE dbTest1; Now try using the database and seeing if any tables exist, write down the commands that you used.

b. Now USE the database to allow us to create some tables.

c.	Now create a table called tblUsers with attributes of Identifier, Name and Password using:

d. Now check that you have been successful, what command did you use?

e. Create another table called suppliers with the following attributes (fields)

f. Create another database called dbTest2; and make this the active database. What command did you use?

g. Now create a table called tblHobbies (within the dbTest2 database) with a HobbyId, HobbyName and HobbyDescription using:

h. Now check that you have been successful, what command did you use?

i. Now create a table to store car parts that shows the use of the following data types: INT UNSIGNED, DECIMAL (used for currency), DATE, and VARCHAR.

j. Now drop the newly created database. Did you have to drop the table first?

k. We will look at modifying table structures at a later lab.

VideoShop Database
'''4. Now it’s time to think about some DML commands, to do this we really need some populated tables to work with, so to save you time I have created an SQL script that will automatically create some tables for you (called VideoShopSQL.txt). Right click on the link (VideoShopSQL.txt) and download the file to your computer. Open the file in notepad and have a look at the code which shows the structure and content of the tables. Make sure you have an understanding of the different tables and the fields within them.'''

a. What does DML mean?

b. What the four most regularly used DML commands?

c. Now using the MySQL command line client create a database called VideoShop.

d. Make the database the active database( USE VideoShop;).

e. Now use the SOURCE command to run the file MovieShopSQL.txt in notepad. (Note there may be two errors generated (ERROR: Unknown Command '/M' and ERROR: Unknown Command '/M') at the start - ignore these errors). I have placed SHOW TABLES in the script to show you where it gets to - they are not needed!

f. The commands within it should generate six populated tables for you to play with.

'''5. Before we get into using the DML commands, let us check the structure of the tables that should have been generated by using a couple of DDL commands. Try the following commands and beside each describe what the command does and for c through g also write the number of fields in each table.'''

a. USE videoshop;

b. SHOW TABLES;

c. DESCRIBE booking;

d. DESCRIBE rental;

e. DESCRIBE supplier;

f. DESCRIBE category;

g. DESCRIBE Customer;

'''6. OK, now it’s time for DML commands. Start by trying the following SELECT statements, making sure that you describe the results beside each command:'''

a. SELECT * FROM tblBooking;

b. SELECT * FROM tblRental;

c. SELECT * FROM tblCategory WHERE CatDescription =’Drama’;

d. SELECT CustName FROM tblCustomer WHERE CustId > 8922;

e. SELECT CustDiscount FROM tblCustomer WHERE CustName = ’C Story’;

f. SELECT MovTitle FROM tblMovie WHERE MovSupplierId = ’VWEST’;

'''7. You are making excellent progress, now it’s time for you to write you own SELECT statements. Best try before you record your answer. What statements would you use to do the following tasks:'''

a. List the addresses of all customers

b. List the Title of all movies from the ABC category

c. List all the description of all categories which have a cat code commencing with the letter higher D

d. List the title of all movies with purchase price of more than $30

'''8. Now it’s time to try using the INSERT command to add records and the UPDATE command to modify existing records. Try the following to make sure that you are comfortable with the syntax:'''

a. INSERT INTO tblCustomer VALUES (9999, 'M Lowe', '1 Kent Tce.','345-5434',487.5, 10);

b. INSERT INTO tblMovie VALUES (0011,’Dark Knight','DRA',6,3,40,5,'VWEST');

c. UPDATE tblCustomer SET CustName = "S Corich" WHERE name = "C Story";

d. UPDATE tblMovie SET MovSupplierId = "VWEST" WHERE MovieId = 0001;

e. UPDATE tblCategory SET CatDescription = "Comic" WHERE Cat = "COM";

9.	Now it’s time you fended for yourselves a bit, next to each task write the command that does the following:

a. Changes the movie category for the movie with a MovieId of 0004 to CHI

b. Changes the SupplierId for all movies which have a SupplierId of VWEST to VEAST

c. Increases the price of all movies by 50%.

d. Insert a customer with the following details

8999, F Abertyn, 2 Loop Rd, 8565-7886, 125.75, 20

e. Insert a new category  SCI, SciFi

f. Delete the customer with a CustId of 8922

g. Delete the supplier with a SupplierId of ABC (what would you have to watch out for before doing this?)

h. Insert a record in the rental table with the following details

8999, 0003, 1, 2007-09-06

That’s all for now, you have covered the basics of DDL and DML well done :)