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.  Do NOT use joins or subqueries on these problems!!

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, although there is an example in our lab that demonstrates creating a view.)
  3. Student ID
    Student Name
    38214
    Letersky
    54907
    Altvater
    66324
    Aiken


  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 (DDL) commands for the following:
  6. a. How would you add an attribute, Class, to the Student table?
    b. How would you remove the Registration table?
    c. How would you change the FacultyName field from 25 characters to 40 characters?
  7. Write SQL commands for the following:
  8. a. Write two different forms of the INSERT command to add a student with a student ID of 65798 and last name Lopez to the Student table.
    b. Now write a command that will remove Lopez from the Student table.
    c. Create an SQL command that will modify the name of the course ISM 4212 from "Database" to "Introduction to Relational Databases". 
    d. Did you get an error or a warning when you tried to do this?  If so, why?  How might you address this?
  9. Write SQL statements to answer the following questions:
  10. a. Which students have an ID number that is less that 50000?
    b. What is the name of the faculty member whose ID is 4756?

    Before doing the next query, you will have to re-create the REGISTRATION table and INSERT the original values.
    c. What is the smallest section number used in the first semester of 2008?
  11. Write SQL queries to answer the following questions:
  12. a. How many students are enrolled in Section 2714 in the first semester of 2008?
    b. Which faculty members have qualified to teach a course since 1993?  List the faculty ID, course, and date of qualification.
  13. Write SQL queries to answer the following questions:
  14. a. Which students are enrolled in both Database and Networking?    (Hint: use SectionNo for each class, so that you can determine the answer from the Registration table by itself.  Similarly, display the StudentID, because that is also in the Registration table..) Do this without a join:  use GROUP BY and HAVING.) 


    b. Which instructors can teach either Syst Analysis or Syst Design, but cannot teach both?

    c. Now re-execute the query, and you should have the Faculty ID for Someone listed in the result.  (You will not have the actual name 'Someone' listed, because that would require a join, which is not required for this homework.

  15. Write SQL queries to answer the following questions:
      1. a. What are the courses included in the Section table? List each course only once.  Since we are not using joins, just display the CourseID.

        b. List all students in alphabetical order by Student Name

        c. List the students who are enrolled in each course in Semester I, 2008.  Group the students by the sections in which they are enrolled.  (Hint:  "Group" is an English word that may or may not correspond to the SQL GROUP clause.)

        d. List the courses avaialble.  Group them by course prefix.  (ISM is the only prefix shown, but there are many others throughout the university.) e. How many courses of each prefix are being offered?  (See many hints below....)

  16. 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.