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:
- SELECT statement
- Search condition (WHERE)
- Computed fields
- Intro to Aggregation functions
- Sorting function
- 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.
SELECT statement is used to select data from a database. The data returned is displayed as a table1.
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
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
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.
-- 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
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'
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
We rename the newly made column using
AS command and the column is called
Available_credit. This is called Alias.
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 meaning2.
The commonly used SQL aggregate functions are
Count is a function that returns the number of rows that matches a specified criteria3.
-- how many customers does sales rep 35 represent? -- SELECT COUNT(CustomerNum) AS Total_customers FROM Customer WHERE RepNum=35
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.
AVG function returns the average value of a numeric column3.
-- what is the average balance of all customers of sales rep 35? -- SELECT AVG(Balance) AS Average_balance FROM Customer WHERE RepNum=35
-- find the total balance of all the customers living in Grove -- SELECT SUM(Balance) AS total_balance FROM Customer WHERE City='Grove'
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
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
DESC to specify whether you want to sort in ascending order (
ASC) or descending order (
DESC). If nothing is specified, the default is
-- 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
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
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.
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
-- 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
And this is
-- 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
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.
And I'll see you in the next part! Ciao~