January 2023 ~ Anitesh'er Khata (Anitesh's Notes)

Assignment #1

Create a table called EMP with the following structure:

Name	Type
EMPNO	NUMBER(6)
ENAME	VARCHAR2(20)
JOB	VARCHAR2(10)
DEPTNO	NUMBER(3)
SAL	NUMBER(7, 2)

Allow NULL for all columns except ENAME and JOB.


CREATE TABLE EMP (
    EMPNO NUMBER(6),
    ENAME VARCHAR2(20) NOT NULL,
    JOB VARCHAR2(10) NOT NULL,
    DEPTNO NUMBER(3),
    SAL NUMBER(7,2)
);

Add a column experience to the EMP table. EXPERIENCE numeric null allowed.
```
ALTER TABLE EMP ADD (
    EXPERIENCE NUMBER(2)
);
```
Modify the column width of the job field of emp table.
```
ALTER TABLE EMP MODIFY (
    JOB VARCHAR2(16)
);
```
Create the EMP1 table with ENAME and EMPNO, add constraints to check the EMPNO value while entering (i.e.) EMPNO>100
```
CREATE TABLE EMP1 (
    ENAME VARCHAR2(20) NOT NULL,
    EMPNO NUMBER(6) CONSTRAINT B CHECK(EMPNO>100)
);
```

Assignment #2

Write query to select all the columns of emp table.


SELECT * FROM EMP;

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7839	KING	PRESIDENT	-	17-NOV-81	5000	-	10
7698	BLAKE	MANAGER	7839	01-MAY-81	2850	-	30
7782	CLARK	MANAGER	7839	09-JUN-81	2450	-	10
7566	JONES	MANAGER	7839	02-APR-81	2975	-	20
7788	SCOTT	ANALYST	7566	19-APR-87	3000	-	20
7902	FORD	ANALYST	7566	03-DEC-81	3000	-	20
7369	SMITH	CLERK	7902	17-DEC-80	800	-	20
7499	ALLEN	SALESMAN	7698	20-FEB-81	1600	300	30
7521	WARD	SALESMAN	7698	22-FEB-81	1250	500	30
7654	MARTIN	SALESMAN	7698	28-SEP-81	1250	1400	30
7844	TURNER	SALESMAN	7698	08-SEP-81	1500	0	30
7876	ADAMS	CLERK	7788	23-MAY-87	1100	-	20
7900	JAMES	CLERK	7698	03-DEC-81	950	-	30
7934	MILLER	CLERK	7782	23-JAN-82	1300	-	10

Write query to select only EMPNAME, ENAME and JOB.


SELECT ENAME, EMPNO, JOB FROM EMP;

ENAME	EMPNO	JOB
KING	7839	PRESIDENT
BLAKE	7698	MANAGER
CLARK	7782	MANAGER
JONES	7566	MANAGER
SCOTT	7788	ANALYST
FORD	7902	ANALYST
SMITH	7369	CLERK
ALLEN	7499	SALESMAN
WARD	7521	SALESMAN
MARTIN	7654	SALESMAN
TURNER	7844	SALESMAN
ADAMS	7876	CLERK
JAMES	7900	CLERK
MILLER	7934	CLERK

Write query to select unique JOBs.


SELECT UNIQUE JOB FROM EMP;

JOB
ANALYST
CLERK
SALESMAN
MANAGER
PRESIDENT

Write a query to select only those employees who are salesman.


SELECT * FROM EMP 
    WHERE JOB = 'SALESMAN';

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7499	ALLEN	SALESMAN	7698	20-FEB-81	1600	300	30
7521	WARD	SALESMAN	7698	22-FEB-81	1250	500	30
7654	MARTIN	SALESMAN	7698	28-SEP-81	1250	1400	30
7844	TURNER	SALESMAN	7698	08-SEP-81	1500	0	30

Select employee name, grade and salary in the order of their salary.


SELECT ENAME, EMPNO, DEPTNO FROM EMP 
    ORDER BY SAL;

EMPNO	ENAME	DEPTNO
SMITH	7369	20
JAMES	7900	30
ADAMS	7876	20
MARTIN	7654	30
WARD	7521	30
MILLER	7934	10
TURNER	7844	30
ALLEN	7499	30
CLARK	7782	10
BLAKE	7698	30
JONES	7566	20
FORD	7902	20
SCOTT	7788	20
KING	7839	10

