SQL Interview Questions and answers Part 13 | SQL Scenario based Interview Question and Answer

SQL Interview Questions and answers Part 13 | SQL Scenario based Interview Question and Answer
Input :- SalesTable has four columns namely ID, Product , SalesYear and QuantitySold

Problem Statements :- Write SQL to get the total Sales in year 1998,1999 and 2000 for all the products as shown below.

#SQLInterviewQuestionsandanswers #sqlInterviewQuestions #sqlInterviewQuestionsForTesting #sqlInterviewQuestionsQuery #sqlInterviewQuestionsOnJoins #sqlTechnicalInterviewQuestions #SQLforETLTesters #CommonSqlInterviewQuestions #SQLRealTimeQuestions

[susmitapanigrahi]

Here is my solution for this question by using case statement :

select 'TotalSales' as TotalSales,
sum(case when SalesYear=1998 then QuantitySold else 0 end ) as "1998",
sum(case when SalesYear=1999 then QuantitySold else 0 end ) as "1999",
sum(case when SalesYear=2000 then QuantitySold else 0 end) as "2000"
from Sales_Table;

[NiteshGupta-jrib]

select 'TotalSales' as TotalSales,
sum(case when SalesYear=1998 then QuantitySold end) as "1998",
sum(case when SalesYear=1999 then QuantitySold end) as "1999",
sum(case when SalesYear=2000 then QuantitySold end) as "2000"
from Sales

[shivakrishna]

Thanks man! Because of your videos I cleared my SQL assessment test of a top company!!

[shakiraafshan]

Query fo creating table for this question

create table salestbl (
id int identity (1, 1),
product varchar(20),
salesyear int,
QuantitySold int)

insert into salestbl (product, salesyear, QuantitySold)
values ('Laptop', 1998, 2500), ('Laptop', 1999, 3600),
('Laptop', 2000, 4200),
('Keyboard', 1998, 2300), ('Keyboard', 1999, 3600), ('Keyboard', 2000, 5000),
('Mouse', 1998, 6000), ('Mouse', 1999, 3400), ('Mouse', 2000, 4600)

[manishkamble]

very helpful tutorial, but please add create table and insert statement in description it help's a lot while doing practice

[sourabhshrivas]

with subquery solution -

select 'total_sales' as total_sales, t1.sales as '1998',
t2.sales as '1999',
t3.sales as '2000'
from
(select sum(qty_sold) as sales from sales1 where sales_year = '1998') t1,
(select sum(qty_sold) as sales from sales1 where sales_year = '1999')t2,
(select sum(qty_sold) as sales from sales1 where sales_year = '2000')t3

[prajjwaljaiswal]

Table Script MySQL:

create table SalesTable (
id int NOT NULL AUTO_INCREMENT,
product varchar(20),
salesyear int,
QuantitySold int,
primary key (ID)
);

insert into SalesTable (product, salesyear, QuantitySold)
values
('Laptop', 1998, 2500),
('Laptop', 1999, 3600),
('Laptop', 2000, 4200),
('Keyboard', 1998, 2300),
('Keyboard', 1999, 4800),
('Keyboard', 2000, 5000),
('Mouse', 1998, 6000),
('Mouse', 1999, 3400),
('Mouse', 2000, 4600);
commit;

select * from SalesTable;

[soumeshkayast]

SELECT 'TOTAL_SALES' AS TOTAL_SALES,
SUM(CASE WHEN SALES_YEAR='1998' THEN QUANTITY_SOLD END) AS '1998',
SUM(CASE WHEN SALES_YEAR='1999' THEN QUANTITY_SOLD END) AS '1999',
SUM(CASE WHEN SALES_YEAR='2000' THEN QUANTITY_SOLD END) AS '2000'
FROM SCENARIO13;

[shrutis]

Solution for Oracle SQL :

select 'TotalSales' TotalSales, t.*
from
(
select Salesyear, QuantitySold from Sales
)
pivot
(
SUM(QuantitySold)
for Salesyear in (1998, 1999, 2000)
)t

[mominmaqbul]

@3.47. You have written all unique years in the code as the data set is small. what if we are dealing with huge dataset and entire years we cant write in the code right. any way?

[imranpathan]

hi ..please provide each SQL Interview Questions --Table and Records

[shikhayadav]

Hi, will this work:
select 'Totalsales' as totalsales, case when salesyear=1998 then sumsales end as 1998, case when salesyear=1999 then sumsales end as 1999,
case when salesyear=2000 then sumsales end as 2000 from
(
select salesyear, sum(quanitysold) as sumsales from sales_table
group by 1)

[dishantpandya]

Using Window Function


Select Distinct (SalesYear), Yearsale from
(
Select *,
Sum(QuantitySold) over (Partition by SalesYear ) as Yearsale
from Sales)A

[gagansingh]

Select 'Totalsales' as Totalsales, [1998], [1999], [2000] from
(Select Salesyear, QuantitysolD
from salestbl) as sourcetable
Pivot
(Sum(quantitysold) for Salesyear In ([1998], [1999], [2000])) as Sales1 Just 1999 one sales figure different so 1999 you will get 10600