Objectives
- Submit your assignment.
Motivation
Today's laboratory class is 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
This submission of assignment requires:
- entity-relation model of your domain topic,
- logical relational schema,
- SQL DDL script for creating database tables.
Step 2
If you already have been checked for your submission, you can use the time to solve following tasks:
Task 2.1
Show the first name and last name of all customers, who are from Czech Republic. For verification, it should be 2 records.
Task 2.2
List all invoices to United Kingdom in May 2013. About invoice show only date and address in form "address city, postalCode" (this will result in string in one column). For verification, there will be record:
| invoicedate | address |
|---|---|
| 01.05.13 | 202 Hoxton Street London, N1 5LH |
Task 2.3
List all employees showing first name, last name, birthdate and age of each employee. For verification: the resulting list will contain record (age using date March 5th 2018):
| firstname | lastname | birthdate | age |
|---|---|---|---|
| Andrew | Adams | 18.02.62 | 56 |
Step 3
The lectures already mentioned joining of tables, so we'll start lightly with that...
Task 3.1
List the titles of all U2 albums. For verification: there should be 10 rows.
Resources
Additional Tasks
Task A.1
Company
Increase salary for all employees except from 'Kosice' by 15%.
Task A.2
Company
Insert the following data in the Vacation table:
INSERT INTO Vacation VALUES (11, 2016, 40,23);
INSERT INTO Vacation VALUES (22, 2016, 35, 10);
INSERT INTO Vacation VALUES (33, 2016,30,2);
INSERT INTO Vacation VALUES (44, 2016,35,5);
INSERT INTO Vacation VALUES (55, 2016,40,35);
INSERT INTO Vacation VALUES (66, 2016,40,0);
INSERT INTO Vacation VALUES (77, 2016,30,0);
INSERT INTO Vacation VALUES (88, 2016,40,27);
INSERT INTO Vacation VALUES (99, 2016,45,12);
INSERT INTO Vacation VALUES (100, 2016,30,4);
INSERT INTO Vacation VALUES (11, 2015, 40,35);
INSERT INTO Vacation VALUES (22, 2015, 35, 35);
INSERT INTO Vacation VALUES (33, 2015,30,30);
INSERT INTO Vacation VALUES (44, 2015,35,30);
INSERT INTO Vacation VALUES (55, 2015,40,35);
INSERT INTO Vacation VALUES (66, 2015,40,40);
INSERT INTO Vacation VALUES (77, 2015,30,30);
INSERT INTO Vacation VALUES (88, 2015,40,37);
INSERT INTO Vacation VALUES (99, 2015,45,42);
INSERT INTO Vacation VALUES (100, 2015,30,28);
Create a new table Vacation2015, to which you will insert ev_number of employee and number of days of not spend vacation from 2015. In the new table there should be only employees which have some remaining vacation from 2015. Do this with a single statement.