SQL SYNTAX

SQL SELECT

select Column
select *

SQL SELECT DISTINCT

select distinct
select COUNT(distinct column_name)

SQL WHERE

where clause

SQL AND,OR AND NOT Operators

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

SQL ORDER BY
ASC:ascending/
DESC:descending

order by
order by desc
order by several column

SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;

SQL INSERT INTO

INSERT INTO

If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query.

INSERT INTO table_name
VALUES (value1, value2, value3, ...)

Insert Data Only in Specified Columns

The following SQL statement will insert a new record, but only insert data in the "CustomerName", "City", and "Country" columns (CustomerID will be updated automatically):

INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');

SQL NULL Values

We will have to 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;

SQL Updates

Updating table

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;

Updating multiple Records

The following SQL statement will update the contactname to "Juan" for all records where country is "Mexico":

UPDATE Customers
SET ContactName='Juan'
WHERE Country='Mexico’;

SQL DELECT Statement

DELETE FROM Customers 
WHERE CustomerName='Alfreds Futterkiste';

Delete All Records

DELETE FROM Customers;

SQL TOP, LIMIT or ROWNUM Clause

TOP

select TOP 3 * from costumers;

LIMIT

SELECT * from Costumers
LIMIT 3`

ROWNUM

SELECT * FROM Customers
WHERE ROWNUM <= 3;

The three syntaxes above are equivalent.

TOP PERCENT

SELCT TOP 50 PERCENT *
FROM Costumers;

With a WHERE clause

SELECT * FROM Customers
WHERE Country='Germany' 
AND ROWNUM <= 3;

SQL MIN() AND MAX()

SELECT MIN(Price) AS
SmallestPrice
From Products;

SQL COUNT(),AVG() and SUM() Functions

COUNT()

SELECT COUNT(ProductID)
FROM Products;

RESULT:

COUNT(ProductID)
77

AVG()

SELECT AVG(Price)
FROM Products;

Result:

AVG(Price)
28.86636363636363637

SUM()

SELECT SUM(Quantity)
FROM OrderDetails;

SQL LIKE
- % The percent sign represents zero, one, or multiple characters
- _ The underscore represents a single character

SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';

SQL Wildcards

REG

SQL IN

SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');

SQL BETWEEN

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

SQL Aliases

SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;

SQL Joins

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Inner Join

Inner Join vs. Outer Join. In SQL, a join is used to compare and combine — literally join — and return specific rows of data from two or more tables in a database. An inner join finds and returns matching data from tables, while an outer join finds and returns matching data and some dissimilar data from tables.

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

Different types of SQL JOINs
- (INNER) JOIN: Returns records that have matching values in both tables
- LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table
- RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table
- FULL (OUTER) JOIN: Return all records when there is a match in either left or right table

SQL UNIONS

The SELECT statement within UNION has to fulfill the following 3 features:

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

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

UNION ALL Syntax

allow duplicate values

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

SQL GROUP BY

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;

Result:

COUNT(CostumerID)	Country
5	Germany
3	England
1	China

SQL HAVING

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

SQL EXISTS

The EXISTS operator is used to test for the existence of any record in a subquery.
The EXISTS operator returns true if the subquery returns one or more records.

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

SQL ANY and ALL

The ANY and ALL operators are used with a WHERE or HAVING clause.
The ANY operator returns true if any of the subquery values meet the condition.
The ALL operator returns true if all of the subquery values meet the condition.

SELECT ProductName
FROM Products
WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);

SQL INSERT INTO SELECT

The INSERT INTO SELECT statement copies data from one table and inserts it into another table.

INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers;

SQL CASE

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;

Above are the basic syntaxes of SQL language, some of them may differ in different SQLs.