Read this before starting: Copy the queries in a text file and submit it on slate. Label the queries properly and do not send me (only) screenshots of the query.
Question#01
Download the “employees.sql” script from slate and run it using the mysql console. After running the script you will get the following tables:
Page 1 of 3
Database Systems Lab
INSERT data in each of the tables.
Add a couple of employees and a couple of departments.
Add an existing employee in a department by inserting data into the dept_emp table.
Add salaries for the added employees.
Add employee titles and managers of the departments.
UPDATE a number of records. For Example:
Change department of an employee.
Update an employees’ salary.
Change the name of a department.
Change an employees’ job title.
Change an employees’ hire date.
DELETE a couple of records. For example:
DELETE every employee who was hired before February 2016.
DELETE all departments. etc.
Question#02
Consider the following Tables:
p_id
p_name
units
unit_price
type
s_id
1005
Ponstan
100
15
Tablets
312
1421
Brufen
25
35
Syrup
657
3215
Avil
122
26
Syrup
478
1215
Flagyl
42
30
Tablets
987
7513
Avil
140
20
Injection
478
1216
Flagyl
10
35
Syrup
987
1007
Disprin
98
15
Tablets
320
Table 1: products [p_id(P.K), s_id(F.K)]
s_id
s_name
contact
city
320
Munir Brothers
0321-1234567
Karachi
312
Alliance Pharmaceuticals
0313-7654321
Peshawar
478
Abbot Pharmaceuticals
0300-9876543
Lahore
657
Sanofi Aventis
0333-5632476
Islamabad
987
Ferozsons laboratories
0301-1934257
Peshawar
Page 2 of 3
Database Systems Lab
Table 2: suppliers [s_id(P.K)]
order_id
customer_name
order_date
22
Waleed Ali
11/25/2014
23
Azhar Akbar
12/02/2014
24
Shahzeb Khan
12/05/2014
25
Javed Iqbal
01/15/2015
26
Tariq Khan
06/23/2015
Table 3: orders [order_id(P.K)]
p_id
order_id
units_purchased
1007
22
5
1216
22
1
1005
22
4
1421
23
1
1005
23
1
3215
23
2
7513
23
3
1421
24
2
1215
24
1
1005
25
5
1215
26
1
1421
26
3
Table 4: order_detail [(p_id, order_id)(P.k)]
Create a database named “pharma”.
Create tables with the primary and foreign key constrains as shown (with on cascade update and delete).
Insert the data as shown.
Change the name of supplier named Munir Brothers to Muneer Brother.
Delete the Avil syrup product from the product table, does it affect order_detail table? If yes then how? If no then why?
Set unit_price to 40 for all the products whose type is “Tablets”.
Delete the order with the order _id 22 from the orders table, does it affect order_detail table? If yes then how? If no then why?
Delete all records from all 4 tables.
Delete all 4 tables.
Page 3 of 3
