Query execution in SQL Server 2005

SQL Query
execution

In this article you will explore

* How to execute query within sql server 2005 environment
* How to filter records with where clause
* How to arrange records with order by clause
* How to group records by group clause
* How to filter grouped records with having clause
* Concept of Joins, explanation and example of
o Inner Join
o Outer Join
+ Left Outer Join
+ Right Outer Join
+ Full Join or Cross Join
o Self Join
* Concept of sub queries, with explanation and example.
* Concept of correlated sub queries, with explanation and example.

Ok, so lets begin…

For execution of Query, Open New Query Editor as shown in figure.

Now lets execute different data manipulation statement.

Select Database to perform Database Operation using “use” Statement
–Select Database for Data Manipulation opperation
use MySamplePractise

For mastering, Data Manipulation from beginner to expert, Study following Example with 150+ Queries.

Select Statement Execution in SQL Server 2005

–Display each details of all Customers
select * from customer

–Display FirstName, City and Country Details of Customers
select FirstName, City, Country from Customer

–Using Alias to display details, Display “Customer Name” by concating FirstName and LastName of Customer
select FirstName + ‘ ‘ + LastName as ‘Customer Name’ from Customer

Filtering Records with “Where Clause”

–Display Customers from country USA
select * from Customer
where
Country = ‘USA’

–Display Customers from All countries except USA
select * from Customer
where
Country ‘USA’

–Display details of Customers whose CustomerID in a range from 1003 to 1008
select * from Customer
where
CustomerID >= 1003 and CustomerID 2

Using both Where and Having Clause to Filter data.

–Display Count of Customers from country “USA”, state wise where in display details of those state only which have atleast 2 Customer
–It will display only Customer Count from “NJ” state of “USA” country.
select State,Count(CustomerID) from Customer
where state=’NJ’ and country=’USA’
group by state
having count(CustomerID) > 2

Using Order by to arrange Grouped data

–Display Count of Customers from country “USA”, state wise and it should display details in descending order of state of those state where Customers are atleast 2.
select State,Count(CustomerID) from Customer
where country=’USA’
group by state
having count(CustomerID) > 2
Order by state desc

Now, lets display data from multiple table. For that we need to understand Joins so lets begin what is Join and how many different ways to display data.

Joins in SQL Server

Joins in SQL are used to combine the data from two or more tables on a common column between the two tables.

Different types of Joins in SQL Server and its explanation

* Inner Join
* Outer Join
o Left Outer Join
o Right Outer Join
o Full Outer Join or Cross Join
* Self Join

Lets go through the concept quickly.

Inner Join
Inner Join return those rows which are common in both the tables. In other words, Inner Join displays only the rows that have matching rows in both the tables.
Note: Nulls are excluded. Another thing you may even write just “Join” instead of “Inner Join” as default join is “Inner Join”

Example of Inner Join
We can use two types of syntax to write Join Query, that is

* Join Query with ANSI Join Syntax
* Join Query with Microsoft Join Syntax

well i will go through both example, but we will follow ANSI Join Syntax as it is widely used, but according to me Microsoft Join Syntax is easy…

Use Northwind
Method 1: ANSI JOIN Syntax
select * from
orders inner join [order details]
on
orders.orderid = [order details].orderid

Method 2: Former Microsoft JOIN Syntax
select * from orders, [order details]
where orders.orderid = [order details].orderid

Both query will be generate the same result.

Now, lets turn to example from our SamplePractise Database

–Display Details of Customer who have placed order.
select LastName, FirstName, Country, OrderId, OrderDate from
Customer inner Join Orders
on
Customer.CustomerID = Orders.CustomerID

–or– Note: Inner join is default join and so you can write just join instead of inner join.
select LastName, FirstName, Country, OrderId, OrderDate from
Customer Join Orders
on
Customer.CustomerID = Orders.CustomerID

