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
- Inner Join
- Outer Join
- Left Outer Join
- Right Outer Join
- Full Join or Cross Join
- 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
whereCountry
= '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 <= 1008
--You can replace above statement by using Between statement.
--Between will show customer with customerID 1003 to 1008.
select *
from Customer
where CustomerID between 1003 and 1008
--Display Customers whose LastName starts from P
select * from Customer where LastName like 'P%'
--Display Customers whose country name ends with A
select * from Customer where Country like '%A'
--Display Customers whose FirstName or LastName consist of word khan
select *
from Customer
where FirstName like '%Khan%' or LastName like '%Khan%'
--Display Customers whose FirstName second character is A
select *
from Customer
where FirstName like '_A%'
Ordering Records with "Order by" Caluse--Note: by default when neither ASC (for Ascending) or DESC (for descending) is specify, it is ordered in Ascending order.
--Display Customers of USA country ordered by state
select * from Customer
where Country='USA'
order by State
--Display Customers of USA country ordered by state in descending order.
select * from Customer
where Country='USA'
order by State DESC
--Order Customer by LastName in descending order and FirstName in Ascending order.
select * from Customer
order by LastName DESC, FirstName ASC
Grouping Records by "Group by" Caluse--Display Count of Customers in each country
select Country,Count(CustomerID) from Customer
group by country
--Display Count of Customers group the data by Country and State
select Country, state, Count(CustomerID) from Customer
group by country, state
Filtering Grouped data by "Having" Caluse--Display Count of Customers country wise and display those countries only where more than 2 customers are available.
select Country,Count(CustomerID) from Customer
group by country
having Count(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 ServerJoins 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
- Left Outer Join
- Right Outer Join
- Full Outer Join or Cross Join
- Self Join
Lets go through the concept quickly.
Inner JoinInner 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 JoinWe 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 Syntaxselect * 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
onCustomer.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
onCustomer.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
onCustomer.CustomerID = Orders.CustomerID
where Country = 'USA'
order by OrderDate DESC
Outer JoinOuter 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 JoinLeft 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
onCustomer.CustomerId = Orders.CustomerID
Right Outer JoinRight 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 JoinA 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 JoinA 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 QueriesA 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 QueriesA 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
