Sql

SQL Quick Reference :

SQL Statement Syntax
AND / OR SELECT column_name(s)
FROM table_name
WHERE condition
AND|OR condition
ALTER TABLE ALTER TABLE table_name
ADD column_name datatypeorALTER TABLE table_name
DROP COLUMN column_name
AS (alias) SELECT column_name AS column_alias
FROM table_nameorSELECT column_name
FROM table_name  AS table_alias
BETWEEN SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2
CREATE DATABASE CREATE DATABASE database_name
CREATE TABLE CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name2 data_type,

)
CREATE INDEX CREATE INDEX index_name
ON table_name (column_name)orCREATE UNIQUE INDEX index_name
ON table_name (column_name)
CREATE VIEW CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
DELETE DELETE FROM table_name
WHERE some_column=some_valueorDELETE FROM table_name
(Note: Deletes the entire table!!)DELETE * FROM table_name
(Note: Deletes the entire table!!)
DROP DATABASE DROP DATABASE database_name
DROP INDEX DROP INDEX table_name.index_name (SQL Server)
DROP INDEX index_name ON table_name (MS Access)
DROP INDEX index_name (DB2/Oracle)
ALTER TABLE table_name
DROP INDEX index_name (MySQL)
DROP TABLE DROP TABLE table_name
GROUP BY SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
IN SELECT column_name(s)
FROM table_name
WHERE column_name
IN (value1,value2,..)
INSERT INTO INSERT INTO table_name
VALUES (value1, value2, value3,….)orINSERT INTO table_name
(column1, column2, column3,…)
VALUES (value1, value2, value3,….)
INNER JOIN SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
LEFT JOIN SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
RIGHT JOIN SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
FULL JOIN SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
LIKE SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern
ORDER BY SELECT column_name(s)
FROM table_name
ORDER BY column_name [ASC|DESC]
SELECT SELECT column_name(s)
FROM table_name
SELECT * SELECT *
FROM table_name
SELECT DISTINCT SELECT DISTINCT column_name(s)
FROM table_name
SELECT INTO SELECT *
INTO new_table_name [IN externaldatabase]
FROM old_table_nameorSELECT column_name(s)
INTO new_table_name [IN externaldatabase]
FROM old_table_name
SELECT TOP SELECT TOP number|percent column_name(s)
FROM table_name
TRUNCATE TABLE TRUNCATE TABLE table_name
UNION SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
UNION ALL SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
UPDATE UPDATE table_name
SET column1=value, column2=value,…
WHERE some_column=some_value
WHERE SELECT column_name(s)
FROM table_name
WHERE column_name operator value

DISTINCT :

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

* SELECT DISTINCT City FROM Customers;

AND, OR Operator Example :

* SELECT * FROM Customers WHERE Country=’Germany’ AND City=’Berlin’;

* SELECT * FROM Customers WHERE City=’Berlin’ OR City=’München’;

* SELECT * FROM Customers WHERE Country=’Germany’ AND (City=’Berlin’ OR City=’München’);

ORDER BY :

The ORDER BY keyword is used to sort the result-set by one or more columns.

*  SELECT * FROM Customers ORDER BY Country DESC;

* SELECT * FROM Customers ORDER BY Country,CustomerName;

INSERT INTO :

* INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES (‘Cardinal’,’Tom B. Erichsen’,’Skagen 21′,’Stavanger’,’4006′,’Norway’);

UPDATE :

* UPDATE Customers SET ContactName=’Alfred Schmidt’, City=’Hamburg’ WHERE CustomerName=’Alfreds Futterkiste’;

DELETE :

*  DELETE FROM Customers WHERE CustomerName=’Alfreds Futterkiste’ AND ContactName=’Maria Anders’;

Injection :

SQL injection refers to the act of someone inserting a MySQL statement to be run on your database without your knowledge. Injection usually occurs when you ask a user for input, like their name, and instead of a name they give you a MySQL statement that you will unknowingly run on your database.

