Scribd
Upload
5 views2 pages

MS SQL Query Examples and Analysis

The document contains SQL queries related to customer and part data in MS SQL Server. It includes examples of selecting customer information, calculating available credit, counting items, and summarizing balances and credit limits. The queries also demonstrate grouping and ordering results based on specific criteria.

Uploaded by

Laurence
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
5 views2 pages

MS SQL Query Examples and Analysis

The document contains SQL queries related to customer and part data in MS SQL Server. It includes examples of selecting customer information, calculating available credit, counting items, and summarizing balances and credit limits. The queries also demonstrate grouping and ordering results based on specific criteria.

Uploaded by

Laurence
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

ANSWERS IN LECTURE 6 – MS SQL SERVER QUERIES – CONT.

-- Example 15
SELECT CustomerNum, CustomerName, (CreditLimit-Balance)
AS [Available Credit]
FROM Customer

-- Example 16
SELECT CustomerNum, CustomerName, (CreditLimit-Balance)
AS [Available Credit]
FROM Customer
WHERE (CreditLimit-Balance) >= 5000

-- Example 17
SELECT Description, (OnHand*Price) AS [On-Hand Value]
FROM Part
WHERE Class = 'HW'

-- Example 18
SELECT Count(Description) AS CountHW
FROM Part
WHERE Class = 'HW'

-- Example 19
SELECT Count(*) AS CountRepNum35
FROM Customer
WHERE RepNum = 35

-- Example 20
SELECT Count(CustomerNum) AS CountCustomer,
Sum(Balance) AS TotalBalance
FROM Customer

-- Example 21
SELECT Sum(Balance) AS TotalBalance,
Avg(Balance) AS AverageBalance,
Max(Balance) AS MaximumBalance,
Min(Balance) AS ManimumBalance
FROM Customer

-- Example 22
SELECT Sum(CreditLimit) AS [Total CreditLimit],
Avg(CreditLimit) AS [Average CreditLimit],
Max(CreditLimit) AS [Largest CreditLimit],
Min(CreditLimit) AS [Smallest CreditLimit]
FROM Customer

-- Example 23
SELECT RepNum, Count(CustomerNum) AS CountCustomer,
Avg(Balance) AS AverageBalance
FROM Customer
GROUP BY RepNum
ORDER BY Count(CustomerNum)
-- Example 24
SELECT Warehouse, Sum(OnHand) AS TotalUnits
FROM Part
GROUP BY Warehouse
ORDER BY Sum(OnHand)

You might also like