A Learner's Blog.....: SQL Query Interview Questions

Q) For every employee find the sum of ASCII codes of all the characters in their names. Write a single SELECT statement only.

EMPNO ENAME SUM_ASCII
----- ---------- ----------
7788 SCOTT 397
7876 ADAMS 358
7566 JONES 383
7499 ALLEN 364
7521 WARD 302
7934 MILLER 453
7902 FORD 299
7369 SMITH 389
7844 TURNER 480
7698 BLAKE 351
7782 CLARK 365
7654 MARTIN 459
7839 KING 297
7900 JAMES 368

ans1:
SELECT ename,
SUM(ascii_val)
FROM
(SELECT ename,ascii(SUBSTR (ename, rn, 1)) ascii_val
FROM emp,
(SELECT ROWNUM rn
FROM DUAL
CONNECT BY LEVEL <=
(SELECT MAX(LENGTH(ename)) FROM emp)
) )
WHERE ascii_val IS NOT NULL
GROUP BY ename;

ans2:-
select empno,ename, sum(regexp_substr(nm, '\d+', 1, occ)) as sum_ascii
from(
select empno, ename,
substr(dump(ename),instr(dump(ename),': ')+2) nm from emp),
(select level occ from dual connect by level < (SELECT MAX(LENGTH(ename)) FROM emp))
group by empno,ename order by 3;

I want to sort data of versions table by individual number separated by Dot of version column. below is the result I need:
1
1.1
1.1.1
1.1.2
1.2
1.2.4
1.2.5
1.2.10
1.10.1
1.10.2
2
2.1

create table versions
(
version varchar2(30)
);
insert into versions values ('1.1');
insert into versions values ('1.1.1');
insert into versions values ('1.2.10');
insert into versions values ('1');
insert into versions values ('1.10.1');
insert into versions values ('1.1.2');
insert into versions values ('2.1');
insert into versions values ('2');
insert into versions values ('1.10.2');
insert into versions values ('1.2');
insert into versions values ('1.2.4');
insert into versions values ('1.2.5');

commit;

SELECT version
FROM versions
ORDER BY TO_NUMBER (REGEXP_SUBSTR (version, '\d+')),
TO_NUMBER (REGEXP_SUBSTR (version,'\d+',1,2)) NULLS FIRST,
TO_NUMBER (REGEXP_SUBSTR (version,'\d+',1,3)) NULLS FIRST;

Q) Lets say we have a table as like below and
101 Ram,Pradeep,Ashok
102 Jagadeeh,Sai,Sravan
103 Lavanya,Naveetha,Aruna

we want the output like below:

101 ram
101 pradeep
101 Ashok
102 Jagadeeh
....

create table pattern (no number(3), name varchar2(100));

insert into pattern values (101,'Ram,Pradeep,Ashok');
insert into pattern values (102,'Jagadeeh,Sai,Sravan');
insert into pattern values (103,'Lavanya,Naveetha,Aruna');
commit;

SELECT distinct no,REGEXP_SUBSTR(name, '[^,]+', 1, LEVEL) AS data FROM pattern
CONNECT BY REGEXP_SUBSTR(name, '[^,]+', 1, LEVEL) IS NOT NULL;

with tb as (
select 101 as id,'Ram,Pradeep,Ashok' as nam from dual
union
select 102 ,'Jagadeeh,Sai,Sravan' from dual
union
select 103,'Lavanya,Naveetha,Aruna' from dual
)
select distinct * from (
select id,regexp_substr (nam,'([^,]+)',1,level) from tb connect by nocycle level <=regexp_count(nam,',',1)+1
) order by id;

with pattern1 as (
select 101 as id,'Ram,Pradeep,Ashok' as nam from dual
union
select 102 ,'Jagadeeh,Sai,Sravan' from dual
union
select 103,'Lavanya,Naveetha,Aruna' from dual
)SELECT id,trim(column_value) Name FROM pattern1,

xmltable(('"'||REPLACE(nam,',','","')||'"'));

Question 1:
Below are three tables: Client, Bank and Bill. The question is based on these three tables.

Creating tables:

CREATE TABLE Client
(
ClientID int,
ClientName varchar(255),
Primary Key (ClientID)
);

