SQL Week-3

sunny1637@gmail.
com
BX9T5ZHNQF
SQL( Week 3)
This file is meant for personal use by sunny1637@gmail.com only.
Sharing or publishing the contents in part or full is liable for legal action.
Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited.
DSBA CURRICULUM DESIGN
CORE DOMAIN
FOUNDATIONS COURSES APPLICATIONS
sunny1637@gmail.com
Python for
BX9T5ZHNQF Data
Advanced Statistics Financial Risk
Science
Analytics
Data Mining
Statistical Methods
for Decision Making Marketing Retail
Predictive Modelling Analytics
Machine Learning
Data Visualization
SQL (Week-3/3)
Time Series
Forecasting
Table of Contents
• SQl Joins
•
sunny1637@gmail.com
BX9T5ZHNQF
Sub Query
This file is meant for personal use by sunny1637@gmail.com only. 3

sunny1637@gmail.com
BX9T5ZHNQF
SQL Joins

SQL JOINS
Combine rows from two or more tables, based on a A∩B
related column between them.
• INNER JOIN − Returns rows when there is a match Table Table

A B
in both tables.
(A ∩ B) U A
sunny1637@gmail.com
BX9T5ZHNQF
Table Table
• LEFT JOIN − Returns all rows from the left A B
table, even if there are no matches in the
right table. (A ∩ B) U B
Table Table
A B
• RIGHT JOIN − Returns all rows from the right

table, even if there are no matches in the left
table. This file is meant for personal use by sunny1637@gmail.com only.
5
SQL JOINS (A ∩ B) U (A-B)U(B-A)
• FULL OUTER JOIN − Returns rows when there Table Table

is a match in one of the tables. A B
• SELF JOIN − Used to join a table to itself

sunny1637@gmail.com Table
BX9T5ZHNQF
as if the table were two tables, A
temporarily renaming at least one table in (Join Itself)
the SQL statement.
Table
• CARTESIAN JOIN (CROSS JOIN) − Returns the Table
B
A
Cartesian product of the sets of records
from the two or more joined tables.
SQL INNER JOIN
The INNER JOIN creates a new result table by combining column
values of two tables (table1 and table2) based upon the join-
predicate. The query compares each row of table1 with each row
of table2
sunny1637@gmail.com
to find all pairs of rows which satisfy the join-
BX9T5ZHNQF
predicate.
SYNTAX :
SELECT table1.col1, table2.col2,…, table1.coln

FROM table1
INNER JOIN table2
ON table1.commonfield = table2.commonfield;

SQL INNER JOIN
• Display details of employee and department
• department_id is a common column between employees & departments

tables
sunny1637@gmail.com
BX9T5ZHNQF
• 'e' and 'd' are alias for the table names
This file is meant for personal

DBMS use by sunny1637@gmail.com only. 8
SQL INNER JOIN
employee first_nam last_nam salary department department department manager_id location_id
_id e e _id _id _name
103 Harry Potter 20000 12 10 IT 200 1700
102 Edwin Thomas 15000 11 11 Marketing 201 1800
101 Steven Cohen 10000 10 13 Resources 203 2400

100
sunny1637@gmail.com
BX9T5ZHNQF
Erik John 10000 12 14 Shipping 121 1500
SELECT e.employee_id,
e.first_name, e.last_name,
d.department_id,
d.department_name employee_i first_name last_name departmen departmen
FROM employees e d t_id t_name
INNER JOIN departments d 101 Steven Cohen 10 IT
ON 102 Edwin Thomas 11 Marketing
e.department_id=d.department_id
;
SQL LEFT JOIN
The LEFT JOIN returns all the values from the left table, plus
matched values from the right table or NULL in case of no
matching join predicate.
SYNTAX :
sunny1637@gmail.com
BX9T5ZHNQF

FROM table1
LEFT JOIN table2

SQL LEFT JOIN
• List all employees those with and without departments
• Department names will be NULL for those employees in which

there are no departments
sunny1637@gmail.com
BX9T5ZHNQF

SQL LEFT JOIN
employee_ first_nam last_nam salary Departmen department department manager_id location_id
id e e t_id _id _name
103 Harry Potter 20000 12 10 IT 200 1700
100
sunny1637@gmail.com
BX9T5ZHNQF
SELECT e.employee_id,
e.first_name, e.last_name, employee first_name last_name departme departme
d.department_id, d.department_name _id nt_id nt_name
FROM employees e 101 Steven Cohen 10 IT
LEFT OUTER JOIN departments d
ON e.department_id = 102 Edwin Thomas 11 Marketing
d.department_id; 103 Harry Potter Null Null
100 Erik John Null Null
SQL RIGHT JOIN
• The RIGHT JOIN returns all the values from the right
table, plus matched values from the left table or NULL
in case of no matching join predicate.
sunny1637@gmail.com
SYNTAX :
BX9T5ZHNQF

