Programme : [Link].
Course : BCSE302L: DBMS Lab
Faculty : Dr. L.M. Jenila Livingston
Date: 16/12/2024
Exercise –2
Consider the database for an organization and create the following
tables.
o department (dept_no, dept_name, location).
o employee (emp_no PRIMARY KEY, emp_name NOT NULL,
DOB, address, doj, mobile_no, dept_no references
department(dept_no), salary number(9,2), check().
Write the queries for the following
1. Add necessary constraints to the tables
• (Primary key, NOT NULL, check (salary>50,000), foreign key)
2. Modify the employee table by adding designation attribute with
check constraint (starting with EM)
3. Modify the employee table by adding gender attribute and set the
default value “Male”
4. Delete the address attribute from employee table
5. Add 5 rows in the employee and department tables
Date format(‘dd/mon/yyyy’ eg ‘01/jan/2023’)
6. Validate the entries by entering
(a) duplicate dept_no – department table
(b) duplicate emp_no – employee table
(c) null value for emp_name – employee table
(d) salary 25000 – employee table
(e)new dept_no value in employee table
(dept_no should not be available in department table)
(f) Insert employee with designation not starting with EM
(g) Update employee salary to negative value
(h) Delete a department referenced by employee (FK violation)
7. Display all the records from employee and department tables
8. Display the emp_no and emp_name of all the employees from
department no ‘d02’
9. Display the mobile number of ‘John’
10. Delete the employee record whose mobile_no=9999955555
11. Update the salary with 10% hike
12. Update the salary with 1% hike to all female employees
13. Display the empno and name of all employees whose salary is
between 60000 and 70000
14. Display emp_no, emp_name, designation, dept_no and salary in
the descending order of salary
15. Display emp_no, emp_name, designation, dept_no and salary in
the ascending order of salary