SCHEMA AND TABLE CREATION

1. CREATION OF SCHEMA AND TABLES -

CREATE SCHEMA EMP_DEPT;
USE EMP_DEPT;
CREATE TABLE DEPARTMENT(DNO INT NOT NULL,
-> DNAME VARCHAR(50),
-> LOCATION VARCHAR(50) DEFAULT 'NEW DELHI',
-> PRIMARY KEY(DNO));
CREATE TABLE EMPLOYEE(ENO CHAR(3) NOT NULL,
-> ENAME VARCHAR(50) NOT NULL,
-> JOB_TYPE VARCHAR(50) NOT NULL,
-> S_ENO CHAR(3),
-> HIRE_DATE DATE NOT NULL,
-> DNO INT,
-> COMMISSION DECIMAL(10,2),
-> SALARY DECIMAL(7,2) NOT NULL,
-> PRIMARY KEY(ENO),
-> FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNO),
-> FOREIGN KEY(S_ENO) REFERENCES EMPLOYEE(ENO));
INSERT INTO DEPARTMENT VALUES(45,'OPERATIONS','MAHARASHTRA'),
-> (50,'HUMAN RESOURCE','NEW DELHI'),
-> (60,'PURCHASE','PUNE'),
-> (70,'GENERAL MANAGEMENT','GOA'),
-> (80,'DESIGN','NEW DELHI'),
-> (90,'BRANDING','PUNJAB');
INSERT INTO DEPARTMENT(DNO,DNAME,LOCATION) VALUES(25,'MANUFACTURING');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
MariaDB [EMP_DEPT]> INSERT INTO DEPARTMENT(DNO,DNAME)
VALUES(25,'MANUFACTURING');
INSERT INTO
EMPLOYEE(ENO,ENAME,JOB_TYPE,HIRE_DATE,DNO,COMMISSION,SALARY)
VALUES('79','ROJO','ANALYST','2002-12-23',10,68000.32,55000.5);
INSERT INTO
EMPLOYEE(ENO,ENAME,JOB_TYPE,HIRE_DATE,DNO,COMMISSION,SALARY,S_ENO)
VALUES('113','DRASHTI','EXPERT','1999-11-20',30,6882,25000,'778');
 QUERIES
1. Query to display Employee Name, Job, Hire Date, Employee Number; for each employee with the
Employee Number appearing first.
SELECT ENO,ENAME,JOB_TYPE,HIRE_DATE FROM EMPLOYEE;
2. Query to display unique Jobs from the Employee Table.
SELECT DISTINCT JOB_TYPE FROM EMPLOYEE;

3. Query to display the Employee Name concatenated by a Job separated by a comma.

SELECT CONCAT(ENAME,',',JOB_TYPE) FROM EMPLOYEE;

4. Query to display all the data from the Employee Table. Separate each Column by a comma and
name the said column as THE_OUTPUT.
SELECT
CONCAT_WS(',',ENO,ENAME,JOB_TYPE,S_ENO,HIRE_DATE,DNO,COMMISSION,SALARY)
AS 'THE_OUTPUT' FROM EMPLOYEE;

5. Query to display the Employee Name and Salary of all the employees earning more than $2850.
SELECT ENAME,SALARY FROM EMPLOYEE WHERE SALARY>2850;

6. Query to display Employee Name and Department Number for the Employee No= 79.
SELECT ENAME,DNO FROM EMPLOYEE WHERE ENO='79';

7. Query to display Employee Name and Salary for all employees whose salary is not in the range of
$1500 and $2850.
SELECT ENAME,SALARY FROM EMPLOYEE WHERE SALARY NOT BETWEEN 1500 AND
2850;

8. Query to display Employee Name and Department No. of all the employees in Dept 10 and Dept
30 in the alphabetical order by name.
SELECT ENAME,SALARY FROM EMPLOYEE WHERE DNO IN(10,30) ORDER BY ENAME;

9. Query to display Name and Hire Date of every Employee who was hired in 1981
SELECT ENAME,HIRE_DATE FROM EMPLOYEE WHERE YEAR(HIRE_DATE) = 1981;

10. Query to display Name and Job of all employees who have not assigned a supervisor.
SELECT ENAME,JOB_TYPE FROM EMPLOYEE WHERE S_ENO IS NULL;

11. Query to display the Name, Salary and Commission for all the employees who earn
commission.
SELECT ENAME,SALARY,COMMISSION FROM EMPLOYEE WHERE COMMISSION != 0;

12. Sort the data in descending order of Salary and Commission.

SELECT * FROM EMPLOYEE ORDER BY SALARY DESC,COMMISSION DESC;

