Basic DB Using SQL Server (Part 3) - retrieve data using SQL

In this post, we are going to learn how to retrieve data in a relational database by using Structured Query Language (SQL).

Topics that we are going to cover:

1. SELECT statement
2. Search condition (WHERE)
3. Computed fields
4. Intro to Aggregation functions
5. Sorting function
6. Intro to Joining

_{In the queries below, we are going to use tables from PremierProduct database. The SQL script to create PremiereProduct database, create tables and relations, and add records to the tables can be found here.}

1) SELECT statement

The SELECT statement is used to select data from a database. The data returned is displayed as a table¹.

SELECT select_list [INTO new_table]
FROM table_source [WHERE search_condition]
[GROUP BY group_by_expression]
[HAVING search_condition]
[ORDER BY order_expression [ASC | DESC]]

_{note: the query expressions inside the [ ] are optional. We are going to go over these expressions later.}

The table that we are going to use is called Customer and it looks like this...

-- List all information from the table Customer --
SELECT * FROM Customer

-- List the number, name, balance, and credit limit from the table Customer --
SELECT CustomerNum, CustomerName, Balance, CreditLine FROM Customer

Distinct

Sometimes, we just want to list distinct values from a column because a column can have a lot of duplicate values, for example, the City column contains duplicate city names.

-- list distinct city from the table Customer --
SELECT DISTINCT City FROM Customer

2) Search condition

We are going to use WHERE clause to filter records and get only the ones that fulfill our condition(s).

-- find the Name of Customer number 148 --
SELECT CustomerName FROM Customer 
WHERE CustomerNum=148

Another table that we are going to use is called Part and it looks like this.

AND / OR conditions

-- List the description, on-hand value, and warehouse number for parts 
-- that have more than 10 units on-hand AND are located in warehouse 3. --
SELECT Description, OnHand, Warehouse FROM Part
WHERE OnHand>10 AND Warehouse=3

-- List the description, on-hand value, and warehouse number for parts 
-- that have more than 10 units on-hand OR are located in warehouse 3. --
SELECT Description, OnHand, Warehouse FROM Part
WHERE OnHand>10 OR Warehouse=3

-- List the Part number, Description, and Price where the price is between $100 and $400. --
SELECT PartNum, Description, Price FROM Part
WHERE Price>100 AND Price<400

NOT

To select records that do NOT have a certain property can be written in 3 ways.

-- list all information of all the customers who are not from the city Grove --
SELECT * FROM Customer
WHERE City!='Grove'

SELECT * FROM Customer
WHERE City<>'Grove'

SELECT * FROM Customer
WHERE NOT City='Grove'

3) Computed fields

A Computed field is a field that is the result of a calculating using one or more existing fields.

Arithmetic operators (e.g. +, -, *, /) and parentheses can be used.

-- list the number, name, and available credit for all customers --
SELECT CustomerNum, CustomerName, (CreditLine - Balance) AS Available_Credit FROM Customer

As you can see, we don't have Available_credit column, so we have to make one. The available credit is the subtraction of CreditLine and Balance.

We rename the newly made column using AS command and the column is called Available_credit. This is called Alias.

4) Intro to Aggregation Functions

Aggregation Functions are functions where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning².

The commonly used SQL aggregate functions are AVG, COUNT, MIN, MAX, and SUM.

COUNT

Count is a function that returns the number of rows that matches a specified criteria³.

-- how many customers does sales rep 35 represent? --
SELECT COUNT(CustomerNum) AS Total_customers FROM Customer
WHERE RepNum=35

The AS command is optional. We use COUNT on one of the column (in the example, we use CustomerNum column) to count how many rows fit the condition.

AVERAGE

AVG function returns the average value of a numeric column³.

-- what is the average balance of all customers of sales rep 35? --
SELECT AVG(Balance) AS Average_balance FROM Customer
WHERE RepNum=35

SUM

-- find the total balance of all the customers living in Grove --
SELECT SUM(Balance) AS total_balance FROM Customer
WHERE City='Grove'

Grouping

Grouping means creating groups of records that share some common characteristics. We can use Aggregation functions together with grouping. The calculations will affect groups of records.

-- what is the average balance for all customers of each sales rep? --
SELECT RepNum, AVG(Balance) AS Average_balance FROM Customer
GROUP BY RepNum

5) Sorting function

ORDER function helps us list the records in query results in a particular way. Sorting records can be performed on more than one field.

-- list all customers and sort the output alphabetically by customer name --
SELECT * FROM Customer
ORDER BY CustomerName ASC

You can add ASC or DESC to specify whether you want to sort in ascending order (ASC) or descending order (DESC). If nothing is specified, the default is ASC.

-- list all customers and sort the output by sales rep num
-- within the sales rep num, sort the output by customer name --
SELECT * FROM Customer
ORDER BY RepNum, CustomerName

6) Intro to Joining

Sometimes, it is necessary to join the tables based on matching fields to query data from different tables.

Another table that will be used is called Rep and it looks like this.

-- list customer's number and name, along with the number, last and first name of each customer's sale rep --
SELECT Customer.CustomerNum, Customer.CustomerName, Rep.RepNum, Rep.LastName, Rep.FirstName
FROM Rep JOIN Customer ON Rep.RepNum = Customer.RepNum

We join Rep table with Customer table on the common column they both have; RepNum. However, when we SELECT the columns, we have to specify which table that column is from; e.g. Customer.CustomerNum.

However, specifying which table it is using the table name can be too long. We can shorten it by making a nickname for the table(s).

SELECT C.CustomerNum, C.CustomerName, R.RepNum, R.LastName, R.FirstName
-- we are making a nickname here --
-- Table Rep is called R and table Customer is called C --
FROM Rep R JOIN Customer C ON R.RepNum = C.RepNum

More examples on JOIN (with WHERE)

-- list the customer's number and name, along with the number, last and first name of the sales rep
-- for customer where credit limit is $10,000 --
SELECT C.CustomerNum, C.CustomerName, R.RepNum, R.LastName, R.FirstName
FROM Rep R JOIN Customer C ON R.RepNum = C.RepNum
WHERE C.CreditLine = 10000

Let's join more than 2 tables! The additional tables that we are going to use are Orders and OrderLine.

This is Order.

And this is OrderLine

-- For each order, list order number, order date, customer number and name
-- in addition, list the part number, description, number of units and quoted price --
SELECT O.OrderNum, O.OrderDate, C.CustomerNum, C.CustomerName, P.PartNum,
P.Description, P.OnHand AS [Number of Units], OL.QuotedPrice
FROM Customer C JOIN Orders O ON C.CustomerNum=O.CustomerNum
JOIN OrderLine OL ON OL.OrderNum=O.OrderNum
JOIN Part P ON P.PartNum=OL.PartNum

We can also use another table to come up with another query command. The table is called CurrentOrders which look like this.

-- another query command that produces the same result --
SELECT CO.OrderNum, O.OrderDate, C.CustomerNum, CO.CustomerName, P.PartNum,
CO.Description, P.OnHand AS [Number of Units], CO.QuotedPrice
FROM CurrentOrders CO JOIN Customer C ON CO.CustomerName=C.CustomerName
JOIN Orders O ON O.OrderNum=CO.OrderNum
JOIN Part P ON P.PartNum=CO.PartNum

EOF!

And so, we have come to the end of part 3! If anyone wishes to try out SQL command, there's another SQL script that will produce another database, tables, and values. The database is called ALEXAMARA and can be accessed here.

Some query problems to exercise with can be found here. There are also queries to those problems in case you want to check if the result is correct.

And I'll see you in the next part! Ciao~