Chat with us, powered by LiveChat MySQL Questionnaire | excelpaper.org/
+1(978)310-4246 credencewriters@gmail.com
  

Q1: List the key components of the Relational Model.

Q4

# SalesOrdersExample database

# Show me a list of cities, in alphabetical order, where our vendors are located, and include the names of the vendors we work with in each city

# Include your SQL code below

Q5

# SchoolSchedulingDatabase

# What kinds of titles are associated with our faculty? Provide a distinct list.

# Include your SQL code below

BUS 205 – Applied Business Technology
SQL Queries for Mere Mortals: A Hands-On Guide to
Data Manipulation in SQL
John L Viescas
4th Edition
Chapter 4:
Creating a Simple
Query
Saving Your SQL Queries!
• Save your MySQL Workbench queries as .sql files!
– You are taking notes!
– Running only part of the .sql query file
– Commenting out some part of your .sql query file
SELECT
• SELECT
– Lies at the heart of SQL
– The means by which you retrieve information from the tables in your
database
– SELECT is used in conjunction with other keywords and clauses
• Find and view information in an almost limitless number of ways
• Who, what, where, when, or even what if and how many can be
answered with SELECT
• Can get the answers you need to make sound decisions for your
organization
– Can be broken down into three smaller operations:
1. SELECT statement
2. SELECT expression
3. SELECT query
SELECT
• SELECT
– Forms the basis of every question you pose to the database
– Composed of several distinct keywords, known as clauses
SELECT

SELECT

SELECT




FROM



Optional clause
Used to filter the rows returned by the FROM clause
Followed by an expression, technically known as a predicate, that evaluates to true, false, or unknown
Can test the expression by using standard comparison operators, Boolean operators, or special operators
GROUP BY





Also required
Specify the tables or views from which to draw the columns listed in the SELECT clause
WHERE





Absolutely required
Used to specify the columns wanted in the result set of the query
The columns themselves are drawn from the table or view specified in the FROM clause
Optional
For aggregate functions in the SELECT clause to produce summary information
Divides the information into distinct groups
The database system uses any column or list of columns following the GROUP BY keywords as grouping columns
HAVING




Optional
Filters the result of aggregate functions in grouped information
Similar to the WHERE clause in that the HAVING keyword is followed by an expression that evaluates to true, false, or unknown
Again, can test the expression by using standard comparison operators, Boolean operators, or special operators
Data vs. Information

Distinct difference between data and information
– Data is what you store in the database

Information is what you retrieve from the database




What’s the difference?
Data must be processed in a manner that enables it to be interpreted as meaningful information
SELECT statement and its clauses are used to manipulate data
The statement itself returns information


Rows of data
Called a “result set”
Translating a Request into SQL
• A query (request for information from the database)
– Usually in the form of a question or a statement that implies a
question
Select from the
– E.g.
• “Which cities do our customers live in?”
Select city from the customers table
Select city from the customers table
SELECT city FROM customers;
Refining
• Being specific
– “Show me the information on our clients”
• Refined: “List the name, city, and phone number for each of our clients.”
• Look at the table in the FROM clause to see what its column names are
• Or, does the statement word or phrase imply any column names?
– E.g.
– “I need the names and addresses of all our employees.”
– Use the EMPLOYEES table as the source in the FROM
clause
– What columns should be inlcluded in the SELECT clause?
SELECT EmpFirstName, EmpLastName,
EmpStreetAddress, EmpCity,
Answer
EmpState,
EmpZipCode
FROM Employees;
Translating
• Inferring
– “What kind of classes do we currently offer?”
• Again, look at the table in the FROM clause to see what its
column names are
• Again, does the statement word or phrase imply any column
names?
– Translated: kind implies category
– E.g.
SELECT Category
Answer
FROM Employees;
More Information
• Retrieving multiple columns from the DB at once

“Show me a list of our employees and their phone numbers.”
SELECT EmpLastName, EmpFirstName, EmpPhoneNumber
FROM Employees;

“What are the names and prices of the products we carry, and under what category is each item
listed?”
SELECT ProductName, RetailPrice, Category
FROM Products;
– “Show me a list of subjects, the category each belongs to, and the
code we use in our catalog. But I’d like to see the name first, followed
by the category, and then the code.”
– SELECT SubjectName, CategoryID,
SubjectCode
FROM Subjects;
A Shortcut
• Retrieving all of the columns from a table at once

“Show me all of the data from the subjects table”
SELECT SubjectID, CategoryID, SubjectCode, SubjectName,
SubjectDescription
FROM Subjects;
– Typing all of the column names for the query can be tedious and
time consuming
– Instead…
SELECT *
FROM Subjects;
– “*” indicates, “all columns from the table(s)”
– Does not account for changes in the underlying table structure
Eliminating Duplicate Rows

What if duplicate values are returned for a column in a query?


But, I don’t need to see all of those duplicates!
DISTINCT

“Which cities are represented by our bowling league membership?”
SELECT City
FROM Bowlers;


Result set displays 20 occurrences of Bellevue, 7 of Kent, and 14 of Seattle
Hmmm…what to do?
SELECT DISTINCT City
Answer
FROM Bowlers;

What about “Hollywood, CA,” “Hollywood, FL,” “Portland, ME,” and “Portland, OR?”
Sorting Information


Ordering the returned result set
– By definition, the rows of a result set returned by a SELECT statement are unordered
– The sequence in which they appear is typically based on their physical position in the table
– The actual sequence is often determined dynamically by your database system based on how it decides to most
efficiently satisfy your request
Can we order the returned result set the way we want to see it?
– ORDER BY



Sorts the result set of the SELECT statement by one or more columns
Provides the option of ascending or descending sort order for each column
The only columns you can use in the ORDER BY clause are those that are currently listed in the SELECT clause.


When two or more columns are used in an ORDER BY clause


Some vendor implementations allow you to disregard this completely and include any column from any table in the FROM clause
Separate each column with a comma
The SELECT query returns a final result set once the sort is complete
Collating Sequences
• Collating Sequences?
– The manner in which the ORDER BY sorts information depends on
the collating sequence used by your database software
– The collating sequence determines the order of precedence
• For every character listed in the current language character set
• Specified by the operating system
• E.g.
– Identifies whether lowercase letters will be sorted before
uppercase letters, or whether case will even matter
– Question: will ORDER BY sort data typed as “character” data
the same as data typed as “numeric?”
» Lexicographic sort order!
More Sorting

Ordering the returned result set
– “What are the categories of classes we currently offer?”
– Can be restated as
• “List the categories of classes we offer and show them in alphabetical order”
• Select from the and order by
SELECT Category
FROM Classes
ORDER BY Category

• Only sorting by Category because it is the only column in the SELECT clause
• Assumes sort order is ascending
“Show me a list of vendor names in ZIP Code order”
SELECT VendName, VendZipCode
FROM Vendors ORDER BY VendZipCode

“Show me a list of vendor names in DESCENDING ZIP Code order”
SELECT VendName, VendZipCode
FROM Vendors ORDER BY VendZipCode DESC

“Display the names of our employees, including their phone number and ID number, and list them by last name and
first name.”
SELECT EmpLastName, EmpFirstName, EmpPhoneNumber, EmployeeID
FROM Employees
ORDER BY EmpLastName DESC, EmpFirstName ASC
Sample Statements
• Hmmm…




Why would these be useful to me?
Should I study these and try them out?
Why would I want to refer to these later?
Would these come in handy, say, on…
• An IN-CLASS LAB?!
• The HOMEWORK?!
• A QUIZ?!
– Hmmm…
Chapter 4
Questions?
We will use a free DBMS tool called “mySQL Workbench”, which is the official tool for mySQL.
First download the file from the following link:
Download link for Windows users:
https://dev.mysql.com/downloads/workbench/
Click on “No thanks start my download”.
Once the installer file is downloaded, double click on it to start the installation. Throughout the
installation process, click on Next to finish the installation. Once the software is successfully installed,
open the software.
The first thing we do is that we need to create a new connection to connect to the Amazon Web
Services (AWS) database space. Click on the + as shown below.
Enter the following information for the new connection.
Connection name: bus205
Hostname: usf-bus-205-abt.clxwqxh7svky.us-east-2.rds.amazonaws.com
Username: Your USF username
Password: (click on “Store in vault …” to enter the password)
Once all the information is in, Test Connection.
After testing the connection, you should see the following message.
Click on OKs. Now you should see a new connection added to the homepage of Workbench. Double click
on the new connection to connect to AWS database space.
You should see the following.
You are done! Good job! The image above is the work area where we use MySQL to create and query
databases and tables.
We will use a free DBMS tool called “mySQL Workbench”, which is the official tool for mySQL.
First download the dmg file from the following link:
Download link for Mac users:
https://dev.mysql.com/downloads/workbench/
Click on “No thanks start my download”.
If your Mac version is earlier than 10.14, download the 6.3.10 version of MYSQL under
Archive.
Double click on the dmg file that you just downloaded and once opened, drag and drop the file
into the Applications folder.
Once file copied to the Applications folder, double click on MySQLWorkbench to open the
software.
You should see the following window. First we need to create a connection to the cloud-based
server that has the space for creating and querying databases on Amazon Web Services
(AWS). Click on the + sign to create the connection.
In the window that opens enter the following information as shown in the image:
Connection name: bus205
Hostname: usf-bus-205-abt.clxwqxh7svky.us-east-2.rds.amazonaws.com
Username: Your USF username
Password: (click on “Store in vault …” to enter the password)
Once all the information entered, click on Test Connection and make sure the connection is
successfully made.
When you click on Test connection, you should see the following message:
Click OK and close the Manage Server Connections window. You will see that a new
connection is added to the homepage of Workbench.
Now the final step: double click on the new connection to connect to the AWS database
space. You should see the following.
You are done! Good job! The image above is where we use mySQL to create and query
databases and tables.
BUS 205 – Applied Business Technology
SQL Queries for Mere Mortals: A Hands-On Guide to
Data Manipulation in SQL
John L Viescas
4th Edition
Chapter 1:
What is Relational?
Types of Databases
• What is a database?
– An organized collection of data used to model some type of
organization or organizational process
– “A structured set of data held in a computer, especially one that is
accessible in various ways”
– Collecting and storing data in some organized manner for a specific
purpose
Types of Databases
• Types of databases:
1. Operational databases
i.
ii.
iii.
Online Transaction Processing (OLTP)
Collect, modify, and maintain data on a day-to-day basis
Data stored is dynamic
a.
Changes constantly and always reflects up-to-the-minute information
2. Analytical databases
i.
ii.
iii.
Online Analytical Processing (OLAP)
Tracks historical and time-dependent data
Trends, viewing statistical data over a long period, making tactical or
strategic business projections
iv. Data stored is static
a.
b.
c.
Data is never (or very rarely) modified
New data might often be added
Usually gleaned from an operational database
Relational Database Systems
• Sharing data
– Multiple-user RDBMS
– Client/server
• Data warehouse
– Emergence of OLAP
• The Internet
– Sharing data between database systems
– XML and JSON
• NoSQL
– The Relational Model broke!
• The Cloud
– Snowflake, BigQuery, Redshift, etc.
Anatomy of an Relational DB
• Tables





Main structures in the database
Two-dimensional matrix of columns and rows
A single, specific subject
logical order of rows and columns within a table is of absolutely no importance
Object or event
• Columns
– Smallest structure in the database
– Represents a characteristic or attribute of the subject
• Rows
– A unique instance of the subject of a table
– Intersection of a column and a row is like a cell in a spreadsheet
• Atomic – one and only one value
– Each row is unique
• Each row is identified by the primary key value
Anatomy of an Relational DB

