Database Assignment Help | SQL Server Practice Set: 4



In this blog we will learn another sql server topics which is very important for every sql server developer, here the topics which we will cover in this: Summarizing Data, Grouping Data: Grouping, Filtering Groups:Filtering, Grouping and Sorting, Working with Subqueries:Queries, Filtering by Subquery, Subqueries as Calculated Fields:Queries, Inserting by Subquery, Update by Subquery, Delete by Subquery, Aggregation.


Summarizing Data

Data aggregation is the process of taking numerous records and collapsing them into a single summary record. When aggregating data, one must decide what records must be considered in the summary and how these records should be summarized. Data can be summarized based on certain fields (attributes) in a database or derived attributes. Once the data to summarize has been decided on, the next step is to decide how to aggregate the data.


The most commonly used aggregate functions are listed below.

  • Count(SomeField)

  • Avg(SomeField)

  • Sum(SomeField)

  • Max(SomeField)

  • Min(SomeField)


The following is the SELECT statement syntax used to get a count of the number of records in a table:


Basic Syntax

SELECT 
      Count(*) AS NewColumnName 
FROM 
      tablename 
Group By 
      columnName 

The following is the SELECT statement syntax used to get a count the number of records in the ProductVendor table:


Write Exercise Code:

SELECT 
   Count(*) AS TotalNumberProducts 
From 
    ProductVendor 

The following is the SELECT statement syntax used to get a count the number of records in the ProductVendor table using the OnOrderQty field:


Write Exercise Code:

SELECT 
      Count(OnOrderQty) AS TotalNumberProducts 
From 
      ProductVendor
 

This returns 155 records. That is because 305 records have NULL values.


The following is the SELECT statement syntax used to get the Average value of a specified column in a table:


Basic Syntax

SELECT 
     AVG(columnname) AS NewColumnName 
FROM 
     tablename

The following is the SELECT statement syntax used to get the Average Standard Price in the ProductVendor table:


Write Exercise Code:

Select 
    AVG(StandardPrice) AS AvgPrice 
From 
    ProductVendor 


The following is the SELECT statement syntax used to get the total value of a column for a group of rows in a table:


Basic Syntax

SELECT 
    SUM(columnname) AS NewColumnName 
FROM 
    tablename 
WHERE 
    columnName = Value


The following is the SELECT statement syntax used to SUM of Order quantity for a specific Product in the SalesOrderDetail table:


Write Exercise Code:

Select 
     SUM(OrderQty) AS TotalOrder 
From
     SalesOrderDetail 
WHERE 
     ProductID = 777

In the following SELECT statement returns the MIN item_price


Basic Syntax

SELECT 
    MIN(columnname) As AliasName 
FROM 
    tablename


In the following SELECT statement returns the MIN unit price


Write Exercise Code:

SELECT 
    MIN(UnitPrice) As MinPrice 
FROM 
    PurchaseOrderDetail

Result

MinPrice

---------------------

0.21

(1 row(s) affected)


In the following SELECT statement returns the Max unit price


Basic Syntax

SELECT 
     MAX(columnname) As AliasName 
FROM 
     Tablename


In the following SELECT statement returns the Max unit price.


Write Exercise Code:

SELECT 
    Max(UnitPrice) As MaxPrice 
FROM 
    PurchaseOrderDetail

Result

MaxPrice

---------------------

482.8345


In the following SELECT statement returns the MIN and Max unit price.


Write Exercise Code:

SELECT 
     MIN(UnitPrice) As MinPrice, Max(UnitPrice) As MAXPrice 
FROM 
     PurchaseOrderDetail

In more common terms, this request could be similar to. “Can you help me get the highest price PO detail item, and the lowest priced one?”


Result

MinPrice MaxPrice

--------------------- ---------------------

0.21 82.8345

(1 row(s) affected)


Grouping Data:

Grouping Grouping allows you to divide data INTO group sets so you can perform some calculations on each group. The Group By clause can have many columns. The Group By is an example of an operation that allows you to return calculated data in groups based on the group By clause. The following is the SELECT statement syntax used for specific functions:


Basic Syntax

SELECT 
      columnname, Count(*) AS NewColumnName 
FROM 
      tablename 
Group By 
      columnName 


The following is the SELECT statement syntax used to get a count the number of products each vendor has:


Write Exercise Code:

SELECT 
      VendorID, Count(*) AS TotalNumberProducts 
From 
      ProductVendor 
Group By 
      VendorID 

In more common terms, this request could be similar to. “Can you help me get report for the number of products carried by each of our vendors?”


Result

VendorID TotalNumberProducts

---------- -------------------

1 1

2 3

3 1

4 2

5 1

(Not all rows displayed)


Filtering Groups:

Filtering Grouping allows you to divide data into group sets so you can perform some calculations on each group. Filtering allows you to include or exclude specific data. The WHERE clause does not work here because we are filtering by group and not rows. Instead of using the WHERE clause we use the Having Clause. The main difference between the two is WHERE clause filters rows and Having filters by groups. The Having clause is an example of an operation that allows you to return calculated data in groups but filtering for specific group totals.


Basic Syntax

SELECT 
     columnname, Count(*) AS NewColumnName 
FROM 
     tablename 
Group By 
     columnName 
Having 
     Count(*) operator value

The following is the SELECT statement syntax that will not work because it uses the Having clause in the WHERE clause. Write this code and test it:


Write Exercise Code:

Select 
     EmployeeID, Sum(VacationHours) 
FROM 
     Employee 
WHERE 
     Sum(VacationHours) > 20 
GROUP BY 
    EmployeeID 

Result

N/A/


The following is the SELECT statement syntax that will work because it does not use the Having clause in the WHERE clause. Write this code and test it:


Write Exercise Code:

Select 
     EmployeeID, Sum(VacationHours) 
FROM 
     Employee 
GROUP BY 
     EmployeeID 
HAVING 
     Sum(VacationHours) > 20

Result

EmployeeID (No column name)

1 21

2 42

4 48

6 40

(Not all rows displayed)



The following is the SELECT statement syntax used for excluding specific data using the Having clause:

Write Exercise Code

SELECT 
     VendorID, Count(*) AS TotalProducts 
From 
     ProductVendor 
Group By 
     VendorID 
Having 
     Count(*) >=10


Result

VendorID TotalProducts

----------- -------------

7 30

23 23

25 16

(Not all rows displayed)



You could however, include a WHERE clause in the SELECT statement. You could specify that the result set only include those rows that standard price are at least 50 but having a group count of 10 or more:


Write Exercise Code

SELECT 
     VendorID, Count(*) AS TotalProducts 
From 
     ProductVendor 
WHERE 
     StandardPrice <=50 
Group By 
     VendorID 
Having 
    Count(*) >=10

In more common terms, this request could be similar to. “Can you help me get a Vendor report for the vendors that have 10 or more products. Oh and the products should be $50 or less in price.”


Result

VendorID TotalProducts

----------- -------------

7 30

23 23

25 16

32 16

(Not all rows displayed)


Grouping and Sorting

Grouping and Ordering do different but related functions. Grouping allows you to divide data into group sets so you can perform some calculations on each group.


Filtering allows you to include or exclude specific data. The WHERE clause does not work here because we are filtering by group and not rows. Instead of using the WHERE clause we use the Having Clause. The main difference between the two is WHERE clause filters rows and Having filters by group.


The Having clause is an example of an operation that allows you to return calculated data in groups but filtering for specific group totals.


Basic Syntax

SELECT 
     columnname, Count(*) AS NewColumnName 
FROM 
     tablename 
Group By 
      columnName 
Having 
     Count(*) operator value 
Order BY 
    columnName 


The following SELECT statement is similar to previous example. Here you get a result set with TotalCostQuantity in default order where sum is >= 1000:


Write Exercise Code:

SELECT 
      SalesOrderID, Sum(OrderQty*UnitPrice) AS TotalCostQuantity 
FROM 
      SalesOrderDetail 
Group By 
      SalesOrderID 
Having 
      Sum(OrderQty*UnitPrice)>=1000

In more common terms, this request could be similar to.


“Can you help me get a Sales order where the total cost of the order is $1000 or above? I’d like the report show show the Sales Order ID, and the total cost of the order.”


Result

SalesOrderID TotalCostQuantity

------------ ---------------------

43659 20565.6206

43660 1294.2529

43661 32726.4786

(Not all rows displayed)


Write Exercise Code:

The following SELECT statement is similar to previous example except we are changing the data type to decimal with two decimals to the right. Here we are using the Cast to change the data type. Here you get a result set with TotalCostQuantity in default order where sum is >= 1000:


SELECT 
      SalesOrderID, Cast(Sum(OrderQty*UnitPrice) As decimal(10,2)) AS TotalCostQuantity FROM 
      SalesOrderDetail 
Group By 
      SalesOrderID 
Having 
      Sum(OrderQty*UnitPrice)>=1000

Result

SalesOrderID TotalCostQuantity

------------ ---------------------------------------

43659 20565.62

43660 1294.25

43661 32726.48

(Not all rows displayed)



In this following SELECT statement is similar to previous example but here you add the Order By to order by Total order:


Write Exercise Code:

SELECT 
    SalesOrderID, Cast(Sum(OrderQty*UnitPrice) As decimal(10,2)) AS TotalCostQuantity FROM 
    SalesOrderDetail 
Group By 
    SalesOrderID 
Having 
    Sum(OrderQty*UnitPrice)>=2000
Order BY 
    SalesOrderID ,TotalCostQuantity


Result

SalesOrderID TotalCostQuantity

------------ ---------------------------------------

43659 20565.62

43660 1294.25

43661 32726.48

(Not all rows displayed)


Working with Subqueries:

Queries So far we have been using single statements to return data from tables. Queries refer to SELECT statements. Filtering by Subquery SQL allows you to embed sub queries INTO other queries. In order for you to assure you are returning correct data sets you tables should be in a Relational Database. If you wanted to retrieve customers who ordered a product you would have to do several steps.


In each of the steps you would use the result set to populate the WHERE clause. The following is a set of SELECT statements that retrieves the required data for the seceding SELECT statements.


The basic syntax is as follows:

Basic Syntax

SELECT 
    column_name 
FROM 
    table 
WHERE 
    column_name = 'VALUE'

The following SELECT statement syntax is the first step used to get the required data from the SalesOrderDetail table. First we want those order numbers from SalesOrderDetail table WHERE the product ID = 897


Write Exercise Code

SELECT 
     SalesOrderID 
FROM 
     SalesOrderDetail 
WHERE 
     ProductID = 897

Result

SalesOrderID

------------

51823

51875

(2 row(s) affected)


Next we use the results from previous SELECT statement to populate the WHERE clause in the following SELECT statement. Now, we use the IN keyword in the WHERE clause to return the customer IDs from the Sales Order Header table. This SELECT statement returns two customer IDs. We now know that these two customers are the only ones with Product ID 897 orders.


Write Exercise Code:

SELECT 
      CustomerID 
FROM 
     SalesOrderHeader 
WHERE 
      SalesOrderID 
IN (51823,51875) 

Result

CustomerID

-----------

520

393

(2 row(s) affected)


Now let’s combine the two previous SELECT statements using the first SELECT statement as a subquery.


This SELECT statement with a subquery returns the same two rows as did the previous two SELECT statements.