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

on   in , , ,   with No comments

Assignment #1

  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)
);
  2. Add a column experience to the EMP table. EXPERIENCE numeric null allowed. 
    
ALTER TABLE EMP ADD (
    EXPERIENCE NUMBER(2)
);
  3. Modify the column width of the job field of emp table.
    
ALTER TABLE EMP MODIFY (
    JOB VARCHAR2(16)
);
  4. 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

  1. 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
  2. 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
  3. Write query to select unique JOBs.
    
SELECT UNIQUE JOB FROM EMP;
    JOB
    ANALYST
    CLERK
    SALESMAN
    MANAGER
    PRESIDENT
  4. 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
  5. 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
  6. 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
  7. 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

  1. 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
  2. Update Email ID, if department id is
    1. <= 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;
    2. <= 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;
    3. 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
    4. 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.
    5. 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
    6. 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.
    7. 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);
    8. Issue necessary insert statements
      
INSERT INTO DEPT(DEPTNO, DNAME, LOC)
VALUES(80, 'DATABASE', 'EDINBURGH');
    9. 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
    10. 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

  1. 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
  2. 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
  3. 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
  4. 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;
  5. 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');
  1. 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
  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
  3. 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
  4. 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
  5. 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
  6. 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
  7. 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
  8. 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;
  9. 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);
  10. 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
  11. 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
  12. Write a SQL query to create a new table with data and structure copied from another table. 
    
SELECT * INTO EMPSALARY 
    FROM EMPSAL;
  13. Write a SQL query to create an empty table with same structure as some other table. 
    
SELECT * INTO EMPSALARY FROM EMPSAL 
    WHERE 1=0;
  14. Write a SQL query to fetch common records between two variables.
    
SELECT * FROM EMPSAL 
    INTERSECT SELECT * FROM MGSAL;
  15. 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;
  16. Write a SQL query to find current date-time.
    
SELECT getdate();
  17. 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
  18. 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
  19. 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
  20. 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
Share:

0 comments:

Post a Comment