IS 631 Midterm Exam Results Absolute

1
IS 631 Midterm Exam Results (Absolute)
91
Median 64
38
2
Midterm Grade Distribution (Absolute)

• 2 As (85-95)
• 7 Bs (75-84)
• 6 Bs (65-74)
• 9 Cs (55-64)
• 3 Cs (45-54)
• 3 Ds (35-44)
• 1 F (lt35)

3
Midterm Grade Distribution (Relative)

• 4 As (90-100)
• 4 Bs (85-89)
• 3 Bs (80-84)
• 3 Cs (75-79)
• 6 Cs (70-74)
• 5 Ds (60-69)
• 6 Fs (lt60)

4
Have not received email with Project Proposal
from Teams 4 8
5
Database Systems Design, Implementation, and
ManagementEighth Edition

• Chapter 7
• Introduction to Structured Query Language (SQL)

6
Goals To Learn and Understand

• The basic commands and functions of SQL
• How to use SQL for data administration (to create
  tables, indexes, and views)
• How to use SQL for data manipulation (to add,
  modify, delete, and retrieve data)
• How to use SQL to query a database to extract
  useful information

7
Introduction to SQL

• SQL functions fit into two broad categories
• SQL is a data definition language (DDL)
• SQL includes commands to
• Create database objects, such as tables, indexes,
  and views
• Define access rights to those database objects
• SQL is a data manipulation language (DML)
• Includes commands to insert, update, delete, and
  retrieve data within database tables

8
Introduction to SQL (continued)

• SQL is relatively easy to learn
• Basic command set has vocabulary of less than 100
  words
• Nonprocedural language (describe what is to be
  done, and not the details of how it is to be
  done)
• American National Standards Institute (ANSI)
  prescribes a standard SQL
• Several SQL dialects exist (but differences among
  them are relatively minor)
• Queries are the heart of SQL (but before issuing
  queries, you must define the database structure
  and environment with DDL commands)
• Queries ask questions, but can also perform
  actions.

9
Introduction to SQL (continued)
10
Introduction to SQL (continued)
11
Introduction to SQL (continued)
12
Data Definition Commands

• We will examine a simple database model and
  database tables that will form basis for many SQL
  examples
• We will have CUSTOMERS who generate INVOICES,
  where each INVOICE has LINES which contain
  PRODUCTS, where the PRODUCTS are obtained from
  VENDORS.

13
The Business Rules

• Customers may generate many invoices, and each
  invoice is associated with only one customer,
  (But, the database may contain customers who have
  not (yet) generated any invoices.)
• An invoice will consist of many invoice lines (at
  least one), where each invoice line is associated
  with only one invoice.
• Each invoice line is for one product. (But, a
  particular product may be associated with many
  invoice lines, or possibly no invoice line at
  all.)
• A vendor may supply many products, but some
  vendors may possibly not currently supply any
  products. A product is supplied by only one
  vendor, but some products may not be supplied by
  any vendor (i.e., they are produced in-house.)

14
The Database Model
strong entity
weak entity
strong relationship
15
Instances of two tables in the Database Model
16
Data Types in SQL

• Data type selection is usually dictated by nature
  of data and by intended use
• Data that may be used in some numeric computation
  or arithmetic expression should be a numeric data
  type.
• Pay close attention to expected use of attributes
  for sorting and data retrieval purposes

17
Includes sign and decimal number
18
Data Types (continued)
19
Examples of Some Data Types for Our Model

• P_PRICE must be numeric a character field would
  be unacceptable.
• P_DESCRIPT is an obvious candidate for a
  character data type (use VARCHAR because vendor
  names are a variable length character string.)
• V_STATE is an obvious 2-character string
• Make P_INDATE a DATE field, rather than a
  character field, so that you can do manipulations
  and comparisons with dates.
• EX SYSDATE is the system date in Oracle, so
  SYSDATE 90 will be 90 days from today
• V_CODE is integer because we might wish to
  generate new vendor codes by adding 1 to the
  highest vendor code.

20
Creating Table StructuresSome Guidelines to
Enhance Readability

• Use one line per column (attribute) definition in
  the CREATE
• Use spaces to line up attribute characteristics
  and constraints
• Table and attribute names are capitalized, while
  end-user specified names are italicized
• Square brackets . denote optional parameters
• NOT NULL specification
• UNIQUE specification

21
Creating Table Structures Some Guidelines

• 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 semicolon

22
The CREATE TABLE in SQL

• CREATE TABLE tablename (
• column1 datatype constraint,
• column2 datatype constraint,,
• PRIMARY KEY (column1,,column2),
• FOREIGN KEY (column1,column2) REFERENCES
  tablename,
• CONSTRAINT constraint )

23
Creating the VENDOR table

• CREATE TABLE VENDOR(
• V_CODE INTEGER NOT NULL UNIQUE,
• V_NAME VARCHAR(35) NOT NULL,
• V_CONTACT VARCHAR(15) NOT NULL,
• V_AREACODE CHAR(3) NOT NULL,
• V_PHONE CHAR(8) NOT NULL,
• V_STATE CHAR(2) NOT NULL,
• V_ORDER CHAR(1) NOT NULL,
• PRIMARY KEY (V_CODE))Note
• In Oracle, with a primary key designation for
  V_CODE, you do not need the NOT NULL and UNIQUE
  specifications in the V_CODE line
• The UNIQUE specification creates a special index
  in V_CODE to prevent duplicate values from being
  entered
• The NOT NULL and UNIQUE in V_CODE enforce entity
  integrity
• The entire definition of the CREATE TABLE is
  enclosed in parenthesis.

24
Creating the PRODUCT table

• CREATE TABLE PRODUCT (
• P_CODE VARCHAR(10) NOT NULL UNIQUE,
• P_DESCRIPT VARCHAR(35) NOT NULL,
• P_INDATE DATE NOT NULL,
• P_QOH SMALLINT NOT NULL,
• P_MIN SMALLINT NOT NULL,
• P_PRICE NUMBER(8,2) NOT NULL,
• P_DISCOUNT NUMBER(4,2) NOT NULL,
• V_CODE INTEGER,
• PRIMARY KEY (P_CODE),
• FOREIGN KEY (V_CODE) REFERENCES VENDOR
• ON UPDATE CASCADE)
• note in Oracle, with a primary key designation
  for P_CODE, you do not need the NOT NULL and
  UNIQUE specifications in the P_CODE line

25
SQL Constraints

• Note that inPRIMARY KEY (V_CODE)
• We set the stage for enforcement of entity
  integrity (all records will be unique)
• Note that inFOREIGN KEY (V_CODE) REFERENCES
  VENDOR ON UPDATE CASCADE
• Referential integrity has been enforced, and the
  DBMS will not allow you to delete a vendor from
  the VENDOR table if at least one product row
  references that vendor. This is the default SQL
  treatment of foreign keys. The ON UPDATE CASCADE
  ensures the preservation of referential ON UPDATE
  CASCADE updates the child table when the parent
  table is updated.

26
SQL Constraints

• NOT NULL constraint
• Ensures that column does not accept nulls
• UNIQUE constraint
• Ensures that all values in column are unique
• DEFAULT constraint
• Assigns value to attribute when a new row is
  added to table
• CHECK constraint
• Validates data when attribute value is entered

27
Column Constraints Table Constraints

• The CREATE TABLE command lets you define
  constraints in two different places
• When you create the column definition (known as a
  column constraint)
• When you use the CONSTRAINT keyword (known as a
  table constraint)
• A column constraint applies to just one attribute
  (column) while a table constraint can apply to
  many attributes (columns).

28
The CUSTOMER table

• CREATE TABLE CUSTOMER (
• CUS_CODE NUMBER PRIMARY KEY,
• CUS_LNAME VARCHAR(15) NOT NULL,
• CUS-FNAME VARCHAR(15) NOT NULL,
• CUS_INITIAL CHAR(1),
• CUS_AREACODE CHAR(3) DEFAULT 615
• CHECK(CUS_AREACODE IN (615,713,931)),
• CUS_PHONE CHAR(8) NOT NULL,
• CUS_BALANCE NUMBER(9,2) DEFAULT 0.00,
• CONSTRAINT CUS_UI1 UNIQUE (CUS_LNAME,
  CUS_FNAME))Note that the CHECK condition
  restricts the area code values to the three area
  codes provided any other values will be
  rejected. If one defaults on areacode, the 615
  areacode will be used.
• The DEFAULT value applies only when new records
  are added. BUT, the CHECK condition is validated
  on BOTH a new record being added OR the area code
  being modified. The CHECK condition will apply to
  all area codes for the table CUSTOMER. The last
  constraint creates a unique index on the
  customers last name and first name, and thus
  prevents two customers from having identical
  first and last names.

Applies to 1 attribute
Applies to 2 attributes
29
The INVOICE Table

• CREATE TABLE INVOICE (
• INV_NUMBER NUMBER PRIMARY KEY,
• CUS_CODE NUMBER NOT NULL REFERENCES
  CUSTOMER (CUS_CODE),
• INV_DATE DATE DEFAULT SYSDATE NOT NULL,
• CONSTRAINT INVCK1 CHECK (INV_DATEgtTO_DATE(01-JAN
  -2006,
• DD-MON_YYYY)))
• Note the CUS_CODE attribute definition indicates
  that CUS_CODE is a foreign key, and this is
  another way to define a foreign key.
• Note the CHECK constraint is used to validate
  that the invoice date is greater than 1/1/2006.
  The TO_DATE Oracle function is used to compare a
  given date to a manually entered date. The
  TO_DATE function has two parameters, the literal
  date, and the date format used.

30
The LINE_NUMBER table