SELECT TOP :

* SELECT TOP number|percent column_name(s) FROM table_name;

* SELECT * FROM Persons LIMIT 5;

* SELECT TOP 2 * FROM Customers;

* SELECT TOP 50 PERCENT * FROM Customers;

LIKE :

The LIKE operator is used to search for a specified pattern in a column.

* SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;

The following SQL statement selects all customers with a City starting with the letter “s”:

* SELECT * FROM Customers WHERE City LIKE ‘s%’;

The following SQL statement selects all customers with a City ending with the letter “s”:

* SELECT * FROM Customers WHERE City LIKE ‘%s’;

The following SQL statement selects all customers with a Country containing the pattern “land”:

* SELECT * FROM Customers WHERE Country LIKE ‘%land%’;

Using the NOT keyword allows you to select records that does NOT match the pattern.

The following SQL statement selects all customers with a Country NOT containing the pattern “land”:

* SELECT * FROM Customers WHERE Country NOT LIKE ‘%land%’;

Wildcards :

In SQL, wildcard characters are used with the SQL LIKE operator.

SQL wildcards are used to search for data within a table.

The following SQL statement selects all customers with a City starting with “ber”:

Example

* SELECT * FROM Customers WHERE City LIKE ‘ber%’;

The following SQL statement selects all customers with a City containing the pattern “es”:

Example

* SELECT * FROM Customers WHERE City LIKE ‘%es%’;

The following SQL statement selects all customers with a City starting with any character, followed by “erlin”:

Example

* SELECT * FROM Customers WHERE City LIKE ‘_erlin’;

The following SQL statement selects all customers with a City starting with “L”, followed by any character, followed by “n”, followed by any character, followed by “on”:

Example

* SELECT * FROM Customers WHERE City LIKE ‘L_n_on’;

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

Example

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

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

Example

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

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

Example

* SELECT * FROM Customers WHERE City LIKE ‘[!bsp]%’;

IN :

The IN operator allows you to specify multiple values in a WHERE clause.

The following SQL statement selects all customers with a City of “Paris” or “London”:

Example

* SELECT * FROM Customers WHERE City IN (‘Paris’,’London’);

BETWEEN :

The BETWEEN operator selects values within a range. The values can be numbers, text, or dates.

The following SQL statement selects all products with a price BETWEEN 10 and 20:

Example

* SELECT * FROM Product WHERE Price BETWEEN 10 AND 20;
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;
*  SELECT * FROM Products WHERE ProductName BETWEEN ‘C’ AND ‘M’;
* SELECT * FROM Products WHERE ProductName NOT BETWEEN ‘C’ AND ‘M’;

BETWEEN Operator ith IN Example :

The following SQL statement selects all products with a price BETWEEN 10 and 20, but products with a CategoryID of 1,2, or 3 should not be displayed:

Example

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

SQL Joins :

SQL joins are used to combine rows from two or more tables.

Different SQL JOINs :

Before we continue with examples, we will list the types the different SQL JOINs you can use:

  • INNER JOIN: Returns all rows when there is at least one match in BOTH tables
  • LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
  • RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
  • FULL JOIN: Return all rows when there is a match in ONE of the tables

SQL INNER JOIN :

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

or:

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

PS! INNER JOIN is the same as JOIN.

SQL INNER JOIN

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

LEFT JOIN :

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

or:

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

PS! In some databases LEFT JOIN is called LEFT OUTER JOIN.

SQL LEFT JOIN

Below is a selection from the “Customers” table:

CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico

And a selection from the “Orders” table:

OrderID CustomerID EmployeeID OrderDate ShipperID
10308 2 7 1996-09-18 3
10309 37 3 1996-09-19 1
10310 77 8 1996-09-20 2

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

Note: The LEFT JOIN keyword returns all the rows from the left table (Customers), even if there are no matches in the right table (Orders).

RIGHT JOIN :

 SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

or:

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

PS! In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

SQL RIGHT JOIN

Below is a selection from the “Orders” table:

