Here comes our next bundle of SQL interview questions. We have also given a brush up for the brief answer hints at the bottom. Hope it help you all in achieving your target job.

Keep sharing and Keep learning.

Do follow us for more updates on  Fb,G+,Twitter.

Learn more and share more
Learn more to achieve more…

  1. What is the difference betweenwhere and having clause?
  2. What is composite key? How it differs from Candidate key?
  3. We have 2 tables A&B. Write a query to get just non matching account ID from B table using joins.
  4. In the above table modify the query to fetch non matching account ID from both the tables(Means removing the common ID’s)
  5. How to remove duplicate values from a Table A.
  6. In question 5? How to delete only one duplicate row from a table for each combination?
  7. We have 2 tables, Candidate (id, name) and vote (id, Count), write a query to give the name of winning candidate?
  8. Write a query to find the nth maximum salary in a emp table
  9. What is a join? Explain the different types of joins?
  10. Explain the sequence of query execution?
Hints:-

1. Where clause cannot be used with aggregate functions (like sum, max, min, count) so we need to implement having clause.
Example for a Table A we need to count the applications where count is greater than 5, so we cannot create a query like select count (applications) from table A where count (app) > 5, we need to use ‘having’ clause select count (app) from Table A having (count (app)) > 5
Note: – We generally use having clause with group by clause but we can use it alone. If the SQL SELECT statement does not contain aggregate functions, here it would act like a Where clause itself.

2. Composite primary key is used to define more than one primary column for a table.
CREATE TABLE A
(ID integer,test
NAME varchar (30),
CLASS varchar (50),
PRIMARY KEY (ID, NAME));
Candidate key: – A Candidate Key can be any column or a combination of columns that can qualify as unique key in database. And there could be multiple Candidate Keys in one table. Each Candidate Key can qualify as Primary Key but we have only one primary key.
Note:-A table can have multiple Candidate Keys that are unique as single column or combined multiple columns to the table. They are all candidates for Primary Key. Candidate keys that follow all the three rules – 1) Not Null, 2) Unique Value in Table and 3) Static – are the best candidates for Primary Key.

3. We need to implement Left outer join on Table B and Table A.Also we used ‘Where’ clause to restrict the common records in both the tables.
SELECT B.Accountid
FROM Table B AS B
LEFT JOIN Table A AS A ON A.ID = B.Accountid
WHERE A.ID IS NULL
Right Outer Join Example


4. This time we would exclude the common ID’s again by using ‘Where’ Clause and “Full Outer Join”
SELECT  B.Accountid, A.ID FROM Table B AS B
FULL OUTER JOIN Table A AS A ON A.ID = B.Accountid
WHERE A.ID IS NULL OR B.AccountID  IS  NULL
Full Outer Join Example


5.  It deletes any of the rows which are repeating from the table.
delete from Table A  where ID in (select  A.ID  from Table  A  group by ID  having
    count (*) >1)

7. We need to use “Inner Join” as we need the common data of both the tables based on the candidate ID.
SELECT  B.CandidateName
FROM Table Candidate AS B
INNER JOIN Table vote AS ON A.ID = B.ID
GROUP BY B.Name
8. SELECT TOP 1 Salary
FROM (
      SELECT DISTINCT TOP N Salary
      FROM Employee
      ORDER BY Salary DESC
      ) AS Emp
ORDER BY Salary
How it works, sub query it finds the N highest salaries in the Employee table and arranges them in descending order.Now,the outer query will actually rearrange those values in ascending order, which is what the very last line “ORDER BY Salary” does, because of the fact that the ORDER BY Default is to sort values in ASCENDING order. Finally, that means the Nth highest salary will be at the top of the list of salaries, which means we just want the first row, which is exactly what “SELECT TOP 1 Salary” will do for us.

9. Join means a guide to database to combine data from more than one table.
Types of join:-
Inner,Outer,Cross&Self Join

Inner Join: – inner join produces a result set that is limited to the rows where there is a match in both tables, thus it sends the matching data from joining tables

Outer types: – Left Outer, Right outer and Full Outer.

Left outer join: – It gives data from the left join table + the common data from the joining tables.

Right outer join: – Reverse of above, data from the right joining table + common data from the joining tables.

Full outer join:-A full outer join, or just outer join, produces a result set with all of the rows of both tables, regardless of whether there are any matches.

Thus full is the combination of left and right outer joins

Cross join: – The row count of the result is equal to the number of rows in the first table times the number of rows in the second table. Each row is a combination of the rows of the first and second table.

Self join: – Joining the same table twice

10 .General sequence of query execution :-

1. FROM
2. ON
3. Joins
4. WHERE
5. GROUP BY
6. HAVING
7. SELECT
8. DISTINCT
9. ORDER BY
10. Aggregate functions



More interview Questions. 
Learn Excel Programming concepts.

Leave a Reply

Your email address will not be published. Required fields are marked *