SQL queries
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.
*CHECK on CREATE TABLE:* creates a CHECK constraint on the "Age" column when the "Persons" table is created to ensure that you can not have any person below 18 years: MySQL: 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 CHECK (Age>=18) ); To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns: 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), CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes') ); *CHECK on ALTER TABLE:* to create a CHECK constraint on the "Age" column when the table is already created: MySQL / SQL Server / Oracle / MS Access: ALTER TABLE Persons ADD CHECK (Age>=18); to allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns: MySQL / SQL Server / Oracle / MS Access: ALTER TABLE Persons ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes'); *DROP a CHECK Constraint:* SQL Server / Oracle / MS Access: ALTER TABLE Persons DROP CONSTRAINT CHK_PersonAge; MySQL: ALTER TABLE Persons DROP CHECK CHK_PersonAge;
VIEWS: In SQL, a view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table. NOTE: A view always shows up-to-date data! The database engine recreates the data, using the view's SQL statement, every time a user queries a view.
*Creating a View:* CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition; eg.: creates a view that shows all customers from Brazil: CREATE VIEW [Brazil Customers] AS SELECT CustomerName, ContactName FROM Customers WHERE Country = "Brazil"; We can query the view above: SELECT * FROM [Brazil Customers]; creates a view that selects every product in the "Products" table with a price higher than the average price: CREATE VIEW [Products Above Average Price] AS SELECT ProductName, Price FROM Products WHERE Price > (SELECT AVG(Price) FROM Products); query the view above: SELECT * FROM [Products Above Average Price]; *Updating a View:* CREATE OR REPLACE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition; eg.: adds the "City" column to the "Brazil Customers" view: CREATE OR REPLACE VIEW [Brazil Customers] AS SELECT CustomerName, ContactName, City FROM Customers WHERE Country = "Brazil"; *SQL Dropping a View:* DROP VIEW view_name; eg.: drops the "Brazil Customers" view: DROP VIEW [Brazil Customers];
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.
*DEFAULT on CREATE TABLE:* sets a DEFAULT value for the "City" column when the "Persons" table is created: CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, City varchar(255) DEFAULT 'Sandnes' ); can also be used to insert system values, by using functions like GETDATE(): CREATE TABLE Orders ( ID int NOT NULL, OrderNumber int NOT NULL, OrderDate date DEFAULT GETDATE() ); *DEFAULT on ALTER TABLE:* to create a DEFAULT constraint on the "City" column when the table is already created: MySQL: ALTER TABLE Persons ALTER City SET DEFAULT 'Sandnes'; SQL Server: ALTER TABLE Persons ADD CONSTRAINT df_City DEFAULT 'Sandnes' FOR City; MS Access: ALTER TABLE Persons ALTER COLUMN City SET DEFAULT 'Sandnes'; Oracle: ALTER TABLE Persons MODIFY City DEFAULT 'Sandnes'; *DROP a DEFAULT Constraint:* MySQL: ALTER TABLE Persons ALTER City DROP DEFAULT; SQL Server / Oracle / MS Access: ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT;
A FOREIGN KEY is a key used to link two tables together. It 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.
*FOREIGN KEY on CREATE TABLE:* creates a FOREIGN KEY on the "PersonID" column when the "Orders" table is created: MySQL: CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) ); 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) ); to allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns: 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) ); *FOREIGN KEY on ALTER TABLE:* to create a FOREIGN KEY constraint on the "PersonID" column when the "Orders" table is already created: MySQL / SQL Server / Oracle / MS Access: ALTER TABLE Orders ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID); to allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns: MySQL / SQL Server / Oracle / MS Access: ALTER TABLE Orders ADD CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES Persons(PersonID); *DROP a FOREIGN KEY Constraint:* MySQL: ALTER TABLE Orders DROP FOREIGN KEY FK_PersonOrder; SQL Server / Oracle / MS Access: ALTER TABLE Orders DROP CONSTRAINT FK_PersonOrder;
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).
*PRIMARY KEY on CREATE TABLE:* creates a PRIMARY KEY on the "ID" column when the "Persons" table is created: 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 ); to allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns: MySQL / SQL Server / Oracle / MS Access: 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). *PRIMARY KEY on ALTER TABLE:* to create a PRIMARY KEY constraint on the "ID" column when the table is already created: MySQL / SQL Server / Oracle / MS Access: ALTER TABLE Persons ADD PRIMARY KEY (ID); 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: 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). *DROP a PRIMARY KEY Constraint:* MySQL: ALTER TABLE Persons DROP PRIMARY KEY; SQL Server / Oracle / MS Access: ALTER TABLE Persons DROP CONSTRAINT PK_Person;
Operators in the WHERE Clause
= 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 AND operator OR operator NOT operator
ALIASES Used to give a table, a column, a view or a subquery (almost any db object you can reference in a SELECT statement) a temporary name. Often used to make column names more readable. An alias only exists for the duration of the query. Note: Double quotes or square brackets are required if the alias name contains spaces. Aliases can be useful when: - There are more than one table involved in a query - Functions are used in the query - Column names are big or not very readable - Two or more columns are combined together
Alias column syntax: SELECT column_name AS alias_name FROM table_name; Alias table syntax: SELECT column_name(s) FROM table_name AS alias_name; eg.: SELECT CustomerID AS ID, CustomerName AS Customer FROM Customers; SELECT CustomerName AS Customer, ContactName AS [Contact Person] FROM Customers; creates an alias named "Address" that combine four columns (Address, PostalCode, City and Country): SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS Address FROM Customers; same IN MYSQL: SELECT CustomerName, CONCAT(Address,', ',PostalCode,', ',City,', ',Country) AS Address FROM Customers; Alias for Tables: 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 o.OrderID, o.OrderDate, c.CustomerName FROM Customers AS c, Orders AS o WHERE c.CustomerName="Around the Horn" AND c.CustomerID=o.CustomerID; same, without aliases: SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName FROM Customers, Orders WHERE Customers.CustomerName="Around the Horn" AND Customers.CustomerID=Orders.CustomerID;
backup database (in SQL server)
BACKUP DATABASE db_name TO DISK = 'filepath'; with differential (reduces backup time (since only the changes are backed up): BACKUP DATABASE db_name TO DISK = 'filepath' WITH DIFFERENTIAL; eg.: BACKUP DATABASE testDB TO DISK = 'D:\backups\testDB.bak'; BACKUP DATABASE testDB TO DISK = 'D:\backups\testDB.bak' WITH DIFFERENTIAL;
The CASE statement goes through conditions 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.
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN ELSE result END; eg.: goes through conditions and returns a value when the first condition is met: 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; will order the customers by City. However, if City is NULL, then order by Country: SELECT CustomerName, City, Country FROM Customers ORDER BY (CASE WHEN City IS NULL THEN Country ELSE City END);
create a database
CREATE SCHEMA db_name; CREATE DATABASE db_name;
SQL CONSTRAINTS: are used to specify rules for data in a table. They can be specified when the table is created with the CREATE TABLE statement, or after the table is created with the ALTER TABLE statement. 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. 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 - 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
CREATE TABLE table_name ( column1 datatype constraint, column2 datatype constraint, column3 datatype constraint, .... );
CREATE TABLE creates a new table in the database. It allows you to specify the name of the table and the name of each column in the table. Create Table Using Another Table: create a copy of an existing table. The new table gets the same column definitions; all columns or specific columns can be selected. The new table will be filled with the existing values from the old table
CREATE TABLE table_name ( column_1 datatype, column_2 datatype, column_3 datatype); CREATE TABLE new_table_name AS SELECT column1, column2,... FROM existing_table_name WHERE ....; eg.: creates new table: CREATE TABLE joke ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, joketext TEXT, jokedate DATE NOT NULL ) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB; creates a new table called "TestTables" (which is a copy of the "Customers" table): CREATE TABLE TestTable AS SELECT customername, contactname FROM customers;
The SELECT INTO statement copies data from one table 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.
Copy all columns into a new table: SELECT * INTO newtable [IN externaldb] FROM oldtable WHERE condition; Copy only some columns into a new table: SELECT column1, column2, column3, ... INTO newtable [IN externaldb] FROM oldtable WHERE condition; eg.: creates a backup copy of Customers: SELECT * INTO CustomersBackup2017 FROM Customers; uses the IN clause to copy the table into a new table in another database: SELECT * INTO CustomersBackup2017 IN 'Backup.mdb' FROM Customers; copies only a few columns into a new table: SELECT CustomerName, ContactName INTO CustomersBackup2017 FROM Customers; copies only the German customers into a new table: SELECT * INTO CustomersGermany FROM Customers WHERE Country = 'Germany'; copies data from more than one table into a new table: SELECT Customers.CustomerName, Orders.OrderID INTO CustomersOrderBackup2017 FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID; 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 * INTO newtable FROM oldtable WHERE 1 = 0;
A STORED PROCEDURE is a prepared SQL code that you can save, so the code can be reused over and over again. 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 it can act based on the parameter value(s) that is passed.
Create a stored procedure: CREATE PROCEDURE procedure_name AS sql_statement GO; Execute a stored procedure: EXEC procedure_name; eg.: creates a stored procedure named "SelectAllCustomers" that selects all records from the "Customers" table: CREATE PROCEDURE SelectAllCustomers AS SELECT * FROM Customers GO; execute it: EXEC SelectAllCustomers; With One Parameter: creates a stored procedure that selects Customers from a particular City from the "Customers" table: CREATE PROCEDURE SelectAllCustomers @City nvarchar(30) AS SELECT * FROM Customers WHERE City = @City GO; execute it: EXEC SelectAllCustomers City = "London"; With Multiple Parameters: creates a stored procedure that selects Customers from a particular City with a particular PostalCode from the "Customers" table: CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10) AS SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode GO; execute it: EXEC SelectAllCustomers City = "London", PostalCode = "WA1 1DP";
The CREATE INDEX statement is used to create indexes in tables. They are used to retrieve data from the database very fast. 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): only create indexes on columns that will be frequently searched against.
Creates an index on a table. Duplicate values are allowed: CREATE INDEX index_name ON table_name (column1, column2, ...); Creates a unique index on a table. Duplicate values are not allowed: 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. eg.: creates an index named "idx_lastname" on the "LastName" column in the "Persons" table: CREATE INDEX idx_lastname ON Persons (LastName); to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas: CREATE INDEX idx_pname ON Persons (LastName, FirstName); to delete an index in a table: MS Access: DROP INDEX index_name ON table_name; SQL Server: DROP INDEX table_name.index_name; DB2/Oracle: DROP INDEX index_name; MySQL: ALTER TABLE table_name DROP INDEX index_name;
DELETE statements are used to remove rows from a table. If you omit the WHERE clause, all records in the table will be deleted! 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.
DELETE FROM table_name WHERE condition; Delete All Records: DELETE FROM table_name;
drop database
DROP DATABASE db_name;
DROP TABLE deletes an existing table along with the data in it
DROP TABLE table_name;
NULL FUNCTIONS: PROBLEM: If a column is optional, it may contain NULL values. That means, if any values are NULL, the result of a SELECT statement will be NULL. SOLUTIONS: MySQL: The IFNULL() function lets you return an alternative value if an expression is NULL: 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; SQL server: The ISNULL() function lets you return an alternative value when an expression is NULL: SELECT ProductName, UnitPrice * (UnitsInStock + ISNULL(UnitsOnOrder, 0)) FROM Products; MS Access: The MS Access IsNull() function returns TRUE (-1) if the expression is a null value, otherwise FALSE (0): SELECT ProductName, UnitPrice * (UnitsInStock + IIF(IsNull(UnitsOnOrder), 0, UnitsOnOrder)) FROM Products; Oracle: The Oracle NVL() function achieves the same result: SELECT ProductName, UnitPrice * (UnitsInStock + NVL(UnitsOnOrder, 0)) FROM Products;
IFNULL(expression, alt_value); Return the specified value IF the expression is NULL, otherwise return the expression: SELECT IFNULL(NULL, "W3Schools.com"); COALESCE(): returns the first non-null value in a list: SELECT COALESCE(NULL, NULL, NULL, 'W3Schools.com', NULL, 'Example.com'); ...
INSERT INTO statement is used to add new rows of data to a table in the database. (2) If you are adding values for all the columns, column names don't need to be specified. However, make sure the order of the values is in the same order as the columns.
INSERT INTO tableName (column1, column2, ...) VALUES ('value1', 'value2', ...); INSERT INTO table_name VALUES ('value1', 'value2', 'value3', ...); eg. INSERT INTO teams ( conference , id, name ) VALUES ( 'F' , 9 , 'Riff Raff' ) ; works without column names as well (if they match): INSERT INTO teams VALUES ( 9 , 'Riff Raff' , 'F' ) ; works with multiple rows as well: INSERT INTO teams ( conference , id, name ) VALUES ( 'F' , 9 , 'Riff Raff' ) , ( 'F' , 37 , 'Havoc' ) , ( 'C' , 63 , 'Brewers' ) ;
INSERT INTO statement is used to add new rows of data to a table in the database. (1)
INSERT INTO tableName SET column1 = value1, column2 = value2, ...
SQL TOP, LIMIT or ROWNUM Clause: The SELECT TOP clause is used to specify the maximum number of records to return. Useful on large tables with thousands of records. Returning a large number of records can impact performance. Not all database systems support the SELECT TOP clause. MySQL: LIMIT; Oracle: ROWNUM.
MySQL Syntax: SELECT column_name(s) FROM table_name WHERE condition LIMIT number; SQL Server / MS Access Syntax: SELECT TOP number|percent column_name(s) FROM table_name WHERE condition; Oracle Syntax: SELECT column_name(s) FROM table_name WHERE ROWNUM <= number;
AUTO INCREMENT allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
MySQL: defines the "Personid" column to be an auto-increment primary key field in the "Persons" table: CREATE TABLE Persons ( Personid int NOT NULL AUTO_INCREMENT, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, PRIMARY KEY (Personid) ); In MySQL, by default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record. To let the AUTO_INCREMENT sequence start with another value: ALTER TABLE Persons AUTO_INCREMENT=100; To insert a new record into the "Persons" table, we will NOT have to specify a value for the "Personid" column (a unique value will be added automatically): INSERT INTO Persons (FirstName,LastName) VALUES ('Lars','Monsen'); SQL Server: defines the "Personid" column to be an auto-increment primary key field in the "Persons" table: CREATE TABLE Persons ( Personid int IDENTITY(1,1) PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int ); NOTE: The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature. In the eg. above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record. Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change it to IDENTITY(10,5). MS Access: defines the "Personid" column to be an auto-increment primary key field in the "Persons" table: CREATE TABLE Persons ( Personid AUTOINCREMENT PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int ); NOTE: MS Access uses the AUTOINCREMENT keyword to perform an auto-increment feature. By default, the starting value for AUTOINCREMENT is 1, and it will increment by 1 for each new record. Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change the autoincrement to AUTOINCREMENT(10,5). Oracle: Trickier: you will have to create an auto-increment field with the sequence object (this object generates a number sequence): CREATE SEQUENCE seq_person MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10; The code above creates a sequence object called seq_person, that starts with 1 and will increment by 1. It will also cache up to 10 values for performance. The cache option specifies how many sequence values will be stored in memory for faster access. To insert a new record into the "Persons" table, we will have to use the nextval function (this function retrieves the next value from seq_person sequence): INSERT INTO Persons (Personid,FirstName,LastName) VALUES (seq_person.nextval,'Lars','Monsen');
NOT NULL Constraint: 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.
NOT NULL on CREATE TABLE: ensures that the "ID", "LastName", and "FirstName" columns will NOT accept NULL values when the "Persons" table is created: CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255) NOT NULL, Age int ); NOT NULL on ALTER TABLE: To create a NOT NULL constraint on the "Age" column when the "Persons" table is already created: ALTER TABLE Persons MODIFY Age int NOT NULL;
SUBQUERY: a query that's subordinate to / nested within another query. DERIVED TABLE / INLINE VIEW: a common type of subquery. It must be given a name (AS). The tabular structure produced by the subquery is used as the source of data for the FROM clause of the main query.
SELECT title , category_name FROM ( SELECT entries.title , entries.created , categories.name AS category_name FROM entries INNER JOIN categories ON categories.category = entries.category ) AS entries_with_category ;
Combining AND, OR and NOT
SELECT * FROM Customers WHERE Country='Germany' AND (City='Berlin' OR City='München'); SELECT * FROM Customers WHERE NOT Country='Germany' AND NOT Country='USA';
SELECT statements are used to fetch data from a database. Every query will begin with SELECT.
SELECT * FROM table_name; SELECT column_name FROM table_name; SELECT column1, column2, ... FROM table_name;
AVG() is an aggregate function that returns the average value for a numeric column. NULL values are ignored.
SELECT AVG(column_name) FROM table_name WHERE condition;
COUNT() is a function that takes the name of a column as an argument and counts the number of rows where the column is not NULL.
SELECT COUNT(column_name) FROM table_name WHERE condition;
The SELECT DISTINCT statement is used to return only distinct (unique) values.
SELECT DISTINCT column_name FROM table_name; SELECT DISTINCT column1, column2, ... FROM table_name; SELECT COUNT(DISTINCT Country) FROM Customers; OR: SELECT Count(*) AS DistinctCountries FROM (SELECT DISTINCT Country FROM Customers);
The MIN() function returns the smallest value of the selected column. The MAX() function returns the largest value of the selected column.
SELECT MIN(column_name) FROM table_name WHERE condition; SELECT MAX(column_name) FROM table_name WHERE condition; eg.: SELECT MIN(Price) AS SmallestPrice FROM Products; SELECT MAX(Price) AS HighestPrice FROM Products;
ROUND() is a function that takes a column name and an integer as an argument. It rounds the values in the column to the number of decimal places specified by the integer.
SELECT ROUND(column_name, integer) FROM table_name;
SUM() is a function that takes the name of a column as an argument and returns the sum of all the values in that column. NULL values are ignored.
SELECT SUM(column_name) FROM table_name WHERE condition;
LEFT function: to display eg. only the first 20 characters of a string
SELECT column1, LEFT(column2, 20), column3 FROM table_name
The WHERE clause is used to filter records: to extract only those records that fulfill a specified condition. (Text values in single quotes, numeric values with no quotes.)
SELECT column1, column2 FROM table_name WHERE condition; SELECT * FROM Customers WHERE Country='Mexico';
ORDER BY is a clause used to sort the result set by a particular column either alphabetically or numerically. It sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword. ORDER BY Several Columns: it orders by Country, but if some rows have the same Country, it orders them by CustomerName.
SELECT column_name FROM table_name ORDER BY column_name DESC; SELECT * FROM Customers ORDER BY Country DESC; SELECT * FROM Customers ORDER BY Country, CustomerName;
The FULL OUTER JOIN keyword return all records when there is a match in left (table1) or right (table2) table records. Note: FULL OUTER JOIN can potentially return very large result-sets! Tip: FULL OUTER JOIN and FULL JOIN are the same. not supported in MySQL: use a UNION instead
SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition; eg.: selects all customers, and all orders: SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName; not supported in MySQL: use a UNION of a LEFT OUTER JOIN and a RIGHT OUTER JOIN instead. UNION removes the duplicate matching rows automatically, UNION ALL keeps them
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 column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name; eg.: select all customers, and any orders they might have: SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.CustomerName;
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.
SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name; eg.: return all employees, and any orders they might have placed: SELECT Orders.OrderID, Employees.LastName, Employees.FirstName FROM Orders RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID ORDER BY Orders.OrderID;
A self JOIN is a regular join, but the table is joined with itself.
SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition; NOTE: T1 and T2 are different table aliases for the same table. eg.: matches customers that are from the same city: 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;
An INNER JOIN will combine rows from different tables if the join condition is true: selects records that have matching values in both tables. If there are records in table_1 that do not have matches in table_2, these orders will not be shown.
SELECT column_name(s) FROM table_1 INNER JOIN table_2 ON table_1.column_name = table_2.column_name; eg.: JOIN Two Tables: selects all orders with customer information: SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID; JOIN Three Tables: selects all orders with customer and shipper information: 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 EXISTS operator is used to test for the existence of any record in a subquery. It returns true if the subquery returns one or more records.
SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition); eg.: returns TRUE and lists the suppliers with a product price less than 20: SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20); returns TRUE and lists the suppliers with a product price equal to 22: SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price = 22);
The NOT operator displays a record if the condition(s) is NOT TRUE.
SELECT column_name(s) FROM table_name WHERE NOT condition;
AND is an operator that combines two conditions. Both conditions must be true for the row to be included in the result set.
SELECT column_name(s) FROM table_name WHERE column_1 = value_1 AND column_2 = value_2;
OR is an operator that filters the result set to only include rows where either condition is true.
SELECT column_name(s) FROM table_name WHERE column_name = value_1 OR column_name = value_2;
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. It is inclusive: begin and end values are included.
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2; eg.: SELECT * FROM Products WHERE Price BETWEEN 10 AND 20; SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 20; selects all products with a price BETWEEN 10 and 20. In addition; do not show products with a CategoryID of 1,2, or 3: SELECT * FROM Products WHERE Price BETWEEN 10 AND 20 AND NOT CategoryID IN (1,2,3); BETWEEN Text Values Example: selects all products with a ProductName BETWEEN Carnarvon Tigers and Mozzarella di Giovanni: SELECT * FROM Products WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni' ORDER BY ProductName; selects all products with a ProductName NOT BETWEEN Carnarvon Tigers and Mozzarella di Giovanni: SELECT * FROM Products WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni' ORDER BY ProductName; BETWEEN Dates Example: selects all orders with an OrderDate BETWEEN '01-July-1996' and '31-July-1996': SELECT * FROM Orders WHERE OrderDate BETWEEN #01/07/1996# AND #31/07/1996#;
The IN operator allows you to specify multiple values in a WHERE clause. It is a shorthand for multiple OR conditions.
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); eg.: SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK'); selects all customers that are located in "Germany", "France" and "UK" SELECT * FROM Customers WHERE Country NOT IN ('Germany', 'France', 'UK'); selects all customers that are NOT located in "Germany", "France" or "UK" SELECT * FROM Customers WHERE Country IN (SELECT Country FROM Suppliers); selects all customers that are from the same countries as the suppliers
LIKE is a special operator used with the WHERE clause to search for a specific pattern in a column, eg. search for entries that contain a certain piece of text There are two wildcards often 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 an asterisk (*) instead of the percent sign (%), and a question mark (?) instead of the underscore (_). You can also combine any number of conditions using AND or OR operators.
SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern; eg. SELECT joketext FROM jokes WHERE joketext LIKE %programmer% WHERE CustomerName LIKE 'a%' - any values that start with "a" WHERE CustomerName LIKE '%a' - any values that end with "a" WHERE CustomerName LIKE '%or%' - any values that have "or" in any position WHERE CustomerName LIKE '_r%' - any values that have "r" in the second position WHERE CustomerName LIKE 'a__%' - any values that start with "a" and are at least 3 characters in length WHERE ContactName LIKE 'a%o' - any values that start with "a" and ends with "o" WHERE CustomerName NOT LIKE 'a%'; - any value that does NOT start with "a"
The ANY and ALL operators are used with a WHERE or HAVING clause. ANY: returns true if any of the subquery values meet the condition. ALL: returns true if all of the subquery values meet the condition. NOTE: The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).
SELECT column_name(s) FROM table_name WHERE column_name operator ANY (SELECT column_name FROM table_name WHERE condition); SELECT column_name(s) FROM table_name WHERE column_name operator ALL (SELECT column_name FROM table_name WHERE condition); ANY eg.: returns TRUE and lists the product names if it finds ANY records in the OrderDetails table that quantity = 10: SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10); returns TRUE and lists the product names if it finds ANY records in the OrderDetails table that quantity > 99: SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity > 99); ALL eg.: returns TRUE and lists the product names if ALL the records in the OrderDetails table has quantity = 10: SELECT ProductName FROM Products WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s); eg.: lists the number of customers in each country. Only include countries with more than 5 customers: SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5; lists the number of customers in each country, sorted high to low (Only include countries with more than 5 customers): SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5 ORDER BY COUNT(CustomerID) DESC; lists the employees that have registered more than 10 orders: 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; lists if the employees "Davolio" or "Fuller" have registered more than 25 orders: 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 GROUP BY clause is similar to the ORDER BY clause, but often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns. On their own, the aggregate functions will only return a single value. However, you can view the results of an aggregate function performed on every matching value in a column by including a GROUP BY clause: it groups rows that have the same values into summary rows, like "find the number of customers in each country".
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s); eg.: lists the number of customers in each country: SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country; lists the number of customers in each country, sorted high to low: SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country ORDER BY COUNT(CustomerID) DESC; GROUP BY With JOIN eg.: lists the number of orders sent by each shipper: SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID GROUP BY ShipperName; count the number of matching values in column_2 and group them in ascending or alphabetical order: SELECT COUNT(column_1), column_2 FROM table GROUP BY column_2; same as above but groups the results in descending or reverse alphabetical order: SELECT COUNT(column_1), column_2 FROM table GROUP BY column_2 DESC;
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. It is impossible to test for NULL values with comparison operators, such as =, <, or <>. Use the IS NULL and IS NOT NULL operators instead.
SELECT column_names FROM table_name WHERE column_name IS NULL; SELECT column_names FROM table_name WHERE column_name IS NOT NULL;
check a list of databases
SHOW DATABASES;
A 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 Server / Oracle / MS Access: creates a UNIQUE constraint on the "ID" column when the "Persons" table is created: CREATE TABLE Persons ( ID int NOT NULL UNIQUE, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int ); MySQL: CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, UNIQUE (ID) ); to name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns: 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) ); SQL UNIQUE Constraint on ALTER TABLE: to create a UNIQUE constraint on the "ID" column when the table is already created: MySQL / SQL Server / Oracle / MS Access: ALTER TABLE Persons ADD UNIQUE (ID); to name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns: MySQL / SQL Server / Oracle / MS Access: ALTER TABLE Persons ADD CONSTRAINT UC_Person UNIQUE (ID,LastName); DROP a UNIQUE Constraint: MySQL: ALTER TABLE Persons DROP INDEX UC_Person; SQL Server / Oracle / MS Access: ALTER TABLE Persons DROP CONSTRAINT UC_Person;
SQL Dates: The most difficult part when working with dates is to be sure that the format of the date you are trying to insert, matches the format of the date column in the database. As long as your data contains only the date portion, your queries will work as expected. However, if a time portion is involved, it gets more complicated. SQL Date Data Types MySQL comes with the following data types for storing a date or a date/time value in the database: DATE - format YYYY-MM-DD DATETIME - format: YYYY-MM-DD HH:MI:SS TIMESTAMP - format: YYYY-MM-DD HH:MI:SS YEAR - format YYYY or YY SQL Server comes with the following data types for storing a date or a date/time value in the database: DATE - format YYYY-MM-DD DATETIME - format: YYYY-MM-DD HH:MI:SS SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS TIMESTAMP - format: a unique number NOTE: The date types are chosen for a column when you create a new table in your database!
SQL Working with Dates You can compare two dates easily if there is no time component involved! SELECT * FROM Orders WHERE OrderDate='2008-11-11' Tip: To keep your queries simple and easy to maintain, do not allow time components in your dates!
COMMENTS: used to explain sections of SQL statements, or to prevent execution of SQL statements
Single line comments start with --. Any text between -- and the end of the line will be ignored (will not be executed). Also used mid-line to ignore the bit that follows. Multi-line comments start with /* and end with */.
TRUNCATE TABLE is used to delete data from the table but not the table
TRUNCATE TABLE table_name;
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 Note: The column names in the result-set are usually equal to the column names in the first SELECT statement in the UNION.
UNION: selects only distinct values by default. SELECT column_name(s) FROM table1 UNION (same as line 1 but with table2); UNION ALL: to allow duplicate values. SELECT column_name(s) FROM table1 UNION ALL (same as line 1 but with table2);
UPDATE statements allow you to edit rows in a table. If you omit the WHERE clause, all records in the table will be updated! UPDATE multiple records: it is the WHERE clause that determines how many records will be updated.
UPDATE table_name SET column1 = 'value1', column2 = 'value2', ... WHERE condition; eg.: UPDATE teams SET conference = 'E' WHERE id = 9 ;
WILDCARD CHARACTERS A wildcard character is used to substitute one or more characters in a string, used with the SQL LIKE operator. All the wildcards can also be used in combinations!
Wildcard Characters in MS Access * zero or more characters: bl* finds bl, black, blue, and blob ? a single character: h?t finds hot, hat, and hit [] any single character within the brackets: h[oa]t finds hot and hat, but not hit ! any character not in the brackets: h[!oa]t finds hit, but not hot and hat - a range of characters: c[a-b]t finds cat and cbt # any single numeric character: 2#5 finds 205, 215, 225, 235, 245, 255, 265, 275, 285, and 295 Wildcard Characters in SQL Server % zero or more characters: bl% finds bl, black, blue, and blob _ a single character: h_t finds hot, hat, and hit [] any single character within the brackets: h[oa]t finds hot and hat, but not hit ^ any character not in the brackets: h[^oa]t finds hit, but not hot and hat - a range of characters c[a-b]t finds cat and cbt eg.: WHERE City LIKE 'ber%'; - finds all customers with a City starting with "ber" WHERE City LIKE '%es%'; - finds all customers with a City containing the pattern "es" WHERE City LIKE '_ondon'; - selects all customers with a City starting with any character, followed by "ondon" WHERE City LIKE 'L_n_on'; - selects all customers with a City starting with "L", followed by any character, followed by "n", followed by any character, followed by "on" WHERE City LIKE '[bsp]%'; - selects all customers with a City starting with "b", "s", or "p" WHERE City LIKE '[a-c]%'; - selects all customers with a City starting with "a", "b", or "c" WHERE City LIKE '[!bsp]%'; OR: WHERE City NOT LIKE '[bsp]%'; - select all customers with a City NOT starting with "b", "s", or "p"
ALTER TABLE: to add, delete, or modify columns in an existing table. Also used to add and drop various constraints on an existing table.
add a column: ALTER TABLE table_name ADD column datatype; delete a column (some database systems don't allow deleting a column): ALTER TABLE table_name DROP COLUMN column_name; modify a column: SQL Server / MS Access: ALTER TABLE table_name ALTER COLUMN column_name datatype; MySQL / Oracle (prior version 10G): ALTER TABLE table_name MODIFY COLUMN column_name datatype; Oracle 10G and later: ALTER TABLE table_name MODIFY column_name datatype; eg.: adds an "Email" column to the "Customers" table: ALTER TABLE Customers ADD Email varchar(255); deletes the "Email" column from the "Customers" table: ALTER TABLE Customers DROP COLUMN Email; changes the data type of the column named "DateOfBirth" in the "Persons" table: ALTER TABLE Persons ALTER COLUMN DateOfBirth year;
The INSERT INTO SELECT statement copies data from one table and inserts it into another table. It requires that data types in source and target tables match. The existing records in the target table are unaffected
copy all columns from one table to another table: INSERT INTO table2 SELECT * FROM table1 WHERE condition; copy only some columns from one table into another table: INSERT INTO table2 (column1, column2, column3, ...) SELECT column1, column2, column3, ... FROM table1 WHERE condition; eg.: copies "Suppliers" into "Customers" (the columns that are not filled with data, will contain NULL): INSERT INTO Customers (CustomerName, City, Country) SELECT SupplierName, City, Country FROM Suppliers; copies "Suppliers" into "Customers" (fill all columns): INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) SELECT SupplierName, ContactName, Address, City, PostalCode, Country FROM Suppliers; copies only the German suppliers into "Customers": INSERT INTO Customers (CustomerName, City, Country) SELECT SupplierName, City, Country FROM Suppliers WHERE Country='Germany';
A JOIN clause is used to combine rows from two or more tables, based on a related column between them. Types: - (INNER) JOIN: Returns records that have matching values in both tables - LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table - RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table - FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table
eg.: (inner join) SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID; NOTE: the "CustomerID" column in the "Orders" table refers to the "CustomerID" in the "Customers" table. The relationship between the two tables above is the "CustomerID" column. Then, we can create the following SQL statement (that contains an INNER JOIN), that selects records that have matching values in both tables.