CREATE TABLE Bank
(
BranchID int,
BranchName varchar(255),
ClientID int,
Primary Key (BranchID),
FOREIGN KEY (ClientID) REFERENCES Client (ClientID)
);

CREATE TABLE Bill
(
InvoiceID int,
Year DateTime,
BranchID int,
Amount int,
Primary Key (InvoiceID),
FOREIGN KEY (BranchID) REFERENCES Bank(BranchID)
);

Inserting data into tables:

insert into Client values (1, 'O_A')
insert into Client values (2, 'O_B')
insert into Client values (3, 'O_C')
insert into Client values (4, 'O_D')
insert into Client values (5, 'O_E')

insert into Bank values (1, 'B_1', 1)
insert into Bank values (2, 'B_2', 2)
insert into Bank values (3, 'B_3', 3)
insert into Bank values (4, 'B_4', 4)
insert into Bank values (5, 'B_5', 5)

insert into Bill values (1, '2012-01-01 00:00:00.000',    1,    100)
insert into Bill values (2, '2013-01-01 00:00:00.000',    2,    200)
insert into Bill values (3, '2012-01-01 00:00:00.000',    3,    300)
insert into Bill values (4, '2017-01-01 00:00:00.000',    4,    400)
insert into Bill values (5, '2012-01-01 00:00:00.000',    5,    500)
insert into Bill values (6, '2012-01-01 00:00:00.000',    1,    900)
insert into Bill values (7, '2013-01-01 00:00:00.000',    1,    900)

Verifying data in the tables:

Select * from Client

ClientID	ClientName
1	O_A
2	O_B
3	O_C
4	O_D
5	O_E

Select * from Bank

BranchID	BranchName	ClientID
1	B_1	1
2	B_2	2
3	B_3	3
4	B_4	4
5	B_5	5

Select * from Bill

InvoiceID	Year	BranchID	Amount
1	2012-01-01 00:00:00.000	1	100
2	2012-01-01 00:00:00.000	2	200
3	2012-01-01 00:00:00.000	3	300
4	2012-01-01 00:00:00.000	4	400
5	2012-01-01 00:00:00.000	5	500
6	2012-01-01 00:00:00.000	1	900
7	2012-01-01 00:00:00.000	1	900

Question:
Retrieve all invoices from table bill for year 2012 and 2013 which belong to client ‘O_A’:

Select i.InvoiceID,i.YEAR, i.BranchID, i.Amount from Bill as i
join Bank as b on i.BranchID = b.BranchID
join Client as o on o.ClientID = b.ClientID
where i.Year >= '2012-01-01' and i.Year <= '2013-01-01'
and o.ClientName = 'O_A'

Result:

Example 2:

Below are three tables: salesman, Client and Items. The questions below are based on these three tables.

Creating tables:

CREATE TABLE salesman
(
salesman_id int,
Name varchar(255),
Age int,
Salary int,
Primary key (salesman_id)
);

CREATE TABLE Client
(
Cust_ID int,
Name varchar(255),
City varchar(255),
IndustryType char,
Primary key (Cust_ID)
);

CREATE TABLE Items
(
Number int,
Order_date date,
Cust_ID int,
salesman_id int,
Amount int,
Foreign Key (Cust_ID) references Client (Cust_ID),
Foreign Key (salesman_id) references salesman (salesman_id)
);

Inserting data into tables:

Insert into salesman values (1, 'Amir', 61, 140000)
Insert into salesman values (2, 'Balbir', 34, 44000)
Insert into salesman values (5, 'Chander', 34, 40000)
Insert into salesman values (7, 'Damdar', 41, 52000)
Insert into salesman values (8, 'Kumar', 57, 115000)
Insert into salesman values (11, 'Jaggu', 38, 38000)

Insert into Client values (4, 'Samsung','Delhi', 'J')
Insert into Client values (6, 'Panasonic','Orange', 'J')
Insert into Client values (7, 'Nokia','Jamshedpur', 'B')
Insert into Client values (9, 'Apple','Jamshedpur', 'B')