13. Query to display Name of all the employees where the third letter of their name is ‘A’.
SELECT ENAME FROM EMPLOYEE WHERE ENAME LIKE '__A%';
14. Query to display Name of all employees either have two ‘R’s or have two ‘A’s in their
name and are either in Dept No = 30 or their Manger’s Employee No = 7788.
SELECT ENAME FROM EMPLOYEE WHERE ENAME LIKE '%R%R%' OR ENAME LIKE
'%A%A%' AND (DNO = 30 OR S_ENO = '778');

15. Query to display Name, Salary and Commission for all employees whose Commission
amount is greater than their Salary increased by 5%.
SELECT ENAME,SALARY,COMMISSION FROM EMPLOYEE WHERE
COMMISSION>1.05*SALARY;

16. Query to display the Current Date along with the day name.
SELECT CURDATE() AS DATE, DAY(CURDATE()) AS DAY, MONTH(CURDATE()) AS
MONTH;

17. Query to display Name, Hire Date and Salary Review Date which is the 1st Monday
after six months of employment.
SELECT ENAME,HIRE_DATE, DATE_ADD(DATE_ADD(HIRE_DATE,INTERVAL 6 MONTH),
INTERVAL 7 - WEEKDAY(DATE_ADD(HIRE_DATE,INTERVAL 6 MONTH)) DAY) AS
SALARY_REVIEW_DATE FROM EMPLOYEE;

18. Query to display Name and calculate the number of months between today and the
date on which employee was hired of department ‘Purchase’.
SELECT ENAME, TIMESTAMPDIFF(MONTH,HIRE_DATE,CURDATE()) AS MONTHS FROM
EMPLOYEE,DEPARTMENT WHERE EMPLOYEE.DNO = DEPARTMENT.DNO AND
DEPARTMENT.DNAME = 'PURCHASE';

19. Query to display the following for each employee <E-Name> earns < Salary> monthly but wants
< 3 * Current Salary >. Label the Column as Dream Salary.
SELECT CONCAT(ENAME,' EARNS ',SALARY,' MONTHLY BUT WANTS ',3*SALARY) AS
DREAM_SALARY FROM EMPLOYEE;

20. Query to display Name with the 1st letter capitalized and all other letter lower case and length
of their name of all the employees whose name starts with ‘J’, ’A’ and ‘M’.
SELECT
CONCAT(UPPER(SUBSTRING(ENAME,1,1)),LOWER(SUBSTRING(ENAME,2,LENGTH(ENA
ME)))) AS NAMES FROM EMPLOYEE WHERE ENAME LIKE 'J%' OR ENAME LIKE 'A%' OR
ENAME LIKE 'M%';

21. Query to display Name, Hire Date and Day of the week on which the employee started.
SELECT ENAME,HIRE_DATE,DAYNAME(HIRE_DATE) FROM EMPLOYEE;

22. Query to display Name, Department Name and Department No for all the employees.
SELECT ENAME,DNAME,DEPARTMENT.DNO FROM EMPLOYEE,DEPARTMENT WHERE
EMPLOYEE.DNO = DEPARTMENT.DNO;

23. Query to display Unique Listing of all Jobs that are in Department number 30.
SELECT DISTINCT JOB_TYPE FROM EMPLOYEE WHERE DNO=30;

24. Query to display Name, Dept Name of all employees who have an ‘A’ in their name.
SELECT ENAME,DNAME FROM EMPLOYEE,DEPARTMENT WHERE ENAME LIKE '%A%'
AND EMPLOYEE.DNO = DEPARTMENT.DNO;

25. Query to display Name, Job, Department No. And Department Name for all the employees
working at the Dallas location.
SELECT ENAME,JOB_TYPE,EMPLOYEE.DNO,DNAME FROM EMPLOYEE,DEPARTMENT
WHERE EMPLOYEE.DNO = DEPARTMENT.DNO AND LOCATION = 'DALLAS';

26. Query to display Name and Employee no. Along with their supervisor’s Name and the
supervisor’s employee no; along with the Employees’ Name who do not have a supervisor.
SELECT E.ENAME,E.ENO,S.ENAME AS SUPERVISOR,S.ENO AS SUPERVISOR_ENO
FROM EMPLOYEE AS E LEFT JOIN EMPLOYEE AS S ON E.S_ENO = S.ENO;

27. Query to display Name, Dept No. And Salary of any employee whose department No. and salary
matches both the department no. And the salary of any employee who earns a commission.
SELECT E.ENAME,E.DNO,E.SALARY FROM EMPLOYEE AS E WHERE(SALARY,DNO) IN
(SELECT SALARY,DNO FROM EMPLOYEE AS F WHERE COMMISSION!=0 AND
E.ENO!=F.ENO);

