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
- CREATE
Creates a new table, a view of a table, or other object in the database.
- ALTER
Modifies an existing database object, such as a table.
- DROP
Deletes an entire table, a view of a table or other objects in the database.
DML Data Manipulation Language
Command & Description
- SELECT
Retrieves certain records from one or more tables.
- INSERT
Creates a record.
- UPDATE
Modifies records.
- DELETE
Deletes records.
DCL Data Control Language
Command & Description
- GRANT
Gives a privilege to user.
- 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
- ALL
The ALL operator is used to compare a value to all values in another value set.
- AND
The AND operator allows the existence of multiple conditions in an SQL statements WHERE clause.
- ANY
The ANY operator is used to compare a value to any applicable value in the list as per the condition.
- 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.
- EXISTS
The EXISTS operator is used to search for the presence of a row in a specified table that meets a certain criterion.
- IN
The IN operator is used to compare a value to a list of literal values that have been specified.
- LIKE
The LIKE operator is used to compare a value to similar values using wildcard operators.
- 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.
- OR
The OR operator is used to combine multiple conditions in an SQL statements WHERE clause.
- IS NULL
The NULL operator is used to compare a value with a NULL value.
- 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;
Here is the result set:
Top comments (0)