FROM table1
RIGHT JOIN table2

SQL RIGHT JOIN
• List all departments those with and without managers
• For those departments where there are no managers, their names

will be shown as NULL
sunny1637@gmail.com
BX9T5ZHNQF

SQL RIGHT JOIN
103 Harry Potter 20000 12 10 IT 200 1700
100
sunny1637@gmail.com
BX9T5ZHNQF
SELECT e.employee_id, e.first_name, employee first_name last_name departme departme

e.last_name, d.department_id, _id nt_id nt_name
d.department_name 101 Steven Cohen 10 IT
FROM employees e
RIGHT JOIN departments d 102 Edwin Thomas 11 Marketing
ON e.department_id=d.department_id; Null Null Null 13 Resources
Null Null Null 14 Shipping
SQL FULL OUTER JOIN
The FULL OUTER JOIN combines the results of both left and right
outer joins. The joined table will contain all records from both
the tables and fill in NULLs for missing matches on either side.
SYNTAX :
sunny1637@gmail.com
BX9T5ZHNQF

FROM table1
Left JOIN table2
Union
FROM table1
Right JOIN table2
SQL FULL OUTER JOIN
103 Harry Potter 20000 12 10 IT 200 1700
100
sunny1637@gmail.com
BX9T5ZHNQF
SELECT e.employee_id, e.first_name, e.last_name,
d.department_id, d.department_name employe first_na last_na depart departme
FROM employees e e_id me me ment_i nt_name
LEFT JOIN departments d d
ON e.department_id=d.department_id
101 Steven Cohen 10 IT
UNION
SELECT e.employee_id, e.first_name, e.last_name, 102 Edwin Thomas 11 Marketing
d.department_id, d.department_name Harry Potter
103 Null Null
FROM employees e
RIGHT JOIN departments d 100 Erik John Null Null
ON e.department_id=d.department_id;
Null Null Null 13 Resources
Null
Sharing or publishing the contents in part or full is liable for legal action. Null
Proprietary content. ©Great Learning. All Rights Reserved. Unautho rized use or distribution prohibited.
Null 14 Shipping
SQL SELF JOIN
• The SELF JOIN joins a table to itself; temporarily renaming
at least one table in the SQL statement.
SYNTAX:
sunny1637@gmail.com
BX9T5ZHNQF
SELECT a.col1, b.col2,..., a.coln

FROM table1 a, table1 b
WHERE a.commonfield = b.commonfield;

SQL SELF JOIN
employee_id first_name manager_id
1 Steven
2 Neena 1
3 Lex 2
4 Alex 3
sunny1637@gmail.com
BX9T5ZHNQF 5 Bruce 3
SELECT emp.employee_id, employee_id first_name manager_id first_name

emp.first_name, emp.manager_id, 2 Neena 1 Steven
mgr.first_name FROM
3 Lex 2 Neena
employeess emp inner join
employeess mgr on 4 Alex 3 Lex
emp.manager_id = mgr.employee_id; 5 Bruce 3 Lex
SQL CROSS JOIN
• The CROSS JOIN produces a result set with the number of rows in
the first table multiplied by the number of rows in the second.
SYNTAX:
sunny1637@gmail.com
BX9T5ZHNQF

FROM table1
CROSS JOIN table2;

SQL CROSS JOIN
• Assume there are 4 records in table1 and 3
A 1
records in table2
A 2
SELECT * FROM table1 A 3
CROSS JOIN table2; B 1
sunny1637@gmail.com B 2
BX9T5ZHNQF
Table-1 Table-2 B 3
alpha Num C 1
A 1 C 2
B 2 C 3
C 3 D 1
D D 2
D 3

sunny1637@gmail.com
BX9T5ZHNQF
Sub Query

Sub Query
❑ Single Row Subquery: It either returns zero or a single row
❑ Multiple Row Subquery: It returns one or multiple rows of a

table
sunny1637@gmail.com
BX9T5ZHNQF
❑ Multiple Column Subquery: It returns one or multiple columns
❑ Correlated Subqueries: It refers to one or more columns in the

outer SQL query. (Only Brief overview to be covered)

1. MySQL subquery on WHERE Clause
❑ General Format
SELECT Field 1, Field2 FROM table 1 WHERE field 3 = (SELECT

MAX(field 3) FROM table 1);
sunny1637@gmail.com
BX9T5ZHNQFImportant points to note
✓ Any number of fields could be specified in the first select
statement
✓ Field 3 specified in the “where” clause has to be same as

the output generated from second “select” statement
✓ Second “Select” statement of a sub-query statement can use

