In this exercise, you will need the Project-database, which is described in Here . You can initialize the Project database like this:
- Download the file project.sql
- Execute the code from the file in your SQL-client
SELECT and WHERE / Project database
Make Query which,...
- Shows all data from all fields in person-table.
- Shows firstnames and lastnames in person table in alphabetical order based to lastname.
- Shows all cities from person-table, but same code city be mentioned only once.
- Shows all data from person-table if the firstname is Jim.
- Shows all data from person-table if the city is Turku and birthyear is bigger than 1980.
- Shows all data from project-table if the place is NULL
- Shows all data from project-table if the place is NOT NULL
- Shows firstname, lastname and age of each person. Note you have to calculate the age based on birthyear and current year.
WHERE LIKE
- Shows all data from person-table if last name begins with the letter s
- Shows all data from person-table if city begins with the letter t
AGGREGATE FUNCTIONS / Project database
- The aggregate functions are: sum, avg, min, max and count (and group_concat in MySQL).
- Remember that if, you put regular fields and aggregate functions to SELECT statement, you should allways add GROUP BY statement to your code.
- If you have to make some selections from your data, you have to options: WHERE and HAVING. They have a different meaning: WHERE is used before grouping and HAVING is used after grouping.
Write Queries which,
- Shows maximum salary and minimum salary
- Shows the number of rows in project table
- Shows average of birthyears
- Shows the amount of persons which lives in Turku
- Shows sum of salary of persons which lives in Turku
Write Queries which will retrieve below views. Notice the column headers
+----------+-----------------+
| city | Sum of Salaries |
+----------+-----------------+
| TURKU | 127 |
| HELSINKI | 75 |
| TAMPERE | 34 |
+----------+-----------------+
+----------+------------------+
| city | Average Salaries |
+----------+------------------+
| TURKU | 42.33 |
| HELSINKI | 37.50 |
| TAMPERE | 34.00 |
+----------+------------------+
+------------+----------------+
| id_project | SUM(work_hour) |
+------------+----------------+
| 101 | 1300 |
| 102 | 700 |
| 103 | 200 |
| 104 | 900 |
+------------+----------------+
-
The same as above, but if Sum of hour is <500, project is not included in view
+------------+----------------+
| id_project | SUM(work_hour) |
+------------+----------------+
| 101 | 1300 |
| 102 | 700 |
| 104 | 900 |
+------------+----------------+
-
+----------+----------------------+
| city | person |
+----------+----------------------+
| HELSINKI | River,Simpson |
| TAMPERE | Daniels |
| TURKU | Morrison,Smith,Jones |
+----------+----------------------+
-
+----------+---------------------+
| place | project |
+----------+---------------------+
| NULL | Statistics |
| HELSINKI | Billing,Store |
| KUOPIO | Customers |
| TURKU | Bookkeeping,Selling |
+----------+---------------------+
JOIN / Project database
Make Query which,...
- Print the person lastnames and the amount of their working hour.
- Print the person lastnames and the sum of their working hour.
- Print the pnames of the project and the sum of their working hour.
- Print the person lastnames , project pnames and and the amount of their working hour.
- Print the pnames of the project where Morrison has been working.
- Print the project pname and person lastname and the amount of their working hour; the project without hour should be included (105 and 106)
SubQuery / Project database
Make Query which,...
- Print the lastnames of person who live in the same city as Morrison.
- Print the pnames of project which lies in the same city where Morrison lives.
- Print the pnames of the project where Morrison has been working.
NOTE : You will need two subqueries
- The inner subquery returns the person_id of Morrison ( person-table )
- The outer subquery gets the person_id of Morrison and returns the corresponding project_id's
- The mainquery gets the project_id and returns the corresponding project pnames
- NB! the mainquery gets several project_id values (because Morrison has been working on several project ) , so you must use the the word IN in your WHERE
Union / Project database
Note! The rows doesn't have to be exactly in the same order as they are below.
- Print this table
+----------+------+
| city |Amount|
+----------+------+
| HELSINKI | 2 |
| TAMPERE | 1 |
| TURKU | 3 |
| TOTAL | 6 |
+----------+------+
- Print this table
+------------+-------------+-------------+
| id_project | pname | sum of hour |
+------------+-------------+-------------+
| 101 | Bookkeeping | 1300 |
| 102 | Billing | 700 |
| 103 | Store | 200 |
| 104 | Selling | 900 |
| PROJ | TOTAL | 3100 |
+------------+-------------+-------------+
- Print this table (you can do this also using SELECT IF )
+-------------+----------+
| pname | place |
+-------------+----------+
| Bookkeeping | TURKU |
| Billing | HELSINKI |
| Store | HELSINKI |
| Selling | TURKU |
| Customers | KUOPIO |
| Statistics | NO PLACE |
+-------------+----------+
- Print this table
+----------+---------------------+
| place | project |
+----------+---------------------+
| NULL | Statistics |
| HELSINKI | Billing,Store |
| KUOPIO | Customers |
| TURKU | Bookkeeping,Selling |
+----------+---------------------+
- Print this table
+----------+---------------------+
| place | project |
+----------+---------------------+
| HELSINKI | Billing,Store |
| KUOPIO | Customers |
| TURKU | Bookkeeping,Selling |
| No Place | Statistics |
+----------+---------------------+
INSERT, UPDATE, DELETE / Project database
Make Query which will
- Add below person to person table in database project
firstname=Jim
lastname=Harrington
city=HELSINKI
birthyear=1981
salary=42
- Update the city of above person to TURKU
- Delete above person
Procedures
Read the examples about procedures and transactions from here
- Make a Stored Procedure named addHours. That procedure should add a given amount of hours to given person(id_person) to given project (id_project). So, you should be able to call it like this:
CALL addHours(x, y, z).
Where
- x is the id_person
- y is the id_project
- z is the amount of hours
You can test your procedure with below values
- CALL addHours(9999,101,500); -> should not success
- CALL addHours(201,101,500); -> should success
- CALL addHours(201,105,500); -> should success
- Make a Stored Procedure named addHoursNew. That procedure should work in the same ways as addHours, but if the x and y already exists in the hour-table, it will add the amount to the existing amount.
In order to test the procedure, execute first this command:
DELETE FROM hour where id_project='105';
And then execute several times CALL addHoursNew(201,'105',500);
Referential Integity
- Create a database named integrity_example
- Create a table named customer with below code
CREATE TABLE customer(
idCustomer INT PRIMARY KEY,
customerName VARCHAR(255)
);
- Create a table named product with below code
CREATE TABLE product(
idProduct INT PRIMARY KEY,
productName VARCHAR(50),
customerId INT,
CONSTRAINT customer_product
FOREIGN KEY(customerId) REFERENCES customer(idCustomer)
ON DELETE RESTRICT ON UPDATE CASCADE
);
- Add two customers with the code
INSERT INTO customer VALUES(1,'Jim Jones');
INSERT INTO customer VALUES(2,'Ann Smith');
- Add two products with below code
INSERT INTO product VALUES(1,'Mobile phone',1);
INSERT INTO product VALUES(2,'Laptop',2);
Check that you managed to add the data.
- Try to add a product with the code
INSERT INTO product VALUES(3,'Laptop',3);
You didn't managed to add the last product. What is the reason?
- Update the id of Jim Jones with below code
UPDATE customer set idCustomer=100 WHERE idCustomer=1;
- Check the data of table customer and table product. Note the idProduct values
- Try to delete Jim Jones with the command
DELETE FROM customer WHERE idCustomer=100;
You didn't managed to add the last product. What is the reason?
- Update the referential integrity rule with below code
ALTER TABLE product DROP CONSTRAINT customer_product;
ALTER TABLE product ADD CONSTRAINT customer_product
FOREIGN KEY(customerId) REFERENCES customer(idCustomer)
ON DELETE CASCADE ON UPDATE CASCADE;
- Try again to delete Jim Jones with the code
DELETE FROM customer WHERE idCustomer=100;
- Check the data from both tables. The product Moblie phone should have been deleted.