Assume that Orion Star Sports & Outdoors Co. has engaged you as a consultant to advise them on business strategies to increase sales. Study the data tables from this perspective. The most important tables in this data set are:
+ orders + product_line
+ products + profit
1. Answer the following questions in general:
a) What are the key business questions that can be asked and answered using this data?
b) How does each table relate to answering those questions?
c) How do I have to link the tables in order to be able to answer those questions?
2. For each field in the four tables, answer the following specific questions:
a) Is this a question field, an answer field, or a link field? In other words, is this a key dimension along which I wish to analyze the data (question field), a data item that I want to “slice and dice” along selected dimensions (answer field), or a field that simply helps me link from one table to another?
b) What data type is represented by this field (e.g. integer, text, symbols, currency, etc.)?
c) Are there missing or null values in the data for this field?
d) For range fields (e.g. dates and numbers), what is the range of values in the data? What are the maximum and minimum values?
e) What do you think is the purpose of this field?
f) What questions would you ask the company about this field?
3. Prepare spreadsheets that answer the following questions:
a) What are the top 10 products for orders (by dollar volume)?
b) What are the top 10 countries for orders (by dollar volume)?
c) What are the top 10 selling products by units? By dollar volume?
d) For top two US states and top two countries (excluding the US) in questions 1 and 2, what are the 5 top selling products by units? By dollar volume?
e) Provide the customer ID’s, order dates, and order amounts for all customers who have ordered more than once.
Chapter 4
Relational Data Retrieval: SQL
Fundamentals of Database Systems
Chapter Objectives
Describe SQL as a relational data manipulation language.
Explain that you can create and update relational tables using SQL.
Write SQL SELECT commands to retrieve relational data using a variety of operators, including GROUP BY, ORDER BY, and the built-in functions of AVG, SUM, MAX, MIN, and COUNT.
Chapter Objectives
Write SQL SELECT commands that join relational tables.
Write SQL SELECT subqueries.
Describe a strategy for writing SQL SELECT statements.
Describe the principles of how a relational query optimizer works.
Data Management:
Data Definition
Operationalized with a data definition language (DDL).
Instructs the DBMS software on what tables will be in the database, what attributes will be in the tables, which attributes will be indexed, etc.
Data Management:
Data Manipulation
Refers to the four basic operations that can and must be performed on data stored in any DBMS.
Data retrieval
Data update
Insertion of new records
Deletion of existing records
Requires data manipulation language (DML)
SQL
Structured Query Language
Incorporates both DDL and DML features.
Very heavily used in practice today.
Building the Data Structure
Base tables – actual physical tables in which the data will be stored on the disk.
Created using the CREATE TABLE command.
Deleted using the DROP TABLE command.
Logical View
Also just called a view.
May consist of a subset of the columns of a single table, a subset of the rows of a single table, or both.
May also be the join of two or more base tables.
A mapping onto the base table(s).
Created using the CREATE VIEW command.
Data Manipulation Operations
UPDATE – used for updating existing data.
INSERT – used for inserting new rows in tables.
DELETE – used for deleting existing rows in tables.
Introduction: SQL SELECT
Used for data retrieval.
You specify what data you are looking for rather than provide a logical sequence of steps that guide the system in how to find the data.
Can be run in either a query or an embedded mode.
Command will work with Oracle, MS Access, SQL Server, DB2, Informix, etc.
The Basic SQL SELECT
SELECT
FROM
WHERE ;
General Hardware Company SQL Query Example
The desired attributes are listed in the SELECT clause.
The required table is listed in the FROM clause.
The restriction (predicate) indicating which row(s) is involved is shown in the WHERE clause in the form of an equation.
“Find the commission percentage and year of hire of salesperson number 186.”
SELECT COMMPERCT, YEARHIRE FROM SALESPERSON
WHERE SPNUM=186;
General Hardware Company SQL Query Example, *
The “*” indicates that all attributes of the selected row are to be retrieved.
“Retrieve the entire record for salesperson 186.”
SELECT *
FROM SALESPERSON
WHERE SPNUM=186;
General Hardware Company SQL Query Example
The search argument is nonunique in this query.
“List the salesperson numbers and salesperson names of those salespersons who have a commission percentage of 10.”
SELECT SPNUM, SPNAME
FROM SALESPERSON
WHERE COMMPERCT=10;
General Hardware Company SQL Query Example, No WHERE
For a Relational Algebra Project operation, there is no need for a WHERE clause to limit which rows of the table are included.
“List the salesperson number and salesperson name of all of the salespersons.”
SELECT SPNUM, SPNAME
FROM SALESPERSON;
General Hardware Company SQL Query Example, *
Retrieves an entire table, that is, the query places no restrictions on either the rows or the attributes.
“Retrieve all of the Salespersons.”
SELECT *
FROM SALESPERSON;
Comparisons
In addition to equal (=), the standard comparison operators can be used in the WHERE clause.
Greater than (>)
Less than (<)
Greater than or equal to (>=)
Less than or equal to (<=)
Not equal to (<>)
General Hardware Company SQL Query Example, <
“List the salesperson numbers, salesperson names, and commission percentages of the salespersons whose commission percentage is less than 12.”
SELECT SPNUM, SPNAME, COMMPERCT
FROM SALESPERSON
WHERE COMMPERCT < 12;
General Hardware Company SQL Query Example, >=
“List the customer numbers and headquarters cities of the customers that have a customer number of at least 1700.”
SELECT CUSTNUM, HQCITY
FROM CUSTOMER
WHERE CUSTNUM >= 1700;
General Hardware Company SQL Query Example: AND
“List the customer numbers, customer names, and headquarters cities of the customers that are headquartered in New York and that have a customer number higher than 1500.”
SELECT CUSTNUM, CUSTNAME, HQCITY
FROM CUSTOMER
WHERE HQCITY=’New York’
AND CUSTNUM>1500;
With the AND operator, both conditions have to be satisfied to be included in the result.
General Hardware Company SQL Query Example: OR
“List the customer numbers, customer names, and headquarters cities of the customers that are headquartered in New York or that have a customer number higher than 1500.”
SELECT CUSTNUM, CUSTNAME, HQCITY
FROM CUSTOMER
WHERE HQCITY=’New York’
OR CUSTNUM>1500;
The OR operator really means one or the other or both.
General Hardware Company SQL Query Example: AND, OR
AND is said to be “higher in precedence” than OR.
So all ANDs are considered before any ORs are considered.
“List the customer numbers, customer names, and headquarters cities of the customers that are headquartered in New York or that satisfy the two conditions of having a customer number higher than 1500 and being headquartered in Atlanta.”
General Hardware Company SQL Query Example: AND, OR
“List the customer numbers, customer names, and headquarters cities of the customers that are headquartered in New York or that satisfy the two conditions of having a customer number higher than 1500 and being headquartered in Atlanta.”
SELECT CUSTNUM, CUSTNAME, HQCITY
FROM CUSTOMER
WHERE HQCITY=’New York’
OR CUSTNUM>1500
AND HQCITY=’Atlanta’;
General Hardware Company SQL Query Example: AND, OR
SELECT CUSTNUM, CUSTNAME, HQCITY
FROM CUSTOMER
WHERE (HQCITY=’New York’
OR CUSTNUM>1500)
AND HQCITY=’Atlanta’;
If you really wanted the OR to be considered first, you could force it by writing the query as:
General Hardware Company SQL Query Example: BETWEEN
“Find the customer records for those customers whose customer numbers are between 1000 and 1700, inclusive.”
SELECT *
FROM CUSTOMER
WHERE (CUSTNUM>=1000 AND CUSTNUM<=1700);
Allows you to specify a range of numeric values in a search.
SELECT *
FROM CUSTOMER
WHERE CUSTNUM BETWEEN 1000 AND 1700;
General Hardware Company SQL Query Example: IN
“Find the customer records for those customers headquartered in Atlanta, Chicago, or Washington.”
SELECT *
FROM CUSTOMER
WHERE (HQCITY=’Atlanta’
OR HQCITY=’Chicago’
OR HQCITY=’Washington’);
SELECT *
FROM CUSTOMER
WHERE HQCITY IN (‘Atlanta’, ‘Chicago’, ‘Washington’);
General Hardware Company SQL Query Example: LIKE
“Find the customer records for those customers whose names begin with the letter “A”.”
SELECT *
FROM CUSTOMER
WHERE CUSTNAME LIKE ‘A%’;
“%” character used as a “wildcard” to represent any string of characters.
General Hardware Company SQL Query Example: LIKE
“Find the customer records for those customers whose names have the letter “a” as the second letter of their names.”
SELECT *
FROM CUSTOMER
WHERE CUSTNAME LIKE ‘_a%’;
The single “_” character in the operator LIKE “_a%” specifies that there will be one character followed by “a.”
General Hardware Company SQL Query Example: DISTINCT
“Which cities serve as headquarters cities for General Hardware customers?”
SELECT HQCITY
FROM CUSTOMER;
Eliminate duplicate rows in a query result.
SELECT DISTINCT HQCITY
FROM CUSTOMER;
General Hardware Company SQL Query Example: ORDER BY
“Find the customer numbers, customer names, and headquarters cities of those customers with customer numbers greater than 1000. List the results in alphabetic order by headquarters cities.”
SELECT CUSTNUM, CUSTNAME, HQCITY
FROM CUSTOMER
WHERE CUSTNUM>1000
ORDER BY HQCITY;
Orders the results of an SQL query by one or more specified attributes.
General Hardware Company SQL Query Example: ORDER BY
The default order for ORDER BY is ascending.
The clause can include the term ASC at the end to make ascending explicit.
The clause can include DESC for descending order.
General Hardware Company SQL Query Example: AVG
“Find the average number of units of the different products that Salesperson 137 has sold (i.e., the average of the quantity values in the first three records of the SALES table).”
SELECT AVG(QUANTITY)
FROM SALES
WHERE SPNUM=137;
AVG is a built-in function of SQL.
General Hardware Company SQL Query Example: SUM
SELECT SUM(QUANTITY)
FROM SALES
WHERE SPNUM=137;
“Find the total number of all products that Salesperson 137 has sold.”
SUM is a built-in function of SQL.
General Hardware Company SQL Query Example: MAX
“What is the largest number of units of Product Number 21765 that any individual salesperson has sold?”
SELECT MAX(QUANTITY)
FROM SALES
WHERE PRODNUM=21765;
MAX is a built-in function of SQL.
General Hardware Company SQL Query Example: MIN
SELECT MIN(QUANTITY)
FROM SALES
WHERE PRODNUM=21765;
MIN is a built-in function of SQL.
“What is the smallest number of units of Product Number 21765 that any individual salesperson has sold?”
General Hardware Company SQL Query Example: COUNT
“How many salespersons have sold Product Number 21765?”
SELECT COUNT(*)
FROM SALES
WHERE PRODNUM=21765;
COUNT counts the number of rows that satisfy a set of criteria.
General Hardware Company SQL Query Example: GROUP BY
“Find the total number of units of all products sold by each salesperson.”
SELECT SPNUM, SUM(QUANTITY) FROM SALES
GROUP BY SPNUM;
Used when calculations are to be made on several different groups of rows.
General Hardware Company SQL Query Example: HAVING
“Find the total number of units of all products sold by each salesperson whose salesperson number is at least 150. Only include salespersons whose total number of units sold is at least 5000.”
SELECT SPNUM, SUM(QUANTITY) FROM SALES
WHERE SPNUM>=150
GROUP BY SPNUM
HAVING SUM(QUANTITY)>=5000;
HAVING limits the results of a GROUP BY based on the values calculated for each group with the built-in functions.
The Join
SQL SELECT allows you to join two or more tables.
Two specifications must be made in the SELECT statement.
The tables to be joined must be listed in the FROM clause.
The join attributes in the tables being joined must be declared and matched to each other in the WHERE clause.
A table name qualifier is required when different tables have an attribute with the same name.
General Hardware Company SQL Query Example: Join
“Find the name of the salesperson responsible for Customer Number 1525.”
SELECT SPNAME
FROM SALESPERSON, CUSTOMER
WHERE SALESPERSON.SPNUM=CUSTOMER.SPNUM AND CUSTNUM=1525;
General Hardware Company SQL Query Example: Join
“List the names of the products of which salesperson Adams has sold more than 2,000 units.”
SELECT PRODNAME
FROM SALESPERSON, PRODUCT, SALES
WHERE SALESPERSON.SPNUM=SALES.SPNUM
AND SALES.PRODNUM=PRODUCT.PRODNUM
AND SPNAME=’Adams’
AND QUANTITY>2000;
Subqueries
One SELECT statement is “nested” within another.
Nesting can go on through several levels of SELECT statements with each successive SELECT statement contained in a pair of parentheses.
The innermost SELECT statement is executed first, and its results are then provided as input to the SELECT statement at the next level up.
General Hardware Company SQL Query Example: Subquery
“Find the name of the salesperson responsible for Customer Number 1525.”
SELECT SPNAME
FROM SALESPERSON
WHERE SPNUM=
(SELECT SPNUM
FROM CUSTOMER
WHERE CUSTNUM=1525);
Subquery as an alternative to join.
General Hardware Company SQL Query Example: Subquery
“Which salespersons with salesperson numbers greater than 200 have the lowest commission percentage of any such salesperson?” (We’ll identify salespersons by their salesperson number.)
SELECT SPNUM
FROM SALESPERSON
WHERE SPNUM>200
AND COMMPERCT=
(SELECT MIN(COMMPERCT)
FROM SALESPERSON)
WHERE SPNUM>200);
A subquery is required.
A Strategy for Writing SQL SELECT Commands
Determine what the result of the query is to be and write the needed attributes and functions in the SELECT clause.
Determine which tables of the database will be needed for the query and write their names in the FROM clause.
If the query involves a join, begin constructing the WHERE clause by equating the join attributes from the tables that are in the FROM clause.
Continue filling in the details of the WHERE clause, the GROUP BY clause, and any subqueries.
Relational Query Optimizer: Relational DBMS Performance
The speed with which the required data can be retrieved. Performance regarding joins is a particular problem.
Solutions
Tuning of the database structure, physical database design.
Relational query optimizer software that evaluates each SQL SELECT statement and determines an efficient way to satisfy it.
Relational Query Optimizer: Concepts
All major SQL processors include a query optimizer.
Using a query optimizer, SQL attempts to figure out the most efficient way of answering a query, prior to actually responding to it.
The query optimizer uses the relational catalog, an internal database.
Query Optimizer Considerations
Which attributes have indexes built over them?
How many rows does each table have?
Which attributes are unique?
How many records of a table are really needed for a particular join?
Which join algorithm is best for this query?
Join Algorithms
Nested-loop join
A Cartesian product
One of the two tables is selected for the outer loop and the other for the inner loop.
Each of the records of the outer loop is chosen in succession, and, for each, the inner loop table is scanned for matches on the join attribute.
Join Algorithms
Merge-scan join
More efficient than the nested-loop join.
Can only be used if certain conditions are met.
Each of the two join attributes has to be in sorted order, or
Each of the two join attributes has to have an index built over it.
Use Cases & Examples
Example – Good Reading Bookstores
General Hardware Company Database (Modified)
Sample Queries
“Find the book number, book name, and number of pages of all of the books published by London Publishing Ltd. List the results in order by book name.”
SELECT BOOKNUM, BOOKNAME, PAGES FROM BOOK
WHERE PUBNAME=’London Publishing Ltd.’ ORDER BY BOOKNAME;
Sample Queries
“How many books of at least 400 pages does Good Reading Bookstores carry that were published by publishers based in Paris, France?”
SELECT COUNT(*)
FROM PUBLISHER, BOOK
WHERE PUBLISHER.PUBNAME=BOOK.PUBNAME AND CITY=’Paris’
AND COUNTRY=’France’
AND PAGES>=400;
Sample Queries
“List the publishers in Belgium, Brazil, and Singapore that publish books written by authors who were born before 1920.”
SELECT DISTINCT PUBNAME
FROM PUBLISHER, BOOK, WRITING, AUTHOR
WHERE PUBLISHER.PUBNAME=BOOK.PUBNAME
AND BOOK.BOOKNUM=WRITING.BOOKNUM
AND WRITING.AUTHORNUM=AUTHOR.AUTHORNUM AND COUNTRY IN (‘Belgium’, ‘Brazil’, ‘Singapore’)
AND YEARBORN<1920;
BLCN 534 Fundamentals of Database Systems
SQL Individual Assignment
Assume that Orion Star Sports & Outdoors Co. has engaged you as a consultant to advise them on business strategies to increase sales. Study the data tables from this perspective. The most important tables in this data set are:
+ orders + product_line
+ products + profit
1
. Answer the following questions in general:
a) What are the key business questions that can be asked and answered using this data?
b) How does each table relate to answering those questions?
c) How do I have to link the tables in order to be able to answer those questions?
2. For each field in the four tables, answer the following specific questions:
a) Is this a question field, an answer field, or a link field? In other words, is this a key dimension along which I wish to analyze the data (question field), a data item that I want to “slice and dice” along selected dimensions (answer field), or a field that simply helps me link from one table to another?
b) What data type is represented by this field (e.g. integer, text, symbols, currency, etc.)?
c) Are there missing or null values in the data for this field?
d) For range fields (e.g. dates and numbers), what is the range of values in the data? What are the maximum and minimum values?
e) What do you think is the purpose of this field?
f) What questions would you ask the company about this field?
3. Prepare spreadsheets that answer the following questions:
a) What are the top 10 products for orders (by dollar volume)?
b) What are the top 10 countries for orders (by dollar volume)?
c) What are the top 10 selling products by units? By dollar volume?
d) For top two US states and top two countries (excluding the US) in questions 1 and 2, what are the 5 top selling products by units? By dollar volume?
e) Provide the customer ID’s, order dates, and order amounts for all customers who have ordered more than once.
1
What Will You Get?
We provide professional writing services to help you score straight A’s by submitting custom written assignments that mirror your guidelines.
Premium Quality
Get result-oriented writing and never worry about grades anymore. We follow the highest quality standards to make sure that you get perfect assignments.
Experienced Writers
Our writers have experience in dealing with papers of every educational level. You can surely rely on the expertise of our qualified professionals.
On-Time Delivery
Your deadline is our threshold for success and we take it very seriously. We make sure you receive your papers before your predefined time.
24/7 Customer Support
Someone from our customer support team is always here to respond to your questions. So, hit us up if you have got any ambiguity or concern.
Complete Confidentiality
Sit back and relax while we help you out with writing your papers. We have an ultimate policy for keeping your personal and order-related details a secret.
Authentic Sources
We assure you that your document will be thoroughly checked for plagiarism and grammatical errors as we use highly authentic and licit sources.
Moneyback Guarantee
Still reluctant about placing an order? Our 100% Moneyback Guarantee backs you up on rare occasions where you aren’t satisfied with the writing.
Order Tracking
You don’t have to wait for an update for hours; you can track the progress of your order any time you want. We share the status after each step.
From brainstorming your paper's outline to perfecting its grammar, we perform every step carefully to make your paper worthy of A grade.
Preferred Writer
Hire your preferred writer anytime. Simply specify if you want your preferred expert to write your paper and we’ll make that happen.
Grammar Check Report
Get an elaborate and authentic grammar check report with your work to have the grammar goodness sealed in your document.
One Page Summary
You can purchase this feature if you want our writers to sum up your paper in the form of a concise and well-articulated summary.
Plagiarism Report
You don’t have to worry about plagiarism anymore. Get a plagiarism report to certify the uniqueness of your work.
Free Features $66FREE
Most Qualified Writer
$10FREE
Plagiarism Scan Report
$10FREE
Unlimited Revisions
$08FREE
Paper Formatting
$05FREE
Cover Page
$05FREE
Referencing & Bibliography
$10FREE
Dedicated User Area
$08FREE
24/7 Order Tracking
$05FREE
Periodic Email Alerts
$05FREE
Our Services
Join us for the best experience while seeking writing assistance in your college life. A good grade is all you need to boost up your academic excellence and we are all about it.
On-time Delivery
24/7 Order Tracking
Access to Authentic Sources
Academic Writing
We create perfect papers according to the guidelines.
Professional Editing
We seamlessly edit out errors from your papers.
Thorough Proofreading
We thoroughly read your final draft to identify errors.
Thorough Proofreading
We thoroughly read your final draft to identify errors.
Delegate Your Challenging Writing Tasks to Experienced Professionals
Work with ultimate peace of mind because we ensure that your academic work is our responsibility and your grades are a top concern for us!
We Analyze Your Problem and Offer Customized Writing
We understand your guidelines first before delivering any writing service. You can discuss your writing needs and we will have them evaluated by our dedicated team.
Clear elicitation of your requirements.
Customized writing as per your needs.
We Mirror Your Guidelines to Deliver Quality Services
We write your papers in a standardized way. We complete your work in such a way that it turns out to be a perfect description of your guidelines.
Proactive analysis of your writing.
Active communication to understand requirements.
We Handle Your Writing Tasks to Ensure Excellent Grades
We promise you excellent grades and academic excellence that you always longed for. Our writers stay in touch with you via email.
Thorough research and analysis for every order.
Deliverance of reliable writing service to improve your grades.
