Section 1
Preview this deck
SELECT * FROM Customers ORDER BY Country;
Front
| 5 | 0 | ||
| 4 | 0 | ||
| 3 | 0 | ||
| 2 | 0 | ||
| 1 | 0 |
Active users
0
All-time users
0
Favorites
0
Last updated
6 years ago
Date created
Mar 1, 2020
Cards (293)
Section 1
(50 cards)
SELECT * FROM Customers ORDER BY Country;
The following SQL statement selects all customers from the "Customers" table, sorted by the "Country" column:
selects the "CustomerName" and "City" columns from the "Customers" table
SELECT CustomerName, City FROM Customers;
SELECT * FROM Customers WHERE Country='Germany' AND ROWNUM <= 3;
The following SQL statement shows the equivalent example using ROWNUM
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES; Did you notice that we did not insert any number into the CustomerID field? The CustomerID column is an auto-increment field and will be generated automatically when a new record is inserted into the table.
The following SQL statement inserts a new record in the "Customers" table:
AND Syntax
SELECT column1, column2, ... FROM table_name WHERE condition1 AND condition2 AND condition3 ...;
IN
To specify multiple possible values for a column
UPDATE Customers SET ContactName='Juan';
Update Warning! Be careful when updating records. If you omit the WHERE clause, ALL records will be updated!
DELETE FROM table_name; or: DELETE * FROM table_name;
It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:
SELECT * FROM Customers WHERE NOT Country='Germany';
The following SQL statement selects all fields from "Customers" where country is NOT "Germany":
DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';
The following SQL statement deletes the customer "Alfreds Futterkiste" from the "Customers" table:
SELECT * FROM Customers WHERE ROWNUM <= 3;
The following SQL statement shows the equivalent example using ROWNUM:
INSERT INTO Customers (CustomerName, City, Country) VALUES
It is also possible to only insert data in specific columns. The following SQL statement will insert a new record, but only insert data in the "CustomerName", "City", and "Country" columns (CustomerID will be updated automatically):
UPDATE Customers SET ContactName='Juan' WHERE Country='Mexico';
It is the WHERE clause that determines how many records that will be updated. The following SQL statement will update the contactname to "Juan" for all records where country is "Mexico":
INSERT INTO table_name VALUES (value1, value2, value3, ...);
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. The INSERT INTO syntax would be as follows:
SELECT * FROM Customers WHERE Country='Germany' AND City='Berlin';
The following SQL statement selects all fields from "Customers" where country is "Germany" AND city is "Berlin":
The SELECT DISTINCT statement is used to return only distinct (different) values.
SELECT DISTINCT column1, column2, ... FROM table_name;
SELECT * FROM Customers WHERE NOT Country='Germany' AND NOT Country='USA';
The following SQL statement selects all fields from "Customers" where country is NOT "Germany" and NOT "USA":
BETWEEN
Between an inclusive range
SELECT TOP 50 PERCENT * FROM Customers;
SQL TOP PERCENT Example The following SQL statement selects the first 50% of the records from the "Customers" table:
SELECT * FROM Customers WHERE City='Berlin' OR City='München';
The following SQL statement selects all fields from "Customers" where city is "Berlin" OR "München":
SELECT column_names FROM table_name WHERE column_name IS NOT NULL;
IS NOT NULL Syntax
OR Syntax
SELECT column1, column2, ... FROM table_name WHERE condition1 OR condition2 OR condition3 ...;
SELECT * FROM Customers ORDER BY Country DESC;
The following SQL statement selects all customers from the "Customers" table, sorted DESCENDING by the "Country" column:
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
The INSERT INTO statement is used to insert new records in a table. INSERT INTO Syntax It is possible to write the INSERT INTO statement in two ways. The first way specifies both the column names and the values to be inserted:
SELECT TOP number|percent column_name(s) FROM table_name WHERE condition;
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 on performance. Note: Not all database systems support the SELECT TOP clause. MySQL supports the LIMIT clause to select a limited number of records, while Oracle uses ROWNUM. Uses both SQL Server / MS Access Syntax:
SELECT TOP 3 * FROM Customers;
The following SQL statement selects the first three records from the "Customers" table:
SELECT * FROM Customers WHERE Country='Germany' LIMIT 3;
The following SQL statement shows the equivalent example using the LIMIT clause:
List all customers within database.
SELECT * FROM Customers;
SELECT TOP 3 * FROM Customers WHERE Country='Germany';
ADD a WHERE CLAUSE The following SQL statement selects the first three records from the "Customers" table, where the country is "Germany":
SELECT * FROM Customers WHERE CustomerID=1;
SQL requires single quotes around text values (most database systems will also allow double quotes). However, numeric fields should not be enclosed in quotes:
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; Note: 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!
The UPDATE statement is used to modify the existing records in a table. UPDATE Syntax
SELECT column_names FROM table_name WHERE column_name IS NULL;
IS NULL Syntax
lists the number of different (distinct) customer countries:
SELECT COUNT(DISTINCT Country) FROM Customers;
UPDATE Customers SET ContactName = 'Alfred Schmidt', City= 'Frankfurt' WHERE CustomerID = 1;
UPDATE Table The following SQL statement updates the first customer (CustomerID = 1) with a new contact person and a new city.
SELECT * FROM Customers WHERE Country='Germany' AND (City='Berlin' OR City='München');
You can also combine the AND, OR and NOT operators. The following SQL statement selects all fields from "Customers" where country is "Germany" AND city must be "Berlin" OR "München" (use parenthesis to form complex expressions):
LIKE
Search for a pattern
SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;
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.
DELETE FROM table_name WHERE condition; Note: Be careful when deleting records in a table! Notice the WHERE clause in the DELETE statement. The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted!
The SQL DELETE Statement The DELETE statement is used to delete existing records in a table. DELETE Syntax
NOT Syntax
SELECT column1, column2, ... FROM table_name WHERE NOT condition;
SELECT * FROM Customers LIMIT 3;
The following SQL statement shows the equivalent example using the LIMIT clause:
SELECT * FROM Customers WHERE Country='Mexico';
WHERE Clause Example The following SQL statement selects all the customers from the country "Mexico", in the "Customers" table:
SELECT LastName, FirstName, Address FROM Persons WHERE Address IS NULL;
The following SQL statement uses the IS NULL operator to list all persons that have no address:
SELECT MIN(column_name) FROM table_name WHERE condition;
The MIN() function returns the smallest value of the selected column. MIN() Syntax
SELECT LastName, FirstName, Address FROM Persons WHERE Address IS NOT NULL;
The following SQL statement uses the IS NOT NULL operator to list all persons that do have an address:
Note: The example above will not work in Firefox and Microsoft Edge! Because COUNT(DISTINCT column_name) is not supported in Microsoft Access databases. Firefox and Microsoft Edge are using Microsoft Access in our examples. Here is the workaround for MS Access:
SELECT Count(*) AS DistinctCountries FROM (SELECT DISTINCT Country FROM Customers);
The following SQL statement selects all (and duplicate) values from the "Country" column in the "Customers" table:
SELECT Country FROM Customers;
SELECT column1, column2, ... FROM table_name WHERE condition;
WHERE Syntax Note: The WHERE clause is not only used in SELECT statement, it is also used in UPDATE, DELETE statement, etc.!
selects only the DISTINCT values from the "Country" column in the "Customers" table:
SELECT DISTINCT Country FROM Customers;
SELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC;
The following SQL statement selects all customers from the "Customers" table, sorted ascending by the "Country" and descending by the "CustomerName"
SELECT * FROM Customers ORDER BY Country, CustomerName;
The following SQL statement selects all customers from the "Customers" table, sorted by the "Country" and the "CustomerName" column:
Section 2
(50 cards)
SELECT * FROM Customers WHERE ContactName LIKE 'a%o';
The following SQL statement selects all customers with a ContactName that starts with "a" and ends with "o":
SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK');
IN Operator Examples The following SQL statement selects all customers that are located in "Germany", "France" and "UK":
SELECT * FROM Customers WHERE CustomerName LIKE '_r%';
The following SQL statement selects all customers with a CustomerName that have "r" in the second position:
SELECT * FROM Customers WHERE City LIKE '[a-c]%'
The following SQL statement selects all customers with a City starting with "a", "b", or "c":
SELECT COUNT(column_name) FROM table_name WHERE condition;
COUNT() Syntax
SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS Address FROM Customers;
The following SQL statement creates an alias named "Address" that combine four columns (Address, PostalCode, City and Country):
SELECT CustomerName AS Customer, ContactName AS [Contact Person] FROM Customers;
The following SQL statement creates two aliases, one for the CustomerName column and one for the ContactName column. Note: It requires double quotation marks or square brackets if the alias name contains spaces:
SELECT * FROM Customers WHERE CustomerName NOT LIKE 'a%';
The following SQL statement selects all customers with a CustomerName that does NOT start with "a":
SELECT * FROM Customers WHERE CustomerName LIKE 'a%';
SQL LIKE Examples The following SQL statement selects all customers with a CustomerName starting with "a":
SELECT * FROM Customers WHERE City LIKE '%es%'
The following SQL statement selects all customers with a City containing the pattern "es":
SELECT * FROM Customers WHERE Country NOT IN ('Germany', 'France', 'UK');
The following SQL statement selects all customers that are NOT located in "Germany", "France" or "UK":
SELECT SUM(Quantity) FROM OrderDetails;
SUM() Example The following SQL statement finds the sum of the "Quantity" fields in the "OrderDetails" table: Example
SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName FROM Customers, Orders WHERE Customers.CustomerName="Around the Horn" AND Customers.CustomerID=Orders.CustomerID;
The following SQL statement is the same as above, but without aliases:
•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
Aliases can be useful when:
SELECT * FROM Customers WHERE CustomerName LIKE '%or%';
The following SQL statement selects all customers with a CustomerName that have "or" in any position:
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 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):
SELECT column_name AS alias_name FROM table_name;
SQL 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 the query. Alias Column Syntax
SELECT * FROM Customers WHERE City LIKE '[bsp]%'
Using the [charlist] Wildcard The following SQL statement selects all customers with a City starting with "b", "s", or "p":
SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;
The following SQL statement selects all products with a price BETWEEN 10 and 20:
SELECT * FROM Orders WHERE OrderDate BETWEEN #01/07/1996# AND #31/07/1996#; OR SELECT * FROM Orders WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';
BETWEEN Dates Example The following SQL statement selects all orders with an OrderDate BETWEEN '01-July-1996' and '31-July-1996':
SELECT * FROM Products WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni' ORDER BY ProductName;
BETWEEN Text Values Example The following SQL statement selects all products with a ProductName BETWEEN 'Carnarvon Tigers' and 'Mozzarella di Giovanni':
SELECT SUM(column_name) FROM table_name WHERE condition;
SUM() Syntax
•% - The percent sign represents zero, one, or multiple characters •_ - The underscore represents a single character
A wildcard character is used to substitute any other character(s) in a string. Wildcard characters are used with the SQL LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards used in conjunction with the LIKE operator:
SELECT CustomerName, CONCAT(Address,', ',PostalCode,', ',City,', ',Country) AS Address FROM Customers;
To get the SQL statement above to work in MySQL use the following:
SELECT * FROM Customers WHERE City LIKE 'L_n_on'
The following SQL statement selects all customers with a City starting with "L", followed by any character, followed by "n", followed by any character, followed by "on":
SELECT CustomerID AS ID, CustomerName AS Customer FROM Customers;
The following SQL statement creates two aliases, one for the CustomerID column and one for the CustomerName column:
SELECT column1, column2, ... FROM table_name WHERE columnN LIKE pattern;
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards used in conjunction with the LIKE operator: •% - The percent sign represents zero, one, or multiple characters •_ - The underscore represents a single character Note: MS Access uses a question mark (?) instead of the underscore (_). The percent sign and the underscore can also be used in combinations! LIKE Syntax
The COUNT() function returns the number of rows that matches a specified criteria. The AVG() function returns the average value of a numeric column. The SUM() function returns the total sum of a numeric column.
The SQL COUNT(), AVG() and SUM() Functions
SELECT * FROM Customers WHERE City LIKE 'ber%'
The following SQL statement selects all customers with a City starting with "ber":
SELECT MAX(Price) AS LargestPrice FROM Products;
The following SQL statement finds the price of the most expensive product:
SELECT * FROM Customers WHERE CustomerName LIKE 'a_%_%';
The following SQL statement selects all customers with a CustomerName that starts with "a" and are at least 3 characters in length:
SELECT * FROM Customers WHERE City LIKE '_erlin'
Using the _ Wildcard The following SQL statement selects all customers with a City starting with any character, followed by "erlin":
SELECT * FROM Customers WHERE Country IN (SELECT Country FROM Suppliers);
The following SQL statement selects all customers that are from the same countries as the suppliers:
SELECT MAX(column_name) FROM table_name WHERE condition;
The MAX() function returns the largest value of the selected column
SELECT MIN(Price) AS SmallestPrice FROM Products;
The following SQL statement finds the price of the cheapest product:
(INNER) JOIN: Returns records that have matching values in both tables •LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table •RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table •FULL (OUTER) JOIN: Return all records when there is a match in either left or right table
Here are the different types of the JOINs in SQL:
SELECT * FROM Customers WHERE CustomerName LIKE '%a';
The following SQL statement selects all customers with a CustomerName ending with "a":
IN Syntax SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...); OR SELECT column_name(s) FROM table_name WHERE column_name IN (SELECT STATEMENT);
The SQL IN Operator The IN operator allows you to specify multiple values in a WHERE clause. The IN operator is a shorthand for multiple OR conditions.
SELECT column_name(s) FROM table_name AS alias_name;
Alias Table Syntax
SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 20;
NOT BETWEEN Example To display the products outside the range of the previous example, use NOT BETWEEN:
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
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. BETWEEN Syntax
SELECT AVG(column_name) FROM table_name WHERE condition;
AVG() Syntax
•[charlist] - Defines sets and ranges of characters to match •[^charlist] or [!charlist] - Defines sets and ranges of characters NOT to match
In MS Access and SQL Server you can also use:
SELECT COUNT(ProductID) FROM Products;
COUNT() Example The following SQL statement finds the number of products: Example
SELECT AVG(Price) FROM Products;
AVG() Example The following SQL statement finds the average price of all products:
SELECT * FROM Customers WHERE City LIKE '[!bsp]%' OR SELECT * FROM Customers WHERE City NOT LIKE '[bsp]%'
Using the [!charlist] Wildcard The two following SQL statements select all customers with a City NOT starting with "b", "s", or "p":
LIKE Operator Description WHERE CustomerName LIKE 'a%' Finds any values that start with "a" WHERE CustomerName LIKE '%a' Finds any values that end with "a" WHERE CustomerName LIKE '%or%' Finds any values that have "or" in any position WHERE CustomerName LIKE '_r%' Finds any values that have "r" in the second position WHERE CustomerName LIKE 'a_%_%' Finds any values that start with "a" and are at least 3 characters in length WHERE ContactName LIKE 'a%o' Finds any values that start with "a" and ends with "o"
Tip: You can also combine any number of conditions using AND or OR operators. Here are some examples showing different LIKE operators with '%' and '_' wildcards:
SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
The INNER JOIN keyword selects records that have matching values in both tables. INNER JOIN Syntax
SELECT * FROM Products WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni' ORDER BY ProductName;
NOT BETWEEN Text Values Example The following SQL statement selects all products with a ProductName NOT BETWEEN 'Carnarvon Tigers' and 'Mozzarella di Giovanni':
SELECT * FROM Products WHERE (Price BETWEEN 10 AND 20) AND NOT CategoryID IN (1,2,3);
BETWEEN with IN Example The following SQL statement selects all products with a price BETWEEN 10 and 20. In addition; do not show products with a CategoryID of 1,2, or 3:
Section 3
(50 cards)
SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;
The SQL UNION Operator The UNION operator is used to combine the result-set of two or more SELECT statements. Each SELECT statement within UNION must have the same number of columns The columns must also have similar data types The columns in each SELECT statement must also be in the same order UNION Syntax
SELECT City, Country FROM Customers WHERE Country='Germany' UNION SELECT City, Country FROM Suppliers WHERE Country='Germany' ORDER BY City;
SQL UNION With WHERE The following SQL statement returns the German cities (only distinct values) from both the "Customers" and the "Suppliers" table:
SELECT City FROM Customers UNION ALL SELECT City FROM Suppliers ORDER BY City;
SQL UNION ALL Example The following SQL statement returns the cities (duplicate values also) from both the "Customers" and the "Suppliers" table:
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;
The following SQL statement lists if the employees "Davolio" or "Fuller" have registered more than 25 orders:
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country ORDER BY COUNT(CustomerID) DESC;
The following SQL statement lists the number of customers in each country, sorted high to low:
SELECT Customers.CustomerName, Orders.OrderID INTO CustomersOrderBackup2017 FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
The following SQL statement copies data from more than one table into a new table:
SELECT 'Customer' As Type, ContactName, City, Country FROM Customers UNION SELECT 'Supplier', ContactName, City, Country FROM Suppliers;
Another UNION Example The following SQL statement lists all customers and suppliers:
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 in the UNION.
UNION ALL Syntax The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:
SELECT Orders.OrderID, Employees.LastName, Employees.FirstName FROM Orders RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID ORDER BY Orders.OrderID; Note: The RIGHT JOIN keyword returns all records from the right table (Employees), even if there are no matches in the left table (Orders).
SQL RIGHT JOIN Example The following SQL statement will return all employees, and any orders they might have placed:
SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE SupplierId = Suppliers.supplierId AND Price = 22);
The following SQL statement returns TRUE and lists the suppliers with a product price equal to 22:
SELECT City FROM Customers UNION SELECT City FROM Suppliers ORDER BY City;
SQL UNION Example The following SQL statement returns the cities (only distinct values) from both the "Customers" and the "Suppliers" table:
SELECT * INTO CustomersBackup2017 FROM Customers;
SQL SELECT INTO Examples The following SQL statement creates a backup copy of Customers:
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);
JOIN Three Tables The following SQL statement selects all orders with customer and shipper information:
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);
The SQL HAVING Clause The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. HAVING Syntax
INSERT INTO Customers (CustomerName, City, Country) SELECT SupplierName, City, Country FROM Suppliers WHERE Country='Germany';
The following SQL statement copies only the German suppliers into "Customers":
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN ELSE result END;
CASE Syntax
SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;
SQL FULL OUTER JOIN Keyword The FULL OUTER JOIN keyword return all records when there is a match in either left (table1) or right (table2) table records. Note: FULL OUTER JOIN can potentially return very large result-sets! FULL OUTER JOIN Syntax
SELECT * INTO newtable [IN externaldb] FROM oldtable WHERE condition;
The SELECT INTO statement copies data from one table into a new table. SELECT INTO Syntax Copy all columns into a new table:
SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID GROUP BY ShipperName;
GROUP BY With JOIN Example The following SQL statement lists the number of orders sent by each shipper:
INSERT INTO Customers (CustomerName, City, Country) SELECT SupplierName, City, Country FROM Suppliers;
SQL INSERT INTO SELECT Examples The following SQL statement copies "Suppliers" into "Customers" (the columns that are not filled with data, will contain NULL):
SELECT column_name(s) FROM table_name WHERE column_name operator ANY (SELECT column_name FROM table_name WHERE condition);
The SQL ANY and ALL Operators The ANY and ALL operators are used with a WHERE or HAVING clause. The ANY operator returns true if any of the subquery values meet the condition. The ALL operator returns true if all of the subquery values meet the condition. ANY Syntax
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;
SQL Self JOIN Example The following SQL statement matches customers that are from the same city:
SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name; Note: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.
SQL RIGHT JOIN Keyword The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match. RIGHT JOIN Syntax
SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);
SQL ANY Examples The ANY operator returns TRUE if any of the subquery values meet the condition. The following SQL statement returns TRUE and lists the productnames if it finds ANY records in the OrderDetails table that quantity = 10:
SELECT column_name(s) FROM table_name WHERE column_name operator ALL (SELECT column_name FROM table_name WHERE condition); Note: The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).
ALL Syntax
SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition;
SQL Self JOIN A self JOIN is a regular join, but the table is joined with itself. Self JOIN Syntax
SELECT * INTO CustomersGermany FROM Customers WHERE Country = 'Germany';
The following SQL statement copies only the German customers into a new table:
SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name; Note: In some databases LEFT JOIN is called LEFT OUTER JOIN.
SQL LEFT JOIN Keyword The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.
SELECT * INTO CustomersBackup2017 IN 'Backup.mdb' FROM Customers;
The following SQL statement uses the IN clause to copy the table into a new table in another database:
SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE SupplierId = Suppliers.supplierId AND Price < 20);
SQL EXISTS Examples The following SQL statement returns TRUE and lists the suppliers with a product price less than 20:
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) SELECT SupplierName, ContactName, Address, City, PostalCode, Country FROM Suppliers;
The following SQL statement copies "Suppliers" into "Customers" (fill all columns):
SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.CustomerName; Note: The LEFT JOIN keyword returns all records from the left table (Customers), even if there are no matches in the right table (Orders).
SQL LEFT JOIN Example The following SQL statement will select all customers, and any orders they might have:
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;
SQL GROUP BY Examples The following SQL statement lists the number of customers in each country:
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;
The following SQL statement lists the employees that have registered more than 10 orders:
The INSERT INTO SELECT statement copies data from one table and inserts it into another table. •INSERT INTO SELECT requires that data types in source and target tables match •The existing records in the target table are unaffected
The SQL INSERT INTO SELECT Statement
SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity > 99);
The following SQL statement returns TRUE and lists the productnames if it finds ANY records in the OrderDetails table that quantity > 99:
SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID; Note: The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are records in the "Orders" table that do not have matches in "Customers", these orders will not be shown!
SQL INNER JOIN Example The following SQL statement selects all orders with customer information
The CASE statement goes through conditions and return 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.
The SQL CASE Statement
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:
SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName; A selection from the result set may look like this: CustomerName OrderID Alfreds Futterkiste Ana Trujillo Emparedados y helados 10308 Antonio Moreno Taquería 10365 10382 10351 Note: The FULL OUTER JOIN keyword returns all the rows from the left table (Customers), and all the rows from the right table (Orders). 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.
SQL FULL OUTER JOIN Example The following SQL statement selects all customers, and all orders:
INSERT INTO table2 SELECT * FROM table1 WHERE condition;
INSERT INTO SELECT Syntax Copy all columns from one table to another table:
SELECT City, Country FROM Customers WHERE Country='Germany' UNION ALL SELECT City, Country FROM Suppliers WHERE Country='Germany' ORDER BY City;
SQL UNION ALL With WHERE The following SQL statement returns the German cities (duplicate values also) from both the "Customers" and the "Suppliers" table:
SELECT column1, column2, column3, ... INTO newtable [IN externaldb] FROM oldtable WHERE condition; 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.
Copy only some columns into a new table:
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s);
The SQL GROUP BY Statement 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. GROUP BY Syntax
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5 ORDER BY COUNT(CustomerID) DESC;
The following SQL statement lists the number of customers in each country, sorted high to low (Only include countries with more than 5 customers):
SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);
The SQL EXISTS Operator 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. EXISTS Syntax
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5;
The following SQL statement lists the number of customers in each country. Only include countries with more than 5 customers:
SELECT CustomerName, ContactName INTO CustomersBackup2017 FROM Customers;
The following SQL statement copies only a few columns into a new table:
SELECT ProductName FROM Products WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);
SQL ALL Example The ALL operator returns TRUE if all of the subquery values meet the condition. The following SQL statement returns TRUE and lists the productnames if ALL the records in the OrderDetails table has quantity = 10:
INSERT INTO table2 (column1, column2, column3, ...) SELECT column1, column2, column3, ... FROM table1 WHERE condition;
Copy only some columns from one table into another table:
Section 4
(50 cards)
CREATE PROCEDURE procedure_name AS sql_statement GO;
Stored Procedure Syntax
ALTER TABLE table_name MODIFY COLUMN column_name datatype;
My SQL / Oracle (prior version 10G):
CREATE DATABASE testDB; Tip: Make sure you have admin privilege before creating any database. Once a database is created, you can check it in the list of databases with the following SQL command: SHOW DATABASES;
CREATE DATABASE Example The following SQL statement creates a database called "testDB":
CREATE TABLE Persons ( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) );
SQL CREATE TABLE Example The following example creates a table called "Persons" that contains five columns: PersonID, LastName, FirstName, Address, and City:
EXEC SelectAllCustomers City = "London";
Execute the stored procedure above as follows:
SELECT CustomerName, City, Country FROM Customers ORDER BY (CASE WHEN City IS NULL THEN Country ELSE City END);
The following SQL will order the customers by City. However, if City is NULL, then order by Country:
ALTER TABLE table_name ADD column_name datatype;
SQL ALTER TABLE Statement The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. The ALTER TABLE statement is also used to add and drop various constraints on an existing table. ALTER TABLE - ADD Column To add a column in a table, use the following syntax:
CREATE DATABASE databasename;
The SQL CREATE DATABASE Statement The CREATE DATABASE statement is used to create a new SQL database. Syntax
SELECT CustomerName, /City,/ Country FROM Customers;
To ignore just a part of a statement, also use the / / comment. The following example uses a comment to ignore part of a line:
ALTER TABLE Persons DROP COLUMN DateOfBirth;
DROP COLUMN Example Next, we want to delete the column named "DateOfBirth" in the "Persons" table. We use the following SQL statement:
PersonID LastName FirstName Address City Tip: The empty "Persons" table can now be filled with data with the SQL INSERT INTO statement.
The PersonID column is of type int and will hold an integer. 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. The empty "Persons" table will now look like this:
CREATE PROCEDURE SelectAllCustomers AS SELECT * FROM Customers GO;
Stored Procedure Example The following SQL statement creates a stored procedure named "SelectAllCustomers" that selects all records from the "Customers" table:
ALTER TABLE Customers ADD Email varchar(255);
The following SQL adds an "Email" column to the "Customers" table:
SELECT ProductName, UnitPrice * (UnitsInStock + NVL(UnitsOnOrder, 0)) FROM Products
Oracle The Oracle NVL() function achieves the same result:
BACKUP DATABASE testDB TO DISK = 'D:\backups\testDB.bak'; Tip: Always back up the database to a different drive than the actual database. If you get a disk crash, you will not lose your backup file along with the database.
BACKUP DATABASE Example The following SQL statement creates a full back up of the existing database "testDB" to the D disk:
CREATE TABLE TestTable AS SELECT customername, contactname FROM customers;
The following SQL creates a new table called "TestTables" (which is a copy of the "Customers" table):
ALTER TABLE Customers DROP COLUMN Email;
The following SQL deletes the "Email" column from the "Customers" table:
ALTER TABLE Persons ADD DateOfBirth date; Notice that the new column, "DateOfBirth", is of type date and is going to hold a date. The data type specifies what type of data the column can hold. For a complete reference of all the data types available in MS Access, MySQL, and SQL Server, go to our complete Data Types reference.
Now we want to add a column named "DateOfBirth" in the "Persons" table. We use the following SQL statement:
CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, .... ); The column parameters specify the names of the columns of the table. The datatype parameter specifies the type of data the column can hold (e.g. varchar, integer, date, etc.). Tip: For an overview of the available data types, go to our complete Data Types Reference.
The SQL CREATE TABLE Statement The CREATE TABLE statement is used to create a new table in a 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!
The SQL DROP DATABASE Statement The DROP DATABASE statement is used to drop an existing SQL database. Syntax
EXEC SelectAllCustomers City = "London", PostalCode = "WA1 1DP";
Execute the stored procedure above as follows:
--SELECT * FROM Customers; SELECT * FROM Products;
The following example uses a single-line comment to ignore a statement:
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!
The SQL DROP TABLE Statement The DROP TABLE statement is used to drop an existing table in a database. Syntax
SELECT ProductName, UnitPrice * (UnitsInStock + ISNULL(UnitsOnOrder, 0)) FROM Products
SQL Server The SQL Server ISNULL() function lets you return an alternative value when an expression is NULL:
SELECT * FROM Customers -- WHERE City='Berlin';
The following example uses a single-line comment to ignore the end of a line:
CREATE TABLE table_name ( column1 datatype constraint, column2 datatype constraint, column3 datatype constraint, .... );
SQL constraints are used to specify rules for data in a table. SQL Create Constraints 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.
ALTER TABLE table_name ALTER COLUMN column_name datatype;
ALTER TABLE - ALTER/MODIFY COLUMN To change the data type of a column in a table, use the following syntax: SQL Server / MS Access:
BACKUP DATABASE databasename TO DISK = 'filepath' WITH DIFFERENTIAL;
The SQL BACKUP WITH DIFFERENTIAL Statement A differential back up only backs up the parts of the database that have changed since the last full database backup. Syntax
/*Select all the columns of all the records in the Customers table:*/ SELECT * FROM Customers;
Multi-line Comments Multi-line comments start with / and end with /. Any text between / and / will be ignored. The following example uses a multi-line comment as an explanation:
ALTER TABLE table_name MODIFY column_name datatype;
Oracle 10G and later:
BACKUP DATABASE testDB TO DISK = 'D:\backups\testDB.bak' WITH DIFFERENTIAL; Tip: A differential back up reduces the back up time (since only the changes are backed up).
BACKUP WITH DIFFERENTIAL Example The following SQL statement creates a differential back up of the database "testDB":
BACKUP DATABASE databasename TO DISK = 'filepath';
The SQL BACKUP DATABASE Statement The BACKUP DATABASE statement is used in SQL Server to create a full back up of an existing SQL database. Syntax
EXEC procedure_name;
Execute a Stored Procedure
SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0)) FROM Products or we can use the COALESCE() function, like this: SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0)) FROM Products
Solutions MySQL The MySQL IFNULL() function lets you return an alternative value if an expression is NULL:
DROP TABLE Shippers;
SQL DROP TABLE Example The following SQL statement drops the existing table "Shippers":
--Select all: SELECT * FROM Customers;
SQL Comments Comments are used to explain sections of SQL statements, or to prevent execution of SQL statements. Note: The examples in this chapter will not work in Firefox and Microsoft Edge! Comments are not supported in Microsoft Access databases. Firefox and Microsoft Edge are using Microsoft Access database in our examples. Single Line Comments Single line comments start with --. Any text between -- and the end of the line will be ignored (will not be executed). The following example uses a single-line comment as an explanation:
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30) AS SELECT * FROM Customers WHERE City = @City GO;
Stored Procedure With One Parameter The following SQL statement creates a stored procedure that selects Customers from a particular City from the "Customers" table:
EXEC SelectAllCustomers;
Execute the stored procedure above as follows:
ALTER TABLE Persons ALTER COLUMN DateOfBirth year; Notice that the "DateOfBirth" column is now of type year and is going to hold a year in a two- or four-digit format.
Change Data Type Example Now we want to change the data type of the column named "DateOfBirth" in the "Persons" table. We use the following SQL statement:
TRUNCATE TABLE table_name;
SQL TRUNCATE TABLE The TRUNCATE TABLE statement is used to delete the data inside a table, but not the table itself. Syntax
SELECT * FROM Customers WHERE (CustomerName LIKE 'L%' OR CustomerName LIKE 'R%' /*OR CustomerName LIKE 'S%' OR CustomerName LIKE 'T%'*/ OR CustomerName LIKE 'W%') AND Country='USA' ORDER BY CustomerName;
The following example uses a comment to ignore part of a statement:
/SELECT FROM Customers; SELECT * FROM Products; SELECT * FROM Orders; SELECT FROM Categories;/ SELECT * FROM Suppliers;
The following example uses a multi-line comment to ignore many statements:
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10) AS SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode GO;
Stored Procedure With Multiple Parameters Setting up multiple parameters is very easy. Just list each parameter and the data type separated by a comma as shown below. The following SQL statement creates a stored procedure that selects Customers from a particular City with a particular PostalCode from the "Customers" table:
A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it. 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.
What is a Stored Procedure?
DROP DATABASE testDB; Tip: Make sure you have admin privilege before dropping any database. Once a database is dropped, you can check it in the list of databases with the following SQL command: SHOW DATABASES;
DROP DATABASE Example The following SQL statement drops the existing database "testDB":
Look at the following SELECT statement: SELECT ProductName, UnitPrice * (UnitsInStock + UnitsOnOrder) FROM Products; In the example above, if any of the "UnitsOnOrder" values are NULL, the result will be NULL.
SQL IFNULL(), ISNULL(), COALESCE(), and NVL() Functions Look at the following "Products" table: P_Id ProductName UnitPrice UnitsInStock UnitsOnOrder 1 Jarlsberg 10.45 16 15 2 Mascarpone 32.56 23 3 Gorgonzola 15.67 9 20 Suppose that the "UnitsOnOrder" column is optional, and may contain NULL values. Look at the following SELECT statement:
CREATE TABLE new_table_name AS SELECT column1, column2,... FROM existing_table_name WHERE ....;
Create Table Using Another Table A copy of an existing table can also be created using CREATE TABLE. 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. Syntax
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE - DROP COLUMN To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column):
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;
SQL CASE Examples The following SQL goes through conditions and returns a value when the first condition is met:
SELECT ProductName, UnitPrice * (UnitsInStock + IIF(IsNull(UnitsOnOrder), 0, UnitsOnOrder)) FROM Products
MS Access The MS Access IsNull() function returns TRUE (-1) if the expression is a null value, otherwise FALSE (0):
Section 5
(50 cards)
The CHECK constraint is used to limit the value range that can be placed in a column. If you define a CHECK constraint on a single column it allows only certain values for this column. If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.
SQL CHECK Constraint
ALTER TABLE Persons MODIFY City DEFAULT 'Sandnes';
Oracle:
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, City varchar(255), CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes') );
To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax: MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) );
SQL FOREIGN KEY on CREATE TABLE The following SQL creates a FOREIGN KEY on the "PersonID" column when the "Orders" table is created: MySQL:
CREATE TABLE Orders ( ID int NOT NULL, OrderNumber int NOT NULL, OrderDate date DEFAULT GETDATE() );
The DEFAULT constraint can also be used to insert system values, by using functions like GETDATE():
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255) NOT NULL, Age int );
SQL NOT NULL on CREATE TABLE The following SQL ensures that the "ID", "LastName", and "FirstName" columns will NOT accept NULL values when the "Persons" table is created:
CREATE INDEX index_name ON table_name (column1, column2, ...);
CREATE INDEX Syntax Creates an index on a table. Duplicate values are allowed
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.
SQL NOT NULL 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.
SQL Constraints
ALTER TABLE Persons DROP PRIMARY KEY;
DROP a PRIMARY KEY Constraint To drop a PRIMARY KEY constraint, use the following SQL: MySQL:
ALTER TABLE Persons ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');
To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax: MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT
SQL Server / Oracle / MS Access:
ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName); Note: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must already have been declared to not contain NULL values (when the table was first created).
To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax: MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, City varchar(255) DEFAULT 'Sandnes' );
SQL DEFAULT on CREATE TABLE The following SQL sets a DEFAULT value for the "City" column when the "Persons" table is created: My SQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons ALTER City DROP DEFAULT;
DROP a DEFAULT Constraint To drop a DEFAULT constraint, use the following SQL: MySQL:
ALTER TABLE Persons MODIFY Age int NOT NULL;
SQL NOT NULL on ALTER TABLE To create a NOT NULL constraint on the "Age" column when the "Persons" table is already created, use the following SQL:
CREATE INDEX idx_lastname ON Persons (LastName);
CREATE INDEX Example The SQL statement below creates an index named "idx_lastname" on the "LastName" column in the "Persons" table:
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).
To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax: MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Orders ( OrderID int NOT NULL PRIMARY KEY, OrderNumber int NOT NULL, PersonID int FOREIGN KEY REFERENCES Persons(PersonID) );
SQL Server / Oracle / MS Access:
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, which may consist of single or multiple fields.
SQL PRIMARY KEY Constraint
ALTER TABLE Orders ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
SQL FOREIGN KEY on ALTER TABLE To create a FOREIGN KEY constraint on the "PersonID" column when the "Orders" table is already created, use the following SQL: MySQL / SQL Server / Oracle / MS Access:
MySQL: ALTER TABLE Persons DROP INDEX UC_Person; SQL Server / Oracle / MS Access: ALTER TABLE Persons DROP CONSTRAINT UC_Person;
DROP a UNIQUE Constraint To drop a UNIQUE constraint, use the following SQL: MySQL:
MySQL: CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, PRIMARY KEY (ID) ); SQL Server / Oracle / MS Access: CREATE TABLE Persons ( ID int NOT NULL PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int );
SQL PRIMARY KEY on CREATE TABLE The following SQL creates a PRIMARY KEY on the "ID" column when the "Persons" table is created: MySQL:
CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...); Note: The syntax for creating indexes varies among different databases. Therefore: Check the syntax for creating indexes in your database.
CREATE UNIQUE INDEX Syntax Creates a unique index on a table. Duplicate values are not allowed
CREATE TABLE Persons ( ID int NOT NULL UNIQUE, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int );
SQL UNIQUE Constraint on CREATE TABLE The following SQL creates a UNIQUE constraint on the "ID" column when the "Persons" table is created: SQL Server / Oracle / MS Access:
ALTER TABLE Persons ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);
To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the following SQL syntax: MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) );
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax: MySQL / SQL Server / Oracle / MS Access:
The DEFAULT constraint is used to provide a default value for a column. The default value will be added to all new records IF no other value is specified.
SQL DEFAULT Constraint
DROP INDEX index_name ON table_name;
DROP INDEX Statement The DROP INDEX statement is used to delete an index in a table. MS Access:
ALTER TABLE Orders DROP CONSTRAINT FK_PersonOrder;
SQL Server / Oracle / MS Access:
ALTER TABLE Persons DROP CHECK CHK_PersonAge;
MySQL:
ALTER TABLE Persons ADD PRIMARY KEY (ID);
SQL PRIMARY KEY on ALTER TABLE To create a PRIMARY KEY constraint on the "ID" column when the table is already created, use the following SQL: MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons ALTER City SET DEFAULT 'Sandnes';
SQL DEFAULT on ALTER TABLE To create a DEFAULT constraint on the "City" column when the table is already created, use the following SQL: MySQL:
ALTER TABLE Persons DROP CONSTRAINT PK_Person;
SQL Server / Oracle / MS Access:
ALTER TABLE Persons DROP CONSTRAINT CHK_PersonAge;
DROP a CHECK Constraint To drop a CHECK constraint, use the following SQL: SQL Server / Oracle / MS Access:
A FOREIGN KEY is a key used to link two tables together. A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table. The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table. The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables. The FOREIGN KEY constraint also prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.
SQL FOREIGN KEY Constraint
ALTER TABLE Persons ADD UNIQUE (ID);
SQL UNIQUE Constraint on ALTER TABLE To create a UNIQUE constraint on the "ID" column when the table is already created, use the following SQL: MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons ADD CHECK (Age>=18);
SQL CHECK on ALTER TABLE To create a CHECK constraint on the "Age" column when the table is already created, use the following SQL: MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons ALTER COLUMN City SET DEFAULT 'Sandnes';
MS Access:
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 - Uniquely identifies a row/record in another table CHECK - Ensures that all values in a column satisfies a specific condition DEFAULT - Sets a default value for a column when no value is specified INDEX - Used to create and retrieve data from the database very quickly
The following constraints are commonly used in SQL: NOT NULL - UNIQUE - PRIMARY KEY - FOREIGN KEY - CHECK - DEFAULT - INDEX -
The CREATE INDEX statement is used to create indexes in tables. Indexes are used to retrieve data from the database very fast. The users cannot see the indexes, they are just used to speed up searches/queries. Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So, only create indexes on columns that will be frequently searched against.
SQL CREATE INDEX Statement
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int CHECK (Age>=18) );
SQL Server / Oracle / MS Access:
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, UNIQUE (ID) );
MySQL:
ALTER TABLE Orders DROP FOREIGN KEY FK_PersonOrder;
DROP a FOREIGN KEY Constraint To drop a FOREIGN KEY constraint, use the following SQL: MySQL:
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CHECK (Age>=18) );
SQL CHECK on CREATE TABLE The following SQL creates a CHECK constraint on the "Age" column when the "Persons" table is created. The CHECK constraint ensures that you can not have any person below 18 years: MySQL:
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.
SQL UNIQUE Constraint
ALTER TABLE Persons ADD CONSTRAINT df_City DEFAULT 'Sandnes' FOR City;
SQL Server:
ALTER TABLE Orders ADD CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax: MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CONSTRAINT UC_Person UNIQUE (ID,LastName) );
To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the following SQL syntax: MySQL / SQL Server / Oracle / MS Access:
CREATE INDEX idx_pname ON Persons (LastName, FirstName);
If you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas:
Section 6
(43 cards)