Introduction to SQL

What is SQL?

. Stands for Structured Query Language.

. Used to perform operations on database like creating, updating, deleting tables or records.

. It is just a language which helps us to use database using commands.

. We need to install any of the databases like Oracle or My-SQL to use SQL.

Types of Commands in SQL:

1. Data Definition Language(DDL):

. Used to create, modify and drop structure of database objects.
. Commands: CREATE, ALTER, DROP, TRUNCATE

CREATE:

. CREATE command is used to create a table.

. Syntax:

CREATE TABLE table_name(col1 datatype, col2 datatype,.....,coln datatype);

2. Data Manipulation Language(DML):

. Used to store, modify data from databases.
. Commands: INSERT, UPDATE, DELETE

INSERT:

. INSERT command is used to insert the new records into the table.

. Syntax:
INSERT INTO table_name VALUES(val1, val2,.....,valn);

UPDATE:

. UPDATE statement is used to modify the existing records in table.

. Syntax:

UPDATE table_name

SET col1=val1, col2=val2

WHERE condition;

DELETE:

. Used to delete existing records in the table.

. Syntax: To delete according to any condition:

DELETE FROM table_name

WHERE condition;

. Syntax: To delete complete records in the table:

DELETE FROM table_name;

3. Data Query Language(DQL):

. Used to retrieve data from the databases.

. Only one command- SELECT

SELECT:

. SELECT statement is used to read data from the tables.

. The data from the database is called as result-set.

. Syntax: To read all the records from the table:

SELECT *

FROM table_name;

. Syntax: To read particular column data from the table:

SELECT col1, col2, col3...

FROM table_name;

4. Transaction Control Language(TCL):

. Used to handle changes that effect databases like commiting, setting a point and rollback to previous change.
. Commands: SAVEPOINT, ROLLBACK, COMMIT

5. Data Control Language(DCL):

. Used to implement security on database objects.
. Commands: GRANT, REVOKE

6. Other commands:

WHERE:

. WHERE clause is used to filter the records.

. Used to extract only those records that fulfill a specified condition.

. WHERE can be used in other commands like UPDATE, DELETE etc.

. Syntax:

SELECT column(s)

FROM table_name

WHERE condition;

AND, OR, NOT Operators:

. WHERE can be combined with AND, OR and NOT.

. AND and OR operators are used to filter the records based on the conditon.

. AND displays a record if all conditions are True.

. OR displays a record if any of the conditions is True.

. NOT displays a record if condition is False.
. Syntax: For AND, OR:

SELECT column(s)

FROM table_name

WHERE condition1 AND condition2 AND condition3;

. Syntax: For NOT:

SELECT column(s)

FROM table_name

WHERE NOT condition;

LIKE Operator:

. WHERE clause uses LIKE operator to search for a specified pattern in a column.

. % represents zero, one or multiple characters.

. _ represents single character.

. Syntax:

SELECT column(s)

FROM table_name

WHERE column LIKE pattern;

. Example: WHERE name LIKE 'a%' returns all names starting with 'a'.

WHERE name LIKE '_r%' returns names where 'r' is in 2nd position.

IN Operator:

. Used to specify multiple values in WHERE clause.

. Can be used in place of 'OR' condition.

. Syntax:

SELECT column(s)

FROM table_name

WHERE column_name IN(val1, val2);

BETWEEN Operator:

. Used in combination with WHERE clause.

. BETWEEN operator selects values within given range.

. Values can be numbers, text or dates.

. Begin and End values are included in the range.

. Syntax:

SELECT column(s)

FROM table_name

WHERE column_name BETWEEN val1 AND val2;

ORDER BY:

. It is a keyword used to sort the result in ascending or descending order.

. Sorts in ascending order by default, but to sort in descending order use DESC keyword.

. Syntax:

SELECT column(s)

FROM table_name

ORDER BY column_name ASC/DESC;

FUNCTIONS:

. MIN() returns smallest value of selected column.

. MAX() returns largest value of selected column.

. COUNT() returns number of rows that matches condition

. AVG() returns average values of a numeric column.

. SUM() returns total sum of numeric column.

. Syntax: MIN()

SELECT MIN(column_name)

FROM table_name;

. Syntax: MAX()

SELECT MAX(column_name)

FROM table_name;

. Syntax: COUNT()

SELECT COUNT(column_name)

FROM table_name;

. Syntax: AVG()

SELECT AVG(column_name)

FROM table_name;

. Syntax: SUM()

SELECT SUM(column_name)

FROM table_name;

GROUP BY:

. Often used with aggregate functions like COUNT, MAX, MIN, SUM, AVG to group result-set by one or more columns.

. Syntax:

SELECT column(s)

FROM table_name

WHERE condition

GROUP BY column

ORDER BY column;

HAVING:

. WHERE keyword cannot be used with aggregate functions.

. So, to filter aggregate function values, we use HAVING clause.

. Syntax:

SELECT column(s)

FROM table_name

WHERE condition

GROUP BY column

HAVING condition

ORDER BY column;

What are Constraints?

. These are used to limit the data that goes into the table.

. Can be applied at column level or table level.

1. NOT NULL:

  . Ensures that column cannot have a null value.
  . Example: CREATE TABLE emp(id int NOT NULL, name varchar(20));

2. DEFAULT:

. Provides a default value when no data is specified.
. Example: CREATE TABLE transaction(id int, sent_on date DEFAULT);

3. PRIMARY KEY:

. Doesn't allow duplicate and null value in the column.
. It helps in reducing the duplicate values so that during referential time, it is easy to refer any data.
. Primary key can be assigned only once in a table.
. It is treated as a parent table when it contains a primary key.
. Example: CREATE TABLE employee(empID int Primary key, name varchar(20));

4. FORIEGN KEY:

. References the the data from the table assigned with primary key.
. Data in the column assigned with Foreign Key can contain duplicate or null values.
. It is mandatory that the data should match with the column assigned with Primary key in its parent table.
. A table with a foreign key is considered as a child table.
. When relation is established between two tables, we cannot perform updation and deletion on the Parent table directly, so we need to define Foreign key with ON UPDATE/DELETE CASCADE rules.
. Example: CREATE TABLE department( depID int, name varchar(20), empID int Foreign Key REFERENCES employee(empID);

5. UNIQUE:

. Ensures there is no duplicate data but allows null values.
. A table may contain number of UNIQUE constraints declared.
. Example: CREATE TABLE emp(ID int UNIQUE, name varchar(20));

6. CHECK:

. Ensures all values in column satisfies a condition.
. Example: CREATE TABLE voter(Id int Primary key, name varchar(20), age int CHECK(age>=18));

What is NORMALIZATION?

. It is a technique to organize the contents of tables by reducing redundancy.
. Involves dividing a database into two or more tables, defining a relationship between them.
. Main objective is to isolate data, so that any modification can be made in just one table.
. We can extract data from any table using the relationships among tables.

Advantages:

. Otains storage efficiency.

. Increases consistency.

. Reduces redundancy.

. Fewer null values so that it obtains referential integrity.

Disadvantages:

. Slower performance.

. Requires more joins to get desired result.

. Higher the level of normalization, greater the number of tables in database.

Levels of Normalization:

1. First Normal Form(1 NF):

. It ensures that every column is unique.

. Data in the fields must be single values.

. Data should be entered into the column of same type.

. In the above example, 1st table is with multiple values initially.

. As per 1 NF, data must be singular.

2. Second Normal Form(2 NF):

. A database is said be in 2 NF if it satisfies the following:

. It must be in 1 NF.

. Repeating data should not be present.

. There should not be partial dependency.

. In the above example, 1st table consists of repetitive information of HOD and Tel for each and every student.

. So, this can be divided into another table called Branch table and it can be referenced by Branch column.

3. Third Normal Form(3 NF):

. A database is said to be in 3 NF if it satisfies the following:

. It must be in 2 NF.

. All non Primary key columns must be dependent on Primary key column.

. Whatever the columns which are not dependent directly on Primary key should be moved to another table.

. It means transitive dependency of columns should be avoided.

  . In the above example, Details Table has Primary Key Id, on which only few of the columns are dependent on.
  . B_Code depends on Bank, Postal Code depends on Address.
  . So dividing the tables in such a way that each non primary key is dependent on primary key.

4. Boyce Codd Normal Form (BCNF):

  . It is advanced version and stricter than 3 NF.
. A database is said to be in BCNF if it satisfies the following:
. It must satisfy 3 NF.
. For every functional dependency X->Y, X must be the superkey in the table.
. The dependencies must be in numerical form for faster access.

. In the above example, Employee table has fields like Dept_Id, Incharge which are not functionally dependent on Emp_id.
. So, tables must be divided as per they maintain functional dependency on a key.
. Finally, mapping table is required for quicker access to the other fields.

What are JOINS?

. Joins are used to combine records from different table which have similar fields.
. Let us consider two tables 'employee' and 'department' with a common column called 'EmpId'.

. There are different types of joins as listed below with their syntax:

1. INNER JOIN:

. It selects the records which have matching values in both tables.

. Syntax:

SELECT column(s)

FROM table1 INNER JOIN table2

ON table1.column_name=table2.column_name;

2. LEFT JOIN:

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

. Result is NULL from right table if there is no match.

. Syntax:

SELECT column(s)

FROM table1 LEFT JOIN table2

ON table1.column_name=table2.column_name;

3. RIGHT JOIN:

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

. Result is NULL from left table if there is no match.

. Syntax:

SELECT column(s)

FROM table1 RIGHT JOIN table2

ON table1.column_name=table2.column_name;

4. FULL OUTER JOIN:

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

. If there are rows in left that do not match with right and vice-versa, those rows will also be listed as well with NULL values.

. Syntax:

SELECT column(s)

FROM table1 FULL OUTER JOIN table2

ON table1.column_name=table2.column_name;

5. SELF JOIN:

. It is a regular join but the table is joined with itself.

. Syntax:

SELECT column(s)

FROM table1 T1, table1 T2

WHERE condition;

What are Views?

. Views are the virtual tables created based on the tables in the database.
. It contains rows and columns similar to the real tables.
. It is created as an object whenever we run the query related to it.

Advantages:

. Whenever we are repeatedly retrieving the same data from multiple tables using joins, it takes long time to write query.

. In that case, we can create a view which acts as virtual table.

. Simplifies the query.

. Security: If we want to give access to only limited columns of a table, we can create a view on that particular column and give it to others.

. Abstraction: Can make views on only essential data hiding unwanted data to load each time query is running.

Disadvantages:

. Complexity issues if the views are based on multiple tables.

. If the structure of base table is changed, then view should also be updated.
. DML queries would not work if the views are based on multiple tables.

Creating View:

. Consider a base table "emp".

. Syntax:
CREATE VIEW view_name
AS SELECT column(s)
FROM table_name;

Creating View based on multiple tables:

. We use join query to create view on multiple tables.

. Here view called "my_view" is created based on two tables 'employee' and 'department'.

Retrieving data from view:

. It is similar to the select query in tables.

. Syntax:

SELECT column(s)

FROM view_name;

Retrieving data from view based on multiple tables:

. It is similar to the normal table.

Inserting data into view:

. When we insert data into view, it is automatically inserted into its base table.

. Syntax:

INSERT INTO view_name VALUES(val1, val2,..,valn);

. We can observe that record is inserted into the base table also.

Inserting record in a view based on multiple tables:

. If the view is based on multiple tables, basic DML queries are restricted.

Updating a record in a view:

. It is similar to the normal tables.

. Once data is updated in the view, it is automatically effected in its base table.

. Syntax:

UPDATE view_name set col_name=value

WHERE condition;

Updating a record in a view based on multiple tables:

. While updating, the effected values is changed only in a single table.

. It gives an error if we try to update any value which is based on multiple tables.

Deleting a record from view:

. Similar to the normal tables.

. Once the data is deleted from the view, it is also deleted from the base table.

. Syntax:

DELETE FROM view_name

WHERE condition;

Deleting a record from view based on multiple tables:

. We cannot delete the data from a view which is based on multiple tables.
. It gives an error if we try to delete any record from view.

What is an Index?

. Index in SQL is used to retrieve data quickly.
. Index is created on column which is frequently used to retrieve data.
. It works similar to the index of a page to locate Chapter easily by looking into it.
. Index is made up index nodes that are organized in B-Tree(Balanced Tree) structure.
. It is hierarchical in nature with root node on top and physical node (data) on the bottom as shown in below figure.

. Whenever we perform any SELECT operation, it scans the data by looking into the index nodes.

. Without defining indexes, data is scanned from starting to ending which is called 'table scan' which is time taking.

Advantages:

. Speeds up SELECT query.

. Helps to make a column with unique values.

. They can be used for sorting as they arrange data in sorted order.

Disadvantages:

. Decreases performance on INSERT, UPDATE, DELETE queries.

. This is because on each updation, index value has to be modified.

. Indexes take additional disk space.

Creating Index:

. Syntax for creating index is as follows:

CREATE INDEX index_name ON table_name(column_name);

Dropping Index:

. Syntax for dropping index is as follows:

DROP INDEX table_name.index_name;

What is a Stored Procedure?

. Stored Procedure is a set of SQL statements which is assigned by a name and it can be reused for multiple times.
. Stored procedures are compiled only once.
. It reduces the traffic as a single statement can execute the query which contains multiple lines.
. Saves time to write the query which is used frequently.

Creating Stored Procedure:

. Following syntax is used to create Stored procedure:
CREATE PROCEDURE procedure_name
BEGIN
AS
.
. (set of sql statements)
.
.
END;
. Let us consider we need to retrieve records from a table called employee where EmpId is >=30.
. We use the following query for each time we need to look at the result.

. So we can create a Stored Procedure to execute this query as show below:

Executing Stored Procedure:

. For each time we want to execute the same query, we need not to write all the commands.
. We can just execute the stored procedure by its name as follows:
Execute procedure_name;

Creating parameterized Stored Procedure:

. If we execute which contains data like inserting values, we can also create a procedure to it.

. Following example shows how to create a procedure for inserting values.

. We can run the parameterized query as follows: