Logical Operator

WAQTD DETAILS OF THE EMPLOYEES WORKING AS CLERK AND EARNING LESS THAN 1500
WAQTD NAME AND HIREDATE OF THE EMPLOYEES WORKING AS MANAGER IN DEPT 30
WAQTD DETAILS OF THE EMP ALONG WITH ANNUAL SALARY IF THEY ARE WORKING IN DEPT 30 AS
SALESMAN AND THEIR ANNUAL SALARY HAS TO BE GREATER THAN 14000
WAQTD ALL THE DETAILS OF THE EMP WORKING IN DEPT 30 OR AS ANALYST
WAQTD NAMES OF THE EMPMLOYEES WHOS SALARY IS LESS THAN 1100 AND THEIR DESIGNATION IS
CLERK
WAQTD NAME AND SAL , ANNUAL SAL AND DEPTNO IF DEPTNO IS 20 EARNING MORE THAN 1100 AND
ANNUAL SALARY EXCEEDS 12000.
WAQTD EMPNO AND NAMES OF THE EMPLOYEES WORKING AS MANAGER IN DEPT 20
WAQTD DETAILS OF EMPLOYEES WORKING IN DEPT 20 OR 30
WAQTD DETAILS OF EMPLOYEES WORKING AS AN ANALYST IN DEPT 20
WAQTD DETAILS OF EMPLOYEE WORKING AS PRESIDENT WITH SALARY OF RUPEES 5000
WAQTD NAMES AND DEPTNO , JOB OF EMPS WORKING AS CLERK IN DEPT 10 OR 20
WAQTD DETAILS OF EMPLOYEES WORKING AS CLERK OR MANAGER IN DEPT 10
WAQTD DETAILS OF EMPLOYEES WITH EMPNO 7902,7839 AND NOT WORKING AS SALESMAN.
WAQTD DETAILS OF EMPLOYEES WORKING AS MANAGER OR SALESMAN OR CLERK
WAQTD NAMES OF EMPLOYEES HIRED AFTER 81 AND BEFORE 87
WAQTD DETAILS OF EMPLOYEES EARNING MORE THAN 1250 BUT LESS THAN 3000
WAQTD NAMES OF EMPLOYEES HIRED AFTER 81 AND DEPTNO IS NOT 10.
WAQTD NAMES OF EMPLOYEES ALONG WITH ANNUAL SALARY FOR THE EMPLOYEES WORKING AS
MANAGER OR CLERK INTO DEPT 10 OR 30
WAQTD ALL THE DETAILS ALONG WITH ANNUAL SALARY IF SAL IS BETWEEN 1000 AND 4000 ANNUAL
SALARY MORE THAN 15000
WAQTD NAMES OF EMPLOYEES WORKING IN DEPT 10 , 20 , 30 , 40

Generated with PDFtion

Special Operators
LIST ALL THE EMPLOYEES WHOSE COMMISSON IS NULL
LIST ALL THE EMPLOYEES WHO DON’T HAVE A REPORTING MANAGER
LIST ALL THE SALESMEN IN DEPT 30
LIST ALL THE SALESMEN IN DEPT NUMBER 30 AND HAVING SALARY GREATER THAN 1500
LIST ALL THE EMPLOYEES WHOSE NAME STARTS WITH ‘S’ OR ‘A’
LIST ALL THE EMPLOYEES EXCEPT THOSE WHO ARE WORKING IN DEPT 10 & 20.
LIST THE EMPLOYEES WHOSE NAME DOES NOT START WITH ‘S’
LIST ALL THE EMPLOYEES WHO ARE HAVING REPORTING MANAGERS IN DEPT 10
LIST ALL THE EMPLOYEES WHOSE COMMISSION IS NULL AND WORKING AS CLERK
LIST ALL THE EMPLOYEES WHO DON’T HAVE A REPORTING MANAGER IN DEPTNO 10 OR 30
LIST ALL THE SALESMEN IN DEPT 30 WITH SAL MORE THAN 2450
LIST ALL THE ANALYST IN DEPT NUMBER 20 AND HAVING SALARY GREATER THAN 2500
LIST ALL THE EMPLOYEES WHOSE NAME STARTS WITH ‘M’ OR ‘J’
LIST ALL THE EMPLOYEES WITH ANNUAL SALARY EXCEPT THOSE WHO ARE WORKING IN DEPT 30
LIST THE EMPLOYEES WHOSE NAME DOES NOT END WITH ‘ES’ OR ‘R’.
LIST ALL THE EMPLOYEES WHO ARE HAVING REPORTING MANAGERS IN DEPT 10
DISPLAY ALL THE EMPLOYEE WHO ARE 'SALESMANS HAVING E AS THE LAST BUT ONE CHARACTER
(second last) IN ENAME BUT SALARY HAVING EXACTLY 4 CHARACTER
DISPLAY ALL THE EMPLOYEE WHO ARE JOINED AFTER YEAR 81
DISPLAY ALL THE EMPLOYEE WHO ARE JOINED IN FEB
LIST THE EMPLOYEES WHO ARE NOT WORKING AS MANAGERS AND CLERKS IN DEPT 10 AND 20 WITH A
SALARY IN THE RANGE OF 1000 TO 3000.

In operator
List employees who work in departments 10, 20, or 30.
Find employees whose job is in CLERK OR MANAGER
Show employees whose salary is 1250 OR 1600 OR 2450.
List employees whose ename is SMITH OR JONES OR KING.
Find employees who received commissions of 300, 500, or 1400.
List employees whose empno is 7369, 7521, 7900.

NOT IN

List employees who don’t work in departments 10 or 20.

