---Write a query to find out the cities where covid cases are increasing continuously.  create table covid(city varchar(50),days date,cases int); delete from covid; insert into covid values('DELHI','2022-01-01',100); insert into covid values('DELHI','2022-01-02',200); insert into covid values('DELHI','2022-01-03',300); insert into covid values('MUMBAI','2022-01-01',100); insert into covid values('MUMBAI','2022-01-02',100); insert into covid values('MUMBAI','2022-01-03',300); insert into covid values('CHENNAI','2022-01-01',100); insert into covid values('CHENNAI','2022-01-02',200); insert into covid values('CHENNAI','2022-01-03',150); insert into covid values('BANGALORE','2022-01-01',100); insert into covid values('BANGALORE','2022-01-02',300); insert into covid values('BANGALORE','2022-01-03',200)...

    What is INDEXING in SQL? 📢   ✔️ Indexes are special lookup tables that the database search engine can use to speed up data retrieval. ✔️ An index in a database is very similar to an index in the back of a book. For example, if you want to reference all pages in a book that discusses a certain topic, you first refer to the index, which lists all the topics alphabetically and are then referred to one or more specific page numbers. ✔️ An index helps to speed up SELECT queries and WHERE clauses, but it slows down data input, with the UPDATE and the INSERT statements. 🔎 When should Indexes be created? - A column contains a wide range of values, and table has millions of rows. - A column does not contain a large number of null values. - One or more columns are frequently used together in a where clause or a join condition. 🔎 When should indexes be avoided? - The table is small. - The columns are not often used as a condition in the query. - The column is ...

  ETL vs ELT ⚡ ETL is a traditional process for data transformation into a data warehouse (Cloud based or On-prem). ELT is a modern process for any kind of structured and non-structured data transformation inside a cloud based data warehouse. ⚡ In ETL, data moves from source to stage, then into data warehouse. In ELT, data staging is not required. Data can directly move into data warehouse from source. ⚡ ETL only transforms and loads the Data which you think is necessary when creating DWH. Hence Data migration is slower. ELT loads all the Data immediately into DWH and users can decide later which Data to transform and analyze. Hence Data migration is faster. ⚡ ETL is not normally a solution for Data lakes. It transforms data for integration with structured and relational DWH. ELT offers a pipeline for Data lakes to ingest any kind of structured or unstructured data. Data could be transformed on need basis for analysis. ⚡ ETL is best suited for dealing with smaller datasets. ELT i...

