SQL queries

 =========================================

49 sql query : find nth highest salary - 54 ,,

=========================================

Difference between Row_number(), Rank() and Dense_rank() is 

=========================================

In case of value is same then 

> Row_number()

 

# employee_id, name, monthofsalary, salary, row_num

'403', 'Rajesh Verma', '2025-03-10', '95000', '1'

'403', 'Rajesh Verma', '2025-03-10', '95000', '2'

'401', 'Arjun Malhotra', '2025-01-15', '89000', '3'

'401', 'Arjun Malhotra', '2025-01-15', '89000', '4'

 

> Rank  () : 

Assigns same Rank but the (next rank )= Prev.Rank + (total previous ranks)

eg :  

1 ,2,2,2, 5  = 2 +(3 ranks) = 5

> Dense_Rank() : 

Assigns same rank and next rank = Prev. Rank + 1

Next rank =  2 +1 =3

1,2,2,2,3 

=========================================

a) Rownum :

( To find 2nd highest salary using ROW_NUMBER)

select employee_id,salary from (

   select e.*,row_number() over (order by salary desc) 

   as row_num from employee e ) a

where row_num = 3 ;

# employee_id, salary

'401', '89000'

 ======================

# employee_id, name, monthofsalary, salary, row_num

'403', 'Rajesh Verma', '2025-03-10', '95000', '1'

'403', 'Rajesh Verma', '2025-03-10', '95000', '2'

'401', 'Arjun Malhotra', '2025-01-15', '89000', '3'

'401', 'Arjun Malhotra', '2025-01-15', '89000', '4'

'402', 'Meera Kapoor', '2025-02-20', '78000', '5'

======================

b) using Rank :

 step 1 : 

-- To show only 2nd highest -- 

select * from

    (select e.*,rank() over (order by salary desc) as rank 

     from emp  e )

where rank = 2;

step 2 : 

-- ** To display ranks ** --  

    select  rank() over (order by salary desc) as rank,e.* 

    from emp  e ;

======================

c ) Dense Rank ( BEST )

-- To show only 2nd highest -- 

select * from

    (select e.*,dense_rank() over (order by salary desc) as rank 

     from emp  e )

where rank = 2;

-- ** To display ranks ** --  

    select  dense_rank() over (order by salary desc) as rank,e.* 

    from emp  e ;

======================

d) using count distinct

select empno ,sal from emp e1

where 3 =

(select count(distinct sal )

from emp e2

where e2.sal > e1.sal )

=========================================

sql query : find and delete duplicate rows Two methods to find and delete duplicate rows

-=================================

1. Delete all rows other than min id value ..

(used when id column is not duplicate but other columns values are duplicate)

===========================================

Method 1. using id Column (if ID column is unique, but title is duplicate)

================================

delete films

where film_id 

not in (select min(film_id) from films group by title, rel_date)

===========================================

Method 2. using CTE ( Two steps)

============================================

step 1 :

with CTE as