Mgmt is considering a pay raise, however they want to find out, if they give a flat $200 increment to all, then what % each person is getting. So in your result, display ENAME, SAL and PCTINCR


SELECT ENAME, SAL, 200/SAL*100 AS PCTINCR 
    FROM EMP;

ENAME	SAL	PCTINCR
KING	5000	4
BLAKE	2850	7.01754385964912280701754385964912280702
CLARK	2450	8.16326530612244897959183673469387755102
JONES	2975	6.72268907563025210084033613445378151261
SCOTT	3000	6.66666666666666666666666666666666666667
FORD	3000	6.66666666666666666666666666666666666667
SMITH	800	25
ALLEN	1600	12.5
WARD	1250	16
MARTIN	1250	16
TURNER	1500	13.33333333333333333333333333333333333333
ADAMS	1100	18.18181818181818181818181818181818181818
JAMES	950	21.05263157894736842105263157894736842105
MILLER	1300	15.38461538461538461538461538461538461538

Express work experience of each of the employees by using sysdate and hiredate in terms of no of years.


SELECT 
    TO_CHAR(HIREDATE, 'DD.MM.YYYY') "HIREDATE", 
    TO_CHAR(SYSDATE, 'DD.MM.YYYY') "Today", 
    (SYSDATE-HIREDATE)/365 "EXPERIENCE" 
FROM EMP;

hiredate	Today	experience
17.11.1981	03.01.2023	41.15791945712836123795027904616945712849
01.05.1981	03.01.2023	41.70586466260781329274479959411466260795
09.06.1981	03.01.2023	41.59901534753932014205986808726534753945
02.04.1981	03.01.2023	41.78531671740233384069000507356671740247
19.04.1987	03.01.2023	35.73600164890918315575849822425164890932
03.12.1981	03.01.2023	41.11408384069000507356671740233384069014
17.12.1980	03.01.2023	42.07572767630644342973110096397767630658
20.02.1981	03.01.2023	41.89764548452562151192288178589548452575
22.02.1981	03.01.2023	41.89216603247082699137493658041603247096
28.09.1981	03.01.2023	41.29490575849822425164890918315575849836
08.09.1981	03.01.2023	41.3497002790461694571283612379502790463
23.05.1987	03.01.2023	35.64285096397767630644342973110096397781
03.12.1981	03.01.2023	41.11408384069000507356671740233384069014
23.01.1982	03.01.2023	40.97435781329274479959411466260781329288

Assignment #3

List down number of employees, minimum salary, maximum salary for each department.


SELECT 
    COUNT(ENAME) AS COUNT, MAX(SAL) AS MAX, MIN(SAL) AS MIN, DEPTNO 
    FROM EMP 
    GROUP BY DEPTNO;

COUNT	MAX	MIN	DEPTNO
6	2850	950	30
3	5000	1300	10
5	3000	800	20

Update Email ID, if department id is

<= 10, update the EMAIL field by appending @oracle.com


ALTER TABLE EMP ADD(
    EMAIL VARCHAR(32)
);
UPDATE EMP 
    SET EMAIL=CONCAT(LOWER(ENAME),'@oracle.com') 
    WHERE DEPTNO<=10;

<= 20, update the EMAIL field by appending @oracle.co.uk


UPDATE EMP 
    SET EMAIL=CONCAT(LOWER(ENAME),'@oracle.co.uk') 
    WHERE DEPTNO>10 AND DEPTNO<=20;

Else update it as @oracle.co.in


UPDATE EMP 
    SET EMAIL=CONCAT(LOWER(ENAME),'@oracle.co.in') 
    WHERE DEPTNO>20;
SELECT ENAME, EMAIL FROM EMP;

ENAME	EMAIL
KING	king@oracle.com
BLAKE	blake@oracle.co.in
CLARK	clark@oracle.com
JONES	jones@oracle.co.uk
SCOTT	scott@oracle.co.uk
FORD	ford@oracle.co.uk
SMITH	smith@oracle.co.uk
ALLEN	allen@oracle.co.in
WARD	ward@oracle.co.in
MARTIN	martin@oracle.co.in
TURNER	turner@oracle.co.in
ADAMS	adams@oracle.co.uk
JAMES	james@oracle.co.in
MILLER	miller@oracle.com

