Basic Select Statments.Sql
Arithmetic Operators.Sql
Column Aliases.Sql
String Concat.Sql
Distinct.Sql
Basic Where.Sql
In Operator.Sql
Between Operator.Sql
Like Operator.Sql
Is Null.Sql
And Or.Sql
Order By.Sql
Top.Sql
Scalar Functions.Sql
String Functions.Sql
Numeric Functions.Sql
Date Functions.Sql
Conversion Functions.Sql
Null Related Functions.Sql
Conditional Logic.Sql
Group Functions.Sql
Group By.Sql
Having.Sql
Basic Join.Sql
Join More Than 2 Tables.Sql
Outer Join.Sql
Self Join.Sql
Sub Queries.Sql
Sub Queries 2.Sql
Basic Select Statments
——————————-
— SQL Server SELECT Statement
——————————-
— 1. About ACDB Database
— 2. Basic Select Statement
SELECT *
FROM customers
— * The SQL Server SELECT clause:
— Lets you choose what you want to display.
— * The asterisk sign (*) indicates that you want to select all fields contained in this table.
— * The SQL Server FROM clause:
— Lets you specify from which table you want to retrieve all of these fields.
— * A table’s name always appears after the FROM keyword.
— 3. Selecting Specific Columns
SELECT customer_id, last_name, city, city
FROM customers
— * The SQL Server SELECT clause:
— Lets you choose what columns you want to display (“Projection”).
— * After the SQL Server SELECT keyword, specify the names of the columns that you would like to
— retrieve, separated by comma (,).
— * You can specify as many columns as you want; you can even specify the same column more than once.
— * The columns appear in the order selected.
— 4. General Guidelines
— * In each SQL statement, SQL Server SELECT and FROM clauses are mandatory.
— * The order of the SQL Server SELECT and FROM clauses cannot be changed.
— * It is possible to specify a column’s name multiple times
— * To enhance readability – even though the SQL Server SQL syntax is neither case-sensitive,
— nor sensitive to spaces or line breaks, ensure writing in an orderly manner:
— write the keywords in capital letters, names of columns/tables in small letters,
— insert a line break after each command and indents when required.
SELEct CUSTomer_ID , CITY
fROM cUSTOMERS
Arithmetic Operators
———————————-
— SQL Server Arithmetic Operators
———————————-
SELECT *
FROM customers
— 1. Arithmetic Operations on Numeric Values
SELECT customer_id, last_name, monthly_discount, monthly_discount * 1.2
FROM customers
— * It is possible to perform any mathematical calculation
— * In order to define operator precedence, simply use round brackets
SELECT customer_id, last_name, monthly_discount, (monthly_discount + 5) * 1.2
FROM customers
— * It is advisable to specify the original column without the change
— in addition to the column with the calculation
SELECT customer_id, last_name, (monthly_discount + 5) * 1.2
FROM customers
— 2. Arithmetic Operations on String Values
SELECT customer_id, last_name * 5, (monthly_discount + 5) * 1.2
FROM customers
— 3. Arithmetic Operations on Date Values
— * The plus (+) and minus (-) operators can be used to perform arithmetic operations on Date values
— * It is not possible to carry out any calculation of (multiplication) or (division) on Date values
SELECT customer_id, last_name, (monthly_discount + 5) * 1.2, join_date, join_date / 5
FROM customers
— 4. Arithmetic Operations on NULL Values
— * A NULL value is a value that indicates an empty field in a table.
— This value does not equal zero (0), nor does it equal space (‘ ‘)
SELECT customer_id, last_name, monthly_discount, (monthly_discount + 5) * 1.2, join_date, join_date + 5
FROM customers
— General Guidelines
— * calculations on columns (of any data type) do not modify the contents (actual data)
— of the column in the table
— * Calculations on columns change the column’s header’s in the output
Column Aliases
———————————-
— Column Aliases
———————————-
— 1. The Need for Column Aliases
SELECT last_name, join_date + 5
FROM customers
— 2. Basic Syntax
SELECT last_name AS 'ln', join_date + 5 AS 'new_join_date'
FROM customers
— 3. Omitting the AS Keyword
SELECT last_name 'ln', join_date + 5 'new_join_date'
FROM customers
— 4. Omitting the Enclosing Single Quotes (‘ ‘)
— * When a SQL Server column alias is written without enclosing single quotes
— (‘ ‘), it is not possible to write the alias as two (or more) separate words
SELECT last_name 'l n', join_date + 5 new_join_date
FROM customers
— General Guidelines
— * In order to make your SQL Server query more readable,
— always use the AS keyword, always use single quotes (‘ ‘),
— and when your alias consists of two words or more, always ensure that
— you insert an underline between them
SELECT last_name AS 'ln', join_date + 5 AS 'new_join_date'
FROM customers
String Concat
————————
— String Concatenation
————————
— 1. Basic String Concatenation (+)
SELECT last_name + first_name, city
FROM customers
— 2. Concatenate Two Fields with a Space
SELECT last_name + ' and the first name is : ' + first_name, city
FROM customers
— 3. Using a SQL Server Column Alias
SELECT last_name + ' ' + first_name AS 'full_name', city
FROM customers
— 4. Retrieving additional columns
SELECT gender, last_name + ' ' + first_name AS 'full_name', city + ' ' + street AS 'ct_street'
FROM customers
— 5. Concatenate more than two values
SELECT gender,
last_name + ' ' + first_name + ' ' + martial_status AS 'full_name_ms',
city + ' ' + street AS 'ct_street'
FROM customers
Distinct
————————-
— The Distinct Keyword
————————-
SELECT DISTINCT gender FROM customers
— 1. Explaining the purpose
— 2. Using DISTINCT on a single column
SELECT DISTINCT gender FROM customers
SELECT DISTINCT martial_status FROM customers
— 3. Using DISTINCT on Multiple Columns
SELECT DISTINCT state, city
FROM customers
Basic Where
—————————
— SQL WHERE clause
—————————
— 1. Introduction
SELECT customer_id, last_name, first_name, city, monthly_discount
FROM customers
— 2. WHERE Clause Example
SELECT customer_id, last_name, first_name, city, monthly_discount
FROM customers
WHERE monthly_discount < 10
-- 3. WHERE Clause Syntax
-- WHERE column_name operator value
-- * Column Name
-- * Operator (>, <, !=, = ..)
-- * Value
-- * Numeric Value
SELECT customer_id, last_name, first_name, city, monthly_discount
FROM customers
WHERE monthly_discount < 10
-- * String Value
SELECT customer_id, last_name, first_name, city, monthly_discount
FROM customers
WHERE city = 'LOS ANGELES'
-- * Date Value
SELECT customer_id, last_name, first_name, city, monthly_discount, join_date
FROM customers
WHERE join_date >= '2050/01/01'
— General Guidelines
— * The value data type must match the column date type
— * If the requested comparison does not match the table values, no result
— will be retrieved but no error will be generated either
— * A SQL Server WHERE clause must be written after a SQL Server FROM clause,
— which in turn must be written after a SQL Server SELECT clause,
— this order cannot be changed
— * The SQL Server WHERE clause is optional
In Operator
———————–
— IN Operator
———————–
SELECT customer_id, last_name, first_name, city, monthly_discount
FROM customers
— 1. Example
SELECT customer_id, last_name, first_name, city, monthly_discount
FROM customers
WHERE city IN ('New York' , 'Los Angeles' , 'Phoenix')
— 2. Syntax
— WHERE column_name IN (…)
— * IN Operator with Numeric Values
SELECT customer_id, last_name, first_name, city, monthly_discount
FROM customers
WHERE monthly_discount IN (4,11,13,30)
— * IN Operator with Character Values
SELECT customer_id, last_name, first_name, city, monthly_discount
FROM customers
WHERE city IN ('New York' , 'Los Angeles' , 'Phoenix')
— * IN Operator with Date Values
SELECT customer_id, last_name, first_name, city, monthly_discount, join_date
FROM customers
WHERE join_date IN ('2006-08-23' , '2006-03-05' , '2010-07-25')
— * NOT Operator
SELECT customer_id, last_name, first_name, city, monthly_discount
FROM customers
WHERE monthly_discount NOT IN (4,11,13,30)
SELECT customer_id, last_name, first_name, city, monthly_discount
FROM customers
WHERE city NOT IN ('New York' , 'Los Angeles' , 'Phoenix')
SELECT customer_id, last_name, first_name, city, monthly_discount, join_date
FROM customers
WHERE join_date NOT IN ('2006-08-23' , '2006-03-05' , '2010-07-25')
Between Operator
———————
— BETWEEN Operator
———————
— 1. Example
SELECT customer_id, last_name, first_name, city, monthly_discount, join_date
FROM customers
WHERE monthly_discount BETWEEN 10 AND 20
— 2. Syntax
— WHERE column_name BETWEEN lower_bound AND upper_bound
— * BETWEEN Operator with Numeric Values
SELECT customer_id, last_name, first_name, city, monthly_discount, join_date
FROM customers
WHERE monthly_discount BETWEEN 10 AND 20
— * BETWEEN Operator with Character Values
SELECT customer_id, last_name, first_name, city, monthly_discount, join_date
FROM customers
WHERE first_name BETWEEN 'A' and 'D'
— * BETWEEN Operator with Date Values
SELECT customer_id, last_name, first_name, city, monthly_discount, join_date
FROM customers
WHERE join_date BETWEEN '2006-01-01' AND '2006-12-31'
— * NOT Operator
SELECT customer_id, last_name, first_name, city, monthly_discount, join_date
FROM customers
WHERE join_date NOT BETWEEN '2006-01-01' AND '2006-12-31'
— General Guidelines
— * The lower limit must be specified before the upper limit.
SELECT customer_id, last_name, first_name, city, monthly_discount, join_date
FROM customers
WHERE join_date BETWEEN '2006-12-31' AND '2006-01-01'
— * The BETWEEN operator is inclusive
Like Operator
———————
— LIKE Operator
———————
— 1. Intro
— 2. Wildcards Operators
— * % – The percent (%) sign, represents any sequence of characters (0 or more).
— * _ – The underscore (_) sign, represents any single character.
— 3. Examples
SELECT last_name, main_phone_num
FROM customers
WHERE last_name LIKE '%a%'
SELECT last_name, main_phone_num
FROM customers
WHERE last_name LIKE '%e'
SELECT last_name, main_phone_num
FROM customers
WHERE main_phone_num LIKE '561%'
SELECT last_name, main_phone_num
FROM customers
WHERE last_name LIKE '%s_'
SELECT last_name, main_phone_num
FROM customers
WHERE main_phone_num LIKE '__1%'
SELECT last_name, main_phone_num
FROM customers
WHERE main_phone_num LIKE '__1.6%'
— 4. NOT Operator
SELECT last_name, main_phone_num
FROM customers
WHERE main_phone_num NOT LIKE '__1.6%'
Is Null
——————–
— IS NULL Operator
——————–
— Intro
SELECT last_name, pack_id
FROM customers
WHERE pack_id = NULL
SELECT last_name, pack_id
FROM customers
WHERE pack_id <> NULL
— Examples
SELECT last_name, pack_id
FROM customers
WHERE pack_id IS NULL
— NOT Operator
SELECT last_name, pack_id
FROM customers
WHERE pack_id IS NOT NULL
And Or
—————————–
— AND & OR Operators
—————————-
— 1. AND Operator
SELECT last_name, city, monthly_discount
FROM customers
WHERE city = 'New York' AND monthly_discount > 10
— 2. OR Operator
SELECT last_name, city, monthly_discount
FROM customers
WHERE city = 'New York' OR monthly_discount > 10
— 3. Order of precendence
SELECT last_name, city, monthly_discount , martial_status
FROM customers
WHERE city = 'New York' OR city = 'Chicago'
AND martial_status = 'Married'
— 4. Changing the order of precendence
SELECT last_name, city, monthly_discount , martial_status
FROM customers
WHERE (city = 'New York' OR city = 'Chicago')
AND martial_status = 'Married'
— * General Guideline
— Note: the WHERE keyword appears only once. After the AND operator or the
— OR operator, specify the additional condition without specifying the
— WHERE keyword again.
Order By
————————–
— The ORDER BY Clause
————————–
/*
SELECT
FROM
WHERE
ORDER BY
*/
— Sort the results in ascending / descending order
SELECT last_name, pack_id, monthly_discount
FROM customers
WHERE pack_id BETWEEN 5 AND 50 AND monthly_discount > 15
ORDER BY pack_id DESC
— Sort by multiple columns
SELECT last_name, pack_id, monthly_discount
FROM customers
WHERE pack_id BETWEEN 5 AND 50 AND monthly_discount > 15
ORDER BY pack_id DESC, monthly_discount DESC
— Nulls
SELECT last_name, pack_id, monthly_discount
FROM customers
ORDER BY pack_id DESC
Top
——————–
— TOP Statement
——————–
— Limiting the Number of Rows
SELECT TOP 10 *
FROM customers
— TOP-N Analysis
SELECT TOP 5 last_name, monthly_discount
FROM customers
ORDER BY monthly_discount
Scalar Functions
——————————
— SQL Server Scalar Functions
——————————
— * Intro
— * Example
SELECT last_name, UPPER(last_name)
FROM customers
— * Function General Structure
— FUNCTION_NAME()
— * Types of functions
— * String
— * Numeric
— * Date
— * Conversion
— * Null
— * Conditional
— * Scope of this lesson
— * Scalar Functions & DB Platforms
— * Queries without FROM clause
SELECT UPPER('hello')
String Functions
——————————
— SQL Server String Functions
——————————
— 1. RIGHT
SELECT RIGHT('hello', 3)
— 2. LEFT
SELECT LEFT('hello', 3)
— 3. LEN
SELECT LEN('hello')
— 4. REPLACE
SELECT REPLACE('hello world' , 'o' , '*')
— 5. REVERSE
SELECT REVERSE('hello world')
— 6. SUBSTRING
SELECT SUBSTRING('hello world' , 3 , 3)
— 7. LOWER
SELECT LOWER('HELLO')
— 8. UPPER
SELECT UPPER('hello')
— 9. Customers Demo
— RIGHT, LEFT, LEN, REPLACE, REVERSE, SUBSTRING, LOWER, UPPER
SELECT last_name,
RIGHT(last_name, 2) AS 'rt_last_name' ,
LOWER(LEFT(last_name, 2)) AS 'lt_last_name' ,
RIGHT(last_name, 2) + LOWER(LEFT(last_name, 2)) + '@gmail.com' AS ’email',
LEN(last_name) AS 'ln_last_name',
REPLACE(main_phone_num, '.' , '-') AS 'new_main_p_number',
SUBSTRING(last_name, 2, 4) AS 'sbstr_last_name'
FROM customers
WHERE LEN(last_name) = 7
Numeric Functions
——————————–
— SQL Server Numeric Functions
——————————–
— 1. FLOOR
— 2. CEILING
— 3. ROUND
— 4. Customers Demo
Date Functions
——————————
— SQL Server Date Functions
——————————
— 1. GETDATE()
SELECT GETDATE()
— 2. DATEADD
SELECT GETDATE(),
DATEADD(DAY, 10, GETDATE()),
DATEADD(MONTH, 10, GETDATE()),
DATEADD(YEAR, 10, GETDATE())
— 3. DATEDIFF
SELECT DATEDIFF(DAY, '2010-01-23', GETDATE())
— 4. DAY
SELECT DAY(GETDATE())
— 5. MONTH
SELECT MONTH(GETDATE())
— 6. YEAR
SELECT YEAR(GETDATE())
— 7. Customers Demo
— GETDATE(), DATEADD, DATEDIFF, DAY, MONTH, YEAR
SELECT last_name,
join_date,
DATEADD(YEAR,5,join_date) AS 'jd_plus_5_years' ,
DATEDIFF(MONTH,join_date,GETDATE()) 'date_diff_jd_cd',
DAY(join_date) AS 'day_jd',
MONTH(join_date) AS 'month_jd',
YEAR(join_date) AS 'year_jd'
FROM customers
WHERE YEAR(join_date) = 2010
Conversion Functions
———————————–
— SQL Server Conversion Functions
———————————–
— 1. Intro
SELECT 'The current date is : ' + GETDATE()
SELECT 'The number is : ' + 10
— 2. CAST
SELECT 'The current date is : ' + CAST(GETDATE() AS VARCHAR)
SELECT 'The number is : ' + CAST(10 AS VARCHAR)
— 3. CONVERT
SELECT 'The current date is : ' + CONVERT(VARCHAR, GETDATE(), 111)
SELECT 'The number is : ' + CONVERT(VARCHAR, 10)
— 4. Customers Demo
SELECT last_name + ' ' + CAST(join_date AS VARCHAR),
first_name + ' ' + CONVERT(VARCHAR, monthly_discount)
FROM customers
Null Related Functions
——————————
— SQL Server NULL Functions
——————————
— ISNULL (Customers Demo)
SELECT last_name, pack_id, ISNULL(pack_id , 0), ISNULL(CAST(pack_id AS VARCHAR), 'No Package')
FROM customers
Conditional Logic
——————————
— Conditional Functions
——————————
— CASE (Customers Demo)
SELECT last_name, monthly_discount,
CASE WHEN monthly_discount BETWEEN 0 AND 20 THEN 'Grade A'
WHEN monthly_discount BETWEEN 21 AND 40 THEN 'Grade B'
WHEN monthly_discount BETWEEN 41 AND 60 THEN 'Grade C'
ELSE 'Grade D'
END AS 'Grades'
FROM customers
Group Functions
——————————
— Group Functions
——————————
— 1. Group Functions vs Scalar Functions
SELECT UPPER(last_name)
FROM customers
SELECT AVG(monthly_discount)
FROM customers
— 2. Common Group Functions
— * MIN
SELECT MIN(monthly_discount) FROM customers
SELECT MIN(join_date) FROM customers
SELECT MIN(last_name) FROM customers
— * MAX
SELECT MAX(monthly_discount) FROM customers
SELECT MAX(join_date) FROM customers
SELECT MAX(last_name) FROM customers
— * AVG
SELECT AVG(monthly_discount) FROM customers
SELECT AVG(join_date) FROM customers
SELECT AVG(last_name) FROM customers
— * SUM
SELECT SUM(monthly_discount) FROM customers
SELECT SUM(join_date) FROM customers
SELECT SUM(last_name) FROM customers
— * COUNT (*)
SELECT COUNT(*) FROM customers
— * COUNT (column)
SELECT COUNT(Customer_Id) FROM customers
SELECT COUNT(pack_id) FROM customers
— * COUNT (DISTINCT column)
SELECT last_name, gender, martial_status
FROM customers
SELECT COUNT(DISTINCT gender) FROM customers
SELECT COUNT(DISTINCT martial_status) FROM customers
— 3. GROUP Functions and NULL
— * All Group functions ignore NULL values.
— * In SQL Server You can use the ISNULL function to force group functions to include NULL values
SELECT AVG(pack_id)
FROM customers
SELECT AVG(ISNULL(pack_id, 0))
FROM customers
Group By
————————–
— GROUP BY Clause
————————–
SELECT * FROM customers
— Using the GROUP BY Clause
SELECT AVG(monthly_discount) FROM customers
SELECT gender, AVG(monthly_discount)
FROM customers
GROUP BY gender
SELECT martial_status, AVG(monthly_discount)
FROM customers
GROUP BY martial_status
SELECT city, COUNT(*)
FROM customers
GROUP BY city
— * Guidelines
— * All columns in the SQL Server SELECT clause that are not group functions
— must be in the GROUP BY clause
SELECT city, COUNT(*)
FROM customers
— * The GROUP BY columns don’t have to be in the SQL Server SELECT clause
SELECT COUNT(*)
FROM customers
GROUP BY city
— * You can list more than one column after the SQL Server GROUP BY clause
SELECT state, city , COUNT(*)
FROM customers
GROUP BY state, city
ORDER BY state
SELECT gender, martial_status, AVG(monthly_discount)
FROM customers
GROUP BY gender, martial_status
ORDER BY gender
SELECT gender, martial_status, AVG(monthly_discount)
FROM customers
GROUP BY gender
ORDER BY gender
Having
————————–
— HAVING Clause
————————–
— * Restricting Groups
— * Using the SQL Server WHERE clause, you can exclude rows before dividing them into groups.
SELECT city, AVG(monthly_discount)
FROM customers
WHERE city LIKE 'b%'
GROUP BY city
— * You cannot use the SQL Server WHERE clause to restrict groups
— * The HAVING Clause
SELECT city, AVG(monthly_discount)
FROM customers
WHERE city LIKE 'b%'
GROUP BY city
HAVING AVG(monthly_discount) > 40
/*
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
*/
Basic Join
—————————
— Join
—————————
–1. Intro
SELECT customer_id, last_name, first_name, pack_id
FROM customers
SELECT * FROM packages
— 2. Basic JOIN Syntax
SELECT customer_id, last_name, first_name, customers.pack_id, speed
FROM customers JOIN packages
ON customers.pack_id = customers.pack_id
–3. Using Table Prefix
SELECT customer_id, last_name, first_name, customers.pack_id, speed
FROM customers JOIN packages
ON customers.pack_id = customers.pack_id
–4 . Using Table Alias
SELECT cust.customer_id, cust.last_name, cust.first_name, cust.pack_id, pack.speed
FROM customers cust JOIN packages pack
ON cust.pack_id = pack.pack_id
SELECT cust.customer_id, cust.last_name, cust.first_name, cust.pack_id, pack.speed
FROM customers cust JOIN packages pack
ON cust.pack_id = cust.pack_id
WHERE pack.pack_id = 7
SELECT pack.speed, AVG(cust.monthly_discount)
FROM customers cust JOIN packages pack
ON cust.pack_id = cust.pack_id
GROUP BY pack.speed
Join More Than 2 Tables
—————————
— Join More than 2 Tables
—————————
SELECT cust.customer_id, cust.last_name, cust.first_name, cust.pack_id, pack.speed
FROM customers cust JOIN packages pack
ON cust.pack_id = pack.pack_id
SELECT * FROM sectors
SELECT * FROM packages
SELECT pack.speed, pack.monthly_payment, sec.sector_name
FROM packages pack JOIN sectors sec
ON pack.sector_id = sec.sector_id
SELECT cust.customer_id, cust.last_name, cust.first_name, cust.pack_id, pack.speed,sec.sector_name
FROM customers cust JOIN packages pack
ON cust.pack_id = pack.pack_id
JOIN sectors sec
ON pack.sector_id = sec.sector_id
SELECT cust.Last_Name, pack.speed, pack.monthly_payment, sec.sector_name
FROM packages pack JOIN sectors sec
ON pack.sector_id = sec.sector_id
JOIN customers cust
ON pack.pack_id = cust.pack_id
WHERE sec.sector_id = 1
ORDER BY cust.last_name
Outer Join
——————————————
— INNER, LEFT, RIGHT, and FULL OUTER Join
——————————————
SELECT * FROM customers
— 1. Inner Join
SELECT cust.customer_id, cust.last_name, cust.first_name, cust.pack_id, pack.speed
FROM customers cust JOIN packages pack
ON cust.pack_id = pack.pack_id
— 2. Left Outer Join
SELECT cust.customer_id, cust.last_name, cust.first_name, cust.pack_id, pack.speed
FROM customers cust LEFT OUTER JOIN packages pack
ON cust.pack_id = pack.pack_id
— 3. Right Outer Join
SELECT cust.customer_id, cust.last_name, cust.first_name, cust.pack_id, pack.speed
FROM customers cust RIGHT OUTER JOIN packages pack
ON cust.pack_id = pack.pack_id
— 4. Full Outer Join
SELECT cust.customer_id, cust.last_name, cust.first_name, cust.pack_id, pack.speed
FROM customers cust FULL OUTER JOIN packages pack
ON cust.pack_id = pack.pack_id
Self Join
—————————-
— SELF Join
—————————-
USE Northwind
GO
SELECT EmployeeID, LastName, ReportsTo FROM employees
— emp.ReportsTo = mng.EmployeeID
SELECT emp.LastName AS 'EmployeeName' , mng.LastName AS 'ManagerName'
FROM employees emp JOIN employees mng
ON emp.ReportsTo = mng.EmployeeID
SELECT emp.LastName AS 'EmployeeName' , mng.LastName AS 'ManagerName'
FROM employees emp LEFT OUTER JOIN employees mng
ON emp.ReportsTo = mng.EmployeeID
Sub Queries
——————————-
— Subqueries
——————————-
— 1. Intro
— * List customers who live in the same city as customer number 170
SELECT city FROM customers WHERE customer_id = 170
SELECT * FROM customers WHERE city = 'Lakewood'
SELECT *
FROM customers
WHERE city = (SELECT city FROM customers WHERE customer_id = 170)
— 2. Guidelines
— * The subquery is executed once before the main query, then the result returned by the
— subquery is submitted to the main query
— * The subquery must be enclosed by round brackets.
SELECT *
FROM customers
WHERE city = (SELECT city FROM customers WHERE customer_id = 170)
— * Place subqueries on the right side of the comparison condition.
SELECT *
FROM customers
WHERE (SELECT city FROM customers WHERE customer_id = 170) = city
— * We're going to learn two types of sub queries:
— * Single Row Subqueries
— * Multiple Row Subqueries
— 2. Single Row Subqueries
SELECT *
FROM customers
WHERE city = (SELECT city,state FROM customers WHERE customer_id = 170)
SELECT *
FROM customers
WHERE city = (SELECT city FROM customers WHERE customer_id > 170)
— * List packages whose sector number equals to the sector number of package number 10
SELECT *
FROM packages
WHERE sector_id = (SELECT sector_id FROM packages WHERE pack_id = 10)
— * List customers whose monthly payment is greater than the average monthly payment
— * List customers who joined the company after customer number 540
Sub Queries 2
———————————-
— Multiple Row Subquery
———————————-
———————————-
— 1. Intro
———————————-
SELECT customer_id, last_name, city, monthly_discount
FROM customers
WHERE monthly_discount >
(SELECT monthly_discount
FROM customers
WHERE customer_id = 10)
SELECT customer_id, last_name, city, monthly_discount
FROM customers
WHERE monthly_discount >
(SELECT monthly_discount
FROM customers
WHERE city = 'New York')
———————————-
— 2. IN
———————————-
SELECT customer_id, last_name, city, monthly_discount
FROM customers
WHERE monthly_discount IN
(SELECT monthly_discount
FROM customers
WHERE city = 'New York')
/*
MIN MAX
| |
|———————————-|
| |
19.46 46.10 49.04 54.19 58.59
*/
———————————-
— 3. ALL
———————————-
———————————-
— * >ALL
———————————-
SELECT customer_id, last_name, city, monthly_discount
FROM customers
WHERE monthly_discount >
(SELECT MAX(monthly_discount)
FROM customers
WHERE city = 'New York')
SELECT customer_id, last_name, city, monthly_discount
FROM customers
WHERE monthly_discount >ALL
(SELECT monthly_discount
FROM customers
WHERE city = 'New York')
/*
MIN MAX
| |
|———————————-|
| |
19.46 46.10 49.04 54.19 58.59
*/
———————————-
— *
———————————-
SELECT customer_id, last_name, city, monthly_discount
FROM customers
WHERE monthly_discount >ANY
(SELECT monthly_discount
FROM customers
WHERE city = 'New York')
/*
MIN MAX
| |
|———————————-|
| |
19.46 46.10 49.04 54.19 58.59
*/
———————————-
— *
