6 1 lecture 8: introduction to structured query language (sql) j. s. chou, p.e., ph.d
TRANSCRIPT
![Page 1: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/1.jpg)
6
1
Lecture 8: Introduction to Structured Query Language (SQL)
J. S. Chou, P.E., Ph.D.
![Page 2: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/2.jpg)
6
2
Introduction to SQL
• SQL functions fit into two broad categories:• Data definition language
– SQL includes commands to create• Database objects such as tables, indexes, and
views
• Commands to define access rights to those database objects
• Data manipulation language– Includes commands to insert, update, delete,
and retrieve data within the database tables
![Page 3: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/3.jpg)
6
3
Introduction to SQL (continued)
• SQL is relatively easy to learn
• Basic command set has a vocabulary of less than 100 words
• Nonprocedural language
• American National Standards Institute (ANSI) prescribes a standard SQL
• Several SQL dialects exist
![Page 4: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/4.jpg)
6
4
SQL Data Definition Commands
![Page 5: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/5.jpg)
6
5
Data Manipulation Commands
![Page 6: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/6.jpg)
6
6
Data Definition Commands
• Examine the simple database model and the database tables that will form the basis for the many SQL examples
• Understand the data environment
![Page 7: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/7.jpg)
6
7
The Database Model
![Page 8: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/8.jpg)
6
8
Creating the Database
• Two tasks must be completed
– create the database structure
– create the tables that will hold the end-user data
• First task
– RDBMS creates the physical files that will hold the database
– Tends to differ substantially from one RDBMS to another
![Page 9: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/9.jpg)
6
9
The Database Schema
• Authentication – Process through which the DBMS verifies that
only registered users are able to access the database
– Log on to the RDBMS using a user ID and a password created by the database administrator
• Schema– Group of database objects—such as tables
and indexes—that are related to each other
![Page 10: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/10.jpg)
6
10
Some Common SQL Data Types
![Page 11: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/11.jpg)
6
11
Creating Table Structures
• Use one line per column (attribute) definition• Use spaces to line up the attribute characteristics and
constraints• Table and attribute names are capitalized• NOT NULL specification • UNIQUE specification • Primary key attributes contain both a NOT NULL and a
UNIQUE specification• RDBMS will automatically enforce referential integrity for
foreign keys• Command sequence ends with a semicolon
![Page 12: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/12.jpg)
6
12
Other SQL Constraints
• NOT NULL constraint
– Ensures that a column does not accept nulls
• UNIQUE constraint
– Ensures that all values in a column are unique
• DEFAULT constraint
– Assigns a value to an attribute when a new row is added to a table
• CHECK constraint
– Validates data when an attribute value is entered
![Page 13: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/13.jpg)
6
13
SQL Indexes• When a primary key is declared, DBMS
automatically creates a unique index
• Often need additional indexes
• Using the CREATE INDEX command, SQL indexes can be created on the basis of any selected attribute
• Composite index
– Index based on two or more attributes
– Often used to prevent data duplication
![Page 14: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/14.jpg)
6
14
Data Manipulation Commands
• Adding table rows
• Saving table changes
• Listing table rows
• Updating table rows
• Restoring table contents
• Deleting table rows
• Inserting table rows with a select subquery
![Page 15: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/15.jpg)
6
15
Common SQL Data Manipulation Commands
![Page 16: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/16.jpg)
6
16
A Data View and Entry Form
![Page 17: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/17.jpg)
6
17
Saving Table Changes
• Changes made to table contents are not physically saved on disk until
– Database is closed
– Program is closed
– COMMIT command is used
• Syntax
– COMMIT [WORK]
• Will permanently save any changes made to any table in the database
![Page 18: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/18.jpg)
6
18
Listing Table Rows
• SELECT
– Used to list contents of table
• Syntax
– SELECT columnlistFROM tablename
• Columnlist represents one or more attributes, separated by commas
• Asterisk can be used as wildcard character to list all attributes
![Page 19: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/19.jpg)
6
19
Updating Table Rows
• UPDATE
– Modify data in a table
• Syntax
– UPDATE tablenameSET columnname = expression [, columname = expression][WHERE conditionlist];
• If more than one attribute is to be updated in the row, separate corrections with commas
![Page 20: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/20.jpg)
6
20
Restoring Table Contents• ROLLBACK
– Used restore the database to its previous condition
– Only applicable if COMMIT command has not been used to permanently store the changes in the database
• Syntax– ROLLBACK;
• COMMIT and ROLLBACK only work with data manipulation commands that are used to add, modify, or delete table rows
![Page 21: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/21.jpg)
6
21
Deleting Table Rows
• DELETE
– Deletes a table row
• Syntax
– DELETE FROM tablename[WHERE conditionlist ];
• WHERE condition is optional
• If WHERE condition is not specified, all rows from the specified table will be deleted
![Page 22: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/22.jpg)
6
22
Inserting Table Rows with a Select Subquery
• INSERT– Inserts multiple rows from another table
(source)– Uses SELECT subquery
• Query that is embedded (or nested) inside another query
• Executed first
• Syntax– INSERT INTO tablename SELECT columnlist
FROM tablename
![Page 23: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/23.jpg)
6
23
Selecting Rows with Conditional Restrictions
• Select partial table contents by placing restrictions on rows to be included in output
– Add conditional restrictions to the SELECT statement, using WHERE clause
• Syntax
– SELECT columnlistFROM tablelist[ WHERE conditionlist ] ;
![Page 24: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/24.jpg)
6
24
Selected PRODUCT Table Attributes for VENDOR Code 21344
![Page 25: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/25.jpg)
6
25
The Microsoft Access QBE and its SQL
![Page 26: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/26.jpg)
6
26
Comparison Operators
![Page 27: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/27.jpg)
6
27
Selected PRODUCT Table Attributes for VENDOR Codes Other than 21344
![Page 28: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/28.jpg)
6
28
Selected PRODUCT Table Attributes with a P_PRICE Restriction
![Page 29: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/29.jpg)
6
29
Selected PRODUCT Table Attributes: The ASCII Code Effect
![Page 30: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/30.jpg)
6
30
Selected PRODUCT Table Attributes: Date Restriction
![Page 31: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/31.jpg)
6
31
SELECT Statement with a Computed Column
![Page 32: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/32.jpg)
6
32
SELECT Statement with a Computed Column and an Alias
![Page 33: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/33.jpg)
6
33
Arithmetic Operators: The Rule of Precedence
• Perform operations within parentheses
• Perform power operations
• Perform multiplications and divisions
• Perform additions and subtractions
![Page 34: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/34.jpg)
6
34
Selected PRODUCT Table Attributes: The Logical OR
![Page 35: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/35.jpg)
6
35
Selected PRODUCT Table Attributes: The Logical AND
![Page 36: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/36.jpg)
6
36
Selected PRODUCT Table Attributes: The Logical AND and OR
![Page 37: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/37.jpg)
6
37
Special Operators
• BETWEEN– Used to check whether attribute value is within a
range• IS NULL
– Used to check whether attribute value is null• LIKE
– Used to check whether attribute value matches a given string pattern
• IN– Used to check whether attribute value matches any
value within a value list• EXISTS
– Used to check if a subquery returns any rows
![Page 38: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/38.jpg)
6
38
Advanced Data Definition Commands
• All changes in the table structure are made by using the ALTER command
– Followed by a keyword that produces specific change
– Three options are available
• ADD
• MODIFY
• DROP
![Page 39: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/39.jpg)
6
39
Changing a Column’s Data Type
• ALTER can be used to change data type
• Some RDBMSs (such as Oracle) do not permit changes to data types unless the column to be changed is empty
![Page 40: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/40.jpg)
6
40
Changing a Column’s Data Characteristics
• Use ALTER to change data characteristics
• If the column to be changed already contains data, changes in the column’s characteristics are permitted if those changes do not alter the data type
![Page 41: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/41.jpg)
6
41
Adding or Dropping a Column
• Use ALTER to add a column
– Do not include the NOT NULL clause for new column
• Use ALTER to drop a column
– Some RDBMSs impose restrictions on the deletion of an attribute
![Page 42: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/42.jpg)
6
42
The Effect of Data Entry into the New P_SALECODE Column
![Page 43: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/43.jpg)
6
43
Update of the P_SALECODE Column in Multiple Data Rows
![Page 44: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/44.jpg)
6
44
The Effect of Multiple Data Updates in the PRODUCT Table (MS Access)
![Page 45: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/45.jpg)
6
45
Copying Parts of Tables
• SQL permits copying contents of selected table columns so that the data need not be reentered manually into newly created table(s)
• First create the PART table structure
• Next add rows to new PART table using PRODUCT table rows
![Page 46: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/46.jpg)
6
46
PART Attributes Copied from the PRODUCT Table
![Page 47: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/47.jpg)
6
47
Advanced Select Queries
• SQL provides useful functions
– Count
– Find minimum and maximum values
– Calculate averages
• SQL allows the user to limit queries to only those entries having no duplicates or entries whose duplicates may be grouped
![Page 48: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/48.jpg)
6
48
Selected PRODUCT Table Attributes: Ordered by (Ascending) P_PRICE
![Page 49: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/49.jpg)
6
49
Partial Listing of EMPLOYEE Table Contents
![Page 50: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/50.jpg)
6
50
Telephone List Query Results
![Page 51: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/51.jpg)
6
51
A Query Based on Multiple Restrictions
![Page 52: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/52.jpg)
6
52
A Listing of Distinct (Different) V_CODE Values in the PRODUCT Table
![Page 53: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/53.jpg)
6
53
Some Basic SQL Aggregate Functions
![Page 54: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/54.jpg)
6
54
COUNT Function Output Examples
![Page 55: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/55.jpg)
6
55
MAX and MIN Function Output Examples
![Page 56: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/56.jpg)
6
56
The Total Value of All Items in the PRODUCT Table
![Page 57: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/57.jpg)
6
57
AVG Function Output Examples
![Page 58: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/58.jpg)
6
58
GROUP BY Clause Output Examples
![Page 59: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/59.jpg)
6
59
An Application of the HAVING Clause
![Page 60: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/60.jpg)
6
60
Virtual Tables: Creating a View
• View is a virtual table based on a SELECT query– Can contain columns, computed columns,
aliases, and aggregate functions from one or more tables
• Base tables are tables on which the view is based
• Create a view by using the CREATE VIEW command
![Page 61: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/61.jpg)
6
61
Creating a Virtual Table with the CREATE VIEW Command
![Page 62: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/62.jpg)
6
62
Joining Database Tables
• Ability to combine (join) tables on common attributes is most important distinction between a relational database and other databases
• Join is performed when data are retrieved from more than one table at a time
• Join is generally composed of an equality comparison between the foreign key and the primary key of related tables
![Page 63: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/63.jpg)
6
63
Creating Links Through Foreign Keys
![Page 64: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/64.jpg)
6
64
The Results of a Join
![Page 65: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/65.jpg)
6
65
An Ordered and Limited Listing After a JOIN
![Page 66: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/66.jpg)
6
66
The Contents of the EMP Table
![Page 67: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/67.jpg)
6
67
Using an Alias to Join a Table to Itself
![Page 68: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/68.jpg)
6
68
The Left Outer Join Results
![Page 69: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/69.jpg)
6
69
The Right Outer Join Results
![Page 70: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/70.jpg)
6
70
Converting an ER Model into a Database Structure
• Requires following specific rules that govern such a conversion
• Decisions made by the designer to govern data integrity are reflected in the foreign key rules
• Implementation decisions vary according to the problem being addressed
![Page 71: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/71.jpg)
6
71
The Ch06_Artist Database ERD and Tables
![Page 72: 6 1 Lecture 8: Introduction to Structured Query Language (SQL) J. S. Chou, P.E., Ph.D](https://reader035.vdocuments.us/reader035/viewer/2022070410/56649f0c5503460f94c201f4/html5/thumbnails/72.jpg)
6
72
A Data Dictionary for the Ch06_Artist Database