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

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.

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.

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.

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

GRANT statement grants rights to the objects (table). While revoke does the vice-versa of it, it removes rights from the object.

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.

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

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.

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 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 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

“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.

Select top 1 * from sales. salesperson

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

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

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.

ASCENDING

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

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.

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'

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

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

Let us Assume we have following two tables:-

Create a new table as"Customers":

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:

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:

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:

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:

Regards,

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:

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

2NF:After applying 2NF the table look like

CountryTable

ProductTable

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

3NF:After applying 3NF the table look like 

A non key field Total is removed from the table. 

Regards,