ilp-assignments-database

Warning To who ever is going to copy paste and submit the same copy as below, Answers can vary depending on thoughts. There is more than one way for a solution, please do NOT blindly Copy Paste.

MP
EMP_NO NOT NULL NUMBER(4)
EMP_NAME VARCHAR(25)
DESIGNATION CHAR(4)
JOINING_DATE DATE
SALARY NUMBER(7,2)
DEPT_NO NUMBER(2)


DEPT
DEPT_NO NOT NULL NUMBER(2)
DEPT_NAME VARCHAR(25)
BUDGET NUMBER(15,2)
MANAGER VARCHAR(25)


Create tables EMP, and DEPT, the structure for which are given above.

Soln
CREATE TABLE `EMP` (
`EMP_NO` INT( 4 ) NOT NULL ,
`EMP_NAME` VARCHAR( 25 ) NULL ,
`DESIGNATION` CHAR( 4 ) NULL ,
`JOINING_DATE` DATE NULL ,
`SALARY` INT( 7 ) NULL ,
`DEPT_NO` VARCHAR( 25 ) NULL
) ;

CREATE TABLE `DEPT` (
`DEPT_NO` INT(2) NOT NULL ,
`DEPT_NAME` VARCHAR(25) NULL,
`BUDGET` INT(15) NULL,
`MANAGER` VARCHAR(25) NULL
) ;

Write SQL queries for the following :

1. Display each employee’s name and date of joining.
SELECT EMP_NAME , JOINING_DATE FROM EMP;

2. Display employees earning more than Rs.5,000. Label the column name “Employee”.
SELECT EMP_NAME AS EMPLOYEE FROM EMP WHERE SALARY > 5000;

3. Display all employee names and employee numbers in the alphabetical order of names.
SELECT EMP_NAME , EMP_NO FROM EMP ORDER BY EMP_NAME ASC;

4. Display all employee names containing the letter “S”.
SELECT EMP_NAME FROM EMP WHERE EMP_NAME LIKE '%S%';

5. Display the employees hired in 1981.
SELECT EMP_NAME FROM EMP WHERE JOINING_DATE LIKE '1981';

6. Display the minimum and maximum salary.
SELECT MIN(SALARY) AS MINIMUM , MAX(SALARY) FROM EMP;

7. Display the list of employees along with their department name and its manager.
SELECT EMP_NAME, DEPT_NAME, MANAGER FROM EMP E, DEPT D WHERE E.DEPT_NO=D.DEPT_NO;

8. Display the number of different employees listed for each department.
SELECT DEPT_NAME, COUNT(EMP_NO) FROM EMP E, DEPT D WHERE E.DEPT_NO=D.DEPT_NO GROUP BY DEPT_NAME;

9. Delete the records of all the employees hired in 1981 from EMP table.
DELETE FROM EMP WHERE JOINING_DATE LIKE '1981';

10. Update the salary of all the employees to Rs. 10,000.
UPDATE EMP SET SALARY=10000;