50 SQL Examples to learn and master SQL
SQL is a powerful language that can be used for a variety of tasks, such as creating and modifying databases, manipulating data, and managing users. In this article, we’ve provided 50 SQL script examples that you can use to learn and master SQL.
Introduction
SQL (Structured Query Language) is a standard database query language that is used to manipulate, store, and retrieve data from databases. It is a widely-used language that is both powerful and easy to learn.
SQL stores data in the form of tables in relational databases (a type of database that consists of multiple tables that relate to each other).
There are many RDBMS (relational database management systems) out there, some of which include MySQL, Microsoft SQL Server, PostgreSQL, SQLite, etc. They may have different syntaxes, but once you learn one of them, it will be easy to switch between them.
For today’s article, we will be using MySQL to master SQL. So without any further ado, let’s get started.
Creating a Database
Once you get connected with the MySQL server by entering the password, we can run our first-ever SQL query. We create a database named “College” using the following query:-
mysql> create database College;
Query OK, 1 row affected (0.01 sec)Code language: SQL (Structured Query Language) (sql)Showing Databases
Using this query you can see all the databases in your system,
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| boot |
| college |
| data |
| demo |
| grocery_shop |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
15 rows in set (0.04 sec)Code language: SQL (Structured Query Language) (sql)Opening Databases
Using the following we make the database active and can manipulate or store data in the database.
mysql> use college;
Database changedCode language: SQL (Structured Query Language) (sql)Delete a Database
If we want to entirely remove/delete a database along with all its tables and structure, we will use the command given below
mysql> Drop Database demo;
Query OK, 3 rows affected (0.05 sec)Code language: SQL (Structured Query Language) (sql)Create a Table
Now we create a table named “Student” for storing data in the database “College” using the following commands.
mysql> create table student (
-> stu_id int primary key,
-> name varchar(25),
-> age int,
-> gender char(1),
-> DOB date);
Query OK, 0 rows affected (0.04 sec)Code language: SQL (Structured Query Language) (sql)Viewing Tables
If you want to check if the tables have been created, you can use the following command:
mysql> show tables;
+-------------------+
| Tables_in_college |
+-------------------+
| hostel |
| student |
+-------------------+
2 rows in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)Viewing Table Structure
We can see the structure of a table in MySQL using the DESCRIBE or DESC command:
mysql> desc student;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| stu_id | int | NO | PRI | NULL | |
| name | varchar(25) | YES | | NULL | |
| age | int | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| DOB | date | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
Code language: SQL (Structured Query Language) (sql)Create a copy of a table
If you want to create a duplicate of a table, we use the following command:
mysql> create table student_copy like student;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables
;
+-------------------+
| Tables_in_college |
+-------------------+
| hostel |
| student |
| student_copy |
+-------------------+
3 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Insert Data in Table
The INSERT INTO command inserts a new record in a table.
mysql> insert into student values(
-> 107,
-> "Aditya",
-> 17,
-> 'M',
-> '2004-12-25');
Query OK, 1 row affected (0.00 sec)Code language: SQL (Structured Query Language) (sql)Showing data of a Table
We can display the data inserted in the table using the SELECT command, we can also specify which columns to show or not. To display all the data, the following query is executed:
mysql> select*from student;
+--------+--------+------+--------+------------+
| stu_id | name | age | gender | DOB |
+--------+--------+------+--------+------------+
| 107 | Aditya | 17 | M | 2004-12-25 |
| 108 | Aagman | 18 | M | 2003-08-12 |
| 109 | Anjali | 17 | F | 2004-04-17 |
| 110 | Bhavya | 17 | M | 2004-01-21 |
| 111 | Preeti | 18 | F | 2003-09-09 |
| 112 | Richa | 17 | F | 2004-05-19 |
+--------+--------+------+--------+------------+
6 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Show Specific Data from a Table
If a table has 5 columns and you want to display 3 specific columns, using the SELECT statement and column names, we can do it. The following query shows the same:
mysql> select stu_id, name, gender from student;
+--------+--------+--------+
| stu_id | name | gender |
+--------+--------+--------+
| 107 | Aditya | M |
| 108 | Aagman | M |
| 109 | Anjali | F |
| 110 | Bhavya | M |
| 112 | Richa | F |
+--------+--------+--------+
5 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Using the WHERE condition statement
Using the SELECT command and without using the ‘*’ (asterisk) symbol, we can specify the column to display and also use the WHERE clause to add a condition to the query:
mysql> select stu_id,name,city from student
-> where gender='M';
+--------+--------+-------+
| stu_id | name | city |
+--------+--------+-------+
| 107 | Aditya | Delhi |
| 108 | Aagman | Delhi |
| 110 | Bhavya | Delhi |
+--------+--------+-------+
3 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Modify/Update data in a table
After we have inserted data in a table and if we might want to modify or update data of a table or to make changes to some or all of the values in the existing records in a table, we use the UPDATE command. For applying conditions, we use the WHERE clause.
mysql> update student
-> set age = 19, DOB='2002/03/08'
-> where name = "Bhavya";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select *from student;
+--------+--------+------+--------+------------+
| stu_id | name | age | gender | DOB |
+--------+--------+------+--------+------------+
| 107 | Aditya | 17 | M | 2004-12-25 |
| 108 | Aagman | 18 | M | 2003-08-12 |
| 109 | Anjali | 17 | F | 2004-04-17 |
| 110 | Bhavya | 19 | M | 2002-03-08 |
| 111 | Preeti | 18 | F | 2003-09-09 |
| 112 | Richa | 17 | F | 2004-05-19 |
+--------+--------+------+--------+------------+
6 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)DELETE Record from a Table
For deleting a record from a table, we use the DELETE command and the condition can be specified using the WHERE clause.
mysql> delete from student where stu_id=111;
Query OK, 1 row affected (0.00 sec)
mysql> select *from student;
+--------+--------+------+--------+------------+
| stu_id | name | age | gender | DOB |
+--------+--------+------+--------+------------+
| 107 | Aditya | 17 | M | 2004-12-25 |
| 108 | Aagman | 18 | M | 2003-08-12 |
| 109 | Anjali | 17 | F | 2004-04-17 |
| 110 | Bhavya | 19 | M | 2002-03-08 |
| 112 | Richa | 17 | F | 2004-05-19 |
+--------+--------+------+--------+------------+
5 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)TRUNCATE a Table
If you want to remove all the records/data present in a table, we use the TRUNCATE command. The query for the same is as follows:-
mysql> select*from student_Copy;
+--------+---------+------+--------+------------+
| stu_id | name | age | gender | DOB |
+--------+---------+------+--------+------------+
| 107 | Aditya | 17 | M | 2004-12-25 |
| 108 | Aagman | 18 | M | 2003-08-12 |
| 109 | Anjali | 17 | F | 2004-04-17 |
| 110 | Bhavya | 19 | M | 2002-03-08 |
| 112 | Richa | 17 | F | 2004-05-19 |
| 121 | Keshav | 18 | M | 2003-07-29 |
| 122 | Payal | 18 | F | 2003-01-04 |
| 123 | Shaurya | 17 | M | 2004-02-14 |
+--------+---------+------+--------+------------+
8 rows in set (0.00 sec)
mysql> truncate student_copy;
Query OK, 0 rows affected (0.03 sec)
mysql> select*from student_Copy;
Empty set (0.01 sec)Code language: SQL (Structured Query Language) (sql)Using ALTER to ADD a Column
if you want to add a column, we use the ALTER command. For adding a column with a default value we use the following query:
mysql> alter table student add(City varchar(20) default "Delhi");
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from student;
+--------+--------+------+--------+------------+-------+
| stu_id | name | age | gender | DOB | City |
+--------+--------+------+--------+------------+-------+
| 107 | Aditya | 17 | M | 2004-12-25 | Delhi |
| 108 | Aagman | 18 | M | 2003-08-12 | Delhi |
| 109 | Anjali | 17 | F | 2004-04-17 | Delhi |
| 110 | Bhavya | 19 | M | 2002-03-08 | Delhi |
| 112 | Richa | 17 | F | 2004-05-19 | Delhi |
+--------+--------+------+--------+------------+-------+
5 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Using ALTER to RENAME Column
If you want to rename a column in a table we use the following query:
mysql> alter table student change DOB Date_of_Birth date;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select *from student;
+--------+--------+------+--------+---------------+-------+
| stu_id | name | age | gender | Date_of_Birth | city |
+--------+--------+------+--------+---------------+-------+
| 107 | Aditya | 17 | M | 2004-12-25 | Delhi |
| 108 | Aagman | 18 | M | 2003-08-12 | Delhi |
| 109 | Anjali | 17 | F | 2004-04-17 | Delhi |
| 110 | Bhavya | 19 | M | 2002-03-08 | Delhi |
| 112 | Richa | 17 | F | 2004-05-19 | Delhi |
+--------+--------+------+--------+---------------+-------+
5 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Using ALTER to Remove a Column
We can Remove a column from a table in SQL using ALTER command:
mysql> alter table student drop city;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select *from student;
+--------+--------+------+--------+---------------+
| stu_id | name | age | gender | Date_of_Birth |
+--------+--------+------+--------+---------------+
| 107 | Aditya | 17 | M | 2004-12-25 |
| 108 | Aagman | 18 | M | 2003-08-12 |
| 109 | Anjali | 17 | F | 2004-04-17 |
| 110 | Bhavya | 19 | M | 2002-03-08 |
| 112 | Richa | 17 | F | 2004-05-19 |
+--------+--------+------+--------+---------------+
5 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)DROP a Table
If you want to delete a table along with its structure in your database you can use the DROP command in the query:
mysql> show tables;
+-------------------+
| Tables_in_college |
+-------------------+
| faculty |
| hostel |
| student |
+-------------------+
3 rows in set (0.00 sec)
mysql> drop table faculty;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+-------------------+
| Tables_in_college |
+-------------------+
| hostel |
| student |
+-------------------+
2 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Using Arithmetic Operators in a Statement
Basic addition, subtraction, multiplication, and division fall under the arithmetic operators.
mysql> select 17+30 from dual;
+-------+
| 17+30 |
+-------+
| 47 |
+-------+
1 row in set (0.00 sec)
mysql> select 29-18 from dual;
+-------+
| 29-18 |
+-------+
| 11 |
+-------+
1 row in set (0.00 sec)
mysql> select 25*40 from dual;
+-------+
| 25*40 |
+-------+
| 1000 |
+-------+
1 row in set (0.00 sec)
mysql> select 57/10 from dual;
+--------+
| 57/10 |
+--------+
| 5.7000 |
+--------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Note: Dual is a dummy, one-row one-column table in MySQL
Using Relational Operators in a Statement
Using Relational operators (like >, <, <=, >=, !=, =) we can impose conditions on the SELECT command using the WHERE clause.
mysql> select stu_id,name,marks from student where marks>=80;
+--------+--------+-------+
| stu_id | name | marks |
+--------+--------+-------+
| 108 | Aagman | 80 |
| 112 | Richa | 100 |
| 121 | Keshav | 90 |
+--------+--------+-------+
3 rows in set (0.00 sec)
mysql> select stu_id,name,marks from student where marks<=50;
+--------+---------+-------+
| stu_id | name | marks |
+--------+---------+-------+
| 107 | Aditya | 50 |
| 123 | Shaurya | 15 |
+--------+---------+-------+
2 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Using Logical Operators in a Statement
Using Relational operators (like AND, OR, NOT) we can impose conditions on the SELECT command using the WHERE clause.
mysql> select stu_id,name,marks from student where branch="CSE" and marks in(50,100);
+--------+--------+-------+
| stu_id | name | marks |
+--------+--------+-------+
| 107 | Aditya | 50 |
| 112 | Richa | 100 |
+--------+--------+-------+
2 rows in set (0.00 sec)
mysql> select name,marks,branch from student where branch NOT in("cse","mechanical");
+--------+-------+----------+
| name | marks | branch |
+--------+-------+----------+
| Aagman | 80 | Chemical |
| Anjali | 63 | IT |
| Keshav | 90 | Chemical |
+--------+-------+----------+
3 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Use BETWEEN Statement
MySQL provides some special operator BETWEEN which is used with the WHERE clause.
mysql> select * from student where DOB between '2002/01/01' and '2003/12/31';
+--------+--------+------+--------+------------+-------+
| stu_id | name | age | gender | DOB | City |
+--------+--------+------+--------+------------+-------+
| 108 | Aagman | 18 | M | 2003-08-12 | Delhi |
| 110 | Bhavya | 19 | M | 2002-03-08 | Delhi |
+--------+--------+------+--------+------------+-------+
2 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Using IN statement
If we want to display certain data and the condition value is in a form of a list, the IN operator is used. This operator selects values that match any value in the given list.
mysql> select stu_id,name,branch from student
-> where branch in("CSE","IT");
+--------+--------+--------+
| stu_id | name | branch |
+--------+--------+--------+
| 107 | Aditya | CSE |
| 109 | Anjali | IT |
| 112 | Richa | CSE |
+--------+--------+--------+
3 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Using % (LIKE-operator) statement
If you want to do pattern searching we use the Wildcard operator which is the LIKE operator, following the query using the same operator to find the name starting with the letter ‘B’.
mysql> select * from student where name like 'B%';
+--------+--------+------+--------+------------+-------+
| stu_id | name | age | gender | DOB | City |
+--------+--------+------+--------+------------+-------+
| 110 | Bhavya | 19 | M | 2002-03-08 | Delhi |
+--------+--------+------+--------+------------+-------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Using % and _ (LIKE-operator) Statement
For Pattern-searching using the LIKE-operator and displaying data for the query, find the name with the second last letter of the name as ‘Y’.
mysql> select * from student where name like '%y_';
+--------+--------+------+--------+------------+-------+
| stu_id | name | age | gender | DOB | City |
+--------+--------+------+--------+------------+-------+
| 107 | Aditya | 17 | M | 2004-12-25 | Delhi |
| 110 | Bhavya | 19 | M | 2002-03-08 | Delhi |
+--------+--------+------+--------+------------+-------+
2 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)SQL ALIASES
Alias is nothing but a temporary name that gives a database table or its column a different name. This Is used with select statements. This does not affect the real name of the table or column name.
mysql> select stu_id as ID, name as Student_name from student clg_stu;
+-----+--------------+
| ID | Student_name |
+-----+--------------+
| 107 | Aditya |
| 108 | Aagman |
| 109 | Anjali |
| 110 | Bhavya |
| 112 | Richa |
+-----+--------------+
5 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Using ORDER BY statement
If we have a variety of unsorted data in a table we use the ORDER BY clause in a query to sort data based on single or multiple columns in ascending or descending order.
mysql> select * from student order by date_of_Birth desc;
+--------+--------+------+--------+---------------+
| stu_id | name | age | gender | Date_of_Birth |
+--------+--------+------+--------+---------------+
| 107 | Aditya | 17 | M | 2004-12-25 |
| 112 | Richa | 17 | F | 2004-05-19 |
| 109 | Anjali | 17 | F | 2004-04-17 |
| 108 | Aagman | 18 | M | 2003-08-12 |
| 110 | Bhavya | 19 | M | 2002-03-08 |
+--------+--------+------+--------+---------------+
5 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Using GROUP BY statement
The GROUP BY clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns.
mysql> select branch, count(*) no_of_students
-> from student
-> where branch in("CSE","IT")
-> group by branch;
+--------+----------------+
| branch | no_of_students |
+--------+----------------+
| CSE | 2 |
| IT | 1 |
+--------+----------------+
2 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Using HAVING clause
If we have a GROUP BY clause in a SELECT statement the HAVING clause complements the query by filtering the entries in a table.
mysql> select stu_id,name,branch,marks from student;
+--------+---------+------------+-------+
| stu_id | name | branch | marks |
+--------+---------+------------+-------+
| 107 | Aditya | CSE | 50 |
| 108 | Aagman | Chemical | NULL |
| 109 | Anjali | IT | 70 |
| 110 | Bhavya | Mechanical | 75 |
| 112 | Richa | CSE | 50 |
| 121 | Keshav | Chemical | NULL |
| 122 | Payal | Mechanical | 75 |
| 123 | Shaurya | CSE | 50 |
+--------+---------+------------+-------+
8 rows in set (0.00 sec)
mysql> select branch, sum(marks)
-> from student
-> group by branch
-> having max(marks)>50;
+------------+------------+
| branch | sum(marks) |
+------------+------------+
| IT | 70 |
| Mechanical | 150 |
+------------+------------+
2 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Copy DATA from one Table to another
If you have a table and wanted to copy its data to another table provided the table structure of both must be the same then we can use the following query:
mysql> select*from student_copy;
Empty set (0.00 sec)
mysql> Insert into student_copy
-> select stu_id,name,age,gender,Date_of_Birth from student;
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> select*from student_Copy;
+--------+---------+------+--------+------------+
| stu_id | name | age | gender | DOB |
+--------+---------+------+--------+------------+
| 107 | Aditya | 17 | M | 2004-12-25 |
| 108 | Aagman | 18 | M | 2003-08-12 |
| 109 | Anjali | 17 | F | 2004-04-17 |
| 110 | Bhavya | 19 | M | 2002-03-08 |
| 112 | Richa | 17 | F | 2004-05-19 |
| 121 | Keshav | 18 | M | 2003-07-29 |
| 122 | Payal | 18 | F | 2003-01-04 |
| 123 | Shaurya | 17 | M | 2004-02-14 |
+--------+---------+------+--------+------------+
8 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Using MAX Function
The MAX() function is used to find the greatest or highest numerical or alphabetical value present in any column or expression dependent on the column.
mysql> select*from student;
+--------+---------+------+--------+---------------+------------+-------+
| stu_id | name | age | gender | Date_of_Birth | branch | marks |
+--------+---------+------+--------+---------------+------------+-------+
| 107 | Aditya | 17 | M | 2004-12-25 | CSE | 50 |
| 108 | Aagman | 18 | M | 2003-08-12 | Chemical | 80 |
| 109 | Anjali | 17 | F | 2004-04-17 | IT | 63 |
| 110 | Bhavya | 19 | M | 2002-03-08 | Mechanical | 75 |
| 112 | Richa | 17 | F | 2004-05-19 | CSE | 100 |
| 121 | Keshav | 18 | M | 2003-07-29 | Chemical | 90 |
| 122 | Payal | 18 | F | 2003-01-04 | Mechanical | 75 |
| 123 | Shaurya | 17 | M | 2004-02-14 | CSE | 15 |
+--------+---------+------+--------+---------------+------------+-------+
8 rows in set (0.00 sec)
mysql> select max(marks) from student;
+------------+
| max(marks) |
+------------+
| 100 |
+------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Using MIN Function
The MIN() method finds the smallest numerical or alphabetical in a given collection of values for any column or expression dependent on the column.
mysql> select min(marks) from student;
+------------+
| min(marks) |
+------------+
| 15 |
+------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Using SUM Function
To find the sum of records in a table based on a column the SUM() function is used.
mysql> select sum(marks) from student;
+------------+
| sum(marks) |
+------------+
| 548 |
+------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Using AVG() Function
AVG() function return the average value of the numerical data of a column or expression.
mysql> select avg(marks) from student;
+------------+
| avg(marks) |
+------------+
| 68.5000 |
+------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Using COUNT Function
To display the count of records in a particular row or an entire table, the COUNT() function is used:
mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
| 8 |
+----------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)JOIN tables Using Cartesian Product
a SQL JOIN clause for merging rows from two or more tables based on a shared field. We use the following query to find the Cartesian product of two tables:
mysql> select*from players;
+---------+--------+
| roll_no | name |
+---------+--------+
| 1 | Vihaan |
| 2 | Rishi |
| 3 | Suresh |
+---------+--------+
3 rows in set (0.00 sec)
mysql> select*from sports;
+---------+-----------+
| game_no | game_name |
+---------+-----------+
| 10 | Cricket |
| 11 | Football |
+---------+-----------+
2 rows in set (0.00 sec)
mysql> select name,game_name from players,sports;
+--------+-----------+
| name | game_name |
+--------+-----------+
| Vihaan | Football |
| Vihaan | Cricket |
| Rishi | Football |
| Rishi | Cricket |
| Suresh | Football |
| Suresh | Cricket |
+--------+-----------+
6 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)JOIN tables with EQUI JOIN
If we want to join two tables based on a common field and establish a relationship between them using the “=” equal to sign, the EQUI JOIN condition can is applicable and the query to execute EQUI JOIN is given below:
mysql> select student.stu_id,name,block_name,room_no
-> from student,hostel
-> where student.stu_id=hostel.stu_id
-> order by stu_id desc;
+--------+--------+------------+---------+
| stu_id | name | block_name | room_no |
+--------+--------+------------+---------+
| 122 | Payal | B1 | 15 |
| 121 | Keshav | B3 | 41 |
| 110 | Bhavya | B9 | 19 |
| 109 | Anjali | B5 | 32 |
| 108 | Aagman | B2 | 7 |
| 107 | Aditya | B2 | 10 |
+--------+--------+------------+---------+
6 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)JOIN tables using NON-EQUI JOIN
The Non-Equi join is a type of join in which column data from different tables are compared on the basis of inequality i.e. other than logical operators like >,<, or =. Each row of the source table’s joint to the column’s value gets compared to corresponding values in another table.
mysql> select student.stu_id,name,year_of_study
-> from student,hostel
-> where student.stu_id between 100 and 109;
+--------+--------+---------------+
| stu_id | name | year_of_study |
+--------+--------+---------------+
| 109 | Anjali | 3rd |
| 108 | Aagman | 3rd |
| 107 | Aditya | 3rd |
| 109 | Anjali | 1st |
| 108 | Aagman | 1st |
| 107 | Aditya | 1st |
| 109 | Anjali | 2nd |
| 108 | Aagman | 2nd |
| 107 | Aditya | 2nd |
| 109 | Anjali | 3rd |
| 108 | Aagman | 3rd |
| 107 | Aditya | 3rd |
| 109 | Anjali | 4th |
| 108 | Aagman | 4th |
| 107 | Aditya | 4th |
| 109 | Anjali | 1st |
| 108 | Aagman | 1st |
| 107 | Aditya | 1st |
+--------+--------+---------------+
18 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Using UNION statement
The UNION operator is used to combine two or more SELECT statement queries, merge the sets of tables, and produce a table containing data from the provided query.
mysql> select*from players;
+---------+--------+
| roll_no | name |
+---------+--------+
| 1 | Vihaan |
| 2 | Rishi |
| 3 | Suresh |
+---------+--------+
3 rows in set (0.00 sec)
mysql> select*from player_girls;
+---------+--------+
| roll_no | name |
+---------+--------+
| 5 | preeti |
| 7 | ruchi |
+---------+--------+
2 rows in set (0.00 sec)
mysql> select roll_no,name from players
-> union select roll_no,name from player_girls;
+---------+--------+
| roll_no | name |
+---------+--------+
| 1 | Vihaan |
| 2 | Rishi |
| 3 | Suresh |
| 5 | preeti |
| 7 | ruchi |
+---------+--------+
5 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Using the NATURAL JOIN statement
If you want to join two tables without specifying the foreign key i.e. common column we can use the NATURAL JOIN command it detects the common column automatically and displays data according to the SELECT statement.
mysql> select stu_id,name,year_of_study
-> from student natural join hostel;
+--------+--------+---------------+
| stu_id | name | year_of_study |
+--------+--------+---------------+
| 107 | Aditya | 3rd |
| 122 | Payal | 1st |
| 110 | Bhavya | 2nd |
| 108 | Aagman | 3rd |
| 121 | Keshav | 4th |
| 109 | Anjali | 1st |
+--------+--------+---------------+
6 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Using REPLACE Function
REPLACE() is the function used to replace the content of one substring with another substring. The following query explains the same:-
mysql> select replace("Python is an example of RDBMS","Python","MySQL");
+-----------------------------------------------------------+
| replace("Python is an example of RDBMS","Python","MySQL") |
+-----------------------------------------------------------+
| MySQL is an example of RDBMS |
+-----------------------------------------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Using the REVERSE Function
We can use the REVERSE() function to basically reverse every datatype of the record chosen to display using the SELECT statement.
mysql> select Reverse(name),Reverse(stu_id) from student;
+---------------+-----------------+
| Reverse(name) | Reverse(stu_id) |
+---------------+-----------------+
| namgaA | 801 |
| aytidA | 701 |
| ilajnA | 901 |
| ayvahB | 011 |
| vahseK | 121 |
| layaP | 221 |
| ahciR | 211 |
| ayruahS | 321 |
+---------------+-----------------+
8 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Comments in MySQL
Sometimes we have to write comments to explain something to the other person or as a reference to ourselves. To add comments before after or in between a query we use /* to start the comment and */ to end a comment this can be used as single-line as well as multi-line comments.
mysql> select*from student /*this is a comment
/*> Above query displays all records in a table */
-> ;
+--------+---------+------+--------+---------------+------------+-------+
| stu_id | name | age | gender | Date_of_Birth | branch | marks |
+--------+---------+------+--------+---------------+------------+-------+
| 107 | Aditya | 17 | M | 2004-12-25 | CSE | 50 |
| 108 | Aagman | 18 | M | 2003-08-12 | Chemical | 80 |
| 109 | Anjali | 17 | F | 2004-04-17 | IT | 63 |
| 110 | Bhavya | 19 | M | 2002-03-08 | Mechanical | 75 |
| 112 | Richa | 17 | F | 2004-05-19 | CSE | 100 |
| 121 | Keshav | 18 | M | 2003-07-29 | Chemical | 90 |
| 122 | Payal | 18 | F | 2003-01-04 | Mechanical | 75 |
| 123 | Shaurya | 17 | M | 2004-02-14 | CSE | 15 |
+--------+---------+------+--------+---------------+------------+-------+
8 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Using the DISTINCT statement
If you have a range of records with data repeating in a column and you want to display distinct data we use the DISTINCT command. The following query is an example of the same:
mysql> select*from student;
+--------+---------+------+--------+---------------+------------+-------+
| stu_id | name | age | gender | Date_of_Birth | branch | marks |
+--------+---------+------+--------+---------------+------------+-------+
| 107 | Aditya | 17 | M | 2004-12-25 | CSE | 50 |
| 108 | Aagman | 18 | M | 2003-08-12 | Chemical | 80 |
| 109 | Anjali | 17 | F | 2004-04-17 | IT | 63 |
| 110 | Bhavya | 19 | M | 2002-03-08 | Mechanical | 75 |
| 112 | Richa | 17 | F | 2004-05-19 | CSE | 100 |
| 121 | Keshav | 18 | M | 2003-07-29 | Chemical | 90 |
| 122 | Payal | 18 | F | 2003-01-04 | Mechanical | 75 |
| 123 | Shaurya | 17 | M | 2004-02-14 | CSE | 15 |
+--------+---------+------+--------+---------------+------------+-------+
8 rows in set (0.00 sec)
mysql> select Distinct(branch) from student;
+------------+
| branch |
+------------+
| CSE |
| Chemical |
| IT |
| Mechanical |
+------------+
4 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Using LENGTH() Function
The LENGTH() function returns the length of the string inserted in the function. Can be used with SELECT statements.
mysql> select name,length(name) as name_length from student;
+---------+-------------+
| name | name_length |
+---------+-------------+
| Aagman | 6 |
| Aditya | 6 |
| Anjali | 6 |
| Bhavya | 6 |
| Keshav | 6 |
| Payal | 5 |
| Richa | 5 |
| Shaurya | 7 |
+---------+-------------+
8 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Using POSITION() function
Given a range of data and we have to find the position or index of a character in a substring of single or multiple records then we use the POSITION() Function. Query using the same function is depicted below:
mysql> SELECT POSITION("a" IN Name)
-> from student;
+-----------------------+
| POSITION("a" IN Name) |
+-----------------------+
| 1 |
| 1 |
| 1 |
| 3 |
| 5 |
| 2 |
| 5 |
| 3 |
+-----------------------+
8 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Using CURRENT_DATE and CURRENT_TIME Function
Current_date() and current_time() are used to display the date and time at the Present instance when the query is executed.
mysql> select current_date(),current_time();
+----------------+----------------+
| current_date() | current_time() |
+----------------+----------------+
| 2022-11-23 | 19:35:32 |
+----------------+----------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Using the UPPER() Function
If we want to display all the column records in uppercase, we use the UPPER() function.
mysql> select UPPER(branch) as Upp_case_branch from student;
+-----------------+
| Upp_case_branch |
+-----------------+
| CSE |
| CHEMICAL |
| IT |
| MECHANICAL |
| CSE |
| CHEMICAL |
| MECHANICAL |
| CSE |
+-----------------+
8 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Using EXTRACT() Function
EXTRACT() is a function that can extract the details from the DATE and TIME datatype records in a table. Among the data that can be extracted are Date, Year, Month, Week, Hours, seconds, minutes, microseconds, year_month, Hour_Minute, etc.
mysql> select Extract(Year from Date_of_Birth) as Year
,
-> Extract(Month from Date_of_Birth) as Month,
-> Extract(Week from Date_of_birth) as Week_of_year
-> from student;
+------+-------+--------------+
| Year | Month | Week_of_year |
+------+-------+--------------+
| 2004 | 12 | 51 |
| 2003 | 8 | 32 |
| 2004 | 4 | 15 |
| 2002 | 3 | 9 |
| 2004 | 5 | 20 |
| 2003 | 7 | 30 |
| 2003 | 1 | 0 |
| 2004 | 2 | 6 |
+------+-------+--------------+
8 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Conclusion
First of all, congratulations!🥳 You are just amazing for making it this far. In today’s article, we saw 50 SQL script examples that make you master SQL. It’s time we conclude our article today. I hope you found my article useful. If you have any queries, feel free to comment down below. Have a wonderful day ahead!😊
Sharing is caring
Did you like what Pravin Gupta wrote? Thank them for their work by sharing it on social media.
No comments so far
Curious about this topic? Continue your journey with these coding courses:
