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 |
|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)
Data Manipulation Language (DML)
Data Control Language (DCL)
Transaction Control Language (TCL)
- 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.
|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 practical
SQL Database Queries
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.
create table employee( id int, name varchar(10) );
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;
|2||Brad Pitt||San Francisco|
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;
|2||Brad Pitt||San Francisco|
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:
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;
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;
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:
%— 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:
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;
1200 #Exclude Duplicacy
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
1200/4 = 300
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.
2 thoughts on “SQL | SQL Tutorial with Examples”
Pingback: Python If, Else: Conditions and statements – Programming Tutorial
Pingback: Microsoft 365 Copilot | Pricing | How to use – Programming Tutorial