Explain ORDER BY statement in SQL Server.

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.

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.

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.

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.

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?

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.

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?

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

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.