Object


Person, place, or thing
Event

Something that occurs at a given point in time
Anatomy of a Relational DB

Keys


primary key
• Primary key consists of one or more columns that uniquely identify each row within a table
• Its value identifies a specific row throughout the entire database
• Column identifies a given table throughout the entire database
• Establish relationships with other tables
foreign key
• Copy of the primary key from the first table inserted it into the second table
• Values of a foreign key must be drawn from the values of the primary key to which it refers
Anatomy of an Relational DB

Views
– A virtual table
• Composed of columns from one or more base tables in the database
• Draws data from base tables rather than storing any data on its own
• Enable one to see the information in your database from many different perspectives
Anatomy of an Relational DB
• Relationships
– One-to-one
• Single row in the first table is related to only one row in the second table
• Single row in the second table is related to only one row in the first table
• Take the primary key of the primary table and inserting it into the secondary table,
where it becomes a foreign key
Anatomy of an Relational DB
• Relationships
– One-to-many
• Single row in the first table can be related to many rows in the second table
• Single row in the second table can be related to only one row in the first table
• Take the primary key of the table on the “one” side and insert it into the table on the “many”
side, where it becomes a foreign key
Anatomy of an Relational DB
• Relationships
– Many-to-many
• Single row in the first table can be related to many rows in the second table
• Single row in the second table can be related to many rows in the first table
• How do you associate a single customer with several entertainers or a specific entertainer
with several customers
• How to do this?
Anatomy of an Relational DB
• Relationships
– Many-to-many
• Associative table (or linking table)
BUS 205 – Applied Business Technology
SQL Queries for Mere Mortals: A Hands-On Guide to
Data Manipulation in SQL
John L Viescas
4th Edition
Chapter 3:
Concise History of
SQL
Origins of SQL

After the Relational Model



How will database users access / use the data in the DB?
IBM began a major research project in the early 1970s called System/R
• Structured English Query Language (SEQUEL)
• Query a relational database using English-style sentences
• Became Structured Query Language (SQL)
Early implementations


1977
• Relational Software
– Built a new relational database product based on SQL
– Shipped its product in 1979
» Beat IBM by two years
– Now Oracle Corporation
UC Berkeley
• Also researching relational database technology
• INGRES ended up using SQL
The SQL Standard
• Multiple vendors
– Multiple “dialects” of SQL
• Whose flavor of SQL should rule them all?
• American National Standards Institute (ANSI)
– Commissioned X3 organization
• Database technical committee X3H2
– Develop a proposal for a SQL standard
– Composed of database industry experts and representatives from almost
every major SQL-based database vendor
– New standard became incompatible with existing major SQL dialects
» Reverted to the original standard
– Ratified X3H2’s standard in 1986
» ANSI X3.135-1986 Database Language SQL (SQL/86)
Evolution of ANSI/ISO Standard

SQL/86 Criticized





In public reviews
The government
Industry pundits
But, at least there was a standard to start with!
ISO 9075:1989 Database Language SQL with Integrity Enhancements
– SQL/89


ANSI X3.168-1989 Database Language Embedded SQL
X3.135-1992 Database Language SQL” and “ISO/IEC 9075:1992 Database
Language SQL
– SQL/92

Structure of the SQL standard
– See table on pp. 81 – 82

And, since…
– SQL:1999, SQL:2003, SQL:2008, SQL:2011, and SQL:2016
Why Should You Learn SQL?
• SQL gives you the skills you need to retrieve information
from any relational database
• Understand the mechanisms behind the graphical query
interfaces found in many RDBMS products
• SQL is found in a wide variety of RDBMS products
– Transfer skills across a variety of platforms
• NoSQL?
– Many use some flavor of SQL
We will use a free DBMS tool called “mySQL Workbench”, which is the official tool for mySQL.
First download the file from the following link:
Download link for Windows users:
https://dev.mysql.com/downloads/workbench/
Click on “No thanks start my download”.
Once the installer file is downloaded, double click on it to start the installation. Throughout the
installation process, click on Next to finish the installation. Once the software is successfully installed,
open the software.
The first thing we do is that we need to create a new connection to connect to the Amazon Web
Services (AWS) database space. Click on the + as shown below.
Enter the following information for the new connection.
Connection name: bus205
Hostname: usf-bus-205-abt.clxwqxh7svky.us-east-2.rds.amazonaws.com
Username: Your USF username
Password: (click on “Store in vault …” to enter the password)
Once all the information is in, Test Connection.
After testing the connection, you should see the following message.
Click on OKs. Now you should see a new connection added to the homepage of Workbench. Double click
on the new connection to connect to AWS database space.
You should see the following.
You are done! Good job! The image above is the work area where we use MySQL to create and query
databases and tables.
BUS 205 – Applied Business Technology
SQL Queries for Mere Mortals: A Hands-On Guide to
Data Manipulation in SQL
John L Viescas
4th Edition
Chapter 5:
Getting More Than
Simple Columns
What Is an Expression?

Expressions





More than simple columns
Some form of operation involving:
• Numbers
• Character strings
• Dates and times
Drawn from:
• Specific columns in a table
• Constant (literal) values
• Combination of both
After the DB completes the operation defined by the expression
• Expression returns a value to the SQL statement for further processing
E.g:
• “What is the total amount for each line item?”
• “Give me a mailing list of employees, last name first”
• “Show me the start time, end time, and duration for each class”
• “Show the difference between the handicap score and the raw score for each bowler”
• “What is the estimated per-hour rate for each engagement?”
• “What if we raised the prices of our products by 5 percent?”
Data Types

Data types associated with database columns:


