ELIJAHNDETO_AdvancedSQLExam.txt

/* ELIJAH NDETO- TRAINING DATA ANALYST 
DATASEAL SOFTWARE
ADVANCED SQL EXAM */

-- 1(MAX EMP COUNT OVER OTHER DEPARTMENTS)
-- HERE WE USE LEFT JOIN
SELECT D.DNAME, COUNT(*) AS MAXEMPCOUNT
FROM DEPT D
LEFT JOIN EMP E
ON D.DEPTNO = E.DEPTNO
GROUP BY D.DNAME
ORDER BY COUNT(*) DESC
FETCH FIRST ROW ONLY;
-- END

--2(CONDITION EMPNO)
-- WE USE JOIN AND WHERE FOR THE EMPNO CONDITION
-- NOTE THAT I HAVE USED THE GIVEN_EMPLOYEE_NUMBER SINCE THE QUERY IS NOT ASKING FOR A SPECIFIC EMPNO
-- IF GIVEN A SPECIFIC EMP NO CHANGE THE CLAUSE 'GIVEN_EMPLOYEE_NUMBER' WITH THE ACTUAL EMPNO

SELECT 
    E.EMPNO,
    E.ENAME,
    E.SAL,
    D.DNAME,
FROM EMP E
JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
WHERE E.EMPNO = GIVEN_EMPLOYEE_NUMBER
-- END

--3(2ND HIGHEST SALGRADE)
-- HERE WE JOIN THE TWO TABLES EMP AND SALGRADE, THEN WE RANK SAL

SELECT EMPNO, ENAME, SAL, GRADE
FROM (SELECT E.EMPNO, E.ENAME, E.SAL, S.GRADE,
RANK() OVER(ORDER BY SAL DESC) AS SAL_RANK
FROM EMP E
JOIN SALGRADE S
ON E.SAL BETWEEN S.LOSAL AND S.HISAL)
WHERE SAL_RANK = 2;
-- END

-- 4(EMPLOYEE AND THEIR BOSS DETAIL)
-- THIS IS A SELF JOIN QUERY SINCE THERE IS NO MANAGER TABLE
SELECT E.EMPNO AS MNO, E.ENAME AS MNAME, E.SAL AS MSAL, M.EMPNO, M.ENAME, M.SAL
FROM EMP E
JOIN EMP M
ON E.EMPNO = M.MGR
WHERE E.SAL < M.SAL;
-- END

