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
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;
| ABC DEF GHI JKL, MN | ABC | DEF GHI | JKL | MN |
| OPQ RST, UV | OPQ | - | RST | UV |
| WXY Z | WXY | - | Z | - |
| Ronald V. McDonald, DO | Ronald | V | McDonald | DO |
| Fred Derf, DD | Fred | - | Derf | DD |
| Pig Pen | Pig | - | 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 :
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_DATE | PRODUCT_ID | QTY |
|---|
| 2007/09/25 | 1000 | 20 |
| 2007/09/26 | 2000 | 15 |
| 2007/09/27 | 1000 | 8 |
| 2007/09/28 | 2000 | 12 |
| 2007/09/29 | 2000 | 2 |
| 2007/09/30 | 1000 | 4 |
I need the Output in the following format:
| PRODUCT_ID | ORDER_DATE | NEXT_ORDER_DATE |
|---|
| 1000 | 2007/09/25 | 2007/09/26 |
| 2000 | 2007/09/26 | 2007/09/27 |
| 1000 | 2007/09/27 | 2007/09/28 |
| 2000 | 2007/09/28 | 2007/09/29 |
| 2000 | 2007/09/29 | 2007/09/30 |
| 1000 | 2007/09/30 | NULL |
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_ID | ORDER_DATE | NEXT_ORDER_DATE |
|---|
| 1000 | 2007/09/25 | 2007/09/27 |
| 1000 | 2007/09/27 | 2007/09/30 |
| 1000 | 2007/09/30 | NULL |
| 2000 | 2007/09/26 | 2007/09/28 |
| 2000 | 2007/09/28 | 2007/09/29 |
| 2000 | 2007/09/29 | NULL |
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;
