sqlLearning-PartOne

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

SQL Structured Query Language

To build a web site that shows data from a database, you will need:

1. An RDBMS database program (i.e. MS Access, SQL Server, MySQL) (software)
2. To use a server-side scripting language, like PHP or ASP.
3. To use SQL to get the data you want
4. To use HTML / CSS to style the page

__________________________________________________________
RDBMS stands for Relational Database Management System.

The data in RDBMS is stored in database objects called tables.
A table is a collection of related data entries and it consists of columns and rows.

Every table is broken up into smaller entities called fields.
The fields in the Customers table consist of CustomerID, CustomerName, ContactName, Address, City, PostalCode and Country.
A field is a column in a table that is designed to maintain specific information about every record in the table.

A record, also called a row, is each individual entry that exists in a table.
A record is a horizontal entity in a table.

A column is a vertical entity in a table that contains all information associated with a specific field in a table.

__________________________________________________________

Database Tables
A database most often contains one or more tables. Each table is identified by a name (e.g. "Customers" or "Orders").
Tables contain records (rows) with data.


SQL Statements
Most of the actions you need to perform on a database are done with SQL statements.

Ex:
SELECT * FROM Customers;        //  selects all the records in the "Customers" table:

__________________________________________________________

SQL keywords are NOT case sensitive: select is the same as SELECT.
write all SQL keywords in upper-case.

Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement
to be executed in the same call to the server.

__________________________________________________________

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 SELECT Statement:

The SELECT statement is used to select data from a database.
The data returned is stored in a result table, called the result-set.

Syntax:
SELECT column1, column2, ...
FROM table_name;

SELECT * FROM table_name;       // select all

Example:
SELECT ContactName, Address, City
FROM Customers;
__________________________________________________________

SQL SELECT DISTINCT Statement

The SELECT DISTINCT statement is used to return only distinct (different) values.

The following SQL statement lists the number of different (distinct) customer countries:

SELECT COUNT(DISTINCT Country) FROM Customers;

SELECT COUNT(Country) FROM Customers;       // to get count of all the entries under Country.

__________________________________________________________

The SQL WHERE Clause

The WHERE clause is used to filter records based on a condition.

Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Note: The WHERE clause is not only used in SELECT statements, it is also used in UPDATE, DELETE, etc.!

Example:
SELECT * FROM Customers
WHERE Country='Mexico';

BETWEEN:
SELECT * FROM Products
WHERE Price BETWEEN 50 AND 60;

LIKE:
SELECT * FROM Customers
WHERE City LIKE 's%';

IN:
SELECT * FROM Customers
WHERE City IN ('Paris','London');

__________________________________________________________

Text Fields vs. Numeric Fields:

SQL requires single quotes around text values (most database systems will also allow double quotes).

However, numeric fields should not be enclosed in quotes.

__________________________________________________________

SQL AND, OR and NOT Operators

AND Syntax:

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

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

The following SQL statement selects all fields from "Customers" where country is "Germany" AND city must be "Berlin" OR "München"
(use parenthesis to form complex expressions):

SELECT *
FROM Customers
Where Country = "Germany" AND (City = "Berlin" OR "München");
__________________________________________________________

SQL ORDER BY Keyword:

The ORDER BY keyword is used to sort the result-set in ascending or descending order.
The ORDER BY keyword sorts the records in ascending order by default.
To sort the records in descending order, use the DESC keyword.


Syntax:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

Example:
SELECT * FROM Customers
ORDER BY CustomerID ASC;
__________________________________________________________

ORDER BY Several Columns Example:
The following SQL statement selects all customers from the "Customers" table,
sorted by the "Country" and the "CustomerName" column.

This means that it orders by Country, but if some rows have the same Country, it orders them by CustomerName.

SELECT * FROM Customers
ORDER BY Country, CustomerName;

Example 2:
SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;
__________________________________________________________

SQL INSERT INTO Statement:

Two ways:
1. Specify both the column names and the values to be inserted:
2. 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.
However, make sure the order of the values is in the same order as the columns in the table.

Syntax:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

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

Example:
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');

__________________________________________________________

The CustomerID column is an auto-increment field and will be generated automatically when a new record is inserted into the table.

So, we do not have to manually insert into them.
__________________________________________________________

Insert Data Only in Specified Columns:

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

CustomerID  CustomerName    ContactName Address City    PostalCode  Country
92          Cardinal        null        null    Stavanger   null    Norway
__________________________________________________________

SQL NULL Values

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.

Note: A NULL value is different from a zero value or a field that contains spaces.
A field with a NULL value is one that has been left blank during record creation!

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.
Tip: Always use IS NULL to look for NULL values.


SYNTAX:
SELECT column_names
FROM table_name
WHERE column_name IS NULL;

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

Example:
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NULL;
__________________________________________________________

SQL UPDATE Statement:

The UPDATE statement is used to modify the existing records in a table.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Note: Be careful when updating records in a table! Notice the WHERE clause in the UPDATE statement.
The WHERE clause specifies which record(s) that should be updated.
If you omit the WHERE clause, all records in the table will be updated!

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

