Structured Query Language or SQL is a standard language which is used to establish a connection with a Relational database system.
With the help of simple SQL statements users can perform the following tasks:
- Create new databases, tables and views
- Insert records in a database
- Update records in a database
- Delete records from a database
- Retrieve data from a database
Some common relational database management systems that use SQL are:
- Oracle
- Sybase
- Microsoft SQL Server
- Access
- Ingres
Syntax = Select * from Employee The above SQL statement selects all the records (*) from the “Employee” table. Here * refers to fetching all records. We can use specific names of columns as well instead of *.
Important SQL Commands
A database is a collection of structured data stored in a computer that is easily approachable and manageable. The relevant purpose of the database is to operate a huge volume of information by storing, retrieving, and managing data.
In this fast-growing world of technology every organization has enormous data that needs to be preserved. Let’s consider a few simple examples.
- A hospital can have N number of patients, doctors, nurses. They would need to store the reports of their patients as well as the details of their employees. How can they exactly store so much data? So here comes the concept of Database.
- An online cell phone directory would use the database to store the contact details of people.
- WhatsApp stores the information of all our contacts, their chats, status, their profile pictures, and a lot more.
What is a Database Management System (DBMS)?
A collection of programs that helps the users to access database, fetch data, update data, or represent data in the form of reports is called a Database Management System (DBMS).
Database Management system helps in improving data sharing and data security.
Types of Database Management System (DBMS)
Hierarchical DBMS
The hierarchical model represents data into a tree-like-structure. It has its root and then the entire data is linked to it. It expands like a tree with its branches wide open. This is a relationship that can be defined as one: Many, which means each parent node consists of a child node. However in this model, a child node will only have a single parent node.
Network DBMS
Network DBMS is an enhanced and improvised version of hierarchical DMBS.
Unlike hierarchical DBMS a Network database management system (Network DBMSs) are based on a network data model that allows a single record can have multiple parents and multiple child records. So we can understand this as a MANY TO MANY relationship.
One of the best advantages that can be counted as a part of Network DBMS is that it has greater flexibility as compared to the hierarchical DBMS.
Relation DBMS
A relational database management system (RDBMS) is a program that allows us to construct, update, manage, and administer a relational database where data is in the form of tables consisting of rows and columns.
Now the term relational signifies that there is a relationship that exists between values within each table. Tables within the same database can also be related to one another. Now at this point, it becomes very proximate to query the database and fetch relevant records from multiple tables.
EXAMPLE:
In this example, both the tables have a common column called Department ID. So we can say that, these two tables are related based on Department ID column.
Object Oriented DMBS
An object-oriented database (OODBMS) is a database that is based on object-oriented programming (OOP). The representation and storage of data is done in the form of objects.
It is generally used to handle structured data and multimedia data such as Audio, Video, and Pictures, etc.
For creating a New SQL Database, we can use the “CREATE DATABASE” statement.
SYNTAX
Where,
- CREATE DATABASE: KEYWORD used to create a database
- DATABASE NAME: The Name of the Database that you want to create.
Example
Let’s create a Demo Database say TestDb
Figure 3.1. Create Database
Note: You can always create a Database in your local but for other environments you might need Admin privileges.
For dropping a SQL Database, we can use the “DROP DATABASE “statement.
SYNTAX
Where,
- CREATE DATABASE: KEYWORD used to drop a database
- DATABASENAME: The Name of the Database that you want to drop.
Example
Let’s drop a Demo Database say TestDb which we initially created in the previous chapter.
Figure 4.1. DROP Database
Note: A Database consists of a collection of huge volumes of data, so make sure that you have proper Admin access before dropping a Database. A dropped Database cannot be recreated and it may lead to loss of all the stored data.
PRACTICE YOURSELF
A relational database system can contain one or more objects called Tables.
We can store all the database information in these tables.
Tables are identified by their names and consist of Columns and Rows.
- Columns are a set of data values, they basically define the data in a table,
- Rows contain the records or data for the columns.
Let’s understand it with a simple example. Below is the structure of EmployeeDetail Table.
Figure5.1. Sample Table EmployeeDetail
The Columns of the Table EmployeeDetail are:
- EmployeeId,
- LastName,
- FirstName,
- Address,
- City
These columns will define what all information can be found in our table.
We have five records or five rows in our Table.
The following are the types of Tables that can be created in SQL.
1. USER TABLES (REGULAR TABLES)
Figure 6.1. Table ProductLists has been successfully created
The CREATE TABLE statement in SQL is used to create a new user defined table table.
2. TEMPORARY TABLES
Temporary tables can be created to save the execution time. They are however only for a session that created them. After execution of another session they automatically get destroyed. They are identified by prefix #. We can create many temp tables in a single stored procedure.
Figure6.2 Table #ProductList has been successfully created
3.GLOBAL TEMPORARY TABLES
Global temporary tables are available to all sessions and all users. They are however terminated automatically when the last session using the temporary table has completed. They are identified with the prefix ##.
Figure6.2 Table ##GlobalTable has been successfully created
Data types specify the type of data that should be used for a column while creating a table.
1. CHARACTER STRINGS DATA TYPES
2.UNICODE CHARACTER STRING DATA TYPES
3.BINARY STRING DATA TYPES
4. DATE AND TIME DATA TYPES
5. MISC. STRING DATA TYPES
SQL commands are broadly divided into four categories:
- DDL – Data Definition Language
- DML – Data Manipulation Language
- DCL – Data Control Language
- TCL – Transactional Control Language
1. DDL
Data Definition Language consists of SQL commands that handle the database schemas and illustrations. DDL can be used to create and modify the schema of database objects within a database.
2. DML
Data Manipulation Language is used to manipulate the data like retrieve, store, modify, delete, and update data in the database.
3. DCL
Data Control Language as the name suggests it is used to create roles, grant permissions, or revoke permissions to the database by securing it.
4. TCL
Transactional Control Language is used to manage multiple transactions within a database.
A Field with No value can be represented as a NULL Value. NULL means an absence of any value. Therefore, A NULL value in a field is different than having a Zero value or a space.
IS NULL SYNTAX
Where,
- SELECT : KEYWORD used to Fetch data from a database
- Table_Name : The Table name from where you want to fetch the data
- IS NULL/IS NOT NULL : KEYWORD used to determine type of values
NULL & NOT NULL OPERATORS
PRACTICE YOURSELF
Whenever we want to store our data into the database the initial step involves creating a table. The CREATE TABLE statement in SQL is used to create a new table. As already discussed, a table consists of rows and columns, while creating a new table we need to provide all the details like Table name, name of the columns, type of data to be stored in columns, size of the data etc.
SYNTAX
Where,
- CREATE TABLE : keyword used to create a table in a database.
- Tablename : The name of the table which we are going to create.
- Column1Name, Column2Name : The name of the columns to be added to the table.
- Datatypes : Data types specify the type of data that should be used for a column.
Ø Example1 : If a column called “Address”, is to be used to hold addresses, then that column should have a “varchar” (variable-length character) data
Ø Example2 : If a column called “CustomerId”, is to be used to hold unique CustomerIds, then that column should have an “INT” (Integer) data type
- [NULL/NOTNULL] : A column can have NULL or NOTNULL Values; this can be specified whenever we are creating a table. If we don’t specify, SQL Server will take NULL as the default.
Let’s understand the concept of “Create Table” by creating a simple table Persons.
On executing this query, we get the following result set:
PRACTICE YOURSELF
Whenever we want to store our data into the database the initial step involves creating a table. The CREATE TABLE statement in SQL is used to create a new table. As already discussed, a table consists of rows and columns, while creating a new table we need to provide all the details like Table name, name of the columns, type of data to be stored in columns, size of the data etc.
SYNTAX
Where,
- CREATE TABLE : keyword used to create a table in a database.
- Tablename : The name of the table which we are going to create.
- Column1Name, Column2Name : The name of the columns to be added to the table.
- Datatypes : Data types specify the type of data that should be used for a column.
Ø Example1 : If a column called “Address”, is to be used to hold addresses, then that column should have a “varchar” (variable-length character) data
Ø Example2 : If a column called “CustomerId”, is to be used to hold unique CustomerIds, then that column should have an “INT” (Integer) data type
- [NULL/NOTNULL] : A column can have NULL or NOTNULL Values; this can be specified whenever we are creating a table. If we don’t specify, SQL Server will take NULL as the default.
Let’s understand the concept of “Create Table” by creating a simple table Persons.
On executing this query, we get the following result set:
PRACTICE YOURSELF
Constraints are used to specify rules for data that can go into a table.
Constraints can be both column level and table level, which ensures the accuracy and authenticity of the data in the database.
The following constraints are commonly used in SQL:
- NOT NULL – Checks that a column cannot have a NULL value
- UNIQUE – Checks that all values in a column are unique or different from one another
- PRIMARY KEY – A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table. We can define only one Primary key in the table.
- FOREIGN KEY – Uniquely identifies a row/record in another table.
- CHECK – Checks that all values in a column satisfies a specific condition
- DEFAULT – Sets a default value for a column when no value is specified.
INDEX CREATION
The SQL INDEX helps increasing the performance of a table, by retrieving the information very quickly.
The INDEX table consists of only two columns:
- Rowid
- Indexed column (ordered).
When data is retrieved from a database table based on the indexed column, the index pointer looks for the Rowid and quickly locates that position. in the actual table and display, the rows sought for.
SYNTAX
Where:
- CREATE INDEX : keyword used to create an Index.
- Index_name : The name of the index which you are going to create.
- Table_name : The name of the table on which you are going to create the index.
- Column1Name, Column2Name : The name of the columns you want to specify index on.
INSERTING DATA
The SQL INSERT Statement is used to insert data to a table in the database.
SYNTAX
Where,
- INSERT INTO : keyword used to insert records in a table.
- Table_name : The name of the table into which you want to insert the data
- Column1Name, Column2Name : The name of the columns of the table in which you want to insert data.
EXAMPLE
Let’s insert data into a new table Persons in a test Database.
PRACTICE YOURSELF
IDENTITY COLUMNS /AUTO INCREEMENT
Identity column is a column whose value increases automatically as and when data is inserted into the table. The value in an identity column is created by the server. A user cannot insert a value into an identity column; it’s created by the server.
Identity column can be used to uniquely identify the rows in the table and acts like a primary key field.
SYNTAX
PRACTICE YOURSELF
If I want to analyze my data, for example considering I own a grocery shop, I want to find out the total products sold by me, I can make use of a simple SELECT Statement.
The select statement in SQL is used to ask the database for a specific set of information.
SYNTAX
Here, column1Name, column2Name are the values of the table you want to fetch from the database.
EXAMPLE
If I want to fetch the entire content of the table, then I can use the following syntax:
Here we are using a “*” to select all columns.
Figure 15.1. Select * from Table_Name
- If I want to analyze only the product prices, then the following Query can be used:
PRACTICE YOURSELF
We might have duplicate data entries in a table. To avoid viewing duplicating entries we can make use of DISTINCT keyword.
The SELECT DISTINCT statement is used to return only distinct values.
SYNTAX
Where,
- SELECT DISTINCT : keyword used to fetch distinct records in a table.
- Table_name : The name of the table from where you want to fetch the data
- Column1Name, Column2Name : The name of the columns of the table which you want to uniquely fetch.
EXAMPLE
Let’s query PersonalInfo Table in our Demo Database and check for distinct records.
Figure 16.1. SELECT * FROM PersonalInfo
Figure 16.2 Selecting Distinct from PersonalInfo
PRACTICE YOURSELF
SQL – IN & NOT IN CLAUSE
SQL IN CLAUSE
SYNTAX
EXAMPLE
Let’s fetch all the data from Persons table in our Demo Database where city is Bangalore or Pune:
SELECT * FROM PERSONS;
Figure 17.1. IN CITY (PUNE, MUMBAI)
SQL NOT IN
SQL NOT IN operator is used to exclude multiple value in a WHERE clause condition. It is similar to a NOT condition.
SYNTAX
EXAMPLE
Let’s fetch all the data from Persons table in our Demo Database where city is not Bangalore or Pune:
SELECT * FROM PERSONS;
PRACTICE YOURSELF
Wherever we have large tables with thousands of records we can use SELECT TOP Clause.
It limits the number of records in the query result set.
SYNTAX
Where,
- SELECT TOP : keyword used to fetch limited records in a table. (3 records in this case)
- Table_name : The name of the table from where you want to fetch the data
EXAMPLE
Let’s query PersonalInfo table in our Demo Database to fetch only top 2 records.
Figure 18.1. SELECT * FROM PersonalInfo
Figure 18.2 Select Top 2 * from table_name;
PRACTICE YOURSELF
The WHERE clause is used to filter records based on specific condition.
SYNTAX
Where,
- SELECT : keyword used to fetch records in a table.
- Table_name : The name of the table from where you want to fetch the data
- Column1Name, Column2Name : The name of the columns of the table which you want to uniquely fetch.
- Where : specific condition based on which you want to filter your records.
EXAMPLE
Let’s query the PersonalInfo Table and look for a specific condition in where clause
Figure 19.1 Select * from PersonalInfo;
Figure 19.2. Select * from PersonalInfo where condition;
PRACTICE YOURSELF
The GROUP BY Clause in SQL is used to club similar forms of data into groups. I.e. if a Column A has same values in different rows then it will arrange these rows in a group.
- GROUP BY returns only one result per group of data and is used with the SELECT statement
- GROUP BY aggregates the results on the basis of selected column: COUNT, MAX, MIN, SUM, AVG, etc.
SYNTAX
Where,
- Group BY : keyword used to group identical records in a table.
- Table_name : The name of the table from where you want to fetch the data
- Column1Name, Column2Name : The name of the columns of the table which you want to uniquely fetch.
EXAMPLE
Let’s query PersonalInfo Table in our Demo Database and check the count of PersonId using Group by
Figure 20.1. Select * from PersonalInfo;
Figure 20.2 Group by on PersonalInfo;
PRACTICE YOURSELF
The ORDER BY keyword is used to sort the results of the query in ascending or descending order.
By default The ORDER BY keyword sorts the records in ascending order. We can apply by ORDER BY on more than one column as well.
SYNTAX
Where,
- ORDER BY : keyword used to arrange records in a table either in ascending or descending order.
- Table_name : The name of the table from where you want to fetch the data.
- Column1Name, Column2Name : The name of the columns of the table which you want to uniquely fetch.
EXAMPLE
Let’s query PersonalInfo Table in our Demo Database and arrange the records in the latest order.
Figure 21.1. Select * from PersonalInfo;
Figure 21.2. Order by Desc on PersonalInfo;
PRACTICE YOURSELF
The SQL HAVING Clause helps you to specify conditions that filter which group results should appear in the final results.ell.
SYNTAX
NOTE: HAVING Clause should always be followed by a GROUP BY Clause in a Query.
EXAMPLE
Following our approach with the PersonalInfo Table, let’s understand how HAVING CLAUSE is used. Below is the Demo Database
Figure 22.1. Select * from PersonalInfo;
Figure 22.2. HAVING CLAUSE ON PersonalInfo;
The query has grouped the Address, City and FirstName from PersonalInfo table to fetch count of PersonId>1
PRACTICE YOURSELF
The SQL EXISTS operator checks the existence of any record through a subquery.
The subquery returns one or more records if the EXISTS operator returns true.
SYNTAX
Where,
- EXISTS : keyword used checks the existence of any record in a table.
- Table_name : The name of the table from where you want to fetch the data
- Column1Name, Column2Name : The name of the columns of the table which you want to uniquely fetch.
EXAMPLE
Let’s consider two different tables: SalaryInfo and Persons. I want to fetch the salary details of all the people present in Persons table. Here is a DEMO DATABASE:
Figure 23.1. SELECT * FROM Persons;
Figure 23.2. SELECT * FROM SalaryInfo;
QUERY
Figure 23.3. EXISTS QUERY
OUTPUT
Figure 23.4. OUTPUT OF EXISTS QUERY
PRACTICE YOURSELF
OFFSET/FETCH
OFFSET provides a starting row from which Server needs to start fetching rows. It is defaulted to 0.
FETCH provides the number of rows we want to fetch from the final query set.
SYNTAX
NOTE:
- OFFSET and FETCH can be used only with an order by clause.
- OFFSET clause is mandatory with the FETCH clause
- We cannot use TOP clause with OFFSET and FETCH
- The OFFSET and FETCH row count must be an integer value and it does not support sub queries
SQL Operators are broadly divided into four categories:
- Arithmetic operators
- Comparison operators
- Logical operators
SQL – ARMITHMETIC OPERATORS
SQL – COMAPARISON OPERATORS
SQL – LOGICAL OPERATORS
In order to fetch values within a given range we can make use of BETWEEN operators.
SYNTAX
Where,
- BETWEEN : keyword used checks records that fall between a certain range
- Table_name : The name of the table from where you want to fetch the data
- Column1Name, Column2Name : The name of the columns of the table which you want to uniquely fetch.
EXAMPLE
Let us consider a demo database with table name Persons.
Figure 26.1. Select * from Persons;
Figure 26.2. BETWEEN QUERY & OUTPUT
PRACTICE YOURSELF
The SQL UPDATE Clause is used to modify the existing/update records in a table.
SYNTAX
Where,
- UPDATE : keyword used to update/modify the column values in table.
- Table_name : The name of the table from where you want to update the data
- Column1Name, Column2Name : The name of the columns of the table which you want to update
NOTE: We must always use the WHERE clause with the UPDATE query to modify the required rows, otherwise all the rows would be affected.
Let us consider a demo database with table name Persons.
Figure 27.1. Select * from Persons;
The above Query updates the City Name where PersonId = 3;
Figure 27.2. UPDATE QUERY & OUTPUT;
PRACTICE YOURSELF
SQL DELETE statement is used to delete existing records in a table.
SYNTAX
Where,
- DELETE : keyword used delete records from a certain table based on the where condition
- Table_name : The name of the table from where you want to fetch the data.
- Column1Name, Column2Name : The name of the columns of the table which you want to fetch.
NOTE: We must always use the WHERE clause with the DELETE query to eliminate the required rows, otherwise all the rows would be deleted.
EXAMPLE
Let us consider a demo database with table name Persons.
Figure 28.1. Select * from Persons;
The above Query will delete a record from table Persons where the FirstName is ‘Erichsen’;
Figure 28.1. DELETE QUERY & OUTPUT;
PRACTICE YOURSELF
The following are the most commonly used SQL aggregate functions:
AVG
The AVG () function returns the average value of a numeric column.
SYNTAX
EXAMPLE
Let’s consider a demo database and table.
Figure 29.1. Select * from SalaryInfo;
Figure 29.2. AGGREGATE QUERY & OUTPUT
COUNT
The COUNT () function counts the number of rows that matches a specified condition.
SYNTAX
EXAMPLE
Figure 29.3. COUNT QUERY & OUTPUT
MIN
The MIN () function returns the smallest value of the selected column.
SYNTAX
EXAMPLE
MAX
The MAX () function returns the smallest value of the selected column.
SYNTAX
EXAMPLE
Figure 29.5. MAX QUERY & OUTPUT
A JOIN clause is used to combine rows from two or more tables, based on a common column between them.
Different types of JOINS
- (INNER) JOIN: Select records that have matching values in both tables
- LEFT (OUTER) JOIN:Select records from the first (left-most) table with matching right table records.
- RIGHT (OUTER) JOIN:Select records from the second (right-most) table with matching left table records.
- FULL (OUTER) JOIN: Selects all records that match either left or right table records.
An Alias is a name given to any table or column temporarily. This can be done for a query or stored procedure. The renaming is a temporary change and the actual table name does not change in the database.
SYNTAX: Table Alias
SYNTAX: Column Alias
Where,
- Alias_name: keyword used to give a temporary name to a table/column
EXAMPLE
Let us consider a demo database with table name Persons & Salaries
Figure 31.1.SELECT * FROM PERSONS;
Figure 31.2. SELECT * FROM SALARY;
TABLE ALIAS:
Figure 31.3. TABLE ALIAS;
COLUMN ALIAS:
Figure 31.4. COLUMN ALIAS
PRACTICE YOURSELF
The INNER Join condition returns values as per below rule:
- All the matching data values from Table A and Table B based on a common column between both the tables
SYNTAX:
Where,
- INNER JOIN : keyword used for INNER JOIN, in order to fetch all the matching records from both the table.
- Table_name1/Table_name2 : The name of the first/second table from where you want to fetch the data
- Column1Name : The name of the columns of the table based on which you want to join.
EXAMPLE
Let us consider a demo database with two tables Persons & Salary .Following is the structure of both the tables:
Figure 32.1. SELECT * FROM PERSONS;
Figure 32.2. SELECT * FROM SALARY;
The following SQL statement selects all records from Salary table with EmployeeId information:
Figure 32.3 EXAMPLE OF INNER JOIN
PRACTICE YOURSELF
The LEFT Join condition returns values as per below rule:
- All the matching data values from Table A and Table B.
- All the non-matching data values from Left Table only i.e. Table A.
SYNTAX:
Where,
- LEFT JOIN : keyword used for LEFT JOIN, in order to fetch all the matching records from both the table and non-matching data from Left table.
- Table_name1/Table_name2 : The name of the first/second table from where you want to fetch the data
- Column1Name : The name of the columns of the table based on which you want to join.
EXAMPLE
Let us consider a demo database with two tables Persons & Salary. Following is the structure of both the tables:
Figure 33.1. LEFT TABLE PERSONS
Figure 33.2. RIGHT TABLE SALARY
The following SQL statement selects all records from Persons table (Left Table) with matching records from Salary Table (Right table)
Note: A left join returns NULL in case of no matching join.
PRACTICE YOURSELF
The RIGHT Join condition returns values as per below rule:
- All the matching data values from Table A and Table B.
- All the non-matching data values from Right Table only i.e. Table B.
SYNTAX:
Where,
- RIGHT JOIN : keyword used for RIGHT JOIN, in order to fetch all the matching records from both the table and non matching data from Right table.
- Table_name1/Table_name2 : The name of the first/second table from where you want to fetch the data
- Column1Name : The name of the columns of the table based on which you want to join.
EXAMPLE
Let us consider a demo database with two tables Persons & Salary .Following is the structure of both the tables:
Figure 34.1. LEFT TABLE PERSONS
Figure 34.2. RIGHT TABLE SALARY
The following SQL statement selects all records from Salary Table (Right table) with matching records from Persons table (Left Table).
Note: A Right join returns NULL from left side in case of no matching join.
PRACTICE YOURSELF
The FULL Join condition returns values as per below rule:
- All the matching data values from Table A and Table B.
- All the non-matching data values from Left Table i.e. Table A and Right Table i.e. Table B.
SYNTAX:
Where,
- FULL JOIN : keyword used for FULL JOIN, in order to fetch all the matching and non matching records from both the table
- Table_name1/Table_name2 : The name of the first/second table from where you want to fetch the data
- Column1Name : The name of the columns of the table based on which you want to join.
Let us consider a demo database with two tables Persons & Salary. Following is the structure of both the tables:
Figure 35.1. PERSONS TABLE
Figure 35.2. SALARY TABLE
The following SQL statement selects all the matching and non –matching records from Persons table (Left Table) and Salary Table (Right table)
Figure 35.3. FULL OUTER JOIN OUTPUT
Note: FULL OUTER JOIN and FULL JOIN are the same.
The SELF Join is a regular join, but the table is joined with itself.
SYNTAX:
Where,
- SELF JOIN : keyword used for joining a table with itself.
- Table_name1/Table_name2 :The name of the table from where you want to fetch the data
- Colume_Names :The name of the columns of the table based on which you want to join.
EXAMPLE
Let us consider a demo database with table Salary. Following is the structure of the table:
Figure 36.1. SALARY TABLE
The following SQL statement shows Self Join
Figure 36.2. SELF JOIN QUERY OUTPUT
PRACTICE YOURSELF
SQL UNION is used to combine the result sets of two or more SELECT Statements, provided they satisfy the below conditions:
- Each SELECT statement within UNION should have the equal number of columns
- The columns must also have similar data types
- The columns in each SELECT statement must also be in the same order
SYNTAX
Where,
- UNION : KEYWORD used to combine the result set of both the tables
Difference between UNION AND UNION ALL
- UNION removes duplicate rows.
- UNION ALL does not remove duplicate rows.
SYNTAX
EXAMPLE
Let’s consider a Demo Database with Table Individual and Address
Figure 37.1. Individual TABLE
Figure 37.2. Address TABLE
UNION QUERY
Figure 37.3. UNION OUTPUT
UNION ALL QUERY
Figure 37.4. UNION ALL OUTPUT
PRACTICE YOURSELF
PIVOT CLAUSE enables us to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output. It turns the unique values in one column into multiple columns in the output and performs aggregations on any remaining column values.
UNPIVOT carries out the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.
SYNTAX
THE SQL ALTER Clause is used to add, drop or modify the existing constraints records in a table.
ALTER TABLE ADD COLUMN SYNTAX
ALTER TABLE DROP COLUMN SYNTAX
ALTER TABLE ALTER/MODIFY COLUMN SYNTAX (SQL SERVER)
ALTER TABLE ALTER/MODIFY COLUMN SYNTAX (MYSQL)
ALTER TABLE ALTER/MODIFY COLUMN SYNTAX (ORACLE 10 G)
Where,
- ALTER : keyword used to update/modify the column in table.
- Table_name : The name of the table from where you want to ALTER
- Column1Name, Column2Name : The name of the columns of the table which you want to ALTER
- DROP COLUMN : KEYWORD used to drop a column
- MODIFY : KEYWORD use to change the data type of a column in a table
EXAMPLE
Let us consider a demo database with table name Persons.
Figure 39.1. Select * from Persons;
Let’s Add a column to PersonalInfo Table named as AGE
Figure 39.2 ALTER COMMAND QUERY OUTPUT
PRACTICE YOURSELF
Wherever we have large tables with thousands of records we can use LIMIT Clause.
It limits the number of records in the query result set.
SYNTAX
Where,
- SELECT : keyword used to fetch limited records in a table.
- Table_name : The name of the table from where you want to fetch the data
- LIMIT : keyword used to limit the number of records in a table.
EXAMPLE
Let’s query PersonalInfo table in our Demo Database to fetch only 2 records.
Where,
- SELECT : keyword used to fetch limited records in a table.
- Table_name : The name of the table from where you want to fetch the data
- LIMIT : keyword used to limit the number of records in a table.
EXAMPLE
Let’s query PersonalInfo table in our Demo Database to fetch only 2 records.
Figure 40.1. SELECT * FROM PersonalInfo
Figure 40.2 Select * from PersonalInfo LIMIT 2;
PRACTICE YOURSELF
SQL TRUNCATE statement is used to delete existing records in a table, but not the table.
SYNTAX
Where,
- TRUNCATE : keyword used delete records from a certain table.
- Table_name : The name of the table from where you want to fetch the data.
PRACTICE YOURSELF
SQL DROP CLAUSE is used to drop the existing table from the database.
SYNTAX
Where,
- DROP : keyword used to DROP the entire table from database
- Table_name : The name of the table you want to drop.
PRACTICE YOURSELF
PRACTICE YOURSELF
THE SELECT INTO statement is use to take a table backup by coping data from one table into a new table.
SYNTAX
EXAMPLE
Let us consider a demo database with table name Persons.
Figure 43.1. PERSONS TABLE
The above Query will create a new Table PersonsBackup in the database and will store the entire information From Persons Table.
Normalization is a technique to reduce the data redundancy and organizing the data in the database.Normalization is a systematic approach of decomposing tables to eliminate data redundancy (repetition) and undesirable characteristics like Insertion, Update and Deletion Anomalies.Normalization divides larger tables into smaller tables and links them using relationships.Normalization rules are divided into the following normal forms:
- First Normal Form
- Second Normal Form
- Third Normal Form
- BCNF
- Fourth Normal Form
First Normal Form (1NF)
Normalization divides larger tables into smaller tables and links them using relationships.
- It should only have single (atomic) valued attributes/columns.
- The attribute Domain should not change.
- All the columns in a table should have unique names.
- The order of the stored data doesn’t matter.
1NF EXAMPLE
Figure 44.1.EXAMPLE OF 1NF
UNDERSTANDING KEY
A KEY is a value used to uniquely identify a record in a table. A KEY could be a single column or combination of multiple columns
Note: The other Columns in a table that are NOT used to identify a record uniquely are called non-key columns.
Primary Key
A primary is a single column value used to identify a database record uniquely.
The following are the characteristics of a PRIMARY KEY
- A primary key value must be unique
- A primary key cannot be NULL
- The primary key values should rarely be changed
- A Primary key should be defined in the initial stages of creating a table
- We are required to provide a value of the primary key whenever a new record is inserted in the table.
COMPOSITE KEY
A composite key is a primary key composed of multiple columns used to identify a record uniquely.
In our database, we can have many people with the same name, but they live in different places. Hence, we require both Full Name and Address to identify a record uniquely. That is a composite key.
Foreign Key
Foreign Key references the primary key of another Table! It helps connect our Tables. The following are the characteristics of a Foreign Key
- A foreign key can have a different name from its primary key
- It ensures rows in one table have corresponding rows in another
- Unlike the Primary key, they do not have to be unique.
- Foreign keys can be null.
Transitive functional dependencies?
A transitive functional dependency is when changing a non-key column, might cause any of the other non-key columns to change
Second Normal Form (2NF)
The following are the rules for a table to be in the Second Normal Form
- Rule 1- Be in 1NF
- Rule 2- Single Column Primary Key
2NF EXAMPLE
Figure 44.3.EXAMPLE OF 2NF
Figure 44.4.EXAMPLE OF 2NF (Primary Table Broken down into TravelId & Placetravelled)
We have divided our 1NF table into two tables viz. Table 1 and Table2. Table 1 contains all the travel Information whereas Table 2 contains Travel information based on TravelId.
We have introduced a new column called TravelId which is the primary key for table 1. Records can be uniquely identified in Table 1 using TravelId
Figure 44.4.EXAMPLE OF 2NF (Primary Table Broken down into TravelId & Placetravelled)
Third Normal Form (3NF)
A table is said to be in the Third Normal Form when,
- It is in the Second Normal form.
- And, it doesn’t have Transitive Dependency.
3NF EXAMPLE
Figure 44.5.EXAMPLE OF 3NF
Figure 44.6.EXAMPLE of 3NF (Primary Table Broken down into TravelId & Placetravelled)
Figure 44.7. Salutation Table
We have divided our tables and created a new table which stores Salutations. There are no transitive functional dependencies, and hence our table is in 3NF.
In Table 3 Salutation ID is primary key, and in Table 1 Salutation ID is foreign to primary key in Table 3.
BCNF (Boyce-Codd Normal Form)
BCNF is also referred as 3.5 Normal Form.
Even when a database is in 3rd Normal Form, still there can be scenarios where anomalies can result if it has more than one Candidate Key.
4NF (Fourth Normal Form) Rules
If no database table instance contains two or more, independent and multivalued data describing the relevant entity, then it is in 4th Normal Form.
5NF (Fifth Normal Form) Rules
A table is in 5th Normal Form only if it is in 4NF and it cannot be broken down into any number of smaller tables without loss of data.
6NF (Sixth Normal Form) Proposed
6th Normal Form is not standardized, yet however, it is being discussed by database experts for some time.
A Subquery can be also called as Inner query or Nested query is a query within another main SQL query and embedded within the WHERE clause.The Following are the properties of Subquery:
- Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.
- Subqueries can return individual values or a list of records
- Subqueries must be enclosed with parenthesis
- The BETWEEN operator cannot be used with a subquery. However, the BETWEEN operator can be used within the subquery.
SUBQUERY WITHIN A SELECT STATEMENT
SYNTAX
EXAMPLE
Let’s consider a demo database with Tables Employee & IndividualSalary.
Figure 45.1. Employee TABLE
Figure 45.2. IndividualSalary TABLE
QUERY
Selecting Employee Details from IndividualSalary using a Where Condition
Figure45.3. SELECT SUBQUERY OUTPUT
SUBQUERY WITH THE INSERT STATEMENT
SYNTAX
SUBQUERY WITH THE UPDATE STATEMENT
SYNTAX
QUERY
Updating Employee Details from IndividualSalary using a Where Condition
Figure 45.4. UPDATE SUBQUERY OUTPUT
SUBQUERY WITH THE DELETE STATEMENT
SYNTAX
QUERY
Deleting Employee Details from IndividualSalary using a Where Condition
Figure 45.5 DELETE SUBQUERY OUTPUT
VIEWS:
A View is a Virtual Table that uses a query to pull data from the underlying tables.
The following are the characteristics of VIEW:
- A View is a virtual relation that acts as an actual relation.
- View is a virtual table, created using Create View command. This virtual table contains the data retrieved from a query expression, in Create View command.
- View can be created from one or more than one base tables or views.
- View is never stored it is only displayed.
- View is never stored it is only displayed.
- View is updated each time the virtual table (View) is used.
- View has slow processing.
- View do not require memory space.
SYNTAX
MATERIALIZED VIEWS:
When the results of a view expression are stored in a database system, they are defined as materialized views.
The following are the characteristics of Materialized Views:
- Materialized View is the copy of the original base tables in the database.
- The following are the characteristics of Materialized Views:
- The Materialized View are stored on a disk like an object, and they do not get updated every time they are used.
- The materialized view must be updated manually or with the help of triggers. The process of updating the Materialized View is called Materialized View Maintenance.
- Materialized Views has fast processing.
- Materialized View requires memory space.
SYNTAX
SQL CASE statement is a conditional flow that returns a value when the first condition is met (like an IF-THEN-ELSE statement). Once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.
If there is no ELSE part and none of the conditions are true, it returns NULL value.
SYNTAX
EXAMPLE
Let us consider a demo database with table name Persons.
Figure 47.1. PERSONS TABLE
The following Query executes through conditions and returns a value when the first condition is met:
PRACTICE YOURSELF
A transaction is a sequential unit of work accomplished in a logical order against a database.A transaction is the propagation of one or more changes to the database.For example, if we are creating a table or updating a record or deleting a record from the table, then you are performing a transaction on that table. It is important to control these transactions in order to ensure the data integrity and to handle the errors in the database.The database transaction must be
- Atomic,
- Consistent,
- Isolated
- Durable.
These are popularly known as ACID properties.Below are the commands are used to control transactions.
1.SET TRANSACTION
SYNTAX
2.COMMIT
The COMMIT command saves all the transactions to the database since the last COMMIT or ROLLBACK command.
SYNTAX
EXAMPLE
Let us consider a demo database with table name Persons.
Figure 48.1. PERSONS TABLE
QUERY
3.ROLLBACK
In case of any error, all changes need to be aborted. Rollback command can be used to revert all the last done transactions.
SYNTAX
QUERY
Figure 48.3. ROLLBACK QUERY OUTPUT
4. SAVEPOINT
A SAVEPOINT creates a point in a transaction in which you can roll the transaction back to a certain point without rolling back the entire transaction.
SYNTAX
5. RELEASE SAVEPOINT
RELEASE SAVEPOINT is a command which is used to remove a SAVEPOINT that we have created.
Note: Once a SAVEPOINT has been released, we can no longer use the ROLLBACK command to undo transactions performed since the last SAVEPOINT.
SYNTAX
The following are the most commonly used SQL aggregate functions:
- UCASE () – Converts a field to upper case
- LCASE () – Converts a field to lower case
- MID () – Extract characters from a text field
- LEN () – Returns the length of a text field
- ROUND () – Rounds a numeric field to the number of decimals specified
- NOW () – Returns the current system date and time
- FORMAT () – Formats how a field is to be displayed
UCASE () – The UCASE () function converts the value of a field to uppercase.
SYNTAX
SYNTAX FOR SQL Server
EXAMPLE
Let’s consider a demo database and table SALARYINFO
Figure 49.1. SALARYINFO TABLE
QUERY
OUTPUT
LCASE () – The LCASE () function converts the value of a field to uppercase.
SYNTAX
SYNTAX FOR SQL Server
EXAMPLE
Let’s consider a demo database and table SALARYINFO
Figure 49.2. SALARYINFO TABLE
QUERY
OUTPUT
MID() – The MID () function is used to extract characters from a text field.
SYNTAX
SYNTAX FOR SQL Server
LEN () – THE LEN() function returns the length of the value in a text field.
SYNTAX
SYNTAX FOR SQL Server
EXAMPLE
Let’s consider a demo database and table SALARYINFO
Figure 49.3. SALARYINFO TABLE
QUERY
OUTPUT
ROUND() – The ROUND() function is used to round a numeric field to the number of decimals specified.
SYNTAX
NOW() – The NOW() function returns the current system date and time.
SYNTAX
FORMAT() – The FORMAT() function is used to format how a field is to be displayed.
SYNTAX
SQL follows different approaches for DATE Format in MYSQL & SQL SERVER
MYSQL FORMAT
SQLSERVER
EXAMPLE
Let’s consider a DEMO Database with Table Individual
Figure 50.1. Individual TABLE
QUERY
OUTPUT
SQL GRANT command is used to grant access or privileges on the database objects to the users.
SYNTAX
Where,
- privilege_name : is the privilege GRANTED to the user
- object_name : is the name of the database object like table, view etc.,
- user_name
: is the name of the user to whom an access right is being GRANTED. Public is used to grant rights to all the users.</li?
SQL REVOKE Command removes user access rights or privileges to the database objects.
SYNTAX
Where,
- privilege_name : is the privilege REVOKED from the user
- object_name : is the name of the database object like table, view etc.,
- user_name : is the name of the user from whom an access right is being REVOKED.
1.SELECT fields instead of using SELECT *
Using SELECT * (read as “select all”) pulls all available data from a table. However, if a table has many fields and many rows, this taxes database resources by querying a lot of unnecessary data. Also, the execution time is very much.
SELECT fields are much cleaner approach and only pull the required columns and are more efficient.
2. Use WHERE instead of HAVING to define filters
As per the SQL order of execution, HAVING CLAUSE is executed after WHERE CLAUSE. Thus, we want to achieve filtration of data based on specific requirements we must USE clause instead of HAVING Clause.
3. AVOID SELECT DISTINCT
SELECT DISTINCT is an easy way to remove duplicates from a query. SELECT DISTINCT works by grouping all fields in the query to create distinct results. However, a large amount of processing power is required to execute such statements. Thus, we must try to avoid using SELECT DISTINCT, and select more fields to create unique results.
4. USING LIMIT
Before running a query for the first time, we must limit our results to obtain only be desirable and meaningful records. This can be achieved by using a LIMIT statement. (In some DBMS systems, the word TOP is used interchangeably with LIMIT.) The LIMIT statement returns only the number of records specified. A LIMIT statement prevents taxing the database with a large query, only to find out the query needs editing or Modification.
5. AVOIDING MANY SUBQUERIES
We may have more than one sub queries in our main stored procedure. We must Try to minimize the number of subquery block in our query. This is fastening up the query execution.
Some Useful Tips
- Usually IN has the slowest performance, thus IN is efficient whenever most of the filter criteria is in the sub-query. Whereas EXISTS is efficient when most of the filter criteria is in the main query.
- We must use UNION ALL in place of UNION.
- In order to store large binary objects, we need to first arrange them in the file system and add the file path in the database.
- Using Case statements for SQL Verbs is also one of the efficient performance techniques
- ALL SQL Verbs must begin on a new line.
- Keep in mind the Right and left aligning of verbs within the initial SQL Verbs.
A stored procedure is a combination of small SQL queries, that we can save, so the code can be reused repeatedly.
SYNTAX
Where,
- CREATE PROCEDURE : KEYWORD used to create a Stored Procedure
- Procedure Name : The Name of the Procedure that you want to create.
EXAMPLE
Lets’ create a Demo Database having a table Individuals
Figure 53.1. Individual TABLE
STORED PROCEDURE WITH PARAMETERS:
The STUFF() function is used to delete a part of a source string and then inserts another part into the string, starting at a specified position.
SYNTAX
PRACTICE YOURSELF
Question 1. What Is A Schema?
Answer: The set of objects owned by user account is called the schema.
Question 2. What Is A Cluster Key?
Answer: The related columns of the tables are called the cluster key. The cluster key is indexed using a cluster index and its value is stored only once for multiple tables in the cluster.