SQL Interview Questions(Set-2) – User friendly Tech help
1. Where clause cannot be used with aggregate functions (like sum, max, min, count) so we need to implement having clause.
n
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
n
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.
n
2. Composite primary key is used to define more than one primary column for a table.
n
CREATE TABLE A
n
(ID integer,test
n
NAME varchar (30),
n
CLASS varchar (50),
n
PRIMARY KEY (ID, NAME));
n
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.
n
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.
n
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.
n
SELECT B.Accountid
n
FROM Table B AS B
n
LEFT JOIN Table A AS A ON A.ID = B.Accountid
n
WHERE A.ID IS NULL
n
n
4. This time we would exclude the common ID’s again by using ‘Where’ Clause and “Full Outer Join”
n
SELECT B.Accountid, A.ID FROM Table B AS B
n
FULL OUTER JOIN Table A AS A ON A.ID = B.Accountid
n
WHERE A.ID IS NULL OR B.AccountID IS NULL
n
n
5. It deletes any of the rows which are repeating from the table.
n
delete from Table A where ID in (select A.ID from Table A group by ID having
n
count (*) >1)
n
7. We need to use “Inner Join” as we need the common data of both the tables based on the candidate ID.
n
SELECT B.CandidateName
n
FROM Table Candidate AS B
n
INNER JOIN Table vote AS ON A.ID = B.ID
n
GROUP BY B.Name
n
8. SELECT TOP 1 Salary
n
FROM (
n
SELECT DISTINCT TOP N Salary
n
FROM Employee
n
ORDER BY Salary DESC
n
) AS Emp
n
ORDER BY Salary
n
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.
n
9. Join means a guide to database to combine data from more than one table.
n
Types of join:-
n
Inner,Outer,Cross&Self Join
n
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
n
Outer types: – Left Outer, Right outer and Full Outer.
n
Left outer join: – It gives data from the left join table + the common data from the joining tables.
n
Right outer join: – Reverse of above, data from the right joining table + common data from the joining tables.
n
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.
n
Thus full is the combination of left and right outer joins
n
n
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.
n
nSelf join: – Joining the same table twice
n
n10 .General sequence of query execution :-
