Homework #3

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.

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

Student ID	Student Name
38214	Letersky
54907	Altvater
66324	Aiken

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?
Write SQL (DDL) commands for the following:

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? (Don't really remove the STudent table. Just write the command so that I know that you know how.)
c. How would you change the StudentName field from 25 characters to 40 characters?

Write SQL commands for the following:

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?

Write SQL statements to answer the following questions:

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?

Write SQL queries to answer the following questions: (remember: no joins or subqueries!)

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.

Write SQL queries to answer the following questions: (remember: no joins or subqueries!)

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

Recall that in Question 5, you changed the name of the Database course to Introduction to Database Programming. Whatever its current name, that's the course you're interested in. Note that there is only one student who should be included in the query response, which is student number 54907.
This question is a big challenge. It would help if you list the StudentID and the Count(*) in your Select clause. Big hint: You're going to need the Count later. This query is not as straightforward as you might think. It helps to recall that a WHERE clause only looks at one row at a time. There does not exist any single row in the table where a student is enrolled in both DB and Networking at the same time. Clearly, you will have to find a way to look at multiple rows at one time. Think of what we learned that references more than one row at a time.

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

In our data, there are no faculty who teach only one but not the other. So add a tuple to Faculty and to Qualified:

For the Faculty table, insert FacultyID 1111 and FacultyName 'Someone'
For Qualified, insert (1111,'ISM 3113', '1996-09-01')

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

Write SQL queries to answer the following questions: (remember: no joins or subqueries!)

You will have to extract the left three characters of the CourseID, and then GROUP BY those left three characters. This is because you want one row for each prefix. That row should have the prefix and the count for that prefix.

To extract the left three characters, use LEFT(CourseID,3). If CourseID is 'ISM 3113', then the leftmost 3 characters are 'ISM'.

In your SELECT statement, you will want to display two things: the LEFT(CourseID,3), and then also the Count(*).

You will also want to be sure to GROUP BY the LEFT(CourseID,3) in your GROUP BY statement. Otherwise, you will get an error for trying to combine a scalar and an aggregate. Unless you GROUP BY Left(CourseID, 3), it will not be an aggregate, and therefore it can't work together with COUNT, which is an aggregate.

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.