What is SQL?

SQL is Structured Query Language, which is a computer language for storing, manipulating
and retrieving data stored in relational database.

[Link] Data Definition Language:

CREATE:

ALTER:

DROP:

TRUNCATE:

[Link] Data Manipulation Language:

INSERT :

UPDATE :

[Link] Data Control Language:

GRANT :

REVOKE :

Example: GRANT SELECT, UPDATE ON employees TO user_name;

[Link] Data Query Language:

SELECT :

NULL value is different than a zero value or a field that contains spaces.

A field with a NULL value is one that has been left blank during record creation.

SQL Constraints:

Constraints are the rules enforced on data columns on table.

[Link]: Provides a default value for a column when none is specified

[Link]: Ensures that all values in a column are different.

Syntax: ALTER TABLE CUSTOMERS MODIFY AGE INT NOT NULL UNIQUE;

[Link] Key: Uniquely identified each rows/records in a database table. When multiple
fields are used as a primary key, they are called a composite key.

Syntax: ALTER TABLE CUSTOMERS

ADD CONSTRAINT PK_CUSTID PRIMARY KEY (ID, NAME);

or

ALTER TABLE CUSTOMER ADD PRIMARY KEY (ID);

[Link] Key: Uniquely identified a rows/records in any another database table.

Syntax: ALTER TABLE ORDERS ADD FOREIGN KEY (Customer_ID) REFERENCES

[Link] Constraint: The CHECK constraint ensures that all values in a column satisfy
certain conditions.

[Link]: Use to create and retrieve data from the database very quickly.

Syntax: CREATE INDEX index_name ON table_name ( column1, column2.....);

CREATE TABLE CUSTOMERS(

ID INT NOT NULL,

NAME VARCHAR (20) NOT NULL,

AGE INT NOT NULL CHECK (AGE >= 18),

ADDRESS CHAR (25) ,

SALARY DECIMAL (18, 2) DEFAULT 5000.00,

PRIMARY KEY (ID) );

ID INT NOT NULL,

DATE DATETIME,

CUSTOMER_ID INT references CUSTOMERS(ID),

AMOUNT double,

PRIMARY KEY (ID) );

SQL Syntax:

SELECT column1, column2....columnN FROM table_name

WHERE CONDITION-1 {AND|OR} CONDITION-2;

WHERE column_name IN (val-1, val-2,...val-N);

WHERE column_name BETWEEN val-1 AND val-2;

WHERE column_name LIKE { PATTERN };

Syntax Order:

SELECT

FROM

WHERE CONDITION

GROUP BY column_name

HAVING (arithematic function condition);

ORDER BY column_name {ASC|DESC};

NOTE:
a) We cannot use Aggregate function like sum, count, min, max avg in WHERE Clause, but
we can in Having clause also in ORDER BY

b) Group by should be included when aggregate function is used in SELECT. All or required
columns apart from aggregate function should be present in group By. If there's only
aggregate column in SELECT Group BY is not mandatory

Example: SELECT EMPLOYEE_CODE,SUM(SALARY) FROM EMP_SAL

GROUP BY EMPLOYEE_CODE

or

SELECT SUM(SALARY) FROM EMP_SAL

WHERE EMPLOYEE_CODE='ABC'

[Link]: TRUNCATE TABLE table_name

TRUNCATE is a DDL command

We cannot use WHERE clause with TRUNCATE.

TRUNCATE removes all rows from a table.

[Link]: DELETE FROM table_name

OR

DELETE FROM TABLE NAME WHERE ID>10

DELETE is a DML command.

We can use where clause with DELETE to filter & delete specific records. Table
stucture will not change

[Link]: DROP TABLE table_name;

The DROP command removes a table from the database.

All the tables' rows, indexes and privileges will also be removed.

DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command.

INSERT Syntax:
INSERT INTO table_name( column1, column2....columnN) VALUES ( value1,
value2....valueN);

UPDATE Syntax:

UPDATE table_name

SET column1 = value1, column2 = value2....columnN=valueN

[ WHERE CONDITION ];

SQL Logical Operators:

[Link]: The AND operator allows the existence of multiple conditions in an SQL
statement's WHERE clause.

[Link]: The BETWEEN operator is used to search for values that are within a set of
values, given the minimum value and the maximum value.

[Link]: The EXISTS operator is used to search for the presence of a row in a specified
table that meets certain criteria.

[Link]: The IN operator is used to compare a value to a list of literal values that have been
specified.

[Link]: The LIKE operator is used to compare a value to similar values using wildcard
operators.

[Link]: 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.

[Link]: The OR operator is used to combine multiple conditions in an SQL statement's

[Link] NULL: The NULL operator is used to compare a value with a NULL value.

[Link]: The UNIQUE operator searches every row of a specified table for uniqueness
(no duplicates).

SQL Arithmetic Operators:

+*-/%
SQL Comparison Operators:

= ,!= ,<> ,> ,< ,>= ,<=

SELECT GETDATE();

CREATE DATABASE DatabaseName;

SELECT TOP number|percent column_name(s) FROM table_name

SELECT DISTINCT column1, column2,.....columnN FROM table_name

-- REPLICATE EXISTING TABLE --

CREATE TABLE TABLE_NAME

SELECT * FROM TABLE

INSERT INTO TABLE_NAME

SELECT * FROM TABLE_NAME (COLUMN AND DATA_TYPE SHOULD BE SAME)

SQL JOINS

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.
SQL INTERSECT Clause: is used to combine two SELECT statements, but returns rows only
from the first SELECT statement that are identical to a row in the second SELECT
statement.

SQL EXCEPT Clause : combines two SELECT statements and returns rows from the first
SELECT statement that are not returned by the second SELECT statement.

Types of Index :

1. Clustered Index:

clustered index alters the way that the rows are physically stored. When you create
a clustered index on a column (or a number of columns), the SQL server sorts the table’s
rows by that column(s).

It is like a dictionary, where all words are sorted in an alphabetical order. Note, that
only one clustered index can be created per table. It alters the way the table is physically
stored, it couldn’t be otherwise.

syntax: CREATE CLUSTERED INDEX [INDEX_NAME] ON [table_name] ([column]

2. Non Clustered Index:

A non-clustered index, on the other hand, does not alter the way the rows are stored
in the table. Instead, it creates a completely different object within the table, that contains
the column(s) selected for indexing and a pointer back to the table’s rows containing the
data.

It is like an index in the last pages of a book. All keywords are sorted and contain a
reference back to the appropriate page number.

syntax: CREATE NONCLUSTERED INDEX [INDEX_NAME] ON [table_name] ([column]

CREATE INDEX _index_name_ ON _table_name_ (_column_name_)

Clustered indexes VS non-clustered indexes

PROS: Fast to return large range of data

Fast for presorted results

CONS: Only one clustered index per table

[Link]-clustered indexes:

PROS: Many non-clustered indexes per table

CONS: Generally slower than clustered indexes due to bookmark lookup

Not recommended for returning large data sets

SQL View:

view is actually a composition of a table in the form of a predefined SQL query. Summarize
data from various tables which can be used to generate reports.

Restrict access to the data such that a user can see and (sometimes) modify exactly what
they need and no more.

Syntax:

CREATE VIEW view_name AS

SELECT column1, column2..... FROM table_name WHERE [condition];

Cars