18th February ALL SQL QUERIES

CLASS XI & XII

EMPLOYEE TABLE

TABLE STRUCTURE

Name Null? Type

------------------------------- -------- ----
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

RECORDS

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--------- ---------- --------- --------- --------- --------- - -------- ---------
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
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
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
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

/
 DEPARTMENT TABLE

TABLE STRUCTURE

Name Null? Type

-------------------------- -------- ----------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

RECORDS

DEPTNO DNAME LOC

--------- -------------- ----------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

PRACTICAL FILE (SQL QUERIES)

S. QUESTION ANSWER
O.
DISPLAY ALL THE RECORDS (ALL COLUMNS ) 1 SELECT *
FROM TABLE EMP. 2 FROM EMP ;
DISPLAY EMPNO AND ENAME OF ALL 1 SELECT EMPNO, ENAME
EMPLOYEES FROM TABLE EMP. 2 FROM EMP ;
DISPLAY ENAME,SAL AND SAL ADDED WITH 1 SELECT ENAME, SAL, SAL+COMM "TOTAL SALARY"
COMM FROM TABLE EMP 2 FROM EMP ;
DISPLAY ENAME JOINED WITH JOB WITH 1 SELECT ENAME ||JOB "EMPLOYEE", SAL*12 "TOTAL
HEADING “EMPLOYEE”, SAL *12 AS “TOTAL SALARY"
SALARY FROM TABLE EMP 2 FROM EMP
DISPLAY DISTINCT SAL OF EMPLOYEES FROM SELECT DISTINCT SAL FROM EMP ;
TABLE EMP

/
S. QUESTION ANSWER
O.
SHOW THE STRUCTURE OF TABLE DEPT DESCRIBE DEPT ;
OR
DESC DEPT ;
WRITE A QUERY TO DISPLAY ENAME AND SAL 1 SELECT ENAME, SAL
OF EMPLOYEES WHOSE SALARY IS GREATER 2 FROM EMP
THAN OR EQUAL TO 3000 FORM TABLE EMP 3 WHERE SAL >=3000 ;
WRITE A QUERY TO DISPLAY EMPLOYEE NAME, 1 SELECT *
SALARY AND DEPARTMENT NUMBER WHO ARE 2 FROM EMP
NOT GETTING COMMISSION FROM TABLE EMP 3 WHERE COMM IS NULL ;
WRITE A QUERY TO DISPLAY EMPLOYEE 1 SELECT EMPNO, ENAME, SAL, SAL*12 "ANNUAL
NUMBER, NAME, SAL AND SAL*12 AS “ANNUAL SALARY"
SALARY “ WHOE COMMISSION IS NOT NULL 2 FROM EMP
FROM TABLE EMP 3 WHERE COMM IS NOT NULL ;
WRITE A QUERY TO DISPLAY EMPLOYEE NAME 1 SELECT ENAME, SAL
AND SALARY OF THOSE EMPLOYEE WHO DON’T 2 FROM EMP WHERE SAL NOT BETWEEN 1500 AND
HAVE THERE SALARY IN RANGE OF 1500 TO 2500
2000
WAQ TO DISPLAY NAME, JOB, SALARY AND 1 SELECT ENAME, JOB, SAL, HIREDATE
HIREDATE OF EMPLOYEES WHO ARE HIRED 2 FROM EMP
BETWEEN FEB 20, 1981 AND MAY 1, 1981, 3 WHERE HIREDATE BETWEEN '20-FEB-1981' AND
ORDER OFDER THE QUERY IN ASCENDING '01-MAY-1981'
ORDER OF HIREDATE 4 ORDER BY HIREDATE ASC
WAQ TO DISPLAY NAME, JOB, SALARY OF 1 SELECT ENAME, HIREDATE
THOSE EMPLOYEE WHO ARE HIRED IN 1981 2 FROM EMP
3 WHERE TO_CHAR(HIREDATE, 'YYYY') = '1982' ;
WRITE A QUERY TO DISPLAY NAME, JOB TITLE 1 SELECT ENAME, JOB, SAL
AND SALARY WHO DO NOT HAVE MANAGER 2 FROM EMP
3 WHERE MGR IS NULL
WRITE A QUERY TO DISPLAY THE NAME OF 1 SELECT ENAME
EMPLOYEE WHOSE NAME CONTAINS ‘A’ AS 2 FROM EMP
THIRD ALPHABET . 3 WHERE ENAME LIKE '_ _A%' ;
WRITE A QUERY TO DISPLAY THE NAME OF 1 SELECT ENAME
EMPLOYEE WHOSE NAME CONTAINS ‘T’ AS THE 2 FROM EMP
LAST ALPHABE. 3 WHERE ENAME LIKE '%T' ;
WRITE A QUERY TO DISPLAY THE NAME OF 1 SELECT ENAME
EMP EMPLOYEE WHOSE NAME CONTANS ‘M’ 2 FROM EMP
AS FIRST ALPHABET ‘L’ AS THIRD ALPHABET 3 WHERE ENAME LIKE 'M%L' ; /
S. QUESTION ANSWER
O.
WRITE A QUERY TO DISPLAY THE NAME OF 1 SELECT ENAME
EMPLOYEE WHO IS HAVING ‘L’ AS ANY 2 FROM EMP
ALPHABET OF THE NAME 3 WHERE ENAME LIKE '%L%'
WRITE A QUERY TO DISPLAY THE CURRENT 1 SELECT SYSDATE
SYSTEM DATE 2 FROM DUAL ;
WRITE A QUERY TO DISPLAY EMPLOYEE 1 SELECT EMPNO, ENAME, SAL, SAL+SAL*.15 "NEW
NUMBER, NAME, SALARY , SALARY INCREASE SALARY"
BY 15% EXPRESSED AS A WHOLE NUMBER. 2 FROM EMP ;
LABEL THE COLUMN AS NEW SALARY
WRITE A QUERY TO DISPLAY THE EMPLOYE’S 1 SELECT ENAME, SAL HIREDATE,
NAME AND SALARY REVIEW DATE, WHICH IS ADD_MONTHS(HIREDATE,6) "REVIEW DATE"
THE DATE AFTER SIX MONTHS OF HIREDATE 2 FROM EMP ;
WRITE A QUERY TO DISPLAY THE EMPLOYEE’S 1 SELECT ENAME, SAL,
NAME AND SALARY, REVIEW DATE , WHICH IS TO_CHAR(ADD_MONTHS(HIREDATE,6), 'DAY, DD MON
THE DATE AFTER SIX MONTHS OF HIREDATE YYYY')
IN FORMAT OF ‘SUNDAY, 7, SEP, 1981’. 2 FROM EMP
FOR EACH EMPLOYEE DISPLAY EMPLOYEE 1 SELECT ENAME, HIREDATE,
NAME AND TOTAL NUMBER OF DAYS LAPSED ROUND( TO_DATE('12-JAN-2007')- HIREDATE ,0)
BETWEEN HIREDATE AND TODAY "NUMBER OF DAYS"
2 FROM EMP
FOR EACH EMPLOYEE DISPLAY EMPLOYEE 1 SELECT ENAME, HIREDATE,
NAME AND TOTAL NUMBER OF WEEKS LAPSED ROUND((TO_DATE('12-JAN-2007')- HIREDATE ) /
BETWEEN HIREDATE AND TODAY 7,0) "NUMBER OF WEAK"
2 FROM EMP
CREATE A QUERY THAT PRODUCES DISPLAY IN 1 SELECT ENAME || 'EARNS ' ||
THE FOLLOWING FORMAT '$', SAL || ' MONTHLY AND WORKING AS ' ,
<EMPLOYEE NAME> EARNS $ <SALARY>
MONTHLY AND WORKING AS <JOB> JOB
2 FROM EMP

WRITE A QUERY WHICH DISPLAYS THE 1 SELECT INITCAP(ENAME), LENGTH(ENAME)

EMPLOYEE NAME WITH THE FIRST LETTER "LENGTH
CAPITALIZED AND ALL OTHER LETTERS LOWER OF NAME"
CASE AND LENGTH OF THERE NAME STRING 2 FROM EMP;

/
S. QUESTION ANSWER
O.
WRITE A QUERY TO DISPLAY THE EMPLOYEE 1 SELECT ENAME, NVL( TO_CHAR(COMM),
NAME AND COMMISSION AMOUNT. IF THE 'NO COMMISSION')
EMPLOYEE DOE NOT EARN COMMISSION , PUT 2 FROM EMP;
“NO COMMISSION”
WRITE A QUERY TO DISPLAY THE ENAME AND 1 SELECT ENAME, DEPT.DEPTNO, DNAME
DEPTNO AND DNAME FOR ALL EMPLOYEES 2 FROM EMP, DEPT
USING TABLES EMP AND DEPT 3 WHERE EMP.DEPTNO = DEPT.DEPTNO ;
WRITE A QUERY TO DISPLAY EMPLOYEE NAME, 1 SELECT ENAME,DNAME, MGR
DEPARTMENT NAME AND LOCATION OF ALL 2 FROM EMP, DEPT
EMPLOYEES WHO HAVE MANAGER NAME 3 WHERE MGR BETWEEN 7500 AND 7900
BETWEEN 7500 AND 7900 4 AND EMP.DEPTNO = DEPT.DEPTNO ;
WRITE A QUERY TO DISPLAY THE EMPLOYEE 1 SELECT ENAME,DEPTNO
NAME, DEPARTMENT NUMBER AND ALL THE 2 FROM EMP
EMPLOYEES THAT WORKED IN THE SAME 3 WHERE DEPTNO = (SELECT DEPTNO FROM EMP
DEPARTMENT AS A GIVEN EMPLOYEE WHERE ENAME = &ENAME)
WRITE A QUERY TO DISPLAY EMPLOYEE NAME 1 SELECT ENAME, HIREDATE
AND HIREDATE OF EMPLOYEES WH ARE 2 FROM EMP
EMPLOYEED AFTER EMPLOYEE ‘BLAKE’ 3 WHERE HIREDATE > (SELECT HIREDATE FROM
EMP
WHERE ENAME ='BLAKE')
WRITE A QUERY TO DISPLAY EMPLOYEE 1 SELECT E1.EMPNO "EMPNO", E1.ENAME
NUMBER, NAME, MANAGER’S NAME WITH "EMPNAME",
THEIR MANAGER NUMBER E2.ENAME "MANAGER NAME", E2.EMPNO "MGR NO"
2 FROM EMP E1, EMP E2
3 WHERE E1.MGR = E2.EMPNO
WRITE A QUERY TO DISPLAY THE SUM, 1 SELECT DEPTNO, JOB, SUM(SAL), AVG(SAL),
AVERAGE, HIGHEST AND LOWEST SALARY OF MIN(SAL), MAX(SAL)
THE EMPLOYEES GROUPED BY DEPARTMENT 2 FROM EMP
NUMBER AND SUB-GROUPED BY JOB 3 GROUP BY DEPTNO, JOB
WRITE A QUERY TO DISPLAY THE SUM, 1 SELECT DEPTNO, SUM(SAL), AVG(SAL), MAX(SAL),
AVERAGE, HIGHEST AND LOWEST SALARY OF MIN(SAL)
THE EMPLOYEES GROUPED BY DEPARTMENT 2 FROM EMP
NUMBER 3 GROUP BY DEPTNO

/
S. QUESTION ANSWER
O.
WRITE A QUERY TO DISPLAY THE SUM, 1 SELECT DEPTNO, JOB , SUM(SAL), AVG(SAL),
AVERAGE, HIGHEST AND LOWEST SALARY OF MIN(SAL),
THE EMPLOYEES GROUPED BY DEPARTMENT MAX(SAL)
AND SUB GROUPED BY JOB 2 FROM EMP
3 GROUP BY DEPTNO, JOB
WRITE A QUERY TO DISPLAY THE NUMBER OF 1 SELECT JOB, COUNT(*)
EMPLOYEE WITH THE SAME JOB 2 FROM EMP
3 GROUP BY JOB ;
WRITE A QUERY TO DISPLAY THE NUMBER OF SELECT JOB, COUNT(JOB) FROM EMP GROUP BY JOB;
EMPLOYEE WITH THE SAME JOB
WRITE A QUERY TO DISPLAY THE AVERAGE OF SELECT AVG( MIN(SAL) + MAX(SAL) ) FROM EMP
HIGHEST AND LOWEST SALARY OF EACH GROUP BY DEPTNO
DEPARTMENT
BEFORE THE FOLLOWING EXERCISE PLEASE NAME OF COLUMN TYPE
ENSURE THAT YOU ARE PROVIDED WITH A ID NUMBER(4)
TABLE EMPLOYEE WITH FOLLOWING FIRST_NAME VARCHAR2(30)
DESCRIPTION LAST_NAME VARCHAR2(30)
USER_ID VARCHAR(10)
SALARY NUMBER (9)

CREATE TABLE EMPLOYEE

( ID NUMBER(4),
FIRSTNAME VARCHAR(30),
LASTNAME VARCHAR2(30),
USERID VARCHAR2(10),
SALARY NUMBER (9)
);

USE DESCRIBE COMMAND TO ENSURE THE DESCRIBE EMPLOYEE

TABLE STRUCTURE
ADD THE FOLLOWING DATA IN THE ABOVE ADD THE DATE AS PER INSTRUCTIONS IN QUERY NO
TABLE AS INSTRUCTED
POPULATE TABLE WITH FIRST RECORD
MENTIONED THE COLUMN LIST IN THE INSERT INSERT INTO EMPLOYEE
CLAUSE (ID, FIRSTNAME, LASTNAME, USERID)
VALUES (1, ‘Dim’, ‘Joseph’, ‘Jdim’, 5000) ;
/
S. QUESTION ANSWER
O.
POPULATE TABLE WITH NEXT TWO RECORDS INSERT INTO EMPLOYEE
WITHOUT MENTIONING THE COLUMN LIST IN VALUES (2, ‘Jagannath’, ‘Mishra’, ‘jmishra’, 4000) ;
THE INSERT CLAUSE
INSERT INTO EMPLOYEE
VALUES (3, ‘Siddharth’, ‘Mishra’, ‘smishra’, 8000) ;

POPULATE WITH 4TH RECORD AND ENTER INSERT INTO EMPLOYEE (ID, FIRSTNAME)
ONLY ID AND FIRST_NAME VALUES (4, 'SHANKAR') ;
FOR RECORD WITH ID=4 UPDATE RECORD UPDATE EMPLOYEE
WITH LAST_NAME, USERID AND SALARY SET LASTNAME='Giri', USERID = 'sgiri', SALARY =
7000 ;
WHRE ID = 4
MAKE THE CHANGES PERMANENT COMMIT ;
MODIFY THE LASTNAME, OF EMPLOYEE 3 TO UPDATE EMPLOYEE
GOUTAM SET LASTNAME=’Goutam’
WHERE ID = 3 ;
MODIFY THE SALARY AND INCREASE IT BY UPDATE EMPLOYEE
1000, FOR ALL WHO GET SALARY LESS THEN SET SALARY = SALRY + 1000
5000 WHERE SALARY < 5000
DELETE THE EMPLOYEE RECORD HAVING DELETE FROM EMPLOYEE
FIRSTNAME = ‘Siddharth’ WHERE FIRSTNAME = ‘Siddharth’
Make the changes permanent COMMIT ;
REMOVE entire contents of the table DELETE FROM EMPLOYEE ;
CREATE A TABLE EMPLOYEE1 WITH COLUMNS CREATE TABLE EMPLOYEE1 AS (SELECT ID,
ID, FIRSTNAME, AND DEPTID FROM TABLE FIRSTNAME FROM EMPLOYEE) ;
EMPLOYEE AND ALSO CONFIRM THE
EXISTENCE OF TABLE EMPLOYEE1 ALTER TABLE EMPLOYEE1 ADD(DEPTID NUMBER(2)) ;

DESCRIBE EMPLOYEE1 ;
CREATE A VIEW VUEMP1 WHICH SHOULD CREATE VIEW VUEMP1
INCLUDE COLUMN EMPNO, ENAME, AND AS (SELECT EMPNO, ENAME, DEPTNO FROM EMP) ;
DEPTNO FROM TABLE EMP
CREATE A VIEW VUEMP2 WHICH SHOULD CREATE VIEW VUEMP2(EMPNUMBER, EMPLOYEE,
INCLUDE COLUMN EMPNO, ENAME, AND DEPARTMENT)
DEPTNO FROM TABLE EMP AND CHANGE THE AS (SELECT EMPNO, ENAME, DEPTNO FROM EMP)
COLUMN HEADING AS EMPNUMBER,
EMPLOYEE, DEPARTMENT. /
S. QUESTION ANSWER
O.
SELECT VIEWNAME AND TEXT FROM THE DATA SELECT VIEW_NAME, TEXT FROM USER_VIEWS ;
DICTIONARY USER_VIEWS RESULT
VIEW_NAME
------------------------------
TEXT
-------------------------------------------------------------
-------------------

VUEMP1
(SELECT EMPNO, ENAME, DEPTNO FROM EMP)

VUEMP2
(SELECT EMPNO, ENAME, DEPTNO FROM EMP)
CREATE THE TABLE DEPARTMENT TABLE SELECT VIEW_NAME, TEXT FROM USER_VIEWS ;
BASED ON THE FOLLOWING INSTANCE CHART RESULT
VIEW_NAME TEXT
-----------------------------------------------------
VUEMP1 (select empno, ename, deptno from
emp)
VUEMP2 (select empno, ename, deptno from
emp)

CREATE THE TABLE DEPARTMENT TABLE CREATE TABLE DEPARTMENT (ID NUMBER(8), DNAME
BASED ON THE FOLLOWING TABLE INSTANCE VARCHAR2(25)) ;
CHART
DESCRIBE DEPARTMENT ;
COL.NAME ID NAME
DATA TYPE NUMBER VARCHAR2
LENGTH 8 25
AND CONFIRM THE TABLE STRUCTURE
POPULATE THE TABLE DEPARTMENT WITH INSERT INTO DEPARTMENT
DATA FROM TABLE DEPT. INCLUDING ONLY SELECT DEPTNO, DNAME FROM DEPT ;
REQUIRED COLUMNS

/
S. QUESTION ANSWER
O.
CREATE THE TABLE EMPLOYEE BASED ON THE CREATE TABLE EMPLOYEE
FOLLOWING TABLE INSTANCE CHART ( ID NUMBER(8),
COL.NAME DATA TYPE LENGTH FIRSTNAME VARCHAR2(25),
ID NUMBER 8 LASTNAME VARCHAR(25),
FIRSTNAME VARCHAR2 25 DEPTID NUMBER(8) ) ;
LASTNAME VARCHAR2 25
DEPTID NUMBER 8

RENAME TABLE EMPLOYEE TO EMPLOYEE1 RENAME EMPLOYEE TO EMPLOYEE1 ;

DROP TABLE EMPLOYEE2 DROP TABLE EMPLOYEE2 ;
DROP TABLE EMPLOYEE AND DEPARTMENT DROP TABLE EMPLOYEE ;
DROP TABLE DEPARTMENT ;
CHANGE THE DATA TYPE OF COLUMN PINCODE ALTER TABLE CUSTOMER
TO VARCHAR2(10) IN THE TABLE CUSTOMER MODIFY (PINCODE VARCHAR2(10) ;
ADD ONE MORE COLUMN CUSTOMERINGROUP ALTER TABLE CUSTOMER
OF DATATYPE VARCHAR2(10) ADD(CUSTOMERINGROUP VARCHAR2(10) ;

Posted 18th February by JIGAR MEHTA

1 View comments

Anonymous March 10, 2020 at 12:55 AM

As reported by Stanford Medical, It's in fact the one and ONLY reason this country's women live 10 years longer and
weigh an average of 19 kilos lighter than we do.

(And realistically, it has NOTHING to do with genetics or some hard exercise and absolutely EVERYTHING about
"how" they are eating.)

BTW, What I said is "HOW", not "WHAT"...

Click this link to discover if this short test can help you decipher your true weight loss potential
Reply

/
Enter your comment...

Comment as: amisha.stkabir2 Sign out

Publish Preview Notify me