Objectives
- Write an exam test.
- Write a trigger in a database.
Motivation
Today's lab excercise is primarily devoted to an exam test. Test is done through Moodle.
Termin for reparation of exam test is on 12.week. Note that only last attempt score is valid, therefore if you decide to do reparation exam test you will have score from it, even if it is less than first exam test.
Exam in first part will contain 4 theoretical test question from lectures up to Subqueries topic. Questions are either test options (one or more possible correct answers, even if it is allowed to choose more answers it does not mean that the question has several correct answers) or short text answer (one textbox) where it is necessary to write for example some keyword or letters.
Second part of exam will be practical and you will get 3 questions with task, where solution will be some SQL select statement. Your answer to question will be entered into moodle exam, where it will be automatically tested against sample database. As part of a question there will be information about database structure and also connection username and password for connecting to sample database, where you can check your SQL Select using SQL Developer. You CANNOT use internet or any other form of communication either personal (another student next to you) or electronic (mobile, facebook). Violation of these rules may be reffered to Disciplinary Board. Be careful, you must not enter any comment when entering SQL into Moodle - only ONE SELECT.
Before starting exam, try to setup new connection in SQL Developer, as you will need it during exam and you cannot use internet during exam.
Some important instructions that must by applied during the exam:
- Students will store all their belongings in specified place out of their reach. Mobiles must be TURNED OFF! If someone is expecting urgent call, you can go and make it but you will be allowed back only next week for reparation exam term.
- Exam can be done only on "school" computers. If there is not enough computers for everybody, use as much computers as you can and remaining students will wait outside of the room and will switch after first group is finished.
- Student who completes exam, should close browser, signout from OS, hands over all notes he/she has made, leaves room and wait for everybody else to finish. Then everybody gets back - the lab continues.
- Correct answers can be consulted with instructor, but only after the exam.
Instructions
Step 1
Task 1.1
As instructed by the instructor, log in to Moodle and into exam and solve all questions.
Comment
In the questions where you should write SELECT, write only SELECT without semicolon (;) at the end and without any comments.
Step 2
If there is enought time after exam, you can try to solve next task. Triggers serve to automatically respond to various events that may happend in database system. With triggers we can:
- generate derived values,
- automatically create database access logs,
- enforce data integrity,
- etc.
With triggers we can specify in database, that if some special event occurs it is possible to act upon it (e.g.: verify whether the data entered does not violate integrity, or create a log of the event, etc.).
At the last laboratory excercise you have created procedure to delete selected invoice with all invoice lines on it. Today we start by writing a trigger that will allow you to delete invoice directly using the SQL DELETE statement.
Task 2.1
Write the trigger that will bind to event of deleting from table Invoice. If there should be deleted some invoce, your trigger should also delete all invoice lines from this invoice. Thanks to that reference integrity will be kept intact.
Comment
To verify the correct implementation, insert new data into the Invoice and InvoiceLine table, which could be deleted afterwards. You can use buyTracks procedure, which you have created last time.
Additional Tasks
Task A.1
Chinook
Get size of 'Grunge' playlist in bytes and its dollar price. Suppose that price of playlist is sum of prices of all tracks from playlist. For verification: price should be 14.85.
Task A.2
Chinook
Which playlists do not include any song from artists 'Black Sabbath' and 'Chico Buarque'? For verification: there are 13 playlists.