SQL
SQL Arithmetic Operators Modulo
%
To create our new table (Postgres)
-- Creating tables for PH-EmployeeDB CREATE TABLE departments ( dept_no VARCHAR(4) NOT NULL, dept_name VARCHAR(40) NOT NULL, PRIMARY KEY (dept_no), UNIQUE (dept_name) );
Single Line Comments
--Select all: SELECT * FROM Customers;
right outer join
A join that includes all of the rows from the second table in the query and only those records from the first table that match the join field in the second table.
What is a Stored Procedure?
A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. 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.
SQL PRIMARY KEY on ALTER TABLE
ALTER TABLE Persons ADD PRIMARY KEY (ID);
SQL UNIQUE Constraint on ALTER TABLE
ALTER TABLE Persons ADD UNIQUE (ID);
DROP a UNIQUE Constraint
ALTER TABLE Persons DROP INDEX UC_Person;
DROP a PRIMARY KEY Constraint
ALTER TABLE Persons DROP PRIMARY KEY;
AUTO INCREMENT Field
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.
Multi-line Comments
Begin with /*, end with */ Can span multiple lines: /* this is a multi-line comment */ Can begin and end on the same line: int area; /* calculated area */
CASE syntax
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN ELSE result END;
CREATE DATABASE Example
CREATE DATABASE testDB;
CREATE INDEX Syntax
CREATE INDEX index_name ON table_name (column1, column2, ...); The SQL statement below creates an index named "idx_lastname" on the "LastName" column in the "Persons" table: CREATE INDEX idx_lastname ON Persons (LastName);
Stored Procedure Syntax
CREATE PROCEDURE procedure_name AS sql_statement GO;
SQL PRIMARY KEY on CREATE TABLE
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, PRIMARY KEY (ID) );
SQL UNIQUE Constraint on CREATE TABLE
CREATE TABLE Persons ( ID int NOT NULL UNIQUE, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int );
SQL NOT NULL on CREATE TABLE
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255) NOT NULL, Age int );
SQL DEFAULT on CREATE TABLE
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, City varchar(255) DEFAULT 'Sandnes' );
The following SQL statement 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) );
CREATE UNIQUE INDEX Syntax
CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);
CREATE VIEW Syntax
CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition; The following SQL creates a view that shows all customers from Brazil: CREATE VIEW [Brazil Customers] AS SELECT CustomerName, ContactName FROM Customers WHERE Country = 'Brazil';
DELETE Syntax
DELETE FROM table_name WHERE condition;
To drop the table
DROP TABLE employees CASCADE; DROP TABLE employees tells Postgres that we want to remove the Employees table from the database completely. CASCADE; indicates that we also want to remove the connections to other tables in the database.
Operator in The WHERE Clause: To specify multiple possible values for a column
IN
INSERT INTO Insert Data Only in Specified Columns
INSERT INTO Customers (CustomerName, City, Country) VALUES ('Cardinal', 'Stavanger', 'Norway');
Insert Data Only in Specified Columns
INSERT INTO Customers (CustomerName, City, Country) VALUES ('Cardinal', 'Stavanger', 'Norway');
INSERT INTO SELECT Syntax
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;
SQL CREATE VIEW statement
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 statements and functions to a view and present the data as if the data were coming from one single table. A view is created with the CREATE VIEW statement.
inner join
Most common type of join; includes rows in the query only when the joined field matches records in both tables.
Multiple columns in a query 1 (SELECT)
Multiple columns that are chosen by the same SELECT command can be indented and grouped together. If you are requesting multiple data fields from a table, you need to include these columns in your SELECT command. Each column is separated by a comma.
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
The syntax of every SQL query
SELECT Columns you want to look at FROM Table where the columns are located WHERE To filter for certain information
ORDER BY Several Columns Example
SELECT * FROM Customers ORDER BY Country, CustomerName;
SQL NOT Keyword
SELECT * FROM Customers WHERE NOT Country='Germany';
WHERE Syntax: Text Fields vs. Numeric Fields
SELECT * FROM Customers WHERE CustomerID=1;
SQL statement selects all products with a price between 10 and 20:
SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;
SQL statement 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;
SQL SELECT INTO Example 1 The following SQL statement creates a backup copy of Customers:
SELECT * INTO CustomersBackup2017 FROM Customers;
SQL SELECT INTO Example 2 The following SQL statement uses the IN clause to copy the table into a new table in another database:
SELECT * INTO CustomersBackup2017 IN 'Backup.mdb' FROM Customers;
SQL SELECT INTO Example 4 The following SQL statement copies only the German customers into a new table:
SELECT * INTO CustomersGermany FROM Customers WHERE Country = 'Germany';
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;
Some of The Most Important SQL Commands
SELECT - extracts data from a database UPDATE - updates data in a database DELETE - deletes data from a database INSERT INTO - inserts new data into a database CREATE DATABASE - creates a new database ALTER DATABASE - modifies a database CREATE TABLE - creates a new table ALTER TABLE - modifies a table DROP TABLE - deletes a table CREATE INDEX - creates an index (search key) DROP INDEX - deletes an index
SQL ALL Example 3 The following SQL statement lists ALL the product names:
SELECT ALL ProductName FROM Products WHERE TRUE;
ALL Syntax With SELECT
SELECT ALL column_name(s) FROM table_name WHERE condition;
AVG() Syntax
SELECT AVG(column_name) FROM table_name WHERE condition;
SQL SELECT INTO Example 3 The following SQL statement copies only a few columns into a new table:
SELECT CustomerName, ContactName INTO CustomersBackup2017 FROM Customers;
SQL SELECT INTO Example 5 The following SQL statement copies data from more than one table into a new table:
SELECT Customers.CustomerName, Orders.OrderIDINTO CustomersOrderBackup2017FROM CustomersLEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
MAX syntax
SELECT MAX(column_name) FROM table_name WHERE condition;
SQL Constraints
SQL constraints are used to specify rules for the data in a table. If there is any violation between the constraint and the data action, the action is aborted by the constraint. Constraints can be specified when the table is created (inside the CREATE TABLE statement) or after the table is created (inside the ALTER TABLE statement). CREATE TABLE table_name ( column_name1 data_type(size) constraint_name, column_name2 data_type(size) constraint_name, column_name3 data_type(size) constraint_name, .... );
SQL Injection
SQL injection is a code injection technique that might destroy your database. SQL injection is one of the most common web hacking techniques. SQL injection is the placement of malicious code in SQL statements, via web page input.
The SQL ALL Operator
The ALL operator: returns a boolean value as a result returns TRUE if ALL of the subquery values meet the condition is used with SELECT, WHERE and HAVING statements ALL means that the condition will be true only if the operation is true for all values in the range.
The SQL CASE Statement
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.
SQL CHECK Constraint
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 column it will allow 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 CREATE INDEX Statement
The CREATE INDEX statement is used to create indexes in tables. Indexes are used to retrieve data from the database more quickly than otherwise. The users cannot see the indexes, they are just used to speed up searches/queries.
SQL DEFAULT Constraint
The DEFAULT constraint is used to set a default value for a column. The default value will be added to all new records, if no other value is specified.
The SQL DELETE Statement
The DELETE statement is used to delete rows in a table.
SQL FOREIGN KEY Constraint
The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables. A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table. The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.
The SQL GROUP BY Statement
The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.
The SQL INSERT INTO SELECT Statement
The INSERT INTO SELECT statement copies data from one table and inserts it into another table. The INSERT INTO SELECT statement requires that the data types in source and target tables match. Note: The existing records in the target table are unaffected. INSERT INTO SELECT Syntax
SQL SELECT INTO Statement
The SELECT INTO statement copies data from one table into a new table.
SQL UNION Operator
The UNION operator is used to combine the result-set of two or more SELECT statements. Every SELECT statement within UNION must have the same number of columns The columns must also have similar data types The columns in every SELECT statement must also be in the same order
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 the age of a person must be 18, or older: CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CHECK (Age>=18) );
SQL CASE Example
The following SQL 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;
ALTER TABLE - ALTER/MODIFY COLUMN
To change the data type of a column in a table, use the following syntax: ALTER TABLE table_name ALTER COLUMN column_name datatype;
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: ALTER TABLE Persons MODIFY Age int NOT NULL;
UPDATE Syntax
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
Multiple columns in a query 2 (WHERE)
Unlike the SELECT command that uses a comma to separate fields/variables/parameters, the WHERE command uses the AND statement to connect conditions. Also, you can use other connectors/operators such as OR and NOT.
The SQL UPDATE Statement
used to change values of existing rows
Not equal
<> or !=
SQL JOIN
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
What is a NULL Value?
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.
FULL OUTER JOIN
A join in which all rows from both tables will be included regardless of whether they match rows from the other table
left outer join
A join that includes all of the rows from the first table in the query and only those records from the second table that match the join field in the first table.
DROP COLUMN Example
ALTER TABLE Persons DROP COLUMN DateOfBirth;
ALTER TABLE - ADD Column
ALTER TABLE table_name ADD column_name datatype; ALTER TABLE Customers ADD Email varchar(255);
ALTER TABLE - DROP COLUMN
ALTER TABLE table_name DROP COLUMN column_name;
BACKUP DATABASE Example
BACKUP DATABASE testDB TO DISK = 'D:\backups\testDB.bak';
Operator in The WHERE Clause: Between a certain range
BETWEEN
SQL CREATE TABLE statement
CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, .... );
Change Data Type Example
Change Data Type Example ALTER TABLE Persons ALTER COLUMN DateOfBirth year;
SELECT INTO syntax
Copy all columns into a new table: SELECT column1, column2, column3, ... 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;
The SQL DROP DATABASE Statement
DROP DATABASE testDB;
The SQL DROP TABLE
DROP TABLE EMPLOYEE;
SQL ANY Example 2 The following SQL statement lists the ProductName if it finds ANY records in the OrderDetails table has Quantity larger than 99 (this will return TRUE because the Quantity column has some values larger than 99):
ELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity > 99);
SQL EXISTS Operator
EXISTS operator is used to test for the existence of any record in a subquery EXISTS operator 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);
INSERT INTO syntax
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
How to Test for NULL Values?
It is not possible to test for NULL values with comparison operators, such as =, <, or <>. We will have to use the IS NULL and IS NOT NULL operators instead.
Operator in The WHERE Clause: Search for a pattern
LIKE
SQL statement select all customers with a City NOT starting with "b", "s", or "p":
SELECT * FROM Customers WHERE City LIKE '[!bsp]%'; or SELECT * FROM Customers WHERE City NOT LIKE '[bsp]%'; Try it Yourself »
SQL statement selects all customers with a City starting with "a", "b", or "c":
SELECT * FROM Customers WHERE City LIKE '[a-c]%';
SQL statement selects all customers with a City starting with "b", "s", or "p":
SELECT * FROM Customers WHERE City LIKE '[bsp]%';
SQL statement selects all customers with a City starting with any character, followed by "ondon":
SELECT * FROM Customers WHERE City LIKE '_ondon';
The following SQL statement selects all customers with a City starting with "ber"
SELECT * FROM Customers WHERE City LIKE 'ber%';
SQL statement selects all customers that are located in "Germany", "France" or "UK":
SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK'); The IN operator is a shorthand for multiple OR conditions
The following SQL statement selects all customers that are from the same countries as the suppliers:
SELECT * FROM Customers WHERE Country IN (SELECT Country FROM Suppliers);
SQL statement selects all customers that are NOT located in "Germany", "France" or "UK":
SELECT * FROM Customers WHERE Country NOT IN ('Germany', 'France', 'UK');
SQL statement selects all orders with an OrderDate between '01-July-1996' and '31-July-1996':
SELECT * FROM Orders WHERE OrderDate BETWEEN #07/01/1996# AND #07/31/1996#; OR: SELECT * FROM Orders WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';
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:
SELECT * FROM Products WHERE Price BETWEEN 10 AND 20AND CategoryID NOT IN (1,2,3);
SQL HAVING Examples
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5;
SQL statement 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;
SQL Statement group by example
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country; COUNT(CustomerID) Country 3 Argentina
HAVING Examples 1: SQL statement lists the number of customers in each country, sorted high to low (Only include countries with more than 5 customers)
SELECT COUNT(CustomerID), CountryFROM CustomersGROUP BY CountryHAVING COUNT(CustomerID) > 5 ORDER BY COUNT(CustomerID) DESC;
lists the number of different (distinct) customer countries
SELECT COUNT(DISTINCT Country) FROM Customers;
COUNT() Syntax
SELECT COUNT(column_name) FROM table_name WHERE condition;
SQL UNION Example
SELECT City FROM Customers UNION SELECT City FROM Suppliers ORDER BY City; City Aachen Albuquerque Anchorage Ann Arbor
SQL statement creates two aliases, one for the CustomerID column and one for the CustomerName column:
SELECT CustomerID AS ID, CustomerName AS Customer FROM Customers;
SQL statement creates two aliases, one for the CustomerName column and one for the ContactName column
SELECT CustomerName AS Customer, ContactName AS [Contact Person] FROM Customers; Note: It requires double quotation marks or square brackets if the alias name contains spaces.
SQL statement creates an alias named "Address" that combine four columns (Address, PostalCode, City and Country):
SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS Address FROM Customers;
SELECT DISTINCT Syntax
SELECT DISTINCT column1, column2, ... FROM table_name;
HAVING Examples 2: SQL statement 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;
HAVING Examples 3: SQL statement 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;
MIN() Syntax
SELECT MIN(column_name) FROM table_name WHERE condition;
SQL JOIN Example
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
SQL ANY Example 1 The following SQL statement lists the ProductName if it finds ANY records in the OrderDetails table has Quantity equal to 10 (this will return TRUE because the Quantity column has some values of 10):
SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);
SUM() Syntax
SELECT SUM(column_name) FROM table_name WHERE condition;
SQL statement lists the number of orders sent by each shipper
SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM OrdersLEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID GROUP BY ShipperName;
SQL EXISTS Example
SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price = 22);
The SQL SELECT TOP Clause (SQL Server / MS Access Syntax)
SELECT TOP number | percent column_name(s) FROM table_name WHERE condition;
ORDER BY Syntax
SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC | DESC;
NOT syntax
SELECT column1, column2, ... FROM table_name WHERE NOT condition;
LIKE Syntax
SELECT column1, column2, ... FROM table_name WHERE columnN LIKE pattern;
AND Syntax
SELECT column1, column2, ... FROM table_name WHERE condition1 AND condition2 AND condition3 ...;
OR Syntax
SELECT column1, column2, ... FROM table_name WHERE condition1 OR condition2 OR condition3 ...;
SELECT Syntax
SELECT column1, column2, ... FROM table_name;
Alias Column Syntax
SELECT column_name AS alias_name FROM table_name;
Inner join syntax
SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
LEFT JOIN Syntax
SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name; Example: SELECT Customers.CustomerName, Orders.OrderIDFROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.CustomerName; There will be null value in OrderID
RIGHT JOIN Syntax
SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;
BETWEEN syntax
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.
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);
ALL Syntax With WHERE or HAVING
SELECT column_name(s) FROM table_name WHERE column_name operator ALL (SELECT column_name FROM table_name WHERE condition);
ANY Syntax
SELECT column_name(s) FROM table_name WHERE column_name operator ANY (SELECT column_name FROM table_name WHERE condition);
GROUP BY syntax
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s);
Alias Table Syntax
SELECT column_name(s) FROM table_name AS alias_name;
UNION syntax
SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;
IS NOT NULL Syntax
SELECT column_names FROM table_name WHERE column_name IS NOT NULL;
IS NULL Syntax
SELECT column_names FROM table_name WHERE column_name IS NULL;
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 o.OrderID, o.OrderDate, c.CustomerNameFROM Customers AS c, Orders AS oWHERE c.CustomerName='Around the Horn' AND c.CustomerID=o.CustomerID; The following SQL statement is the same as above, but without aliases: SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName FROM Customers, Orders WHERE Customers.CustomerName='Around the Horn' AND Customers.CustomerID=Orders.CustomerID;
SQL Aliases
SQL aliases are used to give a database table, or a column in a table, a temporary name. Basically aliases are created to make column names more readable.
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.
The SQL ANY and ALL Operators
The ANY and ALL operators allow you to perform a comparison between a single column value and a range of other values. The ANY operator: returns a boolean value as a result returns TRUE if ANY of the subquery values meet the condition ANY means that the condition will be true if the operation is true for any of the values in the range.