Advanced SQL: Subqueries in SQL, stored procedures, & more

Subqueries in SQL

A subquery is a select query that is enclosed inside another query. The inner select query is usually used to determine the results of the outer select query. Subqueries are a major part of Advanced SQL.

Look at this concept with the help of an example.

• Find the employees whose salary is greater than the average salary.

In this scenario, you will use a subquery to get those employees whose salary is greater than the average salary from the employee table.

Below is the employees' table.

select emp_name, dept, salary

from employees where salary > (select avg(salary) from employees);

The following SQL query shows the employee name, department, and salary columns of employees whose salary is greater than the average salary.

Now, look at another example to understand the subqueries in SQL.

• Find all the employees whose salary is greater than John’s salary.

select emp_name, gender, dept, salary

from employees where salary >

(select salary from employees where emp_name = 'John');

The following SQL query will show all the employees whose salary is greater than John’s salary, i.e. 67000.

Now, that you have understood how subqueries work, look at another advanced SQL topic.

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program

Stored Procedure

A stored procedure is an SQL code that you can save so that you can use the code over and over again. So, if you want to write a query repeatedly, save it as a stored procedure and then call it to execute it. In this Advanced SQL tutorial, you will learn Stored Procedures in depth.

Syntax:

CREATE or REPLACE PROCEDURE name(parameters)

IS

variables;

BEGIN

//statements;

END;

Now, understand this with the help of an example.

• Find the players who have scored over six goals.

In this scenario, you will create a stored procedure and then call the stored procedure.

Below is the player table.

delimiter &&

create procedure top_players()

begin

select name, country, goals

from players where goals>6;

End &&

delimiter ;

call top_players();

The following SQL query will show the name, country, and goals column for players who scored greater than 6 goals.

Now, see another example to understand the Stored Procedure in SQL.

Stored Procedure Using the IN operator

• Create a procedure that displays the top records of employees' tables based on their salaries.

In this scenario, you will create a stored procedure using the IN operator and then call the procedure.

Shown below is the emp_details table.

delimiter //

create procedure sp_sortBySalary(IN var int)

begin

select name, age, salary from emp_details

order by salary desc limit var;

end //

delimiter ;

call sp_sortBySalary(3);

The following SQL query displays the top 3 records with name, age, and salary from emp_details.

Trigger in SQL

A trigger is a special type of Stored Procedure that runs automatically when an event occurs in the database server. This Advanced SQL tutorial will cover trigger in SQL in detail.

There are mainly three types of triggers in SQL, they are:

1. Data Manipulation Trigger
2. Data Definition Trigger
3. Logon Triggers

You will understand how to use the ‘before insert trigger’ with the help of an example.

For this, you will create a simple students' table that will have the attributes - student roll number, age, name, and student marks. Before you insert records in the table, you will have to check if the marks are less than 0. If true, the trigger will set the marks to 50.

Now, create a student table.

create table student

(st_roll int, age int, name varchar(30), mark float);

delimiter //

create trigger marks_verify_st

before insert on student

for each row

if new.mark < 0 then set new.mark=50;

end if; //

insert into student

values(501,10,'Ruth', 75.0),

(502,12, 'Mike', -20.5),

(503, 13, 'Dave', 90.0),

(504, 10, "Jacobs", -12.5);

select * from student;

The following SQL query will show how the trigger sets the marks = 50 when the entries for marks are less than zero.

Learn From The Best Mentors in the Industry!

Automation Testing Masters ProgramExplore Program

Views in SQL

Views are actually virtual tables that do not store any data of their own but display data stored in other tables.

You will understand this with the help of an example.

Create a simple view by selecting specific columns from the customer table.

Below is how the customer table is present in the classicmodels database.

create view cust_details

as

select customerName, phone, city

from customers;

select * from cust_details;

Now, you must create a view joining the two tables - products and product lines.

First, see the records present in the products and product lines table.

Products Table

Product Lines Table

In the below query, you are finding the product name, quantity in stock, msrp, and text description for each product.

create view product_description

as

select productName, quantityinstock,msrp,textdescription

from products as p inner join productlines as pl

on p.productline = pl.productline;

select * from product_description;

Rename a View

SQL allows you to rename a view. Here, you will change the view name product_description to vehicle_description.

rename table product_description 

to vehicle_description; 

Display Views

To view all the views, you can write the following query.

show full tables

where table_type = 'VIEW';

Delete View 

To delete a view, you can use the drop command.

drop view cust_details;

Windows Functions

In this Advanced SQL tutorial, you will learn different windows functions.

The developers incorporated Windows functions in the MySQL 8.0 version.

Windows Functions in MySQL are useful applications in solving analytical problems and have become an essential part of advanced SQL. 

 Let’s understand this with the help of examples.

Find the total combined salary of the employees for each department from the employee table.

select emp_name,age,dept,

sum(salary) over (partition by dept) as total_salary

from employees;

The above SQL query will create a new column total_salary and you get the output with employee names, their respective departments, and the highest salary being credited in their departments.

Row Number

The Row Number Function gives a sequential integer to every row within its partition.

Let’s understand this with the help of an example.

select row_number() over(order by salary) as row_num,

emp_name, salary from employees order by salary;

The following SQL query will create a column row_num and provide the sequential integer order to each record in the employee table. It sort the result by salary in ascending order.

The row number function can also be used to find duplicate values in a table.

To understand this, first, you will create a table demo and insert values in the table.

create table demo (st_id int, st_name varchar(20));

Now, use the row number function to find duplicate values in the employee table.

select st_id, st_name, row_number() over 

(partition by st_id, st_name order by st_id) as row_num

from demo;

The following SQL query will create a column row_num and in that column, this function gives the count = 1 for each unique record. Else, the query will add 1 to the duplicate record.

Advance Your MERN Stack Career in 6 Months!

Full Stack Developer - MERN StackEXPLORE COURSE

Rank Function

The Rank Function assigns a rank to a particular column. There are gaps in the sequence of rank values when two or more rows have the same rank.

To understand this, first, you will create a table demo1 and insert a few records in the table.

create table demo1(var_a int);on

select var_a,

rank() over (order by var_a) as test_rank

from demo1;

The following SQL query will provide a test_rank to every var_a record. If the value of var_a is the same, the function will give the same rank to that record and skip the next rank number for the next record.

First value()

The first value function provides the output value of the specified expression w.r.t. the first row in the window frame.

Now, understand this function with the help of an example.

select emp_name, age, salary, first_value(emp_name)

over (order by salary desc) as highest_salary from employees;

The above SQL query will search for the employee with the highest salary and the first function will populate the same name in the highest_salary column throughout the table. 

Conclusion

SQL was developed to access the data within the Relational databases. Currently, the tech companies are in search of well-versed SQL Developers, who have a great command over advanced SQL concepts. This tutorial will help you learn the advanced concepts of SQL.

If you have any questions or inputs for our editorial team regarding this “The Complete Guide to Advanced SQL” tutorial, do share them in the comments section below. Our team will review them and help solve them for you very soon!

To get certified in SQL and start your career in it, check this course link: SQL Certification Course.

Happy learning!