SQL Interview Question with Answers

 SQL Interview Question with Answers

Previous Questions 1-10

11. How do you copy just the structure of a table? 

SELECT * INTO NewDB.TBL_Structure FROM OldDB.TBL_Structure WHERE 1=0 -- Put any condition that does not make any sense.

12.What are the different types of Joins? 

• INNER JOIN: Gets all the matching records from both the left and right tables based on joining columns.
• LEFT OUTER JOIN: Gets all non-matching records from left table & AND one copy of matching records from both the tables based on the joining columns.
• RIGHT OUTER JOIN: Gets all non-matching records from right table & AND one copy of matching records from both the tables based on the joining columns.
• FULL OUTER JOIN: Gets all non-matching records from left table & all non-matching records from right table & one copy of matching records from both the tables.
• CROSS JOIN: returns the Cartesian product.

13. What are the different types of Restricted Joins? 

• SELF JOIN: joining a table to itself 
• RESTRICTED LEFT OUTER JOIN: gets all non-matching records from left side 
• RESTRICTED RIGHT OUTER JOIN - gets all non-matching records from right side 
• RESTRICTED FULL OUTER JOIN - gets all non-matching records from left table & gets all nonmatching records from right table.

14. What is a sub-query? 

• It is a query within a query 
• Syntax: 
• SELECT FROM 
• WHERE IN/NOT IN 
• ( 
• <another SELECT statement> 
• ) 
• Everything that we can do using sub queries can be done using Joins, but anything that we can do using Joins may/may not be done using Subquery. 
• Sub-Query consists of an inner query and outer query. Inner query is a SELECT statement the result of which is passed to the outer query. The outer query can be SELECT, UPDATE, DELETE. The result of the inner query is generally used to filter what we select from the outer query.
• We can also have a subquery inside of another subquery and so on. This is called a nested Subquery. Maximum one can have is 32 levels of nested Sub-Queries.

15. What are the SET Operators?

SQL set operators allows you to combine results from two or more SELECT statements.
Syntax: 

SELECT Col1, Col2, Col3 FROM T1 <SET OPERATOR>
SELECT Col1, Col2, Col3 FROM T2

Rule 1: The number of columns in first SELECT statement must be same as the number of columns in the second SELECT statement.

Rule 2: The metadata of all the columns in first SELECT statement MUST be exactly same as the metadata of all the columns in second SELECT statement accordingly.

Rule 3: ORDER BY clause do not work with first SELECT statement. 
UNION, UNION ALL, INTERSECT, EXCEPT

16. What is a derived table? 

○ SELECT statement that is given an alias name and can now be treated as a virtual table and operations like joins, aggregations, etc. can be performed on it like on an actual table.

○ Scope is query bound, that is a derived table exists only in the query in which it was defined. 

SELECT temp1.SalesOrderID, temp1.TotalDue FROM 

(SELECT TOP 3 SalesOrderID, TotalDue FROM Sales.SalesOrderHeader ORDER BY TotalDue DESC) AS temp1 LEFT OUTER JOIN 

(SELECT TOP 2 SalesOrderID, TotalDue FROM Sales.SalesOrderHeader ORDER BY TotalDue DESC) AS temp2 ON temp1.SalesOrderID = temp2.SalesOrderID WHERE temp2.SalesOrderID IS NULL 

17. What is a View? 

○ Views are database objects which are virtual tables whose structure is defined by underlying SELECT statement and is mainly used to implement security at rows and columns levels on the base table.

○ One can create a view on top of other views. 

○ View just needs a result set (SELECT statement).

○ We use views just like regular tables when it comes to query writing. (joins, subqueries, grouping ) 

○ We can perform DML operations (INSERT, DELETE, UPDATE) on a view. It actually affects the underlying tables only those columns can be affected which are visible in the view.

18. What are the types of views? 

1. Regular View: It is a type of view in which you are free to make any DDL changes on the underlying table.

-- create a regular view 

CREATE VIEW v_regular AS SELECT * FROM T1

2. Schemabinding View: It is a type of view in which the schema of the view (column) are physically bound to the schema of the underlying table. We are not allowed to perform any DDL changes to the underlying table for the columns that are referred by the schemabinding view structure.

■ All objects in the SELECT query of the view must be specified in two part naming conventions (schema_name.tablename).

■ You cannot use * operator in the SELECT query inside the view (individually name the columns) 

■ All rules that apply for regular view. CREATE VIEW v_schemabound WITH SCHEMABINDING AS SELECT ID, Name FROM dbo.T2 -- remember to use two part naming convention

3. Indexed View

19. What is an Indexed View? 

○ It is technically one of the types of View, not Index. 

○ Using Indexed Views, you can have more than one clustered index on the same table if needed. ○ All the indexes created on a View and underlying table are shared by Query Optimizer to select the best way to execute the query. 

○ Both the Indexed View and Base Table are always in sync at any given point. 

○ Indexed Views cannot have NCI-H, always NCI-CI, therefore a duplicate set of the data will be created. 

20. What does WITH CHECK do? 

○ WITH CHECK is used with a VIEW. ○ It is used to restrict DML operations on the view according to search predicate (WHERE clause) specified creating a view. 

○ Users cannot perform any DML operations that do not satisfy the conditions in WHERE clause while creating a view. 

○ WITH CHECK OPTION has to have a WHERE clause.