either the same table specified in the first query (similar
to self join) or it could have a different table
2. MySQL Subquery with operators IN and NOT
IN
❑ General Format
SELECT Field 1, Field2 FROM table 1 WHERE field 3 NOT IN (SELECT

DISTINCT(field 3) FROM table 2);
sunny1637@gmail.com
Important points to note
BX9T5ZHNQF
✓ Any number of fields could be specified in the first select statement
✓ Field 3 specified in the “where” clause has to be same as the output

generated from second “select” statement
✓ Second “select” statement of a sub-query statement should refer to a

table which is different from the table specified in the first
“select” statement.
3. MySQL subquery with FROM clause
❑ When we use a subquery with FROM clause, the result set

returned is considered as a temporary table rows which is
then known as a derived or materialized subquery.
SELECT I.Field 1, I.Field 2 FROM (select Ave(Field 1) as

sunny1637@gmail.com
BX9T5ZHNQF
averagefield1 from Table 2) as temptable, Table 1 as I WHERE
I.Field 1 > temptable.averagefield1;
Let’s understand the query:
✓ Alias is used here – Table 1 alias is I
✓ Subquery output is stored in the temp table and the output

which is average in the above query is used in the where
clause This file is meant for personal use by sunny1637@gmail.com only. 26
Apply Data Science at your workplace to gain some instant benefits:
• Get noticed by your management with your outstanding analysis backed by data science.
sunny1637@gmail.com
BX9T5ZHNQF
• Create an impact in your organization by taking up small projects/initiatives to solve critical

issues using data science.
• Network with members from the data science vertical of your organization and seek
opportunities to contribute in small projects.
• Share your success stories with us and the world to position yourself as a subject matter expert
in data science.
sunny1637@gmail.com
BX9T5ZHNQF
ANY QUESTIONS
sunny1637@gmail.com
BX9T5ZHNQF
HAPPY LEARNING

SQL Week-3

Uploaded by

Copyright:

Available Formats

SQL Week-3

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

SQL Week-3

Uploaded by

Copyright:

Available Formats

sunny1637@gmail.

This file is meant for personal use by sunny1637@gmail.com only. 3

This file is meant for personal use by sunny1637@gmail.com only.

• INNER JOIN − Returns rows when there is a match Table Table

• RIGHT JOIN − Returns all rows from the right

• FULL OUTER JOIN − Returns rows when there Table Table

• SELF JOIN − Used to join a table to itself

the SQL statement.

SELECT table1.col1, table2.col2,…, table1.coln

This file is meant for personal use by sunny1637@gmail.com only. 7

• department_id is a common column between employees & departments

• 'e' and 'd' are alias for the table names

This file is meant for personal

101 Steven Cohen 10000 10 13 Resources 203 2400

SELECT table1.col1, table2.col2,…, table1.coln

This file is meant for personal use by sunny1637@gmail.com only. 10

• List all employees those with and without departments

• Department names will be NULL for those employees in which

This file is meant for personal use by sunny1637@gmail.com only. 11

SELECT table1.col1, table2.col2,…, table1.coln

This file is meant for personal use by sunny1637@gmail.com only. 13

• For those departments where there are no managers, their names

This file is meant for personal use by sunny1637@gmail.com only. 14

SELECT e.employee_id, e.first_name, employee first_name last_name departme departme

SELECT table1.col1, table2.col2,…, table1.coln

SELECT a.col1, b.col2,..., a.coln

This file is meant for personal use by sunny1637@gmail.com only. 18

SELECT emp.employee_id, employee_id first_name manager_id first_name

SELECT table1.col1, table2.col2,…, table1.coln

This file is meant for personal use by sunny1637@gmail.com only. 20

This file is meant for personal use by sunny1637@gmail.com only. 21

This file is meant for personal use by sunny1637@gmail.com only.

❑ Single Row Subquery: It either returns zero or a single row

❑ Multiple Row Subquery: It returns one or multiple rows of a

❑ Multiple Column Subquery: It returns one or multiple columns

❑ Correlated Subqueries: It refers to one or more columns in the

This file is meant for personal use by sunny1637@gmail.com only. 23

SELECT Field 1, Field2 FROM table 1 WHERE field 3 = (SELECT

✓ Field 3 specified in the “where” clause has to be same as

✓ Second “Select” statement of a sub-query statement can use

SELECT Field 1, Field2 FROM table 1 WHERE field 3 NOT IN (SELECT

✓ Any number of fields could be specified in the first select statement

✓ Field 3 specified in the “where” clause has to be same as the output

✓ Second “select” statement of a sub-query statement should refer to a

❑ When we use a subquery with FROM clause, the result set

SELECT I.Field 1, I.Field 2 FROM (select Ave(Field 1) as

Let’s understand the query:

✓ Alias is used here – Table 1 alias is I

✓ Subquery output is stored in the temp table and the output

• Create an impact in your organization by taking up small projects/initiatives to solve critical

You might also like