Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

SQL > SQL Tutorial > Flashcards

SQL Tutorial Flashcards

1
Q

SQL

A

Structured Query Language

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

RDBMS

A

Relational Database Management System

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

How is the data in RDBMS stored?

A

The data in RDBMS is stored in database objects called tables.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Table

A

A table is a collection of related data entries and it consists of columns and rows.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Record and/or Row

A

A record is a horizontal entity in a table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Select

A

The SELECT statement is used to select data from a database.

Syntax
SELECT column1, column2, …
FROM table_name;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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’;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Operators in The WHERE Clause

A

= , > , < , >= , <= , <> , BETWEEN , LIKE , IN

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

=

A

Equal

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

>

A

Greater than

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

<

A

Less than

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

> =

A

Greater than or equal

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

<=

A

Less than or equal

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

<>

A

Not equal

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

!=

A

Not equal

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

BETWEEN

A

between a certain range

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

LIKE

A

search for a pattern

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

IN

A

To Specify multiple possible values for a column

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

ORDER BY Several Columns

A

SELECT * FROM Customers
ORDER BY Country, CustomerName;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

Using Both ASC and DESC

A

SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

AND

A

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%’;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

AND vs OR

A

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;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

Combining AND and OR

A

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%’;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

OR

A

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’;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

OR vs AND

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

Combining AND and OR

A

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%’);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

NOT

A

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’;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

NOT LIKE

A

Select customers that does not start with the letter ‘A’:

SELECT * FROM Customers
WHERE CustomerName NOT LIKE ‘A%’;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

NOT BETWEEN

A

Select customers with a customerID not between 10 and 60:

SELECT * FROM Customers
WHERE CustomerID NOT BETWEEN 10 AND 60;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

NOT IN

A

Select customers that are not from Paris or London:

SELECT * FROM Customers
WHERE City NOT IN (‘Paris’, ‘London’);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

NOT Greater Than

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

INSERT INTO

A

The INSERT INTO statement is used to insert new records in a table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

INSERT INTO statement in two ways

A
  1. Specify both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3, …)
VALUES (value1, value2, value3, …);

  1. 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, …);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

NULL Values

A

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!

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

How to Test for NULL Values?

A

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;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q

IS NULL

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
42
Q

IS NOT NULL

A

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;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
43
Q

SELECT TOP

A

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;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
44
Q

LIMIT

A

Select the first 3 records of the Customers table:

SELECT * FROM Customers
LIMIT 3;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
45
Q

FETCH FIRST

A

Select the first 3 records of the Customers table:

SELECT * FROM Customers
FETCH FIRST 3 ROWS ONLY;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
46
Q

TOP PERCENT

A

SELECT TOP 50 PERCENT * FROM Customers;

SELECT * FROM Customers
FETCH FIRST 50 PERCENT ROWS ONLY;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
47
Q

ORDER BY

A

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;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
48
Q

SQL Aggregate Functions

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
49
Q

The most commonly used SQL aggregate functions are:

A

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
Q

MIN()

A

The MIN() function returns the smallest value of the selected column.

SELECT MIN(Price)
FROM Products

51
Q

MAX()

A

The MAX() function returns the largest value of the selected column.

SELECT MAX(Price)
FROM Products;

52
Q

Use MIN() with GROUP BY

A

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
Q

COUNT()

A

The COUNT() function returns the number of rows that matches a specified criterion.

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

54
Q

COUNT(*)

A

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
Q

COUNT(*)
Add a WHERE Clause

A

You can add a WHERE clause to specify conditions;

SELECT COUNT(ProductID)
FROM Products
WHERE Price > 20;

56
Q

COUNT(*)
Ignore Duplicates

A

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
Q

Use an Alias

A

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
Q

Use COUNT() with GROUP BY

A

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
Q

SUM()

A

The SUM() function returns the total sum of a numeric column.

60
Q

Return the sum of all Quantity fields in the OrderDetails table:

A

SELECT SUM(column_name)
FROM table_name
WHERE condition;

SELECT SUM(Quantity)
FROM OrderDetails;

61
Q

SUM()
Add a WHERE Clause

A

You can add a WHERE clause to specify conditions:

62
Q

Return the sum of the Quantity field for the product with ProductID 11:

A

SELECT SUM(Quantity)
FROM OrderDetails
WHERE ProductId = 11;

63
Q

SUM()
Use an Alias

A

Give the summarized column a name by using the AS keyword.

Name the column “total”:
SELECT SUM(Quantity) AS total
FROM OrderDetails;

64
Q

Use SUM() with GROUP BY

A

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
Q

SUM() With an Expression

A

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
Q

We can also join the OrderDetails table to the Products table to find the actual amount, instead of assuming it is 10 dollars:

A

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
Q

AVG()

A

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
Q

AVG()
Add a WHERE Clause

A

Example
Return the average price of products in category 1:

SELECT AVG(Price)
FROM Products
WHERE CategoryID = 1;

69
Q

AVG()
Use an Alias

A

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
Q

Higher Than Average

A

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
Q

Use AVG() with GROUP BY

A

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
Q

SQL LIKE

A

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
Q

Select all customers that starts with the letter “a”:

A

SELECT * FROM Customers
WHERE CustomerName LIKE ‘a%’;

Syntax
SELECT column1, column2, …
FROM table_name
WHERE columnN LIKE pattern;

74
Q

The _ Wildcard

A

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
Q

The % Wildcard

A

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
Q

Starts With

A

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
Q

Ends With

A

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
Q

Contains

A

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
Q

Combine Wildcards

A

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
Q

Without Wildcard

A

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
Q

Wildcard

A

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
Q

%

A

Represents zero or more characters

83
Q

_

A

Represents a single character

84
Q

[ ]

A

Represents any single character within the brackets *

85
Q
A

Represents any character not in the brackets *

86
Q

-

A

Represents any single character within the specified range *

87
Q

{}

A

Represents any escaped character **

88
Q

Using the % Wildcard

A

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
Q

Using the _ Wildcard

A

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
Q

Using the [ ] Wildcard

A

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
Q

Using the - Wildcard

A

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
Q

Combine Wildcards
Any wildcard, like % and _ , can be used in combination with other wildcards.

A

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
Q

Without Wildcard

A

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
Q

IN

A

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
Q

NOT IN

A

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
Q

IN (SELECT)

A

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
Q

NOT IN (SELECT)

A

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
Q

BETWEEN

A

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
Q

NOT BETWEEN

A

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
Q

BETWEEN with IN

A

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
Q

BETWEEN Text Values

A

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
Q

NOT BETWEEN Text Values

A

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
Q

BETWEEN Dates

A

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
Q

Aliases

A

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
Q

AS is Optional

A

Actually, in most database languages, you can skip the AS keyword and get the same result:

Example
SELECT CustomerID ID
FROM Customers;

106
Q

Alias for Columns

A

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
Q

Using Aliases With a Space Character

A

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
Q

Concatenate Columns

A

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
Q

Alias for Tables

A

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
Q

SQL JOIN

A

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

111
Q

Different Types of Joins

A

(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
Q

Inner Join

A

The inner join selects records that have matching values in both tables.

Join and inner join are the same thing

113
Q

Left Join

A

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
Q

Right Join

A

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
Q

Full Outer Join

A

The full outer join returns all records when there is a match in left (table 1) or right (table 2) table records.

116
Q

Self join

A

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
Q

Union

A

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
Q

Union All

A

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
Q

Group By

A

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
Q

GROUP BY with JOIN

A

SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
GROUP BY ShipperName;

121
Q

Having

A

The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.

SQL (2 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions