Lesson 1 of 7By Simplilearn








Companies process massive amounts of data to keep extracting information for their business growth. The IT industry has seen an enormous demand for senior SQL Developers, over the years. Industry leaders want and strive to make their companies be market leaders in their respective fields, so they want SQL Developers to extract more helpful information. This demand has urged the SQL Developers to learn the Advanced SQL concepts.
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.
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.
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.
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.
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.
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.

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:
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.

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.


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;

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; 
To view all the views, you can write the following query.
show full tables
where table_type = 'VIEW';

To delete a view, you can use the drop command.
drop view cust_details;
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.

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.

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.

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. 

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: Post Graduate Program in Full Stack Web Development.
Happy learning!
Simplilearn is one of the world’s leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.
The Ultimate Guide to Normalization in SQL
Skilling for the Digital Economy: A Role-Based Approach
Everything That You Need to Know About Stored Procedure in SQL
What Is SQL Injection: How to Prevent SQL Injection
What Is SQL Injection: How to Prevent SQL Injection
Managing Data
© 2009 -2024- Simplilearn Solutions
Follow us!
Company
Work with us
Discover
For Businesses
Learn On the Go!
Trending Post Graduate Programs
Trending Bootcamp Programs
Trending Master Programs
Trending Courses
Trending Categories
Trending Resources

source