SQL / ORACLE- Scenario Based Interview Questions & Answers PART- 10

   Consider below two tables Table_First and Table_Second for answering the below questions. Basically you will be able to check your JOIN concepts. Take your time and ponder over and then try answer questions. It is easy but very conceptual.

SELECT * FROM Table_First;

SELECT * FROM Table_Second;

Table_First

Table_Second

Take pen and paper and then try to solve SQL query.

Q1.What will be the ouput if we are doing INNER JOIN between the above two tables as follows:
SELECT X,Y
FROM Table_First F
INNER JOIN Table_Second S
ON F.X=S.Y;

SQL / ORACLE- Scenario Based Interview Questions & Answers PART- 9

   Consider below Employee table and Department table for answering the questions:-

SELECT * FROM EMP_DETAILS;

SELECT * FROM DEPT_DETAILS;

EMP_DETAILS

DEPT_DETAILS

SQL - ADVANCED JOINS

In my earlier SQL- JOINS  post , I had discussed about INNER JOIN , LEFT OUTER JOIN, RIGHT OUTER JOIN, SELF JOIN and CROSS JOIN. In this post, I am going to discuss about ADVANCED JOINS. Please visit my earlier post on JOINS in below URL if you have not visited.

http://itjunction4all.blogspot.com/2013/08/joins-we-want-to-fetch-data-from-two-or.html

                               We have seen that LEFT OUTER JOIN includes matching rows between left table and right table and non matching rows from left table. This is illustrated with table and venn diagram as below:

                                                                                               

Select Name,Productname,Quantity,

P.Productid,Mrp

From Customerinfo C

LEFT Outer Join Productinfo P

On C.Productid=P.Productid;

SQL- Scenario Based Questions PART- 8

SCENARIO:

Table Employee_Details has Gender column. In this Gender column M should be updated with F and F should be updated with M.

Employee_Details

ORACLE- Guide to install Oracle 11g in your local machine

Step 1:  Visit  to https://www.oracle.com/index.html  and make Oracle account.

Step 2: Login to Oracle account and then navigate to Download Section as below:

ORACLE- ANALYTIC FUNCTIONS LEAD and LAG

ORACLE- ANALYTIC FUNCTIONS LEAD and LAG

LEAD : It is a analytic function which fetches value of a column from next row. If the value in next row goes out of table or partition, default NULL value is populated. The best part of this function is that it gives access to more than one row in a table without using SELF JOIN.

SYNTAX:

SQL- Scenario Based Questions PART- 7

SCENARIO:  We have table EMPLOYEE_INFO which contains Employee information. We want to display all characters before '@' in EMAILID field as Name field. Please see below in details.

TABLE NAME: EMPLOYEE_INFO

Employee_info Table

EXPECTED RESULT:

SQL- Scenario Based Questions PART- 6

SCENARIO:  We have table COUNTRY_INFO which contains country names. Country_name field has some junk characters. Those junk characters are listed in Value_1 column. Those junk characters in Country_name  need to be replaced with value_2 values for all occurrences while displaying. Please see below the scenario in details.

TABLE NAME: COUNTRY_INFO

         COUNTRY_INFO Table

EXPECTED RESULT: