RDBMS EXERCISES
RDBMS EXERCISES
EXERCISE 1:
Using Employee Database with two tables.
Employee_Personal_details as parent table with NOT NULL and UNIQUE constraints
for Employee_ID and NOT NULL for age with Employee ID as primary key. Also create
another table Employee_Salary_details as child table with reference of
Employee_ID as foreign key. Use DDL commands create and alter. Also create Also perform the following queries
(i) Determine the names of employee, who earn
more than 20000.
(ii) Determine the names of employees, who
take highest salary in their departments.
(iii) Determine the employees, who are located
at the same place.
(iv) Determine the employees, whose total
salary is like the minimum salary of any department.
(v) Determine the department which does not
contain any employees.
ANSWER:
EMPLOYEE-PERSONAL DETAILS TABLE:
CREATE TABLE
Employee_Personal_details ( Employee_ID INT PRIMARY KEY, Name VARCHAR(100) NOT NULL,
Age INT NOT NULL, Department VARCHAR(100), Location VARCHAR(100), UNIQUE
(Employee_ID) );
EMPLOYEE-PERSONAL DETAILS TABLE:
CREATE TABLE Employee_Salary_details
( Salary_ID INT PRIMARY KEY, Employee_ID INT, Salary DECIMAL(10, 2) NOT NULL, FOREIGN
KEY (Employee_ID) REFERENCES Employee_Personal_details(Employee_ID) );
QUERIES:
(i) Determine
the names of employees who earn more than $20,000:
SQL>
SELECT emp_name FROM
Employee_Personal_details WHERE Employee_ID IN (SELECT Employee_ID FROM Employee_Salary_details
WHERE salary > 20000 );
(ii) Determine
the names of employees who take the highest salary in their departments:
SQL>
SELECT emp_name FROM
Employee_Personal_details WHERE Employee_ID IN (SELECT Employee_ID FROM
Employee_Salary_details WHERE salary = (SELECT
MAX(salary) FROM Employee_Salary_details));
(iii) Determine
the employees who are located at the same place
SQL>
SELECT emp_name FROM
Employee_Personal_details WHERE location = (SELECT location FROM Employee_Personal_details
GROUP BY location HAVING COUNT(*) > 1);
(iv) Determine
the employees whose total salary matches the minimum salary of any department:
SQL>
SELECT emp_name FROM Employee_Personal_details
WHERE Employee_ID IN ( SELECT
Employee_ID FROM Employee_Salary_details GROUP BY Employee_ID HAVING SUM(salary) = (SELECT MIN(salary) FROM
Employee_Salary_details));
(v) Determine
the department(s) that do not contain any employees:
SQL>
SELECT dep_id FROM
Employee_Personal_details GROUP BY dep_id HAVING COUNT(*) = 0;
EXERCISE2:
Create
Employee Database with Employee_Personal (ID, NAME, DOJ, AGE, ADDRESS,
DEPARTMENT, DESIGNATION) and Employee Salary (ID, NAME, SALARY, BP, DA, HRA,
CCA, GROSS_PAY, NET_PAY). Use sql sub queries to updates the SALARY by 10 times
in the EMPLOYEE_Salary table for all employee whose AGE is greater than or
equal to 29.
ANSWER:
Step 1: Create Tables
Creating the Employee_Personal
table:
SQL>
CREATE TABLE
Employee_Personal (
ID INT PRIMARY KEY,
NAME VARCHAR(100) NOT NULL,
DOJ DATE,
AGE INT NOT NULL,
ADDRESS VARCHAR(255),
DEPARTMENT VARCHAR(100),
DESIGNATION VARCHAR(100)
);
Creating the Employee_Salary
table:
SQL>
CREATE TABLE Employee_Salary
(
ID INT PRIMARY KEY,
NAME VARCHAR(100) NOT NULL,
SALARY DECIMAL(10, 2) NOT NULL,
BP DECIMAL(10, 2),
DA DECIMAL(10, 2),
HRA DECIMAL(10, 2),
CCA DECIMAL(10, 2),
GROSS_PAY DECIMAL(10, 2),
NET_PAY DECIMAL(10, 2),
FOREIGN KEY (ID) REFERENCES
Employee_Personal(ID)
);
Step 2: Update the SALARY
Field
SQL>
UPDATE Employee_Salary SET
SALARY = SALARY * 10 WHERE ID IN ( SELECT ID FROM Employee_Personal WHERE AGE >=
29 );
EXERCISE3:
Create an employee data base and divided into certain
the database into certain departments and each department consists of
employees. The following two tables describes the schemes.
Dept_table
(deptno, dname, loc)
Emp_table
(empno, ename, job, mgr, hiredate, sal, comm,
deptno)
(i) Update the
employee salary by 15%, whose experience is greater than 10 years.
(ii) Delete the
employees, who completed 30 years of service.
(iii) Display
the manager who is having maximum number of employees working under him?
(iv) Create a
view, which contain employee names and their manager.
Generate a
report of employees who gets more than 20000 salary.
ANSWER:
Step 1: Create Tables
Creating the Dept_table:
SQL>
CREATE TABLE Dept_table (
deptno INT PRIMARY KEY,
dname VARCHAR(100),
loc VARCHAR(100)
);
Creating the Emp_table:
SQL>
CREATE TABLE Emp_table (
empno INT PRIMARY KEY,
ename VARCHAR(100),
job VARCHAR(100),
mgr INT,
hiredate DATE,
sal DECIMAL(10, 2),
comm DECIMAL(10, 2),
deptno INT,
FOREIGN KEY (deptno) REFERENCES
Dept_table(deptno)
);
(i) Update the employee
salary by 15%, whose experience is greater than 10 years.
SQL>
UPDATE Emp_table SET sal =
sal * 1.15 WHERE DATEDIFF(CURDATE(), hiredate) / 365 > 10;
(ii) Delete the employees
who completed 30 years of service.
SQL>
DELETE FROM Emp_table WHERE
DATEDIFF(CURDATE(), hiredate) / 365 >= 30;
(iii) Display the manager
who is having the maximum number of employees working under him.
SQL>
SELECT mgr, COUNT(empno) AS
NumberOfEmployees FROM Emp_table
GROUP BY mgr ORDER BY
NumberOfEmployees DESC LIMIT 1;
(iv) Create a view that
contains employee names and their manager.
SQL>
CREATE VIEW
Emp_Manager_View AS SELECT e1.ename AS Employee, e2.ename AS Manager FROM
Emp_table e1 LEFT JOIN Emp_table e2 ON e1.mgr = e2.empno;
(v) Generate a report of
employees who get more than 20000 salary.
SQL>
SELECT ename, sal FROM
Emp_table WHERE sal > 20000;
Comments
Post a Comment