SQL Tutorial Flashcards
(121 cards)
1
Q
SQL
A
Structured Query Language
2
Q
RDBMS
A
Relational Database Management System
3
Q
How is the data in RDBMS stored?
A
The data in RDBMS is stored in database objects called tables.
4
Q
Table
A
A table is a collection of related data entries and it consists of columns and rows.
5
Q
Fields / Columns
A
A field is a column in a table that is designed to maintain specific information about every record in the table.
A column is a vertical entity in a table that contains all information associated with a specific field in a table.
6
Q
Record and/or Row
A
A record is a horizontal entity in a table.
7
Q
Select
A
The SELECT statement is used to select data from a database.
Syntax
SELECT column1, column2, …
FROM table_name;
8
Q
Select All
A
If you want to return all columns, without specifying every column name, you can use the SELECT * syntax:
SELECT * FROM Customers;
9
Q
The SQL SELECT DISTINCT Statement
A
The SELECT DISTINCT statement is used to return only distinct (different) values.
SELECT DISTINCT column1, column2, …
FROM table_name;
10
Q
Count Distinct
A
By using the DISTINCT keyword in a function called COUNT, we can return the number of different countries.
SELECT Count(*) AS DistinctCountries
FROM (SELECT DISTINCT Country FROM Customers);
11
Q
The SQL WHERE Clause
A
The WHERE clause is used to filter records.
It is used to extract only those records that fulfill a specified condition.
SELECT *
FROM Customers
WHERE Country=’Mexico’;
12
Q
Operators in The WHERE Clause
A
= , > , < , >= , <= , <> , BETWEEN , LIKE , IN
13
Q
=
A
Equal
14
Q
>
A
Greater than
15
Q
<
A
Less than
16
Q
> =
A
Greater than or equal
17
Q
<=
A
Less than or equal
18
Q
<>
A
Not equal
19
Q
!=
A
Not equal
20
Q
BETWEEN
A
between a certain range
21
Q
LIKE
A
search for a pattern
22
Q
IN
A
To Specify multiple possible values for a column
23
Q
ORDER BY
A
The ORDER BY keyword is used to sort the result-set in ascending or descending order.
SELECT column1, column2, …
FROM table_name
ORDER BY column1, column2, … ASC|DESC;
24
Q
ORDER BY Several Columns
A
SELECT * FROM Customers
ORDER BY Country, CustomerName;
25
Using Both ASC and DESC
SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;
26
AND
The WHERE clause can contain one or many AND operators.
The AND operator is used to filter records based on more than one condition
SELECT *
FROM Customers
WHERE Country = 'Spain' AND CustomerName LIKE 'G%';
27
AND vs OR
The AND operator displays a record if all the conditions are TRUE.
The OR operator displays a record if any of the conditions are TRUE.
All Conditions Must Be True
The following SQL statement selects all fields from Customers where Country is "Germany" AND City is "Berlin" AND PostalCode is higher than 12000:
SELECT * FROM Customers
WHERE Country = 'Germany'
AND City = 'Berlin'
AND PostalCode > 12000;
28
Combining AND and OR
The following SQL statement selects all customers from Spain that starts with a "G" or an "R".
Make sure you use parenthesis to get the correct result.
Select all Spanish customers that starts with either "G" or "R":
SELECT * FROM Customers
WHERE Country = 'Spain' AND (CustomerName LIKE 'G%' OR CustomerName LIKE 'R%');
Select all customers that either:
are from Spain and starts with either "G", or
starts with the letter "R":
SELECT * FROM Customers
WHERE Country = 'Spain' AND CustomerName LIKE 'G%' OR CustomerName LIKE 'R%';
29
OR
The WHERE clause can contain one or more OR operators.
The OR operator is used to filter records based on more than one condition, like if you want to return all customers from Germany but also those from Spain:
Select all customers from Germany or Spain:
SELECT *
FROM Customers
WHERE Country = 'Germany' OR Country = 'Spain';
30
OR vs AND
The OR operator displays a record if any of the conditions are TRUE.
The AND operator displays a record if all the conditions are TRUE.
31
Combining AND and OR
You can combine the AND and OR operators.
The following SQL statement selects all customers from Spain that starts with a "G" or an "R".
SELECT * FROM Customers
WHERE Country = 'Spain' AND (CustomerName LIKE 'G%' OR CustomerName LIKE 'R%');
32
NOT
The NOT operator is used in combination with other operators to give the opposite result, also called the negative result.
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;
SELECT * FROM Customers
WHERE NOT Country = 'Spain';
33
NOT LIKE
Select customers that does not start with the letter 'A':
SELECT * FROM Customers
WHERE CustomerName NOT LIKE 'A%';
34
NOT BETWEEN
Select customers with a customerID not between 10 and 60:
SELECT * FROM Customers
WHERE CustomerID NOT BETWEEN 10 AND 60;
35
NOT IN
Select customers that are not from Paris or London:
SELECT * FROM Customers
WHERE City NOT IN ('Paris', 'London');
36
NOT Greater Than
Select customers with a CustomerID not less than 50:
SELECT * FROM Customers
WHERE NOT CustomerId < 50;
Note: There is a not-less-than operator: !< that would give you the same result.
37
INSERT INTO
The INSERT INTO statement is used to insert new records in a table.
38
INSERT INTO statement in two ways
1. Specify both the column names and the values to be inserted:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
2. If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. Here, the INSERT INTO syntax would be as follows:
INSERT INTO table_name
VALUES (value1, value2, value3, ...);
39
NULL Values
A field with a NULL value is a field with no value.
If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.
Note: A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation!
40
How to Test for NULL Values?
It is not possible to test for NULL values with comparison operators, such as =, <, or <>.
We will have to use the IS NULL and IS NOT NULL operators instead.
IS NULL Syntax
SELECT column_names
FROM table_name
WHERE column_name IS NULL;
IS NOT NULL Syntax
SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;
41
IS NULL
The IS NULL operator is used to test for empty values (NULL values).
The following SQL lists all customers with a NULL value in the "Address" field:
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NULL;
Tip: Always use IS NULL to look for NULL values.
42
IS NOT NULL
The IS NOT NULL operator is used to test for non-empty values (NOT NULL values).
The following SQL lists all customers with a value in the "Address" field:
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NOT NULL;
43
SELECT TOP
The SELECT TOP clause is used to specify the number of records to return.
The SELECT TOP clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.
Select only the first 3 records of the Customers table:
SELECT TOP 3 * FROM Customers;
44
LIMIT
Select the first 3 records of the Customers table:
SELECT * FROM Customers
LIMIT 3;
45
FETCH FIRST
Select the first 3 records of the Customers table:
SELECT * FROM Customers
FETCH FIRST 3 ROWS ONLY;
46
TOP PERCENT
SELECT TOP 50 PERCENT * FROM Customers;
SELECT * FROM Customers
FETCH FIRST 50 PERCENT ROWS ONLY;
47
ORDER BY
Add the ORDER BY keyword when you want to sort the result, and return the first 3 records of the sorted result.
SELECT * FROM Customers
ORDER BY CustomerName DESC
LIMIT 3;
48
SQL Aggregate Functions
An aggregate function is a function that performs a calculation on a set of values, and returns a single value.
Aggregate functions are often used with the GROUP BY clause of the SELECT statement. The GROUP BY clause splits the result-set into groups of values and the aggregate function can be used to return a single value for each group
49
The most commonly used SQL aggregate functions are:
The most commonly used SQL aggregate functions are:
MIN() - returns the smallest value within the selected column
MAX() - returns the largest value within the selected column
COUNT() - returns the number of rows in a set
SUM() - returns the total sum of a numerical column
AVG() - returns the average value of a numerical column
Aggregate functions ignore null values (except for COUNT()).
50
MIN()
The MIN() function returns the smallest value of the selected column.
SELECT MIN(Price)
FROM Products
51
MAX()
The MAX() function returns the largest value of the selected column.
SELECT MAX(Price)
FROM Products;
52
Use MIN() with GROUP BY
Here we use the MIN() function and the GROUP BY clause, to return the smallest price for each category in the Products table:
SELECT MIN(Price) AS SmallestPrice, CategoryID
FROM Products
GROUP BY CategoryID;
53
COUNT()
The COUNT() function returns the number of rows that matches a specified criterion.
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
54
COUNT(*)
You can specify a column name instead of the asterix symbol (*).
If you specify a column name instead of (*), NULL values will not be counted.
SELECT COUNT(ProductName)
FROM Products;
55
COUNT(*)
Add a WHERE Clause
You can add a WHERE clause to specify conditions;
SELECT COUNT(ProductID)
FROM Products
WHERE Price > 20;
56
COUNT(*)
Ignore Duplicates
You can ignore duplicates by using the DISTINCT keyword in the COUNT() function.
If DISTINCT is specified, rows with the same value for the specified column will be counted as one.
SELECT COUNT(DISTINCT Price)
FROM Products;
57
Use an Alias
Give the counted column a name by using the AS keyword.
Name the column "Number of records":
SELECT COUNT(*) AS [Number of records]
FROM Products;
58
Use COUNT() with GROUP BY
Here we use the COUNT() function and the GROUP BY clause, to return the number of records for each category in the Products table:
SELECT COUNT(*) AS [Number of records], CategoryID
FROM Products
GROUP BY CategoryID;
59
SUM()
The SUM() function returns the total sum of a numeric column.
60
Return the sum of all Quantity fields in the OrderDetails table:
SELECT SUM(column_name)
FROM table_name
WHERE condition;
SELECT SUM(Quantity)
FROM OrderDetails;
61
SUM()
Add a WHERE Clause
You can add a WHERE clause to specify conditions:
62
Return the sum of the Quantity field for the product with ProductID 11:
SELECT SUM(Quantity)
FROM OrderDetails
WHERE ProductId = 11;
63
SUM()
Use an Alias
Give the summarized column a name by using the AS keyword.
Name the column "total":
SELECT SUM(Quantity) AS total
FROM OrderDetails;
64
Use SUM() with GROUP BY
Here we use the SUM() function and the GROUP BY clause, to return the Quantity for each OrderID in the OrderDetails table:
SELECT OrderID, SUM(Quantity) AS [Total Quantity]
FROM OrderDetails
GROUP BY OrderID;
65
SUM() With an Expression
The parameter inside the SUM() function can also be an expression.
If we assume that each product in the OrderDetails column costs 10 dollars, we can find the total earnings in dollars by multiply each quantity with 10:
Use an expression inside the SUM() function:
SELECT SUM(Quantity * 10)
FROM OrderDetails;
66
We can also join the OrderDetails table to the Products table to find the actual amount, instead of assuming it is 10 dollars:
Join OrderDetails with Products, and use SUM() to find the total amount:
SELECT SUM(Price * Quantity)
FROM OrderDetails
LEFT JOIN Products ON OrderDetails.ProductID = Products.ProductID;
67
AVG()
The AVG() function returns the average value of a numeric column.
Find the average price of all products:
SELECT AVG(Price)
FROM Products;
Note: NULL values are ignored.
SELECT AVG(column_name)
FROM table_name
WHERE condition;
68
AVG()
Add a WHERE Clause
Example
Return the average price of products in category 1:
SELECT AVG(Price)
FROM Products
WHERE CategoryID = 1;
69
AVG()
Use an Alias
Give the AVG column a name by using the AS keyword.
Example
Name the column "average price":
SELECT AVG(Price) AS [average price]
FROM Products;
70
Higher Than Average
To list all records with a higher price than average, we can use the AVG() function in a sub query:
Return all products with a higher price than the average price:
SELECT * FROM Products
WHERE price > (SELECT AVG(price) FROM Products);
71
Use AVG() with GROUP BY
Here we use the AVG() function and the GROUP BY clause, to return the average price for each category in the Products table:
Example
SELECT AVG(Price) AS AveragePrice, CategoryID
FROM Products
GROUP BY CategoryID;
72
SQL LIKE
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
There are two wildcards often used in conjunction with the LIKE operator:
The percent sign % represents zero, one, or multiple characters
The underscore sign _ represents one, single character
73
Select all customers that starts with the letter "a":
SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';
Syntax
SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;
74
The _ Wildcard
The _ wildcard represents a single character.
It can be any character or number, but each _ represents one, and only one, character.
Example
Return all customers from a city that starts with 'L' followed by one wildcard character, then 'nd' and then two wildcard characters:
SELECT * FROM Customers
WHERE city LIKE 'L_nd__';
75
The % Wildcard
The % wildcard represents any number of characters, even zero characters.
Example
Return all customers from a city that contains the letter 'L':
SELECT * FROM Customers
WHERE city LIKE '%L%';
76
Starts With
Starts With
To return records that starts with a specific letter or phrase, add the % at the end of the letter or phrase.
Example
Return all customers that starts with 'La':
SELECT * FROM Customers
WHERE CustomerName LIKE 'La%';
Tip: You can also combine any number of conditions using AND or OR operators.
Example
Return all customers that starts with 'a' or starts with 'b':
SELECT * FROM Customers
WHERE CustomerName LIKE 'a%' OR CustomerName LIKE 'b%';
77
Ends With
To return records that ends with a specific letter or phrase, add the % at the beginning of the letter or phrase.
Example
Return all customers that ends with 'a':
SELECT * FROM Customers
WHERE CustomerName LIKE '%a';
Tip: You can also combine "starts with" and "ends with":
Example
Return all customers that starts with "b" and ends with "s"
SELECT * FROM Customers
WHERE CustomerName LIKE 'b%s';
78
Contains
To return records that contains a specific letter or phrase, add the % both before and after the letter or phrase.
SELECT * FROM Customers
WHERE CustomerName LIKE '%or%';
79
Combine Wildcards
Any wildcard, like % and _ , can be used in combination with other wildcards.
Example
Return all customers that starts with "a" and are at least 3 characters in length:
SELECT * FROM Customers
WHERE CustomerName LIKE 'a__%';
Example
Return all customers that have "r" in the second position:
SELECT * FROM Customers
WHERE CustomerName LIKE '_r%';
80
Without Wildcard
If no wildcard is specified, the phrase has to have an exact match to return a result.
Example
Return all customers from Spain:
SELECT * FROM Customers
WHERE Country LIKE 'Spain';
81
Wildcard
A wildcard character is used to substitute one or more characters in a string.
Wildcard characters are used with the LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
ExampleGet your own SQL Server
Return all customers that starts with the letter 'a':
SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';
82
%
Represents zero or more characters
83
_
Represents a single character
84
[ ]
Represents any single character within the brackets *
85
^
Represents any character not in the brackets *
86
-
Represents any single character within the specified range *
87
{}
Represents any escaped character **
88
Using the % Wildcard
The % wildcard represents any number of characters, even zero characters.
Example
Return all customers that ends with the pattern 'es':
SELECT * FROM Customers
WHERE CustomerName LIKE '%es';
Example
Return all customers that contains the pattern 'mer':
SELECT * FROM Customers
WHERE CustomerName LIKE '%mer%';
89
Using the _ Wildcard
The _ wildcard represents a single character.
It can be any character or number, but each _ represents one, and only one, character.
Example
Return all customers with a City starting with any character, followed by "ondon":
SELECT * FROM Customers
WHERE City LIKE '_ondon';
Example
Return all customers with a City starting with "L", followed by any 3 characters, ending with "on":
90
Using the [ ] Wildcard
The [ ] wildcard returns a result if any of the characters inside gets a match.
Example
Return all customers starting with either "b", "s", or "p":
SELECT * FROM Customers
WHERE CustomerName LIKE '[bsp]%';
91
Using the - Wildcard
The - wildcard allows you to specify a range of characters inside the [] wildcard.
Example
Return all customers starting with "a", "b", "c", "d", "e" or "f"
SELECT * FROM Customers
WHERE CustomerName LIKE '[a-f]%';
92
Combine Wildcards
Any wildcard, like % and _ , can be used in combination with other wildcards.
Example
Return all customers that starts with "a" and are at least 3 characters in length:
SELECT * FROM Customers
WHERE CustomerName LIKE 'a__%';
Example
Return all customers that have "r" in the second position:
SELECT * FROM Customers
WHERE CustomerName LIKE '_r%';
93
Without Wildcard
If no wildcard is specified, the phrase has to have an exact match to return a result.
Example
Return all customers from Spain:
SELECT * FROM Customers
WHERE Country LIKE 'Spain';
94
IN
The IN operator allows you to specify multiple values in a WHERE clause.
The IN operator is a shorthand for multiple OR conditions.
ExampleGet your own SQL Server
Return all customers from 'Germany', 'France', or 'UK'
SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
95
NOT IN
By using the NOT keyword in front of the IN operator, you return all records that are NOT any of the values in the list.
Example
Return all customers that are NOT from 'Germany', 'France', or 'UK':
SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');
96
IN (SELECT)
You can also use IN with a subquery in the WHERE clause.
With a subquery you can return all records from the main query that are present in the result of the subquery.
Example
Return all customers that have an order in the Orders table:
SELECT * FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders);
97
NOT IN (SELECT)
The result in the example above returned 74 records, that means that there are 17 customers that haven't placed any orders.
Let us check if that is correct, by using the NOT IN operator.
Example
Return all customers that have NOT placed any orders in the Orders table:
SELECT * FROM Customers
WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders);
98
BETWEEN
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.
The BETWEEN operator is inclusive: begin and end values are included.
ExampleGet your own SQL Server
Selects all products with a price between 10 and 20:
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
99
NOT BETWEEN
To display the products outside the range of the previous example, use NOT BETWEEN:
Example
SELECT * FROM Products
WHERE Price
NOT BETWEEN 10 AND 20;
100
BETWEEN with IN
The following SQL statement selects all products with a price between 10 and 20. In addition, the CategoryID must be either 1,2, or 3:
Example
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20
AND CategoryID IN (1,2,3);
101
BETWEEN Text Values
The following SQL statement selects all products with a ProductName alphabetically between Carnarvon Tigers and Mozzarella di Giovanni:
Example
SELECT * FROM Products
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;
The following SQL statement selects all products with a ProductName between Carnarvon Tigers and Chef Anton's Cajun Seasoning:
Example
SELECT * FROM Products
WHERE ProductName BETWEEN "Carnarvon Tigers" AND "Chef Anton's Cajun Seasoning"
ORDER BY ProductName;
102
NOT BETWEEN Text Values
The following SQL statement selects all products with a ProductName not between Carnarvon Tigers and Mozzarella di Giovanni:
Example
SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;
103
BETWEEN Dates
The following SQL statement selects all orders with an OrderDate between '01-July-1996' and '31-July-1996':
Example
SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/01/1996# AND #07/31/1996#;
OR:
Example
SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';
104
Aliases
SQL aliases are used to give a table, or a column in a table, a temporary name.
Aliases are often used to make column names more readable.
An alias only exists for the duration of that query.
An alias is created with the AS keyword.
ExampleGet your own SQL Server
SELECT CustomerID AS ID
FROM Customers;
105
AS is Optional
Actually, in most database languages, you can skip the AS keyword and get the same result:
Example
SELECT CustomerID ID
FROM Customers;
106
Alias for Columns
The following SQL statement creates two aliases, one for the CustomerID column and one for the CustomerName column:
Example
SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;
107
Using Aliases With a Space Character
If you want your alias to contain one or more spaces, like "My Great Products", surround your alias with square brackets or double quotes.
Example
Using [square brackets] for aliases with space characters:
SELECT ProductName AS [My Great Products]
FROM Products;
Example
Using "double quotes" for aliases with space characters:
SELECT ProductName AS "My Great Products"
FROM Products;
Note: Some database systems allows both [] and "", and some only allows one of them.
108
Concatenate Columns
The following SQL statement creates an alias named "Address" that combine four columns (Address, PostalCode, City and Country):
Oracle Example
SELECT CustomerName, (Address || ', ' || PostalCode || ' ' || City || ', ' || Country) AS Address
FROM Customers;
109
Alias for Tables
The same rules applies when you want to use an alias for a table.
Example
Refer to the Customers table as Persons instead:
SELECT * FROM Customers AS Persons;
It might seem useless to use aliases on tables, but when you are using more than one table in your queries, it can make the SQL statements shorter.
The following SQL statement selects all the orders from the customer with CustomerID=4 (Around the Horn). We use the "Customers" and "Orders" tables, and give them the table aliases of "c" and "o" respectively (Here we use aliases to make the SQL shorter):
Example
SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName='Around the Horn' AND c.CustomerID=o.CustomerID;
The following SQL statement is the same as above, but without aliases:
SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName
FROM Customers, Orders
WHERE Customers.CustomerName='Around the Horn' AND Customers.CustomerID=Orders.CustomerID;
Aliases can be useful when:
There are more than one table involved in a query
Functions are used in the query
Column names are big or not very readable
Two or more columns are combined together
110
SQL JOIN
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
111
Different Types of Joins
(INNER) JOIN: Returns records that have matching values in both tables
LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table
112
Inner Join
The inner join selects records that have matching values in both tables.
Join and inner join are the same thing
113
Left Join
The left join keyword returns all records from the table (table1), the the matching records from the right table (table2). The result is 0 records from the right side if there is no match
114
Right Join
The right join returned all the records from the right table (table 2), and the matching records from the left table (table 2). The result is 0 records from the left side if there is no match
115
Full Outer Join
The full outer join returns all records when there is a match in left (table 1) or right (table 2) table records.
116
Self join
A self join is a regular join, but the table is joined with itself.
SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;
T1 and T2 are different table aliases for the same table.
SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City
ORDER BY A.City;
117
Union
The union operator is used to combine the result-set of two or more select statements.
Every SELECT statement within UNION must have the same number of columns.
The columns must also have similar data types
The columns in every SELECT statement must also be in the same order
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
118
Union All
The UNION operator selects only distinct values by default. To allow duplicate values use union all
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
Note: The column names in the result-set are usually equal to the column names in the first SELECT statement.
119
Group By
The group by statement groups rows that have the same values into summary rows
The GROUP BY statement is often used with aggregate functions (count(), max(), min(), sum(), Avg()) to geroup the result-set by one or more columns.
120
GROUP BY with JOIN
SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
GROUP BY ShipperName;
121
Having
The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.
