Notes

SQL is a standard language for storing, manipulating and retrieving data in databases.

Our SQL tutorial will teach you how to use SQL in: MySQL, SQL Server, MS Access, Oracle, Sybase, Informix, Postgres, and other database systems.

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

1) The SELECT DISTINCT statement is used to return only distinct (different) values.
* SELECT DISTINCT Country FROM Customers;

2) The ORDER BY keyword is used to sort the result-set in ascending or descending order.
*SELECT * FROM Products
ORDER BY Price;

3) The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.
* SELECT * FROM Products
ORDER BY Price DESC;

4) The WHERE clause can contain one or many AND operators.
The AND operator is used to filter records based on more than one condition, like if you want to return all customers from Spain that starts with the letter 'G':

* SELECT *
FROM Customers
WHERE Country = 'Spain' AND CustomerName LIKE 'G%';

5) The WHERE clause can contain one or more OR operators.
The OR operator is used to filter records based on more than one condition, like if you want to return all customers from Germany but also those from Spain:
* SELECT *
FROM Customers
WHERE Country = 'Germany' OR Country = 'Spain';

6) The NOT operator is used in combination with other operators to give the opposite result, also called the negative result.
SELECT * FROM Customers
WHERE NOT Country = 'Spain';

7) The UPDATE statement is used to modify the existing records in a table.
* UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

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

8) The DELETE statement is used to delete existing records in a table.
DELETE FROM table_name WHERE condition;

9) SQL Aggregate Functions
An aggregate function is a function that performs a calculation on a set of values, and returns a single value.

Aggregate functions are often used with the GROUP BY clause of the SELECT statement. The GROUP BY clause splits the result-set into groups of values and the aggregate function can be used to return a single value for each group.

The most commonly used SQL aggregate functions are:

MIN() - returns the smallest value within the selected column
MAX() - returns the largest value within the selected column
COUNT() - returns the number of rows in a set
SUM() - returns the total sum of a numerical column
AVG() - returns the average value of a numerical column
Aggregate functions ignore null values (except for COUNT()).

10) The SQL LIKE Operator
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

There are two wildcards often used in conjunction with the LIKE operator:

The percent sign % represents zero, one, or multiple characters
The underscore sign _ represents one, single character

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

* Return all customers from a city that contains the letter 'L':
Ex: SELECT * FROM Customers
WHERE city LIKE '%L%';

* Return all customers that starts with 'La':

Ex: SELECT * FROM Customers
WHERE CustomerName LIKE 'La%';

* Return all customers that starts with 'a' or starts with 'b':
SELECT * FROM Customers
WHERE CustomerName LIKE 'a%' OR CustomerName LIKE 'b%';

* Return all customers that starts with "b" and ends with "s":
SELECT * FROM Customers
WHERE CustomerName LIKE 'b%s';


11) 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.

* SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

12) SQL Aliases
SQL aliases are used to give a table, or a column in a table, a temporary name. (Nick Name)
Aliases are often used to make column names more readable.
An alias only exists for the duration of that query.
An alias is created with the AS keyword.
* SELECT CustomerID AS ID
FROM Customers;
Ex: SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;

13) SQL JOIN
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
Let's look at a selection from the "Orders" table:

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

Different Types of SQL JOINs
Here are the different types of the JOINs in SQL:

(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

14) 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.
GROUP BY Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

15) The SQL AVG() Function
The AVG() function returns the average value of a numeric column.
Ex: Find the average price of all products:
SELECT AVG(Price)
FROM Products;

16) 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.
* To add a column in a table, use the following syntax:
ALTER TABLE table_name
ADD column_name datatype;
Ex:ALTER TABLE Customers
ADD Email varchar(255);