• CREATE TABLE LINE (
• INV_NUMBER NUMBER NOT NULL,
• LINE_NUMBER NUMBER(2,0) NOT NULL,
• P_CODE VARCHAR(10) NOT NULL,
• LINE_UNITS NUMBER(9,2) DEFAULT 0.00 NOT NULL,
• LINE_PRICE NUMBER(9,2) DEFAULT 0.00 NOT NULL,
• PRIMARY KEY (INV_NUMBER, LINE_NUMBER),
• FOREIGN KEY (INV_NUMBER) REFERENCES INVOICE ON
  DELETE CASCADE,
• FOREIGN KEY (P_CODE) REFERENCES PRODUCT (P_CODE),
• CONSTRAINT LINE_UI1 UNIQUE (INV_NUMBER, PCODE)
• Note the Line_Number table has a composite
  primary key (INV_NUMBER, LINE_Number) and uses a
  UNIQUE constraint to insure that the same product
  (pcode) is NOT ordered twice in the same invoice.
  Also, the ON DELETE CASCADE is recommended for
  weak entity sets to insure that the deletion of a
  row in the strong entity set automatically
  triggers the deletion of the rows in the
  dependent weak entity set. Thus, the deletion of
  an INVOICE row will automatically delete all of
  the LINE rows related to the invoice.

31
SQL Indexes

• When primary key is declared, DBMS automatically
  creates a unique index
• Often need additional indexes
• Using the CREATE INDEX command, SQL indexes can
  be created for any selected attribute
• CREATE UNIQUE INDEX indexname ON tablename
  (column1 ,column2)
• Create an index named P_INDATEX on the attribute
  P_INDATE of the PRODUCT table
• CREATE INDEX P_INDATEX on PRODUCT (P_INDATE)
• Composite index
• Index based on two or more attributes
• Often used to prevent data duplication

32
SQL Indexes (continued)

• Table below has required test scores, where each
  employee may take a test only once on any given
  date.
• Primary key is composite (EMP_NUM, TEST_NUM)
• Test number 3 for employee 111 meets entity
  integrity requirements, but the WEA test entry
  score is clearly duplicated.
• Avoid this duplication by the following unique
  composite index
• CREATE UNIQUE INDEX EMP_TESTDEX ON TEST (EMP_NUM,
  TEST_CODE, TEST_DATE)

33
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

34
Adding Table Rows

• INSERT
• Used to enter data into table
• Syntax
• INSERT INTO columnnameVALUES (value1, value2,
  , valuen)

35
Adding Table Rows (continued)

• When entering values, notice that
• Row contents are entered between parentheses
• Character and date values are entered between
  apostrophes
• Numerical entries are not enclosed in apostrophes
• Attribute entries are separated by commas
• A value is required for each column
• Use NULL for unknown values

36
Saving Table Changes

• Changes made to table contents are not physically
  saved on disk until, one of the following occurs
• 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

37
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

38
Listing Table Rows (continued)
39
Updating Table Rows

• UPDATE
• Modify data in a table
• Syntax
• UPDATE tablenameSET columnname expression ,
  columname expressionWHERE conditionlist
• If more than one attribute is to be updated in
  row, separate corrections with commas

40
Restoring Table Contents

• ROLLBACK
• Used to restore database to its previous
  condition
• Only applicable if COMMIT command has not been
  used to permanently store changes in database
• Syntax
• ROLLBACK
• COMMIT and ROLLBACK only work with data
  manipulation commands that are used to add,
  modify, or delete table rows

41
Deleting Table Rows

• DELETE
• Deletes a table row
• Syntax
• DELETE FROM tablenameWHERE conditionlist
• WHERE condition is optional
• If WHERE condition is not specified, all rows
  from specified table will be deleted

42
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

43
Selecting Rows with Conditional Restrictions

• Select partial table contents by placing
  restrictions on rows to be included in output
• Add conditional restrictions to SELECT statement,
  using WHERE clause
• Syntax
• SELECT columnlistFROM tablelist WHERE
  conditionlist

44
Selecting Rows with Conditional Restrictions
(continued)
45
Selecting Rows with Conditional Restrictions
(continued)
http//web.njit.edu/mysql/phpMyAdmin
46
Selecting Rows with Conditional Restrictions
(continued)
47
Selecting Rows with Conditional Restrictions
(continued)
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM
PRODUCT WHERE V_CODE ltgt 21344
48
Selecting Rows with Conditional Restrictions
(continued)
SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE FROM
PRODUCT WHERE P_PRICE lt 10
49
Selecting Rows with Conditional Restrictions
(continued)
SELECT P_CODE P_DESCRIPT, P_QOH, P_MIN,
P_PRICE FROM PRODUCT WHERE P_CODE lt 1558-QW1
50
Selecting Rows with Conditional Restrictions
(continued)
SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE,
P_INDATE FROM PRODUCT WHERE P_INDATE gt
20-JAN-2006
Returns inventory stock dates that occur on or
after Jan 20, 2008
51
Selecting Rows with Conditional Restrictions
(continued)
SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH
P_PRICE FROM PRODUCT
Defaults to EXPR1
52
Selecting Rows with Conditional Restrictions
(continued)
SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH
P_PRICE AS TOTVALUE FROM PRODUCT
Aliases to TOTVALUE
53
Arithmetic Operators The Rule of Precedence

• Perform operations within parentheses
• Perform power operations
• Perform multiplications and divisions
• Perform additions and subtractions

54
Arithmetic Operators The Rule of Precedence
(continued)
55
Logical OperatorsAND, OR, and NOT
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM
PRODUCT WHERE V_CODE 21344 OR V_CODE 24288
56
Logical OperatorsAND, OR, and NOT (continued)
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM
PRODUCT WHERE P_PRICE lt 50 AND P_INDATE gt
15-JAN-2006
57
Logical OperatorsAND, OR, and NOT (continued)
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM
PRODUCT WHERE (P_PRICE lt 50 AND AND P_INDATE gt
15-JAN-2006 OR V_CODE 24288
58
Special Operators

• BETWEEN
• Used to check whether attribute value is within a
  range
• IS NULL
• Used to check whether attribute value is null
• LIKE (wildcards and _ )
• Used to check whether attribute value matches
  given string pattern

59
Special Operators (continued)

• IN
• Used to check whether attribute value matches any
  value within a value list
• EXISTS
• Used to check if subquery returns any rows

60
Advanced Data Definition Commands

• All changes in table structure are made by using
  ALTER command
• Followed by keyword that produces specific change
  
• Following three options are available
• ADD
• MODIFY
• DROP

61
Changing a Columns Data Type

• ALTER can be used to change data type
• Some RDBMSs (such as Oracle) do not permit
  changes to data types unless column to be changed
  is empty

62
Changing a Columns Data Characteristics

• Use ALTER to change data characteristics
• If column to be changed already contains data,
  changes in columns characteristics are permitted
  if those changes do not alter the data type
• Example Increase the width of the P_PRICE
  column to 9 digits
• ALTER TABLE PRDUCT
• MODIFY (P_PRICE DECIMAL(9,2)

63
Adding a Column

• Use ALTER to add column
• Do not include the NOT NULL clause for new column
• Example
• ALTER TABLE PRODUCT
• ADD (P_SALECODE CHAR(1))

64
Dropping a Column

• Use ALTER to drop column
• Some RDBMSs impose restrictions on the deletion
  of an attribute
• Example
• ALTER TABLE VENDOR DROP COLUMN
  V_ORDER

65
Advanced Data Updates
UPDATE PRODUCT SET P_SALECODE 2 WHERE P_CODE
1546-QQ2
UPDATE PRODUCT SET P_SALECODE 1 WHERE P_CODE
IN (2232/QWE, 2232-QTY
UPDATE PRODUCT SET P_SALECODE 1 WHERE
P_INDATE gt 16-JAN-2006 AND P_INDATE lt
10-FEB-2006
UPDATE PRODUCT SET P_SALECODE 2 WHERE
P_INDATE lt 25-DEC-2005
66
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)
• STEP 1 First create the PART table structure
• STEP 2 Next add rows to new PART table using
  PRODUCT table rows

67
STEP 1 CREATE PART TABLE

• CREATE TABLE PART(
• PART_CODE CHAR(8) NOT NULL,
• PART_DESCRIPT CHAR(35),
• PART_PRICE DECIMAL(8,2),
• V_CODE INTEGER,
• PRIMARY KEY (PART_CODE))

68
STEP 2 ADD ROWS TO PART TABLE

• Syntax
• INSERT INTO target_tablename(target_columnlist)
• SELECT source_columnlist
• FROM source_tablename
• Example
• INSERT INTO PART(PART_CODE, PART_DESCRIPT,
  PART_PRICE,V_CODE)
• SELECT P_CODE, P_DESCRIPT, P_PRICE, V_CODE, FROM
  PRODUCT

69
STEP 1 Copying Parts of Tables (continued)
70
Adding Primary and Foreign Key Designations

• When table is copied, integrity rules do not
  copy, so primary and foreign keys need to be
  manually defined on new table
• User ALTER TABLE command
• Syntax
• ALTER TABLE tablename ADD PRIMARY
  KEY(fieldname)
• For foreign key, use FOREIGN KEY in place of
  PRIMARY KEY

71
Example

• PRIMARY
• ALTER TABLE PART
• ADD PRIMARY KEY (PART_CODE)
• FOREIGN
• ALTER TABLE PART
• ADD FOREIGN KEY (V_CODE) REFERENCES VENDOR

72
Deleting a Table from the Database

• DROP
• Deletes table from database
• Syntax
• DROP TABLE tablename

73
Advanced Select Queries

• SQL provides useful functions that can
• Count
• Find minimum and maximum values
• Calculate averages
• SQL allows user to limit queries to only those
  entries having no duplicates or entries whose
  duplicates may be grouped

74
Ordering a Listing
SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE FROM
PRODUCT ORDER BY P_PRICE
75
Ordering a Listing (continued)
1. ORDER BY last name 2. Within last names, ORDER
BY first name 3. Within first and last names,
ORDER BY middle initial
SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL,
EMP_AREACODE, EMP_PHONE FROM EMPLOYEE ORDER
BY EMP_LNAME, EMP_FNAME, EMP_INITIAL
76
Ordering a Listing (continued)
SELECT P_DESCRIPT, V_CODE, P_INDATE,
P_PRICE FROM PRODUCT WHERE P_INDATE lt 21-JAN
2006 AND P_PRICE ,_50.00 ORDER BY V_CODE,
P_PRICE DESC
Within in each V_CODE, the P_PRICE values are in
descending order
77
Listing Unique Values
How many different vendors are currently
represented in the PRODUCT Table?
SELECT DISTINCT V_CODE FROM PRODUCT
78
Aggregate Functions
79
Aggregate Functions (continued)
Line
80
Aggregate Functions (continued)
81
Aggregate Functions (continued)
Total amount owed by your customers
Total amount of all items carried in inventory
82
Aggregate Functions (continued)
83
Grouping Data
Minimum price for each sales code
Average price for each sales code
84
Grouping Data (continued)
GROUP BY clause is only valid when used in
conjunction with one of the SQL aggregate
functions, such as COUNT, MIN, MAX, AVG, and SUM
Answers how many products supplied by each vendor
85
Grouping Data (continued)
HAVING clause is like the WHERE clause but
instead of applying to columns and expressions
for individual rows, it applies to the output of
a GROUP BY operation
Generate a listing of the number of products in
the inventory supplied by each vendor, and
limit the listing to products whose prices
average below 10.00
86
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 view is based
• Create view by using CREATE VIEW command

87
Virtual Tables Creating a View (continued)
88
Joining Database Tables

• Ability to combine (join) tables on common
  attributes is most important distinction between
  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 foreign key and primary key of
  related tables

89
Joining Database Tables (continued)
90
JOIN Command

• SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT,
  V_AREACODE, V_PHONE
• FROM PRODUCT, VENDOR
• WHERE PRODUCT.V_CODE VENDOR.V_CODE

91
VENDOR and PRODUCT Tables
92
Joining Database Tables Result
93
Joining Database Tables (continued)
SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT,
V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE
PRODUCT.V_CODE VENDOR.V_CODE AND P_INDATE gt
15-JAN-2006
94
Joining Tables with an Alias

• Alias can be used to identify source table
• Any legal table name can be used as alias
• Add alias after table name in FROM clause
• FROM tablename alias

95
Recursive Joins
List all employees with their managers names
96
Recursive Joins (continued)
SELECT E.EMP_MGR, M.EMP_LNAME, E.EMP_NUM,
E.EMP_LNAME FROM EMP E, EMP M WHERE
E.EMP_MGRM.EM_NUM ORDER BY E.EMP_MGR
97
Outer Joins
Left outer join will show all VENDOR rows and all
matching PRODUCT rows
98
Outer Joins (continued)
Right outer join will show all PRODUCT rows with
all matching VENDOR rows
99
Summary

• SQL commands can be divided into two overall
  categories
• Data definition language commands
• Data manipulation language commands
• The ANSI standard data types are supported by all
  RDBMS vendors in different ways
• Basic data definition commands allow you to
  create tables, indexes, and views

100
Summary (continued)

• DML commands allow you to add, modify, and delete
  rows from tables
• The basic DML commands are SELECT, INSERT,
  UPDATE, DELETE, COMMIT, and ROLLBACK
• INSERT command is used to add new rows to tables
• SELECT statement is main data retrieval command
  in SQL

101
Summary (continued)

• Many SQL constraints can be used with columns
• The column list represents one or more column
  names separated by commas
• WHERE clause can be used with SELECT, UPDATE, and
  DELETE statements to restrict rows affected by
  the DDL command

102
Summary (continued)

• Aggregate functions
• Special functions that perform arithmetic
  computations over a set of rows
• ORDER BY clause
• Used to sort output of SELECT statement
• Can sort by one or more columns and use either an
  ascending or descending order
• Join output of multiple tables with SELECT
  statement

103
Summary (continued)

• Natural join uses join condition to match only
  rows with equal values in specified columns
• Right outer join and left outer join used to
  select rows that have no matching values in other
  related table