(select id ,name , email , row_number()

over (partition by name , email order by name , email ) rownum

from sales

step 2 :

Delete from CTE where rownum > 1

===========================================

Method 3. using ROWID

============================================

Delect from table1

where rowid not in (select min(rowid) from table 1

group by col1,col2)

========================================================================

what is inner join and outer join count of below

========================================================================

A       B

1       1

1       1

2       2

2 

3

null   null Inner join = count of matching rows

outer join = inner join + left side unmatched

inner = 6 rows

left outer = 6 + 1 = 7 rows

=======================================================================

 53 . How to write the INNER JOIN ,OUTER JOIN QUERIES 

 =======================================================================

 --  // Create database //

 use db1;

show tables;

create table t1 ( id int , name varchar(44) ) ;

create table t2 ( id int , name varchar(44) ) ;

--  // insert data  into tables  //

insert into t1 values( 1, "x1");

insert into t1 values( 2, "x2");

insert into t1 values( 3, "x3");

insert into t1 values( 4, "x4");

select * from t1;

insert into t2 values( 1, "x1");

insert into t2 values( 2, "x2");

insert into t2 values( 5, "x5");

insert into t2 values( 6, "x6");

select * from t2;

 ------------------

-- // inner join //

select * from t1 join t2 on t1.id = t2.id

-- // left join //

select * from t1 left join t2 on t1.id = t2.id

-- // Right  join //

select * from t1 right join t2 on t1.id = t2.id

- // fuill outer  join //

 select * from t1 left join t2 on t1.id = t2.id

union

 select * from t1 right join t2 on t1.id = t2.id

================================================================

54 . How to find cumulative check - if employee got hike or not 

================================================================

-- --// create employee table // -- 

create table employee (employee_id int ,name varchar(44),

    monthofsalary date,

    salary int);

-- alter table employee     rename column month to  monthOfSalary;

-- --// insert data to employee table // -- 

-- truncate table employee;

insert into employee values(1,"john",'2004-01-01',13000);

insert into employee values(1,"john",'2004-02-01',13000);

insert into employee values(1,"john",'2004-03-01',13000);

insert into employee values(1,"john",'2004-04-01',13000);

insert into employee values(1,"john",'2004-05-01',13000);

insert into employee values(1,"john",'2004-06-01',13000);

insert into employee values(1,"john",'2004-07-01',14000);

insert into employee values(1,"john",'2004-08-01',15000);

insert into employee values(1,"john",'2004-09-01',15000);

-- --// show data in employee table // -- 

select employee_id,name,month(monthofsalary),salary from employee;

-- -------------// query to find cumulativ // ------------------------------

 WITH salarychanges

     AS (SELECT employee_id,
                name,
                monthofsalary,
                salary,
                Lag(salary, 1)
                  over (
                    PARTITION BY employee_id
                    ORDER BY monthofsalary) AS previous_salary
         FROM   employee)

SELECT employee_id,
       name,
       monthofsalary,
       salary,
       CASE
         WHEN salary > previous_salary THEN 'yes'
         ELSE 'no'
       END
       AS salary_hike,
       SUM(salary)
         over (
           PARTITION BY employee_id
           ORDER BY monthofsalary ROWS BETWEEN unbounded preceding AND CURRENT
         ROW) AS
       cumulative_salary
FROM   salarychanges; 

-- ========================================================

 

  ================================================================

55. How to find if a single user purchased same product on different dates

  ================================================================

 

create  table orders( userid int ,prodid int, orderdate date);

insert into orders values ( 1,101,'2024-1-1');

insert into orders values ( 1,101,'2024-1-2');

insert into orders values ( 1,101,'2024-1-1');

insert into orders values ( 2,102,'2024-1-1');

insert into orders values ( 2,102,'2024-1-1');

select * from orders;

-- --------------------------------------------------------------------------------

-- // count of  users who purchased same product on different dates  // 

-- --------------------------------------------------------------------------------

SELECT      userid,  prodid,  COUNT(DISTINCT orderdate) AS Noofdays_purchased 

FROM    orders GROUP BY userid,prodid;

================================================================

SQL - show highest salary in each department 

================================================================

================================================================

SQL - count(*) , count(column) , count(1)

================================================================

 select count(*),count(distinct deptid)  from emp;

================================================================

-- BOTH COUNT(*) AND COUNT(1) ARE SAME BUT COUNT(1) IS FASTER 

================================================================

===========================================================

==========================================================

 

================================================================

GROUP BY TASKS

================================================================

10.Display duplicate Records

11.Display Top 4th highest Salary

12.Find 2nd lowest salary

13.Display Top Two salaries in each department wise

14.Find each department wise sum of salaries

15.Display maximum salary department wise along with employee details

16.to display 5 to 7 rows from a table

================================================================

SQL FUNCTIONS

================================================================

'

================================================================

SQL - CONSTRAINTS

================================================================

• Not NULL
• Unique
• Primary key
• foreign Key
• Check (check  columnname > 0 ) 

================================================================

sql query : TO get highest ordered products in each state

==============================================================

CREATE DATABASE retail_db;

USE retail_db;

-- ----------------------------------------------

CREATE TABLE orders_table
             (
                          order_id    INT NOT NULL,
                          customer_id INT NOT NULL,
                          product_id  INT NOT NULL,
                          quantity    INT NOT NULL,
                          price       DECIMAL NOT NULL
             );

CREATE TABLE customer_table
             (
                          customer_id   INT NOT NULL,
                          customer_name VARCHAR(50),
                          state_name    VARCHAR(50)
             );

-- ----------------------------------------------

INSERT INTO orders_table VALUES
            (
                        101,1001,202,4,1000
            ) ;

INSERT INTO orders_table VALUES
            (
                        102,1002,203,5,2000
            );

INSERT INTO orders_table VALUES
            (
                        103,1003,204,6,3000
            );

INSERT INTO orders_table VALUES
            (
                        104,1004,205,7,4000
            );

SELECT * FROM   orders_table;

-- ----------------------------------------------

INSERT INTO customer_table VALUES
            (
                        1001,
                        'Liam',
                        ' Alabama'
            );INSERT INTO customer_table VALUES
            (
                        1002,
                        'Noah',
                        ' Alaska'
            );INSERT INTO customer_table VALUES
            (
                        1003,
                        'Oliver',
                        ' Arizona'
            );INSERT INTO customer_table VALUES
            (
                        1004,
                        'James',
                        ' Arkansas'
            );SELECT *
FROM   customer_table;

-- ----------------------------------------------

TRUNCATE customer_table ;

-- ----------------------------------------------

SELECT   c.state_name,
         o.product_id,
         Count(o.order_id) AS highest_order_count
FROM     orders_table o
JOIN     customer_table c
ON       o.customer_id = c.customer_id
GROUP BY c.state_name,
         o.product_id
ORDER BY c.state_name,
         highest_order_count DESC

-- =========================================================

-- ==  OR TRY BELOW === --- 

WITH ordercounts
     AS (SELECT c.state_name,
                o.product_id,
                Count(o.order_id)                    AS order_count,
                Rank()
                  OVER (
                    partition BY c.state_name
                    ORDER BY Count(o.order_id) DESC) AS ranka
         FROM   orders_table o
                JOIN customer_table c
                  ON o.customer_id = c.customer_id
         GROUP  BY c.state_name,
                   o.product_id)
SELECT state_name,
       product_id,
       order_count
FROM   ordercounts
WHERE  ranka = 1; 

-- ----------------------------------------------

================================================================

sql query :  

==============================================================

==