MS SQL Server Guide

Explain PRIMARY KEY constraint in SQL Server.

2012-01-18T05:14:00.000-08:00

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.

2011-12-20T08:24:00.001-08:00

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.

2011-12-01T06:59:00.000-08:00

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.

2011-11-20T07:46:00.000-08:00

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.

2011-11-16T07:00:00.000-08:00

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.

2011-09-22T08:50:00.000-07:00

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.

2011-08-18T03:32:00.001-07:00

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.

2011-08-03T02:28:00.000-07:00

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.

2011-08-01T11:08:00.000-07:00

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.

2011-07-27T10:41:00.000-07:00

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.

2011-07-22T23:58:00.000-07:00

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.

2011-07-21T10:39:00.000-07:00

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.

2009-09-22T05:21:00.000-07:00

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.

2009-02-24T06:41:00.000-08:00

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.

2009-02-06T23:01:00.000-08:00

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.

2009-01-28T17:15:00.000-08:00

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?

2008-09-26T19:22:00.000-07:00

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.

2008-06-22T05:11:00.000-07:00

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.

2007-09-19T03:19:00.000-07:00

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?

2007-09-10T00:08:00.000-07:00

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.

Explain ORDER BY statement in SQL Server.

2007-08-06T07:22:00.001-07:00

The ORDER BY clause is used to sort the output in the specified manner like ascending or descending.

Ex :
Select EmployeeName, EmployeeID from Employees order by EmployeeName Desc

This Query will display the Employee names in reverse alphabetical order.

Select EmployeeName, EmployeeID from Employees order by EmployeeName Desc, EmployeeID Asc

This Query will display the Employee names in reverse alphabetical order and the Employee ID in alphabetical order.

Explain DELETE statement in SQL Server.

2007-08-06T07:20:00.000-07:00

The DELETE statement in used to delete the rows in a database table.

Ex :
Delete from Employees where EmployeeName = ‘Peterson’

This Query will delete the row that is having employee name as “Peterson”.

All the rows in a table can be deleted like,

Delete * from Employees
Or
Delete from Employees

This Query will delete all the rows in “Employees” table.

Explain UPDATE statement in SQL Server.

2007-08-06T07:18:00.000-07:00

The UPDATE statement is used to modify the data in a database table.

Ex :
Update Employees set EmployeeName = ‘Peter Heines’ where EmployeeName = ‘Peter’

This Query will modify the Employee name “Peter” into “Peter Heines”.

The data can be updated in several columns like,

Update Employees set Address = ‘134 Gandhi Nagar’ , City = ‘Bangalore’ where EmployeeName = ‘James Roguer’

This Query will modify the Address and City of the Employee with name “James Roguer”.

Explain INSERT INTO statement in SQL Server.

2007-08-06T07:17:00.000-07:00

The INSERT INTO statement is used to insert new rows in a database table.

Ex :

Insert Into Employees values ( ‘Edward’, ‘14991’, ’16 Indira Nagar’, ‘Chennai’ )

This Query will insert data into “Employee Name”, “Employee ID”, “Address”, “City” columns of “Employees” table

The data can be inserted in specified columns like,

Insert Into Employees ( EmployeeName, City ) values ( ‘Andrew’, ‘Chennai’ )

This Query will insert data into “Employee Name” and “City” column of “Employees” table.

Explain SELECT INTO statement in SQL Server.

2007-08-06T07:15:00.000-07:00

The SELECT INTO statement is used to select data from a database table and to insert it to a different table at the same time.

Ex :

Select * into Departments from Employees

This Query will select data from “Employees” table and insert into “Departments.

Explain how to use WHERE clause in SELECT statements?

2007-08-06T07:12:00.000-07:00

The WHERE clause selects data from a table on conditional basis,

Ex :
Select EmployeeID from Employees where Position = 'Manager';

This Query displays the ID Numbers of all Managers from the table “Employees” and make sure that any text that appears in the statement is surrounded by single quotes (').

Select EmployeeID from Employees where Salary >= 50000;

This Query displays the ID Numbers of all Managers from the table “Employees” who gets salary of 50000 and above.

Select * from Employees where EmployeeName like ‘d%’

This Query will display the Employee names that start with ‘d’.

Select * from Employees where EmployeeName like ‘%m’

This Query will display the Employee names that end with ‘m’.

Select * from Employees where EmployeeName like ‘%la%’

This Query will display the Employee names that contain the word ‘la’.

Select * from Employees where EmployeeName in (‘Jerry’ , ‘Sam’)

This Query will display the Employee names starting with ‘Jerry’ and ‘Sam’.

Select * from Employees where EmployeeName between ‘Jerry’ and ‘Sam’

This Query will display the Employee names alphabetically between ‘Jerry’ and ‘Sam’.

Select * from Employees where EmployeeName not between ‘Jerry’ and ‘Sam’

This Query will display the Employee names outside the range of ‘Jerry’ and ‘Sam’.

Some commonly used logical operators in SQL are,
= “Equal”
<> or != “Not Equal”
< “Less than” > “Greater than”
<= “Less than or equal to” >= “Greater than or equal to”
BETWEEN “Used to search between an inclusive range”
LIKE “Used to Search for a pattern”
IN “Used to return based on the Exact value known”.

Explain SELECT statement in SQL Server.

2007-08-05T06:22:00.000-07:00

The SELECT statement is used to select data from a table, basically the SELECT statement will have three clauses,

SELECT - specifies the table columns retrieved
FROM - specifies the tables to be accessed
WHERE - specifies which rows in the FROM tables to use

The WHERE clause is optional and commonly called as Condition, if it is not used all the table rows will get displayed.

Ex :
Select EmployeeName, EmployeeID from Employees;

This Query will list the Employee Name and Employee ID column from table “Employees”.

Select * from Employees;

This Query will list all the columns in the entire table “Employees”.

Select Distinct Department from Employees;

This Query select only the different values from column named “Department” in “Employees” table.

Select EmployeeName, EmployeeID from Employees where City is NULL

This Query selects the list of Employees who have not specified their city name in the table, if you want to list the Employees who specified the city in table use “where city is NOT NULL”.

What is DDL, DML, DCL, TCL in SQL Server?

2007-08-05T06:20:00.001-07:00

The Data Definition Language (DDL) includes,

CREATE TABLE - creates new database table

ALTER TABLE - alters or changes the database table

DROP TABLE - deletes the database table

CREATE INDEX - creates an index or used as a search key

DROP INDEX - deletes an index

The Data Manipulation Language (DML) includes,

SELECT - extracts data from the database

UPDATE - updates data in the database

DELETE - deletes data from the database

INSERT INTO - inserts new data into the database

The Data Control Language (DCL) includes,

GRANT – gives access privileges to users for database

REVOKE – withdraws access privileges to users for database

The Transaction Control (TCL) includes,

COMMIT – saves the work done

ROLLBACK - restore the database to original since the last COMMIT

Explain SQL

2007-08-01T07:50:00.000-07:00

The Structured Query Language is used for operating the data stored in Relational Database Management Systems (RDBMS). SQL provides commands through which data can be extracted, sorted, updated, deleted and inserted.

Most of the important and common SQL statements are supported by RDBMS like MySQL, Informix, DB2, Oracle, MS SQL Server, MS Access, Sybase, etc.