November 2020 - Mohan M A

November 21, 2020November 21, 2020

Primary and Foreign Key in SQL

Syntax to set EMPLOYEE_ID as primary key in employee table.

ALTER TABLE EMPLOYEE add CONSTRAINT EMPLOYEE_PK PRIMARY KEY(EMPLOYEE_ID)

Syntax to set 2 fields(EMPLOYEE_ID, FIRST_NAME) as primary key in employee table.

ALTER TABLE EMPLOYEE add CONSTRAINT EMPLOYEE_PK PRIMARY KEY(EMPLOYEE_ID, FIRST_NAME)

Syntax to drop primary key on employee table.

ALTER TABLE EMPLOYEE drop CONSTRAINT EMPLOYEE_PK

Syntax to create EMPLOYEE_REF_ID in INCENTIVES table as foreign key with respect to EMPLOYEE_ID in employee table.

ALTER TABLE INCENTIVES ADD CONSTRAINT INCENTIVES_FK FOREIGN KEY(EMPLOYEE_REF_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID)

Syntax to drop foreign key on employee table.

ALTER TABLE INCENTIVES drop CONSTRAINT INCENTIVES_FK

November 20, 2020November 20, 2020

Joins in SQL

Query to select first_name, incentive amount from employee and incentives table for those employees who have incentives.

select FIRST_NAME, INCENTIVE_AMOUNT from employee A inner join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID

Query to select first_name, incentive amount from employee and incentives table for those employees who have incentives and incentive amount greater than 3000.

select FIRST_NAME, INCENTIVE_AMOUNT from employee A inner join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID and INCENTIVE_AMOUNT > 3000

Query to select first_name, incentive amount from employee and incentives table for all employees even if they didn’t get incentives.

select FIRST_NAME, INCENTIVE_AMOUNT from employee A left join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID

Query to select first_name, incentive amount from employee and incentives table for all employees even if they didn’t get incentives and set incentive amount as 0 for those employees who didn’t get incentives.

select FIRST_NAME, IFNULL(INCENTIVE_AMOUNT,0) from employee A left join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID

Query to select first_name, incentive amount from employee and incentives table for all employees who got incentives using right join.

select FIRST_NAME, IFNULL(INCENTIVE_AMOUNT,0) from employee A right join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID

Query to select max incentive with respect to employee from employee and incentives table using sub query.

select DEPARTMENT,(select IFNULL(max(INCENTIVE_AMOUNT),0) from INCENTIVES where EMPLOYEE_REF_ID=EMPLOYEE_ID) Max_incentive from EMPLOYEE

November 20, 2020November 20, 2020

Useful SQL Queries – 1

Query to select employee details from employee table if data exists in incentive table.

select * from EMPLOYEE where exists 
(select * from INCENTIVES)

Query to fetch data that are common in two query results.

select * from EMPLOYEE where EMPLOYEE_ID INTERSECT 
select * from EMPLOYEE where EMPLOYEE_ID < 4

Query to get employee ID’s of those employees who didn’t receive incentives without using sub query.

select EMPLOYEE_ID from EMPLOYEE
MINUS 
select EMPLOYEE_REF_ID from INCENTIVES

Query to select 20% of salary from John, 10% of salary for Roy and for other 15% of salary from employee table.

SELECT FIRST_NAME,
CASE FIRST_NAME 
WHEN 'John' THEN SALARY*.2
WHEN 'Roy' THEN SALARY*.1
ELSE SALARY*.15
END "Deducted_Amount" 
from EMPLOYEE

Query to select Banking as ‘Bank Dept’, Insurance as ‘Insurance Dept’ and Services as ‘Services Dept’ from employee table.

select case DEPARTMENT
when 'Banking' then 'Bank Dept'
when 'Insurance' then 'Insurance Dept'
when 'Services' then 'Services Dept'
end 
from employee

Query to delete employee data from employee table who got incentives in incentive table.

delete from EMPLOYEE where EMPLOYEE_ID in 
(select EMPLOYEE_REF_ID from INCENTIVES)

Insert into employee table Last Name with ” ‘ ” ( Single Quote – Special Character)

Insert into employee (LAST_NAME) values ("Test'")