CHARACTER
• Stores a fixed- or varying- length character string of one or more printable characters
• The characters it accepts are usually based upon the American Standard Code for Information Interchange
(ASCII) or the Extended Binary Coded Decimal Interchange Code (EBCDIC) character sets
• A fixed-length character data type is known as CHARACTER or CHAR
• A varying-length character data type is known as CHARACTER VARYING, CHAR VARYING, or VARCHAR
– The user can define the length of data to be stored in a character data type
– The maximum length that can be specified is defined by your database system
– When the length of a character string exceeds a system-defined maximum (usually 255 or 1,024
characters), a CHARACTER LARGE OBJECT, CHAR LARGE OBJECT, or CLOB data type must be
used
– The alias for CLOB is TEXT or MEMO
NATIONAL CHARACTER
• Same as the character data type except that it draws its characters from ISO-defined foreign language
character sets
• NATIONAL CHARACTER, NATIONAL CHAR, and NCHAR are names used to refer to a fixed-length national
character
• NATIONAL CHARACTER VARYING, NATIONAL CHAR VARYING, and NCHAR VARYING are names used
to refer to a varying-length national character
• When the length of a character string exceeds a system-defined maximum (usually 255 or 1,024 characters),
a NATIONAL CHARACTER LARGE OBJECT, NCHAR LARGE OBJECT, or NCLOB data type must be used
• The alias for NCLOB is NTEXT
Data Types

Data types associated with database columns (cont’d):



BINARY
• Use BINARY LARGE OBJECT (or BLOB) data type to store binary data such as images, sounds, videos, or
complex embedded documents such as word processing files or spreadsheets
• The names used for this data type include BINARY, BIT, and BIT VARYING
EXACT NUMERIC
• Stores whole numbers and numbers with decimal places
• The precision (the number of significant digits) and the scale (the number of digits to the right of the decimal
place) of an exact numeric can be user-defined and can only be equal to or less than the maximum limits
allowed by the database system
• NUMERIC, DECIMAL, DEC, SMALLINT, INTEGER, INT, and BIGINT are all names used to refer to this data
type
• BIGINT has a greater range of values than INTEGER, and INTEGER as having a greater range of values
than a SMALLINT
• Some systems also support a TINYINT data type that can hold a smaller range of values than SMALLINT
APPROXIMATE NUMERIC
• Stores numbers with decimal places and exponential numbers
• Names include FLOAT, REAL, and DOUBLE PRECISION
• Approximate numeric data types don’t have a precision and scale per se
• The SQL Standard does allow a user-defined precision only for a FLOAT
• Any scale associated with these data types is always defined by the database system
• The SQL Standard define the range of values for a DOUBLE PRECISION data type to be greater than those
of a REAL or FLOAT data type
Data Types

Data types associated with database columns (cont’d):
– BOOLEAN
• Stores true and false values, usually in a single binary bit
• Some systems use BIT, INT, or TINYINT to store this data type
– DATETIME
• Dates, times, and combinations of both are stored in this data type
• The SQL Standard defines the date format as year-month-day and specifies time values as being based on a
24-hour clock
• Most database systems allow the use of the more common month/day/year or day/month/year date format
and time values based on an A.M./P.M. clock
• TIMESTAMP data type to store a combination of a date and time
• The names and usages for these data types vary depending on the database system you are using
• Some systems store both date and time in the DATE data type
• Others use TIMESTAMP or a data type called DATETIME
– INTERVAL
• Stores the quantity of time between two datetime values, expressed as either year, month; year/month; day,
time; or day/time
• Not all major database systems support the INTERVAL data type
The CAST Function

CAST

When creating an expression
• Make sure the data types of the columns and literals are compatible with the operation
• Doesn’t make sense to try to add character data to a number
• If the character column or literal contains a number
– Use the CAST function to convert the value before trying to add another number

CAST converts a literal value or the value of a column into a specific data type
• Ensures that the data types of the values in the expression are compatible
• All columns or literals in an expression must be the same: characters, numbers, or datetime values
• All the values used in an expression must generally be compatible in order for the operation defined within
the expression to work properly
– Otherwise: barf!
The CAST Function

CAST (cont’d)

Some restrictions:
• “Don’t put a ten-pound sack in a five-pound box”
– One can define the maximum length of the data to be stored in a character data type
– Converting from one type of character field (for example, VARCHAR) to another character type (such
as CHARACTER)
» Data stored in the original column or literal is larger than the maximum length specified in the
receiving data type:
» Database system will truncate the original character string
• “Don’t put a square peg in a round hole


One can convert a character column or literal to any other data type
» But the character data in the source column or literal must be convertible to the target data type
» E.g. Convert a five-character ZIP Code to a number
» Error if ZIP Code column contains Canadian or European postal codes that have letters
» Database system ignores any leading and/or trailing spaces when it converts a character column value to a
numeric or datetime value
» Most commercial systems support a wide range of character strings that are recognizable as date or time
value
“Ten-pound sack”, version 2

Converting a numeric column’s value to another numeric data type
» The current contents of the convert-from column or literal had better fit in the target data type
» Will get an error if you attempt to convert a REAL value greater than 32,767 to a SMALLINT
» Numbers to the right of the decimal place will be truncated or rounded when converting a number that has
a decimal fraction to an INTEGER or SMALLINT
The CAST Function
• CAST (cont’d)
– Can put “a square peg in a round hole” with certain limitations
• When converting the value of a numeric column to a character
data type:
1. Will convert successfully
2. System will pad it with blanks if its length is shorter than the
defined length of the character column
3. The DB system will raise an error if the character
representation of the numeric value is longer than the defined
length of the character column
Specifying Explicit Values


SELECT statement may use constant values
– Character strings, numbers, dates, times
– In any valid expression used within a SELECT statement
– Categorized as literal values
Character String Literals
– Sequence of individual characters enclosed in single quotes

E.g.
‘This is a sample character string literal.’
‘Here’s yet another! ‘
‘B-28’ ‘Seattle’

