Explain PRIMARY KEY constraint in SQL Server.

The PRIMARY KEY ,

• is used to uniquely identify the records in a table.

• it should not contain NULL values.

• each table should have a PRIMARY KEY.

• each table can have only one PRIMARY KEY.

Ex :

Create Table Suppliers

(

Supplier_ID int NOT NULL PRIMARY KEY,

Supplier_Name varchar (300) NOT NULL,

Address varchar (500),

City varchar (200)

)

This Query creates PRIMARY KEY on the “Supplier_ID” column in suppliers table.

Explain NOT NULL constraint in SQL Server.

The NOT NULL constraint is used not to accept NULL values in a column. This makes sure to add values at the time of adding and updating the records.

Ex :

Create Table Suppliers

(

Supplier_ID int NOT NULL,

Supplier_Name varchar (300) NOT NULL,

Address varchar (500),

City varchar (200)

)

This Query will make sure the “Supplier_ID” and “Supplier_Name” columns to not accept NULL values.

Explain the ALTER table statement in SQL Server.

The ALTER table statement is used to add, delete or modify the columns in a table.

Ex :
To ADD a column in a table,

ALTER TABLE Employees

ADD DateofBirth date

This Query will display a new column “DateofBirth” in date format to Employees table.

To DELETE a column in a table,

ALTER TABLE Employees

DROP column DateofBirth

This Query will delete the column “DateofBirth” from Employees table.

Explain the TOP clause in SQL Server.

The TOP clause is used to display the number of records to return. This clause is useful to return records from large tables.

Ex :

Select Top 5 * from Employees

This Query will display the only the first five records from the Employees.

Ex :

Select Top 25 percent * from Employees

This Query will display the 25% of the records from the table.

Explain ROUND () function in SQL Server.

The ROUND () function is used for rounding the numeric value to the number of decimals required.

Ex :

Select EmployeeName, round (EmployeeReimbursement, 0)

as EmployeeReimbursement

from Employees

This Query will display the Employee Name and the Employee Reimbursement rounded to the nearest integer.

Explain HAVING clause in SQL Server.

The HAVING function is used in aggregate functions because WHERE keyword cannot be used in aggregate functions.

Ex :

Select EmployeeName, sum(EmployeeSalary) from Employees

Group by EmployeeName

Having Sum (EmployeeSalary) < 20000

This Query will display all the employees name , sum their salary and display if the salary is less than 20000.

Explain GROUPBY function in SQL Server.

The GROUPBY function is used to group the results fetched from one or more columns. It is widely used with aggregate functions.

Ex :

Select EmployeeName, sum(EmployeeSalary) from Employees

Group by EmployeeName

This Query will display the total sum(Employee Salary) of each employee and group by the employees name.

Explain LAST function in SQL Server.

The LAST function displays the last value of the selected column.

Ex :

Select last(EmployeeID) as LastEmployeeID from Employees

This Query will display the last value of “EmployeeID” column, the output will be displayed with the column name “LastEmployeeID”.

Explain FIRST function in SQL Server.

The FIRST function displays the first value of the selected column.

Ex :

Select first(EmployeeID) as FirstEmployeeID from Employees

This Query will display the first value of “EmployeeID” column, the output will be displayed with the column name “FirstEmployeeID”.

Explain AVG function in SQL Server.

The AVG function is used to return the average value of a numeric column.

Ex :

Select avg(EmployeesSalary) as AverageSalary from Employees

This Query will display the average value of “EmployeesSalary” column fields, the output will be displayed with the column name ”AverageSalary”.

Explain SUM function in SQL Server.

The SUM function is used to total sum of a numeric column.

Ex :

Select sum(EmployeesSalary) as TotalEmployeesSalary from Employees

This Query will display the sum of all the “EmployeesSalary” column fields, the output will be displayed with the column name “TotalEmployeesSalary”.

Explain MAX function in SQL Server.

The MAX function is used to find the highest value in a particular column.

Ex :

Select max(EmployeeAge)as MaximumAgedEmployee from Employees

This Query will display the highest age of the employee from “EmployeeAge” column, the output will be displayed with the column name “MaximumAged Employee”.

Explain MIN function in SQL Server.

The MIN function is used to select the lowest value in a particular column.

