SQL Server interview questions for freshers and experienced professionals
What is DBMS ?A Database Management System(DBMS) is a program that controls creation , maintenance, and use
of a database. DBMS can be termed as File Manager that manages data in a database rather than file systems.
What is RDBMS ?
RDBMS standards for relational database management system. RDBMS store the data into the collection of tables, which is related
by common fields between the columns of the table. It also provides relational operators to manipulate the data stored into the tables.
What is SQL ?
SQL stands for Structured query language, and it is used to communicate with the Database. This is used to perform tasks such as retrieval, updation, insertion, and deletion of data from database.
union
What is database ?
Database is organization of data for easy access, storing, retrieval and managing of data.
What is primary key ?
A primary key uniquely identifies each row in a database table.
Primary keys must contain unique values.
primary key doesn't contains duplicate values and NULL values.
A table can have only one primary key.
What is unique key ?
UNIQUE key constraint also uniquely identify each row in a table, both the UNIQUE and PRIMARY KEY constraints provide uniqueness for a column.
UNIQUE key doesn't allow duplicate values and Allows Null value, but only one Null value.
What is foreign key ?
A foreign key is one table which can be related to the primary key of another table.
What is join and what are the types of join ?
Joins are used to retrive date from 2 or more related tables. In general tables are related to each other using foreign key constraints.
Types of joins:
Inner Join gives the matching rows between tables involved in join.
Left Join gives the matchings rows and non matching rows from left table.
Right Join gives the matchings rows and non matching rows from right table.
Full join returns all rows from both tables including non matching rows.
Left outer join gives the non matching rows from the left table involved in join.
Right outer join gives the non matching rows from the right table involved in join.
Cross join returns the cartesian product of tables involved in join.
Self Join used to access to releted records in same table it self.
What is normalization ?
Normalization is database design and organization process
First Normal Form : this shouls remove all th eduplicate columns from th etable. Creation of tables for the related data and identification of unique columns.
Second normal form meeting all the requirements of the first normal form. Placing the subsets of data in seperate tables and creation of relationships between the tables using primary keys.
Third Normal form should meet all requirements of 2NF, Removing the columns which are not dependent on primary key constraints.
Fourth Normal Form meeting all the requirements of third normal form and it should not have multi valued dependencies.
What is SCOPE_IDENTITY() ?
IT give the last generated identity column value generated in same session and the same scope
What is @@IDENTITY ?
IT give the last generated identity column value generated in same session across any scope
What is stored procesure ?
Stored Procedure is a data base objects which contains recompiled queries.
Types :
System Stored procedures : System Stored procedures are inbuilt stored procedures, which deals with internal meta data of SQL server.
Ex : SP_RENAMEDB, SP_HELPTEXT
User Defined Stored Procedures : User Defined Stored Procedures deals with the relational data in the database created by user.
Create procedure USP_Name
Begin
Select * from SampleTable
End
Parameterized stored procedures involve both input and output both parameters involved.
Advantages of Stored Procedures :
We will reduce unnecessary compilation of codes.
User will get quick response.
Code re usability.
SQL injection attacks removed.
How to Truncate All Tables from a SQL Server Database ?
Select 'Truncate table'+'['+schema_name(schema_id)+'].['+name+']'
How to get list of user databases from SQL Server ?
select * from sys.databases
select * from sys.databases where database_id > 4
What is Constraint ? What are types of Constraints ?
Constraint can be used to specify limit on the datatype of table. constraint can be specified while creating or altering the table statement.
NOT NULL
CHECK
DEFAULT
UNIQUE
PRIMARY KEY
FOREIGN KEY
What is data integrity ?
Data Integrity defines the accuracy and consistency of data stored in database
data integrity deals with accuracy and consistancy
What Aggrigate functions ?
Functions are self contained script program and functions are one word command thet returns single value.
Scalar functions :
Avg, Count, Max, Min
Aggregate functions :
UCASE, LCASE, Len
What is ACID property ?
Atomocity, Consistency, Isolation, Duration are the transaction property, Transaction is a logical unit of work in which all steps must be performed.
What is Implicit and Explicit transaction ?
Implicit transaction is a auto commit there is no beginning or ending of transaction
Explicit transaction has beginning and ending of transaction
SQL query for nth highest salary 2nd Highest Salary
select Max(salary) from Employee where Salary
Select TOP 1 Salary From (select Distnict TOP 2 Salary from Employees order By Salary Desc) Result Order by Salary
Select TOP 1 Salary From (select Distnict TOP 3 Salary from Employees order By Salary Desc) Result Order by Salary
Select Salary , DENSE_RANK() over (Order By Salary DESC) from Employees // DENSE_RANK gives the rank based on value
WITH RESULT
(
Select Salary , DENSE_RANK() over (Order By Salary DESC) as DENSERANK from Employees
)
Select Salary From RESULT where RESULT.DENSERANK = 2 (this number changes)
What are the two authentication modes in SQL Server?
There are two authentication modes –
Windows Mode
Mixed Mode
What is CHECK constraint?
A CHECK constraint can be applied to a column in a table to limit the values that can be placed in a column. Check constraint is to enforce integrity.
What Is SQL Profiler?
SQL Profiler is a tool which allows system administrator to monitor events in the SQL server. This is mainly used to capture and save data about each event of a file or a table for analysis.
What is recursive stored procedure ?
stored procedure which calls by itself is called Recursive stored procedure.
How to execute Stored Procedure with parameters ?
EXEC StoredProcedure_name 'paramValue1','paramValue2'What is sub query ?
A sub-query is a query which can be nested inside a main query like Select, Update, Insert or Delete statements. This can be used when expression is allowed.
What is the use of FLOOR function?
FLOOR function is used to round up a non-integer value to the previous least integer.
Example is given
FLOOR(8.7)
Returns 8.
What is a Cursor?
Cursor is a pointer to a row.
Cursors can be classified into 4 types
Forward only, Static, Key set, Dynamic.
What is trigger ?
Triggers are special type of stored procedures that are automatically fires an action in place of data modification. DBMS automatically fires triggers as a result of data modification to associated table.
Triggers types :
After Triggers and Instead of Triggers
After triggers run after insert , update, delete on a table.
What are the types of database relationships ?
Database Relationship is defined as the connection between the tables in a database.
One to One Relationship
One to Many Relationship
Many to One Relationship
Self-Referencing relationship
How to find rows that contain only numeric values ?
select value from Table where IsNumeric(Value) = 1
How to delete Duplicate records ?
With StudentCTE as
(
Select *, ROW_NUMBER() OVER (Partition By ID order by ID) as RowNumber from Student Master
)
Delete from StudentCTE where RowNumber > 1
SQL query to find Student enrolled last n months
Select * from StudentMaster Where DATEDIFF(MONTH, JoinedDate, GETDATE()) between 1 and 3 order by JoinedDate desc
SQL query to find Student enrolled last n days
Select * from StudentMaster Where DATEDIFF(DAY, JoinedDate, GETDATE()) between 1 and 15 order by JoinedDate desc
What is Union and Union ALL ? What is difference between them ?
Union and Union All all operators in SQL Server and used to combine the result of two or more tables.
The difference between Union and Union All is UnionAll will not eliminate duplicate rows in result set.
Explain SQl String Functions ?
ASCII() - Returns ASCII code of given character
CHAR() - Converts given ASCII code to Chatacter
LTRIM() - Removes blanks on left handside of the text
RTRIM() - Removes blanks on right handside of the text
LOWER() - Converts lower case
UPPER() - Converts upper case
REVERSE() - Reverse given expression
LEN() - Returns count of total characters
