Question SQL

profileSara63
Chapter4RelationalDataRetrievalSQL.ppt
Home>Information Systems homework help>Question SQL

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 <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;

Sample Queries

“How many books did each publisher in Oslo, Norway; Nairobi, Kenya; and Auckland, New Zealand, publish in 2001?”

SELECT PUBNAME, CITY, COUNTRY, COUNT(*)

FROM PUBLISHER, BOOK

WHERE PUBLISHER.PUBNAME=BOOK.PUBNAME

AND ((CITY=’Oslo’ AND COUNTRY=’Norway’)

OR (CITY=’Nairobi’ AND COUNTRY=’Kenya’)

OR (CITY=’Auckland’ AND COUNTRY=’New Zealand’)) AND PUBYEAR=2001

GROUP BY PUBNAME;

Sample Queries

“Which publisher published the book that has the earliest publication year among all of the books that Good Reading Bookstores carries?”

SELECT DISTINCT PUBNAME

FROM BOOK

WHERE PUBYEAR=

(SELECT MIN(PUBYEAR)

FROM BOOK);

Example - World Music Association

Sample Queries

“What is the total annual salary cost for all of the violinists of the Berlin Symphony Orchestra?”

SELECT SUM(ANNSALARY)

FROM MUSICIAN

WHERE ORCHNAME=’Berlin Symphony Orchestra’

AND INSTRUMENT=’Violin’;

Sample Queries

“Make a single list, in alphabetic order of all of the universities attended by the cellists of India.”

SELECT DISTINCT UNIVERSITY

FROM ORCHESTRA, MUSICIAN, DEGREE

WHERE ORCHESTRA.ORCHNAME=MUSICIAN.ORCHNAME

AND MUSICIAN.MUSNUM=DEGREE.MUSNUM

AND INSTRUMENT=’Cello’

AND COUNTRY=’India’

ORDER BY UNIVERSITY;

Sample Queries

“What is the total annual salary cost for all of the violinists of each orchestra located in Canada? Only include in the result those orchestras whose total annual salary for its violinists is in excess of $150,000.”

SELECT ORCHNAME, SUM(ANNSALARY)

FROM ORCHESTRA, MUSICIAN

WHERE ORCHESTRA.ORCHNAME=MUSICIAN.ORCHNAME AND COUNTRY=’Canada’

AND INSTRUMENT=’Violin’

GROUP BY ORCHNAME

HAVING SUM(ANNSALARY)>150,000;

Sample Queries

“What is the name of the most highly paid pianist?”

SELECT MUSNAME

FROM MUSICIAN

WHERE INSTRUMENT=’Piano’

AND ANNSALARY=

(SELECT MAX(ANNSALARY)

FROM MUSICIAN

WHERE INSTRUMENT=’Piano’);

Sample Queries

“What is the name of the most highly paid pianist of any orchestra in Australia?”

SELECT MUSNAME

FROM MUSICIAN, ORCHESTRA

WHERE MUSICIAN.ORCHNAME=ORCHESTRA.ORCHNAME

AND INSTRUMENT=’Piano’

AND COUNTRY=’Australia’

AND ANNSALARY=

(SELECT MAX(ANNSALARY)

FROM MUSICIAN, ORCHESTRA

WHERE MUSICIAN.ORCHNAME=ORCHESTRA.ORCHNAME

AND INSTRUMENT=’Piano’

AND COUNTRY=’Australia’);

Example - Lucky Rent-A-Car

Sample Queries

“List the manufacturers whose names begin with the letter C or the letter D and that are located in Japan.”

SELECT MANUFNAME

FROM MANUFACTURER

WHERE (MANUFNAME LIKE ‘C%’

OR MANUFNAME LIKE ‘D%’)

AND COUNTRY=’Japan’;

Sample Queries

“What was the average mileage of the cars that had tune-ups in August 2003?”

SELECT AVG(MILEAGE)

FROM MAINTENANCE

WHERE PROCEDURE=’Tune-Up’

AND DATE BETWEEN ‘AUG-01-2003’ AND ‘AUG-31-2003’;

Sample Queries

“How many different car models do manufacturers in Italy make?”

SELECT COUNT(DISTINCT MODEL)

FROM MANUFACTURER, CAR

WHERE MANUFACTURER.MANUFNAME=CAR.MANUFNAME

AND COUNTRY=’Italy’;

Sample Queries

“How many repairs were performed on each car manufactured by Superior Motors during the month of March 2004? Only include cars in the result that had at least three repairs.”

SELECT CAR.CARNUM, COUNT(*)

FROM CAR, MAINTENANCE

WHERE CAR.CARNUM=MAINTENANCE.CARNUM

AND MANUFNAME=’Superior Motors’

AND DATE BETWEEN ‘MAR-01-2004’ AND ‘MAR-31-2004’ GROUP BY CAR.CARNUM

HAVING COUNT(*)>=3;

Sample Queries

“List the cars of any manufacturer that had an oil change in January 2004 and had at least as many miles as the highest mileage car manufactured by Superior Motors that had an oil change that same month.”

SELECT MAINTENANCE.CARNUM

FROM MAINTENANCE

WHERE PROCEDURE=’Oil Change’

AND DATE BETWEEN ‘JAN-01-2004’ AND ‘JAN-31-2004’

AND MILEAGE>=

(SELECT MAX(MILEAGE)

FROM CAR, MAINTENANCE

WHERE CAR.CARNUM, MAINTENANCE.CARNUM

AND PROCEDURE=’Oil Change’

AND DATE BETWEEN ‘JAN-01-2004’ AND ‘JAN-31-2004

AND MANUFNAME=’Superior Motors’);