SQL Questions PART-3

Q How many columns can a view support ?

       A view can support maximum of 1024 columns.

Q What is the difference between Primary Key and Unique Key ?
    There can be only one one Primary Key per table but there can be more than one unique key per table. Primary Key does not allow NULL  but Unique Key can allow one NULL . Primary Key creates Clustered Index on the column by default where as Unique Key creates non clustered index on the column by default.

Q How to count number of columns in a table in SQL Server ?
      Following query will return number of columns in a table.
SELECT COUNT(*) AS ColumnCount
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TableName'

Q Up to what level, subqueries can be nested in SQL Server?
     Subqueries can be nested up to 32 level in SQL Server.

SQL- Checking Duplicate Records in a Table

Q. How to check duplicate records in a table using SQL Server ?

        Suppose we have a table without a primary key. We want to find whether duplicate records are present in the table or not. For this scenario, we have to select all columns name and count from a table and group it by all columns name in a table and then filter based on count greater than 1. Lets see the below query.

 SELECT OrderNo,Customer,Amount,Location,Item,COUNT(*)AS TotCount 
 FROM OrderDetails 
 GROUP BY OrderNo,Customer,Amount,Location,Item
HAVING COUNT(*)>1

We see that records which are marked with red is the duplicate record. Lets find out with the help of query.

Suppose we have a scenario where we need to find all the records based on the duplicate value present in a column. Lets assume we have to find all the records from the table where column  'Location' has duplicate value.

SELECT OD.* FROM OrderDetails OD 
INNER JOIN 
(SELECT Location
FROM OrderDetails
GROUP BY Location
HAVING COUNT(*)>1)A
 ON A.Location=OD.Location

Thus we see that all the records related to duplicates present in the column 'Location' has been filtered out.

SQL- NULL Functions

1. ISNULL() : 

Syntax:

ISNULL ( check_expression , replacement_value )

It replaces the NULL value with replacement value. If the Check_expression evaluates to NULL then it is replaced with the replacement value else the value of Check_expression is returned.

ISNULL function can be used both in SQL SERVER and MSAccess

Let's understand with examples: Consider a below table SalesInfo for illustrations:

SELECT * FROM SalesInfo

We see that some of the products has not been sold . This is is evident as we can see there are NULL in QuantitySold column. Now,for better understanding we want  to display zero insteadof  NULL in QuantitySold column,we can do this by using ISNULL function.

SELECT ProductId,ProductName,Price,

ISNULL(QuantitySold,0)
FROM SalesInfo

Suppose we want to calculate, sales per item. For this we need to multiply the unit price and QuantitySold. If the Quantity sold is NULL, we can not get the correct result because multiplication of any number with NULL is always NULL. In such scenario, ISNULL() function is very useful. We can easily replace all NULL values with zero using ISNULL() function.

SELECT ProductName,
Price*(ISNULL(QuantitySold,0))AS SalePerItem
FROM SalesInfo

2. NVL() :

Syntax:

NVL( check_expression , replacement_value )

Just like ISNULL function in SQL SERVER, we have NVL function in ORACLE. It replaces the NULL value with replacement value. If the Check_expression evaluates to NULL then it is replaced with the replacement value else the value of Check_expression is returned.

This function holds the same explaination as that of ISNULL function.

SQL> SELECT ProductId,ProductName,Price,NVL(QuantitySold,0)
   2      FROM SalesInfo;

NOTE: IFNULL performs the same function as ISNULL () and NVL()
in Mysql database.

3. COALESCE():

 Syntax:
 COALESCE(Expression1, Expression2[,Expression3.........ExpressionN)
It returns the first non null value among its arguments.

Let us understand from the 'TelephoneRecords' Table.

SELECT * FROM TelephoneRecords

We want to display the Name and Mobile number of each person. For displaying the mobile number, Primary No should be displayed. If the Primary number is not available then Alternate No should be displayed.If Alternate No is not available then Office No should be displayed. For such a scenario, COALESCE function will serve our purpose as it always returns the first non null value.

SELECT Name,
COALESCE(PrimaryNo,AlternateNo,OfficeNo)
AS MobileNo
FROM TelephoneRecords

NOTE: If all the arguments in COALESCE function returns NULL then output will also be NULL.

It can also work similar to ISNULL function.

SELECT Name,COALESCE(PrimaryNo,0) 

AS MobileNo

FROM TelephoneRecords

We see that all NULL values replaced with zero.

SQL- Scenario Based Questions PART- 1

SQL- Scenario based questions Part 1.

                                   Now the recruiters are setting a new trend by asking a scenario based questions in an interview.  Keeping in this mind, we have come up with many standard scenario based questions.

Scenario: You have been given the following Table "Annualhike". This table contains the name of employee, salary, designation, CRR and the increment. You have to test the increment column of the table based on the below logic:

• Employee gets an increment of 15% of Salary if he/she gets CRR 1+.
• Employee gets an increment of 10% of Salary if he/she gets CRR 1.
• Employee gets an increment of 5% of Salary if he/she gets CRR 2.

You have to find out the records if there are any incorrect population in Increment field.

SELECT * FROM Annualhike

Solution

Method1

SELECT * FROM Annualhike A 
WHERE Increment <> (SELECT CASE WHEN A.CRR='2' 
                      THEN .05* SalaryPackage
      WHEN A.CRR='1'  THEN .1* SalaryPackage
      WHEN A.CRR='1+' THEN .15* SalaryPackage  
      END AS Increment)

Method 2

SELECT * FROM Annualhike

EXCEPT

SELECT Employee,SalaryPackage,Designation,CRR ,

              CASE WHEN CRR='2' THEN .05* SalaryPackage

                         WHEN CRR='1' THEN .1* SalaryPackage

                         WHEN CRR='1+' THEN .15* SalaryPackage  

              END AS Increment

 FROM Annualhike

For other SQL Scenario Based Questions, please visit below link
http://itjunction4all.blogspot.com/2015/05/sql-scenario-based-questions-part-3.html