SQL

Structured Query Language

What is SQL?

SQL stands for Structured Query Language
SQL lets you access and manipulate databases
SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987

DDL (Data Definition Language)

DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema.

It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database.

DDL is a set of SQL commands used to create, modify, and delete database structures but not data.

List of DDL commands:

CREATE: This command is used to create the database or its objects (like table, index, function, views, store procedure, and triggers).

CREATE TABLE Employee

(Employee Id INTEGER PRIMARY KEY,
First name CHAR (50) NULL,
Last name CHAR (75) NOT NULL);

DROP: This command is used to delete objects from the database.

drop table table_name;

ALTER: This is used to alter the structure of the database.

ALTER TABLE table_name ADD PRIMARY KEY (col_name);

Data Types in SQL

Characters: CHAR(size), VARCHAR(size),Text

Numbers: INT, BIGINT, SMALLINT, FLOAT
Others: MONEY, DATE,TIME,Boolean
Arrays

A tuple = a record – Restriction: all attributes are of atomic type

A table = a set of tuples – Like a list… – …but it is unorderd: no first(), no next(), no last().

Types of Set Operation

Union
UnionAll
Intersect
Minus

1. Union

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.
SYNTAX:- SELECT column_name FROM table1 UNION SELECT column_name FROM table2;

2. Union All

Union All operation is equal to the Union operation. It returns the set without removing duplication and sorting the data.

Syntax:

SELECT column_name FROM table1
UNION ALL
SELECT column_name FROM table2;

3. Intersect

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.
Syntax
1. SELECT column_name FROM table1
2. INTERSECT
3. SELECT column_name FROM table2;

4. Minus

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:
1. SELECT column_name FROM table1
2. MINUS
3. SELECT column_name FROM table2;

SQL Aggregate Functions

SQL aggregation function is used to perform the calculations on multiple rows of a single column of a table. It returns a single value.
It is also used to summarize the data.

Types of SQL Aggregation Function

Sample table:

PRODUCT_MAST

PRODUCT	COMPANY	QTY	RATE	COST
Item1	Com1	2	10	20
Item2	Com2	3	25	75
Item3	Com1	2	30	60
Item4	Com3	5	10	50
Item5	Com2	2	20	40
Item6	Cpm1	3	25	75
Item7	Com1	5	30	150
Item8	Com1	3	10	30
Item9	Com2	2	25	50
Item10	Com3	4	30	120

1. COUNT FUNCTION

COUNT function is used to Count the number of rows in a database table. It can work on both numeric and non-numeric data types
Example: COUNT with WHERE
1. SELECT COUNT(*)
2. FROM PRODUCT_MAST;
3. WHERE RATE>=20;
Output:
```
7
```

2. SUM Function

Sum function is used to calculate the sum of all selected columns. It works on numeric fields only.

Example: SUM()

SELECT SUM(COST)
FROM PRODUCT_MAST;

Output:670

3. AVG function

The AVG function is used to calculate the average value of the numeric type. AVG function returns the average of all non-Null values

Example:

SELECT AVG(COST)
FROM PRODUCT_MAST;

Output:

67.00

4. MAX Function

MAX function is used to find the maximum value of a certain column. This function determines the largest value of all selected values of a column.

Syntax

SELECT MAX(RATE)  
FROM PRODUCT_MAST;  

5. MIN Function

MIN function is used to find the minimum value of a certain column. This function determines the smallest value of all selected values of a column.

Example:

SELECT MIN(RATE)
FROM PRODUCT_MAST;

Output:

Nested query

A Subquery or Inner query or a Nested query is a query within another SQL query and embedded within clauses, most commonly in the WHERE clause. It is used to return data from a table, and this data will be used in the main query as a condition to further restrict the data to be retrieved.

Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.

INSERT:

The INSERT statement is a SQL query. It is used to insert data into the row of a table.
Syntax: INSERT INTO TABLE_NAME(col1, col2, col3,.... col N) VALUES (value1, value2, v alue3, .... valueN);

UPDATE:

This command is used to update or modify the value of a column in the table.
Syntax: UPDATE table_name SET [column_name1= value1,...column_nameN =valueN [WHE RE CONDITION]

DELETE:

It is used to remove one or more row from a table.
Syntax: DELETE FROM table_name [WHERE condition];

What is a SQL join?

A SQL Join is a special form of generating a meaningful data by combining multiple tables relate to each other using a “Key”. Typically, relational tables must be designed with a unique column and this column is used to create relationships with one or more other tables.

SQL inner join
1. Equi join
2. Non-equi join (Theta join)
SQL outer join
1. SQL left join or left outer join
2. SQL right join or right outer join
3. SQL full join or full outer join

DBMS Notes

Search This Blog

SQL