To create your assignment database, download and run the DDL and DML files identified in the next two paragraphs - they will create the tables you need for Assignment 1 (this will not disturb the other tables you normally use in your lab sessions). Then create 11 SQL statements in response to the 11 requirements (in bold, non-italicized text) below. Comment these SQL statements thoroughly in your own words to demonstrate your knowledge of SQL and how your statements meet the business requirements (comments will be worth marks). Before submitting your set of 11 SQL's, TEST them to ensure they are running properly. Your SQL's must run under a "generic" ID, where the only preparation was to download and run the DDL and DML scripts. Any SQL's that do not run successfully, for any reason, will be heavily docked.
Download and save your DDL (Data Definition: table creation) file m415ddl1.sql (from the Assignment 1 object). Run this file with the @ command to create your tables. Investigate your newly created tables with the DESC tablename command.
Download and save your DML (Data Manipulation: data-row insertion) file m415dml1.sql (from the Assignment 1 object). Run this file with the @ command to insert data into your tables. Investigate the data content of these tables with the SELECT command (if required, set your linesize and pagesize appropriately, using SET LINESIZE xx and SET PAGESIZE yy commands).
To see a rough ERD diagram of this database, download the jpg image of the data model (from the Assignment 1 object). In this rough diagram, primary keys are highlighted, but foreign keys, though present, are not given special highlighting. In this diagram, attributes that are bolded must have data and non-bolded attributes may contain null.
Enrolling a Student at AcmeTech:
You've learned enough SQL commands to help one of our potential students. Jerry Tyler is a CIBC employee who's interested in the courses AcmeTech offers. You'll need to use some of the SQL commands we've covered to date in order to fulfill her requests - I'm sure you'll do well.
1) To start her selection process, Jerry needs a list of all the courses we offer. Write a SQL query that will retrieve (from left to right) each course's id, title, subject id, effective date, expiry date, cost and duration, sorting the output by subject id in ascending order, then by effective date in descending order, then by course id in ascending order
2) After reviewing the courses available, Jerry has decided to enroll into either course 401, 501 or 601, but only where the offering's start date is after May 9, 2014. You need to find all the course offerings that meet these conditions. You should include (in the following order) the columns offering_id, course_id, site id, start date and the maximum number of students that an offering can hold.
3) Jerry has decided she'd like to be enrolled in course 501, offering 9213, if there's room. Check if there is room in offering 9213. Do this by writing a query to list all the students currently attending that offering (by retrieving from left to right: the student_id, registration-date and amount-paid columns from the appropriate table). Give the registration-date column the title REGSTRN_DATE in your result. Display the three-letter abbreviation of month, followed by the two-digit day, followed by all four digits of the year. To separate the month, day and year, use a slash: ie: / .
4) The attendance table uses the student_id. However, Jerry has forgotten her ID number, so you'll have to look it up from the student table. You should use her first name and gender to find her number, but she's not sure if she's registered under the first name Jerry or Jerri. Write a query to retrieve (in the following order) her last name, first name, date of birth, gender and id. Your WHERE clause should include rows where the gender code means "female" and the first name starts with the letters 'JERR'. Assume the gender and first-name columns on the database hold either all-upper or all-lower-case data - ensure your query will find Jerry regardless of either case.
5) Now that we have Jerry's student_id, we can enroll her into the course by adding her enrollment to the attendance table. Use the INSERT statement to add a row with her student id, desired offering id, and an amount paid of 475. Do not enter data in any other fields unless they are mandatory. Assume today's date and time for your registration date and for the 'Update_ts' field.
Each month, the Accounting Department at Acme sends out invoices to students. Your job is to assist the Accounts Receivable Manager, Jarvis, with this task. As a start, he needs to list the tuition fee for every course offered to start before Oct 27, 2014. To do this, follow the instructions below and use your recent knowledge on SQL techniques to help him with this request and others.
6) First, retrieve the offering_id, start_date, title and cost columns (in order from left to right) from the joined course and offering tables. Note that your WHERE clause should ensure that you retrieve every course offering that was started before Oct 27, 2014.
7) Now retrieve, from the attendance table only, one row for each unique student ID, each row containing (in left to right order) the sum of the amounts paid by that student, the total count of attendance-rows for that student, and the date of the student's earliest registration. Use column aliases and table aliases. Order the result by the total count of attendance rows in ascending order, then by sum of amounts paid in descending order, and then by the date of the student's earliest registration, in descending order.
Good. From here on, Jarvis would like to focus on only three offerings: 9111, 9112, and 9212.
8) Your next step is to restrict your previous query to include only those attendances whose offerings are needed by Jarvis. In other words, build on your question 7 query to include only the attendances whose offerings are 9111, 9112 or 9212 - keep everything else the same.
9) Jarvis has stated that, since some students may have attended more than one course offering, we need to determine which students, on average, paid less than $410 (these students might need to receive invoices). Build on your question 8 query to add an "average_paid" displayed column, and then further restrict the result-table to those students whose average amount paid is less than $410.
10) Jarvis has also indicated that if any student is to be sent an invoice, they must also be permitted, if they wish, to discuss possible exemptions with any lecturer of sufficient experience to understand the student's course history and course-load. Therefore, students identified in the previous SQL can only be sent an invoice if any lecturer (at all) exists who was hired before the date of the student's earliest registration. Build on your question 9 SQL to ensure that students are selected only if at least one lecturer exists who was hired before the (already displayed) date of the student's earliest registration.
11) With the current database, your result should indicate that one student still needs to complete their payment. To complete your task, you need to build on your question 10 SQL to also display the student's names and their associated company-name. Regarding the student's names: company policy requires that on invoices, the student's name be made out with the following format: The last name, followed by an underscore , followed by the first letter of the first name (e.g. Davis_V). Use the necessary character functions in your SQL statement to retrieve the desired above information. Column aliases and table aliases should be used.
Good. Jarvis will use this information to prepare invoice(s) for these student(s).