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!!  I will take off points if you use joins of subqueries! The point of this homework is to exercise certain clauses and skills.  Using joins/subqueries may allow you to work around that, and that defeats the purpose of this homework.

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.  You may use PHPMyAdmin, or the terminal, or, if you are using WorkBench or any other interface, that's fine too.  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, create a new database ("registration"), copy and paste the DDL and INSERT statement for the tables from the file in the link given above, but you still have to create the database first). 
    Students using the VM:  Although the Company database is already installed on the VM, the registration database is not, so you will have to create it.
  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

  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.  What is the statement/clause in the DDL that enforces this relationship?

  5. Write SQL (DDL) commands for the following:
  6. a. How would you add an attribute, GPA, to the Student table?  Make sure that your data type accommodates an appropriate GPA.
    b. How would you remove the Student table?
    c. How would you change the StudentName 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 12345 and last name Lapin to the Student table.
    b. Now write a command that will remove Lapin 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?  Or, is there something "not right" about the course name in the database?  If so, why?  How might you address this?
  9. Write SQL statements to answer the following questions:
  10. a. Which faculty have an ID number that is less that 4000?  List the faculty name(s)
    b. What is the name of the student whose ID is 66324?
    c. What is the largest section number used in the first semester of 2008?
  11. Write SQL queries to answer the following questions:  (remember:  no joins or subqueries!)
  12. a. How many faculty are qualified to teach ISM 3113?  Just display the number.
    b. Who are the faculty who are qualified to teachISM 3113?  Display the faculty ID number.
    c. Which faculty members have qualified to teach a course before 1995?  List the faculty ID, course, and date of qualification.
  13. Write SQL queries to answer the following questions: (remember:  no joins or subqueries!)
  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, since we are not using joins in this homework.  Similarly, display the StudentID, because that is also in the Registration table..) Do this without a join or subquery:  use GROUP BY and HAVING.) 

    b. Which instructors can teach either Syst Analysis or Syst Design, but cannot teach both? (Again, you should reference the CourseID, which appears in the Qualified table.)

    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/allowed for this homework.  But it should display the FacultyID 1111.)

  15. Write SQL queries to answer the following questions: (remember:  no joins or subqueries!)
      1. a. What are the courses included in the Registration table? List each course only once.  Since we are not using joins, just display the CourseID.

        b. List all Facutly in alphabetical order by FacultlyNam

        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 available.  Group them by course prefix.(Again, "group" is just a word.  It doesn't necessarily correspond to SQL's GROUP BY.)    (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 5.11 on p. 201 of the pdf file, which corresponds to p. 170 of the text itself.  Please note:  do NOT answer Question 5.10 in the text.  Answer Question 5.11, which begins at the very bottom of the page and continues on to the next page.