Apart from 'Delete' and 'Truncate' statement can also be used for deleting the rows. Comment on their difference.

When execute the DELETE command, the DBMS logs all removed rows. This means it is easier to recover from a mistake, than it would a mistaken TRUNCATE. When we TRUNCATE a table, less information is logged. This means the TRUNCATE statement executes very fast; however, it does so at the expense of not logging each row deleted.

Display a department id wise count of employees

Getting salary more than 1200


SELECT DEPTNO, COUNT(1) AS "COUNT" FROM EMP 
WHERE SAL > 1200 
GROUP BY DEPTNO;

DEPTNO	COUNT
30	5
10	3
20	3

Apart from the above condition, select only those departments which has an average salary in excess of 1600


    SELECT DEPTNO, COUNT(1) AS "COUNT" FROM EMP
    WHERE SAL > 1200 
    HAVING AVG(SAL) > 1600
    GROUP BY DEPTNO;

DEPTNO	COUNT
30	5
10	3
20	3

Explain how two levels of filtering is happening based on firstly WHERE clause secondly HAVING clause based on this particular scenario.

HAVING clause is used for cases where there are aggregate functions like COUNT, SUM, AVG, etc. WHERE clause is used for extract only those records that fulfil a specified condition and works for binary operation only.
We want to add a new row in the employees table with employee id 1000, First Name = 'Scott', Last Name = 'Tiger', Email = 'Stiger@oracle.co.uk', HireDate = 01/02/2014, Job id = 'PR_Prsdnt'(Title = 'Company President')
```
INSERT INTO EMP(EMPNO, DEPTNO, ENAME, EMAIL, HIREDATE, JOB, SAL) 
VALUES(1000, 80, 'SCOTT', 'Stiger@oracle.co.uk', TO_DATE('01/02/2014','dd/mm/yyyy'), 'PR_PRSDNT', 5000);
```

Issue necessary insert statements


INSERT INTO DEPT(DEPTNO, DNAME, LOC)
VALUES(80, 'DATABASE', 'EDINBURGH');

After the update is over in the email column, use INSTR and SUBSTR to display email id and domain information separately.


SELECT 
    SUBSTR(EMAIL,1,INSTR(EMAIL,'@')-1) AS EMAIL_ID, 
    SUBSTR(EMAIL,INSTR(EMAIL,'@')+1) AS DOMAIN 
FROM EMP WHERE EMPNO=1000;

EMAIL_ID	DOMAIN
Stiger	oracle.co.uk

Display day, month and year of the hire date of the employees.


SELECT EXTRACT(DAY FROM TO_DATE(HIREDATE, 'dd-mm-rr')) AS DAY, 
EXTRACT(MONTH FROM TO_DATE(HIREDATE, 'dd-mm-rr')) AS MONTH, 
EXTRACT(YEAR FROM TO_DATE(HIREDATE, 'dd-mm-rr')) AS YEAR FROM EMP;

DAY	MONTH	YEAR
17	11	1981
1	5	1981
9	6	1981
2	4	1981
19	4	1987
3	12	1981
17	12	1980
20	2	1981
22	2	1981
28	9	1981
8	9	1981
23	5	1987
3	12	1981
23	1	1982
1	2	2014

Assignment #4

Level I

Display name of employees, department name and job name for each employee.


SELECT ENAME, DNAME, JOB FROM EMP E, DEPT D 
    WHERE E.DEPTNO=D.DEPTNO;

ENAME	DNAME	JOB
KING	ACCOUNTING	PRESIDENT
BLAKE	SALES	MANAGER
CLARK	ACCOUNTING	MANAGER
JONES	RESEARCH	MANAGER
SCOTT	RESEARCH	ANALYST
FORD	RESEARCH	ANALYST
SMITH	RESEARCH	CLERK
ALLEN	SALES	SALESMAN
WARD	SALES	SALESMAN
MARTIN	SALES	SALESMAN
TURNER	SALES	SALESMAN
ADAMS	RESEARCH	CLERK
JAMES	SALES	CLERK
MILLER	ACCOUNTING	CLERK
SCOTT	DATABASE	PR_PRSDNT

Display the department name along with no of employees and average salary of that department.


SELECT DNAME, CNT, AVG_SAL
FROM (
    SELECT DEPTNO, COUNT(*) AS CNT, AVG(SAL) AS AVG_SAL FROM EMP GROUP BY DEPTNO 
) E 
INNER JOIN DEPT D
ON D.DEPTNO = E.DEPTNO;

