SQL
SQL Data
Definition
SQL data
definition statements allow you to define the structure of database such as
tables, rows, columns, indexes...etc. using common SQL statements such as SQL
CREATE, SQL ALTER and SQL DROP.
Create Table
CREATE TABLE table_name
(
column_name1 data_type(size) [NULL|NOTNULL],
column_name2 data_type(size) [NULL|NOTNULL],
.......
)
|
CREATE TABLE employee(
id INT NOT NULL AUTO_INCREMENT ,
name VARCHAR(255) NOT NULL ,
salary DECIMAL NOT NULL,
PRIMARY KEY (id)
)
|
Alter Table
ALTER TABLE table_name
{
[ADD [COLUMN] column_definition] |
[ALTER [COLUMN] column_name
{SET DEFAULT default | DROP DEFAULT}] |
[DROP [COLUMN] column_name
RESTRICT | CASCADE] |
[ADD table_constraint] |
[DROP CONSTRAINT constraint_name
RESTRICT | CASCADE] };
|
ALTER TABLE employees
ADD COLUMN hiredate DATE NOT NULL
AFTER salary
ALTER TABLE employees
ALTER COLUMN name VARCHAR(50)
|
Drop Table
DROP TABLE table_name
DROP TABLE employee
DROP DATABASE database_name
SQL
Statements
SELECT [DISTINCT] column_name|*|expression AS column_alias
FROM [table_or_view_name]
SELECT name FROM employees
|
SELECT id, name, salary FROM employees
|
SELECT * FROM employees
|
SELECT DISTINCT salary FROM employees
|
You can also select literals, functions, and calculated columns.
SELECT NOW() AS current_datetime
SELECT MAX(salary) AS max_salary, MIN(salary) AS min_salary FROM employees
SQL WHERE clause
is used with SQL SELECT statement to specifies the search conditions of a
query. When you execute a query with SQL WHERE clause, the database server
searches for records in the database table(s) which meets your search
conditions. SQL provides a variety of search conditions which you can use with
SQL WHERE clause as follows:
a) Comparison operators (=,>,<,.>=,<=,<>)
|
value-1 >= value-2 AND value-1 <= value-3
|
SELECT * FROM sp WHERE qty >= 200
|
b) Combinations or logical negations with (AND, OR,
NOT)
SELECT * FROM sp WHERE sno='S3' AND qty < 500
SELECT * FROM s WHERE sno='S3' OR city = 'London'
c) Ranges with (BETWEEN and NOT BETWEEN)
value-1 [NOT] BETWEEN value-2 AND value-3
|
SELECT * FROM sp WHERE qty BETWEEN 50 and 500
|
d) Lists with (IN, NOT IN)
value-1 [NOT] IN ( value-2 [, value-3] ... )
|
SELECT name FROM s WHERE city IN ('Rome','Paris')
|
e) Character matches with LIKE
value-1 [NOT] LIKE value-2 [ESCAPE value-3]
|
z NOT LIKE 'abc%'
|
A database null may represent any value in the future, but the value is
not available at this time
WHERE qty = NULL
|
value-1 IS [NOT] NULL
|
SELECT * FROM sp WHERE NOT sno = 'S3'
ORDER BY column-1
[ASC|DESC] [ column-2 [ASC|DESC] ] ...
- ASC and DESC request ascending or
descending sort for a column. ASC is the default.
- column-1, column-2, ... are column
names specified (or implied) in the select list. If a select column is
renamed (given a new name in the select entry), the new name is used in
the ORDER BY list. ASC and DESC request ascending or descending sort for a
column. ASC is the default.
SELECT name, city FROM s ORDER BY name
SELECT * FROM sp ORDER BY qty DESC, sno
GROUP BY
SQL GROUP BY clause divides a table into sets and it
usually use with SQL aggregate functions which produces summary value for each
set.
SELECT department_id, count(employee_id) AS employee_count FROM employees
GROUP BY department_id
·
COUNT
-- count of rows
·
SUM --
arithmetic sum of numeric column
·
AVG --
arithmetic average of numeric column; should be SUM()/COUNT().
·
MIN --
minimum value found in column
·
MAX --
maximum value found in column
SELECT pno, MIN(sno), MAX(qty), AVG(qty), COUNT(DISTINCT sno) FROM sp GROUP BY pno
SQL HAVING clause
is a SQL WHERE clause for groups. Just as SQL WHERE clause limits rows, SQL
HAVING clause limits groups
SELECT sno, COUNT(*) parts FROM sp GROUP BY sno HAVING COUNT(*) > 1
INSERT INTO p (pno, color) VALUES ('P4', 'Brown')
INSERT INTO p (pno, color) select cno,colorname from colortable
INSERT INTO temp_table(name,salary) SELECT name,salary FROM employees WHERE salary > 2000
The UPDATE statement modifies columns in
selected table rows. It has the following general format:
UPDATE table-1 SET set-list [WHERE
predicate]
UPDATE sp SET qty = qty + 20
UPDATE s SET name = 'Tony', city = 'Milan' WHERE sno = 'S3'
The
DELETE Statement removes selected rows from a table. It has the following
general format:
DELETE FROM
table-1 [WHERE predicate]
DELETE FROM sp WHERE pno = 'P1'
DELETE FROM p WHERE pno NOT IN (SELECT pno FROM sp)
SQL TRUNCATE statement
SQL TRUNCATE statement is used to delete all data from a table without a WHERE clause. The logic inside SQL TRUNCATE statement is that: SQL TRUNCATE statement drops the table and recreate it again. This way is much faster than deleting rows one by one in some RDBMSs
TRUNCATE TABLE {table_name}
TRUNCATE TABLE employees
SQL UNION
operator
SQL UNION operator
is one of the basic set operations in the relational database. SQL UNION
operator allows you to combine the match result sets of two (or more than two)
select queries into a single table. Two result sets of the select queries must
be made up of same kind elements such as same number of column, and same data
type (or automatically cast to each other) in all columns
query1 UNION (ALL)
query2
SELECT * , 2006 AS
year FROM cost2007
UNION
SELECT * ,2007 AS
year FROM cost2006
SQL CASE
Expression
A special scalar
expression in SQL language is CASE expression. SQL CASE expression is used as a
kind of IF-THEN-ELSE statement. It is similar to switch statement in modern
programming language such as Java or C#.
CASE column_name
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE result
END
|
SELECT name,salary,
CASE
WHEN salary <= 2000 THEN 'low'
WHEN salary > 2000 AND salary <= 3000 THEN 'average'
WHEN salary > 3000 THEN 'high'
END AS salary_level
FROM employees
ORDER BY salary ASC
|
SQL MINUS or
EXCEPT
SQL MINUS or
EXCEPT operator work on two table expressions. The result set takes records
from the first table expression, and then subtract out the ones that appear in
the second table expression. If the second table expression includes the
records which are not appear in the first table expression, these records will
be ignored.
table_expression1
MINUS
table_expression2
|
table_expression1
EXCEPT
table_expression2
|
INTERSECT
INTERSECT operator
allows you to combine two table expressions into one and return a result set
which consists of rows that appear in the results of both table expressions.
INTERSECT operator, like UNION operator, removes all duplicated row from the
result sets. Unlike the UNION operator, INTERSECT operator operates as AND
operator on tables expression. It means data row appears in both table expression
will be combined in the result set while UNION operator operates as OR operator
(data row appear in one table expression or both will be combined into the
result set).
table_expression1
INTERSECT
table_expression2
SELECT *
FROM employees
WHERE department_id in (1,2)
INTERSECT
SELECT *
FROM employees
WHERE salary > 2500
SQL
Functions
The AVG()
Function
SELECT AVG(OrderPrice) AS OrderAverage FROM Orders
SQL COUNT()
SELECT COUNT(*) FROM table_name
MAX() Function
SELECT MAX(OrderPrice) AS LargestOrderPrice FROM
Orders
MIN() Function
SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM
Orders
SUM() Function
SELECT SUM(OrderPrice) AS OrderTotal FROM Orders
UCASE() / LCASE() Function
SELECT upper(LastName) as LastName,FirstName FROM
Persons
SELECT lower(LastName) as LastName,FirstName FROM
Persons
LEN() Function
SELECT LEN(Address) as LengthOfAddress FROM Persons
ROUND()
Function
SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice
FROM Products
Replace
Function
SELECT REPLACE(region_name, 'ast', 'astern') FROM
Geography;
Substring
select substring(name,1,3) from cust
Trim
SELECT LTRIM('
Sample ')
SELECT RTRIM('
Sample ')
DateAdd
Function
SELECT DATEADD(day, 10,'2000-01-05 00:05:00.000');
Datediff Function
DATEDIFF (datepart, expression1, expression2)
- year yy, yyyy day dd, d
- quarter qq,
q week wk, ww
- month mm, m hour hh
- dayofyear dy, y minute mi, n
- millisecond ms second ss, s
Datepart
Function
SELECT DATEPART (yyyy,'2000-01-20');
Getdate
Function
SELECT GETDATE();
SQL Join
The SQL JOIN
refers to using the JOIN keyword in a SQL statement in order to query data from
two tables.
Join Types
Depending on your
requirements, you can do an "inner" join or an "outer"
join. These are different in a subtle way
•INNER JOIN:
This will only return rows when there is at least one row in both tables that
match the join condition.
•LEFT OUTER
JOIN (or LEFT JOIN): This will return rows that have data in the left table
(left of the JOIN keyword), even if there's no matching rows in the right
table.
•RIGHT OUTER
JOIN (or RIGHT JOIN): This will return rows that have data in the right
table (right of the JOIN keyword), even if there's no matching rows in the left
table.
•FULL OUTER
JOIN (or FULL JOIN): This will return all rows, as long as there's matching
data in one of the tables.
Join Syntax
Inner Join:
SELECT * FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name =
table_name2.column_name
Left Join:
SELECT * FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name =
table_name2.column_name
Right Join:
SELECT * FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name =
table_name2.column_name
Full Join:
SELECT * FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name = table_name2.column_name
Example
Inner Join Statement
SELECT * FROM Individual
INNER JOIN Publisher
ON Individual.IndividualId =
Publisher.IndividualId
Note: We could use
table aliases instead of the full table name. This will keep our statement
shorter. For example:
SELECT * FROM Individual AS Ind
INNER JOIN Publisher AS Pub
ON Ind.IndividualId = Pub.IndividualId
Left Outer Join
SELECT * FROM Individual AS Ind
LEFT JOIN Publisher AS Pub
ON Ind.IndividualId = Pub.IndividualId
Right Outer
Join
SELECT * FROM Individual AS Ind
RIGHT JOIN Publisher AS Pub
ON Ind.IndividualId = Pub.IndividualId
Full Outer Join
SELECT * FROM Individual AS Ind
FULL JOIN Publisher AS Pub
ON Ind.IndividualId = Pub.IndividualId
VIEW
The view is a
virtual table, which can have the multiple columns from the one or more table.
It can be used like the normal table. Normally view cannot store the data
permanently in the table. When we create the view it stores the view definition
schema as object under the concern database.
When to use
VIEW?
When you have
complex queries, that use many places in the stored procedures or functions,
etc..,
It will be used as
security mechanism in the web applications. When we use the original table in
the web applications the hackers may drop the table. That time the original
data will be persist in the table.
When you want to
hide the particular columns to the specific people then we can create the
specialized view.
CREATE VIEW [View_Name]
AS
[SELECT
Statement]
CREATE VIEW SampleView
As
SELECT EmpID, EmpName
FROM EmpInfo
select * from SampleView
DROP VIEW SampleView
The SQL SELECT INTO Statement
The SELECT INTO statement selects data from one
table and inserts it into a different table.
The SELECT INTO statement is most often used to
create backup copies of tables.
SELECT *
INTO Persons_Backup FROM Persons
SELECT LastName,FirstName INTO Persons_Backup FROM Persons
SELECT LastName,Firstname INTO Persons_Backup FROM Persons WHERE
City='Sandnes'
SELECT Persons.LastName,Orders.OrderNo INTO Persons_Order_Backup
FROM Persons INNER JOIN
Orders
ON
Persons.P_Id=Orders.P_Id