Insert into Items values (10, '8/2/97', 4, 2, 540)
Insert into Items values (20, '1/30/96', 4, 8, 1800)
Insert into Items values (30, '7/14/94', 9, 1, 460)
Insert into Items values (40, '1/29/95', 7, 2, 2400)
Insert into Items values (50, '2/3/95', 6, 7, 600)
Insert into Items values (60, '3/2/95', 6, 7, 720)
Insert into Items values (70, '5/6/95', 9, 7, 150)

Verifying data in the tables:

Select * from Items

Number	Order_date	Cust_ID	salesman_id	Amount
10	1997-08-02	4	2	540
20	1996-01-30	4	8	1800
30	1994-07-14	9	1	460
40	1995-01-29	7	2	2400
50	1995-02-03	6	7	600
60	1995-03-02	6	7	720
70	1995-05-06	9	7	150

Select * from Client

Cust_ID	Name	City	IndustryType
4	Samsung	Delhi	J
6	Panasonic	Orange	J
7	Nokia	Jamshedpur	B
9	Apple	Jamshedpur	B

Select * from Salesman

salesman_id	Name	Age	Salary
1	Amir	61	140000
2	Balbir	34	44000
5	Chander	34	40000
7	Damdar	41	52000
8	Kumar	57	115000
11	Jaggu	38	38000

Question 1:
Get the names of all salespersons that have an order with Samsung.

Select distinct (s.name) from salesman as s 
join Items as o on s.Salesman_id = o.Salesman_id 
join Client as c on c.Cust_ID = o.Cust_ID
where c.Name = 'Samsung'

Result:
name
Balbir
Kumar

Question 2:
Get the names of all salespersons that do not have any order with Samsung.

Select name from salesman where name not in  (
Select distinct (s.name) from salesman as s 
join Items as o on s.salesman_id = o.salesman_id 
join Client as c on c.Cust_ID = o.Cust_ID
where c.Name = 'Samsung')

Result:
name
Amir
Chander
Damdar
Jaggu

Question 3:
Get the names of salespersons that have 2 or more orders.

Select s.name from salesman as s join Items as o on s.salesman_id = o.salesman_id
group by s.name
having count (*) >= 2

Result:
name
Balbir
Damdar

Question 4:
Find the third highest salary:

select min (salary)
from (select distinct top 3 salary from salesman
order by Salary desc) as a

Result:
(No column name)
52000

Question 5:
Find the third lowest salary:

select max (salary)
from (select distinct top 3 salary from salesman
order by Salary asc) as a

Result:
(No column name)
44000

Example 3:

Below example has been taken from Stackoverflow:

CREATE TABLE A
(
A int,
);

CREATE TABLE B
(
B int,
);

Insert into A values (1)
Insert into A values (2)
Insert into A values (3)
Insert into A values (4)

Insert into B values (3)
Insert into B values (4)
Insert into B values (5)
Insert into B values (6)

Select * from A

A
1
2
3
4

Select * from B

B
3
4
5
6

Question 1:
What will be the query and result of inner join between tables A and B?

Select * from a 
INNER JOIN b 
on a.a = b.b;

A	B
3	3
4	4

Question 2:
What will be the query and result of full outer join between tables A and B?

Select * from a 
FULL OUTER JOIN b 
on a.a = b.b;

A	B
1	NULL
2	NULL
3	3
4	4
NULL	5
NULL	6

Question 3:
What will be the query and result of left outer join between tables A and B?

Select * from a 
LEFT OUTER JOIN b 
on a.a = b.b;

A	B
1	NULL
2	NULL
3	3
4	4

1.Write a SQL query to remove the logical duplicate

Source Destination Distance

bangalore chennai 500

bangalore hyd 800

chennai bangalore 500

delhi hyd 1000

hyd delhi 1000

with traveldist as(

select 'chennai' source,'bangalore' destination,500 distance from dual

union

select 'hyd' source,'delhi' destination,1000 distance from dual

union

select 'bangalore' source,'chennai' destination,500 distance from dual

union

select 'delhi' source,'hyd' destination,1000 distance from dual

union

select 'bangalore' source,'hyd' destination,800 distance from dual

)

select source, destination, distance from(

select source,destination,lag(source) over (order by distance) oldsrc,distance

from traveldist)

where nvl(destination,'@') <> nvl(oldsrc,'@');

