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.