SQL Tutorial

SQL tutorial provides basic and advanced concepts of SQL. Our SQL tutorial is designed for both beginners and professionals.

SQL (Structured Query Language) is used to perform operations on the records stored in the database, such as updating records, inserting records, deleting records, creating and modifying database tables, views, etc.

SQL is not a database system, but it is a query language.

Suppose you want to perform the queries of SQL language on the stored data in the database. You are required to install any database management system in your systems, for example Oracle, MySQL, MongoDB, PostgreSQL, SQL Server, DB2, etc.

What is SQL?

SQL is a short-form of the structured query language, and it is pronounced as S-Q-L or sometimes as See-Quell.

This database language is mainly designed for maintaining the data in relational database management systems. It is a special tool used by data professionals for handling structured data (data which is stored in the form of tables). It is also designed for stream processing in RDSMS.

You can easily create and manipulate the database, access and modify the table rows and columns, etc. This query language became the standard of ANSI in the year of 1986 and ISO in the year of 1987.

If you want to get a job in the field of data science, then it is the most important query language to learn. Big enterprises like Facebook, Instagram, and LinkedIn, use SQL for storing the data in the back-end.

How SQL Works?

When we are executing the command of SQL on any Relational database management system, then the system automatically finds the best routine to carry out our request, and the SQL engine determines how to interpret that particular command.

Structured Query Language contains the following four components in its process:

  • Query Dispatcher
  • Optimization Engines
  • Classic Query Engine
  • SQL Query Engine, etc.

A classic query engine allows data professionals and users to maintain non-SQL queries. The architecture of SQL is shown in the following diagram:

SQL Tutorial

Basic SQL Commands

The SQL commands help in creating and managing the database. The most common SQL commands which are highly used are mentioned below:

This command helps in accessing the single or multiple rows from one or multiple tables of the database. We can also use this command with the WHERE clause.

Command Description
CREATE Command This command helps in creating the new database, new table, table view, and other objects of the database.
INSERT Command This command helps in inserting the data or records into the database tables. We can easily insert the records in single as well as multiple rows of the table.
UPDATE Command This command helps in updating or changing the stored data in the database.
DELETE Command This command helps in removing or erasing the saved records from the database tables. It erases single or multiple tuples from the tables of the database.
SELECT Command  
DROP Command This command helps in deleting the entire table, table view, and other objects from the database.

Why SQL?

Nowadays, SQL is widely used in data science and analytics. Following are the reasons which explain why it is widely used:

  • The basic use of SQL for data professionals and SQL users is to insert, update, and delete the data from the relational database.
  • SQL allows the data professionals and users to retrieve the data from the relational database management systems.
  • It also helps them to describe the structured data.
  • It allows SQL users to create, drop, and manipulate the database and its tables.
  • It also helps in creating the view, stored procedure, and functions in the relational database.
  • It allows you to define the data and modify that stored data in the relational database.
  • It also allows SQL users to set the permissions or constraints on table columns, views, and stored procedures.

Advantages of SQL

SQL provides various advantages which make it more popular in the field of data science. It is a perfect query language which allows data professionals and users to communicate with the database. Following are the best advantages or benefits of Structured Query Language:

1. No programming needed

SQL does not require a large number of coding lines for managing the database systems. We can easily access and maintain the database by using simple SQL syntactical rules. These simple rules make the SQL user-friendly.

2. High-Speed Query Processing

A large amount of data is accessed quickly and efficiently from the database by using SQL queries. Insertion, deletion, and updation operations on data are also performed in less time.

3. Standardized Language

SQL follows the long-established standards of ISO and ANSI, which offer a uniform platform across the globe to all its users.

4. Portability

The structured query language can be easily used in desktop computers, laptops, tablets, and even smartphones. It can also be used with other applications according to the user’s requirements.

5. Interactive language

We can easily learn and understand the SQL language. We can also use this language for communicating with the database because it is a simple query language. This language is also used for receiving the answers to complex queries in a few seconds.

6. More than one Data View

The SQL language also helps in making the multiple views of the database structure for the different database users.

Disadvantages of SQL

With the advantages of SQL, it also has some disadvantages, which are as follows:

1. Cost

The operation cost of some SQL versions is high. That’s why some programmers cannot use the Structured Query Language.

2. Interface is Complex

Another big disadvantage is that the interface of Structured query language is difficult, which makes it difficult for SQL users to use and manage it.

3. Partial Database control