Or:
SELECT VendWebPage, ‘is the Web site for’, VendName FROM Vendors
Result:
www.viescas.com
is the Web site for
John Viescas Consulting
Specifying Explicit Values

Numeric Literals
– Consists of an optional sign and a number
– Can include a decimal place
– An exponent symbol, and an exponential number

E.g.
427
–11.253
.554
0.3E–3
Specifying Explicit Values

Datetime Literals



Date literals
Time literals
Timestamp literals
Specifying Explicit Values

Bear in mind


DATE
• The format for a date literal is year-month-day
• Many SQL databases allow the more common month/day/year format (United States)
– Or day/month/year format (most non-U.S. countries)
• Include the DATE keyword before the literal
– Nearly all commercial implementations allow for simply specifying the literal value surrounded by
delimiter characters
» Usually single quotes
» MySQL:
» Requires one to specify a date literal in quotes and then to use the CAST function to
convert the string to the DATE data type
TIME
• The hour format is based on a 24-hour clock
• E.g.
– 07:00 P.M. is represented as 19:00
• Include the TIME keyword before the literal
• Nearly all commercial implementations allow one to simply specify the literal value surrounded by delimiter
characters
– Usually single quotes. I found one case
– Again, MySQL:
» Requires one to specify a time literal in quotes and then to use the CAST function to convert the
string to the TIME data type before you can use it in time calculations
Specifying Explicit Values

Bear in mind (cont’d)





TIMESTAMP
• Simply the combination of a date and a time separated by a single space
• Rules for formatting the date and the time within a timestamp follow the individual rules for date and time
• Include the TIMESTAMP keyword before the literal
• All commercial implementations that support the TIMESTAMP data type allow one to simply specify the literal
value surrounded by delimiter characters
– Usually single quotes
Datetimestamp examples:
‘2007-05-16’
‘2016-11-22′
’21:00′
’03:30:25’
‘2008-09-29 14:25:00’
MySQL:
CAST(‘2016-11-22′ AS DATE)
CAST(’03:30:25’ AS TIME)
CAST(‘2008-09-29 14:25:00’ AS DATETIME)
SQL Standard: precede each literal with a keyword indicating the desired value
Most database systems rarely support these keywords in this particular context and require only the character
string portion of the literal
Types of Expressions

Three types of expressions when working with SQL statements:

CONCATENATION
• Combining two or more character columns or literals into a single character string
• Concatenate two character items by placing a single item on either side of the concatenation operator

E.g. #1
• Expression: ColumnOne || ColumnTwo
• Result:
ContentsOfColumnOneContentsOfColumnTwo
E.g. #2
• Expression: ‘Mike’ || ‘ ‘ || ‘Hernandez’
• Result:
Mike Hernandez
E.g. #3
• Expression: CompanyName || ‘ is based in ‘ || City



• Result:
DataTex Consulting Group is based in Seattle
E.g. #4
• Expression: EntStageName || ‘ was signed with our agency on ‘ || CAST(DateEntered as
CHARACTER(10))

Result:
Modern Dance was signed with our agency on 1995-05-16
Types of Expressions

Three types of expressions when working with SQL statements:

MATHEMATICAL
• Adding, subtracting, multiplying, and dividing numeric columns or literals
• Common mathematical functions to calculate
– Absolute value
– Modulus
– Exponentiation
– Logarithms
Function
ABS()
MOD(, )
LN()
EXP()
POWER(, )
SQRT()
FLOOR()
CEIL() CEILING()
Purpose
Returns the absolute value of the expression
Returns the remainder produced by dividing the dividend by the divisor
Returns the natural logarithm of the expression
Returns the value of the natural logarithm raised to the power of the
expression
Returns the value of the base raised to the power of the exponent
Returns the square root of the expression
Returns the largest integer less than or equal to the expression
Returns the smallest integer greater than or equal to the expression
WIDTH_BUCKET(, , , )
between 0 and the bucket count + 1 indicating where in the range the first
argument resides
Types of Expressions

Three types of expressions when working with SQL statements:

MATHEMATICAL, (cont’d)
• Most RDBMS programs provide the previously outlined operations
– Plus, a wide array of scientific, trigonometric, statistical, and mathematical functions
• The order in which the four basic mathematical operations are performed
– Order of precedence
» Equal precedence to multiplication and division
» Specifies that they should be performed before any addition or subtraction
» Slightly contrary to the order of precedence learned back in school
» Multiplication is done before division, division before addition, and addition before
subtraction
» Matches the order of precedence used in most modern programming languages
– Mathematical expressions are evaluated from left to right
– Make extensive use of parentheses in complex mathematical expressions
» Ensure that they evaluate properly
– Use an optionally signed numeric value, a mathematical operator, and another optionally signed
numeric value to create the expression
Types of Expressions

Three types of expressions when working with SQL statements:

MATHEMATICAL, (cont’d)

Examples of mathematical expressions using numeric literal values, column references, and combinations of both:
25 + 35
–12 * 22
RetailPrice * QuantityOnHand
TotalScore / GamesBowled
RetailPrice – 2.50
TotalScore / 12

Exressions / results
Expression:
Result:
(11 – 4) + (12 * 3)
43
Expression
Result
(23 * 11) + 12
265
Expression
Result
23 * (11 + 12)
529
Expression
Result
(12 * (3 + 4)) – (24 / (10 + (6 – 4)))
82
(3 + 4) = 7
(12 * 7) = 84 12
(6 − 4) = 2
(10 + 2) = 12
(24 / 12) = 2
84 − 2 = 82 84
times the result of the first operation
10 plus the result of the third operation
24 divided by the result of the fourth operation
minus the result of the second operation
Date and Time Arithmetic

Date Expressions

Many RDBMS programs differ in the way they implement these operations
• Some allow one to define these operations as you would in a mathematical expression
• Others require you to use special built-in functions for these tasks

The SQL Standard:





DATE plus or minus INTERVAL yields DATE
DATE minus DATE yields INTERVAL
INTERVAL plus DATE yields DATE
INTERVAL plus or minus INTERVAL yields INTERVAL
INTERVAL times or divided by NUMBER yields INTERVAL
Date and Time Arithmetic

Date Expressions, cont’d




Can subtract only a DATE from a DATE or add only a DATE to an INTERVAL
When using a column reference, make certain it is based on a DATE or INTERVAL data type, as
appropriate
• Might have to use the CAST function
• Many database systems convert the column’s data type for you automatically
Only a few commercial systems support the INTERVAL data type
• Many allow you to use an integer value (such as SMALLINT or INT) to add to or subtract from
a date value
– Like adding and subtracting days
– “What is the date nine days from now?” and “What was the date five days ago?”
• When subtracting a date from another date = interval between the two dates
E.g.
‘2017-05-16’ – 5
‘2017-11-14’ + 12
ReviewDate + 90
EstimateDate – DaysRequired
‘2017-07-22’ – ‘2017-06-13′
ShipDate – OrderDate
Date and Time Arithmetic

Time Expressions

Date and time expressions are very similar
• The same rules and restrictions that apply to a date expression also apply to time expressions

The SQL Standard:
• TIME plus or minus INTERVAL yields TIME
• TIME minus TIME yields INTERVAL
• INTERVAL plus or minus INTERVAL yields INTERVAL
• INTERVAL times or divided by NUMBER yields INTERVAL
Can subtract only a TIME from a TIME or add only a TIME to an INTERVAL

Date and Time Arithmetic

Time Expressions, cont’d


DATETIME (some RDBMSs)
• The time portion of the value is stored as a fraction of a day accurate at least to seconds
• Can usually add or subtract a decimal fraction value to a datetime value
• E.g.
– 0.25 is 6 hours (one-fourth of a day)
E.g.
’14:00′ + ’00:22′
’19:00′ – ’16:30′
StartTime + ’00:19’
StopTime – StartTime

Most database systems do not implement the SQL Standard’s specification for time expressions
exactly
• Many only partially support the specification for the date expression
• All database systems provide one or more functions that allow you to work with dates and
times
Using Expressions in a SELECT Clause

One of the most important concepts you’ll learn in this book!



E.g.
• Create a calculated column in a query
• Search for a specific column value
• Filter the rows in a result set
• Connect two tables in a JOIN operation
SELECT clause examples
• Concatenate first and last names
• Calculate the total price of a product
• Determine how long it took to complete a project
• Specify a date for a patient’s next appointment
Working with a Concatenation Expression


Enhance the readability of the information contained in the result set
“Show me a current list of our employees and their phone numbers.”
SELECT EmpFirstName || ‘ ‘ || EmpLastName, ‘Phone Number: ‘ || EmpPhoneNumber
FROM Employees

Result:
Mary Thompson
Phone Number: 555-2516
Using Expressions in a SELECT Clause

Naming the Expression

When using an expression in a SELECT clause
• Result set includes a new column that displays the result of the operation defined in the expression
• Known as a calculated (or derived) column
• E.g.: Result set for the following SELECT statement will contain three columns
– two “real” columns and one calculated column:
SELECT EmpFirstName || ‘ ‘ || EmpLastName, EmpPhoneNumber, EmpCity
FROM Employees

– Real columns are EmpPhoneNumber and EmpCity
– Calculated column is derived from the concatenation at the beginning of the SELECT clause
Can optionally provide a name for the new column by using the AS keyword
SELECT EmpFirstName || ‘ ‘ || EmpLastName AS EmployeeName,
EmpPhoneNumber, EmpCity
FROM Employees
– Or, “Give me a list of employee names and their dates of birth.”
SELECT EmpFirstName || ‘ ‘ || EmpLastName
EmpDOB AS DateOfBirth
FROM Employees
AS EmployeeName,
Using Expressions in a SELECT Clause

Working with a Mathematical Expression

Possibly the most versatile of the three types of expressions
• Used frequently
• E.g.
– Calculate a line item total
– Determine the average score from a given set of tests
– Calculate the difference between two lab results
– Estimate the total seating capacity of a building
• “Display for each agent the agent name and projected income (salary plus commission),
assuming each agent will sell $50,000 worth of bookings.”
SELECT AgtFirstName || ‘ ‘ || AgtLastName || ‘ has a projected
income of ‘ || CAST(Salary + (50000 * CommissionRate) AS
CHARACTER) AS ProjectedIncome
FROM Agents
– Parenthesis: commission rate should be multiplied by 50,000 and then add the salary,
not add 50,000 to the salary and then multiply by the commission rate
Using Expressions in a SELECT Clause

Working with a Date Expression
– Similar to using a mathematical expression
• Simply adding or subtracting values
• E.g.
– Calculate an estimated ship date
– Project the number of days it will take to finish a project
– Determine a follow-up appointment date for a patient
• “How many days did it take to ship each order?”
SELECT OrderNumber, CAST(ShipDate – OrderDate AS INTEGER)
DaysToShip FROM Orders
AS
• “What would be the start time for each class if we began each class ten minutes later than the
current start time?”
SELECT StartTime, StartTime + ’00:10’ AS NewStartTime
FROM Classes
Using Expressions in a SELECT Clause

A Brief Digression: Value Expressions

SQL Standard refers to
• Column reference
• Literal value
• Expression
Collectively as a value expression
Using Expressions in a SELECT Clause

