Review Problems SQL

1
Review ProblemsSQL
2
Learning Objectives

• Review problems
• Relational model
• ERD
• Normalization
• SQL command
• SQL queries

3
Review Problems

• Propose for the EMPLOYEE table
• A primary key EMP_NUM
• A superkey EMP_NUM EMP_LNAME
• A candidate key none acceptable
• A foreign key DEPT_CODE
• A secondary key EMP_LNAME EMP_FNAME

4
Review Problems

• The Hudson Engineering Group (HEG) has contacted
  you to create a conceptual model whose
  application will meet the expected database
  requirements for its training program. The HEG
  administrator gives you the following description
  of the training group's operating environment
• The HEG has 12 instructors and can handle up to
  30 trainees per class. HEG offers five "advanced
  technology" courses, each of which may generate
  several classes. If a class has fewer than 10
  trainees in it, it will be canceled. It is,
  therefore, possible for a course not to generate
  any classes during a session. Each class is
  taught by one instructor. Each instructor may
  teach up to two classes or may be assigned to do
  research. Each trainee may take up to two classes
  per session.
• Given this information, draw the E-R diagram for
  HEG with Crows foot model.

5
Review Problems
6
Review Problems

• To keep track of office furniture, computers,
  printers, and so on, the FOUNDIT company uses the
  following table structure
• Attribute name Sample value
• ITEM_ID 2311345-678
• ITEM_DESCRIPTION HP DeskJet 660C printer
• BLDG_ROOM 325
• BLDG_CODE DEL
• BLDG_NAME Dawn's Early Light
• BLDG_MANAGER E. R. Rightonit
•  
• Given this information, create a set of relations
  that meet 3NF requirements.

7
Review Problems
8
Review Problems

• Give the SQL command to create the following
  table

9
Review Problems
10
Creating Table Structure

• CREATE TABLE Vendor(v_code CHAR(4) NOT NULL
  UNIQUE, v_name VARCHAR(35) NOT NULL,
  v_contact VARCHAR(25) NOT NULL, v_areacode
  CHAR(3) NOT NULL, v_phone CHAR(8) NOT NULL,
  v_state CHAR(2) NOT NULL, v_order BOOL NOT
  NULL,UNIQUE (v_name, v_phone), PRIMARY KEY
  (v_code))

11
Creating Table Structure

• CREATE TABLE Product(p_code VARCHAR(10) NOT NULL
  UNIQUE, p_descript VARCHAR(35) NOT NULL,
  p_indate DATE NOT NULL, p_onhand SMALLINT NOT
  NULL, p_min SMALLINT NOT NULL, p_price
  NUMBER(8,2) NOT NULL, p_discount NUMBER(5,2)
  NOT NULL, v_code CHAR(4), PRIMARY KEY
  (p_code), FOREIGN KEY (v_code) REFERENCES
  Vendor ON DELETE RESTRICT ON UPDATE CASCADE)

12
Data Manipulation Commands

• Common SQL Commands

Table 5.3
13
Data Entry and Saving

• Enters data into a table
• Saves changes to disk

INSERT INTO lttable namegt VALUES (attribute 1
value, attribute 2 value, etc.)
COMMIT lttable namesgt
14
Listing Table Contents and Other Commands

• Allows table contents to be listed
• UPDATE command makes data entry corrections
• ROLLBACK command restores database back to
  previous condition if COMMIT hasnt been used
• DELETE command removes table row

SELECT ltattribute namesgt FROM lttable namesgt
15
Queries

• Creating partial listings of table contents

SELECT ltcolumn(s)gtFROM lttable namegtWHERE
ltconditionsgt
Table 5.4 Mathematical Operators
16
Examples

• Mathematical operators
• Mathematical operators on character attributes
• Mathematical operators on dates

SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM
PRODUCT WHERE V_CODE ltgt 21344
SELECT P_CODE,P_DESCRIPT,P_ONHAND,P_MIN,P_PRICEFR
OM PRODUCTWHERE P_CODE lt 1558-QWI
SELECT P_DESCRIPT,P_ONHAND,P_MIN,P_PRICE,PINDATEF
ROM PRODUCTWHERE P_INDATE gt 01/20/2002
17
Computed Columns

• New columns can be created through valid
  computations or formulas
• Formulas may contain mathematical operators
• May contain attributes of any tables specified in
  FROM clause
• Alias is alternate name given to table or column
  in SQL statement

SELECT P_DESCRIPT,P_ONHAND,P_PRICE,P_ONHANDP_PRIC
E AS TOTVALUE FROM PRODUCT
18
Operators

• Logical AND, OR, NOT
• Rules of precedence
• Conditions within parenthesis executed first
• Boolean algebra
• Special
• BETWEEN - defines limits
• IS NULL - checks for nulls
• LIKE - checks for similar string
• IN - checks for value in a set
• EXISTS - opposite of IS NULL

SELECT FROM PRODUCTWHERE V_CODE 21344 OR
V_CODE 24288
19
Advanced Data ManagementCommands

• ALTER - changes table structure
• ADD - adds column
• MODIFY - changes column characteristics
• Entering data into new column

ALTER TABLE lttable namegtADD (ltcolumn namegt ltnew
column characteristicsgt) ALTER TABLE lttable
namegtMODIFY (ltcolumn namegt ltnew column
characteristicsgt)
UPDATE PRODUCTSET P_SALECODE 2WHERE P_CODE
1546-QQ2
20
Advanced Data Management Commands (cont.)

• Dropping a column
• Arithmetic operators and rules of precedence

ALTER TABLE VENDORDROP COLUMN V_ORDER
Table 5.5
21
Advanced Data Management Commands (cont.)

• Copying parts of tables
• Deleting a table from database
• Primary and foreign key designation

INSERT INTO ltreceiving tablegt ltreceiving tables
column namesgtSELECT ltcolumn names of the columns
to be copiedgtFROM ltcontributing table namegt
DROP TABLE PART
ALTER TABLE LINE ADD PRIMARY KEY (INV_NUMBER,
LINE_NUMBER) ADD FOREIGN KEY (INV_NUMBER)
REFERENCES INVOICE ADD FOREIGN KEY (PROD_CODE)
REFERENCES PRODUCT
22
Example Aggregate Function Operations

• COUNT
• MAX and MIN

SELECT COUNT(DISTINCT V_CODE)FROM
PRODUCT SELECT COUNT(DISTINCT V_CODE)FROM
PRODUCTWHERE P_PRICE lt 10.00
SELECT MIN(P_PRICE)FROM PRODUCT SELECT P_CODE,
P_DESCRIPT, P_PRICEFROM PRODUCTWHERE P_PRICE
MAX(P_PRICE)
23
Example Aggregate Function Operations (cont.)

• SUM
• AVG

SELECT SUM(P_ONHAND P_PRICE)FROM PRODUCT
SELECT P_DESCRIPT, P_ONHAND, P_PRICE, V_CODEFROM
PRODUCTWHERE P_PRICE gt (SELECT AVG(P_PRICE)
FROM PRODUCT)ORDER BY P_PRICE DESC
24
More Complex Queries and SQL Functions

• Ordering a listing
• Results ascending by default
• Descending order uses DESC
• Cascading order sequence

ORDER BY ltattributesgt
ORDER BY ltattributesgt DESC
ORDER BY ltattribute 1, attribute 2, ...gt
25
More Complex Queries and SQL Functions (cont.)

• Listing unique values
• DISTINCT clause produces list of different values
• Aggregate functions
• Mathematical summaries

SELECT DISTINCT V_CODE FROM PRODUCT
Table 5.6
26
More Complex Queries and SQL Functions (cont.)

• Grouping data
• Creates frequency distributions
• Only valid when used with SQL arithmetic
  functions
• HAVING clause operates like WHERE for grouping
  output

SELECT P_SALECODE, MIN(P_PRICE)FROM
PRODUCT_2GROUP BY P_SALECODE
SELECT V_CODE,COUNT(DISTINCT(P_CODE)),AVG(P_PRICE)
FROM PRODUCT_2 GROUP BY V_CODE HAVING
AVG(P_PRICE) lt 10
27
More Complex Queries and SQL Functions (cont.)

• Virtual tables creating a view
• CREATE VIEW command
• Creates logical table existing only in virtual
  memory
• SQL indexes

CREATE VIEW PRODUCT_3 ASSELECT P_DESCRIPT,
P_ONHAND, P_PRICEFROM PRODUCTWHERE P_PRICE gt
50.00
CREATE INDEX P_CODEXON PRODUCT(P_CODE)
28
More Complex Queries and SQL Functions (cont.)

• Joining database tables
• Data are retrieved from more than one table
• Recursive queries joins a table to itself
• Outer joins can be used when null values need
  to be included in query result

SELECT PRODUCT.P_DESCRIPT, PRODUCT.P_PRICE,
VENDOR.V_NAME, VENDOR.V_CONTACT,
VENDOR.V_AREACODE, VENDOR.V_PHONEFROM PRODUCT,
VENDORWHERE PRODUCT.V_CODE VENDOR.V_CODE
SELECT A.EMP_NUM,A.EMP_LNAME,A.EMP_MGR, B.EMP_LNAM
E FROM EMP A, EMP B WHERE A.EMP_MGRB.EMP_NUM ORDE
R BY A.EMP_MGR
29
Updatable Views

• Common operation in production environments is
  use of batch routines to update master table
  attributes using transaction data
• Overnight batch jobs
• Not all views are updatable
• Restrictions
• GROUP BY expressions cannot be used
• Cannot use set operators---UNION, INTERSECTION,
  etc.
• Most restrictions based on use of JOINS or group
  operators in views

30
Procedural SQL

• SQL shortcomings
• Doesnt support execution of stored procedures
  based on logical condition
• Fails to support looping operations
• Solutions
• Embedded SQL can be called from within procedural
  programming languages
• Shared Code is isolated and used by all
  application programs.
• Procedural SQL (PL/SQL) stored within the
  database, executed by DBMS, and invoked by the
  end user
• Triggers
• Stored procedures
• PL/SQL functions

31
Procedural SQL (cont.)

• Procedural SQL allows the use of procedural code
  and SQL statements that are stored within the
  database.
• The procedural code is executed by the DBMS when
  it is invoked by the end user.
• End users can use procedural SQL (PL/SQL) to
  create
• Triggers
• Stored procedures
• PL/SQL functions

32
Triggers

• Procedural SQL code invoked before or after data
  row is selected, inserted, or updated
• Associated with a database table
• Table may have multiple triggers
• Executed as part of transaction
• Can enforce particular constraints
• Automate critical actions and provide warnings
  for remedial action
• Can update values, insert records, and call
  procedures
• Add processing power

33
Triggers (cont.)

• Oracle example

CREATE OR REPLACE TRIGGER lttrigger_namegtBEFORE/A
FTER DELETE/INSERT/UPDATE OF ltcolumn_name ON
lttable_namegtFOR EACH ROWBEGIN PL/SQL
instructions END
34
Stored Procedures

• Named collection of procedural and SQL statements
  stored in database
• Invoked by name
• Executed as unit
• Invoked with EXEC

CREATE OR REPLACE PROCEDURE procedure_name
(argument IN/OUT data-type, etc) IS/AS
BEGIN DECLARE variable name and data
type PL/SQL or SQL statementsEND
EXEC store_procedure_name (parameter, parameter,
)
35
PL/SQL Stored Functions

• Named group of procedural and SQL statements that
  returns a value
• Invoked from within stored procedures or triggers
• Cannot be invoked from within SQL statements

CREATE FUNCTION function_name (argument IN
data-type, etc)RETURN data-typeAS BEGIN PL/SQL
statements RETURN (value) END
36
Artist Database ERD and Tables
Figure 5.55
37
General Rules Governing Relationships Among
Tables
Figure 5.56 MN, Both Sides Mandatory
Figure 5.57 MN, Both Sides Optional
38
General Rules Governing Relationships Among
Tables (Cont.)
Figure 5.58 MN, One Side Optional
Figure 5.59 1M, Both Sides Mandatory
39
General Rules Governing Relationships Among
Tables (Cont.)
Figure 5.60 1M, Both Sides Optional
Figure 5.61 1M, Many Side Optional, One Side
Mandatory
40
General Rules Governing Relationships Among
Tables (Cont.)
Figure 5.62 1M, One Side Optional, One Side
Mandatory
Figure 5.63 11, Both Sides Mandatory
41
General Rules Governing Relationships Among
Tables (Cont.)
Figure 5.64 11, Both Sides Optional
Figure 5.65 11, One Side Optional, One Side
Mandatory
42
General Rules Governing Relationships Among
Tables (Cont.)
Figure 5.66 Weak Entity, Foreign Key Located in
Weak Entity
Figure 5.67 Multivalued Attributes (New Table in
1M Relationship, Foreign Key CAR_VIN in the New
Table