Sql Server Exclusive Interview Questions and Answers (56) - Page 2

  • A joint initiative from DotNetFunda.Com and Questpond.
  • A one stop place on the internet to get trusted and proven Interview Questions (based on more than a decade of experience in this field) to ensure the success of the candidates.
  • You will find almost all Interview questions from Questpond here and this list is growing day by day with all latest and updated interview questions.

56 records found.

Get 650+ Questpond's Interview videos on discount

In which Files does SQL Server Actually Store Data?

Any SQL Server database is associated with two kinds of files: *.mdf and *.ldf. *.mdf files are actual physical database files where your data is stored finally. *.ldf (LOG) files are actually data, which is recorded from the last time data was committed in the database.




MDF and LDF files.


What is Collation in SQL Server?

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types, and character width.




Collation according to language


Note: - Different languages will have different sort orders.


Case Sensitivity


If A and a, B and b, etc. are treated in the same way, then it is case-insensitive. A computer treats A and a differently because it uses ASCII code to differentiate the input. The ASCII value of A is 65, while a is 97. The ASCII value of B is 66 and b is 98.


Accent Sensitivity


If a and A, o and O are treated in the same way, then it is accent-insensitive. A computer treats a and A differently because it uses ASCII code for differentiating the input. The ASCII value of a is 97 and A 225. The ASCII value of o is 111 and O is 243.


Kana Sensitivity


When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.


Width Sensitivity


When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive.


Can We Have a Different Collation for Database and Table?

Yes, you can specify different collation sequence for both the entities differently.


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? OR What is “ON DELETE CASCADE” and “ON DELETE RESTRICT”?

RESTRICT specifies that table should not be dropped if any dependencies (i.e. triggers, stored procedure, primary key, foreign key etc) exist. Therefore, if there are dependencies then error is generated and the object is not dropped.


CASCADE specifies that even if there dependencies go ahead with the drop. That means drop the dependencies first and then the main object. 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 table using “INSERT” statement?

I have made a new temporary color table which is flourished using the below SQL. Structures of both the table should be 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 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 database.
DCL (Data Control Language) controls you DML and DDL statements so that your data is protected and has consistency. COMITT and ROLLBACK are DCL control statements. DCL guarantees ACID fundamentals of a transaction.


What are different types of joins in SQL?

INNER JOIN

Inner join shows matches only when they exist in both tables. Example in the below SQL there are two tables Customers and Orders and the inner join in made on Customers.Customerid and Orders.Customerid. So this SQL will only give you result with customers who have orders. If the customer does not have order, it will not display that record.

SELECT Customers.*, Orders.* FROM Customers INNER JOIN Orders ON

Customers.CustomerID =Orders.CustomerID
LEFT OUTER JOIN

Left join will display all records in 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.CustomerID =Orders.CustomerID
RIGHT OUTER JOIN