28. Query to display Name and Salaries represented by asterisks, where each asterisk (*) signifies
$100.(* = $1000)
SELECT ENAME,REPEAT('*',SALARY/1000) FROM EMPLOYEE;

29. Query to display the Highest, Lowest, Sum and Average Salaries of all the employees.
SELECT MAX(SALARY),MIN(SALARY),AVG(SALARY),SUM(SALARY) FROM EMPLOYEE;

30. Query to display the number of employees performing the same Job type functions.
SELECT JOB_TYPE,COUNT(ENO) FROM EMPLOYEE GROUP BY JOB_TYPE;

31. Query to display the total number of supervisors without listing their names.
SELECT COUNT(S_ENO) FROM EMPLOYEE;

32. Query to display the Department Name, Location Name, No. of Employees and the average
salary for all employees in that department.
SELECT DNAME,LOCATION,E.DNO,COUNT(E.ENO),AVG(E.SALARY) FROM EMPLOYEE
AS E, DEPARTMENT AS D WHERE E.DNO=D.DNO GROUP BY E.DNO;
33. Query to display Name and Hire Date for all employees in the same dept. as Blake.(Blake is also
counted)
SELECT ENAME,HIRE_DATE FROM EMPLOYEE WHERE (DNO) IN (SELECT DNO FROM
EMPLOYEE WHERE ENAME = 'BLAKE');

34. Query to display the Employee No. And Name for all employees who earn more than the
average salary.
SELECT ENO,ENAME FROM EMPLOYEE WHERE SALARY>(SELECT AVG(SALARY) FROM
EMPLOYEE);

35. Query to display Employee Number and Name for all employees who work in a department with
any employee whose name contains a ‘T’.
SELECT ENO,ENAME FROM EMPLOYEE WHERE (DNO) IN (SELECT DNO FROM
EMPLOYEE WHERE ENAME LIKE '%T%');

36. Query to display the names and salaries of all employees who report to supervisor named ‘King’
SELECT ENAME,SALARY FROM EMPLOYEE WHERE (S_ENO) IN (SELECT ENO FROM
EMPLOYEE WHERE ENAME = 'KING');

37. Query to display the department no, name and job for all employees in the Sales department
SELECT E.DNO,ENAME,JOB_TYPE FROM EMPLOYEE AS E, DEPARTMENT AS D WHERE
E.DNO = D.DNO AND (D.DNO) IN (SELECT DNO FROM DEPARTMENT WHERE DNAME =
'SALES');

38. Display names of employees along with their department name who have more than 20 years
experience.(including those with 20 years of experience)
SELECT ENAME,DNAME FROM EMPLOYEE AS E,DEPARTMENT AS D WHERE E.DNO =
D.DNO AND TIMESTAMPDIFF(YEAR,HIRE_DATE,CURDATE())>=20;

39. Display total number of departments at each location

SELECT LOCATION,COUNT(DNO) FROM DEPARTMENT GROUP BY LOCATION;

40. Find the department name in which at least 20 employees work in.
SELECT DNAME FROM DEPARTMENT WHERE (DNO) IN (SELECT DNO FROM EMPLOYEE
GROUP BY DNO HAVING COUNT(DNO)>=20);

41. Query to find the employee’ name who is not supervisor and name of supervisor supervising
more than 5 employees.
SELECT ENAME FROM EMPLOYEE WHERE (ENO) NOT IN (SELECT S_ENO FROM
EMPLOYEE WHERE S_ENO IS NOT NULL) OR (ENO) IN (SELECT S_ENO FROM
EMPLOYEE GROUP BY S_ENO HAVING COUNT(ENO)>5);

42. Query to display the job type with maximum and minimum employees
SELECT JOB_TYPE,COUNT(JOB_TYPE) FROM EMPLOYEE GROUP BY JOB_TYPE
HAVING COUNT(JOB_TYPE) = (SELECT MAX(MYCOUNT) FROM(SELECT
JOB_TYPE,COUNT(JOB_TYPE) AS MYCOUNT FROM EMPLOYEE GROUP BY
JOB_TYPE)EMPLOYEE) UNION SELECT JOB_TYPE,COUNT(JOB_TYPE) FROM
EMPLOYEE GROUP BY JOB_TYPE HAVING COUNT(JOB_TYPE) = (SELECT
MIN(MYCOUNT) FROM (SELECT JOB_TYPE,COUNT(JOB_TYPE) AS MYCOUNT FROM
EMPLOYEE GROUP BY JOB_TYPE)EMPLOYEE);

OUTPUTS
EACH IMAGE CORRESPONDS TO AN OUTPUT FROM THE QUERIES.
(42 IMAGES ATTACHED)