OrderID CustomerID EmployeeID OrderDate ShipperID
10308 2 7 1996-09-18 3
10309 37 3 1996-09-19 1
10310 77 8 1996-09-20 2

And a selection from the “Employees” table:

EmployeeID LastName FirstName BirthDate Photo Notes
1 Davolio Nancy 12/8/1968 EmpID1.pic Education includes a BA in psychology…..
2 Fuller Andrew 2/19/1952 EmpID2.pic Andrew received his BTS commercial and….
3 Leverling Janet 8/30/1963 EmpID3.pic Janet has a BS degree in chemistry….

SELECT Orders.OrderID, Employees.FirstName
FROM Orders
RIGHT JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID
ORDER BY Orders.OrderID;

Note: The RIGHT JOIN keyword returns all the rows from the right table (Employees), even if there are no matches in the left table (Orders).

FULL OUTER JOIN :

 SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

SQL FULL OUTER JOIN
Below is a selection from the “Customers” table:

CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico

And a selection from the “Orders” table:

OrderID CustomerID EmployeeID OrderDate ShipperID
10308 2 7 1996-09-18 3
10309 37 3 1996-09-19 1
10310 77 8 1996-09-20 2

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

A selection from the result set may look like this:

CustomerName OrderID
Alfreds Futterkiste
Ana Trujillo Emparedados y helados 10308
Antonio Moreno Taquería 10365
10382
10351

Note: The FULL OUTER JOIN keyword returns all the rows from the left table (Customers), and all the rows from the right table (Orders). If there are rows in “Customers” that do not have matches in “Orders”, or if there are rows in “Orders” that do not have matches in “Customers”, those rows will be listed as well.

UNION :

The UNION operator is used to combine the result-set of two or more SELECT statements.

SQL UNION Syntax

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

Note: The UNION operator selects only distinct values by default. To allow duplicate values, use the ALL keyword with UNION.

SQL UNION ALL Syntax

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

PS: The column names in the result-set of a UNION are usually equal to the column names in the first SELECT statement in the UNION.

SQL UNION Example

The following SQL statement selects all the different cities (only distinct values) from the “Customers” and the “Suppliers” tables:

Example

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

Note: UNION cannot be used to list ALL cities from the two tables. If several customers and suppliers share the same city, each city will only be listed once. UNION selects only distinct values. Use UNION ALL to also select duplicate values!


SQL UNION ALL Example

The following SQL statement uses UNION ALL to select all (duplicate values also) cities from the “Customers” and “Suppliers” tables:

Example

SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

SQL UNION ALL With WHERE

The following SQL statement uses UNION ALL to select all (duplicate values also) German cities from the “Customers” and “Suppliers” tables:

Example

SELECT City, Country FROM Customers
WHERE Country=’Germany’
UNION ALL
SELECT City, Country FROM Suppliers
WHERE Country=’Germany’
ORDER BY City;

SELECT INTO :

With SQL, you can copy information from one table into another.

* SELECT * INTO newtable FROM table1

* SELECT * INTO CustomersBackup2013 FROM Customers WHERE Country=’Germany’;

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

Constraints :

In SQL, we have the following constraints:

  • NOT NULL – Indicates that a column cannot store NULL value
  • UNIQUE – Ensures that each row for a column must have a unique value
  • PRIMARY KEY – A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have an unique identity which helps to find a particular record in a table more easily and quickly
  • FOREIGN KEY – Ensure the referential integrity of the data in one table to match values in another table
  • CHECK – Ensures that the value in a column meets a specific condition
  • DEFAULT – Specifies a default value when specified none for this column

SQL UNIQUE Constraint :

The UNIQUE constraint uniquely identifies each record in a database table.

The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.

Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

SQL Views :

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 functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

SQL CREATE VIEW Syntax :

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Note: A view always shows up-to-date data! The database engine recreates the data, using the view’s SQL statement, every time a user queries a view.

SQL Updating a View :

You can update a view by using the following syntax:

SQL CREATE OR REPLACE VIEW Syntax

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

SQL Dropping a View :

You can delete a view with the DROP VIEW command.

SQL DROP VIEW Syntax

DROP VIEW view_name

MySQL Date Functions :

The following table lists the most important built-in date functions in MySQL:

Function Description
NOW() Returns the current date and time
CURDATE() Returns the current date
CURTIME() Returns the current time
DATE() Extracts the date part of a date or date/time expression
EXTRACT() Returns a single part of a date/time
DATE_ADD() Adds a specified time interval to a date
DATE_SUB() Subtracts a specified time interval from a date
DATEDIFF() Returns the number of days between two dates
DATE_FORMAT() Displays date/time data in different formats

SQL Server Date Functions

The following table lists the most important built-in date functions in SQL Server:

Function Description
GETDATE() Returns the current date and time
DATEPART() Returns a single part of a date/time
DATEADD() Adds or subtracts a specified time interval from a date
DATEDIFF() Returns the time between two dates
CONVERT() Displays date/time data in different formats

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:MM:SS
  • TIMESTAMP – format: YYYY-MM-DD HH:MM:SS
  • YEAR – format YYYY or YY

SQL Server 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:MM:SS
  • SMALLDATETIME – format: YYYY-MM-DD HH:MM:SS
  • TIMESTAMP – format: a unique number

Note: The date types are chosen for a column when you create a new table in your database!

MySQL Data Types :

In MySQL there are three main types : text, number, and Date/Time types.

Text types:

Data type Description
CHAR(size) Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis. Can store up to 255 characters
VARCHAR(size) Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis. Can store up to 255 characters. Note: If you put a greater value than 255 it will be converted to a TEXT type
TINYTEXT Holds a string with a maximum length of 255 characters
TEXT Holds a string with a maximum length of 65,535 characters
BLOB For BLOBs (Binary Large OBjects). Holds up to 65,535 bytes of data
MEDIUMTEXT Holds a string with a maximum length of 16,777,215 characters
MEDIUMBLOB For BLOBs (Binary Large OBjects). Holds up to 16,777,215 bytes of data
LONGTEXT Holds a string with a maximum length of 4,294,967,295 characters
LONGBLOB For BLOBs (Binary Large OBjects). Holds up to 4,294,967,295 bytes of data
ENUM(x,y,z,etc.) Let you enter a list of possible values. You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted.Note: The values are sorted in the order you enter them.You enter the possible values in this format: ENUM(‘X’,’Y’,’Z’)
SET Similar to ENUM except that SET may contain up to 64 list items and can store more than one choice

Number types:

Data type Description
TINYINT(size) -128 to 127 normal. 0 to 255 UNSIGNED*. The maximum number of digits may be specified in parenthesis
SMALLINT(size) -32768 to 32767 normal. 0 to 65535 UNSIGNED*. The maximum number of digits may be specified in parenthesis
MEDIUMINT(size) -8388608 to 8388607 normal. 0 to 16777215 UNSIGNED*. The maximum number of digits may be specified in parenthesis
INT(size) -2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The maximum number of digits may be specified in parenthesis
BIGINT(size) -9223372036854775808 to 9223372036854775807 normal. 0 to 18446744073709551615 UNSIGNED*. The maximum number of digits may be specified in parenthesis
FLOAT(size,d) A small number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter
DOUBLE(size,d) A large number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter
DECIMAL(size,d) A DOUBLE stored as a string , allowing for a fixed decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter

*The integer types have an extra option called UNSIGNED. Normally, the integer goes from an negative to positive value. Adding the UNSIGNED attribute will move that range up so it starts at zero instead of a negative number.

Date types:

