SQL Theory Interview Question for Fresher
SQL Theory Interview Question for Fresher, Dear fresher “Developers” here you going to get actual interview question for “Full Stack Developers”. These all question is give enogh knowleg and overview of SQL Theory Interview Question which help to you in your interview. So lets start.
Difference between Where and Having clause?
1.WHERE clause can be used with – Select, Insert, and Update statements, where as HAVING clause can only be used with the Select statement.
2. WHERE clause filters rows before aggregation (GROUPING), whereas, HAVING clause filters groups, after the aggregations are performed.
3. Aggregate functions cannot be used in the WHERE clause, unless it is in a sub query contained in a HAVING clause, whereas, Aggregate functions can be used in HAVING clause.
What are Joins? What are different types of joins available in sql? Explain them[Most Imp Question]
- Basically there are 3 types of joins available in SQL.
- Inner Join
- Outer Join which again classified to 3 subtypes
- Left Outer Join
- Right Outer Join
- Full Outer Join
- Cross Join
Inner Join – This joins returns only the matching rows from both the tables.
Left Outer Join – This joins returns matching rows from both the tables and non matching rows from the left table.
Right Outer Join – This joins returns matching rows from both the tables and non matching rows from the right table.
Full Outer Join – This joins returns all the rows from both the tables as well as all non matching rows.
Cross Join – This join returns Cartesian product of the tables involved in the join.
SQL Theory Interview Question for Fresher
What are self join? Can you write a query using self join with one scenario?
- Joining a table with itself is called as SELF JOIN. SELF JOIN is not a different type of JOIN. It can be classified under any type of JOIN – INNER, OUTER or CROSS Joins.
- Output should be :
- Select Name as Employee, M.Name as Manager
from tblEmployee E
Left Join tblEmployee M
On E.ManagerId = M.EmployeeId
- Output by replacing NULL with ‘No Manager’ :
SELECT E.Name as Employee, ISNULL(M.Name,’No Manager’) as Manager
FROM tblEmployee E
LEFT JOIN tblEmployee M
ON E.ManagerID = M.EmployeeID
- Or
SELECT E.Name as Employee, CASE WHEN M.Name IS NULL THEN ‘No Manager’
ELSE M.Name END as Manager
FROM tblEmployee E
LEFT JOIN tblEmployee M
ON E.ManagerID = M.EmployeeID
- Or
SELECT E.Name as Employee, COALESCE(M.Name, ‘No Manager’) as Manager
FROM tblEmployee E
LEFT JOIN tblEmployee M
ON E.ManagerID = M.EmployeeID
What are different ways to replace NULL values?
- We can replace NULL value using 3 different options :
- Using ISNULL function (refer Question No 13 for query)
- Using case statement(refer Question No 13 for query)
- Using Coalesce() function – COALESCE() returns the first Non NULL value.
What is difference between Union and Union ALL ?
- UNION and UNION ALL operators in SQL Server, are used to combine the result-set of two or more SELECT queries.
- For UNION and UNION ALL to work, the Number, Data types, and the order of the columns in the select statements should be same.
- UNION removes duplicate rows, whereas UNION ALL does not.
- When we use UNION, to remove the duplicate rows, sql server has to to do a distinct sort, which is time consuming. For this reason, UNION ALL is much faster than UNION.
SQL Theory Interview Question for Fresher
Difference between Join and Union?
- JOINS and UNIONS are different things. UNION combines the result-set of two or more select queries into a single result-set which includes all the rows from all the queries in the union, whereas JOINS, retrieve data from two or more tables based on logical relationships between the tables.
- In short, UNION combines rows from 2 or more tables, where JOINS combine columns from 2 or more table.
What is a sub query? What are its various types? Explain Correlated and Non Correlated Sub query?
- A subquery is simply a select statement, that returns a single value and can be nested inside a SELECT, UPDATE, INSERT, or DELETE statement.
- Subqueries are always enclosed in parenthesis and are also called as inner queries, and the query containing the subquery is called as outer query.
- There are two types of subqueries :
- Non Correlated SubQuery :
- Sub query is executed first and only once. The sub query results are then used by the outer query.
- A non-correlated subquery can be executed independently of the outer query.
- Correlated SubQuery :
- If the subquery depends on the outer query for its values, then that sub query is called as a correlated subquery.
- Correlated subqueries get executed, once for every row that is selected by the outer query.
- Corelated subquery, cannot be executed independently of the outer query.
What are stored procedures? Explain its advantages?
- A stored procedure is group of T-SQL (Transact SQL) statements. If you have a situation, where you write the same query over and over again, you can save that specific query as a stored procedure and call it just by it’s name.
Advantages :
- Execution plan retention and reusability– Stored Procedures are compiled and their execution plan is cached and used again, when the same SP is executed again. Although adhoc queries also create and reuse plan, the plan is reused only when the query is textual match and the datatypes are matching with the previous call. Any change in the datatype or you have an extra space in the query then, a new plan is created.2. Reduces network traffic– You only need to send, EXECUTE SP_Name statement, over the network, instead of the entire batch of adhoc SQL code.
3. Code reusability and better maintainability – A stored procedure can be reused with multiple applications. If the logic has to change, we only have one place to change, where as if it is inline sql, and if you have to use it in multiple applications, we end up with multiple copies of this inline sql. If the logic has to change, we have to change at all the places, which makes it harder maintaining inline sql.
4. Better Security – A database user can be granted access to an SP and prevent them from executing direct “select” statements against a table. This is fine grain access control which will help control what data a user has access to.
5. Avoids SQL Injection attack – SP’s prevent sql injection attack.
SQL Theory Interview Question for Fresher
Subcrib my Youtube Channel- Subcribe Now