Professor Adams' Lecture Materials

Learning Objective 4-03: Describe how to query using Structured Query Language (SQL).

Structured Query Language (SQL)

SQL (usually pronounced "Sequel") stands for Structured Query Language
A computer language designed to query data in a relational database
Based on relational algebra and allows a user to query and update the database
Queries allow the user to access, read, and report on desired data but the responsibility of making physical changes to the relational database belongs to the Database Management System
SQL uses these operators and clauses:
- INSERT operator: create new record
- SELECT operator: begin a query, most useful operator in SQL, tells the query which columns (or attributes) of a table should be included in the query
- UPDATE operator: update record
- DELETE operator: delete existing record

SQL Example 1

Given the attributes in the Customer table, how do you use a query to find the salesperson for each customer?

FROM clause

added to the SELECT statement
indicates the name of table(s) from which to retrieve data

SELECT Customer#, Name, SP#

FROM Customer;

We will get the following query result:

SQL Example 2

How do you retrieve all cash receipt information for customer C-2 from the “Cash Receipt” table?

WHERE Clause

States the criteria that must be met to be shown in the query result.

SELECT * /*asterisk indicating all columns should be selected*/

FROM [Cash Receipt]

WHERE [Customer Number] = ’C-2’

We will get the following query result:

Search criteria using relational operator- the BETWEEN operator and the LIKE operator.

SQL Example 3

Assume you would like to use a query to find the salesperson for each customer, and you would like to show the name of each salesperson as part of the result.

SELECT Customer#, Name, SP#, SP_Name

FROM Customer, Salesperson

WHERE Customer.SP#=Salesperson. SP#;

We will get the following query result:

Include WHERE to link 2 tables, SP# is a foreign key

SQL Example 4

Refer to the Cash Receipt table in SQL Example #2. Assume you would like to know the total cash receipt amount from each customer?

GROUP BY operator

Is used with aggregate functions on the query results based on one or more columns.

SELECT [Customer Number], SUM(Amount)

FROM [Cash Receipt]

GROUP BY [Customer Number];

We will get the following query result:

SQL Example 5

Refer to the Cash Receipt table in SQL Example #2., if we use the following SQL commands instead, the amount of cash receipt would be ordered in ascending amount (ASC) or descending amount (DESC).

ORDER BY Clause

Identifies which columns are used to sort the resulting data
Without it, the order of rows returned by a SQL query will not be defined

SELECT *

FROM [Cash Receipt]

WHERE [Customer Number] = ‘C-2’

ORDER BY Amount ASC;

We will get the following query result:

Structured Query Language (SQL)

SELECT DISTINCT

You can retrieve each customer number once from the Cash Receipt table

SELECT DISTINCT [Customer Number]

FROM [Cash Receipt];

BETWEEN

Finding out cash receipt entries made in July.

FROM [Cash Receipt]
WHERE Date BETWEEN ‘01-JUL-2020’ AND ‘31-JUL-2020’;

Membership Operator (IN)

Allows you to test whether a data value matches the specified target values. For example, assume you would like to know the total cash receipt amount from customers C-1 and C-2.

SELECT [Customer Number], SUM(Amount)

FROM [Cash Receipt]

WHERE [Customer Number] IN (‘C-1’, ‘C-2’)

GROUP BY [Customer Number];

Six Relational Operators in SQL

Aggregate Functions in SQL

Page updated

Google Sites

Report abuse