Create
following tables:
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
)A
GROUP
BY PRODUCT_NAME
HAVING
MIN(QUAN_DIFF) >= 0;
2.
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;
3.
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;
4.
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
);
5.
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
Using
self join:
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
Using
DECODE:
select
year,max(decode(product_name,'IPhone',quantity)) Iphone_QUAN,
max(decode(product_name,'Samsung',quantity))
SAM_QUAN,
max(decode(product_name,'IPhone',price))
Iphone_PRICE,
max(decode(product_name,'Samsung',price))
SAM_PRICE
from
sales s, products p where s.product_id=p.product_id
group
by year
Using
PIVOT (from oracle 11g onwards):
select
* from (
select
year,quantity,price,product_name
from
sales s, products p where s.product_id=p.product_id
)
pivot
(max(quantity) AS QUAN,max(price) AS PRICE FOR product_name in
('IPhone','Samsung'))
6.
Write a query to transpose the quantity for each product and display
it in columns? The output should look like as
PRODUCT_NAME | QUAN_2010 | QUAN_2011 | QUAN_2012
---------------------------------------------------------------------------------
IPhone 10 15 20
Samsung 20 18 20
Nokia 25 16 8
Using
DECODE:
SELECT
P.PRODUCT_NAME,
MAX(DECODE(S.YEAR,2010,
S.QUANTITY)) QUAN_2010,
MAX(DECODE(S.YEAR,2011,
S.QUANTITY)) QUAN_2011,
MAX(DECODE(S.YEAR,2012,
S.QUANTITY)) QUAN_2012
FROM
PRODUCTS P,
SALES
S
WHERE
(P.PRODUCT_ID = S.PRODUCT_ID)
GROUP
BY P.PRODUCT_NAME;
Using
PIVOT (from oracle 11g onwards):
SELECT
* FROM
(
SELECT
P.PRODUCT_NAME,
S.QUANTITY,
S.YEAR
FROM
PRODUCTS P,
SALES
S
WHERE
(P.PRODUCT_ID = S.PRODUCT_ID)
)A
PIVOT
( MAX(QUANTITY) AS QUAN FOR (YEAR) IN (2010,2011,2012));
7.
Write a query to generate sequence numbers from 1 to the specified
number N?
SELECT
LEVEL FROM DUAL CONNECT BY LEVEL<=&N;
8.
Write a query to display only friday dates from Jan, 2000 to till
now?
select
d_date,to_char(d_date,'DY') as Day from (
select
to_date('01-JAN-2000','DD-MON-YYYY')+Level-1 as d_date from dual
connect
by level < = (SYSDATE - to_date('01-JAN-2000','DD-MON-YYYY')+1)
)
where to_char(d_date,'DY') = 'FRI'
9.
Write a query to duplicate each row based on the value in the repeat
column? The input table data looks like as below
Products,
Repeat
----------------
A,
3
B,
5
C,
2
Now
in the output data, the product A should be repeated 3 times, B
should be repeated 5 times and C should be repeated 2 times. The
output will look like as below
Products,
Repeat
----------------
A,
3
A,
3
A,
3
B,
5
B,
5
B,
5
B,
5
B,
5
C,
2
C,
2
SELECT
PRODUCTS,
REPEAT
FROM
T,
(
SELECT LEVEL L FROM DUAL
CONNECT
BY LEVEL <= (SELECT MAX(REPEAT) FROM T)
)
A
WHERE
T.REPEAT >= A.L
ORDER
BY T.PRODUCTS;
10.
Write a query to display each letter of the word "IPHONE"
in a separate row?
I
P
H
O
N
E
SELECT
SUBSTR('IPHONE',LEVEL,1) A
FROM
DUAL
CONNECT
BY LEVEL <=LENGTH('IPHONE');
11.
Convert the string "IPHONE" to Ascii values? The output
should look like as 73,80,72,79,78,69. Where 73 is the ascii value of
I and so on.
Using
WM_CONCAT() which is not official & documented:
select
wm_concat(A) from(
SELECT
ascii(SUBSTR('IPHONE',LEVEL,1)) A
FROM
DUAL
CONNECT
BY LEVEL <=LENGTH('IPHONE'))
Using
DUMP:
SELECT
SUBSTR(DUMP('IPHONE'),15)
FROM
DUAL;
Using
LISTAGG():
select
LISTAGG(A,',') within group (order by B) from(
SELECT
ascii(SUBSTR('IPHONE',LEVEL,1)) A ,LEVEL B
FROM
DUAL
CONNECT
BY LEVEL <=LENGTH('IPHONE'))
12.
I have a table which has only one column and one row. The numeric
column contains a 6 digit number. My requirements is to find out the
product of the 3 consecutive digits and display them in separate
rows.
Sample
input data output are shown below:
Source
Table Name: Digits
Value
-----------
123456
Output:
product
------------
6
24
60
120
select
(substr(Value,level,1)*substr(Value,level+1,1)*substr(Value,level+2,1))
as product from Digits
connect
by level <= length(Value)-2
13.
Write a query to print star triangle.
select
rpad( '* ', level*2, '* ' ) from dual connect by level <= 3
14.
How to create test data in bulk ?
Refer
following queries :
Insert
into test(id, name) select level, dbms_random.string('ss',4) from
dual connect by level<=1000
SELECT
LEVEL empl_id,
MOD
(ROWNUM, 50000) dept_id,
TRUNC
(DBMS_RANDOM.VALUE (1000, 500000), 2) salary,
DECODE (ROUND (DBMS_RANDOM.VALUE (1, 2)), 1, 'M', 2, 'F') gender,
TO_DATE ( ROUND (DBMS_RANDOM.VALUE (1, 28))
|| '-'
|| ROUND (DBMS_RANDOM.VALUE (1, 12))
|| '-'
|| ROUND (DBMS_RANDOM.VALUE (1900, 2010)),
'DD-MM-YYYY'
) dob,
DBMS_RANDOM.STRING
('x', DBMS_RANDOM.VALUE (20, 50)) address
FROM
DUAL
CONNECT
BY LEVEL < 10000;
15.
One of the columns in the table is of varchar data type. It contains
both strings, numbers and mix of char and numeric values. My problem
is to print only the values that contains only numeric digits.
Sample
data in the columne is shown below:
--------------
Ora123
786
database
92db
The
output should contain the following values:
786
select
* from test where regexp_like(a,'^[0-9]+$')
select
* from test where regexp_like(a,'^[0-9]*$')
select
* from test where regexp_like(a,'^[[:digit:]]+$')
select
* from test where upper(a)=lower(a) --will fail for special
characters
select
* from test where LTRIM (a, '0123456789') IS NULL
select
* from test where translate(a,'0123456789',' ') IS NULL
