Tuesday, April 9, 2019

SQL Interview Questions

Question 1: If we have two tables having 1 column each, with 4 records.

 













What is the number of records if you apply

JOIN
LEFT JOIN
RIGHT JOIN


Solution

Create two tables:
CREATE TABLE Test1
(
  COL1         NUMBER
);

CREATE TABLE Test2
(
  COL1         NUMBER
);


Insert records:

INSERT INTO Test1 VALUES (1);
INSERT INTO Test2 VALUES (1);


Join

select * from Test1 a JOIN Test2 b
On a.col1 = b.col1

select * from Test1 a Left JOIN Test2 b
On a.col1 = b.col1

select * from Test1 a Right JOIN Test2 b
On a.col1 = b.col1

For all the result will be below:


_______________________________________________________________________
Question 2: How to find count of duplicate rows? 
Answer:
Select rollno, count (rollno) from Student
Group by rollno
Having count (rollno)>1
Order by count (rollno) desc;
______________________________________________________________________

Question 3: How to remove duplicate rows from table?
Answer:
First Step: Selecting Duplicate rows from table
Tip: Use concept of max (rowid) of table. Click here to get concept of rowid.
Select rollno FROM Student WHERE ROWID <>
(Select max (rowid) from Student b where rollno=b.rollno);
Step 2:  Delete duplicate rows
Delete FROM Student WHERE ROWID <>
(Select max (rowid) from Student b where rollno=b.rollno);

_____________________________________________________________________

Question: How to fetch all the records from Employee whose joining year is  2017?
Answer:
Oracle:
select * from Employee where To_char(Joining_date,’YYYY’)=’2017′;
MS SQL:
select * from Employee where substr(convert(varchar,Joining_date,103),7,4)=’2017′;
___________________________________________________________________________

Question:

sql> SELECT * FROM runners;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | John Doe     |
|  2 | Jane Doe     |
|  3 | Alice Jones  |
|  4 | Bobby Louis  |
|  5 | Lisa Romero  |
+----+--------------+

sql> SELECT * FROM races;
+----+----------------+-----------+
| id | event          | winner_id |
+----+----------------+-----------+
|  1 | 100 meter dash |  2        |
|  2 | 500 meter dash |  3        |
|  3 | cross-country  |  2        |
|  4 | triathalon     |  NULL     |
+----+----------------+-----------+
What will be the result of the query below?
SELECT * FROM runners WHERE id NOT IN (SELECT winner_id FROM races)
Explain your answer and also provide an alternative version of this query that will avoid the issue that it exposes.

Surprisingly, given the sample data provided, the result of this query will be an empty set. The reason for this is as follows: If the set being evaluated by the SQL NOT IN condition contains any values that are null, then the outer query here will return an empty set, even if there are many runner ids that match winner_ids in the races table.
Knowing this, a query that avoids this issue would be as follows:
SELECT * FROM runners WHERE id NOT IN (SELECT winner_id FROM races WHERE winner_id IS NOT null)

Note, this is assuming the standard SQL behavior that you get without modifying the default ANSI_NULLS setting.
_________________________________________________________________________

 Question : How would you create a list or a table from scratch in a database where you dont have write permission.

 Answer: Use the With Clause

 With Table1 As(
select 'MLCF02353' As C1,'Thalam' As Names from dual union
select 'ICBNY0061', 'Abhishek' from dual union
select 'BNPN.02E9','Roza' from dual
 )


 select * from table1

 _________________________________________________________________________

 Question : Extract the first name, last name from a full name

 Answer:

 with table_A as
(
select 'Abhishek Rath' as Full_name from dual union all
select 'Piyush Gangwar ' as Full_name from dual union all
select 'kabita Ghosh' as Full_name from dual union all
select 'Nimisha Jain ' as Full_name from dual union all
Select 'Pranav Thale' as Full_name from dual
)

 select Full_name,
SUBSTR(Full_name,0,INSTR(TRIM(Full_name),' ')- 1) AS first_name,

SUBSTR(full_name,INSTR(TRIM(full_name),' ',-1)+ 1) AS last_name  from table_A


_______________________________________________________________________________

Question : Extract 

Answer :

Assuming you requirement is something like..

FIRST_NAME : The first word in your full name
LAST_NAME  : The last Word in the Full Name excluding the SUFFIX (if present)
MID_NAME    : The entire set of word between FIRST_NAME and LAST_NAME
SUFFIX             : The word / letters after the last comma