The business rules are hidden. So, the data professionals and users who are using this query language cannot have full database control.

SQL Syntax

Simple Example of SQL statement:

 
  1. SELECT “column_name” FROM “table_name”;    

Each SQL statement begins with any of the SQL keywords and ends with the semicolon (;). The semicolon is used in the SQL for separating the multiple Sql statements which are going to execute in the same call. In this SQL tutorial, we will use the semicolon (;) at the end of each SQL query or statement.

1. SELECT Statement

This SQL statement reads the data from the SQL database and shows it as the output to the database user.

Syntax of SELECT Statement:

 SELECT column_name1, column_name2, .…, column_nameN [ FROM table_name ] [ WHERE condition ] [ ORDER BY order_column_name1 [ ASC | DESC ], …. ];  

Example of SELECT Statement:

SELECT Emp_ID, First_Name, Last_Name, Salary, City FROM Employee_details WHERE Salary = 100000 ORDER BY Last_Name;  

This example shows the Emp_ID, First_Name, Last_Name, Salary, and City of those employees from the Employee_details table whose Salary is 100000. The output shows all the specified details according to the ascending alphabetical order of Last_Name.

3. UPDATE Statement

This SQL statement changes or modifies the stored data in the SQL database.

Syntax of UPDATE Statement:

UPDATE table_name SET column_name1 = new_value_1, column_name2 = new_value_2, …., column_nameN = new_value_N [ WHERE  CONDITION ];  

Example of UPDATE Statement:

UPDATE Employee_details SET Salary = 100000 WHERE Emp_ID = 10;  

This example changes the Salary of those employees of the Employee_details table whose Emp_ID is 10 in the table.

3. DELETE Statement

This SQL statement deletes the stored data from the SQL database.

Syntax of DELETE Statement:

DELETE FROM table_name [ WHERE CONDITION ];  

Example of DELETE Statement:

DELETE FROM Employee_details WHERE First_Name = ‘Sumit’;  

This example deletes the record of those employees from the Employee_details table whose First_Name is Sumit in the table.

4. CREATE TABLE Statement

This SQL statement creates the new table in the SQL database.

Syntax of CREATE TABLE Statement:

CREATE TABLE table_name (column_name1 data_type [column1 constraint(s)], column_name2 data_type [column2 constraint(s)], …..….., column_nameN data_type [columnN constraint(s)], PRIMARY KEY(one or more col));  

Example of CREATE TABLE Statement:

CREATE TABLE Employee_details(Emp_Id NUMBER(4) NOT NULL, First_name VARCHAR(30),  Last_name VARCHAR(30), Salary Money, City VARCHAR(30), PRIMARY KEY (Emp_Id));  

This example creates the table Employee_details with five columns or fields in the SQL database. The fields in the table are Emp_Id, First_Name, Last_Name, Salary, and City. The Emp_Id column in the table acts as a primary key, which means that the Emp_Id column cannot contain duplicate values and null values.

5. ALTER TABLE Statement

This SQL statement adds, deletes, and modifies the columns of the table in the SQL database.

Syntax of ALTER TABLE Statement:

 ALTER TABLE table_name ADD column_name datatype[(size)];  

The above SQL alter statement adds the column with its datatype in the existing database table.

 ALTER TABLE table_name MODIFY column_name column_datatype[(size)];  

The above ‘SQL alter statement’ renames the old column name to the new column name of the existing database table.

 ALTER TABLE table_name DROP COLUMN column_name;  

The above SQL alter statement deletes the column of the existing database table.

Example of ALTER TABLE Statement:

 ALTER TABLE Employee_details ADD Designation VARCHAR(18);  

This example adds the new field whose name is Designation with size 18 in the Employee_details table of the SQL database.

6. DROP TABLE Statement

This SQL statement deletes or removes the table and the structure, views, permissions, and triggers associated with that table.

Syntax of DROP TABLE Statement:

 DROP TABLE [ IF EXISTS ] table_name1, table_name2, ……, table_nameN;  
 

The above syntax of the drop statement deletes specified tables completely if they exist in the database.

Example of DROP TABLE Statement:

 DROP TABLE Employee_details;   

This example drops the Employee_details table if it exists in the SQL database. This removes the complete information if available in the table.

7. CREATE DATABASE Statement

This SQL statement creates the new database in the database management system.

Syntax of CREATE DATABASE Statement:

 CREATE DATABASE database_name;  

Example of CREATE DATABASE Statement:

CREATE DATABASE Company;   

The above example creates the company database in the system.

8. DROP DATABASE Statement

This SQL statement deletes the existing database with all the data tables and views from the database management system.

Syntax of DROP DATABASE Statement:

 DROP DATABASE database_name;  

Example of DROP DATABASE Statement:

 DROP DATABASE Company;   

The above example deletes the company database from the system.

9. INSERT INTO Statement

This SQL statement inserts the data or records in the existing table of the SQL database. This statement can easily insert single and multiple records in a single query statement.

Syntax of insert a single record:

 INSERT INTO table_name ( column_name1, column_name2, .…, column_nameN ) VALUES(value_1, value_2, ..…, value_N);  

Example of insert a single record:

 INSERT INTO Employee_details (Emp_ID, First_name, Last_name, Salary, City )  VALUES (101,  Akhil, Sharma, 40000, Bangalore );  

This example inserts 101 in the first column, Akhil in the second column, Sharma in the third column, 40000 in the fourth column, and Bangalore in the last column of the table Employee_details.

Syntax of inserting a multiple records in a single query:

INSERT INTO table_name ( column_name1, column_name2, .…, column_nameN) VALUES (value_1, value_2, ..…, value_N), (value_1, value_2, ..…, value_N),….;  

Example of inserting multiple records in a single query:

 INSERT INTO Employee_details ( Emp_ID, First_name, Last_name, Salary, City ) VALUES (101, Amit, Gupta, 50000, Mumbai), (101,  John, Aggarwal, 45000, Calcutta), (101, Sidhu, Arora, 55000, Mumbai);  

This example inserts the records of three employees in the Employee_details table in the single query statement.

10. TRUNCATE TABLE Statement

This SQL statement deletes all the stored records from the table of the SQL database.

Syntax of TRUNCATE TABLE Statement:

  1. TRUNCATE TABLE table_name;  

Example of TRUNCATE TABLE Statement:

  1. TRUNCATE TABLE Employee_details;  

This example deletes the record of all employees from the Employee_details table of the database.

11. DESCRIBE Statement

This SQL statement tells something about the specified table or view in the query.

Syntax of DESCRIBE Statement:

 DESCRIBE table_name | view_name;  

Example of DESCRIBE Statement:

 DESCRIBE Employee_details;  

This example explains the structure and other details about the Employee_details table.

12. DISTINCT Clause

This SQL statement shows the distinct values from the specified columns of the database table. This statement is used with the SELECT keyword.

Syntax of DISTINCT Clause:

 SELECT DISTINCT column_name1, column_name2, … FROM table_name;  

Example of DISTINCT Clause:

 SELECT DISTINCT City, Salary FROM Employee_details;  

This example shows the distinct values of the City and Salary column from the Employee_details table.

13. COMMIT Statement

This SQL statement saves the changes permanently, which are done in the transaction of the SQL database.

Syntax of COMMIT Statement:

 COMMIT;  

Example of COMMIT Statement:

 DELETE FROM Employee_details WHERE salary = 30000; 

 COMMIT;  

This example deletes the records of those employees whose Salary is 30000 and then saves the changes permanently in the database.

14. ROLLBACK Statement

This SQL statement undo the transactions and operations which are not yet saved to the SQL database.

Syntax of ROLLBACK Statement:

 ROLLBACK;  

Example of ROLLBACK Statement:

 DELETE FROM Employee_details WHERE City = Mumbai; 

 ROLLBACK;  

This example deletes the records of those employees whose City is Mumbai and then undo the changes in the database.

15. CREATE INDEX Statement

This SQL statement creates the new index in the SQL database table.

Syntax of CREATE INDEX Statement:

 CREATE INDEX index_name ON table_name ( column_name1, column_name2, …, column_nameN );  

Example of CREATE INDEX Statement:

 CREATE INDEX idx_First_Name ON employee_details (First_Name);  

This example creates an index idx_First_Name on the First_Name column of the Employee_details table.

16. DROP INDEX Statement

This SQL statement deletes the existing index of the SQL database table.

Syntax of DROP INDEX Statement:

 DROP INDEX index_name;   

Example of DROP INDEX Statement:

 DROP INDEX idx_First_Name;   

This example deletes the index idx_First_Name from the SQL database.

17. USE Statement

This SQL statement selects the existing SQL database. Before performing the operations on the database table, you have to select the database from the multiple existing databases.

Syntax of USE Statement:

 USE database_name;  

Example of USE DATABASE Statement:

 USE Company;   

This example uses the company database.