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);
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:
. 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;
. 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.
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));
. 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);
. 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));
. 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);
. 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));
. 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));
. 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.
. 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.
. 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'.
. 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
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.
. 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.
. DML queries would not work if the views are based on multiple tables.
Creating View:
. Consider a base table "emp".
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.
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.
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.
. 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.
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:
. We can just execute the stored procedure by its name as follows:
Execute procedure_name;
. We can run the parameterized query as follows:
. 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: