SQL for Relational Data Retrieval: Understanding SELECT Queries and Basic Operations, Slides of Database Management Systems (DBMS)

Download SQL for Relational Data Retrieval: Understanding SELECT Queries and Basic Operations and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Relational Data Retrieval: SQL

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.

Data Management

• Data Definition

• Data Manipulation

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.

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.

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.

General Hardware Company Database

(Modified)

S PN U M S PN A ME C OMMP ER CT YEA R HIRE OF FN U M 1 3 7 Baker 1 0 1 9 9 5 1 2 8 4 1 8 6 A dam s 1 5 2 0 0 1 1 2 5 3 2 0 4 Dickens 1 0 1 9 9 8 1 2 0 9 3 6 1 Carlyle 2 0 2 0 0 1 1 2 2 7 (a) SALES P ERSO N T able.

CUS TN U M CUS TN AME S PN U M H QCITY 0121 Main S t. H ardw are 137 N ew Y ork 0839 Jane’s S tores 186 Chicago 0933 ABC H om e S tores 137 Los Angeles 1047 Acme H ardw are S tore 137 Los Angeles 1525 Fred’s To ol S tores 361 Atlanta 1700 X YZ S tores 361 W ashingto n 1826 City H ardw are 137 N ew Y ork 2198 W estern H ardw are 204 N ew Y ork 2267 Central Stores 186 N ew Y ork C US TN U M EMPN U M EMPN A ME TITLE (b) CUST OMER Table. 0 1 2 1 2 7 4 9 8 S m ith C o-O w ner 0 1 2 1 3 0 4 4 1 G arcia C o-O w ner 0 9 3 3 2 5 2 7 0 C hen VP Sales 0 9 3 3 3 0 4 4 1 Levy Sales Mana ger 0 9 3 3 4 8 2 8 5 M or to n President 1 5 2 5 3 3 7 7 9 Baker Sales Mana ger 2 1 9 8 2 7 4 7 0 S m ith President 2 1 9 8 3 0 4 4 1 Jo nes VP Sales 2 1 9 8 3 3 7 7 9 G arcia VP Per so nnel 2 1 9 8 3 5 2 6 8 K aplan Senior A cco un tant (c) Customer EMP LO YEE T able.

PR O DUM N PR O DAME N U NITP RICE 1 6 3 8 6 Wre n c h 1 9 4 4 0 H ma mer 1 21 7.9 5.5 0 2 1 7 6 5 Dr i l 2 4 0 1 3 Sa wl 3 22 6.9 9.2 5 ( d) PROD^ 2 6 7 2 2 P l i U CT Ta be rsle.^ 1 1.5 0

S PN U MPR O UMD N Q U TA TIN Y

1 3 7 1 9 4 4 0 4 7 3 1 3 7 2 4 0 1 3 1 7 0 1 3 7 2 6 7 2 2 6 8 8 1 8 6 1 6 3 8 6 1 7 4 1 8 6 1 9 4 4 0 2 5 2 1 8 6 2 1 7 6 5 1 9 6 1 8 6 2 4 0 1 3 3 0 7 2 0 4 2 1 7 6 5 8 0 9 2 0 4 2 6 7 2 2 7 3 4 3 6 1 1 6 3 8 6 3 7 2 3 6 1 2 1 7 6 5 3 1 1 3 6 1 2 6 7 2 2 2 7 3 ( e) SALES Tab le.

OF FN U TELEPHM OE N SIZ E

1 2 5 3 9 0 -5 1 5 5 - 4 2 7 1 26 0 1 2 2 7 9 0 -5 1 5 5 - 0 3 6 1 04 0 1 2 8 4 9 0 -5 1 5 5 - 7 3 3 1 25 0 1 2 0 9 9 0 -5 1 5 5 - 3 1 0 9 5 8 (f) OF ICEF Tl e .a b

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;

C OMMP ER CT YEA R I REH

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;

S NP U S NMP AE

1 3 7 Bak c r 2 0 4 Dic k s e

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;

S NP U S NMP EA

1 3 7 Bak c r 1 8 6 A md as 2 0 4 Dic ks e 3 6 1 Carlyle

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;

S PN U MS PN A ME C OMMP ER CT

1 3 7 Bak c r 1 0 2 0 4 Dic k e ns 1 0