A Brief Digression: Value Expressions, cont’d
– Components of a value expression:
• Syntax begins with an optional plus or minus sign
– Use either of these signs when the value expression to return should include a signed numeric value
– The value itself can be a
» Numeric literal
» Value of a numeric column
» A call to a function that returns a numeric value
» The return value of a mathematical expression
» Cannot use the plus or minus sign before an expression that returns a character or datetime
data type
• The first list in the figure also includes (Value Expression)
– Means that a complex value expression comprised of other value expressions that include
concatenation or mathematical operators of their own can be used
– The parentheses force the database system to evaluate this value expression first
• The next item in the syntax is a list of operators
– The type of expression one can use at the beginning of the syntax determines which operators that
can be selected from this list
• No, you’re not seeing things:
– The value Expression does appear after the list of operators as well
– The fact that one can use other value expressions within a value expression allows you to create very
complex expressions
Using Expressions in a SELECT Clause

A Brief Digression: Value Expressions, cont’d



A value expression returns a value that is used by some component of an SQL statement
This new syntax gives you the flexibility to use literals, column references, expressions, or any
combination of these within a single SELECT statement
One can optionally name value expressions with the AS keyword
That “Nothing” Value: Null

Introducing Null
– A table consists of columns and rows
– Each column represents a characteristic of the subject of the table
– Each row represents a unique instance of the table’s subject
• A row is one complete set of column values
– Each row contains exactly one value from each column in the table
– Sometimes a column might not contain a value for a particular row in the table
– The absence of a value might adversely affect your SELECT statements and value
expressions
That “Nothing” Value: Null

Introducing Null, cont’d


Null represents a missing or an unknown value
A Null does not represent
• Zero
– A zero can represent
» The state of an account balance
» The current number of available first-class ticket upgrades
» The current stock level of a particular product
• A character string of one or more blank spaces
– A character string of one or more blank spaces is guaranteed to be meaningless to most of us
» Definitely meaningful to SQL
» A blank space is a valid character as far as SQL is concerned
» A character string composed of three blank spaces (‘ ‘) is just as legitimate as a character string
composed of several letters (‘a character string’)
• A “zero-length” character string
– A zero-length string
» Two consecutive single quotes with no space in between (’’)
» Can be meaningful under certain circumstances
» In an employee table, for example, a zero-length string value in a column called
MiddleInitial might represent that a particular employee does not have a middle initial in
her name
That “Nothing” Value: Null

Introducing Null, cont’d


In the CustCounty column
• Each blank cell represents a missing or unknown county name for the row in which it appears
– Null
In order to use Nulls correctly
• One must understand why they occur in the first place
– Missing values are commonly the result of human error
– Unknown values may appear in a table for a variety of other reasons
» A specific value you need for a column is as yet undefined
» A table might contain truly unknown values
» No values apply to a particular row
» Slim difference between “does not apply” and “is not applicable.”
That “Nothing” Value: Null

The Problem with Nulls
– The major drawback of Nulls is their adverse effect on mathematical operations
• Any operation involving a Null evaluates to Null
• Logically reasonable
– If a number is unknown, then the result of the operation is necessarily unknown
– E.g. #1
(25 *
(Null
(25 *
(25 *

3) + 4 = 79
* 3) + 4 = Null
Null) + 4 = Null
3) + Null = Null
E.g. #2
SELECT ProductID, ProductDescription, Category,
Price * QuantityOnHand AS TotalValue
FROM Products
Price, QuantityOnHand,
Chapter 5
Questions?
BUS 205 – Applied Business Technology
SQL Queries for Mere Mortals: A Hands-On Guide to
Data Manipulation in SQL
John L Viescas
4th Edition
Chapter 6:
Filtering your Data
Refining What You See Using WHERE

The WHERE Clause
– What if you want to find only the rows that apply to
• A specific person
• A specific place
• A particular numeric value
• A range of dates?
– E.g.
• “Who are our customers in Seattle?”
• “Show me a current list of our Bellevue employees and their phone numbers.”
• “What kind of music classes do we currently offer?”
• “Give me a list of classes that earn three credits.”
• “Which entertainers maintain a Web site?”
• “Give me a list of engagements for the Caroline Coie Trio.”
• “Give me a list of customers who placed orders in May.”
• “Give me the names of our staff members who were hired on May 16, 1985.”
• “What is the current tournament schedule for Red Rooster Lanes?”
• “Which bowlers are on team 5?”
Refining What You See Using WHERE

The WHERE Clause, cont’d
– The WHERE clause in a SELECT statement is used to filter data the statement draws from a table
• Contains a search condition that it uses as the filter
• Provides the mechanism needed to select only the rows needed or exclude the ones not needed
• The database system applies the search condition to each row in the logical table defined by the FROM
clause
• Contains one or more predicates
– Tests one or more value expressions and returns a true, false, or unknown
– Multiple predicates can be included in a search condition using AND or OR Boolean operators
• When the entire search condition evaluates to true for a particular row
– That row will be included in the final result set
Refining What You See Using WHERE

The WHERE Clause, cont’d
– A value expression (see chapter 5) can contain
• Column names
• Literal values
• Functions
• Other value expressions
– When constructing a predicate
• Typically included: at least one value expression that refers to a column from the table(s) in
the FROM clause
• Simplest, most commonly used predicate:
– Compares one value expression (a column) to another (a literal)
– E.g.
SELECT CustLastName
FROM Customers
WHERE CustLastName = ‘Smith’;
– The predicate in the WHERE clause:
» equivalent to asking : “Does the customer last name equal ‘Smith’?”
» When, “yes” (true) for any given row in the Customers table it appears in the result
set
Refining What You See Using WHERE

The WHERE Clause, cont’d
– Five basic predicates:
• COMPARISON
– Use one of the six comparison operators to compare one value expression to another value
expression. The six operators and their meanings are
– = equal to
– not equal to
– < less than
– > greater than
– = greater than or equal to
• BETWEEN (RANGE)
– Tests whether the value of a given value expression falls within a specified range of values
– Specify the range using two value expressions separated by the AND keyword
• IN (MEMBERSHIP)
– Test whether the value of a given value expression matches an item in a given list of values
• LIKE (PATTERN MATCH)
– Tests whether a character string value expression matches a specified character string pattern
• IS NULL
– Determines whether a value expression evaluates to NULL
Refining What You See Using WHERE
• The WHERE Clause, cont’d
– “Select first name and last name from the customers table for those customers who
live in Washington State”
• Is there a restriction in the above statement?
• Which column is going to be tested?
• What value will the column be tested against?
• How will the column be tested?
• E.g.
SELECT CustFirstName, CustLastName
FROM Customers
WHERE CustState
= ‘WA’;
Answer
• The result set will display only those customers who live in the state of
Washington
Defining Search Conditions

Comparison
– The most common type of condition uses a comparison predicate to compare two value
expressions to each other
= ………… Equal To
< ………… Less Than
………… Greater Than
>= ………. Greater Than or Equal To
Defining Search Conditions

Comparing String Values: A Caution


Easy to compare numeric or datetime data
Must pay close attention when you compare character strings
• Compare two seemingly similar strings such as “Mike” and “MIKE”
• The determining factor for all character string comparisons: collating sequence used by the DB system
– Collating sequence also determines how character strings are sorted
– Impacts how you use other comparison conditions as well
– Many vendors have implemented SQL on machines with different architectures
» Languages other than English
– The SQL Standard does not define any default collating sequence for character string sorting or comparison
– How characters are sorted from “lowest” to “highest” depends on
» The database software
» How the software was installed
– ASCII collating sequence
» Places numbers before letters
» All uppercase letters before all lowercase letters
… 0123456789 … ABC … XYZ … abc … xyz …

Some systems offer a case-insensitive option.
» Lowercase a is considered equal to uppercase A
… 0123456789 … {Aa}{Bb}{Cc} … {Xx}{Yy}{Zz} …

DB systems running on IBM mainframe systems use the IBM-proprietary EBCDIC sequence
» All lowercase letters come first
» Then all uppercase letters
» Finally numbers
… abc … xyz … ABC … XYZ … 0123456789 …
Defining Search Conditions

Comparing String Values: A Caution, cont’d
ASCII
No Case Sensitivity
EBCDIC
Company Name
3rd Street Warehouse
5th Avenue Market
Al’s Auto Shop
Ashby’s Cleaners
Zebra Printing
Zercon Productions
allegheny & associates
anderson tree farm
zorn credit services
ztech consulting
Company Name
3rd Street Warehouse
5th Avenue Market
Al’s Auto Shop
allegheny & associates
anderson tree farm
Ashby’s Cleaners
Zebra Printing
Zercon Productions
zorn credit services
ztech consulting
Company Name
allegheny & associates
anderson tree farm
zorn credit services
ztech consulting
Al’s Auto Shop
Ashby’s Cleaners
Zebra Printing
Zercon Productions
3rd Street Warehouse
5th Avenue Market



Can also encounter unexpected results when trying to compare two character strings of unequal length
• “John” and “John ” – (Second “John” has an extra space at the end)
• “Mitch” and “Mitchell.”
The SQL Standard clearly specifies how the database system must handle this
Before your database compares two character strings of unequal length
• Must add the special default pad character to the right of the smaller string until it is the same length as the larger string
• The database then uses its collating sequence to determine whether the two strings are now equal to each other
Defining Search Conditions

Equality and Inequality

“Show me the first and last names of all the agents who were hired on March 14, 1977”
SELECT AgtFirstName, AgtLastName
FROM Agents
WHERE DateHired = ‘1977-03-14’;


Inclusive process
• A given row in the Agents table will be included in the result set only if the current value of the
DateHired column for that row matches the specified date
“Give me a list of vendor names and phone numbers for all our vendors, with the exception of
those here in Bellevue”
SELECT VendName, VendPhoneNumber
FROM Vendors
WHERE VendCity ‘Bellevue’;

Exclusive process
• Use a comparison condition with a “not equal to” operator
• Exclude those vendors based in Bellevue
Defining Search Conditions

Less Than and Greater Than

When rows to be returned are smaller or larger than the comparison value
• This type of comparison employs:
– “Less than” (=)
comparison operators

CHARACTER STRINGS
• Determines whether the value of the first value expression precedes () the value of the second value
expression in your database system’s collating sequence
• E.g.
– Interpret a < c as “Does a precede c?”
NUMBERS
• Determines whether the value of the first value expression is smaller () than the value of the second value
expression
• E.g.
– Interpret 10 > 5 as “Is 10 larger than 5?”
DATES/TIMES
• Determines whether the value of the first value expression is earlier () than the value of the second value
expression
• E.g.
– Interpret ‘2007-05-16’ < ‘2007-12-15’ as “Is May 16, 2007, earlier than December 15, 2007?”
– Dates and times are evaluated in chronological order


Defining Search Conditions

Less Than and Greater Than

“Are there any orders where the ship date was accidentally posted earlier than the order date?”
SELECT OrderNumber
FROM Orders
WHERE ShipDate < OrderDate;
Answer

• Result set will include only those rows from the Orders table where ShipDate is before OrderDate
“Are there any classes that earn more than four credits?”
SELECT ClassID
FROM Classes
WHERE Credits > 4;
Answer

• Result set will include only those rows from the Classes table where Credits is 5 or more
“I need the names of everyone we’ve hired since January 1, 1989”
SELECT AgtFirstName || ‘ ‘ || AgtLastName AS EmployeeName
FROM Agents
WHERE DateHired >= ‘1989-01-01’;
Answer

• Result set will include all hire dates from January 1, 1989, to the present, including agents hired on that date
“Show me a list of products with a retail price of fifty dollars or less”
SELECT ProductName
FROM Products
WHERE RetailPrice = Value2 AND Value1 = 5 AND MyColumn =10 AND MyColumn
Purchase answer to see full
attachment

error: Content is protected !!