DNAME	CNT	AVG_SAL
SALES	6	1566.666666666666666666666666666666666667
ACCOUNTING	3	2916.666666666666666666666666666666666667
RESEARCH	5	2175
DATABASE	1	5000

For each department, find out no. of jobs the employees are assigned to


SELECT JOBS, DNAME 
FROM (
  SELECT COUNT(UNIQUE JOB) AS JOBS, DEPTNO FROM EMP GROUP BY DEPTNO
) E, DEPT D 
WHERE E.DEPTNO=D.DEPTNO;

JOBS	DNAME
3	SALES
3	ACCOUNTING
3	RESEARCH
1	DATABASE

Check for correctness of the above queries in terms of count, if you want to bring in all entries, how would you achieve the same?
```
SELECT * FROM EMP;
```

Group by the employees based on the first character of employee first name. Display the results in alphabetic order (descending) of first character.


SELECT SUBSTR(ENAME, 1, 1) AS ALPHA, COUNT(1) AS CNT FROM EMP 
GROUP BY SUBSTR(ENAME, 1, 1) 
ORDER BY SUBSTR(ENAME, 1, 1) DESC;

ALPHA	CNT
W	1
T	1
S	3
M	2
K	1
J	2
F	1
C	1
B	1
A	2

Level II

Table - EmployeeDetails

EmpId	FullName	ManagerId	DateOfJoining
121	John Snow	321	01/31/2014
321	Walter White	986	01/30/2015
421	Kuldeep Rana	876	27/11/2016

Table - EmployeeSalary

EmpId	Project	Salary
121	P1	8000
321	P2	1000
421	P1	12000


CREATE TABLE EMPSAL (EMPID NUMBER(3), 
    PROJECT VARCHAR(2), 
    SAL NUMBER(7,2), 
    CONSTRAINT PK_EMPID PRIMARY KEY(EMPID) 
);
CREATE TABLE EMPDET  
(
    EMPID NUMBER(3),  
    FNAME VARCHAR(16),  
    MGID NUMBER(3), 
    JDT DATE, 
    CONSTRAINT PK_MGID PRIMARY KEY(MGID), 
    CONSTRAINT FK_EMPID FOREIGN KEY(EMPID) REFERENCES EMPSAL(EMPID) 
);
INSERT INTO EMPSAL VALUES(121, 'P1', 8000);
INSERT INTO EMPSAL VALUES(321, 'P2', 1000);
INSERT INTO EMPSAL VALUES(421, 'P1', 12000);
INSERT INTO EMPDET VALUES(121, 'JOHN SNOW', 321, DATE '2014-01-31');
INSERT INTO EMPDET VALUES(321, 'WALTER WHITE', 986, DATE '2015-01-30');
INSERT INTO EMPDET VALUES(421, 'KULDEEP RANA', 876, DATE '2016-11-27');

Write a SQL query to fetch the count of employees working in project 'P1'.


SELECT COUNT(*) AS COUNT FROM EMPSAL 
    WHERE PROJECT='P1';

COUNT
2

Write a SQL query to fetch employee names having salary greater than or equal to 5000 and less than or equal 10000.


SELECT FNAME FROM EMPSAL 
    INNER JOIN EMPDET ON EMPSAL.EMPID=EMPDET.EMPID 
    WHERE EMPSAL.SAL BETWEEN 5000 AND 10000;

FNAME
John Snow

Write a SQL query to fetch project-wise count of employees sorted by project's count in descending order.


SELECT PROJECT, COUNT(*) AS COUNT FROM EMPSAL 
    GROUP BY PROJECT 
    ORDER BY COUNT DESC;

PROJECT	COUNT
P1	2
P2	1

Write a query to fetch only the first name(string before space) from the Full Name column of EmployeeDetails table.


SELECT SUBSTR(FNAME,1,INSTR(FNAME,' ')-1) AS FIRSTNAME 
    FROM EMPDET;

FIRSTNAME
John
Walter
Kuldeep

Write a query to fetch employee names and salary records. Return employee details even if the salary record is not present for the employee.


SELECT FNAME, SAL FROM EMPDET 
LEFT JOIN EMPSAL ON EMPSAL.EMPID=EMPDET.EMPID;

FNAME	SAL
John Snow	8000
Walter White	1000
Kuldeep Rana	12000

Write a SQL query to fetch all the Employees who are also managers from EmployeeDetails table.


SELECT * FROM EMPDET E 
WHERE EXISTS (SELECT * FROM EMPDET S WHERE E.EMPID = S.MGID);

EMPID	FNAME	MGID	JDT
321	Walter White	986	30-JAN-15

Write a SQL query to fetch all employee records from EmployeeDetails table who have a salary record in EmployeeSalary table.


SELECT * FROM EMPDET E 
WHERE EXISTS (SELECT * FROM EMPSAL S WHERE E.EMPID = S.EMPID);

EMPID	FNAME	MGID	JDT
121	John Snow	321	31-JAN-14
321	Walter White	986	30-JAN-15
421	Kuldeep Rana	876	27-NOV-16

Write a SQL query to fetch duplicate records from a table.


SELECT EMPID, FNAME, MGID, JDT FROM EMPDET 
    GROUP BY EMPID, FNAME, MGID, JDT 
    HAVING COUNT(*) > 1;

Write a SQL query to remove duplicates from a table without using temporary table.


DELETE FROM EMPSAL 
WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM EMPSAL GROUP BY EMPID);

Write a SQL query to fetch only odd rows from table.


SELECT E.EMPID, E.PROJECT, E.SAL FROM ( 
SELECT ROW_NUMBER() OVER(ORDER BY EMPID) AS ROWNUMBER, EMPID, PROJECT, SAL FROM EMPSAL) E 
WHERE MOD(E.ROWNUMBER, 2) = 1;

EMPID	PROJECT	SAL
121	P1	8000
421	P1	12000

Write a SQL query to fetch only even rows from table.


SELECT E.EMPID, E.PROJECT, E.SAL FROM ( 
SELECT ROW_NUMBER() OVER(ORDER BY EMPID) AS ROWNUMBER, EMPID, PROJECT, SAL FROM EMPSAL) E 
WHERE MOD(E.ROWNUMBER, 2) = 0;

EMPID	PROJECT	SAL
321	P2	1000

Write a SQL query to create a new table with data and structure copied from another table.
```
SELECT * INTO EMPSALARY 
    FROM EMPSAL;
```
Write a SQL query to create an empty table with same structure as some other table.
```
SELECT * INTO EMPSALARY FROM EMPSAL 
    WHERE 1=0;
```
Write a SQL query to fetch common records between two variables.
```
SELECT * FROM EMPSAL 
    INTERSECT SELECT * FROM MGSAL;
```
Write a SQL query to fetch records that are present in one table but not in another table.
```
SELECT * FROM EMPSAL 
    MINUS SELECT * FROM MGSAL;
```
Write a SQL query to find current date-time.
```
SELECT getdate();
```

Write a SQL query to fetch all the Employees details from EmployeeDetails table who joined in year 2016.


SELECT * FROM EMPDET 
WHERE JDT BETWEEN DATE '2016-01-01' AND DATE '2016-12-31';

EMPID	FNAME	MGID	JDT
421	Kuldeep Rana	876	27-NOV-16

Write a SQL query to fetch top n records.


SELECT * FROM EMPSAL 
    ORDER BY SAL DESC 
    FETCH NEXT 2 ROWS ONLY;

EMPID	PROJECT	SAL
421	P1	12000
121	P1	8000

Write a SQL query to find the nth highest salary from table.


SELECT * FROM (
    SELECT * FROM EMPSAL ORDER BY SAL DESC FETCH NEXT 2 ROWS ONLY
) ORDER BY SAL ASC 
FETCH NEXT 1 ROWS ONLY;

EMPID	PROJECT	SAL
121	P1	8000

Write SQL query to find the 3rd highest salary from table without using TOP/limit keyword.


SELECT SAL 
    FROM EMPSAL EMP1 
    WHERE 2 = ( 
        SELECT COUNT( DISTINCT( EMP2.SAL ) ) FROM EMPSAL EMP2 
        WHERE EMP2.SAL > EMP1.SAL 
    );

SAL
1000

Anitesh'er Khata (Anitesh's Notes)

Sharing my notes and questions from my life courses

DBMS Lab - Assignment 1, 2, 3, 4 || KD Edition || B.Tech Diaries