WITH NAMES(FULL_NAME) AS(
         SELECT 'ABC DEF GHI JKL, MN'    FROM DUAL UNION ALL
         SELECT 'OPQ RST, UV'                      FROM DUAL UNION ALL
         SELECT 'WXY Z'                                  FROM DUAL UNION ALL
         SELECT 'Ronald V. McDonald, DO' FROM DUAL UNION ALL
         SELECT 'Fred Derf, DD'                     FROM DUAL UNION ALL
         SELECT 'Pig Pen'                                 FROM DUAL )
SELECT  FULL_NAME
        ,SUBSTR(FULL_NAME,0,INSTR(FULL_NAME,' ')-1) AS FIRST_NAME
        ,CASE WHEN (REGEXP_COUNT(FULL_NAME,',')=0) AND (REGEXP_COUNT(FULL_NAME,' ')>1)
                             THEN SUBSTR(FULL_NAME, INSTR(FULL_NAME,' ',1)+1, INSTR(FULL_NAME, ' ',-1)-1)
                    WHEN (REGEXP_COUNT(FULL_NAME,',')>0) AND (REGEXP_COUNT(SUBSTR(FULL_NAME,1,INSTR(FULL_NAME,',', -1)-1),' ')>1)
                             THEN REGEXP_REPLACE(SUBSTR(REGEXP_SUBSTR(FULL_NAME, ' (.*?),'), 1, INSTR(REGEXP_SUBSTR(FULL_NAME, ' (.*?),'),' ',-1)),'[[:punct:]]')
                    ELSE NULL END AS MID_NAME
        ,CASE WHEN REGEXP_COUNT(FULL_NAME,',')=0
                             THEN SUBSTR(FULL_NAME, INSTR(FULL_NAME,' ',-1)+1)
                    ELSE SUBSTR(SUBSTR(FULL_NAME,1,INSTR(FULL_NAME,',',-1)-1), INSTR(SUBSTR(FULL_NAME,1,INSTR(FULL_NAME,',',-1)-1),' ',-1)+1) END AS LAST_NAME
        ,CASE WHEN REGEXP_COUNT(FULL_NAME,',')>0
                              THEN SUBSTR(FULL_NAME,INSTR(FULL_NAME,',',-1)+1)
                    ELSE NULL END AS SUFFIX
FROM  NAMES;

FULL_NAME
FIRST_NAME
MID_NAME
LAST_NAME
SUFFIX
ABC DEF GHI JKL, MNABCDEF GHIJKLMN
OPQ RST, UVOPQ-RSTUV
WXY ZWXY-Z-
Ronald V. McDonald, DORonaldVMcDonaldDO
Fred Derf, DDFred-DerfDD
Pig PenPig-Pen-

 _______________________________________________________________________________