Write a SQL Query TO FIND THE AVERAGE PRICE OF EACH PRODUCT  -------------------START-----------------------------------------------------------------------   CREATE TABLE Price ( product_id INT NOT NULL, start_date DATE, end_date DATE, price INT ) insert Price values (1,GETDATE()-90,GETDATE()-70,100) insert Price values (1,GETDATE()-300,GETDATE()-100,120) insert Price values (2,GETDATE()-400,GETDATE()-300,220) insert Price values (2,GETDATE()-600,GETDATE()-500,320) SELECT * FROM Price SELECT * FROM Unitsold CREATE TABLE Unitsold ( product_id INT NOT NULL, purchase_date DATE, units INT) insert  Unitsold values(1,GETDATE()-80,22) insert  Unitsold values(1,GETDATE()-200,30) insert  Unitsold values(2,GETDATE()-340,40) insert  Unitsold values(2,GETDATE()-540,50) SELECT * FROM Price SELECT * FROM Unitsold ---------------------------------------------------SOLUTION--------------------------------- with CTE_1 as( select product_id,units,units*price as product_pric...

 ---------- Write a SQL Query who all are present inside the hospital--- -------------------------------------- create table hospital ( emp_id int , action varchar(10) , time datetime); insert into hospital values ('1', 'in', '2019-12-22 09:00:00'); insert into hospital values ('1', 'out', '2019-12-22 09:15:00'); insert into hospital values ('2', 'in', '2019-12-22 09:00:00'); insert into hospital values ('2', 'out', '2019-12-22 09:15:00'); insert into hospital values ('2', 'in', '2019-12-22 09:30:00'); insert into hospital values ('3', 'out', '2019-12-22 09:00:00'); insert into hospital values ('3', 'in', '2019-12-22 09:15:00'); insert into hospital values ('3', 'out', '2019-12-22 09:30:00'); insert into hospital values ('3', 'in', '2019-12-22 09:45:00'); insert into hospit...

------------- --Pareto Principle (80-20 Rule) Implementation in SQL- ------------------------------- ---------------START--------------------------------------------------------------- with CTE1 as ( select SSO.Product_ID,SUM (SSO.Sales) as 'Product Sales' FROM  [dbo].[Sample - Superstore_Orders] SSO GROUP BY SSO.Product_ID ),Sales_cal as ( select Product_ID,Sales,sum (Sales) over (ORDER BY Sales DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING ) as 'Running Sales',SUM (Sales) OVER ()* 0.8 as"Total Sales" FROM  [dbo].[Sample - Superstore_Orders] ) select * from Sales_cal WHERE Sales_cal.[Running Sales] <=Sales_cal.[Total Sales] ------END----------------------------------------------------------

----- ----Write a query to find PersonID,Name,Number of friends, and sum of marks of person who have friends with total score greater then 100 ----------- -------------------START---------------------------------------------------------------------------- select * from [ Person_Table] SELECT * FROM [dbo].[Friend] WITH CTE1 AS ( SELECT A.PersonID, count (A.PersonID) as "No of friends", SUM(B.Score) as "Total Marks" FROM [dbo].[Friend] A INNER JOIN [ Person_Table] B ON A.FriendID = B.PersonID group by  A.PersonID Having SUM(B.Score) >100 ) SELECT P.*,CTE1.[No of friends],CTE1.[Total Marks] FROM [ Person_Table] P INNER JOIN CTE1 ON CTE1.PersonID=P.PersonID --------------END----------------------------------------------------------------------------------------------------

 /*Assume that you are given the table below containing information on viewership by device type (where the three types are laptop, tablet, and phone).  Define “mobile” as the sum of tablet and phone viewership numbers. Write a query to compare the viewership on laptops versus mobile devices. Output the total viewership for laptop and mobile devices in the format of "laptop_views" and "mobile_views". viewership Table:*/ --------Creating the table with sample data--------------- create table viewership ( user_id  INT NOT NULL , device_type Varchar(10) NOT NULL , view_time DATETIME ) select * from viewership  insert into viewership values (123, 'tablet','01/02/2022 00:00:00') insert into viewership values  (125,'tablet', '02/07/2022 00:00:00') insert into viewership values  (127,'phone', '05/07/2022 00:00:00') insert into viewership values  (128,'phone', '06/07/2022 00:00:00') insert into viewersh...

 ------------------------------script-------------- --- ------Find the room type that searched most no of times & output the room type with the no of searchs for it------ ---------------------------------------------START---------------------------------- create table airbnb_searches  ( user_id int, date_searched date, filter_room_types varchar(200) ); delete from airbnb_searches; insert into airbnb_searches values (1,'2022-01-01','entire home,private room') ,(2,'2022-01-02','entire home,shared room') ,(3,'2022-01-02','private room,shared room') ,(4,'2022-01-03','private room') -------TABLE CREATED WITH SAMPLE DATA----------------------------- select A.value as "Room Type",count(A.value) as "No of times Search" from ( select * from airbnb_searches Airnub CROSS APPLY string_split (Airnub.filter_room_types,',')   )A GROUP BY A.value ORDER BY count(A.value) DESC; --------------------END--------...

 ------------------------------------------SQL Interview Question -------------------------------- ---Write the SQL to return all the employees those salary are same in same department------------- ------------------------------------------------------------Sample data & Code--------------------------------------------------- CREATE TABLE [emp_salary] (     [emp_id] INTEGER  NOT NULL,     [name] NVARCHAR(20)  NOT NULL,     [salary] NVARCHAR(30),     [dept_id] INTEGER ); INSERT INTO emp_salary (emp_id, name, salary, dept_id) VALUES(101, 'sohan', '3000', '11'), (102, 'rohan', '4000', '12'), (103, 'mohan', '5000', '13'), (104, 'cat', '3000', '11'), (105, 'suresh', '4000', '12'), (109, 'mahesh', '7000', '12'), (108, 'kamal', '8000', '11'); -------------------------------Table got created----------------------------------...

D  ay-2: Question-2: Date-5th Nov 2022 New and repeat customers Count from a customer table. create table customer_orders ( order_id integer, customer_id integer, order_date date, order_amount integer ); select * from customer_orders insert into customer_orders values(1,100,cast('2022-01-01' as date),2000),(2,200,cast('2022-01-01' as date),2500),(3,300,cast('2022-01-01' as date),2100) ,(4,100,cast('2022-01-02' as date),2000),(5,400,cast('2022-01-02' as date),2200),(6,500,cast('2022-01-02' as date),2700) ,(7,100,cast('2022-01-03' as date),3000),(8,400,cast('2022-01-03' as date),1000),(9,600,cast('2022-01-03' as date),3000) select * from customer_orders Select a.order_date, Sum(Case when a.order_date = a.first_order_date then 1 else 0 end) as new_customer, Sum(Case when a.order_date != a.first_order_date then 1 else 0 end) as repeat_customer from( Select customer_id, order_date, min(order_date) over(partition by cust...

  Please find the SQL Question & Answer. Day-1: Date-5th Nov 2022 Question-1: Derive Points table for ICC tournament. select ICC1.Team_1 "Team Name" FROM  icc_world_cup ICC1 UNION  select ICC2.Team_2 FROM  icc_world_cup ICC2 INSERT INTO icc_world_cup values('Aus','India','India','NZ'); INSERT INTO icc_world_cup values('Aus','India','India','Aus'); INSERT INTO icc_world_cup values('Aus','India','India','SA'); INSERT INTO icc_world_cup values('Aus','India','India','SL'); ALTER TABLE icc_world_cup ADD DRAW varchar(100); select * from  icc_world_cup ICC1  select A.[Team Name],Count(1) as "No of Match Played",SUM (A.[Win])as "Winner",Count(1)-SUM (A.[Win]) as "Loss" from ( select ICC1.Team_1 as "Team Name" ,CASE WHEN ICC1.Team_1=ICC1.Winner THEN 1 else 0 END as "Win"  from icc_world_cup ICC1 union all ...