We have compiled most frequently asked SQL Server Interview Questions which will help you with different expertise levels.
SQL Server Interview Questions on SQL
Note This is one of the crazy things which I did not want to put in my book. But when I did a sampling of some real interviews conducted across companies I was stunned to find some interviewers judging developers on syntaxes. I know many people will conclude this is childish but it's the interviewer's decision. If you think that this chapter is not useful you can happily skip it. But I think on fresher's level they should not
Note I will be heavily using the "Adventure Works " database which is a sample database shipped (in the previous version we had the famous "Northwind" database sample) with SQL Server 2005. Below is a view expanded from "SQL Server Management Studio".
Revisiting basic syntax of SQL?
CREATE TABLE ColorTable (code VARCHAR(2), ColorValue VARCHAR (16) ) INSERT INTO ColorTable (code, colorvalue) VALUES ('bl', 'Brown') DELETE FROM ColorTable WHERE code = 'b1' UPDATE ColorTable SET colorvalue ='Black' where code='b1' DROP TABLE table-name (CASCADE I RESTRICT} GRANT SELECT ON ColorTable TO SHIVKOIRALA WITH GRANT OPTION REVOKE SELECT, INSERT, UPDATE (ColorCode) ON ColorTable FROM Shivkoirala COMMIT [WORK] ROLLBACK [WORK] Select * from Person.Address Select AddressLinel, City from Person.Address Select AddressLinel, City from Person.Address where city = 'Sammamish'
What are “GRANT” and “REVOKE’ statements?
GRANT statement grants rights to the objects (table). While revoke does the vice-versa of it, it removes rights from the object.
What is Cascade and Restrict in DROP table SQL?
Twist: – What is “ONDELETE CASCADE” and “ONDELETE RESTRICT”?
RESTRICT specifies that the table should not be dropped if any dependencies (i.e. triggers, stored procedure, primary key, foreign key, etc) exist. So if there are dependencies then an error is generated and the object is not dropped.
CASCADE specifies that even if their dependencies go ahead with the drop. That means drop the dependencies first and then the main object also. So if the table has stored procedures and keys (primary and secondary keys) they are dropped first and then the table is finally dropped.
How to import a table using the “INSERT” statement?
I have made a new temporary color table which is flourished using the below SQL, Structures of both the table should be the same in order that this SQL executes properly.
INSERT INTO TempColorTable SELECT code, Colorvalue FROM ColorTable
What is a DDL, DML, and DCL concept in the RDBMS world?
DDL (Data definition language) defines your database structure. CREATE and ALTER are DDL statements as they affect the way your database structure is organized.
DML (Data Manipulation Language) lets you do basic functionalities like INSERT, UPDATE, DELETE and MODIFY data in the database.
DCL (Data Control Language) controls your DML and DDL statements so that your data is protected and has consistency. COMMIT and ROLLBACK are DCL control statements. DCL guarantees ACID fundamentals of a transaction.
Note Refer to "Transaction and Locks" chapter.
What are the different types of joins in SQL?
Inner join shows match only when they exist in both tables. For example, in the below SQL there are two tables Customers and Orders and the inner join is made on Customers. Customers and Orders.Customer. So this SQL will only give you result with customers who have ordered. If the customer does not have order it will not display that record.
SELECT Customers.*, Orders.* FROM Customers INNER JOIN Orders ON Customers.CustomerlD =Orders.CustomerlD
LEFT OUTER JOIN
Left join will display all records in the left table of the SQL statement. In SQL below customers with or without orders will be displayed. Order data for customers without orders appears as NULL values. For .example, you want to determine the amount ordered by each customer and you need to see who has not ordered anything as well. You can also see the LEFT OUTER JOIN as a mirror image of the RIGHT OUTER JOIN (Is covered in the next section) if you switch the side of each table.
SELECT Customers.*, Orders.* FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerlD =Orders.CustomerlD
RIGHT OUTER JOIN
Right, join will display all records in the right table of the SQL statement. In SQL below all orders with or without matching customer records will be displayed. Customer data for orders without customers appear as NULL values. For example, you want to determine if there are any orders in the data with undefined CustomerlD values (say, after conversion or something like it). You can also see the RIGHT OUTER JOIN as a mirror image of the LEFT OUTER JOIN if you switch the side of each table.
SELECT Customers.*, Orders.* FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerlD =Orders.CustomerlD
What is “CROSS JOIN”?
Twist: – What is a Cartesian product?
“CROSS JOIN” or “CARTESIAN PRODUCT” combines all rows from both tables. The number of rows will be product of the number of rows in each table. In real life scenario, I can hot imagine where we will want to use a Cartesian product. But there are scenarios where we would like permutation and combination probably Cartesian would be the easiest way to achieve it.
Do you want to select the first record in a given set of rows?
Select top 1 * from sales.salesperson
How do you sort in SQL?
Using the “ORDER BY” clause, you either sort the data in ascending manner or descending manner.
select * from sales.salesperson order by salespersonid asc select * from sales.salesperson order by salespersonid desc
How do you select unique rows using SQL?
Using the “DISTINCT” clause. For example, if you fire the below give SQL in “AdventureWorks”,
the first SQL will give you distinct values for cities, while the other will give you distinct rows.
select distinct city from person.address select distinct * from person.address
Can you name some aggregate functions in SQL Server?
Some of them which every interviewer will expect:-
- AVG: Computes the average of a specific set of values, which can be an expression list or a set of data records in a table.
- SUM: Returns the sum of a specific set of values, which can be an expression list or a set of data records in a table.
- COUNT: Computes the number of data records in a table.
- MAX: Returns the maximum value from a specific set of values, which can be an expression list or a set of data records in a table.
- MIN: Returns the minimum value from a specific set of values, which can be an expression list or a set of data records in a table.
What is the default “SORT” order for SQL?
What is a self-join?
If you want to join two instances of the same table you can use self-join.
What’s the difference between DELETE and TRUNCATE?
Following are differences between them:
- DELETE TABLE syntax logs the deletes thus making the delete operations low. The TRUNCATE table does not log any information but it logs information about the deallocation of the data page of the table. So TRUNCATE table is faster as compared to the delete table.
- DELETE table can be rolled back while TRUNCATE can not be.
- DELETE table can have criteria while TRUNCATE can not.
- The TRUNCATE table can not have triggers.
Select addresses which are between ‘1/1/2004’ and ‘1/4/2004’?
Select * from Person. Address where modified date between 1/1/2004′ and 1/4/2004′
What are Wildcard operators in SQL Server?
Twist: – What is like a clause in SQL?
Note For showing how the wildcards work I will be using the “person. address” table in AdventureWorks.
There are basically two types of operators:-
“%” operator (During Interview you can spell it as “Percentage Operator”).
“%” operator searches for one or many occurrences. So when you fire a query using “%” SQL Server searches for one or many occurrences. In the below SQL I have applied the “%” operator to the “S” character.
Select AddressLinel from a person. address where AddressLinel like 'S% '
“-” operator (During Interview you can spell as “Underscore Operator”).
“-” operator is the character defined at that point. In the below sample I have fired a query
Select AddressLinel from a person. address where AddressLinel like '_h% '
So all data where the second letter is “h” is returned.
What’s the difference between “UNION” and “UNION ALL”?
UNION SQL syntax is used to select information from two tables. But it selects only distinct records from both the table. , while UNION ALL selects all records from both the tables.
To explain it practically below are two images one fire “UNION” and one “UNION ALL” on the “person. address” table of the “AdventureWorks” database.
Select * from person.address Union Select * from a person. address This returns 19614 rows (that's mean it removes duplicates) Select * from person.address union all Select * from person.address This returns 39228 rows ("unionall" does not check for duplicates so returns double the record show up)
Note Selected records should have the same data type or else the syntax will not work. Note In the coming questions you will see some 5 to 6 questions on cursors. Though not a much discussed topic but stillfrom my survey 5% of interviews have asked questions on cursors. So let's leave no stonefor the interviewer to reject us.
What are cursors and what are the situations you will use them?
SQL statements are good for the set at a time operation. So it is good at handling a set of data. But there are scenarios where you want to update rows depending on certain criteria. You will loop through all rows and update data accordingly. There’s where cursors come in to picture.
What are the steps to create a cursor?
Below are the basic steps to execute a cursor.
- Close and Deallocate
This is a small sample that uses the “person. address” class. This T-SQL program will only display records that have “@Provinceid” equal to “7”.
DECLARE @provinceid int - - Declare Cursor DECLARE provincecursor CURSOR FOR SELECT stateprovinceid FROM Person.Address - - Open cursor OPEN provincecursor - - Fetch data from cursor in to variable FETCH NEXT FROM provincecursor INTO @provinceid WHILE @@FETCH_STATUS =0 BEGIN -- Do operation according to row value if @Provinceid=7 begin PRINT @Provinceid end -- Fetch the next cursor FETCH NEXT FROM provincecursor INTO @provinceid END - - Finally do not forget to close and deallocate the cursor CLOSE provincecursor DEALLOCATE provincecursor
What are the different Cursor Types?
Cursor types ate assigned when we declare a cursor.
DECLARE cursor_name CURSOR [LOCAL I GLOBAL] [F0RWARD_0NLY / SCROLL] [STATIC I KEYSET / DYNAMIC / FAST_FORWARD] [READ_0NLY / SCR0LL__L0CKS / OPTIMISTIC] [TYPEJWARNING] FOR select_statement [FOR UPDATE [OF column_list]]
The STATIC cursor is a fixed snapshot of a set of rows. This fixed snapshot is stored in a temporary database. As the cursor is using a private snapshot any changes to the set of rows external will not be visible in the cursor while browsing through it. You can define a static cursor using the “STATIC” keyword.
DECLARE cusorname CURSOR STATIC FOR SELECT. * from tablename WHERE column1 = 2
In KEYSET the key values of the rows are saved in tempdb. For instance, let’s say the cursor has fetched the following below data. So only the “supplierid” will be stored in the database. Any new inserts happening is not reflected in the cursor. But any updates in the key-set values are reflected in the cursor. Because the cursor is identified by key values you can also absolutely fetch them using “FETCH ABSOLUTE 12 FROM mycursor”
In DYNAMIC cursor you can see any kind of changes happening i.e. either inserting new records or changes in the existing and even deletes. That’s why DYNAMIC cursors are slow and have the least performance.
FORWARD_ ONL Y
As the name suggests they only move forward and only a one-time fetch is done. In every fetch, the cursor is evaluated. That means any changes to the data are known until you have specified “STATIC” or “KEYSET”.
These types of the cursor are forward-only and read-only and in every fetch, they are not re-evaluated again. This makes them a good choice to increase performance.
What are “Global” and “Local” cursors?
Cursors are global for a connection. By default cursors are global. That means you can declare a cursor in one stored procedure and access it outside also. Local cursors are accessible only inside the object (which can be a stored procedure, trigger, or function). You can declare a cursor as “Local” or “Global” in the “DECLARE” cursor syntax. Refer to the “DECLARE” statement of the cursor in the previous sections.
What is “Group by” clause?
“Group by” clause group similar data so that aggregate values can be derived. In “AdventureWorks” there are two tables “Salesperson” and “Sales territory”. In the below figure “Actual data” is the complete view of “Salesperson”. But now we want a report that per territory wise how many salespeople are there. So in the second figure, I made a group by on territory id and used the “count” aggregate function to see some meaningful data. “Northwest” has the highest number of sales personnel.
What is ROLLUP?
ROLLUP enhances the total capabilities of the “GROUP BY” clause.
Below is a GROUP BY SQL which is applied on “SalesorderDetail” on “Productid” and “Specialofferid”. You can see 707,708,709 etc products grouped according to “Specialofferid” and the third column represents the total according to each pair of “Productid” and “Specialofferid”. Now you want to see subtotals for each group of “Productid” and “Specialofferid”.
So after using ROLLUP you can see the sub-total. The first row is the grand total or the main total, followed by sub-totals according to each combination of “Productid” and “Specialofferid”. ROLLUP retrieves a result set that contains aggregates for a hierarchy of values in selected columns.
What is CUBE?
CUBE retrieves a result set that contains aggregates for all combinations of values in the selected columns. ROLLUP retrieves a result set that contains aggregates for a hierarchy of values in selected columns.
What is the difference between “HAVING” and “WHERE” clause?
“HAVING” clause is used to specify filtering criteria for “GROUP BY”, while “WHERE” clause applies on normal SQL.
In the above example if we want to filter on territory which has sales personnel count above 2. then:-
select sales.salesterritory.name count(sales.salesperson.territoryid) as numberofsalesperson from sales.salesperson inner join sales.salesterritory on sales.salesterritory. territoryid=sales.salesperson.territoryid group by sales. salesperson. terri tory.id, sales . salesterri tory. name having count(sales.salesperson.territoryid) >= 2
Note You can see the having clause applied. In this case you can not specify it ivith “WHERE” clause it will throw an error. In short “HA VING” clause applies filter on a group while “WHERE” clause on a simple SQL.
What is “COMPUTE” clause in SQL?
“COMPUTE “clause is used in SQL to produce subtotals for each group.
What is “WITH TIES” clause in SQL?
“WITH TIES” clause specifies that additional rows be returned from the base result set with the same value in the ORDER BY columns appearing as the last of the TOP n(PERCENT) rows, So what does that sentence mean? See the below figure there are four products p1,p2,p3andp4
So when we do a TOP 3 on the “ProductCost” table we will see three rows as show below. But even p3 has the same value as p4. SQL just took the TOP 1. So if you want to display tie up data like this you can use “WITH TIES”.
You can see after firing SQL with “WITH TIES” we are able to see all the products properly.
Note You should have an “ORDER CLA USE” and “TOP” keyword specified or else “WITH TIES” is not of much use.
What does “SET ROWCOUNT” syntax achieves?
Twist: – What’s the difference between “SETROWCOUNT” and “TOP” clause in SQL?
“SET ROWCOUNT” limits the number of rows returned. Its looks very similar to “TOP” clause, but there is a major difference the way SQL is executed. The major difference between “SET ROWCOUNT” and “TOP” SQL clause is following: –
“SET ROWCOUNT is applied before the order by clause is applied. So if “ORDER BY” clause is specified it will be terminated after the specified number of rows are selected. ORDER BY clause is not executed after the row count value.”
What is a Sub-Query?
A query nested inside a SELECT statement is known as a subquery and is an alternative to complex join statements. A subquery combines data from multiple tables and returns results that are inserted into the WHERE condition of the main query. A subquery is always enclosed within parentheses and returns a column. A subquery can also be referred to as an inner query and the main query as an outer query. JOIN gives better performance than a subquery when you have to check for the existence of records.
For example, to retrieve all EmployeelD and CustomerlD records from the ORDERS table that have the EmployeelD greater than the average of the EmployeelD field, you can create a nested query, as shown:
SELECT DISTINCT EmployeelD, CustomerlD FROM ORDERS WHERE EmployeelD > (SELECT AVG (EmployeelD) FROM ORDERS)
What is “Correlated Subqueries”?
A simple subquery retrieves rows that are then passed to the outer query to produce the desired result set. Using Correlated Subqueries, the outer query retrieves rows that are then passed to the subquery. The subquery runs for each row that is processed by the outer query. Below is an example of a simple co-related subquery. You can execute it in “AdventureWorks” to see the results.
Select salespersonid ,
(Select name ' from sales.sales territory where sales.sales territory.territoryid= sales.Salesperson.Territoryid) from sales.Salesperson
Note Below are some homework questions, you can discuss with your friends for better insight.
What is “ALL” and “ANY” operator?
What is a “CASE” statement in SQL?
What does COLLATE Keyword in SQL signify?
What is CTE (Common Table Expression)?
GTE is a temporary table created from a simple SQL query. You can say it’s a view. Below is a simple CTE created “PurchaseOrderHeaderCTE” from “PurchaseOrderHeader”.
WITH PURCHASEORDERHEADERCTE(Orderdate, Status) as ( Select orderdate,Status from purchasing.PURCHASEORDERHEADER ) Select * from PURCHASEORDERHEADERCTE
The WITH statement defines the CTE and later using the CTE name I have displayed the CTE data.
Why should you use CTE rather than simple views?
With CTE you can use a recursive query with CTE itself. That’s not possible with views.
What is the TRY/CATCH block in T-SQL?
No, I am not referring to the .NET TRY/CATCH block this is the new way of handling errors in SQL Server. For instance, in the below T-SQL code any error during the delete statement is caught and the necessary error information is displayed.
BEGIN TRY DELETE tablel WHERE id=122 END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrNum, ERROR_SEVERITY() AS ErrSev, ERR0R_STATE() as ErrSt, ERROR_MESSAGE( ) as ErrMsg; END CATCH
What is the PIVOT feature in SQL Server?
PIVOT feature converts row data to columns for a better analytical view. Below is a simple PIVOT fired using CTE. Ok, the first section is the CTE which is the input and later PIVOT is applied over it.
WITH PURCHASEORDERHEADERCTE(Orderdate, Status, Subtotal) as ( Select year(orderdate),Status, isnull(Subtotal,0) from purchasing. PURCHASEORDER HEADER ) Select Status as OrderStatus,isnul1(, 0) as ' Yr 2001' ,isnull ( ,0) as 'Yr 2002' from PURCHASEORDERHEADERCTE pivot (sum(Subtotal) for Orderdate in ( , )) as pivoted
You can see from the above SQL the top WITH statement is the CTE supplied to the PIVOT. After that PIVOT is applied on subtotal and order date. You have to specify in what you want the pivot (here it is 2001 and 2002). So below is the output of CTE table.
After the PIVOT is applied you can see the rows are now grouped column wise with the subtotal assigned to each. You can summarize that PIVOT summarizes yo.ur data in cross tab format.
What is UNPIVOT?
It’s exactly the vice versa of PIVOT. That means you have a PIVOTED data and you want to UNPIVOT it.
What are RANKING functions?
They add columns that are calculated based on a ranking algorithm. These functions include
ROW_NUMBER( ), RANK( ), DENSE_RANK( )> and NTILE( ).
What is ROW.NUMBER( )?
The ROW_NUMBER() function adds a column that displays a number corresponding to the row’s position in the query result. If the column that you specify in the OVER clause is not unique, it still produces an incrementing column based on the column specified in the OVER clause. You can see in the figure below I have applied the ROW_NUMBER function over column col2 and you can notice the incrementing numbers generated.
What is KANK( )?
The RANKO function works much like the ROW_NUMBER() function in that it numbers records in order. When the column specified by the ORDER BY clause contains unique values, then ROW_NUMBER() and RANKO produce identical results. They differ in the way they work when duplicate values are contained in the ORDER BY expression. ROW_NUMBER will increment the numbers by one on every record, regardless of duplicates. RANKO produces a single number for each value in the result set. You can see for the duplicate value does not increment the row number.
What is DENSE_RANK( )?
DENSE_RANI<0 works the same way as RANK() does but eliminates the gaps in the numbering. When I say GAPS you can see in previous results it has eliminated 4 and 5 from the count because of the gap in between COL2. But for dense_rank it overlooks the gap.
What is NTILE( )?
NTILEO breaks the result set into a specified number of groups and assigns the same number to each record in a group. Ok NTILE just groups depending on the number given or you can say divides the data. For instance, I have said to NTILE it to 3. It has 6 total rows so it grouped in a number of 2.
(DB)What is SQ1 injection?
It is a form of attack on a database-driven Web site in which the attacker executes unauthorized SQL commands by taking advantage of insecure code on a system connected to the Internet, bypassing the firewall. SQL injection attacks are used to steal information from a database from which the data would normally not be available and gain access to an organization’s host computers through the computer that is hosting the database.
SQL injection attacks typically are easy to avoid by ensuring that a system has strong input validation.
As the name suggests we inject SQL which can be relatively dangerous for the database. For example this is a simple SQL
SELECT email, passwd, login_id, full_name FROM members WHERE email = 'x'
Now somebody does not put “x” as the input but puts “x ; DROP TABLE members;”. So the actual SQL which will execute is
SELECT email, passwd, login_id, full_name FROM members WHERE email = 'x' ; DROP TABLE members;
Think what will happen to your database if the above SQL statement is executed.