ELIJAHNDETO_BasicSQLExam.txt

/* ELIJAH NDETO; FIRST EXAM ON BASIC SQL
WE ARE USING SQL DEVELOPER+ORACLE DATABASE
ROAD TO BE A DATA ANALYST AT DATASEAL SOFTWARE */

-- 1(CREATING MY TABLE, WITH PK, NOT NULL, UNIQUE AND FK CONSTRAINTS)
-- I CREATED THE TABLE DON IN THE DATABASE WHERE I USED DEPTNO AS FK FROM THE DEPT TABLE
CREATE TABLE DON(
    DNO NUMBER(4) CONSTRAINT PK_DNO PRIMARY KEY,
	DN VARCHAR2(10)NOT NULL,
	CATEGORY VARCHAR2(9),
	PNO NUMBER(10)NOT NULL,
	EMAIL VARCHAR2(100),
    UNIQUE_ID INT UNIQUE,
	DEPTNO NUMBER(2) 
    CONSTRAINT FK_DEPTNO1 
    REFERENCES DEPT(DEPTNO));
--END

-- 2(SELECT ALL FROM EMP WHERE DEPTNO IS 20 AND MONTH IS APRIL
-- HERE IN THE SECOND WHERE CONDITION I EXTRACTED THE MONTH FROM HIRE DATE USING EXTRACT FUNCTION
SELECT * 
FROM EMP
WHERE DEPTNO = 20
AND EXTRACT(MONTH FROM HIREDATE) = 4;
-- END


-- 3(DISPLAY EMPNO, ENAME, SALARY AND SAL+COMM AS TOTALSALARY FROM EMP)
-- IS NOT NULL CLAUSE
SELECT EMPNO, ENAME, SAL, SAL+COMM AS TOTALSALARY
FROM EMP
WHERE COMM IS NOT NULL
AND DEPTNO = 30;
-- END


-- 4(QUERY TO FIND THE UNIQUE DESIGNATIONS FROM EMPLOYEE TABLE)HERE WE USE THE DISTINCT CLAUSE
SELECT DISTINCT JOB
FROM EMP;
-- END

-- 5(LIST OF EMPLOYEE NAME AND INCREASED THEIR SALARY BY 50%)
-- I USED THE ORDER BY AND DESC TO SORT THE DATA IN DESCENDING ORDER
SELECT ENAME, 
SAL*1.5 AS INCREASEDSALARY
FROM EMP
ORDER BY SAL*1.5 DESC;
-- END

-- 6(MIN,MAX,AVG OF SAL;  BY DEPT AND EMPLOYEES WHOSE JOB IS 'SALESMAN' AND 'CLERK'
-- HERE WE USE THE MIN,MAX,AVG FUCNTIONS AND THEN USE IN FOR MULTIPLE VARIABLES
-- WE END BY GROUP BY DEPTNO AND JOB TO CLASSIFY THE DATA AS SUCH
SELECT DEPTNO, JOB, 
MIN(SAL), MAX(SAL), AVG(SAL)
FROM EMP 
WHERE JOB IN('SALESMAN','CLERK')
GROUP BY DEPTNO, JOB;
-- END

-- 7(TOTAL SALARY OF EMP WHERE COMM IS NULL AND MONTH IS APRIL)
-- HERE I USED THE EXTRACT FUNCTION TO EXTRACT MONTH FROM HIREDATE
SELECT SUM(SAL) AS TOTALSALARY
FROM EMP
WHERE COMM IS NULL
AND EXTRACT(MONTH FROM HIREDATE) = 4;
-- END

-- 8(COMM IS > SAL)
-- WHERE CLAUSE TO SPECIFY THE CONDITION
SELECT *
FROM EMP
WHERE COMM>SAL;
-- END

-- 9(EMPLOYEES WHOSE SALARY > 2000 AFTER INCREASING BY 10%
-- 10% = 0.1 SO WE USE THE MULTIPLICATION SIGN(*) WHERE WE ADD 1+0.1=1.1
SELECT EMPNO, ENAME, SAL*1.1 AS INCREASEDSALARY
FROM EMP
WHERE SAL*1.1>2000;
-- END

-- 10(DEPT TOTAL SALARY > 10000)
-- SINCE ITS A GROU BY FUNCTION WE WILL USE HAVING INSTEAD OF WHERE
SELECT DEPTNO, SUM(SAL) AS TOTALSALARY
FROM EMP
GROUP BY DEPTNO
HAVING SUM(SAL)>10000;
--END

-- 11(EMPLOYEE EXPERIENCE IN MONTHS TILL CURRENT MUNTH)
-- TRUCN FUNCTION TO MAKE SURE WE DONT HAVE DECIMAL POINTS AND MONTH_BETWEEN FUNCTION
SELECT 
    EMPNO,
    ENAME,
    HIREDATE,
    TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)) AS ExperienceInMonths
FROM EMP;
-- END


-- 12 (TOTAL SALRY OF ALL EMPLOYEES FROM DEPTNO 10 AND 30, COMM IS NOT NULL AND SAL IS <=1000)
-- WE USE THE IN FOR DEPTNO TO SPECIFY MORE THAN ONE VARIABLE AND IS NULL FOR COMM THEN WE END BY GROUP BY DEPTNO
SELECT DEPTNO,
SUM(SAL) AS TOTALSAL
FROM EMP
WHERE DEPTNO IN(10,30)
AND COMM IS NOT NULL
AND SAL <= 1000 
GROUP BY DEPTNO;
-- END

-- 13($ AT THE BEGINNING AND END OF EACH NAME)
-- SINCE CONCAT DOESNT WORK IN ORACLE WE USE THE '||' 
SELECT '$'||ENAME||'$'
FROM EMP;
-- END

-- 14(EMPLOYEES WHO JOINED IN THE MONTH OF MAY BUT NOT BETWEEN 1982 AND 1988)
-- WE USE THE EXTRACT FUNCTION FOR HIREDATE AND NOT BETWEEN FUNCTION TO FILTER THE DATA
SELECT *
FROM EMP
WHERE EXTRACT(MONTH FROM HIREDATE)= 5
AND NOT(EXTRACT(YEAR FROM HIREDATE)
BETWEEN 1982 AND 1988);
-- END


-- 15(NAME STARTS WITH A AND JOINED IN THE YEAR 1987
-- HERE WE USE THE WILDCARD FOR FILTERING FIRST LETTER AND EXTRACT FUNCTION
SELECT *
FROM EMP
WHERE ENAME LIKE 'A%'
AND EXTRACT(YEAR FROM HIREDATE)=1987;
-- END



-- 16(SHOWING WHICH JOB TOTAL SAL<=5000 IN THE EMP TABLE)
-- WE USE SUM(SAL) FUNCTION AND THE PRECEDE TO GROUP BY JOB AND USE HAVING FOR CONDITION
SELECT JOB, SUM(SAL) AS TOTALSAL
FROM EMP
GROUP BY JOB
HAVING SUM(SAL)<=5000;

-- 17(DISPLAY HIRE DATE IN ('YYYY-MM-DD') FORMAT
-- WE USE THE TO_CHAR FUNCTION
SELECT 
    EMPNO,
    ENAME,
    TO_CHAR(HIREDATE, 'YYYY-MM-DD') AS HireDate
FROM EMP;

-- 18(TO REPLACE 1 FROM NULL IN COMM)
-- UPDATE FUNCTION TO SET THE COMM TO 1 FROM NULL
UPDATE EMP
SET COMM = '1'
WHERE COMM IS NULL;
-- END

-- 19(COUNT OF EMPLOYEES WHO WERE EMPLOYED IN THE SAME MONTH)
--  COUNT FUNCTION, EXTRACT FUNCTION FOR DATE, GROUP BY TO FILTER AND ORDER BY TO SORT
SELECT 
    EXTRACT(MONTH FROM HIREDATE) AS Month,
    COUNT(*) AS EmployeeCount
FROM EMP
GROUP BY EXTRACT(MONTH FROM HIREDATE)
ORDER BY EXTRACT(MONTH FROM HIREDATE);
-- END

-- 20(QUERY TO INSERT A NEW RECORD WITH MY NAME ELIJAH)
-- INSERT INTO FUNCTION AND VALUES
INSERT INTO EMP (EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO)
VALUES (8000, 'ELIJAH', 'ANALYST', TO_DATE('2023-07-26', 'YYYY-MM-DD'), 3000, NULL, 20);

-- TO CHECK WHETHER I HAVE INSERTED MY NAME IN THE EMP TABLE
SELECT*
FROM EMP
WHERE ENAME = 'ELIJAH';
-- THE END
-- THANKS