SQL Server interview questions advance: Lets quickly go through these top 20 queries to make it through the interview.
If you’re planning to attend an Interview on Database, and now you think, where should I focus to get prepared for SQL Server?. Then I am bringing out this article, to make sure that you qualify. I am listing here my top 20 SQL Server interview questions for experienced questions and their answers.
Questions asked for those having 2- 5 years of experience.
1. Write a query to get the second maximum record from a table.
Method 1:
SELECT * FROM (Select * , dense_rank() over (order by salary desc) as rnk from Customer ) as t where t.rnk = 2
Method2:
select * from customer order by id offset 1 rows fetch next 1 rows only
2. What are Common table Expressions and write a query to get the second maximum record from a table?
A Common Table Expression allows us to define a named result which is temporary,i.e it results to give a complex query, simplifies complex joins and subqueries, and provides a means to query hierarchical data with much higher speed.
;With Cust as
(Select * ,dense_rank() over(order by id desc) as salary_rnk from Customer
)
select * from Cust where salary_rnk = 6
3. Write a query to select the top 3 with all the repeating values if present?
SELECT TOP 3 WITH TIES
product_name,
list_price
FROM
production.products
ORDER BY
list_price DESC;
Output:
200
100
50
50
50
50
//Select top(3) with ties * from Customer order by Salary
4. What is Group By? Write a query to get the queries by using group by?
Group By statement groups rows that have the same values into rows
Select country, count(Country) as countru_count from Customer group by Country order by countru_count
Select Salary, count(Salary) as counts from customer group by Salary having Salary > 1000 order by counts
Norway 1
Poland 1
Ireland 1
Portugal 2
Sweden 2
Switzer 2
Austria 2
Belgium 2
Denmark 2
Finland 2
Argentina 3
5. Write a query to use all clauses in SQL Server ?
SELECT country, count(Country) as counts FROM Customer where country like ‘%a’ group by Country having country like ‘%a’ order by counts desc
6. Write a query to show alter operation for dropping, adding, changing the type of a column?
ALTER TABLE table_name
ALTER COLUMN column_name new_data_type(size);
ALTER TABLE table_name
ADD column_name data_type column_constraint;
ALTER TABLE table_name
DROP COLUMN column_name;
7. Write a query to Rename a table?
EXEC sp_rename ‘old_table_name’, ‘new_table_name’
8. What are Synonyms? Write a query to show it?
A synonym is an alias or alternative name for a database object such as a table, view, stored procedure, user-defined function, and sequence.
CREATE SYNONYM [ schema_name_1. ] synonym_name
FOR object;
eg
CREATE SYNONYM orders FOR sales.orders;
DROP SYNONYM [ IF EXISTS ] [schema.] synonym_name
DROP SYNONYM IF EXISTS orders;
9. Write a query to show Foreign Key ?
CREATE TABLE procurement.vendors (
vendor_id INT IDENTITY PRIMARY KEY,
vendor_name VARCHAR(100) NOT NULL,
group_id INT NOT NULL,
CONSTRAINT fk_group FOREIGN KEY (group_id)
REFERENCES procurement.vendor_groups(group_id)
)
CREATE TABLE procurement.vendor_groups (
group_id INT IDENTITY PRIMARY KEY,
group_name VARCHAR (100) NOT NULL
);
10. Write a query to use CASE in SQL Server ?
CASE input
WHEN e1 THEN r1
WHEN e2 THEN r2
…
WHEN en THEN rn
[ ELSE re ]
END
eg.
SELECT
CASE order_status
WHEN 1 THEN ‘Pending’
WHEN 2 THEN ‘Processing’
WHEN 3 THEN ‘Rejected’
WHEN 4 THEN ‘Completed’
END AS order_status,
COUNT(order_id) order_count
FROM
sales.orders
WHERE
YEAR(order_date) = 2018
GROUP BY
order_status;
11. What are index in SQL Server?
Indexes are the special addresses given to any column name to retrieve data from the database more quickly than otherwise. The users cannot see these special indexes, as they are just used to make the queries speedy .
- Clustered
- Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be stored in only one order.
- The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.
- Nonclustered
- Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.
- The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.
- You can add nonkey columns to the leaf level of the nonclustered index to by-pass existing index key limits, and execute fully covered, indexed, queries. For more information, see Create Indexes with Included Columns. For details about index key limits see Maximum Capacity Specifications for SQL Server.
- ** The above information is taken from
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-server-ver15
12. What do you mean by Dynamic queries in SQL Server?
Query passing as like parameter
Declare @table nvar char(max)
Declare @query nvarchar(max)
set @query = ‘select * from ‘ + @table;
exec sp_executesql @query
13. What is VIEW in SQL Server ?
A view is like an abstract form of a table or a virtual table based on the certain result carried out by a SQL statement. It contains rows and columns, just like a real table where it can comprise fields from one or more real tables in the database.
14. Write a query to show a VIEW?
CREATE VIEW sales.product_info
AS
SELECT
product_name,
brand_name,
list_price
FROM
production.products p
INNER JOIN production.brands b
ON b.brand_id = p.brand_id;
15. Write a query to show output parameter for a Stored procedure?
CREATE PROCEDURE uspFindProductByModel (
@model_year SMALLINT,
@product_count INT OUTPUT
) AS
BEGIN
SELECT
product_name,
list_price
FROM
production.products
WHERE
model_year = @model_year;
SELECT @product_count = @@ROWCOUNT;
END
//To execute
DECLARE @count INT;
EXEC uspFindProductByModel
@model_year = 2018,
@product_count = @count OUTPUT;
SELECT @count AS ‘Number of products found’;
16. What are cursors in a SQL Server?
A SQL cursor is an object that is used to retrieve data from a set of the result set where it allows us to process individual row returned by a query.
When we want to process the data row by row of table or view then we use it.
17. Life cycles or command of Cursors in SQL Server?
- Declaring Cursor
- Opening Cursor
- Fetching Cursor
- Closing Cursor
- Deallocating Cursor
--Declare <cursor name> cursor for <select statement>
--open <cursor name>
--fetch next from cursor into <variable list >
--while @@fetch_status = 0
begin
fetch next from <cusrsor name>
end
--close <cursor name>
--deallocate <cursor name >18. Write a query to show cursor operation?
DECLARE
@product_name VARCHAR(MAX),
@list_price DECIMAL;DECLARE cursor_product CURSOR
FOR SELECT
product_name,
list_price
FROM
production.products;
OPEN cursor_product;
FETCH NEXT FROM cursor_product INTO
@product_name,
@list_price;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @product_name + CAST(@list_price AS varchar);
FETCH NEXT FROM cursor_product INTO
@product_name,
@list_price;
END;
CLOSE cursor_product;
DEALLOCATE cursor_product;
19. Different types of Join in SQL Server?
- INNER JOIN
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- FULL OUTER JOIN
- SELF JOIN
- UNION
**For detailed you can check in SQL Server interview questions advance– part(2)
20. Explain the TRIGGER in SQL Server.
Special stored procedures that are executed automatically in response to the database object , database and server events.
–3 types
1.DML triggers (insert , update , delete)
2.DDL triggers (create , alter , drop)
3.Logon triggers
—————–//Create triggers
–sql server provides two virtual tables (inserted and deleted tables) , and it uses to capture the modified row before and after th event occurs
—
create table electronics
(
prod_id int identity constraint pk_electronics primary key ,
prod_name nvarchar(100),
numbers int ,
status bit )
create table electronics_audit
(
id int identity primary key ,
prod_id int ,
name varchar(max),
numbers int,
status bit ,
updated datetime,
operation varchar(10)
)
create trigger tr_elctronics_audit on electronics
after insert , delete , update
as
begin
set nocount on ;
insert into electronics_audit
select prod_id ,prod_name , numbers , status , getdate() , ‘inserted’
from inserted as i
union all
select prod_id , prod_name , numbers , status , getdate() , ‘deleted’
from deleted as d
union all
select u.prod_id , u.prod_name , u.numbers , u.status , getdate() , ‘updated’
from inserted as u
end
insert into electronics values
(
‘trimmer’ , 10 ,1
)
Select * from electronics
Select * from electronics_audit
create a table on which u want a trigger operation
–let it be some electronics table
–create a trigger table , where u want to store ur triggered data – electrobics_audit
–create a trigger
create trigger tr_electronics_audit on electronics
after insert delete
–set nocount on :to sparse the count
–union all the inserted and deleted table in it
–now add or delete any rows
— tehn u can heck teh audit table
These were SQL Server interview questions advance , You can also check SQL Server interview questions
Also Visit:
Jquery, it’s time to master, but how much?
20 Important ASP.NET MVC Interview Questions
Encapsulation protects abstraction.
C#- Programming Guide | Starting Phase Learning(1)