with tab as(

select 'delhi' from_st,'mumbai' to_st,2400 distance from dual

union

select 'mumbai' from_st,'delhi' to_st,2400 distance from dual

union

select 'delhi' from_st,'bangalore' to_st,2000 distance from dual

union

select 'bangalore' from_st,'delhi' to_st,2000 distance from dual

union

select 'delhi' from_st,'chennai' to_st,2400 distance from dual

)SELECT from_st,to_st,distance

FROM

(SELECT from_st,to_st,distance,row_number() over (partition BY citymap order by citymap) rn

FROM

(SELECT from_st,

to_st,distance,

CASE

WHEN from_st < to_st THEN from_st||to_st ELSE to_st||from_st

END citymap

FROM tab

) )WHERE rn=1 ;

2.Write a SQL query to find the customer who has purchaged both the products "A" and "B"?

CUST_ID PROD

1 A

2 A

2 C

3 A

3 B

4 A

5 B

select * from product a

where exists(select * from product b where product_nm='A' and a.cust_id=b.cust_id)

and exists(select * from product b where product_nm='B' and a.cust_id=b.cust_id);

select a.cust_id,a.product_nm from product a,(select cust_id ,sum(case when product_nm = 'A' then 1 else 0 end),sum(case when product_nm = 'B' then 1 else 0 end)

from product where product_nm in('A','B')

group by cust_id

having sum(case when product_nm = 'A' then 1 else 0 end) > 0

and sum(case when product_nm = 'B' then 1 else 0 end) > 0)b where a.cust_id=b.cust_id;

To solve these interview questions on SQL queries you have to create the products, sales tables in your oracle database. The "Create Table", "Insert" statements are provided below.

CREATE TABLE PRODUCTS

(

PRODUCT_ID INTEGER,

PRODUCT_NAME VARCHAR2(30)

);

CREATE TABLE SALES

(

SALE_ID INTEGER,

PRODUCT_ID INTEGER,

YEAR INTEGER,

Quantity INTEGER,

PRICE INTEGER

);

INSERT INTO PRODUCTS VALUES ( 100, 'Nokia');

INSERT INTO PRODUCTS VALUES ( 200, 'IPhone');

INSERT INTO PRODUCTS VALUES ( 300, 'Samsung');

INSERT INTO PRODUCTS VALUES ( 400, 'LG');

INSERT INTO SALES VALUES ( 1, 100, 2010, 25, 5000);

INSERT INTO SALES VALUES ( 2, 100, 2011, 16, 5000);

INSERT INTO SALES VALUES ( 3, 100, 2012, 8, 5000);

INSERT INTO SALES VALUES ( 4, 200, 2010, 10, 9000);

INSERT INTO SALES VALUES ( 5, 200, 2011, 15, 9000);

INSERT INTO SALES VALUES ( 6, 200, 2012, 20, 9000);

INSERT INTO SALES VALUES ( 7, 300, 2010, 20, 7000);

INSERT INTO SALES VALUES ( 8, 300, 2011, 18, 7000);

INSERT INTO SALES VALUES ( 9, 300, 2012, 20, 7000);

COMMIT;

SELECT * FROM PRODUCTS;

PRODUCT_ID PRODUCT_NAME

-----------------------

100 Nokia

200 IPhone

300 Samsung

SELECT * FROM SALES;

SALE_ID PRODUCT_ID YEAR QUANTITY PRICE

--------------------------------------

1 100 2010 25 5000

2 100 2011 16 5000

3 100 2012 8 5000

4 200 2010 10 9000

5 200 2011 15 9000

6 200 2012 20 9000

7 300 2010 20 7000

8 300 2011 18 7000

9 300 2012 20 7000

1. Write a SQL query to find the products which have continuous increase in sales every year?

SELECT PRODUCT_NAME

FROM

