COMP 353-453
Homework #3
DDL and Simple SQL Queries

Simple means using one relation, mostly.  It doesn't mean "not difficult", although they generally less difficult than queries accessing more than one table.
You will have to look up (possibly google) some of the DDL that you will need.

Look at the figure of the StudentRegistration schema.  It has some sample data. For our purposes, let's assume that it is the entire database.

After you create the database, you may want to back it up.  You will probably want to keep a working copy where you will apply changes, and then a backup copy with the original version.

For questions 1-9, you will run your queries on the MySQL database.  Copy and paste your queries into a Word (or similar) document, and copy a picture of your results.  Submit your Word document on Sakai for this assignment.

  1. Using either PHPMyAdmin or from the command line, creat a new database, write and implement the DDL and the Insert Statements to create and populate this database.  Assume the following attribue data types:   (actually, you can copy and paste from the file in the link given above).
  2. Use SQL to define the following view:  (use CREATE VIEW.  Google it if you have to.)
  3. Student ID
    Student Name

  4. Because of referential integrity, before any row can be entered into the SECTION table, the CourseID to be entered must already exist in the COURSE table.  If you have not already enforced this constraint in your DDL, do so now.  (What is the statement/clause that enforces this relationship?)
  5. Write SQL commands for the following:
  6. Write SQL commands for the following:
  7. Write SQL statements to answer the following questions:
  8. Write SQL queries to answer the folowing questions:
  9. Write SQL queries to answer the following questions:
  10. Write SQL queries to answer the following questions:
  11. Complete Problem 3.11 on page 80 of your text.  Just write the answers on paper.  For the 7th edition of the text, it's problem 5.11 on p. 201.