Q1.
Remove Duplicates where duplicates is only in few column and you have delete only dulpicayes
row not all the rows.
S1. Delete using unique identifiers:-
Delete from cars
Where id in (
Select max(id)
From cars
group by model, brand
having count(*)>1)
S2. Using Self Join
Delete from cars
Where id in (
Select [Link]
From cars c1
Join cars c2 on [Link] = [Link] and [Link] = [Link]
Where [Link]<[Link]);
S3. Using Window Function (Best Method and can be used in case of whole row duplicated)
Delete from cars
Where id in (
Select id from ( Select *,
Row_number() over(partition by model, brand) as rn
From cars) x
Where [Link]>1);
S4. Using MIN aggregate function.
Delete from cars
Where id not in (
Select min(id)
From cars
Group by model, brand);
S5. By Using a Backup Table (very bad method as we cannot drop a table in productions environment
as apps as running 24*7)
Create table cars_backup
As
Select * from cars where 1=2;
Insert into cars_backup
Select *
From cars
Where id in (
Select min(id)
From cars
Group by model, brand);
Drop table cars;
Alter table cars_backup rename to cars;
[Link] backup table without dropping the original table. (not good either)
Create table cars_backup
As
Select * from cars where 1=2;
Insert into cars_backup
Select *
From cars
Where id in (
Select min(id)
From cars
Group by model, brand);
Truncate table cars;
Insert into cars
Sselect * from cars_backup;
DROP TABLE cars_backup;
Q2. When Whole column is duplicated then we have to remove all duplicated column.
S1. Using CTID (in postgre sql) or rowid(in oracle). These are the internally generated by them. So, we
can use them to delete duplicate rows as these are different for all the rows.
Delete from cars
WHERE CTID IN (
SELECT MAX(CTID)
FROM cars
GROUP BY model, brand
HAVING COUNT(*)>1);
S2. BY Using Temp UNIQUE ID Column
ALTER TABLE cars ADD COLUMN row_num INT AUTO_INCREMENT PRIMARY KEY;
Delete from cars
WHERE row_num IN (
SELECT MAX(row_num)
FROM cars
GROUP BY model, brand
HAVING COUNT(*)>1);
ALTER TABLE cars DROP COLUMN row_num;
S3. By Creating a Backup Table.
S4. By Crateing a Backup Table but not dropping the original
Question 3:
Solution:-
Select *,
(Cumulative_distance – LAG(Cumulative_distance, 1, 0) OVER (PARTITION BY cars ORDER BY
days)) as dist_travelled_per_day
From cars_travels
Question 4 :-
with cte as(
SELECT CONCAT(ID, ‘ ‘, Name) as name,
NTILE(4) OVER(ORDER BY ID) AS Buckets
From emp_inputs)
SELECT STRING_AGG(name, ‘ , ‘) AS final_result
From cte
GROUP BY Buckets
Question 5 :-
Select *,
o CASE WHEN p_id IS NULL THEN ‘Root’
o WHEN p_id IS NOT NULL AND id IN (SELECT DISTINCT p_id FROM tree) THEN ‘Inner’
o ELSE ‘Leaf’
o END AS Type
FROM tree
Question 6:- Print meaningful comments. Write an SQL query to display
the correct message (meaningful message) from input
comments_and_translation table.
SELECT
o CASE WHEN translation IS NULL THEN comment
o ELSE translation
o END AS Output
FROM Table;
OR,
SELECT COALESCE(translation, comment) AS output FROM Table;
Question 7 :-
Question 8:- There are 10 IPL team. write an sql query such that each team
play with every other team just once.
Also write another query such that each team plays with every other team
twice.
Question 9:-
Question 10:- Write a query to fetch the record of brand whose amount is
increasing every year