Basic SELECT Queries
SELECT * FROM parks_and_recreation.employee_demographics;
select first_name,
last_name,
birth_Date,
age,
age+100
from employee_demographics;
select distinct gender
from employee_demographics;
select distinct first_name,gender
from employee_demographics;
WHERE Clause
select *
from employee_salary
where dept_id=1;
select *
from employee_salary
where salary<=50000;
select *
from employee_demographics
where gender!='Male';
select *
from employee_demographics
where birth_date>'1985-01-01';
select *
from employee_demographics
where birth_date >'1985-01-01'
and gender='Male';
select *
from employee_demographics
where birth_date>'1985-01-01'
and not gender='Male';
select *
from employee_demographics
where (birth_date>'1979-01-01' and age=44) or age>60;
LIKE Statement
select *
from employee_demographics
where first_name LIKE 'A%';
select *
from employee_demographics
where first_name LIKE '%n__';
select *
from employee_demographics
where birth_date LIKE '198%';
GROUP BY Statement
select gender
from employee_demographics
group by gender;
select gender,avg(age)
from employee_demographics
group by gender;
select occupation,salary
from employee_salary
group by occupation,salary;
select gender,avg(age),max(age),min(age),count(age)
from employee_demographics
group by gender;
ORDER BY Statement
select *
from employee_demographics
order by first_name;
select *
from employee_demographics
order by first_name desc;
select *
from employee_demographics
order by gender desc,age;
HAVING vs WHERE
select gender, avg(age)
from employee_demographics
group by gender
having avg(age)>40;
select occupation,avg(salary)
from employee_salary
where occupation like '%manager%'
group by occupation
having avg(salary)>75000;
LIMIT & Aliasing
select *
from employee_demographics
order by age desc
limit 3;
select (select distinct salary
from Employee
order by salary Desc
LIMIT 1 OFFSET 1) as SecondHighestSalary;
select gender,avg(age) as new_age
from employee_demographics
group by gender
having new_age>40;
Joins
-- INNER JOIN
select ed.employee_id,ed.first_name,birth_date,age,gender,occupation,salary,dept_id
from employee_demographics as ed
Inner join employee_salary as es
on ed.employee_id=es.employee_id;
-- LEFT JOIN
select *
from employee_demographics as ed
LEFT OUTER join employee_salary as es
on ed.employee_id=es.employee_id;
-- RIGHT JOIN
select *
from employee_demographics as ed
RIGHT OUTER join employee_salary as es
on ed.employee_id=es.employee_id;
Self Join
select *
from employee_salary as emp1
join employee_salary as emp2
on emp1.employee_id=emp2.employee_id;
select emp1.employee_id,emp1.first_name,emp2.employee_id,emp2.first_name
from employee_salary as emp1
join employee_salary as emp2
on emp1.employee_id+1=emp2.employee_id;
Multiple Joins
select
ed.employee_id,ed.first_name,ed.last_name,ed.age,birth_date,occupation,salary,es.dept_id,de
partment_name
from employee_demographics as ed
inner join employee_salary as es
ON ed.employee_id=es.employee_id
inner join parks_departments as pd
on es.dept_id=pd.department_id;
Union
select age,gender
from employee_demographics
union
select first_name,last_name
from employee_salary;
select first_name,last_name
from employee_demographics
union
select first_name,last_name
from employee_salary;
select first_name,last_name
from employee_demographics
union all
select first_name,last_name
from employee_salary;
select employee_id,last_name,'old man' as label
from employee_demographics
where age>40 and gender='Male'
union
select employee_id,last_name,'old lady' as label
from employee_demographics
where age>40 and gender='female'
union
select employee_id,last_name,'high salaried' as label
from employee_salary
where salary>70000
order by employee_id;
String Functions
select length('skyfall');
select first_name,length(first_name) as length_of_name
from employee_demographics
order by length_of_name;
select upper('shivashlok');
select lower('ShiVASHlok');
select trim(' shiva ');
select ltrim(' shiva ');
select rtrim(' shiv ');
select first_name, left(first_name,3),
right(first_name,3),
substring(first_name,3,2),
birth_date,substring(birth_date,6,2) as month
from employee_demographics;
select first_name,replace(first_name,'a','@')
from employee_demographics;
select locate('x','Alex');
select first_name,
last_name,
concat(first_name,' ',last_name) as fullName
from employee_demographics;
CASE Statement
select first_name,age,
case
when age<=30 then 'YOUNG'
when age between 31 and 50 then 'old'
when age>50 then 'almost dead'
end as status
from employee_demographics;
Case Study
select employee_id,first_name,last_name,salary,
case
when salary<=50000 then salary+(0.05*salary)
when salary>50000 then salary+(0.07*salary)
end as newsalary,
case
when department_name like 'Finance' then 0.10*salary
end as bonus
from employee_salary
join parks_departments
on employee_salary.dept_id=parks_departments.department_id ;
Subqueries
select *
from employee_demographics
where employee_id in (
select employee_id
from employee_salary
where dept_id in (
select department_id
from parks_departments
where department_name like 'parks and recreation'
)
);
select employee_id,first_name,last_name,salary,
(select avg(salary) from employee_salary) as average_sal
from employee_salary;
select avg(avgage)
from (
select gender, avg(age) as avgage,max(age) as maxage,min(age) as minage,count(age)
as countage
from employee_demographics
group by gender
) as agg_table;
Window Functions
select ed.first_name,ed.last_name,AVG(salary) over (partition by gender)
from employee_demographics as ed
join employee_salary as es
on ed.employee_id=es.employee_id;
select ed.first_name,ed.last_name,
gender,salary,
sum(salary) over(partition by gender order by ed.employee_id) as rolling_sum
from employee_demographics as ed
join employee_salary as es
on ed.employee_id=es.employee_id;
select ed.employee_id,ed.first_name,ed.last_name,
gender,salary,
row_number() over(partition by gender order by salary desc) as rno,
rank() over(partition by gender order by salary desc) as rankk,
dense_rank() over(partition by gender order by salary desc) as dense_rankk
from employee_demographics as ed
join employee_salary as es
on ed.employee_id=es.employee_id;
CTEs
with CTE_Example as (
select ed.gender,avg(salary) as avg_sal,max(salary) as max_Sal
from employee_demographics as ed
join employee_salary as es
on ed.employee_id=es.employee_id
group by gender
)
select avg(avg_sal) as avg_Sal_M_F
from CTE_Example;
with cte_ex1 as
(
select employee_id,gender,birth_date
from employee_demographics
where birth_date>'1985-01-01'
),
cte_ex2 as
(
select employee_id,salary
from employee_salary
where salary>50000
)
select employee_id,gender,birth_date,salary
from cte_ex1
join cte_ex2
on cte_ex1.employee;
Temporary Tables
create temporary table temp_table(
first_name varchar(50),
last_name varchar(50),
favourite_movie varchar(100)
);
insert into temp_table
values('shiv','shlok','shutterIsland');
create temporary table sal_over_50k
select *
from employee_salary
where salary>50000;
select *
from sal_over_50k;
Stored Procedures
create procedure large_sal()
select *
from employee_salary
where salary>50000;
call large_sal();
DELIMITER $$
create procedure sal_2()
begin
SELECT *
FROM employee_salary
where salary>50000;
select *
from employee_salary
where salary>10000;
end $$
DELIMITER ;
call sal_2();
DELIMITER $$
create procedure sal_3(huggymuff INT)
begin
SELECT salary
FROM employee_salary
where employee_id=huggymuff;
end $$
DELIMITER ;
call sal_3(1);
Triggers and Events
DELIMITER $$
create trigger employee_insert
after INSERT on employee_salary
for each row
begin
insert into employee_demographics(employee_id,first_name,last_name)
values(NEW.employee_id,new.first_name,NEW.last_name);
end $$
DELIMITER ;
insert into employee_salary
values(13,'shlok','shiv','PR',100000,null);
DELIMITER $$
create event delete_retirees
on schedule every 30 second
do
Begin
delete
from employee_demographics
where age>=60;
end $$
DELIMITER ;
show variables like 'event%';
Interview Question
1. What are the main types of SQL commands?
DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE.
DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE.
DCL (Data Control Language): GRANT, REVOKE.
TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT.
2. What is a primary key?
A primary key is a unique identifier for each record in a table. It ensures that
no two rows have the same value in the primary key column(s), and it does not
allow NULL values.
3. What is a foreign key?
A foreign key is a column in one table that refers to the primary key in another
table. It establishes and enforces a relationship between the two tables,
ensuring data integrity.
4. What is a view in SQL?
A view is a virtual table created by a SELECT query. It does not store data itself,
but presents data from one or more tables in a structured way. Views simplify
complex queries, improve readability, and enhance security by restricting
access to specific rows or columns.
5. What are the different types of joins in SQL?
INNER JOIN: Returns rows that have matching values in both tables.
LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table, and
matching rows from the right table.
RIGHT JOIN (RIGHT OUTER JOIN): Returns all rows from the right table, and
matching rows from the left table.
FULL JOIN (FULL OUTER JOIN): Returns all rows when there is a match in
either table.
CROSS JOIN: Produces the Cartesian product of two tables.
