Q: What is Normalization.
Ans. Normalization is a database design technique to remove
redundant data.
Example :Dept table : Id First name Dept
Q2. How to implement normalization ?
Ans: Normalization is implemented by splitting tables in to
two, one with reference data
(Master table) and other transaction data.
Q3: What is denormalization ?
Ans. Denormalization is database design technique to improve
search performance .We merge table .
Duplicate data is present in denormalization,
Q. Explain OLTP VS OLAP ?
System have high load database server so we have to create two database OLTP & OLAP
OLTP : Online Transaction Processing : is create for insert update delete
OLAP : Online Analytical Processing : If you want to fetch , read select data fast so we have to go with OLAP
Q :What is difference between primary key and unique key .
with recursive empctc
as
(
select empid,empname,manid from org where empid=7
union all
select org.empid,org.empname,org.manid from empctc inner join org
on empctc.manid=org.empid
)
select e1.empname as employee,case when e2.empname is null then 'No Boss' else e2.empname end as Manager from empctc e1 left join empctc e2 on e1.manid=e2.empid
Q : Compare two tables
CREATE TABLE foo
( ID INT PRIMARY KEY,
NAME VARCHAR (50)
);
INSERT INTO foo (ID, NAME) VALUES(1, 'a'),(2, 'b');
CREATE TABLE bar
(ID INT PRIMARY KEY,
NAME VARCHAR (50)
);
INSERT INTO bar (ID, NAME) VALUES(1, 'a'),(2, 'b');
SELECT fruit,count(*) FROM basket
group by fruit
having count(*) >1 ;
delete from basket where id in (
select id from (
select id, fruit , dense_rank() over(partition by fruit order by id) as rnk from basket
) a
where rnk > 1 ;
)
Q : Find the Max value from row
table1
c1 c2 c3
1 2 3
4 5 6
7 8 9
select case when a1 > a2 and a1 > a3 then a1
when a2 > a1 and a2 > a3 then a2 else a3 end
from (
select max(10) a1 ,max(28) a2,max(19) a3
) a
Q1.Write a query to find the highest salary in each department .
With rank_data as
(
select *,dense_rank() over (order by salary desc ) salary_rank from EmployeePosition
)
select * from rank_data
Q2: Find the two Max salary from each department .
select distinct empposition,salary from EmployeePosition a
where a.salary in ( SELECT distinct salary from EmployeePosition b
where a.empposition=b.empposition order by salary desc limit 2 )
Q3 : Find the two Min salary from each department ?
select * from EmployeePosition a
where 2 >=(select count(distinct salary) from EmployeePosition b where b.salary >= a.salary);
Q4 :Find out two max salary
select * from EmployeePosition a
where 2 >=(select count(distinct salary) from EmployeePosition b
where b.salary >= a.salary);
Q5: How to convert column string into one row ?
select STRING_AGG (empposition, ',') from EmployeePosition ;
Q6: Write a query to calculate the even and odd records from a table.
select * from EmployeePosition where mod(emp_id,2)=0 ;
select * from EmployeePosition where mod(emp_id,2)=1 ;
Q:7 Find Domin name from email address .
select substring('tarunraj@gmail.com',position('@' in 'tarunraj@gmail.com')+1,
length('tarunraj@gmail.com') - position('@' in 'tarunraj@gmail.com')) as Domin;
Q8: How to add five start appending to domin name like sa*****@abc.com
select substring('sara@abc.com',1,2) || REPEAT('*',5) || substring('sara@abc.com',position('@' in'sara@abc.com'), length('sara@abc.com')-position('@' in'sara@abc.com'))
OUTPUT : sa*****@abc.com
Q9:Write a function it is return domin name
create or replace function patten_matching(val varchar)
returns varchar
language plpgsql
as $$
declare data_string varchar;
begin
select string_val into data_string from (
select substring(val,position('@' in val)+1,
length(val)-position('@' in val)) as string_val )
a;
return data_string;
end; $$
select patten_matching('tarun_rajput13@hotmail.com')
output : hotmail.com
Q10:crate function write patten Matching domin name .
create or replace function fn_length_domin (domin varchar,email varchar)
returns varchar
language plpgsql as $$
declare val1 varchar ;
declare val2 varchar ;
begin
select length(string_val) into val1 from (
select substring(email,position('@' in email)+1,
length(email)-position('@' in email)) as string_val
) a where a.string_val like domin ;
return val1 ;
end ; $$
## select fn_length_domin('%hotmail.com','tarun@hotmail.com')
Q11: Write function return total length email
create or replace function patten_matching1(val varchar)
returns int
language plpgsql
as $$
declare data_string int;
declare email_val varchar;
begin
select length(a.string_val) into data_string from (
select substring(val,position('@' in val)+1,
length(val)-position('@' in val)) as string_val ) a
;
return data_string;
end; $$
## select patten_matching1('tarun_rajput13@hotmail.com')
Q12 :Write a query to replace .com to .net
select replace('tarunraj13@hotmail.com','.com','.net')
tarunraj13@hotmail.net
Q13:- STUFF FUNCATION is user sql server and overlay used in postgres
select overlay('tarun@hotmai.com' placing '*****' from 2 for 3) ;
SELECT STUFF('tarun@hotmai.com', 2, 3, '*****');
select overlay('tarunraj@gmail.com' placing '*****' from
length(left('tarunraj@gmail.com',2))
for position('@' in 'tarunraj@gmail.com') -2)
OUTPUT: t*****n@hotmail.com
Q14 : Write function for check date is valid or not & exception handling in Postgres .
CREATE OR REPLACE FUNCTION IS_DATE(val varchar ) returns boolean as $$
begin
perform val::DATE ;
return true ;
exception when others then
return false;
end;
$$
language plpgsql;
Q15 : FIND DAY YEAR MONTH TIME DATE
POSTGRES :
- select extract(DAY from CURRENT_DATE);
- select extract(MONTH from '2021-01-01 01:01:01'::DATE);
- select extract(YEAR FROM CURRENT_DATE)
- select localtime(0) : - 17:40:40
- select now()::DATE
- select current_timestamp ;
SQL SERVER
- select DATENAME(DAY ,'2021-01-01 20:50:50') Return 01
- select DATENAME(WEEKDAY ,'2021-01-01 20:50:50') Return Sunday
- select DATENAME(MONTH ,'2021-09-01 20:50:50') Return SEPTEMBER
- select YEAR(GETDATE()) - Return 2021
- select month(GETDATE()) - Return 1
Q16 :- FIND OUT WEEKDAY NAME
SELECT TO_CHAR(CURRENT_TIMESTAMP, 'Day')
OUTPUT : SANDAY
Q17 : Find out Month Name & extract datetime from
- select TO_CHAR(CURRENT_DATE,'Month')
- select extract(hour from timestamp '2021-01-01 23:50:50')
Q18 : Write simple code print 10
DO $$
DECLARE myvar integer := 10;
BEGIN
RAISE NOTICE ' %', myvar;
END $$;
Q19 : Find DAY MONTH YEAR
DO $$
DECLARE myvar Date := '2021-01-01';
BEGIN
RAISE NOTICE 'YEAR %', extract(year from myvar) ;
RAISE NOTICE 'MONTH %', extract(month from myvar) ;
RAISE NOTICE 'DAY %', extract(month from myvar) ;
RAISE NOTICE 'NAME OF MONTH %', TO_CHAR(myvar ,'Month');
RAISE NOTICE 'NAME OF DAY %', TO_CHAR(myvar ,'Day') ;
END $$;
Q20: DATEADD in current date
SQL SERVER
- select DATEADD(DAY ,20 ,'2021-01-01);
- select DATEPART(weekday,'2021-01-01 23:40:40') -return 5
- select datename(weekday,2021-01-01 23:50:50') -return Thursday
- select DATEADD(DAY,10,'2021-01-01 23:50:50') -return 2021-01-11
- select DATEADD(DAY,- 10,'2021-01-01 23:50:50')-return
- select DATEDIFF(DAY,'2021-10-10','2021-11-01') -- return 12
POSTGRES
- select CURRENT_DATE + interval '1 DAY'
- select current_date + '1 HOUR'::INTERVAL
Q20 DATEDIFF IN & CALCULATE AGE
SQL SERVER
- SELECT DATEDIFF(year, '2011-10-02', '2012-01-01');
POSTGRES
- select age('2021-10-16','1989-10-13') output: 32 year 3 day
- select DATE_PART('year', '2021-10-01'::DATE) - DATE_PART('year', '2010-10-01'::DATE)
- select extract(year from '2021-01-01'::DATE) - extract(YEAR from '2000-01-01'::DATE)
Q21 date_trunc it is truncate left data from date not required
- select date_trunc('month','2021-09-10'::DATE)
- 2021-09-01 00:00:00+05:30
- SELECT DATE_TRUNC('hour', TIMESTAMP '2017-03-17 02:09:30');
- OUTPUT : - 2017-03-17 02:00:00
Q22 Find no of days in month & get data go to first date of month
- select extract(days from date_trunc('month','2021-08-09'::DATE) + interval '1 month - 1 day')
- select date_part('days', date_trunc('month','2021-01-01'::DATE) + interval '1 month - 1 day' );
31 Day
- select extract(days from date_trunc('month','2021-08-09'::DATE) + interval '1 month - 1 day') -
- extract(days from date_trunc('month','2021-08-09'::DATE) + interval '1 month - 2 day')
Q23 write the function return more than column & display value
create or replace function fn_return_value(emp_id int)
returns RECORD
language plpgsql
as
$$
declare
ret RECORD;
begin
select id,name,dept,salary from emp_details into ret where id = 4;
return ret;
end; $$
select ID ,NAME,dept,salary From fn_return_value(3) as (ID int,NAME varchar,dept varchar,salary int)
11-07-2018 10000 28-08-2018 10000 03-11-2018 10000
Q24 :Write the function return query and print date
returns table(id int,datadate timestamp)
language plpgsql
as $$
declare total_days int:=(select e_date::DATE - s_date::DATE);
day_of_date date:=s_date; r1 date;
begin
drop table if exists datadate;
create table datadate(id serial,datadate timestamp);
for total_days in 0 ..total_days loop
r1:=day_of_date + interval '1 day' * total_days;
insert into datadate(datadate) values (r1);
end loop;
return query select * from datadate ; end $$
select fn_datadate('2021-01-01','2021-01-15');
Exercise using SQL left join
CREATE TABLE IF NOT EXISTS `customers` ( `c_id` int(1) DEFAULT NULL, `customer` varchar(4) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -- Dumping data for table `customers` -- INSERT INTO `customers` (`c_id`, `customer`) VALUES (1, 'Rabi'), (2, 'Raju'), (3, 'Alex'), (4, 'Rani'), (5, 'King'), (7, 'Ronn'), (8, 'Jem'), (9, 'Tom'); -- -------------------------------------------------------- -- -- Table structure for table `products` -- CREATE TABLE IF NOT EXISTS `products` ( `p_id` int(1) DEFAULT NULL, `product` varchar(12) DEFAULT NULL, `price` int(2) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -- Dumping data for table `products` -- INSERT INTO `products` (`p_id`, `product`, `price`) VALUES (1, 'Hard Disk', 80), (2, 'RAM', 90), (3, 'Monitor', 75), (4, 'CPU', 55), (5, 'Keyboard', 20), (6, 'Mouse', 10), (7, 'Motherboard', 50), (8, 'Power supply', 20); -- -------------------------------------------------------- -- -- Table structure for table `sales` -- CREATE TABLE IF NOT EXISTS `sales` ( `sale_id` int(1) DEFAULT NULL, `c_id` int(1) DEFAULT NULL, `p_id` int(1) DEFAULT NULL, `product` varchar(7) DEFAULT NULL, `qty` int(1) DEFAULT NULL, `store` varchar(3) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -- Dumping data for table `sales` -- INSERT INTO `sales` (`sale_id`, `c_id`, `p_id`, `product`, `qty`, `store`) VALUES (1, 2, 3, 'Monitor', 2, 'ABC'), (2, 2, 4, 'CPU', 1, 'DEF'), (3, 1, 3, 'Monitor', 3, 'ABC'), (4, 4, 2, 'RAM', 2, 'DEF'), (5, 2, 3, 'Monitor', 3, 'ABC'), (6, 3, 3, 'Monitor', 2, 'DEF'), (7, 2, 2, 'RAM', 3, 'ABC'), (8, 3, 2, 'RAM', 2, 'DEF'), (9, 2, 3, 'Monitor', 2, 'ABC');
select * from customers;
- List of products sold
- List of quantity sold against each product.
- List of quantity and total sales against each product
- List of quantity sold against each product and against each store.
- List of quantity sold against each Store with total turnover of the store.
- List of products which are not sold
- List of customers who have not purchased any product.
select * from products;
select * from sales;
- select c.customer,nullif ('Not purchas',s.product) from customers c left join sales s
- on c.c_id=s.c_id
- where s.product is null
left join sales b
on a.p_id=b.p_id
where b.product is null
- select a.store,sum(a.qty) Total_qty,sum(b.price * a.qty) turnover
- from sales a inner join products b
- on a.p_id=b.p_id
- group by a.store
group by store,product
- select product,p_id,sum(qty) from sales
- group by product,p_id ;
- select a.p_id,a.product,sum(a.qty),sum(a.qty*b.price) as Total_sales
- from sales a inner join products b
- on a.p_id=b.p_id
- group by a.p_id,a.product
No comments:
Post a Comment