Ex :
Select min(Employeeage) as MinimumAgedEmployee from Employees

This Query will display the lowest age of the employee from “EmployeeAge” column, the output will be displayed with the column name “MinimumAgedEmployee”.

Explain COUNT () function in SQL Server.

Using COUNT (Column_Name) function

The COUNT (Column_Name) function is used to view the number of specified rows without displaying the null values.

Ex :
Select count(EmployeeName) as EmployeeNameRamesh from Employees
Where EmployeeName

This Query will display the total numbers who has name “Ramesh”. For ex. if there were eight people in the name of “Ramesh” in the table, then the output will show as “8”.

Using COUNT (*) function

The COUNT (*) function is used to view the total number of rows in a particular table.

Ex :
Select count(*) as NumberofEmployees from Employees

This Query will display the total number of rows in the table “Employees”, the output will be displayed in the column name of “NumberofEmployees”.

Using COUNT (DISTINCT Column_Name) function

The COUNT (DISTINCT Column_Name) function is used to view the distinct number of records.

Ex :
Select count(distinct Employees) as NumberofEmployees from Employees

This Query will display the total number of employees distinctly with the new table name “NumberofEmployees”. i.e employees records with the same name is not shown repeatedly.

Explain ALIASES clause in SQL Server.

The ALIASES clause is used to give another name for a column or a table. This is mostly used to show the output meaningful.

Ex :
Using ALIAS in a Column

Select EmployeeName as FullName, EmployeeID as ID from Employees

This Query will display the column ‘Employee Name’ as ‘Full Name’ and ‘Employee ID’ as ‘ID’ from the table “Employees”.

Using ALIAS in a Table

Select EmployeeName, EmployeeID from Employees as Resources

This Query will select the columns ‘Employee Name’ and ‘Employee ID’ from the table “Employees” and display as a “Resource” table.

Using WHERE clause with SQL ALIAS

Select EmployeeName as FullName, EmployeeID as ID from Employees where EmployeeID > 250

This Query will select the ‘Employee Name’ and ‘Employee ID’ from “Employees” table and display as ‘Full Name’ and ‘ID’ with a condition of ‘Employees ID’ greater than 250.

Explain DISTINCT clause in SQL Server.

The DISTINCT clause is normally used with SQL SELECT statement to get unique dataset entries from a database table.

Ex :
Select distinct EmployeeName
from Employees

This Query will display only the unique Employee Names from the Employees table.

What is BETWEEN operator in SQL Server?

The BETWEEN operator is used to view data between two values.

Ex :
Select * from Employees
where Joiningdate
between ’06-Jan-2006’ and ’15-July-2007’

This Query will display all the records of employees joined between 06-Jan-2006 and 15-July-2007.

Explain IN operator in SQL Server.

The IN operator is used to compare a column with one or more value. It is similar to OR condition.

Ex :
Select EmployeeName, EmployeeID
from Employees
where City in (‘Chennai’, ‘Bangalore’)

This Query will display the Employee Name, Employee ID only from city “Chennai” and “Bangalore”.

Explain AND & OR conditions in SQL Server.

The AND & OR is used to join two or more conditions in a WHERE clause.

The AND operator displays a row if ALL the conditions listed are true.

Ex :
Select * from Employees
where EmployeeName = ‘Steve’
and City = ‘Bangalore’

This Query will display the each person with EmployeeName equal to “Steve” and City equal to “Bangalore”

The OR operator displays a row if ANY of the conditions listed are true

Ex :
Select * from Employees
where EmployeeName = ‘Steve’
or City = ‘Bangalore’

This Query will display the each person with EmployeeName equal to “Steve” or City equal to “Bangalore”.

What is the difference between UNION and UNIONALL commands in SQLServer?

The UNION command is used to select the related data from two different tables, while using the UNION command all the selected columns have to to be of the same data type.

Ex :
Select EmployeeName from Employees
union
Select EmployeeName from Department

This Query will display only the distinct Employee names in Employees and Department Tables.

The UNION ALL command is similar to UNION command, but it displays all the values in the both the tables.

Ex :
Select EmployeeName from Employees
union all
Select EmployeeName from Department

This Query will display all the Employee names in Employees and Department Tables.