Show employees whose job is not in ('CLERK', 'ANALYST').
Display employees whose manager is not in (7839, 7698).
Find employees whose salary is not in (1250, 1600).
Show employees whose names are not in (’ALLEN', 'SCOTT', 'MARTIN').
Display employees who have not received commissions in (0, 300, 500).
Display employees whose empno is not in (7521, 7844, 7934).

BETWEEN Operator

List employees whose salary is between 1000 and 3000.

Show employees hired in the year 1981.
Display employees whose empno is between 7500 and 7900.
Show employees whose commission is between 300 and 1500.
Find employees whose job title is between 'ANALYST' and 'SALESMAN'
Display employees hired between the year 81 and 90.
Show employees with salary between 1250 and 2850.

Generated with PDFtion

IS NULL / IS NOT NULL

WAQTD name , salary and commission OF THE EMPLOYEE WHO IS NOT EARNING COMM.
WAQTD the details of employee who is not earning salary
WAQTD details of employees who are getting commission.
WAQTD name , sal and comm of employees who is earning commission.
WAQTD details of employees who does not have a manager.
WAQTD details of employee who have manager
WAQTD employee who have a department.
WAQTD the Names of department who have a location.
WAQTD employees whose commission is 0 (0 is not a NULL value ).

LIKE Operator
WAQTD employees whose name starts with 'S'.
WAQTD employees whose name ends with 'N'.
WAQTD employees whose name contains 'AR'.
WAQTD employees whose name has second letter 'L'.
WAQTD employees whose job title ends with 'MAN'.
WAQTD employees whose job has 'MAN' in it.
WAQTD employees whose job title starts with ‘MAN'.
WAQTD employees whose names start with 'M' and end with 'R'.
WAQTD employees whose names have exactly 5 characters.
WAQTD employees whose job title starts with 'A' and ends with 'T'.

Generated with PDFtion

Set Operators
USE SET OPERATORS

WAQTD names of employees from department 10 combined with names of employees from department 20.
List names and sal of employees from department 10 combined with names and sal of employees from department
20, including any duplicates.
List sal, hiredate of employees that appear in both department 10 and department 20.
List names of employees from department 10 that do not appear in department 20.
List department numbers that are present in both the EMP and DEPT tables.
List department numbers that are present in the EMP table but do not appear in the DEPT table.
List names of employees from department 30 combined with names of employees from department 40, also
remove duplicates.
List names of employees from departments 10, combined with dept 20, combined with 30 including duplicates.
List department names that have no matching employee names.
List names of employees that occur in both department 10 and 30.

WAQTD unique department numbers from emp table (without using distinct)
WAQTD the Combined department numbers from the EMP and DEPT tables. ( without repeatation )
WAQTD all department numbers from both EMP and DEPT, including duplicates.
WAQTD the department numbers that exist in both EMP and DEPT.
WAQTD the department numbers present in DEPT but not used in EMP.
WAQTD all unique jobs from EMP combined with all unique department names from DEPT.
WAQTD the HIREDATES of employees who salary is greater than 2000 along with all the HIREDATES of
employees working in dept 30 (using set operators ).
WAQTD the names of employees who are working as clerk but not in the dept 10.
WAQTD the employee number of employee who are not managers.
WAQTD the employee number of employee who are managers.
WAQTD the job OR name of the department if the job title and department name is same.

Generated with PDFtion

Multi row Functions
WAQTD maximum salary given to a manager.
WAQTD Total salary given to dept 10
WAQTD number of employees earing more than 1500 in dept 20.
WAQTD number of employee having 'E' in their names.
WAQTD minimum salary given to the employees working as clerk in Dept 10 or 20 .
WAQTD number of employees hired after 1982 and before 1985 into Dept 10 or 30.
WAQTD number of employees getting commission.
WAQTD maximum salary given to employees if the emp has character 'S' in the name and works as a Manager in
dept 10 with a salary more than 1800.
WAQTD NUMBER OF EMPLOYEES GETTING SALARY LESS THAN 2000 IN DEPTNO 10
WAQTD TOTAL SALARY NEEDED TO PAY EMPLOYEES WORKING AS CLERK
WAQTD AVERAGE SALARY NEEDED TO PAY ALL EMPLOYEES
WAQTD NUMBER OF EMPLOYEES HAVING 'A' AS THEIR FIRST CHARACTER
WAQTD NUMBER OF EMPLOYEES WORKING AS CLERK OR MANAGER
WAQTD TOTAL SALARY NEEDED TO PAY EMPLOYEES HIRED IN FEB
WAQTD NUMBER OF EMPLOYEES REPORTING TO 7839 (MGR)
WAQTD NUMBER OF EMPLOYEES GETTING COMISSION IN DEPTNO 30
WAQTD AVG SAL , TOTAL SAL , NUMBER OF EMPS AND MAXIMUM SALARY GIVEN TO EMPLOYEES
WORKING AS PERSIDENT
WAQTD NUMBER OF EMPLOYEES HAVING 'A' IN THEIR NAMES
WAQTD NUMBER OF EMPS AND TOTAL SALARY NEEDED TO PAY THE EMPLOYEES WHO HAVE 2
CONSICUTIVE L's IN THEIR NAMES.
WAQTD NUMBER OF DEPARTMENTS PRESENT IN EMPLOYEE TABLE.
WAQTD NUMBER OF EMPLOYEES HAVING CHARACTER 'Z' IN THEIR NAMES.
WAQTD NUMBER OF EMPLOYEES HAVING '$' IN THEIR NAMES.
WAQTD TOTAL SALARY GIVEN TO EMPLOYEES WORKING AS CLERK IN DEPT 30
WAQTD NUMBER OF DISTINCT SALARIES PRESENT IN EMPLOYEE TABLE
WAQTD MINIMUM SALARY GIVEN TO THE EMPLOYEES WHO WORK IN DEPT 10 AS MANAGER OR A
CLERK

Generated with PDFtion

Group By Clause
WAQTD NUMBER OF EMPLOYEES WORKING IN EACH DEPARTMENT.
WAQTD MAXIMUM SALARY GIVEN TO EACH JOB.
WAQTD MAX SAL & MIN SAL OF THE EMPLOYEE IN EACH DEPT IF THE EMPLOYEE NAME STARTS WITH
CHARACTER A.
WAQTD number of employees working in each dept except the Employee working as analyst.
WAQTD number of employees getting commission in each dept.
WAQTD the average salary paid to each job.
WAQTD the hire-dates without repetition, i.e unique hire-dates.
WAQTD the salaries without repetition.
WAQTD the number of employees working in each department along with the total money spent on salaries,
minimum salary, maximum salary and average salary in each department.
WAQTD NUMBER OF EMPLOYEES WORKING IN EACH DEPARTEMENT EXCEPT PRESIDENT
WAQTD TOTAL SALARY NEEDED TO PAY ALL THE EMPLOYEES IN EACH JOB
WAQTD NUMBER OF EMPLOYEEES WORKING AS MANAGER IN EACH DEPARTMENT
WAQTD AVG SALARY NEEDED TO PAY ALL THE EMPLOYEES IN EACH DEPARTMENT EXCLUDING THE
EMPLOYEES OF DEPTNO 20
WAQTD NUMBER OF EMPLOYEES HAVING CHARACTER 'A' IN THEIR NAMES IN EACH JOB
WAQTD NUMBER OF EMPLOYEES AND AVG SALARY NEEDED TO PAY THE EMPLOYEES WHO SALARY IN
GREATER THAN 2000 IN EACH DEPT
WAQDTD TOTAL SALARY NEEDED TO PAY AND NUMBER OF SALESMANS IN EACH DEPT
WAQTD NUMBER OF EMPLOYEES WITH THEIR MAXIMUM SALARIES IN EACH JOB
WAQTD MAXIMUM SALARIES GIVEN TO AN EMPLOYEE WORKING IN EACH DEPT
WAQTD NUMBER OF TIMES A SALARIES IS PRESENT IN EMPLOYEE TABLE
WAQTD THE NUMBER OF EMPLOYEE WHO WERE HIRED ON THE SAME DATE AND IN THE SAME
DEPARTMENT
WAQTD THE AVERAGE SALARY OF ALL THE MANAGERS
WAQTD THE SUM OF SALARIES OF ALL THE EMPLOYEES WHO ARE WORKING IN THE SAME
DEPARTMENT AND EARNING SAME SALARIES.

Generated with PDFtion

Having Clause
WAQ to find number of employees working in each Dept if there are at least 3 employees in each dept.
WAQTD the SAL that are repeated .
WAQTD SAL that are repeated exactly twice.
WAQTD department number and number of employees working in the dept if the department have At least 2 emp
who have Character 'A' or 'S' in their names
WAQTD job and total salary of each job , if the total salary of each job is greater than 3000.
WAQTD job and total salary of the employees working in the job if the employee is earning more than 1500.
WAQTD DEPTNO AND NUMBER OF CLERKS WORKING IN DEPT IF THE DEPARTMENT HAVE ATLEAST 2
CLERKS.
WAQTD DEPTNO AND TOTAL SALARY NEEDED TO PAY THE SALESMAN IN THE DEPT HAS ATLEAST 3
SALESMAN.
WAQTD NUMBER OF EMP EARNING SAL MORE THAN 1200 IN EACH JOB AND THE TOTAL SAL NEEDED TO
PAY THE JOB EXCEEDS 3500.
WAQTD DEPTNO AND NUMBER OF MANAGERS WORKING ONLY IF THERE ARE 2 EMP WORKING IN EACH
DEPT AS MANAGER .
WAQTD JOB AND MAX SAL OF EMP IN EACH JOB IF THE MAX SAL EXCEEDS 2600
WAQTD THE SALARIES WHICH ARE REPEATED IN EMP TABLE
WAQTD THE HIREDATE WHICH ARE DUPLICATED IN EMP TABLE
WAQTD AVG SALARY OF EACH DEPT IF AVG SAL IS LESS THAN 3000
WAQTD MIN AND MAX SALARIES OF EACH JOB IF MIN SAL IS MORE THAN 1000 AND MAX SAL IS LESS
THAN 5000.
WAQTD THE NAME AND SALARIES OF ALL THE EMPLOYEES, IN ASCENDING ORDER OF NAME.

Generated with PDFtion

Order By Clause
WAQTD THE DETAILS OF EMPLOYEE ORDERED ACCORDING TO THE DEPTNO
WAQTD THE ENAME, SAL OF EMPLOYEES ORDERED ACCORDING TO THE SALARIES
WAQTD THE HIREDATE, SAL AND ENAME IN THE ASCEDING ORDER OF NAME AND SALARY
WAQTD THE EMPNO AND ENAME ARRANGED ACCORDING TO THE EMPNO
ARRANGE THE NAME IN ASCENDING ORDER AND IF NAME IS SAME THEN THE SALARIES IN
DESCENDING ORDER
WAQTD THE NUMBER OF EMPLOYEES PRESENT IN EACH DEPARTMENT IN INCREASING ORDER OF THE
EMPLOYEE COUNT.
WAQTD THE NAME, SAL AND HIREDATE OF EMPLOYEES WHO'S SALARY IS GREATER THAN 2000 AND HE
IS NOT WORKING AS MANAGER , ORDER THE NAMES ACCORDING TO THE SALARY.
WAQTD THE DEPARTMENT NAMES IN ALPHABETICAL ORDER.
WAQTD THE DETAILS OF DEPARTMENT ARRANGED ACCORDING TO THE LOCATION
WAQTD THE MINIMUM, MAXIMUM AND AVERAGE SALARY OF EACH DEPARTMENT ARRANGED
ACCORDING TO THE DEPTNO.
WAQTD THE DETAILS OF EMPLOYEE ARRANGED ACCORDING TO THE DEPTNO IN ASCENDING ORDER ,
AND IN THOSE DEPARTMENTS ARRANGE THE EMPLOYEES ACCORDING TO THE NAMES.
WAQTD THE SALARY GRADE DETAILS ARRANGED ACCORDING TO THE LOWER SALARY
ARRANGE THE RECORDS PRESENT IN DEPARTMENT TABLE IN DESCENDING ORDER OF THE
DEPARTMENT NAMES AND ASCENDING ORDER OF LOCATIONS
WAQTD THE ENAME ACCORDING TO THE SENIORITY BASED ON THE YEAR OF EXPERIENCE, MOST
SENIOR AT THE TOP AND THEN THE ORDER FOLLOWS
WAQTD THE SALARY OF SMITH.
WAQTD THE DETAILS OF EMPLOYEE EARNING MORE THAN WARD.

Generated with PDFtion

Sub Query
WAQTD NAME OF THE EMPLOYEE WHO IS WORKING IN SAME DEPT AS SMITH.
WAQTD DETAILS OF THE EMPLOYEE WHO IS DESIGNATION IS SAME AS WARD.
WAQTD DETAILS OF THE EMPLOYEE WHO IS HIRED ON THE SAME DATE AS FORD
WAQTD name and DEPTNO of the employees working in the same Dept as SMITH.
WAQTD name and HIREDATE of the employees if the employee Was hired after JONES.
WAQTD all the details of the employee working in the same designation as KING.
WAQTD NAME , SAL , DEPTNO of the employees if the employees earn more than 2000 and work in the same
dept as JAMES .
WAQTD all the details of the employees working in the same designation as MILLER and earning more than 1500.
WAQTD details of the employees earning more than SMITH but less than KING.
WAQTD name , SAL and DEPTNO of the employees if the employee is earning commission in dept 20 and earning
salary more than Scott.
WAQTD name and HIREDATE of the employees who's name ends with 'S' and hired after James

Assignment Questions ( CASE 1 ):

WAQTD NAME OF THE EMPLOYEES EARNING MORE THAN ADAMS
WAQTD NAME AND SALARY OF THE EMPLOYEES EARNING LESS THAN KING
WAQTD NAME AND DEPTNO OF THE EMPLOYEES IF THEY ARE WORKING IN THE SAME DEPT AS JONES.
WAQTD NAME AND JOB OF ALL THE EMPLOYEES WORKING IN THE SAME DESIGNATION AS JAMES.
WAQTD EMPNO AND ENAME ALONG WITH ANNUAL SALARY OF ALL THE EMPLOYEES IF THEIR ANNUAL
SALARY IS GREATER THAN WARDS ANNUAL SALARY.
WAQTD NAME AND HIREDATE OF THE EMPLOYEES IF THEY ARE HIRED BEFORE SCOTT
WAQTD NAME AND HIREDATE OF THE EMPLOYEES IF THEY ARE HIRED AFTER THE PRESIDENT
WAQTD NAME AND SAL OF THE EMPLOYEE IF THEY ARE EARNING SAL LESS THAN THE EMPLOYEE
WHOS EMPNO IS 7839
WAQTD ALL THE DETAILS OF THE EMPLOYEES IF THE EMPLOYEES ARE HIRED BEFORE MILLER
WAQTD ENAME AND EMPNO OF THE EMPLOYEES IF EMPLOYEES ARE EARNING MORE THAN ALLEN
WAQTD ENAME AND SALARY OF ALL THE EMPLOYEES WHO ARE EARNING MORE THAN MILLER BUT
LESS THAN ALLEN
WAQTD ALL THE DETAILS OF THE EMPLOYEES WORKING IN DEPT 20 AND WORKING IN THE SAME
DESIGNATION AS SMITH
WAQTD ALL THE DETAILS OF THE EMPLOYEES WORKING AS MANAGER IN THE SAME DEPT AS TURNER
WAQTD NAME AND HIREDATE OF THE EMPLOYEES HIRED AFTER 1980 AND BEFORE KING
WAQTD NAME AND SAL ALONG WITH ANNUAL SAL FOR ALL EMPLOYEES WHOS SAL IS LESS THAN
BLAKE AND MORE THAN 3500
WAQTD ALL THE DETAILS OF EMPLOYEES WHO EARN MORE THAN SCOTT BUT LESS THAN KING
WAQTD NAME OF THE EMPLOYEES WHOS NAME STARTS WITH ‘A’ AND WORKS IN THE SAME DEPT AS
BLAKE
WAQTD NAME AND COMM IF EMPLOYEES EARN COMISSION AND WORK IN THE SAME DESIGNATION AS
SMITH
WAQTD DETAILS OF ALL THE EMPLOYEES WORKING AS CLERK IN THE SAME DEPT AS TURNER
WAQTD ENAME, SAL AND DESIGNATION OF THE EMPLOYEES WHOS ANNUAL SALARY IS MORE THAN
SMITH AND LESS THAN KING

CASE 2 :
When the data to be fetched and the condition to be executed is present in different table, use SUB-QUERY to
execute the condition.

WAQTD DEPTNO of the employee whose name is SMITH

Generated with PDFtion

 WAQTD DNAME of the employee whose name is MILLER.
WAQTD Location of ADAMS
WAQTD names of the employees working in Location L2

Assignments Questions ( CASE 2 ):

WAQTD DNAME OF THE EMPLOYEES WHOS NAME IS SMITH.
WAQTD DNAME AND LOC OF THE EMPLOYEE WHOS ENAME IS KING.
WAQTD LOC OF THE EMP WHOS EMPLOYEE NUMBER IS 7902.
WAQTD DNAME OF THE EMPLOYEE WHOS DESIGNATION IS PRESIDENT.
WAQTD NAMES OF THE EMPLOYEES WORKING IN ACCOUNTING DEPARTMENT.
WAQTD DETAILS OF THE EMPLOYEES WORKING IN SALES.
WAQTD DETAILS OF THE EMP ALONG WITH ANNUAL SALARY IF EMPLOYEES ARE WORKING IN NEW
YORK.
WAQTD NAMES OF EMPLOYEES WORKING IN OPERATIONS DEPARTMENT
WAQTD THE LOCATION OF SALESMAN.
WAQTD DNAME AND LOC ALONG WITH DEPTNO OF THE EMPLOYEE WHO’S NAME ENDS WITH ‘R’.

ASSIGNMENT ON CASE 1 & 2

WAQTD NAMES OF THE EMPLOYEES EARNING MORE THAN SCOTT IN ACCOUNTING DEPT
WAQTD DETAILS OF THE EMPLOYEES WORKING AS MANAGER IN THE LOCATION CHICAGO
WAQTD NAME AND SAL OF THE EMPLOYEES EARNING MORE THAN KING IN THE DEPT ACCOUNTING
WAQTD DETAILS OF THE EMPLOYEES WORKING AS SALESMAN IN THE DEPARTEMENT SALES.
WAQTD NAME , SAL , JOB , HIREDATE OF THE EMPLOYEES WORKING IN OPERATIONS DEPARTMENT
AND HIRED BEFORE KING.
DISPLAY ALL THE EMPLOYEES WHOSE DEPARTMENT NAME ENDS 'S'.
WAQTD DNAME OF THE EMPLOYEES WHOS NAMES HAS CHARACTER 'A' IN IT .
WAQTD DNAME AND LOC OF THE EMPLOYEES WHOS SALARY IS RUPEES 800 .
WAQTD DNAME OF THE EMPLOYEES WHO EARN COMISSION
WAQTD LOC OF THE EMPLOYEES IF THEY EARN COMISSION IN DEPT 40
WAQTD the name of employee earning highest salary.A

NESTED SUBQUERY :

nth MIN(>) & MAX(<)

WAQTD DETAILS OF EMPLOYEE EARNING HIGHEST SALARY IN EMP TABLE.
WAQTD THE NAME OF EMPLOYEE EARNING MAXIMUM COMMISSION IN EMP TABLE.
WAQTD THE EMPLOYEE WHO IS EARNING 2nd HIGHEST SALARY IN EMP TABLE.
WAQTD THE DETAILS OF EMPLOYEE EARNING 7th MAX SALARY IN EMP TABLE.
WAQTD DETAILS OF EMPLOYEE EARNING LOWEST SALARY IN EMP TABLE.
WAQTD THE DETAILS OF EMPLOYEE EARNING 2ND MINIMUM SALARY AMONG ALL EMPLOYEES.
WAQTD THE DETAILS OF EMPLOYEE EARNING 3rd MINIMUM SALARY AMONG ALL EMPLOYEES.
WAQTD DETAILS OF THE EMPLOYEES HIRED FIRST.
WAQTD NAME AND HIREDATE OF THE EMPLOYEES HIRED AT THE LAST
WAQTD NAME, COMM OF THE EMPLOYEE WHO EARNS 2ND MIN COMISSION
WAQTD NAME, SAL AND COMM OF THE EMPLOYEE EARNING 3RD MAXIMUM COMISSION
WAQTD DETAILS OF THE EMPLOYEE WHO HAS GREATEST EMPNO
WAQTD DETAILS OF THE EMPLOYEES EARNING LEAST ANNUAL SALARY
WAQTD 2ND MINIMUM SALARY
WAQTD 5TH MAXIMUM SALARY
WAQTD NAME OF THE EMPLOYEE EARNING 3RD MAXIMUM SALARY
WAQTD DEPARTMENT NAME OF AN EMPLOYEE GETTING 4TH MAX SAL

Generated with PDFtion

 WAQTD DETAILS OF THE EMPLOYEE WHO WAS HIRED 2nd
WAQTD NAME OF THE EMPLOYEE HIRED BEFORE THE LAST EMPLOYEE
WAQTD LOC OF THE EMPLOYEE WHO WAS HIRED FIRST
WAQTD DETAILS OF THE EMPLOYEE EARNING 7TH MINIMUM SALARY

ALL & ANY

WAQTD NAME OF THE EMPLOYEES EARNING SALARY MORE THAN THE SALESMAN.
WAQTD DETAILS OF THE EMPLOYEES HIRED AFTER ALL THE CLERKS.
WAQTD NAME AND SALARY FOR ALL THE EMPLOYEES IF THEY ARE EARNING LESS THAN ATLEST A
MANAGER.
WAQTD NAME AND HIREDATE OF EMPLOYEES HIRED BEFORE ALL THE MANAGERS.
WAQTD NAMES OF THE EMPLOYEES HIRED AFTER ALL THE MANAGERS AND EARNING SALARY MORE
THAN ALL THE CLERKS.
WAQTD DETAILS OF THE EMPLOYEES WORKING AS CLERK AND HIRED BEFORE ATLEST A SALESMAN.
WAQTD EMP NAMES IF EMPLOYEES ARE HIRED AFTER ALL THE EMPLOYEES OF DEPT 10

EMP-MGR
WAQTD SMITHS REPORTING MANAGER'S NAME
WAQTD ADAMS MANAGER'S MANAGER NAME
WAQTD DNAME OF JONES MANAGER
WAQTD MILLER'S MANAGER'S SALAR
WAQTD LOC OF SMITH'S MANAGER'S MANAGER.
WAQTD NAME OF THE EMPLOYEES REPORTING TO BLAKE
WAQTD NUMBER OF EMPLPOYEES REPORTING TO KING
WAQTD DETAILS OF THE EMPLOYEES REPORTING TO JONES
WAQTD ENAMES OF THE EMPLOYEES REPORTING TO BLAKE'S MANAGER
WAQTD NUMBER OF EMPLOYEES REPORTING TO FORD'S MANAGER

.WAQTD DNAME IN WHICH THERE ARE ATLEAST 5 EMPLOYEES WORKING

WAQTD LOC IN WHICH THERE ARE EXACTLY 3 EMPLOYEES WORKING
WAQTD DETAILS OF THE EMPLOYEES EARNING SAME SALARY
WAQTD SALARIES OF THE EMPLOYEES HAVING SAME NAMES
WAQTD DETAILS OF THE EMPLOYEES GETTING SAME SALARY IN THE SAME DEPARTMENT .
WAQTD DETAILS OF THE EMPLOYEES HIRED ON THE SAME DAY INTO THE SAME DEPARTMENT .
WAQTD NAME AND SAL OF THE EMPLOYEES IF THEIR NAME IS REPEATED ATLEST 4 TIMES IN
EMPLOYEE TABLE .
WAQTD DETAILS OF THE EMPLOYEES HAVING SAME DESIGNATIONS IN THE SAME DEPARTMENT .
WAQTD DETAILS OF THE EMPLOYEES HAVING SAME NAMES IN THE SAME JOB .
WAQTD DETAILS OF THE EMPLOYEE TO WHOM ATLEST 3 EMPLOYEES REPORT.

Generated with PDFtion

Joins
1. Cross / Cartesian JOIN. SQL

Cartesian / Cross join the records of table 1 will merge with all the records of table 2.
Syntax:

-- ANSI
SELECT COL_NAME / EXP
FROM TABLE_1 CROSS JOIN TABLE_2;

-- ORACLE
SELECT COL_NAME / EXP
FROM TABLE_1 , TABLE_2 ;

NOTE:
The no. of column present in result table will be equal to the summation of
column present in both the table.
The no. of records present in result table will be equal to the product of ROWS
present in both the table.

Questions :
WAQTD THE DETAILS OF EMP AND DEPT TABLE.
WAQTD THE NAME AND DEPARTMENT NAME OF THE EMPLOYEE.
Display all combinations of employees and departments.
List all EMPNO and LOC combinations.
Count total rows returned by the cross join of EMP and DEPT.
WAQDT the details from a CROSS JOIN of EMP AND DEPT and sort the result by ENAME.
WAQTD the list showing every department name combined with every employee name starting with ‘S’ .
WAQTD THE DEPARTMENT NUMBERS FROM employee and department TABLE.
Show job titles with all combinations of department names.
Generate a list of all possible EMPNO and DEPTNO pairings.
Show all combinations of employee names and salary grades.
Create a Cartesian product between EMP and SALGRADE tables.
Pair each department with every possible hire date from EMP.

2. INNER / EQUI JOIN

Returns only the matching rows from both tables based on the join condition.

-- ANSI SYNTAX
SELECT COL_NAME/EXP
FROM TABLE_1
INNER JOIN TABLE_2 ON <JOIN_CONDITION>;

-- ORACLE SYNTAX
SELECT COL_NAME/ EXP
FROM TABLE_1 , TABLE_2
WHERE <JOIN_CONDITION>;

JOIN CONDITION : used to specify the condition on which you want to join the tables.

WAQTD THE DETAILS OF THE EMPLOYEE ALONG WITH THE DEPARTMENT THEY ARE WORKING IN.
WAQTD NAME OF THE EMPLOYEE AND HIS DEPARTMENT NAME.
WAQTD THE NAME, SALARY AND LOCATION OF THE EMPLOYEE WHO ARE EARNING MORE THAN 2000.

Generated with PDFtion

 WAQTD NAME , DEPARTMENT NAME AND HIREDATE OF EMPLOYEE WHO ARE WORKING IN RESEARCH
DEPARTMENT AND HIRED BEFORE 1987.
WAQTD ENAME AND DEPTNO AND DNAME OF EMPLOYEES WHO ARE WORKING AS MANAGER.
WAQTD ENAME AND SALARY ALONG WITH DEPT DETAILS OF EMPLOYEES WHO ARE WORKING IN DEPT
10.
WAQTD DETAILS OF THE EMPLOYEES ALONG WITH THE ANNUAL SALARY & LOC IF THE EMPLOYEES IS
WORKING AS CLERK.
WAQTD THE NAMES OF EMPLOYEE WORKING IN RESEARCH DEPARTMENT.
WAQTD THE NAME AND LOCATION OF EMPLOYEES WHO’S LOCATION IS DALLAS.

SELECT ENAME , LOC

FROM EMP
INNER JOIN DEPT ON [Link] = [Link]
WHERE LOC = 'DALLAS';

DISPLAY THE NAME , SAL, DEPARTMENT NAME AND LOCATION OF EMPLOYEES IN THE ASCENDING
ORDER OF ENAME.

SELECT ENAME, SAL, DNAME, LOC

FROM EMP
INNER JOIN DEPT ON [Link] = [Link]
ORDER BY ENAME;

WAQTD THE NAME AND SALARY AND DEPARTMENT NAME OF EMPLOYEE WHO ARE EARNING MORE
THAN 1500.
WAQTD THE NAME , DEPARTMENT NAME AND SALARY OF EMPLOYEE WHO ARE HIRED AFTER 01-JAN-
1985.
WAQTD THE DEPARTMENT NAME AND THE NUMBER OF EMPLOYEES WORKING IN THAT DEPARTMENT.

SELECT DNAME, COUNT(*)

FROM EMP
INNER JOIN DEPT ON [Link] = [Link]
GROUP BY DNAME;

WAQTD ENAME, DNAME for employees working in department numbers 10 or 30.

Note :
Filters out unmatched rows.
Join condition must be specified using ON ( ANSI ) or WHERE ( oracle ).
Filter nulls/unmatched records by default.

3. OUTER JOIN
IT IS USED TO FETCH UNMATCHED RECORDS ALONG WITH MATCHING RECORDS

Types of Outer join

LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN

1. LEFT OUTER JOIN

→ It will fetch the matching records from both the table and unmatched records form LEFT TABLE.

-- ANSI
SELECT ..
FROM T_N_1 LEFT JOIN T_N_2
ON <JOIN_CONDITION>;

-- ORACLE

Generated with PDFtion

 SELECT ...
FROM T1,T2
WHERE T1.COL_NAME = T2.COL_NAME(+);

Questions :

2. RIGHT OUTER JOIN

→ It will fetch the matching records from both the table and unmatched records form RIGHT TABLE.

-- ANSI
SELECT ..
FROM T_N_1
RIGHT JOIN T_N_2 ON <JOIN_CONDITION>;

-- ORACLE

SELECT ...
FROM T1,T2
WHERE T1.COL_NAME(+) = T2.COL_NAME;

Questions:
WAQTD all the details of departments and their employees, including departments with no employees.
WAQTD department names and employee names, even if no employee exists.
WAQTD all DEPT locations with the employees working there.
WAQTD DEPTNOs and matching EMPNOs, including departments without any employee.
WAQTD department names and job roles if any assigned.
WAQTD hire dates of employees and department name ( or NULL )
WAQTD all departments and total employees if present.
WAQTD DEPTNO and ENAME, including DEPTNOs without employees.
WAQTD departments if they have a MANAGER working in it.
WAQTD department names with average salary if any employee exists.

3. FULL OUTER JOIN

It will return all matching records from both table, and unmatched records from both table.

-- ANSI
SELECT COL/EXP
FROM TN_1 FULL JOIN TN_2
ON <JOIN_CONDITION>;

Questions :
WAQTD employee names and department names, including unmatched records.
WAQTD employee and department data where either may be missing.
WAQTD all employee names and department locations.
WAQTD employee names and department names for all, including those without matches.
WAQTD EMPNO and DNAME for all combinations including NULLs.
WAQTD employees without departments and departments without employees.
WAQTD EMP and DEPT data for all valid and invalid mappings.
WAQTD EMP's ENAME and DEPT's LOC even if there’s no match.
WAQTD employee names with department numbers or NULL.
WAQTD departments with or without employees.
WAQTD employees AND LOCATION IF EXISTS who are not assigned to any department.
WAQTD all EMP and DEPT data with unmatched entries clearly visible.
WAQTD unmatched employees and unmatched departments in one query.
WAQTD all hire dates and department locations together.
WAQTD all job titles with all department names.

Natural Join
Only ANSI Syntax is there.

Generated with PDFtion

 -- ANSI
SELECT col_name/exp
FROM TN_1 NATURAL JOIN TN_2;

→ It will show two behavior :

CROSS JOIN : when there is no common/matching column in two tables.
INNER JOIN : when there is a common//matching column.

Questions
Use NATURAL JOIN to show EMP and DEPT details.
Display employee name and department name using NATURAL JOIN.
Show ENAME, JOB, LOC using EMP and DEPT natural join.
Join EMP and DEPT with NATURAL JOIN and list all fields.
Use NATURAL JOIN to get ENAME and DEPTNO.
Display DNAME and JOB using EMP and DEPT.
List employees and department locations using NATURAL JOIN.
Show employee details along with department location.(if possible).
Display ENAME, SAL, LOC, DNAME using NATURAL JOIN.
Use NATURAL JOIN to display EMP and SALGRADE details.

5. Self Join.
→ Joining a table to itself.

-- ANSI
SELECT COL_NAME / EXP
FROM table_name A1 JOIN table_name A2
ON A1.COL_NAME = A2.COL_NAME;

-- ORACLE
SELECT COL_NAME / EXP
FROM table_name A1, table_name A2
WHERE A1.COL_NAME = A2.COL_NAME;

Questions :
WAQTD DETAILS OF THE EMPLOYEE AND THEIR MANAGER.
WAQTD NAME OF THE EMPLOYEE AND HIS MANAGER.
WAQTD NAME OF THE EMPLOYEE AND HIS SALARY ALONG WITH MANAGER NAME & SALARY.
WAQTD ename , manager's name along with their deptno If employee is working as clerk.
WAQTD ename and manager's name along with their job if emp and manager are working for same designation
WAQTD ename, salary manager's name manager's salary If manager earns more than employee.
WAQTD ename and manager's name along with manager's commission if manager earns commission.
WAQTD NAME OF THE EMPLOYEE AND MANAGER'S DESIGNATION IF MANAGER WORKS IN DEPT 10 OR
20
WAQTD NAME OF THE EMP AND MANAGERS SALARY IF EMPLOYEE AND MANAGER BOTH EARN MORE
THAN 2300
WAQTD EMP NAME AND MANAGER'S HIREDATE IF EMPLOYEE WAS HIRED BEFORE 1982.
WAQTD EMP NAME AND MANAGER'S COMM IF EMPLOYEE WORKS AS SALESMAN AND MANAGER
WORKS IN DEPT 30
WAQTD EMP NAME AND MANAGER NAME AND THEIR SALARIES IF EMPLOYEE EARNS MORE THAN
MANAGER
WAQTD EMP NAME AND HIREDATE , MANAGER NAME AND HIREDATE IF MANAGER WAS HIRED BEFORE
EMPLOYEE
WAQTD EMP NAME AND MANAGER NAME IF BOTH ARE WORKING IN SAME JOB.
WAQTD EMP NAME AND MANAGER NAME IF MANAGER IS WORKING AS ACTUAL MANAGER.

Generated with PDFtion

 WAQTD EMP NAME AND MANAGER NAME ALONG WITH THEIR ANNUAL SALARIES IF EMPLOYEE WORKS
IN DEPT 10 , 20 AND MANAGER'S SAL IS GREATER THAN EMPLOYEES SALARY.
WAQTD EMPLOYEE'S NAME AND MANAGER'S DESIGNATION FOR ALL THE EMPLOYEE
WAQTD EMPLOYEE'S NAME AND MANAGER'S SALARY FOR ALL THE EMPLOYEES IF MANAGER'S SALARY
ENDS WITH 50.
WAQTD THE NAME OF EMPLOYEE, HIS MANAGER’S NAME AND HIS MANAGER’S MANAGER NAME.

Nested Joins
→ USED TO JOIN MORE THAN 2 TABLES.

SELECT COL_NAME / EXP

FROM TABLE_1
JOIN_NAME TABLE_2 ON <JOIN_CONDITION>
JOIN_NAME TABLE_3 ON <JOIN_CONDNTION>
JOIN_NAME TABLE_4 ON <JOIN_CONDITION>
....
;

-- ORACLE
SELECT COL_NAME/ EXP
FROM TABLE_1, TABLE_2, TABLE_3,....
WHERE <JOIN_CONDTION> .....(N-1);

—> remind me to add this questions.

INNER JOIN
NAME OF THE EMPLOYEE AND HIS LOCATION OF ALL THE EMPLOYEES .
WAQTD DNAME AND SALARY FOR ALL THE EMPLOYEE WORKING IN ACCOUNTING.
WAQTD DNAME AND ANNUAL SALARY FOR ALL EMPLOYEES WHOS SALARY IS MORE THAN 2340
WAQTD ENAME AND DNAME FOR EMPLOYEES HAVING CAHARACTER 'A' IN THEIR DNAME
WAQTD ENAME AND DNAME FOR ALL THE EMPLOYEES WORKING AS SALESMAN
WADTD DNAME AND JOB FOR ALL THE EMPLOYEES WHOS JOB AND DNAME STARTS WITH CHARACTER
'S'
WAQTD DNAME AND MGR NO FOR EMPLOYEES REPORTING TO 7839
WAQTD DNAME AND HIREDATE FOR EMPLOYEES HIRED AFTER 83 INTO ACCOUNTING OR RESEARCH
DEPT
WAQTD ENAME AND DNAME OF THE EMPLOYEES WHO ARE GETTING COMMIN DEPT 10 OR 30
WAQTD DNAME AND EMPNO FOR ALL THE EMPLOYEES WHO'S EMPNO ARE (7839,7902) AND ARE
WORKING IN LOC NEW YORK.

SELF JOIN
WAQTD NAME OF THE EMPLOYEE AND HIS MANAGER'S NAME IF EMPLOYEE IS WORKING AS CLERK
WAQTD NAME OF THE EMPLOYEE AND MANAGER'S DESIGNATION IF MANAGER WORKS IN DEPT 10 OR
20
WAQTD NAME OF THE EMP AND MANAGERS SALARY IF EMPLOYEE AND MANAGER BOTH EARN MORE
THAN 2300
WAQTD EMP NAME AND MANAGER'S HIREDATE IF EMPLOYEE WAS HIRED BEFORE 1982

Generated with PDFtion

 WAQTD EMP NAME AND MANAGER'S COMM IF EMPLOYEE WORKS AS SALESMAN AND MANAGER
WORKS IN DEPT 30
WAQTD EMP NAME AND MANAGER NAME AND THEIR SALARIES IF EMPLOYEE EARNS MORE THAN
MANAGER
WAQTD EMP NAME AND HIREDATE , MANAGER NAME AND HIREDATE IF MANAGER WAS HIRED BEFORE
EMPLOYEE
WAQTD EMP NAME AND MANAGER NAME IF BOTH ARE WORKING IN SAME JOB
WAQTD EMP NAME AND MANAGER NAME IF MANAGER IS WORKING AS ACTUAL MANAGER
WAQTD EMP NAME AND MANAGER NAME ALONG WITH THEIR ANNUAL SALARIES IF EMPLOYEE WORKS
IN DEPT 10 , 20 AND MANAGER'S SAL IS GREATER THAN EMPLOYEES SALARY .
WAQTD EMPLOYEE'S NAME AND MANAGER'S DESOGNATION FOR ALL THE EMPLOYEES
WAQTD EMPLOYEE'S NAME AND MANAGER'S SALARY FOR ALL THE EMPLOYEES IF MANAGER'S SALARY
ENDS WITH 50

Generated with PDFtion

Co-related Sub-Query
WAQTD the employees who earn more than the average salary of their department.
WAQTD employees whose salary is greater than their manager's salary.
WAQTD the employees whose commission is higher than the commission of any other employee in their
department.
WAQTD the employees who joined the earliest in their department.
WAQTD the employee names who do not have the minimum salary in their department.
WAQTD the employees whose salary is greater than the average salary of all employees with the same job title.
Find employees whose salary is not the maximum in their department but is more than the average in his
department.

Generated with PDFtion