Data type Description
DATE() A date. Format: YYYY-MM-DDNote: The supported range is from ‘1000-01-01’ to ‘9999-12-31’
DATETIME() *A date and time combination. Format: YYYY-MM-DD HH:MM:SSNote: The supported range is from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’
TIMESTAMP() *A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch (‘1970-01-01 00:00:00’ UTC). Format: YYYY-MM-DD HH:MM:SSNote: The supported range is from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’ UTC
TIME() A time. Format: HH:MM:SSNote: The supported range is from ‘-838:59:59’ to ‘838:59:59’
YEAR() A year in two-digit or four-digit format.Note: Values allowed in four-digit format: 1901 to 2155. Values allowed in two-digit format: 70 to 69, representing years from 1970 to 2069

*Even if DATETIME and TIMESTAMP return the same format, they work very differently. In an INSERT or UPDATE query, the TIMESTAMP automatically set itself to the current date and time. TIMESTAMP also accepts various formats, like YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, or YYMMDD.

The AVG() Function :

The AVG() function returns the average value of a numeric column.

SQL AVG() Syntax

SELECT AVG(column_name) FROM table_name
* SELECT AVG(Price) AS PriceAverage FROM Products;
* SELECT ProductName, Price FROM Products WHERE Price>(SELECT AVG(Price) FROM Products);

SQL COUNT(column_name) Syntax

The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:

SELECT COUNT(column_name) FROM table_name;

SQL COUNT(*) Syntax

The COUNT(*) function returns the number of records in a table:

SELECT COUNT(*) FROM table_name;

SQL COUNT(DISTINCT column_name) Syntax

The COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column:

SELECT COUNT(DISTINCT column_name) FROM table_name;

Note: COUNT(DISTINCT) works with ORACLE and Microsoft SQL Server, but not with Microsoft Access.

* SELECT COUNT(CustomerID) AS OrdersFromCustomerID7 FROM Orders WHERE CustomerID=7;

* SELECT COUNT(*) AS NumberOfOrders FROM Orders;

* SELECT COUNT(DISTINCT CustomerID) AS NumberOfCustomers FROM Orders;

The MAX() Function :

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

SQL MAX() Syntax

SELECT MAX(column_name) FROM table_name;
* SELECT MAX(Price) AS HighestPrice FROM Products;

The MIN() Function :

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

SQL MIN() Syntax

SELECT MIN(column_name) FROM table_name;
* SELECT MIN(Price) AS SmallestOrderPrice FROM Products;

The SUM() Function :

The SUM() function returns the total sum of a numeric column.

SQL SUM() Syntax

SELECT SUM(column_name) FROM table_name;
* SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;

The GROUP BY Statement :

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

SQL GROUP BY Syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
* SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID
GROUP BY ShipperName;
We can also use the GROUP BY statement on more than one column, like this:
* SELECT Shippers.ShipperName, Employees.LastName,
COUNT(Orders.OrderID) AS NumberOfOrders
FROM ((Orders
INNER JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID)
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID)
GROUP BY ShipperName,LastName;

The HAVING Clause :

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

SQL HAVING Syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

The following SQL statement finds if any of the employees has registered more than 10 orders:

Example

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;

Now we want to find the if the employees “Davolio” or “Fuller” have more than 25 orders

We add an ordinary WHERE clause to the SQL statement:

Example

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;

The UCASE() Function :

The UCASE() function converts the value of a field to uppercase.

SQL UCASE() Syntax

SELECT UCASE(column_name) FROM table_name;
* SELECT UCASE(CustomerName) AS Customer, City
FROM Customers;

The LCASE() Function :

The LCASE() function converts the value of a field to lowercase.

SQL LCASE() Syntax

SELECT LCASE(column_name) FROM table_name;
* SELECT LCASE(CustomerName) AS Customer, City FROM Customers;

The MID() Function :

The MID() function is used to extract characters from a text field.

SQL MID() Syntax

SELECT MID(column_name,start[,length]) FROM table_name;
* SELECT MID(City,1,4) AS ShortCity FROM Customers;
The ROUND() Function :

The ROUND() function is used to round a numeric field to the number of decimals specified.

SQL ROUND() Syntax

