As a predominant scripting language, SQL (Structured Query Language) has been used by a plenty of organizations worldwide. In case you are gearing up for a programming interview, make sure you are prepped well with SQL. In case you already have a work experience on web projects, for a good one to two years, then there could be a fair opportunity to be aware of handling database, writing SQL queries, inserting, updating, deleting or selecting records. SQL standardized by ANSI, revolves around Database-centric applications.
Herein the Top 10 SQL queries and their answers are enlisted, so as to make the demonstration of SQL study more comprehensive.
1. What is a JOIN?
A keyword which is used to query data from various tables based on the relationship between the fields of the tables is called a Join. These Keys are important when JOINs are used.
2. What are the types of Joins?
● Inner join
Inner Join return rows when there is at least one match of rows between the tables.
● Outer Join
Outer Join will return rows which are matching from both the tables as well as any unmatched rows from one or both the tables.
● Right Join
Simply, from the right hand side table, the Right Join returns all the rows, even though there are no matches in the left hand side table.
● Left Join
A Left Join returns all the rows from Left hand side table even though there are no matches in the Right hand side table.
● Full Join
Full Join return rows when there are matching rows in any one of the tables, which means it returns all the rows from the left hand side table and all the rows from the right hand side table.
3. How does JOIN differ from UNION?
SQL JOIN lets one “lookup” or track records on the other table based on the given conditions between two tables. Whereas, UNION operation adds 2 data sets, similar in nature, to create an output data set that comprises of all the data from the source data sets, without any condition for joining.
4. How does UNION differ from UNION ALL?
Both UNION and UNION ALL stand for adding two structurally similar data sets, but UNION operation returns only the unique records from the resulting data set whereas UNION ALL will return all the rows, even in case one or more rows are duplicated to one another.
5. What is the difference between UNION, MINUS and INTERSECT?
UNION combines 2 tabular outputs and eliminates duplicate records from the output set. On the other hand, MINUS operator when used between 2 tables, provides us with all the rows from the first table except the rows which are present in the second table. And, INTERSECT operator returns us only the similar or common rows between 2 result sets.
6. How does DELETE and TRUNCATE commands differ from each other?
DELETE command erases rows from the table, and WHERE clause is often used for a conditional set of parameters. Though Commit and Rollback can be performed after Delete command, TRUNCATE removes all rows from the table which cannot be rolled back.
7. How to select any 5 records from the table?
It is a generic solution which can be worked out for this problem if and only if there exists at least one distinct column in the table.
WHERE ROWNUM <= 5;
In SQL Server,
SELECT TOP 5 * FROM EMP;
8. Write SQL Query to display the current date.
SQL has a built-in function called GetDate() which yields the current timestamp. This also works in Microsoft SQL Server. Oracle and MySQL have also presented equivalent functions.
9. What is the difference between ROWNUM, RANK and DENSE_RANK?
ROW_NUMBER assigns contiguous, unique numbers from 1.. N to a result set. But RANK does not assign unique numbers—nor does it assign contiguous numbers. If two records tie for second place, interestingly no record will be assigned the 3rd rank as no one came in third. Lastly, DENSE_RANK does not assign unique numbers. Instead it assigns contiguous numbers. Even if two records tied for the second place, there is an availability of a third-place record.
10. SQL Query to find second highest salary of Employee.
There are many ways to find second highest salary of Employee in SQL. Either use SQL Join or Subquery to solve this problem instantly. Here is the SQL query using Subquery:
select MAX(Salary) from Employee WHERE Salary NOT IN (select MAX(Salary) from Employee );
Click here! for more SQL interview question and answers.
All the Best!