fundemental of data systems
Durga-prasad_01
Chapter4fdsm.pptxChapter 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 <columns>
FROM <table>
WHERE <predicates identifying rows to be included>;
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
Relational Query Optimizer Concepts
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;
