queries.sql

Solving a Namaste SQL Assignment Question | SQL For Analytics

queries.sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
create table namaste_orders
(
order_id int,
city varchar(10),
sales int
)
create table namaste_returns
(
order_id int,
return_reason varchar(20),
)
insert into namaste_orders
values(1, 'Mysore' , 100),(2, 'Mysore' , 200),(3, 'Bangalore' , 250),(4, 'Bangalore' , 150)
,(5, 'Mumbai' , 300),(6, 'Mumbai' , 500),(7, 'Mumbai' , 800)
;
insert into namaste_returns values
(3,'wrong item'),(6,'bad quality'),(7,'wrong item');
--SQL to find cities where not even single order has was returned
--Achieve this without using subquery and CTE
SELECT * FROM namaste_returns;
SELECT * FROM namaste_orders;
SELECT O.city , COUNT(R.order_id)
FROM namaste_orders O LEFT JOIN namaste_returns R ON O.order_id = R.order_id
GROUP BY O.city
HAVING COUNT(R.order_id)= 0
 
 
הההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההההה
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

STDIN

Output:

order_id    return_reason       
----------- --------------------
          3 wrong item          
          6 bad quality         
          7 wrong item          
order_id    city       sales      
----------- ---------- -----------
          1 Mysore             100
          2 Mysore             200
          3 Bangalore          250
          4 Bangalore          150
          5 Mumbai             300
          6 Mumbai             500
          7 Mumbai             800
city                  
---------- -----------
Mysore               0

created 1 year ago by shishir shetty

Solving a Namaste SQL Assignment Question | SQL For Analytics

OneCompiler.com

Languages

More