Vellore Institute of Technology

Fall Semester 2023

SUBMITTED BY-

21BCT0188
HARSHAL RANJHANI

Database Systems Lab

Digital Assignment 1
Exercise 1:

Creating a student Schema.

1. Add a column Email_id

description after adding the email column:

2. Remove the column Address

description after removing the address column:

3. Modify the column Phone with size 10.

description of the table after changing the phone size:

4. Rename a column Phone by Mobile_no.

description after renaming:

5. Rename the table name STUDENT by STUDENTS

description of students table:

6. Insert 10 students records in STUDENTS table.

- Inserting one student:
Table after inserting all 10 students -

7. Show the table description

8. Display all the records.

9. Delete all the records

10. Drop the table.

Exercise 2:

Creating an employee Schema.

Creating a department Schema.

Creating a project Schema.

1. Insert the data given in employee, department and project tables.

- Employee table after adding all the data:
 - Department table after adding all the data:

- Project table after adding all the data:

2. Display all the employees’ information.

3. Display Employee name along with his SSN and Supervisor SSN.

4. Display the employee names whose bdate is ’20-JUN-1983’.

5. Display salary of the employees without duplications.

6. Display the MgrSSN, MgrStartDate of the manager of ‘Finance’ department.

7. Modify the department number of an employee having fname as ‘Joyce’ to 5.

8. Alter Table department add column DepartmentPhoneNum of NUMBER data

type and insert values into this column only.

description after adding the column:

 Insert into that column:

Similarly, we can add values in each row.

9. Alter table department to modify the size of DepartmentPhoneNum.

description after altering:

10. Modify the field name DepartmentPhoneNum of the departments table to PhNo.
 description after altering:

11. Rename Table Department as myDept.

12. Alter Table department remove column PhNo.

13. Create a table COPYOFDEPT as a copy of the table myDept.

14. Delete all the rows from COPYOFDEPT table.

15. Remove COPYOF DEPT table.

Exercise 3:

Execute the following Query on the Db to display and discuss the integrity constraints violated
by any of the following operations.

1. Insert ('Robert', 'F', 'Scott', '235', '21-JUN-1990', 'Bangalore', M, 58000, '100', 1 ) into
EMPLOYEE.

Here we can see that the employee sex is given to be M which does not match the data
type of char. Hence we get the error.

2. Insert ( 'ProjectF', null, 'Chennai', 3 ) into Project.

 Here we can see that the project number is given to be null. Since the project number is
the primary key, it cannot be null. Hence we get the error.

3. Insert ( 'ProjectF', 1234, 'Chennai', 4 ) into Project.

Since all the inputs align with the required data types, we get no error in this case.

Alter the tables to:

1. Add Foreign Keys using Alter Table [if not done earlier].
2. Make name of Project as Unique and sex of employee as not null.

3. Make salary of employee to accept real values.

Since the column is not empty, it can’t be modified, nevertheless we can use the above
command to do the same.
Exercise 4:

1. Creating the client_master and product_master tables:

2. Client_master after adding all the data:

Product_master after adding all the data:

3. Retrieving records from a table
a. Find out the names of all the clients

b. Retrieve the entire contents of the Client_Master table

c. Retrieve the list of names, city and the state of all the clients.
 d. List the various products available from the Product_Master table.

e. List all the clients who are located in Mumbai.

4. Updating records in a table

a. Change the City of Clientno ‘C00001’ to ‘Chennai’.

b. Change the Baldue of Clientno ‘C00005’ to Rs.1000 .

c. Change the cost price of ‘shirts’ to Rs. 500.

d. Change the QOH of the ‘T-shirts’ to 150.

5. Deleting records in a table

a. Delete all products from Product_Master where the quantity on hand is equal to
100.

b. Delete from Client_Master where the column state holds the value “Tamilnadu”.

c. Delete all clients from Client_Master where the Baldue is equal to 0.

6. Altering the table structure

a. Add a column call Profitpercent of datatype number10,2 to the Product_Master.

b. Change the size of sellprice column in Product_Master to 10,2.

c. Change the name of the column UnitMeasure to UM in Product_Master.

d. Drop the column UM.

 e. Drop the table Client_Master along with its data.

Exercise 5: Operators in SQL -

1. Find the employee names having a salary greater than Rs.45000.

2. Find the employee names whose salary lies in the range between 35000 and 75000.
3. Find the employees who have no supervisor.

4. Display the employee names having ‘salt lake’ in their address.

5. Display the department name that starts with ’H’.

6. Display the project numbers along project name that ends with ‘i’ in project location.
7. Display the names of all the employees having supervisor with any of the following SSN
123, 533.

8. Display all the employee details based on the salary in descending order.

9. Display the employees belongs to Chennai or vellore.

10. Display the department details in ascending order of department number

11. Display all the MgrSSN and MgrStartDate as ManagerSSN and Manager_DOJ.

12. Display all the male employees with SSN and salary.