–Display Details of Customer living in “USA” who have placed order and arrange data by latest order placed.
select LastName, FirstName, Country, OrderId, OrderDate from
Customer inner Join Orders
on
Customer.CustomerID = Orders.CustomerID
where Country = ‘USA’
order by OrderDate DESC

Outer Join
Outer Join, return all the rows from at least one of the table mentioned in the Join clause. Lets understand 3 types of outer join

Left Outer Join
Left outer join returns all the records from left table and only matching records from right table.

Example of Left Outer Join:
–Display details of all Customers and order details for those customer who have place any order
select LastName, FirstName, Country, OrderID, OrderDate
from Customer Left Outer Join Orders
on
Customer.CustomerId = Orders.CustomerID

Right Outer Join
Right outer join returns all the records from right table and only matching records from left table.

Example of Right Outer Join:
–Display all product type and product available in it
select producttype, productname
from producttype right outer join product
on
producttype.producttypeid = product.producttypeid
order by producttype

Full Join or Cross Join
A cross join returns the sets of records from the two joined tables. If A and B are two sets then cross join = A X B.

Example of Full Join or Cross Join
–Display all orders and all its details
select * from orders cross join orderdetails
-or-
select * from orders, orderdetails

Self Join
A table is join to itself in a self join.

Example for Self Join:
–Display details of customer living in same state
select distinct a.* from customer a, customer b
where a.customerid b.customerid and
a.state = b.state
order by state

–Consider an Example of Employee Table, wherein you want to find details of all the employee drawing same salary
select distinct a.* from employee a, employee b
where a.empid b.empid and
a.salary = b.salary

Joining three or more table
In joining three or more tables, join applies to only one relationship, and each relationship represents an association between only two tables.

FROM table1 join table2
ON table1.primarykey = table2.foreignkey join table3
ON table2.primarykey = table3.foreignkey

The first join applies to table1 and table2, the second join applies to table2 and table3.

Example of Joining three or more tables

–Sample syntax
select * from
table1 left outer join table2 on (table1.field1 = table2.field1),
join table3 on (table2.field2 = table3.field2)

–Display details from customer, order and orderdetails table.
select c.lastname, c.firstname, o.orderid, o.orderdate, od.orderdetailsid, p.productname
from
customer c Inner Join orders o on (c.customerid = o.customerid)
inner join orderdetails od on (o.orderid = od.orderid)
inner join product p on (od.productid = p.productid)
order by o.orderid, od.orderdetailsid, p.productname

Sub Queries
A query within another query is called sub query.

Example of Sub Query
–Display details of customer who haven’t placed any order
select * from customer
where customerid not in (select customerid from orders)

–Display details of customer belonging to “NJ” state of “USA” country has placed order.
select * from customer
where state = ‘NJ’ and
customerid in (select customerid from orders)

Correlated Sub Queries

A correlated subquery is a SELECT statement nested inside another T-SQL statement, which contains a reference to one or more columns in the outer query. Therefore, the correlated subquery can be said to be dependent on the outer query. This is the main difference between a correlated subquery and just a plain subquery. A plain subquery is not dependent on the outer query, can be run independently of the outer query, and will return a result set. A correlated subquery, since it is dependent on the outer query will return a syntax errors if it is run by itself. Source of Information

Example of Correlated Sub Queries

–Display order information along with amount of order.

select o.orderid, o.orderdate,
(select sum(od.amount) from orderdetails od
where o.orderid = od.orderid) as “Amount”
from orders o

–Display orderdetails along with product details.
select od.orderdetailsid,
(select p.productname from product p
where od.productid = p.productid) as “ProductName”,
(select p.productprice from product p
where od.productid = p.productid) as “ProductPrice”,
od.quantity as “Quantity”,
od.quantity * (select p.productprice from product p
where od.productid = p.productid) as “Amount”
from orderdetails od
Note: you can also use
ANY – To display any matching record within sub query
ALL – To display details of those records who match all the criteria of sub query

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: