Complete SQL Tutorial with Examples

by Mashood Ali

This is a complete SQL tutorial for a beginners in which we have used lots of examples to make it easier to understand.

What is SQL?

SQL stands for Structured Query Language. Just like Python, Java, and PHP are programming languages, SQL is a database language to interact with databases.

A programming language can’t directly interact with a database, so SQL language acts as a messenger that accesses and manipulate databases.

What are Database and DBMS?

A database is the collection of organized data that is stored either on the local system or on the internet. Storing data in a database makes it easier to access and manipulate it. Any dynamic website uses a database to store data at the backend. For example, when you post anything on social media, all the alphanumeric data gets stored in the database.

A DBMS is a Database Management System, a software that helps us to manage our databases. A DBMS serves as an interface between the database and its end users or programs, allowing users to retrieve, update, and manage how the information is organized and optimized.

There are various types of DBMS but the main two are RDBMS and Non-RDBMS

RDBMS or Relational DBMS is one of the most used DBMSs that provides the most efficient and flexible way to access structured information. Items in a relational database are organized as a set of tables with columns and rows.

Some of the popular RDBMS are MySQL, Microsoft SQL, Oracle DB, MariaDB, and SQLite. SQL is the language that is used to deal with these types of Databases.

Non-RDBMS or non-relational DBMS is growing in popularity because web applications are becoming more and more complex, storing various types of data. A non-relational database, allows unstructured and semistructured data to be stored and manipulated. It generally stores data in JSON format.

Some of the popular Non-RDBMS are MongoDB, Apache Cassandra, Redis, Couchbase, and Apache HBase. NoSQL is the language that is used to deal with these types of Databases.

In this tutorial, we will only focus on SQL

Clause in SQL

A clause in SQL is a built-in function that helps to fetch the records from the database table using some constraints of your choice. For example, if you want to show only those data which are greater than a particular number, or contain a certain specific word, you can use the clause. Below are the clause used in SQL.

SQL Clause	Used when
OR	If you want to return a dataset by providing multiple conditions in a query. If any condition gets satisfied, it will show the relevant result
AND	This clause returns the dataset when all the provided conditions get satisfied
FROM	This clause is used to select a particular table which we want to manipulate
LIKE	LIKE clause is used to search the data using some provided pattern
WHERE	This clause is used to provide conditions to fetch specific data from the table
ORDER BY	In SQL, ORDER BY is used for sorting the records of the database tables.
GROUP BY	The GROUP BY statement groups rows that have the same values into summary rows
SELECT TOP	The SELECT TOP clause is used to specify the number of records to return.
LIMIT	This clause gives same output as SELECT TOP but used in MySQL

SQL Commands Types

Data Definition Language (DDL)

• Create
• Alter
• Drop
• Truncate
• Rename

Data Manipulation Language (DML)

• Select
• Insert
• update
• Delete

Data Control Language (DCL)

• Grant
• Revoke

Transaction Control Language (TCL)

• Commit
• Roll Back
• Save Point

Difference between Delete, Drop, and Truncate

Delete, DROP, and TRUNCATE, all three commands are used to perform delete operation in database, but they have some differences.

Delete	Drop	Truncate
It is a Data Manipulation Language (DML)	It is a Data Definition Language (DDL)	It is a Data Definition Language (DDL)
It will delete one or more rows/records from the existing database table.	It will delete the whole table	It will delete all the rows from a table or entire records from the existing table in one go.
It is slower	It is faster	It is faster than Delete command
Restore is possible	Restore is not possible	Restore is not possible

Let’s Start with practical

SQL Database Queries

Manipulating Tables

Below are some of the basic SQL commands to manipulate a Database. Even though we have used it for Oracle DBMS, the commands are the same for any RDBMS.

Creating Table:

create table employee(
          id int,
          name varchar(10)
);

Updating Tables:

We will update the above table like adding a new row, deleting a row, modifying the data type of a column, renaming a column, renaming the whole table

alter table employee add address varchar(10);

alter table employee drop column name;

alter table employee modify id varchar(10);

alter table employee rename column id to roll_no;

alter table employee rename to student;

Manipulating Table Data

Inserting records into Table:

We have already created a table above named ’employee’ with the schema of id, name, and address. This table is empty right now. Let’s enter some value into it.

INSERT INTO employee (id, name, address)
VALUES (1, 'Tom', 'New York');

If you are adding the records for every column in the table, you can omit the column names, see code below:

INSERT INTO employee VALUES (1, 'Tom', 'New York');

You can also add multiple records to the table using the query below:

INSERT INTO employee VALUES (2, 'Brad', 'Chicago');
INSERT INTO employee VALUES (3, 'Vin', 'Los Angeles');
INSERT INTO employee VALUES (4, 'Will', 'Austin');

Updating records of the table:

We can modify the existing records of a table either every column’s records or just the one we want. For this, SQL has the ‘UPDATE’ command. Let’s suppose we want to update the above records by replacing the name and address of id 2 with new data:

UPDATE employee
 SET name = 'Brad Pitt', address = 'San Francisco' 
WHERE id = 2;

As you can see in the above code, we have used the WHERE clause to just change the data of the employee whose id is 2. Also, it’s not necessary to replace both columns’ data. If we have to just change the name of the employee keeping its address the same, no need to point to the address column in the query. See the code below:

UPDATE employee SET name = 'Brad Pitt' 
WHERE id = 2;

Deleting a record from the table:

We can delete the existing records from the table using any column data as a reference: let’s see the codes below:

DELETE FROM employee WHERE id = 3;
#OR
DELETE FROM employee WHERE name = 'Vin';
#OR
DELETE FROM employee WHERE address = 'Los Angeles';

Any of the above SQL queries will delete the 3rd record from the table.

DELETE FROM employee;

This SQL query will delete all the rows from the table employee without deleting the table.

Selecting records from the table:

We need to fetch and show data from the database. For this, we have the SELECT command in SQL. We can either select the whole table’s data or just the data of the column we want. See the queries below:

SELECT * FROM employee;

SELECT id, name
 FROM employee;

JOINS in SQL

Joins clause is one of the important commands in SQL which is used to combine rows from two or more tables using a common column in them.

When we want to show the output data which are present in two or more different tables, we use JOINS. It is similar to the mathematical SET joins where we have set intersection, set union, etc.

There are 4 types of SQL JOINS that are used:

INNER JOIN

The INNER JOIN keyword selects records that have matching values in both tables. It basically joins two tables based on a common column and selects records that have matching values in these columns.

# INNER JOIN Syntax
SELECT tableA.column1, tableB.column2...
FROM tableA
INNER JOIN tableB
ON tableA.common_column = tableB.common_column;

LEFT JOIN

The LEFT JOIN keyword returns all records from the left table (table A) and the matching records from the right table (table B). If there is no match, it will only show all data from the left table only.

# LEFT JOIN Syntax
SELECT tableA.column1, tableB.column2...
FROM tableA
LEFT JOIN tableB
ON tableA.common_column = tableB.common_column;

RIGHT JOIN

This is just the opposite of the LEFT JOIN which returns all records from the right table (table B), and the matching records from the left table (table A). If there is no match, the result will be all the data from the right table only.

# RIGHT JOIN Syntax
SELECT tableA.column1, tableB.column2...
FROM tableA
RIGHT JOIN tableB
ON tableA.common_column = tableB.common_column;

FULL OUTER JOIN

The FULL OUTER JOIN keyword returns all records when there is a match in the left (table1) or right (table2) table records.

# FULL OUTER SYNTAX
SELECT tableA.column1, tableB.column2...
FROM tableA
FULL OUTER JOIN tableB
ON tableA.common_column = tableB.common_column
WHERE condition;

Let’s understand the JOIN by taking an example:

Below there are two tables, first table A containing columns ‘user_id’ and ‘user_name’ and the second is table B that contains columns ‘user_id’ and ‘user_address’. We want to show output records with user_name and user_address columns data. We can do this using LEFT JOIN having ‘user_id’ as a common column.

SELECT tableA.user_name, tableB.user_address
FROM tableA
INNER JOIN tableB
ON tableA.user_id = tableB.user_id;

LIKE commands in SQL

LIKE command is used for searching purposes in the database. It searches database records according to the provided pattern in the query and returns the most alike records in decreasing order.

Patterns of LIKE commands:

• x% — will return all values that begin with x
• %x — will return all values that end with x
• %x%  — will return all values that include x at any position
• x%y — will select all values that begin with x and end with y
• x_%  — will select all values that begin with x and are at least two characters long.

Let’s understand the LIKE command with an Example:

Given below is a table of records with the table name ’emp’. Answer the following questions below:

Q1). Write a query to find the employee’s details whose names start with ‘A’.

SELECT * FROM emp WHERE name LIKE 'A%'

Q2). Write a query to find the employee’s details whose name ends with ‘n’.

SELECT * FROM emp WHERE name LIKE '%n'

Q3). Write a query to find the employee’s details whose name contains ‘r’.

SELECT * FROM emp WHERE name LIKE '%r%'

Q4). Find the employee’s details whose name contains ‘t’ in second place.

SELECT * FROM emp WHERE name LIKE '_t%'

Q5). Find the employee’s details whose name contains ‘t’ in second place and the name should contain a total of 4 characters.

SELECT * FROM emp WHERE name LIKE '_a__'

Aggregate Function in SQL

In SQL, aggregate functions perform a calculation on multiple values and return a single value. The various aggregate functions in SQL are:

• COUNT()
• SUM()
• AVG()
• MIN()
• MAX()

An aggregate function ignores NULL values when it performs the calculation, except for the count function. 

Let’s understand this by an example:

Below is a table named emp where we have employee data.

1.) Selecting Maximum salary from the data:

SELECT Max(Salary) from emp;

2.) Selecting Minimum salary from the data:

SELECT Min(Salary) from emp;

3.) Getting all the number of rows in the table:

SELECT Count(*) from emp;

4.) Getting the number of rows in the Salary column:

SELECT Count(Salary) from emp;

5.) Getting distinct rows from the Salary column:

SELECT Distinct(Count(Salary)) from emp;

6.) Getting sum and distinct sum:

#Sum
SELECT Sum(Salary) from emp;
#Distinct Sum
SELECT Distinct(Sum(Salary)) from emp;

7.) Getting average and distinct average:

#Average
SELECT avg(Salary) from emp;
#In this case we divide the total with the number of rows excluding NULL

#Distinct Average
SELECT Distinct(avg(Salary)) from emp;
#In this case we divide the distinct total with distinct no. of rows excluding NULL

Use of GROUP BY and ORDER BY in SQL

The GROUP BY clause groups rows that have the same values into summary rows, while the ORDER BY clause is used to sort the result-set in ascending or descending order.

Below is the user’s table:

SELECT COUNT(user_id), country
FROM user
GROUP BY country;

Using ORDER BY together in the below code:

SELECT COUNT(user_id), country
FROM user
GROUP BY country
ORDER BY COUNT(user_id) ASC;

LIMIT in SQL

LIMIT clause is used to retrieve a specific number of data from the top of the table. So, if we apply the LIMIT clause in the above user’s table using the code below, it will provide the first few data like:

SELECT * FROM user
LIMIT 3;

That’s for now. I hope you liked the tutorial.