Subqueries

 Subqueries

Subqueries

  • This is very intuitive way of writing queries.
  • Breakdown bigger problems into smaller ones.
  • Will use result of smaller problems to get final answer.
Process of working on it.

Suppose we have a question to answer:
Get students detail who have psp greater than student with id as 1.

So to solve it steps are:
1. Get student2 psp
    select psp from students where id = 1;

2. Now use it to generate the other details.
    select * from students where psp > (select psp from students where id = 1); 

Time complexity of this query is O(N2)
as for each row of data for main query inner subquery will run on the table all records and then the result extracted from the inner query will be used for comparison.

Subqueries are costlier then joints with respect to resources usage and even in time complexity.

use school;

show tables;

create table students(
id int not null primary key auto_increment,
name varchar(500),
b_id int ,
psp int,
CONSTRAINT fk_student_batch
        FOREIGN KEY (b_id)
        REFERENCES batches(b_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
        );
        
create table batches(
b_id int not null primary key auto_increment,
name varchar(500)
);


insert into batches (name) values('A'), ('B'), ('C');

select * from batches;

select * from students;

insert into students (name, b_id, psp) values('Puneet Kumar Singh', 1, 99), ('Devansh Singh', 2, 100), ('Anu Singh', 3, 100), ('Rahul Soni', 1, 30), ('Rupesh Mishra', null , 56);

-- find all the students having psp > (psp of id = 1)
select * from students where psp > (select psp from students where id = 1);
-- as you can see in above query first we have found the psp and then come to main query to find all the members who have psp then that id 1 person.

-- Find data of all the students having psp > ( min psp of b_id = 1 )
select * from students where psp > ( select min(psp) from students where b_id = 1);
-- so in subquery we need to first find the desired condition information and then need to use it in main query to get result.

-- Find all rating where average rental_rate > ( The global average rental rate )
use sakila;
select * from film;

-- select global avg rate for  rental rate.
select avg(rental_rate) from film;

-- avg rental rate for ratings
select avg(rental_rate), rating
from film
group by rating;

-- complete query
select avg(rental_rate), rating
from film
group by rating
havingavg(rental_rate) > (
select avg(rental_rate) from film);

Subqueries and IN Clause:

When your subquery give more than one value as result then you need to use IN clause to compare them with main query.


use school;

create table users(
id int not null primary key auto_increment,
name varchar(500),
is_student int not null,
is_TA int not null);


insert into users ( name, is_student, is_TA) values ('Gaurav', 1, 1),
('Rohit', 1, 0), ('Pravallika', 1, 0), ('Krishna', 1, 1),('Nandini', 1, 0), ('Rohit', 0, 1);

select * from users;
-- Get names of all the students who are having same name as TA.

-- Step 1: Get names of all TA.
select name from users where is_TA = 1;

-- Step 2: Get name of students which are in the list of TA names as well as are name of student as well.
select name from users where is_student = 1 and name in (select name from users where is_TA = 1);


Subqueries inside from clause

Whenever we use subquery inside from clause, always use aliasing.
As you will generate another table from this subquery which is going to be used in from clause hence we need to name this table and column present in it if got manipulated.

-- Subquery inside from clause.

-- Find data of all students where psp > (min(psp) among avg(psp) of every batch)
-- step 1: finding avg psp of every batch
select avg(psp) from students group by b_id;
-- step 2: minimum psp among the result of step one.
select min(a.avg_psp) from (select avg(psp) as avg_psp from students group by b_id) as a;
-- step 3: Find all the students whose psp is greater than step 2 output.
select * 
from 
students 
where psp > 
(
select min(a.avg_psp) 
from 
(
select avg(psp) as avg_psp 
from students 
group by b_id
) as a
);

So it is a manditory to give an alias to derived table and column.


ALL and ANY

ALL means to have and condition and to check the detail from entire sample of data or table similarly ANY means or condition.

-- ALL and ANY 
-- Find data of all the students where psp >= (min(psp) of student in every batch)
-- Step 1: Find min(psp) of every batch
select min(psp)
from students
group by b_id;

-- Step 2: Get all the students having psp >= All ( min(psp))
select *
from students
where psp >= All(
select min(psp)
    from students
    group by b_id
    );

-- Find data of all the students where psp >= (min(psp) of student in any batch)
select *
from students
where psp >= Any(
select min(psp)
    from students
    group by b_id
    );

Co-related Subqueries:

Where outer query is related to inner query. It means the input record which is running in outer query will act as input for inner subquery and as a result the comparison or subquery result will chage as per outer query record information.

 -- Co-related Subqueries

-- Find data of all the students where psp > (avg(psp) of their batch)
-- Step 1: Find avg(psp) of every batch 
select avg(psp) from students group by b_id;-- here b_id is variable and will change everytime with respect to the student id which is used for comparison.
-- Step 2: Find the students whose psp is greater then the avg(psp) of his batch.
 select *
 from students s
 where psp > (
 select avg(psp)
 from students
 where b_id = s.b_id);

Exists:


This Keyword will help you find the information from subquery result.


-- Exists
-- Find the actor who has acted in any movie.
select * from sakila.actor;

-- solution to question if we can check that actor_id is present in file_actor table then that actor has worked in any movie else not.
use sakila;
select *
from actor a 
where exists(
select actor_id from film_actor
    where actor_id = a.actor_id);

Comments

Post a Comment

Popular posts from this blog

Aggregate Queries and Views

 Aggregate Queries and Views Aggregate Functions It provide consolidated result it means work on records and then will provide summary of it or the consolidated result of it. It is basically used to find the facts and figures of table. Means max and other similar type of functions which are consolidate in nature. count - it take count of only not null values and will ignore the null values. max min avg sum notes : Distinct is not an aggregate function as it don't combine the records it just help to remove the duplicate records. All aggregate functions by default work on integers or anything which can be converted into countable information. count: Pseudo code: table = [] count = 0 for row in table:     if row[b_id]:          count++ print(count) Note: You cannot use aggregate functions in nested loops. Group by: Trying to make some groups in a data by using some criteria or conditions. It use the column to form the group and take action on that gr...
Read more

Schema Design

Image
 Schema Design Schema Blue print of out database of to define how are we going to create and use our tables in a database. It gives information about: Structure of Database Tables in Database Columns in a Table Primary Key Foreign Key Index Pictorial Representation How to approach a schema design?  Requirement: Scaler will have multiple batches For each batch, we need to store the name, start month and current instructor Each batch of  Scaler will have multiple students. Each batch has multiple classes. For each class, store the name, date and time, instructor of the class. For evey student, we store their name, graduation year, university name, email, phone no. Every student has a buddy, who is also a student A student may move from one batch to another For each batch a student moves to, the date of starting is stored. Every student has a mentor. For every mentor, we store their name and current company name. Store information about all mentor sessions ( time, duration, ...
Read more

Transactions in SQL

Image
 Transactions Now as you can see that in example A is a person who has 1000 rupees in his account and he want to transfer 500 rupees to B who has account balance as 5000. So now this information is stored in a table. Steps to make is possible programatically: Get account balance of A. - > DB call. Check, if A >= amount. - > logical call not a DB call. Debit Rs. 500 from A and updte table. - > DB call Update the balance of B and update the table. - > DB call These 4 steps mechanism complete 1 transaction between two people. Take a scenario: Multiple person are trying to send amount to same accont. Like how it happens at the time of shopping when multiple person comes to purchase goods from a shop then they pay money from their account to shop keeper account. Now suppose a developer has created a method for transaction to transfer amount from one account to another. Scenario: Multiple person are making transaction and transfering amount to same person say is as shopkee...
Read more