DBMS FINAL HANDS ON

PL SQL FINAL HACKERRANK CODE ASSESSMENT

1)
SET SERVEROUTPUT ON;

DECLARE
    CURSOR C1 IS SELECT * FROM EMPLOYEES;
    E EMPLOYEES%ROWTYPE;
    GRADE VARCHAR2(40);
BEGIN
    OPEN C1;
    LOOP
        FETCH C1 INTO E;
        EXIT WHEN C1%NOTFOUND;
        IF ( E.SALARY >= 30000 ) THEN
            GRADE := 'Senior manager';
        ELSIF ( E.SALARY >= 25000 ) THEN
            GRADE := 'Middle manager';
        ELSIF ( E.SALARY >= 20000 ) THEN
            GRADE := 'Junior manager';
        ELSE
            GRADE := 'Team member';
        END IF;

        dbmsoutput.putline(E.NAME || ' ' || GRADE);
    END LOOP;
END;
/


2)
SET SERVEROUTPUT ON;

DECLARE
    CURSOR C1 IS SELECT * FROM SCORES WHERE SCORE < 100;
    LINE SCORES%ROWTYPE;
    total NUMBER := 0;
    num NUMBER;
    flag NUMBER;

BEGIN
    OPEN C1;
    LOOP
        flag :=1;
        FETCH C1 INTO LINE;
        EXIT WHEN C1%NOTFOUND;
        num := LINE.SCORE;
        FOR i IN 2..sqrt(num)
        LOOP
            IF ( mod(num, i) = 0 ) THEN
                flag := 0;
                exit;
            END IF;
        END LOOP;
        IF ( flag=1 ) THEN
            total := total + LINE.SCORE;
        END IF;
    END LOOP;
    dbmsoutput.putline('Total=' || total);
END;
/


SQL CODE ASSESSMENT

1) DEPARTMENT WHIHC DOES NOT CONTAIN PROGRAMMER POEPLE

SELECT DISTINCT D.DEPTNAME FROM EMPLOYEES E JOIN DEPARTMENTS D ON E.DEPTID=D.DEPTID WHERE E.DEPTID NOT IN (SELECT E.DEPTID FROM EMPLOYEES E JOIN DEPARTMENTS D ON E.DEPTID=D.DEPTID WHERE E.EMPSKILL LIKE 'Programmer');

2) SOME OTHER QUESTION

SELECT DISTINCT D.DEPTNAME, E.EMPSKILL FROM EMPLOYEES E JOIN DEPARTMENTS D ON E.DEPTID = D.DEPTID;