We have compiled most frequently asked .NET Interview Questions which will help you with different expertise levels.
SQL Server Interview Questions and Answers PDF Free Download for .Net Developers
Question 1.
What is Microsoft SQL Server?
Answer:
The Microsoft relational database management system is a software product that primarily stores and retrieves data requested by other applications. These applications may run on the same or a different computer. SQL is a special-purpose programming language designed to handle data in a relational database management system. A database server is a computer program that provides database services to other programs or computers, as defined by the client-server model. Therefore, a SQL Server is a database server that implements the Structured Query Language (SQL). SQL Server is a central part of the Microsoft data platform. SQL Server is an industry leader in operational database management systems (ODBMS).
Question 2.
What is Database?
Answer:
A database is a collection of information that is organized so that it can be easily accessed, managed and updated. Computer databases, typically contain aggregations of data records or files, containing information about sales transactions or interactions with specific customers.
Question 3.
Write Advantage of DBMS?
Answer:
The following advantage of DBMS in SQL Server.
- The amount of data redundancy in-store data can be reduced.
- Store data can be shared by single or multiple users.
- Data abstraction and independence & Data Security
- The ability to swiftly recover from crashes and errors, including restorability and recoverability
- Simple access using a standard application programming interface (API)
- Uniform administration procedures for data
Question 4.
What is Normalization Explain?
Answer:
Normalization is a process that helps analysts or database designers to design table structures for an application. The focus of normalization is to attempt to reduce redundant table data to the very minimum. Through the normalization process, the collection of data in a single table is replaced, by the same data being distributed over multiple tables with a specific relationship being set up between the tables.
First Normal Form (INF) :
A relation is in INF if it contains an atomic value.
Ex: Sample Employee table, it displays employees are working with multiple departments.
| Employee |
Age |
Department |
| Melvin |
30 |
Marketing |
| Melvin |
30 |
Sales |
| Edward |
40 |
Quality Assurance |
| Melvin |
30 |
Marketing, Sales |
| Edward |
40 |
Quality Assurance |
| Jay |
35 |
Human Resource |
| Jay |
35 |
Human Resource |
Second Norma! Form (2nd NF):
A relation will be in 2NF if it is in INF and all non-key attributes are fully functional dependent on the primary key
Ex: The entity should be considered already in INF, and all attributes within the entity should depend solely on the unique identifier of the entity.
Sample Products table:
| Product ID |
Product |
Brand |
| 1 |
Monitor |
Apple |
| 2 |
Monitor |
Acer |
| 3 |
Scanner |
HP |
| 4 |
Head Phone |
Nokia |
product table following 2NF:
products Category table:
Brand table:
| Product ID |
Product |
| 1 |
Monitor |
| 2 |
Scanner |
| 3 |
Head Phone |
| BrandID |
Brand |
| 1 |
Apple |
| 2 |
Acer |
| 3 |
HP |
| 4 |
Nokia |
Product Brand table:
| Pb ID |
Product ID |
Brand ID |
| 1 |
1 |
1 |
| 2 |
1 |
2 |
| 3 |
2 |
3 |
| 4 |
3 |
4 |
Third Normal Form(NF3): A relation will be in 3NF if it is in 2NF and no transition dependency exists. The entity should be considered already in 2NF, and no column entry should be dependent on any other entry (value) other than the key for the table. If such an entity exists, move it outside into a new table. 3NF is achieved considered as the database is normalized. The BCNF, 3NF, and all tables in the database should be only one primary key.
Fourth Normal form(NF4): Tables cannot have multi-valued dependencies on a Primary Key.
Fifth Normal Form(NF5): A relation is in 5NF if it is in 4NF and not contains any join dependency and joining should be lossless.
Question 5.
What is the difference between a duplicate table and a cloning/coping table in SQL Server?
Answer:
Cloning may be a situation when you just want to create an exact copy or clone of an existing table to test or perform something without affecting the original table. The data copy returns a database with the structure and datable. Ex:
Datatable dt_copy = new Datatable();
Dt.Table name = “Copy table”;
dt_copy = it.copy( );
The clone will copy the structure of a data whereas Copy will copy the complete structure as well as data.
You can duplicate an existing table in SQL Server 2017 by using SQL
Server Management Studio or Transact-SQL by creating a new table
and then copying column information from an existing table.
Question 6.
What is the difference between “•Between” Operator & “IN” Operator in SQL Server?
Answer:
The BETWEEN operator selects a range of data between two values can be number text (Range) etc. Ex: Select * from abx where marks between 50 and 80;
The IN operator allows you to specify multi values.
Ex. Select * from abd where marks EM (89,82);
Question 7.
What is the difference between Temp Table & Table variable in SQL Server?
Answer:
| SL |
Temp Table |
Table Variable |
| 1 |
The temp table is created in tempdb database |
Table variable (@table) is crated in the memory. |
| 2 |
The temp table is shown in a table variable. |
Table variable can’t involve transaction logging or locking. |
| 3 |
Temp table allows creating Index |
Table variable can’t allow Index. |
| 4 |
Temp table can’t pass SP & function |
Table variable can pass SP & function. |
| 5 |
Temporary tables are visible in the created routine and also in the child routines. |
Table variables are only visible in the created routine |
Question 8.
How to bulk data upload in SQL Server types of process Explain?
Answer:
BCP Utility: A command-line utility (Bcp.exe) that bulk exports and bulk imports data and generates format files.
BULK INSERT Statement: A Transact-SQL statement that imports data directly from a data file into a database table or no partitioned view.
SQL Server Import & Expert Wizard: The wizard creates simple packages that import and export data between many popular data formats including databases, spreadsheets, and text files.
Question 9.
Difference between function & Store procedure in SQL Server.
Answer:
| Store Procedure |
Function |
| SP can return Zero, single or multiple values. |
The function must return a single value
(were maybe a scalar or a table). |
| SP can use input & output parameters both |
Function can’t have input parameter. |
| SP can’t call function. |
The function can call SP. |
| SP is DML command (Insert, update & delete) |
Function is DML command only single value. |
| SP can’t use where and having statement. |
The function can use Where/Select & having statement. |
| We can use exception handling try & catch statement in SP |
Function can’t use try-catch statement. |
| We can use transactions in SP. |
Function can’t use transaction. |
Question 10.
What is the SOLID Principle?
Answer:
SOLID are five basic principles that help to create good software architecture. SOLID is an acronym where: –
- S stands for SRP (Single responsibility principle
- O stands for OCP (Open closed principle)
- L stands for LSP (Liskov substitution principle)
- I stand for ISP (Interface segregation principle)
- D stands for DIP (Dependency inversion principle)
Question 11.
What is Transaction in SQL Server?
Answer:
A Transaction is a unit of work that is perforated against a database. A transaction is the propagation of one or more changes to the database example if you are creating records, delete, changes, and update from the table you are performing transactions.
Transaction control:
- Commit: to save the changes
- Rollback: to roll back the changes.
- Save Point: creates points within the group of transactions in which to roll back.
- Set Transaction: Place a name on a transaction.
Question 12.
Write different between Cluster Index & Non-Cluster Index?
Answer:
Cluster index defines the order in which data is physically stored in a table data can be sorted in only one way, therefore, they can be only one cluster index per table. The primary key constraint automatically creates a clustered index on a particular column.
Non-Cluster Index doesn’t sort the physical data inside the table. Non cluster index is sorted at one place and table data is sorted in another place. Like your textbook content is located in one place and index is located in another place. This allows for more than one non-cluster index per table. The non-cluster index is slow the clustered index.
Ex: create nonclustered index ABC Name ON Student (name ASC)
It’s useful for improved query performance & searching data in SQL Server.
Question 13.
Different between Having & Where in SQL Server?
Answer:
The Where is coming before Group by where having is used to check condition after the aggression task. Where is used for filtering row and it applies enhanced every row while having used grouping filter.
Question 14.
What is common table expression(CTE)?
Answer:
A Temporary name result set knows as a common table expression(CTE). The simple query and defined within the execution scope of a single select, Insert, Update, Delete or Merge statement.
Ex: Syntax
With Expression Name (Column 1, Column2)
(
CTE query definition
)
Select column form expression name.
Advantage of CTE:
- Can be used to create a recursive query.
- Can be substituted for a view.
- Can reference itself multiple Items.
Question 15.
The difference between Primary Key, Unique Key, Foreign Key?
Answer:
| Primary Key |
Unique Key |
Foreign Key |
| Primary Key can’t have a null value |
Unique Key may have a null value. |
A foreign key is a field in the table that is the primary key in another table. |
| Each table has one primary key. |
Each table has more than one unique key. |
A foreign key can accept multiple null values. |
| By default primary key is the clustered index. |
By default, the Unique key is a nonclustered index. |
Foreign key doesn’t create cluster or non-cluster index. |
| The support Auto Incremental value. |
They don’t support |
We can have more than one foreign key in the table. |
Question 16.
How to Query optimize in SQL Server?
Answer:
The following method of SQL Query Optimize is given below:
• Do not use the * operator in your SELECT statements. Instead, use column names, because SQL Server scans for all column names and replaces the * with all the column names of the table)s) in the SQL SELECT statement.
• Do not use NOT IN when comparing with Nullable columns. Use NOT EXISTS instead.
• Do not use table variables in joins. Use temporary tables, CTEs (Common Table Expressions), or derived tables in joins instead. Even though table variables are very fast and efficient in a lot of situations, the SQL Server engine sees it as a single row. Due to this, they perform horribly when used in joins. CTEs and derived tables perform better with joins compared to table variables.
• Do not begin your stored procedure’s name with sp_. Because the stored procedure is named sp or SP, SQL Server always checks in the system/master database even if the Owner/Schema name is provided.
• Avoid using a wildcard (%) at the beginning of a predicate. The predicate LIKE ‘%abc’ causes a full table scan, For example: Select from table 1 where coll like ‘%likeabc’
• Use inner join, instead of outer join if possible. The outer join should only be used if it is necessary. Using outer join limits the database optimization options which typically results in slower SQL execution
• Avoid using GROUP BY, ORDER BY, and DISTINCT as much as possible. When using GROUP BY, ORDER BY, or DISTINCT, the SQL Server engine creates a work table and puts the data on the work table. After that, it organizes this data in the work table as requested by the query, and then it returns the final result.
• Use SET NOCOUNT ON with DML operations. When performing DML operations (i.e. INSERT, DELETE, SELECT, and UPDATE), SQL Server always returns the number of rows affected. In complex queries with a lot of joins, this becomes a huge performance issue.
Question 17.
What is Identity? How many types of Identity are in SQL Server?
Answer:
An Identity column of a table is a column whose value increases automatically. The value in an identity column is created by the server. A user generally can’t insert a value into an identity column.
Syntax: Identity [(Seed, Increment)]
Seed – Starting value of column default value is 1.
Increment – It’s the Increment value that is added to the identity value of the previous row that was loaded. The default value is 1.
- Identity Inserted ON – Allow a user to insert data into an identity column.
- Identity Inserted OFF – redirect a user from inserting data into and Identify column.
Question 18.
What are Triggers in SQL? Explain the type of triggers.
Answer:
A Trigger is a special kind of store procedure that automatically executes when an event occurs in the database server. DML (Data Manipulation Language) Trigger execute when a user tries to modify data through (DML) event Insert, Update or delete.
DDL Triggers: SQL Server we can create triggers on DDL statements (like CREATE, ALTER, and DROP) and certain system-defined stored procedures that perform DDL-like operations.
DML Triggers: SQL Server we can create triggers on DML statements (like INSERT, UPDATE, and DELETE) and stored procedures that perform DML-like operations. DML Triggers are of two types. After Trigger (using FOR/AFTER CLAUSE) & Instead of Trigger (using INSTEAD OF CLAUSE)
CLR Triggers: CLR triggers are a special type of trigger based on the CLR (Common Language Runtime) in the .net framework. CLR integration of triggers has been introduced with SQL Server 2008 and allows for triggers to be coded in one of.NET language like C#, Visual Basic, and F#.
Login Triggers: Logon triggers are a special type of trigger that fires when the LOGON event of SQL Server is raised. This event is raised when a user session is being established with SQL Server that is made after the authentication phase finishes, but before the user session is actually established.
Example of Insert Trigger:
Create trigger abc on [dbo]. [mytable]
For INSERT As
Declare @ ID int,
Declare @Name varchare(50);
Declate @Audit_Action(50);
Select @ID = j.id from Inserted j;
Select @Name = j.name from Inserted j;
Select @Audit_Action = ‘Inserted Record after triggers’;
Insert into xyzBackupfile (Id,Name,Audit Action,Audit time)
values(@id,@name,@audit_action,GetDate());
Print ‘Trigger Inserted sucessfully’;
Question 19.
What is Scaler function in SQL Server?
Answer:
SQL Server scalar function takes one or more parameters and returns a single value. The scalar functions help you simplify your code. For example, you may have a complex calculation that appears in many queries. Instead of including the formula in every query, you can create a scalar function that encapsulates the formula and uses it in the queries.\
Ex:
CREATE FUNCTION [schema_name.]function_name (parameter_list)
RETURNS data type AS
BEGIN
statements
RETURN value
END
Question 20.
What is the difference between the # & @ table in SQL Server?
Answer:
# table refers to a local variable (visible to only users who created it) temporary table.
## table refers to a global temporary table (display all users).
@ variable name refer to a variable that can hold value depending on its size.
Question 21.
What is function Explain?
Answer:
A function is a database object in SQL Server. Basically, it is a set of SQL statements that accept only input parameters, perform actions and return the result. A function can return only a single value or a table. We can’t use a function to Insert, Update, Delete records in the database tables.
System Defined Function
Scaler function: Scalar functions operates on a single value and return a single value.
Example: rand, round, upper, lower, trim, convert
Aggregation function: SQL Server aggregate functions perform a calculation on a set of values and return a single value. With the exception of the COUNT aggregate function, all other aggregate functions ignore NULL values.
Example :
- min( ),
- max( ),
- ave( ),
- Count( ).
User Define function: These functions are created by the user in the system database or in the user-defined database. There are 3 types of user define function scaler function, table-valued function & System
Question 22.
What is SQL Injection?
Answer:
SQL Injection is a code injection technique that might destroy your database. SQL Injection is one of the most common web hacking techniques. SQL injection is the placement of malicious code in SQL statements, via web page input.
Question 23.
What are Constraints in SQL Server explain?
Answer:
The Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted. Constraints can be column level or table level. Column level constraints apply to a column, and table-level constraints apply to the whole table.
Example:
- NOT NULL – Ensures that a column cannot have a NULL value
- UNIQUE – Ensures that all values in a column are different
- PRIMARY KEY – A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
- FOREIGN KEY – Uniquely identifies a row/record in another table
- CHECK – Ensures that all values in a column satisfy a specific condition
- DEFAULT – Sets a default value for a column when no value is specified
- INDEX – Used to create and retrieve data from the database very quickly.
Question 24.
What is Rollback in SQL Server?
Answer:
We can undo a single or even multiple consecutive written and delete operations. the command used rollback in SQL server.
Ex:
SQL> DELETE FROM CUSTOMERS WHERE AGE = 25;
SQL> COMMIT;
SQL> Rollback;
SQL> select * from customer;
Question 25.
The difference between delete & truncate?
Answer:
| SL |
Truncate |
Delete |
| 1 |
TRUNCATE is a DDL command |
DELETE is a DML command |
| 2 |
TRUNCATE is executed using a table lock and the whole table is locked to remove all records. |
DELETE is executed using a row lock, each row in the table is locked for deletion. |
| 3 |
We cannot use the Where clause with TRUNCATE. |
We can use the where clause with DELETE to filter & delete specific records. |
| 4 |
TRUNCATE removes all rows from a table. |
The DELETE command is used to remove rows from a table based on the WHERE conditions. |
| 5 |
Minimal logging in the transaction log, so it is performance-wise faster. |
It maintains the log, so it is slower than truncates. |
| 6 |
To use Truncate on a table you need at least ALTER permission on the table. |
To use Delete you need DELETE permission on the table. |
| 7 |
Truncate uses less transaction space than Delete statement. |
Delete uses more transaction space than a Truncate statement. |
| 8 |
Truncate cannot be used with an indexed view |
The delete can be used with indexed views |
Question 26.
What is PIVOT & UNPIVOT Explain?
Answer:
The PIVOT and UNPIVOT relational operators change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output. PIVOT runs aggregations, where they’re required on any remaining column values that are wanted in the final output UNPIVOT, carries out the opposite operation to PIVOT by rotating columns of a table-valued expression into column values in SQL Server.
Question 27.
What is Join Explain?
Answer:
The SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.
There is 4 type of joins
- Inner Join,
- Outer Joins,
- Cross Joins.
1) Inner Join: Returns records that have matching values in both tables.
Syntax:SELECT column name(s)¥RON[ ta/)/e/INNER JOIN table2 ON table 1 .column jiame = table2.column _name\
2) Outer Join:
• Left Outer Join: Returns all records from the left table, and the matched records from the right table
Syntax.SELECT column_name(s)F7?0M table 1 LEFT JOIN table2
ON table 1 .column_name = table2.column_name;
• Right Outer Join: Returns all records from the right table, and the matched records from the left table
Syntax.SELECT column_name(s)F/?6W table 1 RIGHT JOIN table2
ON table 1 .column jiame = table2.column jiame;
• Full outer Join: Returns all records when there is a match in either the left or the right table.
Syntax:SELECT column_name(s)Fi?OM table IFULL OUTER JOIN la. ble2
ON table l.columnname = table2. column jiame WHERE condition;
3) Cross Join (Cartesian Product): A result set which is the number of rows in the first table multiplied by the number of rows in the second table if nowhere clause is used along with cross join.
Syntax: Select * from table 1 corss join table2;
Question 28.
How can SQL Injection be stopped?
Answer:
The following process of SQL Injection prevention is given below:
- Validate the SQL commands that are being passed by the front end.
- Validate the length and data type per parameter.
- Convert dynamic SQL to stored procedures with parameters.
- Prevent any commands from executing with the combination of or all of the following commands: semi-colon, EXEC, CAST, SET, two dashes, apostrophe, etc.
- Based on your front end programming language determine what special characters should be removed before any commands are passed to SQL Server
- Depending on the language this could be a semi-colon, dashes, apostrophes, etc.
- Prevent traffic from particular IP addresses or domains & See if email-based alerts can be sent if traffic comes from these sources
- Review the firewall settings to determine if SQL Injection attacks can be prevented
Question 29.
What do Database Encryption and Decryption mean?
Answer:
Database encryption is the process of converting data, within a database, in plain text format into a meaningless cipher text by means of a suitable algorithm.
Database decryption is converting the meaningless ciphertext into the original information using keys generated by the encryption algorithms.
Question 30.
What is a Symmetric-key in the Data encryption process?
Answer:
In the Symmetric cryptography system, the sender and the receiver of a message share a single, common key that is used to encrypt and decrypt the message. This is relatively easy to implement, and both the sender and the receiver can encrypt or decrypt the messages.
Question 31.
What key provides the strongest encryption in SQL Server DBA?
Answer:
Used AES (256 bit)
- If we choose a longer key, then encryption will be better, so choose longer keys for more encryption. However, there is a larger performance penalty for longer keys. DES is a relatively old and weaker algorithm than AES.
- AES: Advanced Encryption Standard
- DES: Data Encryption Standard
Question 32.
How to find the 2nd Highest salary in the table?
Answer:
Select distinct salary from emp el where 2 = select count(distinct salary) from emp e2 where el.salary=e2.salary;
Or
(DEN SE_RANK Method)
Select a.name, s.salary from (select name, salary, DENSE RANK()
over (ORDER BY Salary) As RK from personal) as a where rk = 3;
Question 33.
How to delete all duplicate records in SQL Server?
Answer:
Delete from emp where id in (select id emp GroupBy e id having count (*) >1); ‘
Question 34.
How to update Male = Female & Female = Male in SQL Server;
Answer:
update emp set gender = case gender were ‘male’ then ‘female’ when ‘female’ than ‘male’ else gender END;
Question 35.
How do find the last records from the table?
Answer:
Select * from emp where id = select max (id) from emp;
Question 36.
How do find 1st five records from the table?
Answer:
Select * from emp where ROWNUM <= 5;
Question 37.
How do find the last 5 records from the table?
Answer:
Select * from e where ROWNUM <=5 union select * from (select * from emp e Order by Desc.) where ROWNUM <=5;
Question 38.
How do find the 3 highest salaries from the table?
Answer:
Select distinct salary from emp el where 3>= select counts (distinct salary) from emp2 where el. salary <= e2. salary order by el. salary disc;
Question 39.
How to create a clone table in SQL Server?
Answer:
create table ABC as select * from emp where 1=2;
Question 40.
How to delete all duplicate records from the table?
Answer:
Delete from emp where id in select id emp Group by E id having count (*)> 1;
Question 41.
How do find out the maximum salary of each department?
Answer:
Select Deptid Max(Salary) from emp Group By Deptid;
Question 42.
How do I fetch only common records between 2 tables?
Answer:
Select from emp Intersect select from empl;
Question 43.
Find out monthly salary from the table?
Answer:
Select emp name salary/12 as ‘monthly’ from EMP;
Question 44.
Write benefits of Store Procedure with Example?
Answer:
The following benefit as given below:
- Reduced amount of information sent to the database server.
- Compilation step is required only once when the store procedure used means one-time compilation required.
- Re-Usability of code means multiple clients can have used the same code.
- Helpful for enhancing the system security since we can grant permission to the users for executing the store procedure.
- Helpful for your business logic.
- Save time for coding.
Example:
/* This store procedure is used to insert a value into the table “Mapa” */
Create Procedure record
(
@ Name varchar (20),
@Age int,
@Sex char (1)
)
As
Begin
Insert into Mapa(Name,Age,Sex) Values(@Name,@Age,@Sex)
END
Question 45.
Write Example of Index with Syntax?
Answer:
Syntax of Index:
CREATE INDEX Index name ON Table Name (Column1,Column2….);
Example My Table name is Emp:
CREATE INDEX Salarylndex ON Emp (Salary, Age);
Question 46.
Write Cluster Index Example?
Answer:
Example Table Name is EmpRecords:
CREATE TABLE [EmpRecord]
(
[EmpKey] [int] NOT NULL PRIMARY KEY,
[Name] [varchar](50) NOT NULL ,
[Email] [nvarchar](50) NULL,
[Profession] [nvarchar]( 100) NULL,
[Yearly Income] [money] NULL
)
Details you can check by using: EXECUTE SP_HELP EmpRecord
Question 47.
Write a Syntax of Constraint in SQL Server with Example?
Answer:
Constraint “Constraint Name” “Constraint definition” Example:
- Unique Constraint: “column name” datatype<size> unique
- Not null Constraint: “column name” datatype<size> not null
- Check Constraint: “column name” datatype<size> checkdogical expression>
- Default Constraint: “column name” datatype<size> Default <value> constraint definition.
Question 48.
Date function in SQL Server, write a Query to Add after the 4-month date of March 2019?
Answer:
Select ADD_Month(SYSDATE,4) “Next Month” from XYZ;
Output: Next Month: 01-Aug-19
Question 49.
Calculate last day in month SQL Server?
Answer:
Select SYSDATE, LAST DAY(SYSDATE) “NewDay” from XYZ;
Output: SYSDATE:01-Jul-19 & NewDay:31-Jul-19.
Question 50.
Calculate the Month between (Ol-May-19 to 30-Oct-19) in SQL server?
Answer:
Select Months Between (’01-MAY-19′ , ’30-OCT-19′)”Total Month ” from ABC.
Question 51.
in SQL Server?
Answer:
Select Months Between(’01-MAY-19’,’30-OCT-19’) “Total Month” from ABC;
Output: Total Month: 6
Question 52.
How to calculate Next day in SQL Server in Date function?
Answer:
Select NEXT_DAY (’14-Oct-19’,’ Monday’) “Next Week” from Apple;
Output: Next Week: 21-Oct-19
Question 53.
Write a Syntax of time Zone? Answer:New_Time(date, Zonal,zone2)
Example:
SELECT EID, EMPJoiningDate,
EMPJoiningDate AT TIME ZONE ‘Pacific Standard Time’ AS
EMPJoiningDate TimeZonePST
FROM EMP.EMPEnterHeader;
Question 54.
What are used Rollup Operators?
Answer:
The Rollup operator is used to calculate aggregates and super aggregates expression in the group by statement. The Rollup option allows you to include extra rows that represent the subtotals, which are commonly referred to as super-aggregate rows, along with the grand total row. ROLLUP (Ml, M2, M3) creates only four grouping sets, assuming the hierarchy Ml > M2 > M3, as follows
Syntax: Select Ml,M2,M3 aggregate function(p4) from tablename GROUP BY ROLLUP (Ml,M2,M3);
Question 55.
What is Sub Query Explain with Example?
Answer:
The Subquery is a query set inside of another SQL Statement. It’s a nested query inside select, insert, update and delete statements. A subquery can be used anywhere an expression is allowed. We can also use where & having condition. The DISTINCT keyword & COMPUTE and INTO clauses cannot be used with subqueries that include GROUP BY.
Example:
Select * from SalDetail
where sal_add IN(select dept_add from dept where dept name – finance’)
Question 56.
Write Query of Not Exists in SQL Server?
Answer:
SELECT StudentID, ProductName FROM Student p WHERE NOT EXISTS (SELECT * FROM [Student Details] od WHERE p.Studentld = od.Studentld)
Question 57.
Write a triggered update & delete in SQL Server?
Answer:
Update after Trigger Example as given below:
Create Trigger abc as on [dbo].[mytable]
For UPDATE As
Begin
Declare @id int;
Declare @name varchar (20);
Declare @Audit_Time (60);
Select @id = j.Id from Inserted j;
Select @Name = j.Name from Inserted j;
Select @Audit_Time = Updated Record – After Updated Trigger;
Update into MPGbackup (id,Name.Audit Time) values(@id,@Name, @Audit_time ,GetDate());
Print ‘Trigger Updated Successfully’
–Acb is table name, MPGbackup is backup file name
Example of Delete after Trigger as given below:
Delete Trigger abc as on [dbo].[mytable]
For UPDATE As
Begin
Declare @id int;
Declare @name varchar (20);
Declare @Audit_Time (60);
Select @id = j.Id from deleted j;
Select @Name = j.Name from deleted j;
Select @Audit_Time = Record Deleted – After Updated Trigger;
Insert into MPGbackup (id,Name.Audit_Time) values(@id,@Name,@Audit_time,GetDate());
Print ‘Trigger Deleted Successfully’
– -Acb is table name, MPGbackup is backup file name
Question 58.
Write Case Statement in SQL Server with Example?
Answer:Syntax:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN condition3 THEN result3
WHEN condition4 THEN result4
WHEN condition THEN resultN
ELSE result
END;
Example:
SELECT EmpID, EmpAge,
CASE WHEN EmpAge > 40 THEN “The employee is greater than 30”
WHEN EmpAge = 30 THEN “The Age is 30”
ELSE “The Age is under 30”
END AS EmpText FROM EmployeeDetails; ‘
26) Give me Example of a Function in SQL Server?
Answer:
Example 1: User Define a function
CREATE FUNCTION dbo. Stock1(@ItemID int)
RETURNS int AS
— Returns the Store Inventory level for the product.
BEGIN
DECLARE @ret int;
SELECT @ret = SUM(r.Quantity)
FROM Production.Productlnventory r WHERE r. ItemID = @ItemID AND r.Location® = ‘6’;
IF (@ret IS NULL)
SET @ret = 0;
RETURN @ret;
END;
Example 2: The Example of Scaler Function
SELECT student id, SUM(sales.udfNetSale(quantity, list_price, dis count))
net amount FROM sales.orderitems
GROUP BY order_id
ORDER BY net_amount DESC;
Question 59.
Find 3rd Highest salary using Rank Method?
Answer:
Select * from (Select Dense Rank() over ( order by salaryl desc)
as Rnk,E.* from Employee E) where Rnk=3;
Question 60.
How to get a Distinct Record without using distinct keywords?
Answer:
Select * from Student a where rowid = (select max(rowid) from
Student b where a.Student_ID =b. Student_ID);
Question 61.
How to remove duplicate rows from the table?
Answer:
Select EID FROM Employee WHERE ROWID <>
(Select max (rowid) from Employee b where EID=b.EID);
Delete duplicate rows:
Delete FROM Employee WHERE ROWID
(Select max (rowid) from Employee b where EID =b. EID);
Question 62.
How to fetch duplicate records from the table?
Answer:
SELECT Empld, Name, Salary, COUNT(*)
FROM EmpSalary
GROUP BY Empld, Name, Salary
HAVING COUNT(*)> 1;
Question 63.
Write a SQL query to fetch top n records?
Answer:
Select Top N * from Manager Order By Salary DESC;
Question 64.
What is Cursor, Explain?
Answer:
The SQL Server Query basically works on a complete set like Select Statement & as per Where condition. Sometimes you may want to process a data set on a row by row basis. This is where cursors come into play. Here we can use cursors.
Type of cursor in SQL Server:
- Forward Only
- Static
- Keyset
- Dynamic
Syntax:
DECLARE cursor name CURSOR [ LOCAL | GLOBAL ] [ FORWARD ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | LAST LORWARD]
[ READ-ONLY | SCROLL_LOCKS | OPTIMISTIC]
[ TYPEWARNING ] FOR select statement
FOR UPDATE [coll,col2,…coln]
—define columns that need to be updated
Question 65.
What is Collection in SQL Server?
Answer:
According to Microsoft Definition “A collection is a list of objects that have been constructed from the same object class and that share the same parent object. The collection object always contains the name of the object type with the Collection suffix. Lor example, to access the columns in a specified table, use the ColumnCollection object type.”
Question 66.
Explain the locks in SQL Server and type of locks?
Answer:
The Lock is used for security purposes and to prevent data from being corrupted by the database. The locks are used to implement concurrency control when multiple users access the Database to manipulate its data at the same time.
Lock Mode as given below:
• Shared(S) – The Shared Mode is used for reading operations that do not change or update data, such as a SELECT statement.
• Exclusive(X) – The Exclusive Mode used for data- modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.
• Intent – The Intent used to establish a lock hierarchy. The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SEX).
• Bulk Update – We can have updated bulk data lock mode Tab Lock.
Question 67.
Write a Query of Bulk(BCP) data uploaded in SQL Server?
Answer:
create table Customer (id int,
name varchar(60),
Age int,
email varchar(60))
Now we will load the data using the BULK INSERT statement & We have CVS file with bulk data:
BULK
INSERT Customer
FROM 'c:\sql\mycustomers.CSV
WITH
(
FIELDTERMINATOR = ROWTERMINATOR = '\n'
)
Question 68.
Query Data Received from server Syntax with getting Method Example?
Answer:
<!DOCTYPE html>
<html>
<head>
<script
src="https ://aj ax. googleapis. com/aj ax/libs/j query/3.4.1/j query. mi
n.j s'"></script><script>
$(document).ready(function() {
$("button").click(function() {
$.get("demo_test2.asp", fimction(data, status){
alert("Data: " + data+ "YnStatus: " + status);
}); '
});
});
</script>
</head>
<body>
<button>Send an HTTP GET Method</button>
</body>
</html>
Question
Question 69.
JQuery Method Post Method data in SQL Server Example?
Answer:
<!DOCTYPE html>
<html>
<head>
<script
src= "https ://aj ax.googleapis. com/aj ax/libs/j query/3.4.1/j query .mi n.js"></script>
<script> .
$(document).ready(function() {
$('"button"). click(function() {
$ .post(" demotest 1 _post.asp",
{
name: "Tom",
city: "Rome"
},
function(data,status){
alert("Data: " + data + "\nStatus: " + status);
});
});
});
</script>
</head>
<body>
<button>Send an HTTP POST Method</button>
</body>
</html>
Question 70.
Write a JQuery Load Data Method?
Answer:
Just add the code in JQuery as per your txt file name.
<script>
$(document).ready(function() {
$("button").click(function() {
$( "#di v 1"). load(" demo_test.txt");
});
});
</script>
Question 71.
Write a Simple code for Ajax Data Request from Serve, Example?
Answer:
The Code as given below for changes in Script File.
<script>
$(document).ready(function() {
$("button").click(function() {
$("#divl ").load("demo_testl .txt");
});
});
</script>
Question 72.
Write List Method in LINQ in MVC?
Answer:
public static void Main( )
{
// string collection
IList<string> stringList = new List<string>() { "Dot NET",
"MVC",
"SQL",
"AJAX",
"JQuery"
// LESTQ Query Syntax
var result = from s in stringList
where s.Contains("Interview2") select s;
foreach (var str in result)
{
Console. WriteLine(str);
}
}
Question 73.
Write Group by Query in LINQ Example?
Answer:
string[ ] groupingQuery = {“Banana”, “Apple”, “Grape”, “beans”, “Mango” }; ‘
IEnumerable<IGrouping<char, string» queryFoodGroups = from item in groupingQuery
group item by item[0];
Question 74.
Write an Inner Join Example in LINQ MVC?
Answer:
public static void Main( )
{
// Color collection in table IList<string> strListl = new List<string>() { "Red",
"Yellow",
"Green",
"Blue"
};
IList<string> strList2 = new List<string>() { "Red",
"Yellow",
"Orange",
"Black"
};
var innerJoinResult = strListl Join(// outer sequence
strList2, // inner sequence
str1 => strl, // outerKeySelector
str2 => str2, // innerKeySelector
(strl, str2) => strl);
foreach (var str in innerJoinResult)
{
Console.WriteLine("{0} ", str);
}
}
}
Output: Red, Yellow
Question 75.
Write a Query to skip method in LINQ with the top 4 highest value skip?
Answer:
The LINQ Example is given below:
int[ ] grades = { 58, 80, 68, 40, 100, 200, 150,250 };
IEnumerable<int> lowerGrades =
grades.OrderByDescending(g => g).Skip(4);
Console.WriteLine("All grades except the top three are:");
foreach (int grade in lowerGrades)
{
Console.WriteLine(grade);
}
/*
Output:
All grades except the top three are:
80
68
58
40
*/ ‘
Question 76.
The difference between FirstOrDefault and First in LINQ?
Answer:
The first return First element value from the collection of elements or a sequence. It also returns the first element of sequence when you specified the condition. The First ( ) simply gives you the first one.
The default not showing the first value it’s showing the default value set in LINQ Operation. The default return is null when there is no result, it will not throw an exception.
Question 77.
Write an Anonymous Function Example? Answer: The following example of anonymous:
delegate int func(int r, int s);
static void Main(string[ ] args)
{
func f1= delegate(int r, int s)
{
return r + s;
};
Console.WriteLine(fl(l, 2));
}
Question 78.
How can we achieve Ajax method call using MVC?
Answer:
The Example as given below:
$.ajax({type: ‘POST’,url: ’@Url.Action(“TestLogic”, “MySearch”)’
,data: {listQuize },success: function (response)
{window.location.href = urldata;if (response != null)
{alert(“Search Working”);}
else {alert(“Search Not Working);}},
error: function (response) {}});
Question 79.
Give me Example of Lambda Expression in MVC?
Answer:
delegate int func(int m, int n);
static void Main(string[] args)
{
func fl = (m, n) => m + n;
Console.WriteLine(fl(l, 2));
}
Question 80.
How to find out duplicate Array in the list?
Answer:
Example:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace CodingAlgorithms
{
//Given an array of integers, find if the array contains any duplicates.
//This function should return true if any value appears at least twice in the array, and it should return false if every element is distinct.
public static class ArrayDuplicates1
{
//Dictionary solution
public static bool ContainsDuplicates(params int[ ] z)
{
Dictionary<int, int> d = new Dictionary<int, int>( );
foreach (int i in z)
{
if (d.ContainsKey(i))
return true;
else
d.Add(i, 1);
}
return false;
}
}
}
Question 81.
Write a Program to Rotate array to the right given a pivot?
Answer:
Example:
using System;
using System.Collections.Generic;
using System. Linq;
using System. Text;
using System.Threading.Tasks;
namespace CodingAlgorithm
{
public static class RotateArrayRight
{
//Rotate array to the right of a given pivot
public static int[] Rotate(int[] y, int pivot)
{
if (pivot < 0 || y == null)
throw new Exception ("Invalid argument");
pivot = pivot % y.Length;
//Rotate first half
y = RotateSub(y, 0, pivot - 1);
//Rotate second half
y = RotateSub(y, pivot, x.Length - 1);
//Rotate all
y = RotateSub(y, 0, y.Length - 1);
return y;
}
private static int[ ] RotateSub(int[ ] y, int start, int end)
{
while (start < end)
{
int temp = y[start];
y[start] = y[end];
y[end] = temp;
start++;
end - ;
}
return x;
}
}
}
Question 82.
How to remove duplicates from the sorted linked lists?
Answer:
Example:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace LinkedListAlgorithm
{
partial class LinkedList1
{
//Remove duplicates from a sorted linked list (Assumes nodes contain integers as data)
//Source: https://leetcode.com/problems/remove- duplicates-from-sorted-list/
public void RemoveDuplicates( )
{
Node lag = head;
Node lead = head.Next; ?
while (lead != null)
{
if ((int)lag.Data == (int)lead.Data)
{
lag.Next = lead.Next;
lead = lag .Next;
}
else
{
lag = lag.Next;
lead = lead.Next;
}
}
}
}
}
Question 83.
What is next Number 5,10,19,32,49,70….?
Answer:
The Solution is given below in Bold:
10-5 = 5
19-10 = 9
32-19=13
49-32= 17
70-49=21
?- 70= 25
Question 84.
A trader buys tea for $1200 and sells it for $1500, per sack of tea he makes a profit of $50. How many sacks of tea did he have?
Answer:
Solution:
He might have 6 sacks of sugar,
$1500-$1200 = 300
300/50 = 6
Question 85.
Now we have a committee of 10 men, where the age of all 10 men is the same as it was 4 years ago because an old man is replaced by a young man? Find out how much younger is the new men?
Answer:
Let say the sum of nine men = p and age of old man = R
So its average 4 years before = (p+r)/10
After 4 years let R be replaced by Y
So now avg=(p+4x 10+y)/10
Now, (p+r)/10 = (p+40+y)/10
So in the end you will get z=y+40
So young committee member is 40 years younger than old member
Question 86.
Find the next Series F21, S23, T25, T27, S29, M31,____?
Answer:
Solution: W02
F21: Friday the 21 st.
S23: Sunday the 23rd.
T25: Tuesday the 25th.
T27: Thursday the 27th.
S29: Saturday the 29th.
M31: Monday the 31 st.
Avoiding every other day, the next term must be ‘Wednesday the 2nd’ i.e. ‘W02’
Question 87.
You are given a 6 by 6 grid and asked to start on the top left corner. Now your aim is to get to the bottom right corner. You are only allowed to move either right or down. You must never move diagonally or backward.
Answer:
Solution: 252 Way
Think how symmetric the problem is. Pick up any random cell. Suppose that you took ‘n’ steps in reaching the particular cell above it and ‘m’ steps to reach the particular cell placed left to it. In such circumstances, the number of ways to reach the random cell is equal to the sum of two steps ‘n+m’. Thus, keep that logic in mind and fill out the six by six grid. In that manner, there are 252 ways to get to the destination that is the bottom right step.
Question 88.
You have 100 coins lying flat on a table, each with a head side and a tail side. 10 of them are heads up, 90 are tails up. You can’t feel, see or in any other way find out which side is up. Split the coins into two piles such that there is the same number of heads in each pile.
Answer:
Place 50 coins into two piles on their edges so that both have the same amount of heads in each pile, neither facing up or down.
Question 89.
There are three boxes, one contains only apples, one contains only oranges, and one contains both apples and oranges. The boxes have been incorrectly labeled such that no label identifies the actual contents of the box it labels. Opening just one box, and without looking in the box, you take out one piece of fruit. By looking at the fruit, how can you immediately label all of the boxes correctly?
Answer:
Let’s assume:
box 1 is labeled Oranges (O)
box 2 is labeled Apples (A)
box 3 is labeled Apples and Oranges (A+O)
and that ALL THREE BOXES ARE LABELLED
INCORRECTLY”
If you are picking fruit from box 1
if you pick an Orange:
- box l’s real label can only be O or A+O
- box 1 ‘s current label is O
- since ALL LABELS ARE INCORRECT then box l’s real label can not be O
- box l’s new label should then be A+O by elimination
- since ALL LABELS ARE INCORRECT
- box 2’s label is changed to O
- box 3’s label is changed to A
Question 90.
If you’re given a Mug/Gallon with a mix of fair and unfair coins, and you pull one out and flip it 3 times, and get the specific sequence heads tails, what are the chances that you pulled out a fair or an unfair coin?
Answer:
If you don’t know how unfair the coins are then you have to compare fair vs. not fair. A fair coin Pf = 1/2 probability of heads or tails and therefore has a 1/(2A3) = 1/8 probability of going HHT. An unfair coin has a probability P*P*(1-P) = 2*(PA2) – (PA3) of going HHT. Integrating across P from 0 to 1, you also get 1/8. Therefore, whether the coin was biased or not, you had an even chance when you got HHT that the coin was fair or not fair (50%). However, if you were to know the distribution of the coins in the jar between fair and not fair, you would be able to estimate the probability the coin was fair based on that distribution.
Question 91.
If you have 2 eggs, and you want to figure out what’s the highest floor from which you can drop the egg without breaking it, how would you do it? What’s the optimal solution?
Answer:
you can say the answer more like, you can drop the egg even from the topmost floor without breaking it. The egg only breaks when it hits the floor.
Question 92.
The probability of a car passing a certain intersection in 20-minute windows is 0.9. What is the probability of a car passing the intersection in a 5-minute window? (Assuming a constant probability throughout)
Answer:
This is one of the basic probability questions asked in a software interview. Let’s start by creating an equation. Let x be the probability of a car passing the intersection in a 5-minute window.
Probability of a car passing in a 20 minute window = 1 – (probability of no car passing in a 20 minute window)
Probability of a car passing in a 20 minute window = 1 – (1 – probability of a car passing in a 5 minute window)A4
0.9= 1-(1-x)∧4
(1 -x)∧4 = 0.1
1 – x= 10∧(-0.25)
x = 1 – 10∧(-0.25) = 0.4377
Question 93.
There are 3 light bulbs in a hidden room and 3 switches outside the room that corresponds to those light bulbs. You do not know which switch affects which bulb and you cannot see inside of the room. You are allowed to go inside of the room only one time. How do you find out which switch corresponds to which bulb?
Answer:
Turn on two switches and wait for a while. Then turn off one switch and go inside the room. The bulb that is still on corresponds to the switch that is still on. Touch the remaining bulbs. The hotter bulb is the switch that you turned off, and the remaining bulb is the switch that you never turned on.
Question 94.
How would you cut a rectangular cake into two equal pieces when a rectangular piece has already been cut out of it? The cut piece can be of any size and orientation. You are only allowed to make one straight cut.
Answer:
Simple question right? There are two possible solutions to this problem. People often overlook the easier solution to this problem. Let’s start with the easiest solution. If you make one straight horizontal cut along with the height of the cake, the resulting slices are of equal sizes. But this solution may not work so well on a cake with icing. So let’s rethink.
Question 95.
John and Matt decide to play a game using a deck of cards. The game involves flipping two cards at a time. If both cards are black, they go into John’s pile and if both cards are red, they go into Matt’s pile. If one card is red and one card is black, they go into a discard pile. The game is played until all 52 cards have been flipped. The person with the most cards in their pile wins. If there is a tie, John wins. If Matt has more cards than John, then John pays Matt a dollar. What is the chance of Matt getting a dollar?
Answer:
Per deck is arranged in a way such that there are 13 black pairs. In this situation, John gets 13 black pairs. And since there are no other black cards left, Matt gets 13 red pairs too. So there’s a tie and John wins.
Suppose there are 12 black pairs in the deck. In this case, there would be 2 black cards left in the deck that would pair up with 2 other red cards in the deck. These 2 black cards would never be together since we have already claimed that there are only 12 black pairs in the deck. As a result, 2 pairs of cards end up in the discard pile. The remaining cards would all be red and Matt would get 12 red pairs too. So once again, there’s a tie and John wins.
We could continue this process through induction. Assuming there are 11 black pairs, there would be 4 other black cards that pair up with 4 other red cards and go into the discard pile. Once again, both John and Matt end up with 11 pairs of cards each and John wins.
Question 96.
What is the output of given data in JavaScript?
- alert (‘7’+ 7 + 7)
- alert (7 + 7)
- alert (7+ 7‘7’)
- alert (7 + ‘7’ + 7)
Answer:
In this JavaScript series ‘7’ representation of string format. Hence, result will be , String + number = string Number + Number = Number Therefore,
- alert (‘7’ + 1 + 1) = 111
- alert (7 + 7 ) = 14
- alert (7+ 7‘7’) = 147
- alert (7 + ‘7’ + 7) = 111
Question 97.
Uses of find() method .closestO method in Jquery?
Answer:
The method used for searches for descendant elements that match the specified selector. Syntax: $(selector) . find{filter)
The. the closest method used for matching element in DOM Tree or new jQuery object from the matching elements. Syntax: $(selector) . closest(filter)
Question 98.
How to Authenticated data coming from the right way by API?
Answer:
We can authenticate 3 basic approaches for our data coming from right way or wrong.
• HTTP Based Authentication: Simple authentication checking user-id & password by authorized users. we can also use OTP-based authentication.
• API Key Based Authenticate: we can also use the API key and after matching the API key we can verify authorized users or not. Most APIs require you to sign up for an API key in order to use the API. The API key is a long string that you usually include either in the request URL or request header
• HAMC based Authentication: We can crate HAMC for authentication.
Question 99.
Write a LINQ Count Query in MVC?
Answer:
IEnumerable <string> Items = new List
<string>{“3aywj”Soniya”,”Lita”,”Arohi”);
Int count = Item.Count();
// count: 3
Or
//Count between tables Example < then items.
IEnumerable <string> Items = new List <string>
{“10″,”15″,”18″,”20″,”30”}; ‘
Int Count = Items.Count (x=> x<15), // Count:3 Int Count = Items.Where (x=> x<15)Count.();
//Count:3
Question 100.
Write Select Statement Example in LINQ?
Answer:
string [ ] daysArrayDotNet =
{“Sunday”,”Monday”j”T uesday”};
Var days = from day in daysArrayDotNet select day;
//Example of Where Condition in Select Statement
Var days = from day in daysArrayDotNet where day == “Sunday”;
Question 101.
Order By Example in LINQ Select Statement?
Answer:
OrderByDecending (x =>’ x.Deliveryl.SubmissionMe);
Question 102.
Average Count Example in LINQ Statements?
Answer:
Var list = new List <int> {I,3,5,7,9};
Double result = list. Average (); //result: 5
Question 103.
Difference between Ref & Out Parameter?
Answer:
The Ref parameter means that the parameter has a value on it before going into the function. And out parameter means that the parameter has no official value before going into the function.
The ref keyword reference parameter when a passing argument in the method. While Out parameter we can pass argument in the method by out keyword.
The reference parameter or argument must be initialized first before it is passed to ref. while Out parameter is not compulsory to initialize a parameter or argument before it is passed to an out.
Example :
Out Parameter
Int m ;
Foo (out m);
Ref parameter
Int n;
Foo (ref n);