Right join will display all records in 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 appears as NULL values. For example, you want to determine if there are any orders in the data with undefined CustomerID values (say, after a 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.CustomerID =Orders.CustomerID

What is “CROSS JOIN”? OR What is Cartesian product?

“CROSS JOIN” or “CARTESIAN PRODUCT” combines all rows from both tables. Number of rows will be product of the number of rows in each table. In real life scenario, I cannot imagine where we will want to use a Cartesian product. However, there are scenarios where we would like permutation and combination probably Cartesian would be the easiest way to achieve it.


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” , 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 function is 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 a self-join?

If you want to join, two instances of the same table you can use self-join.


What is the difference between DELETE and TRUNCATE?

Following are difference between them:


• DELETE TABLE: - syntax logs the deletes thus making the delete operations low. TRUNCATE table does not log any information but it logs information about deallocation of data page of the table. So TRUNCATE table is faster as compared to delete table.


• DELETE table can be rolled back while TRUNCATE cannot be.


• DELETE table can have criteria while TRUNCATE cannot.


• TRUNCATE table cannot have triggers.


Select addresses which are between ‘1/1/2004’ and ‘1/4/2004’?

Following SQL Statement can be used, please replace the date accordingly.

Select * from Person.Address where modifieddate between '1/1/2004' and '1/4/2004'

SQL Server interview question: - What is the difference between unique key and primary key?

This is a typical SQL Server interview question and below is the comparison sheet.



















Unique Key


Primary Key


Unique key can have nulls


Primary key cannot have nulls.


In a single table we can create multiple unique keys.


In a single table we can have only one primary key.


It creates a non-clustered index by default.


It created a clustered index by default.


Both unique keys and primary keys can be referenced by foreign key.


Below is a nice video which demonstrates the same in a much precise manner...Enjoy.





SQL Server Interview Question - What are the diferences between INNER JOIN, LEFT JOIN and RIGHT JOIN in SQL Server?

Let us Assume we have following two tables:-


Create a new table as"Customers":



























Cust_Id    LastName   FirstName  Address City
1    Shaikh Moosa Churchgate Mumbai
2 Khan Salman Bandra Mumbai
3 Shaikh Feroz Mahim Chennai


Note that the "Cust_Id" column is the primary key in the "Customers" table.
This means that no two rows can have the same Cust_Id.
The Cust_Id distinguishes two persons even if they have the same name.
Next, we have the "Orders" table:




























Order_Id OrderNo   Cust_Id
1 7895 3
2 4678 3
3 2456 1
4 4562 1
5 4764 15


Note that the "Order_Id" column is the primary key in the "Orders" table and that the "Cust_Id" column refers to the persons in the "Customers" table without using their names.


Notice that the relationship between the two tables above is the "Cust_Id" column.


LEFT JOIN:  Return all rows from the left table, even if there are no matches in the right table.
For Example:- 
                    The Following is the example for LEFT JOIN:


Considering the above two tables:


Query:-  Select * from Customers left join Orders on Customers.Cust_Id = Orders.Cust_Id


The output will look like following:

















































Cust_Id LastName  FirstName   Address   City Order_Id OrderNo Cust_Id
1 Shaikh Moosa Churchgate Mumbai 3 2456 1
2 Khan Salman Bandra Mumbai NULL NULL NULL
3 Shaikh Feroz Mahim Chennai 1 7895 3
3 Shaikh Feroz Mahim Chennai 2 4678 3


RIGHT JOIN:  Return all rows from the right table, even if there are no matches in the left table.
For Example:- 
                    The Following is the example for RIGHT JOIN:


Considering the above two tables:


Query:-  Select * from Customers right join Orders on Customers.Cust_Id = Orders.Cust_Id


The output will look like following:


























































Cust_Id LastName FirstName  Address   City   Order_Id OrderNo   Cust_Id
3 Shaikh Feroz Mahim Chennai 1 78958 3
3 Shaikh Feroz Mahim Chennai 2 4678 3
1 Shaikh Moosa Churchgate Mumbai 3 2456 1
NULL NULL NULL NULL NULL 4 4562 4
NULL NULL NULL NULL NULL 5 4764 6


INNER JOIN:  The INNER JOIN keyword return rows when there is at least one match in both tables.
For Example:- 
                   The Following is the example for RIGHT JOIN:
Considering the above two tables:


Query:-  Select * from Customers inner join Orders on Customers.Cust_Id = Orders.Cust_Id


The output will look like following:








































Cust_Id LastName FirstName  Address   City   Order_Id OrderNo   Cust_Id
3 Shaikh Feroz Mahim Chennai 1 78958 3
3 Shaikh Feroz Mahim Chennai 2 4678 3
1 Shaikh Moosa Churchgate Mumbai 3 2456 1


Regards,




SQL Server Interview Quetion - What is Normalization and its different forms?

Normalization is the process of organizing the table's data in proper manner.
In other words Normalization is the process of breaking up data into a logical non-repetitive format that can be easily reassembled as a whole.


Normalization have 3 different forms namely 1Normal Form,2Normal Form,3Normal Form.


1Normal Form:
A table is said to be in 1NF if it satisfies the following rules.
 • The table must not contain any redundant groups of data
 • data must be broken up into the smallest units possible. In addition to breaking data up into the smallest meaningful values.


2Normal Form:
 A table is said to be in 2NF if it satisfies the following rules.
 • The table must be in 1NF
 • All the non-key column must depend on primary key.


3Normal Form:
A table is said to be in 3NF if it satisfies the following rules.
 • The table must be in 2NF
 • A non-key field should not depend on another Non-key field.
 • The data should not be derived further.


For ex:
Below table is in denormalize format:







































CustomerName Region Product Quantity PerProduct Total
Shivprasad Bist India,Mumbai Shirt 2 10 20
Raju Bist India,Gujrat Pant 2 30 60
Moosa Shaikh India,Chennai Pant 4 15 60
Feroz Shaikh India,Gujrat Shirt 2 10 20


Applying Normalization on this table.
1NF:After applying 1NF the table look like

















































CustomerFirstName CustomerLastName Country State Product Quantity PerProduct Total
Shivprasad Bist India Mumbai Shirt 2 10 20
Raju Bist India Gujrat Pant 2 30 60
Moosa Shaikh India Chennai Pant 4 15 60
Feroz Shaikh India Gujrat Shirt 2 10 20

The customer Name is divided into two units like Customer FirstName and Customer LastName.
Region field is also divided into two units like Country and State.


2NF:After applying 2NF the table look like
















































CustomerFirstName CustomerLastName Country State Product Quantity PerProduct Total
Shivprasad Bist 1 1 2 2 10 20
Raju Bist 1 2 1 2 30 60
Moosa Shaikh 1 3 1 4 15 60
Feroz Shaikh 1 2 2 2 10 20

CountryTable
























CountryId CountryName StateId StateName
1    India 1 Mumbai
2 Gujrat
3 Chennai


ProductTable













ProductId  ProductName
1 Pant
2 Shirt


For avoiding duplication Create a new master table of Country and Product.


3NF:After applying 3NF the table look like 











































CustomerFirstName CustomerLastName Country State Product Quantity PerProduct
Shivprasad Bist 1 1 2 2 10
Raju Bist 1 2 1 2 30
Moosa Shaikh 1 3 1 4 15
Feroz Shaikh 1 2 2 2 10

A non key field Total is removed from the table. 


Regards,




More SQL SERVER Interview Questions & Answers here

Found this useful, bookmark this page to the blog or social networking websites. Page copy protected against web site content infringement by Copyscape

 Exclusive Interview Questions and Answers Categories