DBMS Slip

 Create a RDB in 3 NF with  appropriate data types and Constraints. 

Emp(eno ,ename ,designation ,salary, Date_Of_Joining)

Dept(dno,dname ,loc)

The relationship between Dept & Emp is one-to-many.

Constraints: - Primary Key, ename should not be NULL, salary must be greater than 0.

Consider the above tables and Execute the following queries:

1. Add column phone_No into Emp table with data type int.

2. Delete the details of Employee whose designation is ‘Manager’.

1.Display the count of employees department wise.

2. Display the name of employee who is ‘Manager’ of “Account Department”.

3. Display the name of department whose location is “Pune” and “Mr. Advait” is

working in it.

4. Display the names of employees whose salary is greater than 50000 and department

is “Quality”.

5. Update Dateofjoining of employee to ‘15/06/2019’ whose department is ‘computer

science’ and name is “Mr. Roy’.

 Slip 2 . Create a RDB in 3 NF with  appropriate data types and Constraints. 

Sales_order (ordNo, ordDate)

Client (clientNo, ClientName, addr)

The relationship between Client & Sales_order is one-to-many.

Constraints: - Primary Key, ordDate should not be NULL.

Consider the above tables and execute the following queries:

1. Add column amount into Sales_order table with data type int.

2. Delete the details of the clients whose names start with ‘A’ character.

Q4. Consider the above tables and execute the following queries:

1. Delete sales order details of client whose name is “Patil” and order date is

“09/08/2019”.

2. Change order date of client_No ‘CN001’ to ‘18/03/2019’.

3. Delete all sales_record having order date is before ‘10 /02/2018’.

4. Display date wise sales_order given by clients.

5. Update the address of client to “Pimpri” whose name is ‘Mr. Roy’.