Question: INSTR Function explained with examples (https://www.databasestar.com/oracle-instr/)

Question: Regular Expression explained with examples (https://www.databasestar.com/oracle-regexp-functions/)

_______________________________________________________________________________

Question: Print all the dates from past x number of months or x number of days till today.

Answer:

select
   to_date(to_CHAR(ADD_MONTHS(TRUNC(SYSDATE), -3),'dd/mm/yyyy'),'dd-mm-yyyy') + rownum -1 AS DAY
from
   all_objects -- All objects table is used as it has a lot of records.
where
   rownum <=   to_date(TO_CHAR(SYSDATE,'DD/MM/YYYY'),'DD/MM/YYYY')-to_date(to_CHAR(ADD_MONTHS(TRUNC(SYSDATE), -3),'dd/mm/yyyy'),'dd-mm-yyyy')+1
_______________________________________________________________________________
Question :

Table A

Table B

1

1

2

2

1

3

3

4

4

4

5

4


What is the Output of Left Join and Inner join

_______________________________________________________________________________

Question :

Name

Subject

Marks

Grade

Range

Ravi

Maths

70

A

80>=

Sandeep

English

80

B

70-79

Rahul

Science

65

C

60-69

Sakshi

SST

76

D

<60

Swati

Computers

84

Ramesh

Accounting

55

What is the count of total students having grades A to D

_______________________________________________________________________________

Question:

abhishekrath@ubs.com
akankshakapoor@mastercard.com
rahul.gandhi@congress.com


Write a query to display only the domains

_______________________________________________________________________________


Question:

Select col1,col2 from table group by col1
Select col1 from table group by col1,col2
select col1,col2 from table group by col1,col2

Which one will give error?

_______________________________________________________________________________

Question:

with rws as (
  select 'split,into,rows' str from dual
)
  select regexp_substr (
           str,
           '[^,]+',
           1,
           level
         ) value
  from   rws
  connect by level <= 
    length ( str ) - length ( replace ( str, ',' ) ) + 1;
 
Output
--------
   
VALUE   
split    
into     
rows



So what's going on here?

The connect by level clause generates a row for each value. It finds how many values there are by:

  • Using replace ( str, ',' ) to remove all the commas from the string
  • Subtracting the length of the replaced string from the original to get the number of commas
  • Add one to this result to get the number of values

The regexp_substr extracts each value using this regular expression:

[^,]+

This searches for:

  • Characters not in the list after the caret. So everything except a comma.
  • The plus operator means it must match one or more of these non-comma characters

The third argument tells regexp_substr to start the search at the first character. And the final one instructs it to fetch the Nth occurrence of the pattern. So row one finds the first value, row two the second, and so on.


_______________________________________________________________________________

Question:

Given the following table:

ORDER_DATEPRODUCT_IDQTY
2007/09/25100020
2007/09/26200015
2007/09/2710008
2007/09/28200012
2007/09/2920002
2007/09/3010004

I need the Output in the following format:

PRODUCT_IDORDER_DATENEXT_ORDER_DATE
10002007/09/252007/09/26
20002007/09/262007/09/27
10002007/09/272007/09/28
20002007/09/282007/09/29
20002007/09/292007/09/30
10002007/09/30NULL


Answer:
SELECT product_id, order_date,
LEAD (order_date,1) OVER (ORDER BY order_date) AS next_order_date
FROM orders;

In this example, the LEAD function will sort in ascending order all of the order_date values in the orders table and then return the next order_date since we used an offset of 1.

If we had used an offset of 2 instead, it would have returned the order_date from 2 orders later. If we had used an offset of 3, it would have returned the order_date from 3 orders later....and so on.

Using Partitions

Now let's look at a more complex example where we use a query partition clause to return the next order_date for each product_id.

Enter the following SQL statement:

SELECT product_id, order_date,
LEAD (order_date,1) OVER (PARTITION BY product_id ORDER BY order_date) AS next_order_date
FROM orders;

It would return the following result:

PRODUCT_IDORDER_DATENEXT_ORDER_DATE
10002007/09/252007/09/27
10002007/09/272007/09/30
10002007/09/30NULL
20002007/09/262007/09/28
20002007/09/282007/09/29
20002007/09/29NULL

In this example, the LEAD function will partition the results by product_id and then sort by order_date as indicated by PARTITION BY product_id ORDER BY order_date. This means that the LEAD function will only evaluate an order_date value if the product_id matches the current record's product_id. When a new product_id is encountered, the LEAD function will restart its calculations and use the appropriate product_id partition.

As you can see, the 3rd record in the result set has a value of NULL for the next_order_date because it is the last record for the partition where product_id is 1000 (sorted by order_date). This is also true for the 6th record where the product_id is 2000.

_______________________________________________________________________________

Question

https://techtfq.com/blog/practice-sql-interview-query-big-4-interview-question#google_vignette

-->> Problem Statement:
Write a query to fetch the record of brand whose amount is increasing every year.


-->> Dataset:
drop table brands;
create table brands
(
    Year    int,
    Brand   varchar(20),
    Amount  int
);
insert into brands values (2018, 'Apple', 45000);
insert into brands values (2019, 'Apple', 35000);
insert into brands values (2020, 'Apple', 75000);
insert into brands values (2018, 'Samsung', 15000);
insert into brands values (2019, 'Samsung', 20000);
insert into brands values (2020, 'Samsung', 25000);
insert into brands values (2018, 'Nokia', 21000);
insert into brands values (2019, 'Nokia', 17000);
insert into brands values (2020, 'Nokia', 14000);

-------------------------------------------------------------------------------------------------
-->> Solution:

with cte as
    (select *
    , (case when amount < lead(amount, 1, amount+1)
                                over(partition by brand order by year)
                then 1
           else 0
      end) as flag
    from brands)
select *
from brands
where brand not in (select brand from cte where flag = 0)

Or (My answer)

WITH CTE AS(

Select a.*,lead(amount)  over(partition by brand order by year),
case 
    when amount< lead(amount)  over(partition by brand order by year) THEN 1 
    when lead(amount)  over(partition by brand order by year) IS NULL THEN 1
    ELSE 0 END AS FLAG
from brands a
)

Select YEAR, BRAND, AMOUNT
FROM CTE
WHERE BRAND NOT IN (Select BRAND from CTE where FLAG = 0)

_______________________________________________________________________________

Scenario-

Q) How to get the first day and the last day of any month in Oracle SQL

A) SELECT TRUNC(SYSDATE, 'MM') AS first_day_of_mnth, LAST_DAY(SYSDATE) AS last_day_of_mnth
FROM dual;




Posted by at No comments:
Subscribe to: Posts (Atom)