-- 5(EMPLOYEES FROM ALL DEPARTMENT AND THOSE WHO DONT BELONG TO ANY DEPARTMENT
-- WE USE UNION ALL TO JOIN THE TWO QUERIES

SELECT 
    E.EMPNO,
    E.ENAME,
    E.JOB,
    E.MGR,
    E.HIREDATE,
    E.SAL,
    E.COMM,
    E.DEPTNO,
    D.DNAME AS DepartmentName
FROM EMP E
LEFT JOIN DEPT D ON E.DEPTNO = D.DEPTNO

UNION ALL

SELECT 
    E.EMPNO,
    E.ENAME,
    E.JOB,
    E.MGR,
    E.HIREDATE,
    E.SAL,
    E.COMM,
    E.DEPTNO,
    NULL AS DepartmentName
FROM EMP E
WHERE E.DEPTNO IS NULL;
-- END

--6 (5 IS HIGHEST PAID, 1 IS LOWEST  PAID , ELSE MEDIUM PAID)
-- CASE WHEN IS USED AS A PIVOT FUNCTION
SELECT
    E.EMPNO,
    E.ENAME,
    E.SAL,
    S.GRADE,
    CASE S.GRADE
        WHEN 5 THEN 'highest paid guys'
        WHEN 1 THEN 'lowest paid guys'
        ELSE 'medium paid guys'
    END AS GradeName
FROM EMP E
JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL;
--END

--7(INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN AND CROSS JOIN

-- INNER JOIN
SELECT COUNT(*) AS ROWCOUNT
FROM TABLE1 T1
INNER JOIN TABLE2 T2 ON T2.COL2=T1.COL1;
--END

-- LEFT JOIN
SELECT COUNT(*) AS ROWCOUNT
FROM TABLE1 T1
LEFT JOIN TABLE2 T2 ON T2.COL2=T1.COL1;
-- END

-- RIGHT JOIN
SELECT COUNT(*) AS ROWCOUNT
FROM TABLE1 T1
RIGHT JOIN TABLE2 T2 ON T2.COL2=T1.COL1;
-- END

-- FULL JOIN
SELECT COUNT(*) AS ROWCOUNT
FROM TABLE1 T1
FULL JOIN TABLE2 T2 ON T2.COL2=T1.COL1;
-- END

--CROSS JOIN
SELECT COUNT(*) AS ROWCOUNT
FROM TABLE1 
CROSS JOIN TABLE2;
-- END 

-- 8(UPPERCASE, LOWERCASE, LENGTH AND FIRST CHARACTER)
-- HERE WE USE THE UPPER(),LOWER(), LENGTH(), SUBSTR() FUNCTIONS TO DISPLAY THE RESULTS
SELECT
    UPPER(ENAME) AS UPPERCASENAME,
    LOWER(ENAME) AS LOWERCASENAME,
    LENGTH(ENAME) AS LENGTHNAME,
    SUBSTR(ENAME, 1, 1) AS FIRSTCHARACTER
FROM EMP;
-- END

-- 9(MOST EXPIRIENCED AND LEAST EXPIREINCED)
-- HERE WE USE SUBQUERIES, AND THEN TRUNC() TO REMOVE DECIMAL PLACES FROM THE DIFF BETWEEN SYSDATE AND HIREDATE AS EXPIRIENCE
-- THE OR STATEMENT WAS USED TO DISPLAY BOTH OF THEM FROM THE SUBQUERIES
SELECT ENAME, HIREDATE,
TRUNC(SYSDATE - HIREDATE) AS EXPIRIENCE
FROM EMP
WHERE HIREDATE = (SELECT MAX(HIREDATE)FROM EMP)
OR HIREDATE = (SELECT MIN(HIREDATE)FROM EMP);
-- END

-- 10(FIRST DAY AND SUNDAY OF THE NEXT MONTH)
-- WE USE DATE OPERATORS
SELECT TRUNC(SYSDATE, MM) AS FIRSTDAYOFCURRENTMONTH,
NEXT_DAY(TRUNC(ADD_MONTH(SYSDATE, 1), 'MM') - 1, 'SUNDAY') AS FIRSTSUNDAYOFNEXTMONTH
FROM DUAL;
-- END

-- 11(DEPARTMENTWISE AVG SAL)
-- WE USE GROUP BY CLAUSE TO GROUP BY DEPARTMENT
SELECT DEPTNO, TRUNC(AVG(SAL))
FROM EMP
GROUP BY DEPTNO
HAVING AVG(SAL) > (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 20);
-- END

-- 12(DETAILS OF EMP AND DEPT WHERE NO EMP IS WORKING
-- THE LEFT JOIN CLAUSE WAS USED
SELECT E.*, D.*
FROM DEPT D
LEFT JOIN EMP E
ON D.DEPTNO = E.DEPTNO
WHERE E.ENAME IS NULL;
-- END

-- 13(DISPLAY 5TH,7TH AND 9TH)
-- WE USE THE SYS FUNCTION ROWNUM TO KNOW THE ROW NUMBERS
SELECT * FROM 
(SELECT E.*, ROWNUM AS RN
FROM EMP E)
WHERE RN IN (5, 7, 10);
-- END

-- 14(FIRST AND LAST RECORD)
-- WE USE THE SYS FUNCTION ROWNUM TO KNOW THE ROW NUMBERS
SELECT * FROM 
(SELECT E.*, ROWNUM AS RN
FROM EMP E)
WHERE RN IN (1, 14);
-- END

-- 15(2ND LAST RECORD)
-- WE USE THE SYS FUNCTION ROWNUM TO KNOW THE ROW NUMBERS
SELECT * FROM 
(SELECT E.*, ROWNUM AS RN
FROM EMP E)
WHERE RN = 13;
-- END

-- 16(2ND, 5TH, 9TH MAX SAL)
-- WE USE A SUBQUERY FOR RANKING MAX SAL
SELECT SAL, SALARY_RANK
FROM (
    SELECT SAL, DENSE_RANK() OVER (ORDER BY SAL DESC) AS salary_rank
    FROM EMP
)
WHERE salary_rank IN (2, 5, 9);
;
-- END


-- 17(2ND TO 4TH MIN SAL)
-- WE USE A SUBQUERY FOR RANKING MIN SAL
SELECT SAL, SALARY_RANK
FROM (
    SELECT SAL, DENSE_RANK() OVER (ORDER BY SAL) AS salary_rank
    FROM EMP
)
WHERE salary_rank BETWEEN 2 AND 4;
-- END


--18(SCENARIO)
-- QUESTION 1
SELECT LOC_NO, LOC_NM
FROM Location
GROUP BY LOC_NO, LOC_NM
HAVING COUNT(*) > 1;
-- END

-- QUESTION 2
SELECT LOC_NO, LOC_NM
FROM (
    SELECT 
        LOC_NO,
        LOC_NM,
        ROW_NUMBER() OVER (PARTITION BY LOC_NO, LOC_NM ORDER BY LOC_NO) AS rn
    FROM Location
)
WHERE rn > 1;
-- END

-- QUESTION 3
DELETE FROM Location
WHERE ROWID NOT IN (
    SELECT MIN(ROWID)
    FROM Location
    GROUP BY LOC_NO, LOC_NM
);
-- END

-- 19(SAL DIFF FOR NOW AND PREVIOUS)
-- WE USE LAG()
SELECT EMPNO, ENAME, SAL, 
    LAG(SAL)OVER(ORDER BY ENAME) AS PREVIOUS_SAL,
    (SAL - LAG(SAL)OVER(ORDER BY ENAME))AS SAL_DIFFERENCE
FROM EMP
ORDER BY ENAME;
-- END

-- 20(SAL DIFF FOR NOW AND PREVIOUS IN DEPT WISE)
-- WE USE LAG() AND PARTITION BY DEPTNO

SELECT DEPTNO, ENAME, SAL, 
    LAG(SAL)OVER(PARTITION BY DEPTNO ORDER BY ENAME) AS PREVIOUS_SAL,
    (SAL - LAG(SAL)OVER(PARTITION BY DEPTNO ORDER BY ENAME))AS SAL_DIFFERENCE
FROM EMP
ORDER BY DEPTNO, ENAME;
-- END

--21(SAL DIFF FOR NEXT AND NOW)
-- WE USE LEAD()

SELECT EMPNO, ENAME, SAL, 
    LEAD(SAL)OVER(ORDER BY ENAME) AS NEXT_SAL,
    (SAL - LEAD(SAL)OVER(ORDER BY ENAME))AS SAL_DIFFERENCE
FROM EMP
ORDER BY ENAME;
-- END

-- 22(SAL DIFF FOR NEXT AND NOW DEPARTMENTWISE)
-- WE USE LEAD() AND PARTITION BY DEPTNO

SELECT DEPTNO, ENAME, SAL, 
    LEAD(SAL)OVER(PARTITION BY DEPTNO ORDER BY ENAME) AS NEXT_SAL,
    (SAL - LEAD(SAL)OVER(PARTITION BY DEPTNO ORDER BY ENAME))AS SAL_DIFFERENCE
FROM EMP
ORDER BY DEPTNO, ENAME;
-- END



--23(CREATE VIEW)
CREATE VIEW SKIT AS
SELECT 
    SUM(SAL) AS TOTALSALARY,
    AVG(SAL) AS AVERAGESALARY,
    MIN(COMM) AS MINIMUMCOMMISION,
    MAX(COMM) AS MAXIMUMCOMMISION
FROM EMP;
-- END


-- 24 (DISPLAY ALL DEPT NO)
-- UNION IS USED TO JOIN THE TWO TABLES

SELECT E.DEPTNO
FROM EMP E
UNION
SELECT D.DEPTNO 
FROM DEPT D;
-- END

--25 (DISPLAY DEPT NOT PRESENT IN EMP WITHOUT JOIN AND SUBQUERY)
-- WE USE MINUS TO SUBSTRACT EVERYTHING ON DEPT FROM EMP

SELECT D.DEPTNO
FROM DEPT D
MINUS
SELECT E.DEPTNO
FROM EMP E;
-- END

-- 26(QUERY TO GET O/P)
-- PIVOT STATEMENT CASE WHEN IS USED IN THIS CASE 
SELECT
    SNAME AS NAME,
    (CASE WHEN SUBJECT = 'ENGLISH' THEN MARKS END) AS ENGLISH,
    (CASE WHEN SUBJECT = 'SCIENCE' THEN MARKS END) AS SCIENCE,
    (CASE WHEN SUBJECT = 'COMPUTERSCIENCE' THEN MARKS END) AS COMPUTERSCIENCE
FROM MARKS
GROUP BY SNAME;
-- END