SELECT ROUND(column_name,decimals) FROM table_name;
* SELECT ProductName, ROUND(Price,0) AS RoundedPrice FROM Products;

The following SQL statement selects the product name and the price rounded to the nearest integer from the “Products” table:

Example

* SELECT ProductName, ROUND(Price,0) AS RoundedPrice FROM Products;

The NOW() Function :

The NOW() function returns the current system date and time.

SQL NOW() Syntax

SELECT NOW() FROM table_name;
* SELECT ProductName, Price, Now() AS PerDate FROM Products;

The FORMAT() Function :

The FORMAT() function is used to format how a field is to be displayed.

SQL FORMAT() Syntax

SELECT FORMAT(column_name,format) FROM table_name;
* SELECT ProductName, Price, FORMAT(Now(),’YYYY-MM-DD’) AS PerDate FROM Products;
————————————————————————————————————————————————————————————-
  • Multiple way of insert query :
  • include column names in every INSERT statement
    Example: INSERT INTO tbl_name (col_A,col_B,col_C) VALUES (1,2,3)
  • insert multiple rows in every INSERT statement
    Example: INSERT INTO tbl_name VALUES (1,2,3), (4,5,6), (7,8,9)
  • both of the above
    Example: INSERT INTO tbl_name (col_A,col_B) VALUES (1,2,3), (4,5,6), (7,8,9)
  • neither of the above
    Example: INSERT INTO tbl_name VALUES (1,2,3)

——————————————————————————————————————————–

Fetch the records as randomly :

$sql = “SELECT * FROM “.TBL_LEGENDS.” WHERE status = ‘Y’ ORDER BY RAND() LIMIT 0,3″;

 Get records from two tables Joins:

$sql = “SELECT p.*, l.lang_name lang_name, p.lang_id lang_id
FROM latest_news p, language_master l WHERE p.lang_id = l.lang_id AND p.news_id = ‘”.$insert_id.”‘ order by p.id desc”;

Get Month and Week Record from db :

if($type == ‘WEEK’)
{
$sql = “SELECT * FROM testimonials WHERE WEEK(created_date) = WEEK(now()) and lang_id = 1 ORDER BY sno desc”;
}
if($type == ‘MONTH’)
{
$sql = “SELECT * FROM testimonials WHERE MONTH(created_date) = MONTH(now()) and lang_id = 1 ORDER BY sno desc”;
}

———————————————————————————————————————————————————————————————–

SQL Join for retreive all fields :

$sql = “SELECT p.*, l.lang_name lang_name, p.lang_id lang_id
FROM “.$this->table_name.” p, language_master l WHERE p.lang_id = l.lang_id AND p.change_tyre_id = ‘”.$insert_id.”‘”;

Search query using Like :

SELECT pid,page_title,page_short_description,page_long_description FROM page_content WHERE lang_id='”.$site_lang_id.”‘ and page_title LIKE ‘%$search%’ OR lang_id='”.$site_lang_id.”‘ and page_short_description LIKE ‘%$search%’ OR lang_id='”.$site_lang_id.”‘ and page_long_description LIKE ‘%$search%'”

Two Table Joins :

a) $sql = “select pul.title,pul.description,pul.image_name,pul.lang_id,l.lang_id,l.lang_name from safety_saving_takecareof_tyre pul,language_master l where take_care_id='”.$id.”‘ AND pul.lang_id=l.lang_id”;

b)$sql = “SELECT p.title title,p.description description, l.lang_name lang_name, p.lang_id lang_id from
tyre_structure p, language_master l WHERE p.lang_id = l.lang_id AND p.t_structure_id = ‘”.$id.”‘ order by p.t_structure_id desc”;

DISTINCT :

$sql = “select DISTINCT driving_skill_types_id from safety_saving_spl_driving_skill where lang_id=’1′ AND status=’Y'”;

GET MAX ID :

$sql_insertid = $this->db->query(“SELECT max(spl_driving_id) as max_id FROM  safety_saving_spl_driving_skill”);

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s