Oracle SQL Queries In VeriSign Written Test Interview

Oracle SQL Queries Most Frequently Asked In VeriSign Written Test Interview

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

Solution:

Nokia is the top product sold in the year 2010. Similarly, Samsung in 2011 and IPhone, Samsung in 2012. The query for this is

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;

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

Solution:

This is a simple query. You just need to group by the data on PRODUCT_NAME and then find the sum of sales.

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;

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?

Solution:

This can be solved with the help of correlated query. The SQL query for this is

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
       );

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

Solution:

By using self-join SQL query we can get the required result. The required SQL query is

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

Oracle SQL Queries Most Frequently Asked In VeriSign Written Test Interview

Write a query to find the ratios of the sales of a product?

Solution:

The ratio of a product is calculated as the total sales price in a particular year divide by the total sales price across all years. Oracle provides RATIO_TO_REPORT analytical function for finding the ratios. The SQL query is

SELECT P.PRODUCT_NAME,
       S.YEAR,
       RATIO_TO_REPORT(S.QUANTITY*S.PRICE)
         OVER(PARTITION BY P.PRODUCT_NAME ) SALES_RATIO
FROM   PRODUCTS P,
       SALES S
WHERE (P.PRODUCT_ID = S.PRODUCT_ID);

In the SALES table quantity of each product is stored in rows for every year. Now 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

Solution:

Oracle 11g provides a pivot function to transpose the row data into column data. The SQL query for this is

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));

If you are not running oracle 11g database, then use the below query for transposing the row data into column data.

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;

Get Joining Date,Time including milliseconds from employee table

select to_char(JOINING_DATE,'dd/mm/yyyy HH:mi:ss.ff') from
EMPLOYEE . Column Data Type should be “TimeStamp”

Oracle Create Query

Oracle create query is used to create a table, view, sequence, procedure and function. For example:

CREATE TABLE customers
( id number(10) NOT NULL,
  name varchar2(50) NOT NULL,
  city varchar2(50),
CONSTRAINT customers_pk PRIMARY KEY (id) 
);

Oracle Alter Query

Oracle alter query is used to add, modify, delete or drop colums of a table. Let's see a query to add column in customers table:

ALTER TABLE customers
ADD age varchar2(50);

How to add column in a table

Syntax:

ALTER TABLE table_name
  ADD column_name column-definition;
Example:

Consider that already existing table customers. Now, add a new column customer_age into the table customers.

ALTER TABLE customers
  ADD customer_age varchar2(50);
Now, a new column "customer_age" will be added in customers table.

How to add multiple columns in the existing table

Syntax:

ALTER TABLE table_name
  ADD (column_1 column-definition,
       column_2 column-definition,
       ...
       column_n column_definition);
Example

ALTER TABLE customers
  ADD (customer_type varchar2(50),
       customer_address varchar2(50));
Now, two columns customer_type and customer_address will be added in the table customers.

How to modify column of a table

Syntax:

ALTER TABLE table_name
  MODIFY column_name column_type;
Example:

ALTER TABLE customers
  MODIFY customer_name varchar2(100) not null;
Now the column column_name in the customers table is modified
to varchar2 (100) and forced the column to not allow null values.

How to modify multiple columns of a table

Syntax:

ALTER TABLE table_name
  MODIFY (column_1 column_type,
          column_2 column_type,
          ...
          column_n column_type);
Example:

ALTER TABLE customers
  MODIFY (customer_name varchar2(100) not null,
          city varchar2(100));
This will modify both the customer_name and city columns in the table.