Task#02 INSERT, UPDATE & DELETE SOlution

$30.00

Description

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

  1. INSERT data in each of the tables.

    1. Add a couple of employees and a couple of departments.

    1. Add an existing employee in a department by inserting data into the dept_emp table.

    1. Add salaries for the added employees.

    1. Add employee titles and managers of the departments.

  1. UPDATE a number of records. For Example:

    1. Change department of an employee.

    1. Update an employees’ salary.

    1. Change the name of a department.

    1. Change an employees’ job title.

    1. Change an employees’ hire date.

  1. DELETE a couple of records. For example:

    1. DELETE every employee who was hired before February 2016.

    1. 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)]

  1. Create a database named “pharma”.

  1. Create tables with the primary and foreign key constrains as shown (with on cascade update and delete).

  1. Insert the data as shown.

  1. Change the name of supplier named Munir Brothers to Muneer Brother.

  1. Delete the Avil syrup product from the product table, does it affect order_detail table? If yes then how? If no then why?

  1. Set unit_price to 40 for all the products whose type is “Tablets”.

  1. 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?

  1. Delete all records from all 4 tables.

  1. Delete all 4 tables.

Page 3 of 3


error: Content is protected !!