{tocify} $title={Table of Contents}
SQL
is Structured Query Language, which is a computer language for storing,
manipulating and retrieving data stored in a relational database.
SQL
is the standard language for Relational Database System.
DDL — Data Definition Language
Command
& Description
1. CREATE
Creates
a new table, a view of a table, or other object in the database.
2. ALTER
Modifies
an existing database object, such as a table.
3. DROP
Deletes
an entire table, a view of a table or other objects in the database.
DML — Data Manipulation Language
Command
& Description
1.SELECT
Retrieves
certain records from one or more tables.
2.INSERT
Creates
a record.
3.UPDATE
Modifies
records.
4.DELETE
Deletes
records.
DCL
— Data Control Language
Command
& Description
1. GRANT
Gives
a privilege to user.
2.REVOKE
Takes
back privileges granted from user.
a. Constraints:
Following
are some of the most commonly used constraints available in SQL −
·
NOT NULL Constraint − Ensures that a column cannot have a NULL value.
·
DEFAULT Constraint − Provides a default value for a column when none is
specified.
·
UNIQUE Constraint − Ensures that all the values in a column are different.
·
PRIMARY Key − Uniquely identifies each row/record in a database table.
·
FOREIGN Key − Uniquely identifies a row/record in any another database table.
·
CHECK Constraint − The CHECK constraint ensures that all values in a column
satisfy certain conditions.
·
INDEX − Used to create and retrieve data from the database very quickly.
b. SYNTAX:
SQL SELECT Statement
SELECT column1, column2....columnN
FROM table_name;
SQL
DISTINCT Clause
SELECT DISTINCT column1, column2....columnN
FROM table_name;
SQL
WHERE Clause
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION;
SQL
AND/OR Clause
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION-1 {AND|OR} CONDITION-2;
SQL
IN Clause
SELECT column1, column2....columnN
FROM table_name
WHERE column_name IN (val-1, val-2,...val-N);
SQL
BETWEEN Clause
SELECT column1, column2....columnN
FROM table_name
WHERE column_name BETWEEN val-1 AND val-2;
SQL
LIKE Clause
SELECT column1, column2....columnN
FROM table_name
WHERE column_name LIKE { PATTERN };
SQL
ORDER BY Clause
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION
ORDER BY column_name {ASC|DESC};
SQL
GROUP BY Clause
SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name;
SQL
COUNT Clause
SELECT COUNT(column_name)
FROM table_name
WHERE CONDITION;
SQL
HAVING Clause
SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name
HAVING (arithematic function condition);
SQL
CREATE TABLE Statement
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
);
SQL
DROP TABLE Statement
DROP TABLE table_name;
SQL
CREATE INDEX Statement
CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,...columnN);
SQL
DROP INDEX Statement
ALTER TABLE table_name
DROP INDEX index_name;
SQL
DESC Statement
DESC table_name;
SQL
TRUNCATE TABLE Statement
TRUNCATE TABLE table_name;
SQL ALTER TABLE Statement
ALTER TABLE table_name {ADD|DROP|MODIFY} column_name {data_ype};
SQL
ALTER TABLE Statement (Rename)
ALTER TABLE table_name RENAME TO new_table_name;
SQL
INSERT INTO Statement
INSERT INTO table_name( column1, column2....columnN)
VALUES ( value1, value2....valueN);
SQL
UPDATE Statement
UPDATE table_name
SET column1 = value1, column2 = value2....columnN=valueN
[ WHERE CONDITION ];
SQL
DELETE Statement
DELETE FROM table_name
WHERE {CONDITION};
SQL
CREATE DATABASE Statement
CREATE DATABASE database_name;
SQL
DROP DATABASE Statement
DROP DATABASE database_name;
SQL
USE Statement
USE database_name;
SQL
COMMIT Statement
COMMIT;
SQL
ROLLBACK Statement
ROLLBACK;
c.
SQL Logical Operators
Here
is a list of all the logical operators available in SQL.
Operator
& Description
1.ALL
The
ALL operator is used to compare a value to all values in another value set.
2.AND
The
AND operator allows the existence of multiple conditions in an SQL statement’s
WHERE clause.
3.ANY
The
ANY operator is used to compare a value to any applicable value in the list as
per the condition.
4.BETWEEN
The
BETWEEN operator is used to search for values that are within a set of values,
given the minimum value and the maximum value.
5.EXISTS
The
EXISTS operator is used to search for the presence of a row in a specified
table that meets a certain criterion.
6.IN
The
IN operator is used to compare a value to a list of literal values that have
been specified.
7.LIKE
The
LIKE operator is used to compare a value to similar values using wildcard
operators.
8.NOT
The
NOT operator reverses the meaning of the logical operator with which it is
used. Eg: NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is a negate operator.
9.OR
The
OR operator is used to combine multiple conditions in an SQL statement’s WHERE
clause.
10.IS
NULL
The
NULL operator is used to compare a value with a NULL value.
11.UNIQUE
The
UNIQUE operator searches every row of a specified table for uniqueness (no duplicates).
d.
Create Table syntax:
The
basic syntax of the CREATE TABLE statement is as follows −
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
…..
columnN datatype,
PRIMARY KEY( one or more columns )
);
The
SQL INSERT INTO syntax will be as follows −
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,…valueN);
The
basic syntax of the UPDATE query with a WHERE clause is as follows −
UPDATE table_name
SET column1 = value1, column2 = value2…., columnN = valueN
WHERE [condition];
DELETE FROM table_name
WHERE [condition];
The basic syntax of % and _ is as follows −
SELECT FROM table_name
WHERE column LIKE ‘XXXX%’
or
SELECT FROM table_name
WHERE column LIKE ‘%XXXX%’
or
SELECT FROM table_name
WHERE column LIKE ‘XXXX_’
or
SELECT FROM table_name
WHERE column LIKE ‘_XXXX’
or
SELECT FROM table_name
WHERE column LIKE ‘XXXX’
The
basic syntax of the TOP clause with a SELECT statement would be as follows.
SELECT TOP number|percent column_name(s)
FROM table_name
WHERE [condition]
GROUP
BY clause must follow the conditions in the WHERE clause and must precede the
ORDER BY clause if one is used.
SELECT column1, column2
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2
ORDER BY column1, column2
The
basic syntax of DISTINCT keyword to eliminate the duplicate records is as
follows −
SELECT DISTINCT column1, column2,…..columnN
FROM table_name
WHERE [condition]
JOINS:
There
are different types of joins available in SQL −
·
INNER JOIN − returns rows when there is a
match in both tables.
·
LEFT JOIN − returns all rows from the left
table, even if there are no matches in the right table.
·
RIGHT JOIN − returns all rows from the
right table, even if there are no matches in the left table.
·
FULL JOIN − returns rows when there is a
match in one of the tables.
·
SELF JOIN − is used to join a table to
itself as if the table were two tables, temporarily renaming at least one table
in the SQL statement.
·
CARTESIAN JOIN − returns the Cartesian
product of the sets of records from the two or more joined tables.
INDEX:
Single-Column Indexes
A
single-column index is created based on only one table column. The basic syntax
is as follows.
CREATE INDEX index_name
ON table_name (column_name);
Unique
Indexes
Unique
indexes are used not only for performance, but also for data integrity. A
unique index does not allow any duplicate values to be inserted into the table.
The basic syntax is as follows.
CREATE UNIQUE INDEX index_name
on table_name (column_name);
Composite
Indexes
A
composite index is an index on two or more columns of a table. Its basic syntax
is as follows.
CREATE INDEX index_name
on table_name (column1, column2);
Implicit
Indexes
Implicit
indexes are indexes that are automatically created by the database server when
an object is created. Indexes are automatically created for primary key
constraints and unique constraints.
RANK:
We
have the following rank functions.
• ROW_NUMBER()
-ROW_Number() SQL RANK function to get a unique sequential number for each row
• RANK()
• DENSE_RANK()
— if we have duplicate values, SQL assigns different ranks to those rows as
well. Ideally, we should get the same rank for duplicate or similar values.
• NTILE()
Using
SQL Server RANK() function over a result set example
SELECT
product_id,
product_name,
list_price,
RANK () OVER (
ORDER BY list_price DESC
) price_rank
FROM
production.products;