Objectives
- Submit your assignment.
Motivation
Today's laboratory class is once again dedicated to submitting and checking your assignment. If you are finished with your assignment check by your instructor, use the remaining time to practice SQL.
Instructions
Step 1
Your submission for this week must include:
-
entity-relational model of your domain (also included in last submission, corrected in case of problems),
-
logical relational model (also included in last submission, corrected in case of problems),
-
SQL DDL script for creation of database tables (also included in last submission, corrected in case of problems),
-
script for filling database (sequence of INSERT statements), so that yeach table has at least 5 meaningful records (you can take an ispiration from real world),
-
script for creation of 7 views and textual comment explaining each of a view as task description (e.g.: 'Get all users with first name Milan'):
- 2 views with simple not trivial select from just one table,
- 3 views with joining tables (1x joining of 2 tables, 1x joining of 3 tables, 1x outer join),
- 2 views with aggregate function and/or group by
try to design tasks (views) to provide an interesting solution in the given domain, e.g. for Kosice travel system it would be nice to get solution for question about next buses of line 72 from 'Mier' to 'Lingov' if actual time is '13:33' (it is a real life situation).
Remember that you have to have it prepared either on school DB server or on your laptop during time of lab with all the tables, data and views working.
Step 2
If you already have been checked for your submission, you can use the time to solve following tasks:
Task 2.1
What was your invoice income in May 2013? For verification: it should be 37.62.
Comment
Remember that the 'total' column should have been removed in previous task, therefore you cannot use that column.
Task 2.2
Get an invoice with id = 2 and it's lines in the form: number of invoice, name of track, name of artist (not composer), title of album, unit price, name of customer (concatenated first name and last name in one column). For verification: there are 4 tracks on that invoice.
Task 2.3
What composer is the most popular for customer Wyatt Girard? Get a list of composers bought by this customer sorted by the number of songs purchased by each of composers. For verification: most favourite composer by that customer was 'Mike Bordin, Billy Gould, Mike Patton' with 3 bought tracks.
Resources
- Lecture 11: Transactions
- Lecture 12: Views
- Lecture 13: Sequences
- Lecture 14: Indexes
- Use the index, Luke! (great online tutorial)
Additional Tasks
Task A.1
Chinook
Using a subquery, find out the first name, last name, and birthdate of the oldest employee. Resulting query compare with solution using only joining of tables.