DDL : CREATE, ALTER, DROP, RENAME, TRUNCATE

Creating and Managing Tables

Click Here:

http://www.slideshare.net/vikasgupta963434/sql-ddl-28784528

DML: - Insert, Update, Delete, Merge

Manipulating Data

DML: - Insert, Update, Delete, Merge

Click Here:

http://www.slideshare.net/vikasgupta963434/sql-8-dml-28784268

Sub queries

Sub queries

click here:

 http://www.slideshare.net/vikasgupta963434/sql-6-subquery

UNION Operator

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

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

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.

SELECT column_name(s) FROM table1
 

UNION ALL
 

SELECT column_name(s) FROM table2;

SQL Joins

Combine rows from two or more tables, based on a common field between them.

---

• The most common type of join is: SQL INNER JOIN (simple join).
• An SQL INNER JOIN return all rows from multiple tables where the join condition is met.

Ex:

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

Different SQL JOINs

• 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

INNER JOIN Syntax

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

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

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

SQL LEFT JOIN Syntax

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

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

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

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

RIGHT JOIN Keyword

The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.

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

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

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

FULL OUTER JOIN Keyword

The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2).

The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.

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

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

Click here for ppt presentation :- 

http://www.slideshare.net/vikasgupta963434/sql-4-joinhttp://www.slideshare.net/vikasgupta963434/sql-4-join

INSERT INTO SELECT Statement

selects data from one table and inserts it into an existing table.

We can copy all columns from one table to another, existing table:

INSERT INTO table2
SELECT * FROM table1;

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

 

 We can copy only the columns we want to into another, existing table:

 

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers
WHERE Country='Germany';

SELECT INTO Statement

You can copy information from one table into another. 

 The SELECT INTO statement copies data from one table and inserts it into a new table.

SELECT *

INTO newtable [IN externaldb]
 

FROM table1;

- selects data from one table and inserts it into a new table. 

SELECT column_name(s)
 

INTO newtable [IN externaldb]
 

FROM table1; 

- copy only the columns we want into the new table. 

Qs:  Create a backup copy of Customers:

SELECT *
INTO CustomersBackup2013
FROM Customers; 

Qs: Use the IN clause to copy the table into another database: 

SELECT *
INTO CustomersBackup2013 IN 'Backup.mdb'
FROM Customers;

Qs: Copy only a few columns into the new table:

SELECT CustomerName, ContactName
INTO CustomersBackup2013
FROM Customers; 

Qs: Copy only the German customers into the new table:

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

Qs: Copy data from more than one table into the new table:

SELECT Customers.CustomerName, Orders.OrderID
INTO CustomersOrderBackup2013
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID;

 

 

 

SELECT TOP Clause

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

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

Note: Not all database systems support the SELECT TOP clause. This is for SQL Server.

SELECT TOP Equivalent in Oracle

SELECT column_name(s)
 
FROM table_name
 
WHERE ROWNUM <= number;


SELECT * FROM Persons WHERE ROWNUM <=5; 











 

SQL Basic Syntax

SELECT Statement

SELECT column_name,column_name
FROM table_name;

SELECT * FROM table_name;

SELECT CustomerName,City FROM Customers;

SELECT * FROM Customers;

(all the columns from the "Customers" table)

SELECT DISTINCT Statement

SELECT DISTINCT column_name,column_name
FROM table_name; 

SELECT DISTINCT City FROM Customers;

WHERE Clause

extract only those records that fulfill a specified criterion.

SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;

SELECT * FROM Customers
WHERE Country='Mexico';

SELECT * FROM Customers
WHERE CustomerID=1;

Operators in The WHERE Clause

Operator	Description
=	Equal
<>	Not equal. Note: In some versions of SQL this operator may be written as !=
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
BETWEEN	Between an inclusive range
LIKE	Search for a pattern
IN	To specify multiple possible values for a column

AND & OR Operators (Logical Operators)

The AND operator displays a record if both the first condition AND the second condition are true.
The OR operator displays a record if either the first condition OR the second condition is true.

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 Keyword

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

SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC|DESC;

SELECT * FROM Customers
ORDER BY Country; 

SELECT * FROM Customers
ORDER BY Country DESC;

SELECT * FROM Customers
ORDER BY Country,CustomerName; 

LIKE Operator

search for a specified pattern in a column.

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

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

selects all customers with a City starting with the letter "s":

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

selects all customers with a City ending with the letter "s": 

SQL Wildcard Characters

used to search for data within a table. 

Wildcard	Description
%	A substitute for zero or more characters
_	A substitute for a single character
[charlist]	Sets and ranges of characters to match
[^charlist] or [!charlist]	Matches only a character NOT specified within the brackets

SELECT * FROM Customers WHERE City LIKE '_erlin';  

selects all customers with a City starting with any character, followed by "erlin":

SELECT * FROM Customers WHERE City LIKE 'L_n_on';

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

selects all customers with a City starting with "b", "s", or "p"

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

selects all customers with a City starting with "a", "b", or "c"

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

selects all customers with a City NOT starting with "b", "s", or "p"

 

IN Operator

specify multiple values in a WHERE clause.

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);

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

 

BETWEEN Operator

select values within a range.

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

The values can be numbers, text, or dates.

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20; 

SELECT * FROM Products
WHERE ProductName BETWEEN 'C' AND 'M'; 

SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'C' AND 'M';  

SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;  

 

 

INSERT INTO Statement

insert new records in a table.

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

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

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

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

UPDATE Statement

update existing records in a table.

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

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

DELETE Statement

delete rows/ records in a table.

DELETE FROM table_name
WHERE some_column=some_value;

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

Delete All Data

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;

or

DELETE * FROM table_name;

Be very careful when deleting records. You cannot undo this statement!

 

 

 

  

SQL select statement and functions

SELECT and FUNCTIONS

Click here :
http://www.slideshare.net/vikasgupta963434/sql-selectn-function
 

SQL Introduction