UPDATE Multiple Records:
It is the WHERE clause that determines how many records will be updated.

Be careful when updating records. If you omit the WHERE clause, ALL records will be updated!
__________________________________________________________

The SQL DELETE Statement:
The DELETE statement is used to delete existing records in a table.

DELETE Syntax
DELETE FROM table_name WHERE condition;

Delete All Records:
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;
DELETE FROM Customers;

__________________________________________________________
SQL TOP, LIMIT, FETCH FIRST or ROWNUM Clause

The SQL SELECT TOP Clause

The SELECT TOP clause is used to specify the number of records to return.

The SELECT TOP clause is useful on large tables with thousands of records.
Returning a large number of records can impact performance.


SYNTAX:
SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;

__________________________________________________________
SQL TOP, LIMIT and FETCH FIRST Examples

SELECT * FROM Customers
LIMIT 3;
__________________________________________________________

SQL TOP PERCENT Example:
SELECT TOP 50 PERCENT * FROM Customers;     SQL Server/MS Access

ADD a WHERE CLAUSE

SELECT * FROM Customers
WHERE Country='Germany'
LIMIT 3;

__________________________________________________________

SQL MIN() and MAX() Functions:

The MIN() function returns the smallest value of the selected column.

The MAX() function returns the largest value of the selected column.

MIN() Syntax:

SELECT MIN(column_name)
FROM table_name
WHERE condition;


MAX() Syntax:
SELECT MAX(column_name)
FROM table_name
WHERE condition;

Ex:

SELECT MIN(Price) AS SmallestPrice
FROM Products;

SELECT MAX(Price) AS LargestPrice
FROM Products;
__________________________________________________________
SQL COUNT(), AVG() and SUM() Functions:


COUNT() Syntax:
SELECT COUNT(column_name)
FROM table_name
WHERE condition;

AVG() Syntax:
SELECT AVG(column_name)
FROM table_name
WHERE condition;

SUM() Syntax:
SELECT SUM(column_name)
FROM table_name
WHERE condition;
__________________________________________________________

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

LIKE Operator   Description
WHERE CustomerName LIKE 'a%'    Finds any values that start with "a"
WHERE CustomerName LIKE '%a'    Finds any values that end with "a"
WHERE CustomerName LIKE '%or%'  Finds any values that have "or" in any position
WHERE CustomerName LIKE '_r%'   Finds any values that have "r" in the second position
WHERE CustomerName LIKE 'a_%'   Finds any values that start with "a" and are at least 2 characters in length
WHERE CustomerName LIKE 'a__%'  Finds any values that start with "a" and are at least 3 characters in length
WHERE ContactName LIKE 'a%o'    Finds any values that start with "a" and ends with "o"

__________________________________________________________
SQL Wildcard Characters:

A wildcard character is used to substitute one or more characters in a string.

Wildcard characters are used with the LIKE operator.
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.


The following SQL statement selects all customers with a City starting with "b", "s", or "p":

SELECT * FROM Customers
WHERE City LIKE '[bsp]%';


The following SQL statement selects all customers with a City starting with "a", "b", or "c":

SELECT * FROM Customers
WHERE City LIKE '[a-c]%';

The two following SQL statements 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]%';
__________________________________________________________

SQL IN Operator:

The IN operator allows you to specify multiple values in a WHERE clause.
The IN operator is a shorthand for multiple OR conditions.

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);

The following SQL statement selects all customers that are located in "Germany", "France" or "UK":

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

The following SQL statement selects all customers that are NOT located in "Germany", "France" or "UK":

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

The following SQL statement selects all customers that are from the same countries as the suppliers:

Example:

SELECT * FROM Customers
WHERE Country IN (SELECT Country FROM Suppliers);
__________________________________________________________

SQL BETWEEN Operator:

BETWEEN Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Example:

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

NOT BETWEEN Example
To display the products outside the range of the previous example, use NOT BETWEEN:

Example:
SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;

BETWEEN with IN Example
The following 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:

Example
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20
AND CategoryID NOT IN (1,2,3);

BETWEEN Text Values Example
The following SQL statement selects all products with a ProductName between Carnarvon Tigers and Mozzarella di Giovanni:

Example:
SELECT * FROM Products
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;

BETWEEN Dates Example
The following SQL statement selects all orders with an OrderDate between '01-July-1996' and '31-July-1996':

Example:
SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/01/1996# AND #07/31/1996#;

OR:

Example:
SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';
__________________________________________________________

SQL Aliases:

SQL aliases are used to give a table, or a column in a table, a temporary 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 column_name AS alias_name
FROM table_name;

SELECT column_name(s)
FROM table_name AS alias_name;

EXAMPLES:
SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;

Note: It requires double quotation marks or square brackets if the alias name contains spaces:

Example
SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;

SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS Address
FROM Customers;

To get the SQL statement above to work in MySQL use the following:

SELECT CustomerName, CONCAT(Address,', ',PostalCode,', ',City,', ',Country) AS Address
FROM Customers;


__________________________________________________________