(

SELECT P.PRODUCT_NAME,

S.QUANTITY -

LEAD(S.QUANTITY,1,0) OVER (

PARTITION BY P.PRODUCT_ID

ORDER BY S.YEAR DESC

) QUAN_DIFF

FROM PRODUCTS P,

SALES S

WHERE P.PRODUCT_ID = S.PRODUCT_ID

GROUP BY PRODUCT_NAME

HAVING MIN(QUAN_DIFF) >= 0;

2. Write a SQL query to find the products which does not have sales at all?

SELECT P.PRODUCT_NAME

FROM PRODUCTS P

LEFT OUTER JOIN

SALES S

ON (P.PRODUCT_ID = S.PRODUCT_ID);

WHERE S.QUANTITY IS NULL;

SELECT P.PRODUCT_NAME

FROM PRODUCTS P

WHERE P.PRODUCT_ID NOT IN

(SELECT DISTINCT PRODUCT_ID FROM SALES);

SELECT P.PRODUCT_NAME

FROM PRODUCTS P

WHERE NOT EXISTS

(SELECT 1 FROM SALES S WHERE S.PRODUCT_ID = P.PRODUCT_ID);

3. Write a SQL query to find the products whose sales decreased in 2012 compared to 2011?

SELECT P.PRODUCT_NAME

FROM PRODUCTS P,

SALES S_2012,

SALES S_2011

WHERE P.PRODUCT_ID = S_2012.PRODUCT_ID

AND S_2012.YEAR = 2012

AND S_2011.YEAR = 2011

AND S_2012.PRODUCT_ID = S_2011.PRODUCT_ID

AND S_2012.QUANTITY < S_2011.QUANTITY;

4. Write a query to select the top product sold in each year?

SELECT PRODUCT_NAME,

YEAR

FROM

(

SELECT P.PRODUCT_NAME,

S.YEAR,

RANK() OVER (

PARTITION BY S.YEAR

ORDER BY S.QUANTITY DESC

) RNK

FROM PRODUCTS P,

SALES S

WHERE P.PRODUCT_ID = S.PRODUCT_ID

) A

WHERE RNK = 1;

5. Write a query to find the total sales of each product.?

SELECT P.PRODUCT_NAME,

NVL( SUM( S.QUANTITY*S.PRICE ), 0) TOTAL_SALES

FROM PRODUCTS P

LEFT OUTER JOIN

SALES S

ON (P.PRODUCT_ID = S.PRODUCT_ID)

GROUP BY P.PRODUCT_NAME;

6. Write a query to find the products whose quantity sold in a year should be greater than the average quantity of the product sold across all the years?

SELECT P.PRODUCT_NAME,

S.YEAR,

S.QUANTITY

FROM PRODUCTS P,

SALES S

WHERE P.PRODUCT_ID = S.PRODUCT_ID

AND S.QUANTITY >

(SELECT AVG(QUANTITY)

FROM SALES S1

WHERE S1.PRODUCT_ID = S.PRODUCT_ID

);

7.Write a query to compare the products sales of "IPhone" and "Samsung" in each year? The output should look like as

YEAR IPHONE_QUANT SAM_QUANT IPHONE_PRICE SAM_PRICE

---------------------------------------------------

2010 10 20 9000 7000

2011 15 18 9000 7000

2012 20 20 9000 7000

SELECT S_I.YEAR,

S_I.QUANTITY IPHONE_QUANT,

S_S.QUANTITY SAM_QUANT,

S_I.PRICE IPHONE_PRICE,

S_S.PRICE SAM_PRICE

FROM PRODUCTS P_I,

SALES S_I,

PRODUCTS P_S,

SALES S_S

WHERE P_I.PRODUCT_ID = S_I.PRODUCT_ID

AND P_S.PRODUCT_ID = S_S.PRODUCT_ID

AND P_I.PRODUCT_NAME = 'IPhone'

AND P_S.PRODUCT_NAME = 'Samsung'

AND S_I.YEAR = S_S.YEAR;

8.How to Delete Duplicate Rows in Table

Table Name: Products

ProductId Price

---------------

1 10

2 20

3 30

i. Using Rowid

The following Delete statement deletes the rows using the Rowid.

Delete from <tablename>

where rowid not in (select max(rowid) from <tablename> group by <unique columns or primary key>);

Example:

Delete from products

where rowid not in (select max(rowid) from products group by productid);

ii. Using temp table and Distinct

Here, first create a temp table and insert distinct rows in the temp table. Then truncate the main table and insert records from the temp table.

Create temporary table products_temp As

Select Distinct ProductID, Price

From Products;

Truncate table Products;

Insert into products

Select *

From products_temp;

iii. Using temp table and Row Number analytic function.

The row_number analytic function is used to rank the rows. Here we use the row_number function to rank the rows for each group of productId and then select only record from the group.

Create temporary table products_temp As

Select productid, price

From

(

Select productid, price,

row_number() over (partition by productId order by price) group_rank

From products

)

Where group_rank = 1;

---------------------------------------------------------------------------

I have a table like below

COl1

-----

We need output like below

col1 col2 col3

---- ---- ----

1 4 7

2 5 8

3 6 9

with a as (

select a.*,floor((rownum-1)/3) fl,row_number() over(partition by floor((rownum-1)/3) order by col1) rn from(

select level col1 from dual connect by level<10)a

)

,b as(select col1,row_number() over(partition by floor((rownum-1)/3) order by col1) rn from a where fl=0)

,c as(select col1,row_number() over(partition by floor((rownum-1)/3) order by col1) rn from a where fl=1)

,d as(select col1,row_number() over(partition by floor((rownum-1)/3) order by col1) rn from a where fl=2)

select b.col1 col1,c.col1 col2,d.col1 col3

from b join c on b.rn=c.rn

join d on b.rn=d.rn;

WITH x AS (

SELECT ROW_NUMBER() OVER(PARTITION BY FLOOR((LEVEL-1)/3) ORDER BY LEVEL) as grid,

CASE WHEN MOD(FLOOR((LEVEL-1)/3),3)=0 THEN level END AS col1,

CASE WHEN MOD(FLOOR((LEVEL-1)/3),3)=1 THEN level END AS col2,

CASE WHEN MOD(FLOOR((LEVEL-1)/3),3)=2 THEN level END AS col3

FROM dual

CONNECT BY LEVEL<=9

)

SELECT MAX(col1) col1, MAX(col2) col2, MAX(col3) col3

FROM x

GROUP BY grid;

WITH X AS

(

SELECT &N as N

FROM dual

)

SELECT MOD(LEVEL,CEIL(N/3)),

MIN(CASE WHEN MOD(FLOOR((LEVEL-1)/CEIL(N/3)),3)=0 THEN level END) col1,

MIN(CASE WHEN MOD(FLOOR((LEVEL-1)/CEIL(N/3)),3)=1 THEN level END) col2,

MIN(CASE WHEN MOD(FLOOR((LEVEL-1)/CEIL(N/3)),3)=2 THEN level END) col3

FROM dual, x

CONNECT BY LEVEL<=N

GROUP BY MOD(LEVEL,CEIL(N/3))

ORDER BY CASE WHEN MOD(LEVEL,CEIL(N/3))=0 THEN CEIL(N/3) ELSE MOD(LEVEL,CEIL(N/3)) END;

select rownum,rownum+y,case when (rownum+(2*y)) -x > 0 then null else rownum+(2*y) end from

(select :a x,ceil(:a/3) y from dual) connect by level <=y;

------------------------------------------------------------------

Write a single SELECT statement that would list all 12 months and number of employees hired in each month. Year part should be ignored.

Expected Result:

Month Number of hires

------ ---------------

JAN 1

FEB 2

MAR 0

APR 2

MAY 2

JUN 1

JUL 0

AUG 0

SEP 2

OCT 0

NOV 1

DEC 3

select nvl(emp.Months,mn.Months),nvl(CNT,0) from

(select extract(Month from hiredate) id,to_char(hiredate,'MON') Months,count(*) cnt from emp

group by extract(Month from hiredate),to_char(hiredate,'MON') ) emp,

(SELECT to_char(to_date( level,'mm'), 'MON') Months,level id FROM DUAL CONNECT BY LEVEL <=12) mn

where mn.id=emp.id(+) order by mn.id;

Top 4 salary with out using subquery:-

------------------------------------

select e.ename, e.sal

from emp e,

emp e2

where e2.sal >= e.sal

group by e.ename, e.sal

having count(distinct e2.sal) <= 4

order by sal desc;

A Learner's Blog.....

Labels

11 Apr 2016

SQL Query Interview Questions

No comments:

Post a Comment