WEEK-5B
JOINS
1-INNER JOIN
2-LEFT OUTER JOIN
3-RIGHT OUTER JOIN
4-FULL OUTERJOIN
DISTINCT
SET OPERATORS
1-UNION
2-INTERSECT
3-MINUS
DISTINCT : IT IS USED TO LIST UNIQUE VALUES.
SET OPERATORS
1-UNION 2-INTERSECT 3-MINUS
A INTERSECT B
A UNION B
A MINUS B
B MINUS A
UNION and UNION ALL
UNION
The UNION command is used to select related information from two tables, much
like the JOIN command. However, when using the UNION command all selected
columns need to be of the same data type.
Note: With UNION, only distinct values are selected. (NO DUPLICATED VALUE)
Economy Engineering
ID NAME ID NAME
01 AYNUR 01 SERIK
02 GULNUR 02 BERIK
03 NURGUL 03 YERIK
04 SERIK 04 NURIK
Economy Engineering
ID NAME ID NAME
01 AYNUR NAME 01 SERIK
02 GULNUR AYNUR 02 BERIK
03 NURGUL GULNUR 03 YERIK
04 SERIK NURGUL 04 NURIK
SERIK
BERIK
Economy Engineering YERIK
NURIK
AYNUR
NURIK 7 rows selected
YERIK
GULNUR SERIK
BERIK
NURGUL
SELECT NAME FROM ECONOMY
UNION
SELECT NAME FROM ENGINEERING
UNION ALL
The UNION ALL command is equal to the UNION command, except that UNION
ALL selects all values.
Economy Engineering
ID NAME ID NAME
01 AYNUR 01 SERIK
02 GULNUR 02 BERIK
03 NURGUL 03 YERIK
04 SERIK 04 NURIK
NAME
AYNUR
GULNUR
SELECT NAME FROM ECONOMY
NURGUL
UNION ALL SERIK
BERIK
SELECT NAME FROM ENGINEERING
YERIK
NURIK
8 rows selected
INTERSECT
The INTERSECT command is used to select the same values from two tables.
Economy Engineering
ID NAME ID NAME
01 AYNUR 01 SERIK
02 GULNUR 02 BERIK
03 NURGUL 03 YERIK
04 SERIK 04 NURIK
Economy Engineering
SELECT NAME FROM ECONOMY
AYNUR
NURIK INTERSECT
YERIK SELECT NAME FROM ENGINEERING
GULNUR SERIK
BERIK NAME
NURGUL SERIK
1 rows selected
MINUS
The MINUS command is used select all distinct rows selected by the first table but
not by the second table.
Economy Engineering
ID NAME ID NAME
01 AYNUR 01 SERIK
02 GULNUR 02 BERIK
03 NURGUL 03 YERIK
04 SERIK 04 NURIK
Economy Engineering SELECT NAME FROM ECONOMY
AYNUR MINUS
NURIK
SELECT NAME FROM ENGINEERING
YERIK
GULNUR SERIK NAME
BERIK AYNUR
NURGUL GULNUR
NURGUL
3 rows selected
NAME
SELECT NAME FROM ECONOMY AYNUR
MINUS GULNUR
SELECT NAME FROM ENGINEERING NURGUL
3 rows selected
NAME
SELECT NAME FROM ENGINEERING
NURIK
MINUS
YERIK
SELECT NAME FROM ECONOMY
BERIK
3 rows selected
GROUP BY
TABLE : CELLS
Company Month Members
KCELL JANUARY 5
KCELL FEBRUARY 3
DALACOM JANUARY 4
DALACOM FEBRUARY 5
BEELINE JANUARY 3
BEELINE FEBRUARY 2
SELECT COMPANY,SUM(MEMBERS) SELECT MONTH,SUM(MEMBERS)
FROM CELLS FROM CELLS
GROUP BY COMPANY GROUP BY MONTH
Company Members
KCELL 8 Month Members
DALACOM 9 JANUARY 12
FEBRUARY 10
BEELINE 5
HAVING
TABLE : CELLS
Company Month Members
KCELL JANUARY 5
KCELL FEBRUARY 3
DALACOM JANUARY 4
DALACOM FEBRUARY 5
BEELINE JANUARY 3
BEELINE FEBRUARY 2
SELECT COMPANY,SUM(MEMBERS) SELECT COMPANY,SUM(MEMBERS)
FROM CELLS FROM CELLS
GROUP BY COMPANY GROUP BY COMPANY
HAVING SUM(MEMBERS) > 6
Company Members Company Members
KCELL 8 KCELL 8
DALACOM 9 DALACOM 9
BEELINE 5
HAVING
TABLE : CELLS
Company Month Members
KCELL JANUARY 5
KCELL FEBRUARY 3
DALACOM JANUARY 4
DALACOM FEBRUARY 5
BEELINE JANUARY 3
BEELINE FEBRUARY 2
SELECT MONTH,SUM(MEMBERS) SELECT MONTH,SUM(MEMBERS)
FROM CELLS FROM CELLS
GROUP BY MONTH GROUP BY MONTH
HAVING SUM(MEMBERS) < 11
Month Members Month Members
JANUARY 12 FEBRUARY 10
FEBRUARY 10
1-Write an SQL statement which returns JOB and TOTAL SALARY of jobs.
2-Write an SQL statement which returns JOB and AVERAGE SALARY of jobs.
3-Write an SQL statement which returns JOB and TOTAL SALARY of jobs is greater
than 5000.
4-Write an SQL statement which returns JOB and AVERAGE SALARY of jobs is
greater than 2000.
5-Write an SQL statement which returns the average salary of all DEPTNO.
6-Display the highest,lowest,sum, and average salary of all [Link] the columns
Maximum,Minimum,Sum,and Average, respectively. Round your results to the decimal position.
7-Display the maximum, minimum,sum, and average salary for each job type.
8-Write a query to display the number of people with the same job.
9-Determine the number of managers without listing them. Label the column “Number of Managers”
10-Write an SQL statement that will display the difference between the highest and lowest salaries.
Label the column “Diffrenece”
11-Display the manager number and salary of the lowest paid employee for the manager.
Exclude anyone where the manager id is knot known.
Exclude any groups where the minimum salary is less than $1000. Sort the output in
Descending order of salary
12-Write a query to display the department name, location name, number of employees, and the
Average of salary for all employees in that department.
Label the columns dname,loc,Number of people, and Salary , respectively.
13-List DEPTNO exists in DEPT but not in EMP.
14-List All Deptno from emp and dept.
1)SELECT JOB,SUM(SAL) FROM EMP GROUP BY JOB;
2)SELECT JOB,AVG(SAL) FROM EMP GROUP BY JOB;
3)SELECT JOB,SUM(SAL) FROM EMP GROUP BY JOB HAVING SUM(SAL)>5000;
4)SELECT JOB,AVG(SAL) FROM EMP GROUP BY JOB HAVING AVG(SAL)>2000;
5)SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO;
6)SELECT MAX(SAL) as MAXIMUM,MIN(SAL) as MINIMUM,SUM(SAL) as SUM,ROUND(AVG(SAL),1) as AVERAGE FROM
EMP;
7)SELECT JOB,MAX(SAL) as MAXIMUM,MIN(SAL) as MINIMUM,SUM(SAL) as SUM,AVG(SAL) as AVERAGE FROM EMP
GROUP BY JOB;
8)SELECT JOB,COUNT(*) from EMP GROUP BY JOB;
9)SELECT COUNT(*) as NUMBERS FROM EMP GROUP BY JOB HAVING JOB='MANAGER';
10)SELECT MAX(SAL)-MIN(SAL) as DIFFERENT from EMP;
11)SELECT MGR,MIN(SAL) FROM EMP group by MGR having MGR IS NOT NULL AND MIN(SAL)>1000 ORDER BY MIN(SAL)
DESC;
12)SELECT DNAME,LOC,COUNT(*),AVG(SAL) from (SELECT [Link],LOC,[Link] FROM EMP e JOIN dept d ON
[Link]=[Link]) GROUP BY DNAME,LOC ;
13)SELECT DEPTNO from dept MINUS SELECT DEPTNO from emp;
14)SELECT DEPTNO from EMP UNION SELECT DEPTNO from dept;
