MySQL Basics Flashcards
(121 cards)
1
Q
What does RDBMS stand for?
A
RDBMS stands for Relational Database Management System.
RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access. The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows.
2
Q
What is a field?
A
Every table is broken up into smaller entities called fields. The fields in the Customers table consist of CustomerID, CustomerName, ContactName, Address, City, PostalCode and Country. 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.
3
Q
What is a record?
A
A record, also called a row, is each individual entry that exists in a table. For example, there are 91 records in the above Customers table. A record is a horizontal entity in a table.
4
Q
SELECT * FROM table_name;
example:
SELECT * FROM Customers;
A
Selects all the records in the “Customers” table and shows them in a result table, called the result-set.
5
Q
SELECT column1, column2, …
FROM table_name;
A
Selects the data from table “table_name” in column1, column2,… and shows the data in a result table.
6
Q
SELECT DISTINCT column1, column2, …
FROM table_name;
A
The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.
7
Q
SELECT COUNT(DISTINCT column_name) FROM table_name;
example:
SELECT COUNT(DISTINCT Country) FROM Customers;
A
Lists and counts the number of different (distinct) values in the column “Country” of the table “Customers”.
8
Q
What does the WHERE clause do?
A
The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified condition.
9
Q
SELECT column1, column2, …
FROM table_name
WHERE condition;
example1:
SELECT * FROM Customers
WHERE Country=’Mexico’;
example2:
A
example1:
Selects all records in the table “Customers” where the value equals “Mexico” in the column “Country”.
example2:
Selects all values in the table “Customers” where the value equals 1 in the column “CostumerID”.
SQL requires single quotes around text values. However, numeric fields should not be enclosed in quotes.
10
Q
Which operators can be used in the WHERE clause?
A
= Equal > Greater than < Less than >= Greater than or equal <= Less than or equal <> Not equal. NOTE: in some versions of SQL this operator may be written as != BETWEEN Between a certain range LIKE Search for a pattern IN To specify multiple possible values for a column
11
Q
SELECT * FROM table_name
WHERE column_name BETWEEN integer AND integer
example:
SELECT * FROM Products
WHERE Price BETWEEN 50 AND 60;
A
Lists all records of table “Products” where the values in column “Price” are between 50 and 60.
12
Q
SELECT * FROM table_name
WHERE column_name LIKE ‘,,,’;
example:
SELECT * FROM Customers
WHERE City LIKE ‘s%’;
A
Lists all records in the table “Customers” where the values in column “City” start with the character ‘s’.
13
Q
SELECT * FROM table_name
WHERE column_name IN (strings, intgers,…);
example:
SELECT * FROM Customers
WHERE City IN (‘Paris’ , ‘London’);
A
Lists all records of table “Customers” where the values in column “City” are equal to “Paris” or “London”.
14
Q
SELECT column1, column2, …
FROM table_name
WHERE condition1 AND condition2 AND condition3 …;
SELECT column1, column2, …
FROM table_name
WHERE condition1 OR condition2 OR condition3 …;
SELECT column1, column2, …
FROM table_name
WHERE NOT condition;
A
The AND operator displays a record if all the conditions separated by AND are TRUE.
The OR operator displays a record if any of the conditions separated by OR is TRUE.
The NOT operator displays a record if the condition(s) is NOT TRUE.
15
Q
SELECT * FROM Customers
WHERE Country=’Germany’ AND City=’Berlin’;
A
Lists all records from table “Customers” where the value in the column “Country” is equal to “Germany” AND the value in the column “City” is equal to “Berlin”.
16
Q
SELECT * FROM Customers
WHERE City=’Berlin’ OR City=’München’;
A
Lists all records in table “Costumers” where the value in column “City” equals “Berlin” OR the value in column “City” equals “München”.
17
Q
SELECT * FROM Customers
WHERE NOT Country=’Germany’;
A
Lists all records of table “Customers” where the value in column “Country” is NOT equal to “Germany”.
18
Q
SELECT * FROM Customers
WHERE Country=’Germany’ AND (City=’Berlin’ OR City=’München’);
A
Lists all records from table “Customers” where the value in column “Country” equals to “Germany” AND the value in column “City” equals to “Berlin” or to “München” (use parenthesis fo form complex expressions).
19
Q
SELECT * FROM Customers
WHERE NOT Country=’Germany’ AND NOT Country=’USA’;
A
Lists all records of table “Costumers” where the value in column “Country” equals NOT “Germany” and NOT “USA”.
20
Q
SELECT column1, column2, …
FROM table_name
ORDER BY column1, column2, … ASC | DESC;
A
The ORDER BY keyword is used to sort the result-set in ascending or descending order.
The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.
21
Q
SELECT * FROM Customers
ORDER BY Country DESC, CustomerName DESC;
A
Lists all records from table “Costumers” and orders them first in descending order by the value in column “Country” and then records with the same value in “Country” are further ordered in descending order by the value in column “CostumerName”.
Numbers are first ordered then letters.
22
Q
example1:
INSERT INTO table_name (column1, column2, column3, …)
VALUES (value1, value2, value3, …);
example2:
INSERT INTO table_name
VALUES (value1, value2, value3, …);
A
The INSERT INTO statement is used to insert new records in a table.
It is possible to write the INSERT INTO statement in two ways:
- Specify both the column names and the values to be inserted.
- 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.
23
Q
What is a NULL Value?
How to test for 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!
It is not possible to test for NULL values with comparison operators, such as =, .
We will have to use the IS NULL and IS NOT NULL operators instead.
24
Q
SELECT column_names
FROM table_name
WHERE column_name IS NULL;
example:
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NULL;
A
Lists all records in column “CostumerName”, “ContactName” and “Address” of table “Costumers” where the value in column “Address” is empty (NULL).
25
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NOT NULL;
Lists all records in column "CostumerName", "ContactName" and "Address" of table "Costumers" where the value in column "Address" is NOT empty (NOT NULL).
26
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
The UPDATE statement is used to modify the existing records in a table.
Be careful when updating records in a table! Notice the WHERE clause in the UPDATE statement. The WHERE clause specifies which record(s) that should be updated. If you omit the WHERE clause, all records in the table will be updated!
27
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;
This statement updates the first record (CostumerID = 1) of table "Costumers" with the new value "Alfred Schmidt" in column "ContactName" and with the new value "Frankfurt" in the column "City".
28
UPDATE table_name
SET column_name1=new_value
WHERE column_name2=value;
example:
UPDATE Customers
SET ContactName='Juan'
WHERE Country='Mexico';
This statement will update the value in column "ContactName" to "Juan" for all records where the value for column "Country" is "Mexico".
It is the WHERE clause that determines how many records will be updated.
When you omit the WHERE clause, ALL records will be updated!
29
DELETE FROM table_name WHERE condition;
example:
DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';
The DELETE statement is used to delete existing records in a table.
The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted!
example:
This statement deletes in table "Costumers" the value "Alfreds Futterkiste" in column "CustomerName".
30
DELETE FROM table_name;
This statement deletes all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact.
31
SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;
example:
SELECT * FROM Customers
WHERE Country='Germany'
LIMIT 3;
Lists from all records of table "Customers" the first 3 records where the value in column "Country" is "Germany".
32
SELECT * FROM Customers
| LIMIT 3;
Lists the first 3 records of the table "Customers".
33
SELECT MIN(column_name)
FROM table_name
WHERE condition;
SELECT MAX(column_name)
FROM table_name
WHERE condition;
```
The MIN( ) function returns the smallest value of the selected column where a certain condition is met.
The MAX( ) function returns the largest value of the selected column where a certain condition is met.
```
34
```
SELECT MIN(Price) AS SmallestPrice
FROM Products;
```
Finds the smalles value of table "Products" in column "Price" and lists it in result_set (result_table) with columname "SmallestPrice".
35
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
The COUNT( ) function returns the number of rows from table "table_name" that matches a specified criterion.
36
SELECT AVG(column_name)
FROM table_name
WHERE condition;
The AVG( ) function returns the average value of a numeric column where a certain condition is met.
37
SELECT SUM(column_name)
FROM table_name
WHERE condition;
The SUM( ) function returns the total sum of a numeric column where a certian condition is met.
38
SELECT * FROM table_name
WHERE column_name LIKE pattern;
1. WHERE column_name LIKE 'a%'
2. WHERE column_name LIKE '%a'
3. WHERE column_name LIKE '%or%'
4. WHERE column_name LIKE '_r%'
5. WHERE column_name LIKE 'a_%'
6. WHERE column_name LIKE 'a__%'
7. WHERE column_name LIKE 'a%o'
8. WHERE column_name LIKE 'h[oa]t'
9. WHERE column_name LIKE 'h[^oat]t'
10. WHERE column_name LIKE 'c[a-b]t'
11. WHERE column_name LIKE '[!bsp]%'
There are two wildcards often used in conjunction with the LIKE operator:
a) The percent sign (%) represents zero, one, or multiple characters
b) The underscore sign (_) represents one, single character
c) [ ] Represents any single character within the brackets
d) ^ Represents any character not in the brackets
e) - Represents any single character within the specified range
Tip: You can also combine any number of conditions using AND or OR operators.
1. Finds any values that start with "a"
2. Finds any values that end with "a"
3. Finds any values that have "or" in any position
4. Finds any values that have "r" in the second position
5. Finds any values that start with "a" and are at least 2 characters in length
6. Finds any values that start with "a" and are at least 3 characters in length
7. Finds any values that start with "a" and ends with "o"
8. h[oa]t finds hot and hat, but not hit
9. h[^oa]t finds hit, but not hot and hat
10. c[a-b]t finds cat and cbt
11. Finds any value NOT starting with "b", "s" or "p"
39
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
example:
SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');
The IN operator is a shorthand for multiple OR conditions.
| Lists all records of table "Customers" where the value in column "Country" equals "Germany", "France" or "UK".
40
SELECT * FROM Customers
| WHERE Country NOT IN (SELECT Country FROM Suppliers);
Lists all records of table "Customers" where the value in column "Country" equals NOT the value in column "Country" of table "Suppliers".
41
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
example:
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
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.
Example:
Lists all records from table "Products" where the value in column "Price" is between 10 and 20.
42
SELECT * FROM Products
| WHERE Price NOT BETWEEN 10 AND 20;
Lists all records from table "Products" where the value in column "Price" is NOT between 10 and 20.
43
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20
AND CategoryID NOT IN (1,2,3);
Lists all records from table "Products" where the value in column "Price" is between 10 and 20 and where the value in column "CategoryID" is not 1, 2, or 3.
44
SELECT * FROM Products
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;
Lists all records from table "Products" where the value in column "ProducrName" is between "Carnarvon Tigers" AND "Mozzarella di Giovanni" and orders them in ascending alphabetic order by value in column "ProductName".
45
SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/01/1996# AND #07/31/1996#;
or:
SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';
Lists all records from table "Orders" where the value in column "OrderDate" is between 07/01/1996 AND 07/31/1996 or between 1996-07-01 AND 1996-07-31.
46
What are Aliases for?
Examples:
SELECT column_name AS alias_name
FROM table_name;
or:
```
SELECT column_name(s)
FROM table_name AS alias_name;
```
SQL alisases 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.
47
SELECT CustomerID AS ID, CustomerName AS Customer
| FROM Customers;
Lists all values in column "CustomerID" of table "Customers" and displays them as "ID" and lists all values in column "CustomerName" of table "Customers" and displays them as "Customer" in the result_set.
48
SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;
Lists all values in column "CustomerName" of table "Customers" and displays them as "Customer" and lists all values in column "ContactName" of table "Customers" and displays them as "ContactPerson" in the result_set.
Note: It requires double quotation marks or square brackets if the alias name contains spaces.
49
SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS Address
FROM Customers;
Lists all values of column "CustomerName" of table "Customer" and concatenats all values of columns "Address", "PostalCode", "City" and "Country" by adding a comma + whitespace (", ") or a whitespace (" ") between theses values and lists them as "Address" in the result_set.
50
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;
example without aliases:
SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName
FROM Customers, Orders
WHERE Customers.CustomerName='Around the Horn' AND Customers.CustomerID=Orders.CustomerID;
We use the "Customers" and "Orders" tables, and give them the table aliases of "c" and "o" respectively (to make the statement shorter). The statement lists all values of column "OrderID" and of column "OrderDate" of table "Orders" and of column "CustomerName" of table "Customers" where values of column "CustomerName" of table "Customers" equal "Around the Horn" and values of column "CustomerID" of table "Customer" equal values of column "CustomerID" of table "Orders".
51
What are the different types of SQL JOINs ?
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
1. (INNER) JOIN: Returns records that have matching values in both tables
2. LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
3. RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
4. FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table
52
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
example:
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
The relationship between the two tables "Orders" and "Customers" is the "CustomerID" column, existing in both tables. This statement selects records that have matching values in column "CustomerID" in both tables and lists the values of "OrderID" from table "Orders", of "CustomerName" from table "Customers" and of "OrderDate" from table "Orders".
53
```
SELECT column_name(s)
FROM ((table1
INNER JOIN table2 ON table1.column_name1 = table2.column_name1)
INNER JOIN table3 ON table1.column_name2 = table3.column_name2);
```
example:
SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);
The relationship between the two tables "Orders" and "Customers" is the "CustomerID" column, existing in both tables, and the relationship between the two tables "Orders" and "Shippers" is the "ShipperID" column, existing in both columns. This statement selects records that have matching values in column "CustomerID" in tables "Orders" and "Customers" and matching values in column "ShipperID" in tables "Orders" and "Shippers" and lists the values of "OrderID" from table "Orders", of "CustomerName" from table "Customers" and of "ShipperName" from table "Shippers".
54
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
example:
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;
The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match.
Note: In some databases LEFT JOIN is called LEFT OUTER JOIN.
The LEFT JOIN keyword returns all records from the left table (Customers), even if there are no matches in the right table (Orders) and displays a NULL value in these cases.
55
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
example:
SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;
The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1). The result is NULL records from the left side, if there is no match.
Note: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.
56
```
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;
```
example:
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records.
Tip: FULL OUTER JOIN and FULL JOIN are the same.
Note: The FULL OUTER JOIN keyword returns all matching records from both tables whether the other table matches or not. So, if there are rows in "Customers" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Customers", those rows will be listed as well and the NOT matching value of the relationship column will be displayed as NULL.
57
SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;
example:
```
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;
```
A self join is a regular join, but the table is joined with itself.
T1 and T2 are different table aliases for the same table.
The example statement matches customers that are from the same city and lists them as "CustomerName1", "CustomerName2" and lists the "City" in witch both are located.
58
What is the UNION operator used for?
The UNION operator is used to combine the result-set of two or more SELECT statements and returns only the distinct values.
1. Every SELECT statement within UNION must have the same number of columns
2. The columns must also have similar data types
3. The columns in every SELECT statement must also be in the same order
Syntax:
```
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
```
59
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;
This statement returns the cities (only distinct values) from both the "Customers" and the "Suppliers" table and orders them alphabetically in ascending order.
Note: If some customers or suppliers have the same city, each city will only be listed once, because UNION selects only distinct values. Use UNION ALL to also select duplicate values!
Note: The column names in the result-set are usually equal to the column names in the first SELECT statement.
60
```
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
```
example:
SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;
This statement returns the cities (duplicate values also) from both the "Customers" and the "Suppliers" table and orders them alphabetically in ascending order.
Note: The column names in the result-set are usually equal to the column names in the first SELECT statement.
61
```
SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;
```
This statement returns the German cities (only distinct values) from both the "Customers" and the "Suppliers" table ordered alphabetically in ascending order.
62
SELECT 'Customer' AS Type, ContactName, City, Country
FROM Customers
UNION
SELECT 'Supplier', ContactName, City, Country
FROM Suppliers;
This statement lists all customers and suppliers. Notice the "AS Type" above - it is an alias. SQL Aliases are used to give a table or a column a temporary name. An alias only exists for the duration of the query. So, here we have created a temporary column named "Type", that list whether the contact person is a "Customer" or a "Supplier". Note: The column names in the result-set are usually equal to the column names in the first SELECT statement. That is why it is not necessary to set "Supplier" as Type in the second SELECT statement.
63
```
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
```
example:
```
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;
```
The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".
The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.
Example:
This statement lists the number of customers as "Count(CustomerID)" for each country, displayed as "Country", so the result_set has two columns.
64
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;
This statement selects columns "CustomerID" and "Country" of table "Customers" and counts the number of customers ("COUNT(CustomersID)") which are in the same country ("GROUP BY Country") and orders the result_set by number of customers in each country in descending order.
65
SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
GROUP BY ShipperName;
Selects the column ShipperName of table Shippers and counts the number of orders in table "Orders" ("COUNT(Orders.OrderID)") for each shipper ("GROUP BY ShipperName") and displays them in the result_set as "NumberOfOrders" and left joins the two tables "Orders" and "Shippers" on the related column "ShipperID" of both tables.
66
```
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
```
example:
```
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;
```
The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.
Example:
This statement selects "CustomerID" and "Country" of table "Customers" and counts the number of customers ("COUNT(CustomerID)") in each country ("GROUP BY Country") but displays only those resulsts where more than 5 customers were counted for one country and displays the count of customers in descending order.
67
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM (Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;
Displays the column "LastName" of table "Employees" and displays the number of counts of orders in table "Orders" ("COUNT(Orders.OrderID)") per "LastName" of employees ("GROUP BY LastName") as "NumberOfOrders" by making an "INNER JOIN" to table "Employees", which searches for the matching values in column "EmployeeID" of table "Orders" and in column "EmployeeID" of table "Employees". And displays only counts that have more than 10 orders per employee.
68
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
WHERE LastName = 'Davolio' OR LastName = 'Fuller'
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 25;
Displays the column "LastName" of table "Employees" and displays the number of counts of orders in table "Orders" ("COUNT(Orders.OrderID)") per "LastName" of employees ("GROUP BY LastName") as "NumberOfOrders" by making an "INNER JOIN" to table "Employees", which searches for the matching values in column "EmployeeID" of table "Orders" and in column "EmployeeID" of table "Employees". On the condition that the value in column "LastName" equals to "Davolio" or "Fuller" and displays only counts that have more than 25 orders per employee. In short, it lists if employees "Davolio" or "Fuller" habe registerd more than 25 orders.
69
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);
example:
SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);
The EXISTS operator is used to test for the existence of any record in a subquery. The EXISTS operator returns TRUE if the subquery returns one or more records.
Example:
Lists the records of the column "SupplierName" of table "Suppliers" of whom the ID (SupplierID) is in both tables, "Suppliers" and "Products", and where the value in column "Price" of table "Products" is less than 20. In short, it lists the suppliers with a product price less than 20.
70
```
SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
(SELECT column_name
FROM table_name
WHERE condition);
```
example:
```
SELECT ProductName
FROM Products
WHERE ProductID = ANY
(SELECT ProductID
FROM OrderDetails
WHERE Quantity = 10);
```
The ANY operator allows to perform a comparison between a single column value and a range of other values. Note: The operator must be a standard comparison operator.
Example:
Llists only values of column "ProductName" of table "Products" whose ProductID is listed 10 times in table "OrderDetails".
71
SELECT ALL column_name(s)
FROM table_name
WHERE condition;
example:
SELECT ALL ProductName
FROM Products
WHERE TRUE;
The ALL operator:
1. returns a boolean value as a result
2. returns TRUE if ALL of the subquery values meet the condition
3. is used with SELECT, WHERE and HAVING statements
ALL means that the condition will be true only if the operation is true for all values in the range.
Example:
Lists all the product names.
72
```
SELECT ProductName
FROM Products
WHERE ProductID = ALL
(SELECT ProductID
FROM OrderDetails
WHERE Quantity = 10);
```
The following SQL statement lists the ProductName if ALL the records in the OrderDetails table has Quantity equal to 10. This will of course return FALSE (or no record) because the Quantity column has many different values (not only the value of 10).
73
example1:
SELECT *
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;
example2:
SELECT column1, column2, column3, ...
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;
The SELECT INTO statement copies data from one table into a new table.
Example1:
Copies all columns into a new table.
Example2:
Copies only some columns into a new table.
The new table will be created with the column-names and types as defined in the old table. You can create new column names using the AS clause.
74
SELECT * INTO CustomersBackup2017
| FROM Customers;
This statement creates a backup copy called "CustomersBackup2017" of table "Customers".
75
SELECT * INTO CustomersBackup2017 IN 'Backup.mdb'
| FROM Customers;
This statement uses the IN clause to copy the table "Customers" into a new table called "CustomersBackup2017" in another database called "Backup.mdb".
76
SELECT CustomerName, ContactName INTO CustomersBackup2017
| FROM Customers;
This statement copies the columns "CustomerName" and "ContactName" of table "Customers" into a new table called "CustomersBackup2017".
77
SELECT * INTO CustomersGermany
FROM Customers
WHERE Country = 'Germany';
This statement selects only the records of table "Customers" where the value in column "Country" equals "Germany" and copies them into a new table called "CustomersGermany".
78
SELECT Customers.CustomerName, Orders.OrderID
INTO CustomersOrderBackup2017
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
This statement copies data from table "Customers" and from table "Orders" into a new table called "CustomersOrderBackup2017".
79
SELECT * INTO newtable
FROM oldtable
WHERE 1 = 0;
SELECT INTO can also be used to create a new, empty table using the schema of another. Just add a WHERE clause that causes the query to return no data (WHERE 1 = 0).
80
example1:
INSERT INTO table2
SELECT * FROM table1
WHERE condition;
example2:
INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;
The INSERT INTO SELECT statement copies data from one table and inserts it into another table. The INSERT INTO SELECT statement requires that the data types in source and target tables match.
Note: The existing records in the target table are unaffected.
Example1:
Copies all columns from table1 to table2.
Example2:
Copies only some columns from table1 into table2.
81
INSERT INTO Customers (CustomerName, City, Country)
| SELECT SupplierName, City, Country FROM Suppliers;
This statement copies the values of columns "SupplierName", "City" and "Country" of table "Suppliers" into the columns "CustomerName", "City" and "Country" of table "Customers". The columns in table "Customers" that are not filled with data, will contain NULL.
82
INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers
WHERE Country='Germany';
This statement copies only the values of columns "SupplierName", "City" and "Country" of table "Suppliers" where the value in column "Country" equals "Germany" into the columns "CustomerName", "City" and "Country" of table "Customers". The columns in table "Customers" that are not filled with data, will contain NULL.
83
```
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;
```
The CASE statement goes through coditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause. If there is no ELSE part and no conditions are true, it returns NULL.
84
SELECT OrderID, Quantity,
CASE
WHEN Quantity > 30 THEN 'The quantity is greater than 30'
WHEN Quantity = 30 THEN 'The quantity is 30'
ELSE 'The quantity is under 30'
END AS QuantityText
FROM OrderDetails;
Lists the values of columns "OrderID" and "Quantity" of table "OrderDetails" and checks in each record whether the value in column "Quantity" is greater, equal or under 30 and displays the findings in new column "QuantitText".
85
```
SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
WHEN City IS NULL THEN Country
ELSE City
END);
```
Lists the values of columns "CustomerName", "City" and "Country" of table "Customers" and checks in each record if the value in column "City" is NOT NULL, then orders the records alphabetically in ascending order of cities. If the value in column "City" is NULL then the records are ordered accordingly by countries.
86
SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0))
FROM Products;
Suppose that the "UnitsOnOrder" column is optional, and may contain NULL values. Then the result of the following statement will be NULL if any of the "UnitsOnOrder" values are Null. In case of NULL values it is better to use the IFNULL() function or the COALESCE() function. Instead of NULL these functions returns 0 (with which mathematical operations are possible):
SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0))
FROM Products;
or
SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0))
FROM Products;
87
What is a stored procedure?
A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.
CREATE PROCEDURE procedure_name
AS
sql_statement
GO;
execute a stored procedure:
EXEC procedure_name;
88
CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;
execute the stored procedure "SelectAllCustomers":
EXEC SelectAllCustomers;
This statement creates a stored procedure named "SelectAllCustomers" that selects all records from the "Customers" table. By writing "EXEC SelectAllCustomers" the stored procedure will be executed.
89
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;
to execute stored procedure:
EXEC SelectAllCustomers @City = 'London', @PostalCode = 'WA1 1DP';
The first statement creates a stored procedure that selects Customers from a particular city with a particular PostalCode from the "Customers" table. The parameters @City and @PostalCode and their datatypes are separated by a comma. To execute the stored procedure the actual parameters "London" and "WA 1DP" have to be passed in the EXEC clause.
90
How are not to be executed comments symbolized in SQL?
Single line comments start with --
Any text between -- and the end of the line will be ignored (will not be executed).
Examples:
--Select all:
SELECT * FROM Customers;
SELECT * FROM Customers -- WHERE City='Berlin';
Multi-line comments start with /* and end with */
Any text between /* and */ will be ignored:
/*Select all the columns
of all the records
in the Customers table:*/
SELECT * FROM Customers;
Note: These comments symbols will not work in Firefox and Microsoft Edge, because Firefox and Microsoft Edge are using Microsoft Access databases!
91
What are the arithmetic operators in SQL?
```
+ Add
- Subtract
* Multiply
/ Divide
% Modulo (SELECT 17 % 5; ---> Result: 2)
```
92
What are the bitwise operators in SQL?
& Bitwise AND
| Bitwise OR
^ Bitwise exclusive OR
93
What are the compound operators in SQL?
```
+= Add equals
-= Subtract equals
*= Multiply equals
/= Divide equals
%= Modulo equals
&= Bitwise AND equals
^-= Bitwise exclusive equals
|*= Bitwise OR equals
```
94
What are the logical operators in SQL?
ALL TRUE if all of the subquery values meet the condition
AND TRUE if all the conditions separated by AND is TRUE
ANY TRUE if any of the subquery values meet the condition
BETWEEN TRUE if the operand is within the range of comparisons
EXISTS TRUE if the subquery returns one or more records
IN TRUE if the operand is equal to one of a list of expressions
LIKE TRUE if the operand matches a pattern
NOT Displays a record if the condition(s) is NOT TRUE
OR TRUE if any of the conditions separated by OR is TRUE
SOME TRUE if any of the subquery values meet the condition
95
How to create a new database?
The CREATE DATABASE statement is used to create a new SQL database.
Syntax:
CREATE DATABASE databasename;
Once a database is created, you can check it in the list of databases with the following SQL command: SHOW DATABASES;
96
How to delete a database?
The DROP DATABASE statement is used to drop an existing SQL database.
Syntax:
DROP DATABASE databasename;
Note: Be careful before dropping a database. Deleting a database will result in loss of complete information stored in the database!
97
How to create a full back up of an existing SQL database?
| And what is a differential back up?
BACKUP DATABASE databasename
TO DISK = 'filepath';
example:
BACKUP DATABASE testDB
TO DISK = 'D:\backups\testDB.bak';
The BACKUP DATABASE statement is used in SQL Server to create a full back up of an existing SQL database. Tip: Always back up the database to a different drive than the actual database. Then, if you get a disk crash, you will not lose your backup file along with the database. A differential back up only backs up the parts of the database that have changed since the last full database backup. Tip: A differential back up reduces the back up time (since only the changes are backed up). Syntax:
BACKUP DATABASE databasename
TO DISK = 'filepath'
WITH DIFFERENTIAL;
98
How to create a new table in a database?
```
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);
```
example:
```
CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
```
The LastName, FirstName, Address, and City columns are of type varchar and will hold characters, and the maximum length for these fields is 255 characters.
99
How to crate a copy from an existing table?
CREATE TABLE new_table_name AS
SELECT column1, column2,...
FROM existing_table_name
WHERE ....;
The new table gets the same column definitions. All columns or specific columns can be selected. If you create a new table using an existing table, the new table will be filled with the existing values from the old table.
100
How to delete a table?
DROP TABLE table_name;
Note: Be careful before dropping a table. Deleting a table will result in loss of complete information stored in the table!
101
How to delete the values inside a table but not the table itself?
TRUNCATE TABLE table_name;
102
How to add, delete or modify columns in an existing table?
To add a column in a table:
ALTER TABLE table_name
ADD column_name datatype;
To delete a column in a table:
ALTER TABLE table_name
DROP COLUMN column_name;
To modify the data type of a columns in a table:
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
Note: The ALTER TABLE statement is also used to add and drop various constraints on an existing table.
103
What are constraints and how can they be applied?
SQL constraints are used to specify rules for data in a table. Constraints can be specified when the table is created with the CREATE TABLE statement, or after the table is created with the ALTER TABLE statement.
```
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
....
);
```
104
What are the benefits of constraints and what types are commonly used in SQL?
```
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
....
);
```
SQL constraints are used to specify rules for the data in a table. Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted. Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table.
The following constraints are commonly used in SQL:
NOT NULL - Ensures that a column cannot have a NULL value
UNIQUE - Ensures that all values in a column are different
PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
FOREIGN KEY - Prevents actions that would destroy links between tables
CHECK - Ensures that the values in a column satisfies a specific condition
DEFAULT - Sets a default value for a column if no value is specified
CREATE INDEX - Used to create and retrieve data from the database very quickly
105
What is the NOT NULL constraint for?
```
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);
```
By default, a column can hold NULL values.
The NOT NULL constraint enforces a column to NOT accept NULL values.
This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.
This statement ensures that the "ID", "LastName", and "FirstName" columns will NOT accept NULL values when the "Persons" table is created.
106
How to create a NOT NULL constraint on a column when the table already exists?
ALTER TABLE table_name
MODIFY column_name NOT NULL;
example:
ALTER TABLE Persons
MODIFY Age int NOT NULL;
107
What is the UNIQUE constriant for?
The UNIQUE constraint ensures that all values in a column are different.
Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.
A PRIMARY KEY constraint automatically has a UNIQUE constraint.
However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
108
```
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
UNIQUE (ID)
);
```
This constraint creates a UNIQUE constraint on the "ID" column when the "Persons" table is created.
109
```
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT UC_Person UNIQUE (ID,LastName)
);
```
This statement is used to name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns.
110
How to create a UNIQUE constraint on a (multiple) column when the table is already created?
```
ALTER TABLE table_name
ADD UNIQUE (column_name);
```
example:
```
ALTER TABLE Persons
ADD UNIQUE (ID);
```
example on multiple columns:
ALTER TABLE Persons
ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);
111
How to drop a UNIQUE constraint?
ALTER TABLE table_name
DROP UNIQUE column_name;
example:
ALTER TABLE Persons
DROP UNIQUE UC_Person;
112
What is a PRIMARY KEY ?
The PRIMARY KEY constraint uniquely identifies each record in a table.
Primary keys must contain UNIQUE values, and cannot contain NULL values.
A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).
113
```
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);
```
This statement creates a PRIMARY KEY in the "ID" column when the "Persons" table is created.
114
With wich syntax can you allow naming of a PRIMARY KEY constraint, and with wich syntax can you define a PRIMARY KEY constrain on multiple columns,?
```
CREATE TABLE table_name (
column_name1,
column_name2,
column_name3,
CONSTRAINT PK_name PRIMARY KEY (column_name1, column_name2)
);
```
example:
```
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);
```
Note: In the example above there is only ONE PRIMARY KEY (PK_Person). However, the VALUE of the primary key is made up of TWO COLUMNS (ID + LastName).
115
How to create a PRIMARY KEY constraint on a column when the table already exists? And for multiple columns when the the table already exists?
ALTER TABLE table_name
ADD PRIMARY KEY (colmn_name);
for multiple columns:
ALTER TABLE table_name
ADD CONSTRAINT PK_name PRIMARY KEY (column_name1,column_name2);
Note: If you use ALTER TABLE to add a primary key, the primary key column(s) must have been declared to not contain NULL values (when the table was first created).
116
How to delete a PRIMARY KEY constraint?
ALTER TABLE table_name
| DROP PRIMARY KEY;
117
What is a FOREIGN KEY?
The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.
A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table.
The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.
The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the parent table.
118
How can you create a FOREIGN KEY on a column during the creation of a table?
```
CREATE TABLE table_name1 (
column_name1,
column_name2,
column_name3,
PRIMARY KEY (column_name1),
FOREIGN KEY (column_name3) REFERENCES table_name2(column_name4)
);
```
119
How to name a FOREIGN KEY constraint, and how to define a FOREIGN KEY constraint on multiple columns?
```
CREATE TABLE table_name1 (
column_name1,
column_name2,
column_name3,
PRIMARY KEY (column_name1),
CONSTRAINT FK_name FOREIGN KEY (column_name2, column_name3)
REFERENCES table_name2(column_name4)
);
```
120
How to create a FOREIGN KEY constraint on a column when the table already exists? And how to name a FOREIGN KEY constraint and how to define a FOREIGN KEY constraint on multiple columns?
ALTER TABLE table_name1
ADD FOREIGN KEY (column_name1) REFERENCES table_name2(column_name2);
on multiple columns:
ALTER TABLE table_name1
ADD CONSTRAINT FK_name
FOREIGN KEY (column_name1, column_name2) REFERENCES table_name2(column_name3);
121
How to delete a FOREIGN KEY constraint?
ALTER TABLE table_name
| DROP FOREIGN KEY FK_name;