Query to select last name from employee table which contain only numbers.

select * from employee where 
lower(LAST_NAME)=upper(LAST_NAME)

Query to rank employees based on their incentives for a month.

select FIRST_NAME, INCENTIVE_AMOUNT,
DENSE RANK() OVER 
(PARTITION BY INCENTIVE_DATE
ORDER BY INCENTIVE_AMOUNT DESC) AS RANK
from EMPLOYEE a,
INCENTIVES b 
where a.EMPLOYEE_ID = b.EMPLOYEE_REF_ID

Update incentive table where employee name is ‘John’

update INCENTIVES
set INCENTIVE_AMOUNT='9000'
where EMPLOYEE_REF_ID=(select EMPLOYEE_ID from
employee where FIRST_NAME='John')

November 19, 2020

Cumulative Average or Running Average in SQL

Query to obtain cumulative average or running average from employee table for salary column.

     select Employee_id, Salary,
     AVG(Salary) OVER (ORDER BY Employee_id) 
     As RunningAverage
     from employee;

November 19, 2020

Cumulative Sum or Running Total in SQL

Query to obtain cumulative sum or running total from employee table for salary column.

     select Employee_id, Salary,
     SUM(Salary) OVER (ORDER BY Employee_id) 
     As RunningTotal
     from employee;

November 19, 2020November 19, 2020

Obtaining Grandfather and Grandson Table from Father and Son Table

Query to obtain 1st generation and 3rd generation combination based on 2nd and 3rd generation.

Family table

Father | Son

J | K

K | L

M | N

N | O

SELECT m1.Father AS GrandFather, m2.Son AS GrandSon FROM 
Family m1 join family m2 ON m1.son = m2.father;

Result

Grandfather | Grandson

J | L

K | N

M | O

November 18, 2020November 18, 2020

Group Functions in MySQL

Query to get department, total salary with respect to a department from employee table.

select DEPARTMENT, sum(SALARY) Total_Salary 
from employee 
group by department

Query to get department, total salary with respect to a department from employee table order by total salary descending.

select DEPARTMENT, sum(SALARY) Total_Salary 
from employee 
group by department 
order by Total_Salary desc

Query to get department, number of employees in a department, total salary with respect to a department from employee table order by total salary descending.

select DEPARTMENT, count(FIRST_NAME), 
sum(SALARY) Total_Salary 
from employee 
group by department 
order by Total_Salary desc

Query to get department wise average salary from employee table order by salary ascending.

select DEPARTMENT, avg(SALARY) AvgSalary 
from employee 
group by department 
order by AvgSalary asc

Query to get department wise maximum salary from employee table order by salary ascending.

select DEPARTMENT, max(SALARY) MaxSalary
from employee
group by department
order by MaxSalary asc

Query to get department wise minimum salary from employee table order by salary ascending.

select DEPARTMENT, min(SALARY) MinSalary
from employee
group by department
order by MinSalary asc

Query to select number of employees joined with respect to year and month from employee table.

select year(JOINING_DATE) Join_Year, 
month(JOINING_DATE) Join_Month, 
count(*) Total_Emp 
from employee 
group by year(JOINING_DATE), month(JOINING_DATE)

Query to select department, total salary with respect to a department from employee table where total salary greater than 800000 order by Total_Salary descending.

select department, sum(SALARY) Total_Salary 
from employee
group by department
having sum(SALARY) > 800000 
order by Total_Salary desc

November 18, 2020

SQL Pattern Queries

Query to get names of employees from employee table who has ‘%’ in Last_Name.

select FIRST_NAME from employee where Last_Name like '%\%%'

Query to get last name from employee table after replacing special character with white space.

select REPLACE(LAST_NAME,'%',' ') from employee

November 14, 2020

Happy Deepavali 2020

Wishing everyone a Happy Deepavali.

Let the light of knowledge remove the darkness of ignorance.

Let the warmth of lamp bring more energy to your homes.

Let the beauty of the oil lamp fill the eyes of the beholder.

Let everlasting joy and happiness become a part of your life from this Deepavali.

Let the supreme light which governs everything enlighten the light of every individual soul.

Month: November 2020