SQL DCL and TCL

Data Control Language:

DCL commands are used to GRANT and TAKE BACK authority from any database user.

• Grant
• Revoke

Grant
 It is used to give user access privileges to a database.

 Example:
 GRANT SELECT, UPDATE ON MY TABLE TO SOME_USER;

REVOKE: 

It is used to take back permissions from the user.

Example:
REVOKE SELECT, UPDATE ON MY TABLE FROM USER1, USER2;

 Transaction Control Language
 TCL commands can only use with DML commands  INSERT, DELETE and UPDATE only.
 These operations are automatically committed in the database that's why they cannot be used while create  tables or dropping them

• COMMIT 
• ROLLBACK
• SAVEPOINT

Commit: 

Commit command is used to save all the transaction the database. 

Syntax:
 COMMIT;

 

Example:
 DELETE FROM CUSTOMERS WHERE AGE = 25; 

COMMIT;

 Rollback
 Rollback command is used to undo transactions that have not already been saved to the database.

 

Syntax:

 ROLLBACK;

 Example:
 DELETE FROM CUSTOMERS WHERE AGE = 25; ROLLBACK;

 SAVEPOINT: 

It is used to roll transaction back to a certair point without rolling back th entire transaction.
 Syntax:
 SAVEPOINT SAVEPOINT_NAME;

SQL Views

SQL CREATE VIEW Statement

• In SQL, a view is a virtual table based on the result-set of an SQL statement.
• A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
• You can add SQL statements and functions to a view and present the data as if the data were coming from one single table.
• A view is created with the CREATE VIEW statement. 

Syntax:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example:

CREATE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = 'Brazil';

SQL Updating a View

A view can be updated with the CREATE OR REPLACE VIEW statement.

Syntax:

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

SQL Dropping a View

A view is deleted with the DROP VIEW statement.

Syntax:

Drop view view_name;

Creating View from multiple tables

View from multiple tables can be created by simply include multiple tables in the SELECT statement. In the given example, a view is created named MarksView from two tables Student_Detail and Student_Marks. 

CREATE VIEW MarksView AS
SELECT Student_Detail.NAME, Student_Detail.ADDRESS, Student_Marks.MARKS
FROM Student_Detail, Student_Mark
WHERE Student_Detail.NAME = Student_Marks.NAME;

SELECT * FROM MarksView;

SQL DDL commands

DDL:

