-- creating emp table
CREATE TABLE emp (
empno INT PRIMARY KEY,
ename VARCHAR(50),
job VARCHAR(20),
mgr INT DEFAULT NULL,
hiredate DATE,
sal FLOAT NOT NULL,
comm FLOAT DEFAULT NULL,
deptno INT NOT NULL,
gr CHAR(1)
);
-- creating dept table
CREATE TABLE dept(
deptno INT PRIMARY KEY,
dname VARCHAR(100) NOT NULL,
loc VARCHAR(50) NOT NULL
);
-- insert into emp table
INSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno, gr)
VALUES
(8569, 'SMITH', 'CLERK', 7902, '1980-12-17', 880, NULL, 20, 'D'),
(8839, 'ALIEN', 'SALESMAN', 7698, '1991-10-12', 1600, 300, 30, 'C'),
(8521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30, 'C'),
(8934, 'JONES', 'MANAGER', 7839, '1981-02-04', 3272.50, NULL, 20, 'B'),
(8788, 'SCOTT', 'ANALYST', 7566, '1987-08-15', 20000, NULL, 20, 'A'),
(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1290, 500, 30, 'C'),
(7876, 'ADMS', 'CLERK', 7788, '1987-05-23', 1500, 350, 10, 'D'),
(7934, 'JOHN', 'ANALYST', 7566, '1980-01-23', 21780, NULL, 20, 'A'),
(7900, 'JAMES', 'CLERK', 7698, '1981-01-23', 1045, 300, 40, 'D'),
(9987, 'SAM', 'MANAGER', 7839, '1990-09-18', 5580, 1500, 20, 'B');
-- insert into dept table
INSERT INTO dept(deptno, dname, loc)
VALUES(10, 'ACCOUNTING DEPT.', 'NOIDA'),
(20, 'RESEARCH & DEVELOPMENT DEPT.', 'DELHI'),
(30, 'SALES & MARKETING DEPT.', 'LUCKNOW'),
(40, 'PRODUCTION DEPT.', 'LUDHIANA');
SELECT * FROM emp;
SELECT * FROM dept;
-- 2. list all employee names along with their salaries from emp table:
SELECT ename, sal
FROM emp;
-- 3. list all department numbers, employee numbers and their manager number in descending order of deptno from emp table:
SELECT deptno, empno, mgr
FROM emp
ORDER BY deptno DESC;
-- 4. list department names and locations from the dept table:
SELECT dname, loc FROM dept;
-- 5. list the employees belonging to the department 20:
SELECT empno, ename
FROM emp
WHERE deptno=20;
-- 6. list the name and salary of the employee whose salary is more than 1000:
SELECT ename, sal
FROM emp
WHERE sal>1000;
-- 7. list the names of clerks working in the dept 20:
SELECT ename
FROM emp
WHERE job='CLERK' AND deptno=20;
-- 8. list the names of analyst and salesman:
SELECT ename FROM emp
WHERE job IN ('ANALYST', 'SALESMAN');
-- 10. list the names of employees who are not managers:
SELECT ename
FROM emp
WHERE job!='MANAGER';
-- 11. list the names of employee whose employee number are 8569, 8521, 8839, 8934, 8788:
SELECT ename
FROM emp
WHERE empno IN (8569, 8521, 8839, 8934, 8788);
-- 12. list the employee detalis not belonging to the department 10, 30 and 40:
SELECT * FROM emp
WHERE deptno NOT IN (10, 30, 40);
-- 13. list the employee name and salary whose salary in between 5000 and 10000:
SELECT ename, sal
FROM emp
WHERE sal BETWEEN 5000 AND 10000;
-- 14. list the employee names, who are not eligible for commission:
SELECT ename
FROM emp
WHERE sal<=15000;
-- 15. list the employee who are eligible for commission:
SELECT ename
FROM emp
WHERE sal>15000;
-- 16. list the details of employees, whose salary is greater than 2000 and commission is NULL:
SELECT * FROM emp
WHERE sal>2000 AND comm IS NULL;
-- 17. List the employees whose names start with an “S” (not”s”):
SELECT * FROM emp
WHERE ename LIKE "s%";
-- 18. List the name, salary and PF amount of all the employees(PF is calculated as 10% of salary):
ALTER TABLE emp
ADD pf float;
UPDATE emp SET pf=sal * 10/100;
SELECT ename, sal, pf FROM emp;
-- 19. List the empno, ename, sal in ascending order of salary:
SELECT empno, ename, sal FROM emp
ORDER BY sal ASC;
-- 20. List the employee name, salary, job and Department no. descending order of Department No. and salary:
SELECT ename, sal, job, deptno FROM emp
ORDER BY deptno DESC, sal DESC;
-- 21. List the employee details in ascending order of salary:
SELECT * FROM emp
ORDER BY sal ASC;
-- 22. List the employee details in descending order of salary:
SELECT * FROM emp
ORDER BY sal DESC;
-- 23. Display name, and sal and commission of all employees whose monthly salary is greater than their commission:
SELECT ename, sal, comm FROM emp
WHERE sal>comm;
-- 24. Select SMITH HAS WORKED IN THE POSITION OF CLERK IN DEPT 20. Display result in this format:
SELECT CONCAT(ename, "HAS WORKED IN THE POSITION OF ", job, " IN DEPT ", deptno)
AS result FROM emp
WHERE ename="SMITH " AND job='CLERK' AND deptno=20;
-- 25. Generate a statement which prompts the user at runtime. The intention is to display employees hired between 2 given dates:
-- 26. Define a variable representing an expression used to calculate total annual remuneration. Use the variable in a statement which finds all employees who earn $30000 a year or more
-- 27. List all the employees name and salaries increased by 15% and expressed as a whole number of dollar:
SELECT ename, sal, ROUND(sal+(sal*15/100),0) AS new_sal FROM emp;
-- 28. Produce the following
-- EMPLOYEE AND JOB
-- SMITH CLERK
-- ALLEN SALESMAN
SELECT CONCAT(ename, ' ', job) AS "EMPLOYEE AND JOB" FROM emp;
-- 29. Produce the following output: SMITH( Clerk) ALLEN( Salesman)
SELECT CONCAT(ename, ' (', job, ')') AS "ENAME (JOB)" FROM emp;
-- 30. Do a case sensitive search for a list of employees with a job that the user enters:
-- 31. It has been discovered that the sales people in dept. 30 are not all male. Please produce the following output:
-- ENAME DEPTNO JOB
-- ALLEN 30 Sales
-- Person
-- 32. Display each employees name and hire date of dept 20:
SELECT ename, hiredate FROM emp
WHERE deptno=20;
-- 33. Display each employees name, hire date and salary review date. Assume salary review date is one year from hire date. Output should be in ascending review date.
-- SELECT ename, hiredate, DATE_ADD(hiredate, INTERVAL 1 YEAR) AS review_date FROM emp
-- ORDER BY review_date ASC;
-- 34. Print list of employees displaying "just salary", if more than 1500. If exactly 1500 display “ On Target”. If less than 1500 display “ Below 1500”.
SELECT
ename, sal,
CASE
WHEN sal > 1500 THEN 'Just Salary'
WHEN sal = 1500 THEN 'On Target'
WHEN sal < 1500 THEN 'Below 1500'
END AS salary_category
FROM emp;
-- 35. Write a query which returns DAY of the week (i.e. MONDAY) for any date entered in the format DD/MM/YY.
-- SELECT DAYNAME(STR_TO_DATE('02/12/24', '%d/%m/%y')) AS day_of_week;
-- 36. Write a query to calculate length of service of each employee:
-- SELECT ename, hiredate,
-- CONCAT(
-- FLOOR(DATEDIFF(CURRENT_DATE, hiredate) / 365), 'years ',
-- MOD(DATEDIFF(CURRENT_DATE, hiredate), 365), 'days'
-- ) AS "lenght of service"
-- FROM emp;
-- 37. Find the minimum salary of all employees:
SELECT MIN(sal) AS min_sal FROM emp;
-- 38. Find the maximum, minimum, and average salaries of all employees:
SELECT MIN(sal) AS min_sal, MAX(sal) AS max_sal, AVG(sal) AS avg_sal FROM emp;
-- 39. List the maximum and minimum salary of each job type.
SELECT job, MIN(sal) AS min_sal, MAX(sal) AS max_sal
FROM emp
GROUP BY job;
-- 40. Find how many managers are in each dept:
SELECT job, COUNT(DISTINCT mgr) AS "No. of managers" FROM emp
GROUP BY job;
-- 50. Find the average salary and average total remuneration of each job type. Remembers sales man earn commission:
SELECT job,
AVG(sal) AS "avg_sal",
AVG(sal+comm) AS "avg_renum"
FROM emp
GROUP BY job;
-- 51. Find out the difference between highest and lowest salary:
SELECT MAX(sal) AS max_sal,
MIN(sal) AS min_sal,
(MAX(sal)-MIN(sal)) AS "diff_max-min_sal"
FROM emp;
-- 52. Find all Departments which have more than three employees.
SELECT deptno, ename FROM emp;