• Data Definition Language or DDL changes the structure of the table like creating a table, deleting a table, altering a table, etc.
• All the command of DDL are auto-committed that means it permanently save all the changes in the database.
•  Here are some commands that come under DDL: 
• CREATE 
• ALTER 
• DROP 
• TRUNCATE

 CREATE

 CREATE It is used to create a new table in the database.

 Syntax:

 CREATE TABLE table_name (

    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

 Example:

CREATE TABLE EMPLOYEE(

Name VARCHAR2(20), 

Email VARCHA R2(100), 

DOB DATE);

Create Table Using Another Table:

A copy of an existing table can also be created using CREATE TABLE.

The new table gets the same column definitions. All columns or specific columns can be selected.

If you create a new table using an existing table, the new table will be filled with the existing values from the old table.

Syntax:

CREATE TABLE new_table_name AS
    SELECT column1, column2,...
    FROM existing_table_name
    WHERE ....;

Example:

CREATE TABLE TestTable AS
SELECT customername, contactname
FROM customers;

Drop

It is used to delete both the structure and record stored in the table.

 Syntax: 

DROP TABLE  table_name;

 Example:

 DROP TABLE EMPLOYEE;

 ALTER

 It is used to alter the structure of the database. This change could be either to modify the characteristics of an existing attribute or probably to add a new attribute.

 Syntax:

Add columm:

 ALTER TABLE table_name

ADD column_name datatype;

Example:

ALTER TABLE Customers
ADD Email varchar(255);

Drop column:

ALTER TABLE table_name
DROP COLUMN column_name;

Example:

ALTER TABLE Customers
DROP COLUMN Email;

Rename column:

ALTER TABLE table_name
RENAME COLUMN old_name to new_name;

Modify column data type:

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

 Example:

ALTER TABLE STU_DETAILS 

MODIFY column NAME VARCHAR2(20);

TRUNCATE

 It is used to delete all the rows from the table and free the space containing the table.

 Syntax:

 TRUNCATE TABLE table_name;

 Example:

 TRUNCATE TABLE EMPLOYEE;

SQL Sub Query

A subquery or inner query or nested query is a query within another SQL query. 

We can use subquery with select, update, insert and delete statements.

In the subquery, order by command cant be used, but Group by command can be used to perform the same function as order by command.

A subquery can be placed as follow:

• Where clause
• From clause
• Having clause

Syntax:

Select columns From table_name

Where col_nane operator (subquery).

Examples with Where clause:

Get names of all the students who scored more than class average marks.

Select name, marks from student

Where marks > (select avg(marks) from student);

Find the names of all students with even roll numbers.

Select name, rollno from student

Where rollno in (select rollno from student where rollno % 2 = 0);

Example with From clause:

Find the max numbers from the students of Delhi.

Select max(marks) 

From (select * from student where city = 'Delhi' ) as temp;

Example with select:

Select (select max(marks) from student ), name

From student.

SQL Set Operations

The SQL Set operations is used to combine the two or more sql statements.

Types of Set operations:

• Union
• Unionall
• Intersect
• Minus

Union Operation:

• The SQL Union operation is used to combine the result of two or more SQL SELECT queries.
• In the union operation, all the number of datatype and columns must be same in both the tables on which UNION operation is being applied. 
• The columns in every SELECT statement must also be in the same order
• The union operation eliminates the duplicate rows from its resultset.

 Syntax

 SELECT Column_name FROM table1 

UNION

 SELECT column_name FROM table2;

 Example

 SELECT * FROM First 

UNION

 SELECT * FROM Second;

Union all operation:

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

Intersect Operation

• It is used to combine two SELECT statements. 
• The Intersect operation returns the common rows from both the SELECT statements.
• In the Intersect operation, the number of datatype and columns must be the same.
• It has no duplicates and it arranges the data in ascending order by default.

Syntax

SELECT column_name FROM table1

INTERSECT

SELECT column_name FROM table2;

Example

SELECT * FROM First

INTERSECT

SELECT * FROM Second;

MINUSOperation

• It combines the result of two SELECT statements. 
• Minus operator is used to display the rows which are present in the first query but absent in the second query.
• It has no duplicates and data arranged in ascending order by default.

Syntax

SELECT column_name FROM table1

MINUS

SELECT column_name FROM table2;

Example

SELECT * FROM First

MINUS

SELECT * FROM Second

SQL Joins | Inner join | Left Join | Right Join | Full Join | Self Join

SQL JOIN:

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Different Types of SQL JOINs

Here are the different types of the JOINs in SQL:

• (INNER) JOIN: Returns records that have matching values in both tables
• LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
• RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
• FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

INNER JOIN:

Returns records that have matching values in both tables.

Syntax:

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

Example:

SELECT * FROM STUDENT

INNER JOIN COURSE

OJ STUDENT.ID =COURSE.ID;

Left Join:

Returns all records from the left table and the matched records from the right table.

Syntax:

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

Example:

SELECT * FROM STUDENT AS S

LEFT JOIN COURSE C

ON S.ID =C.ID

Right Join:

Returns all records from the right table and the matched records from thebleft table.

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

Example:

SELECT * FROM STUDENT AS S

RIGHT JOIN COURSE C

ON S.ID =C.ID

Full Join:

Returns all records when there is a match in either left or right table.

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

Example:

Select * from student s

Full join course c

On s.id =c.id

Self join:

A self join is a regular join, but the table is joined with itself.

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

Or

Select columns

From table as a

JOIN table as b

On a.col_name = b.col_name;

Example

Select a.name as manager_name, b.name

From employee as a 

Join employee as b

On a.id =v

Manager_id;

Left and right exclusive joins:

Left exclusive join:

Select * from student as a

Left join course as b

On a.id = b.id

Where b.id is null

SQL - Aggregate functions

An aggregate function is a function that performs a calculation on a set of values, and returns a single value.

Aggregate functions are often used with the GROUP BY clause of the SELECT statement. The GROUP BY clause splits the result-set into groups of values and the aggregate function can be used to return a single value for each group.

The most commonly used SQL aggregate functions are:

• MIN() - returns the smallest value within the selected column
• MAX() - returns the largest value within the selected column
• COUNT() - returns the number of rows in a set
• SUM() - returns the total sum of a numerical column
• AVG() - returns the average value of a numerical column

SELECT MIN(Price)
FROM Products;

SELECT MAX(Price)
FROM Products;

Set Column Name (Alias):

When you use MIN() or MAX(), the returned column will not have a descriptive name. To give the column a descriptive name, use the AS keyword

SELECT MIN(Price) AS SmallestPrice
FROM Products;

SELECT MIN(Price) AS SmallestPrice, CategoryID
FROM Products
GROUP BY CategoryID;

SELECT COUNT(*)
FROM Products;

Note: If you specify a column name instead of (*), NULL values will not be counted.

SELECT OrderID, SUM(Quantity) AS [Total Quantity]
FROM OrderDetails
GROUP BY OrderID;

SELECT AVG(Price) AS AveragePrice, CategoryID
FROM Products
GROUP BY CategoryID;