Transcript cos346day3 - Ecom and COS classes

COS 346
Day 3
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-1
Agenda
• Questions?
• WebCt accounts should be available
• Assignment 1 is DUE on Monday
– There was some confusion due to WebCT not being accessible
in the first class
• Begin Intro to SQL
– Chap 2 in DP
– Chap 1 in SQL
• Good news!
– Looks like I’ll be getting CA to donate 12 or so copies of
AllFusion Data Modeling Suite Bundles which includes
ERwin to UMFK.
– $150,000+ donation
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-2
Accessing the Cape Codd
Database
• Download an MS access version from WebCT
under Database section (chapter_2.mdb)
• Use the Oracle Server LittleBlack.advtech.local
– Database SID: CapeCodd
– You all have read access
• Account: Cos346
• Password: Cos346
• At a command Prompt type (temp fix)
– \\littleblack\oracle10g\namefile\fixnames.bat
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-3
Using SQL*Plus
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-4
USING SQL*Plus Worksheet
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-5
Using MS Access - Results
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-6
Using MS SQL Server
[SQL Query Analyzer]
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-7
Using Oracle
[SQL*Plus]
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-8
Using Oracle
[Quest Software’s TOAD]
http://www.toadsoft.com/toad_oracle.htm
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-9
Using MySQL
[MySQL Command Line Client]
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-10
Using MySQL
[MySQL Query Browser]
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-11
Sorting the Results: ORDER BY
SELECT *
FROM
ORDER BY
Chapter_2.ORDER_ITEM
OrderNumber, Price;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-12
Sort Order:
Ascending and Descending
SELECT
*
FROM
Chapter_2.ORDER_ITEM
ORDER BY Price DESC, OrderNumber ASC;
NOTE: The default sort order is ASC – does not have to be specified.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-13
WHERE Clause Options: AND
SELECT
FROM
WHERE
AND
*
Chapter_2.SKU_DATA
Department = 'Water Sports'
Buyer = 'Nancy Meyers';
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-14
WHERE Clause Options: OR
SELECT
FROM
WHERE
OR
*
Chapter_2.SKU_DATA
Department = 'Camping’
Department = 'Climbing’;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-15
WHERE Clause Options:- IN
SELECT
FROM
WHERE
*
Chapter_2.SKU_DATA
Buyer IN ('Nancy Meyers',
'Cindy Lo', 'Jerry Martin');
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-16
WHERE Clause Options: NOT IN
SELECT
FROM
WHERE
*
Chapter_2.SKU_DATA
Buyer NOT IN ('Nancy Meyers',
'Cindy Lo', 'Jerry Martin');
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-17
WHERE Clause Options:
Ranges with BETWEEN
SELECT
FROM
WHERE
*
Chapter_2.ORDER_ITEM
ExtendedPrice
BETWEEN 100 AND 200;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-18
WHERE Clause Options:
Ranges with Math Symbols
SELECT
FROM
WHERE
AND
*
Chapter_2.ORDER_ITEM
ExtendedPrice >= 100
ExtendedPrice <= 200;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-19
WHERE Clause Options:
LIKE and Wildcards
• The SQL keyword LIKE can be combined
with wildcard symbols:
– SQL 92 Standard (SQL Server, Oracle, etc.):
• _ = Exactly one character
• % = Any set of one or more characters
– MS Access (based on MS DOS)
•?
•*
= Exactly one character
= Any set of one or more characters
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-20
WHERE Clause Options:
LIKE and Wildcards (Continued)
SELECT
*
FROM Chapter_2.SKU_DATA
WHERE
Buyer LIKE 'Pete%';
MS ACCESS Buyer LIKE ‘Pete*’;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-21
WHERE Clause Options:
LIKE and Wildcards (Continued)
SELECT
FROM
WHERE
*
Chapter_2.SKU_DATA
SKU_Description LIKE '%Tent%';
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-22
WHERE Clause Options:
LIKE and Wildcards
SELECT
*
FROM Chapter_2.SKU_DATA
WHERE
SKU LIKE '%2__';
MS ACCESS SKU LIKE ‘*2??’;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-23
SQL Built-in Functions
• There are five SQL Built-in Functions:
– COUNT
– SUM
– AVG
– MIN
– MAX
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-24
SQL Built-in Functions (Continued)
SELECT SUM (ExtendedPrice)
AS Order3000Sum
FROM
Chapter_2.ORDER_ITEM
WHERE OrderNumber = 3000;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-25
SQL Built-in Functions (Continued)
SELECT
FROM
SUM (ExtendedPrice) AS
AVG (ExtendedPrice) AS
MIN (ExtendedPrice) AS
MAX (ExtendedPrice) AS
Chapter_2.ORDER_ITEM;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
OrderItemSum,
OrderItemAvg,
OrderItemMin,
OrderItemMax
1-26
SQL Built-in Functions (Continued)
SELECT COUNT(*) AS NumRows
FROM
Chapter_2.ORDER_ITEM;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-27
SQL Built-in Functions (Continued)
SELECT COUNT
(DISTINCT Department)
AS DeptCount
FROM
Chapter_2.SKU_DATA;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-28
Arithmetic in SELECT Statements
SELECT Quantity * Price AS EP,
ExtendedPrice
FROM
Chapter_2.ORDER_ITEM;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-29
String Functions in SELECT
Statements (T-SQL)
SELECT
FROM
DISTINCT RTRIM (Buyer)
+ ' in ' + RTRIM (Department)
AS Sponsor
Chapter_2.SKU_DATA;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-30
String Functions in SELECT
Statements (SQL*PLUS)
SELECT
DISTINCT CONCAT(RTRIM(Buyer),
CONCAT(' in ',RTRIM(Department)))
AS Sponsor
FROM Chapter_2.SKU_DATA;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-31
The SQL keyword GROUP BY
SELECT
FROM
GROUP BY
Department, Buyer,
COUNT(*) AS
Dept_Buyer_SKU_Count
Chapter_2.SKU_DATA
Department, Buyer;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-32
The SQL keyword GROUP BY
(Continued)
• In general, place WHERE before GROUP BY.
Some DBMS products do not require that
placement, but to be safe, always put WHERE
before GROUP BY.
• The HAVING operator restricts the groups that
are presented in the result.
• There is an ambiguity in statements that include
both WHERE and HAVING clauses. The results
can vary, so to eliminate this ambiguity SQL
always applies WHERE before HAVING.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-33
The SQL keyword GROUP BY
(Continued)
SELECT
FROM
WHERE
GROUP BY
ORDER BY
Department, COUNT(*) AS
Dept_SKU_Count
chapter_2.SKU_DATA
SKU <> 302000
Department
Dept_SKU_Count;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-34
The SQL keyword GROUP BY
(Continued)
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
Department, COUNT(*) AS
Dept_SKU_Count
Chapter_2.SKU_DATA
SKU <> 302000
Department
COUNT (*) > 1
Dept_SKU_Count;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-35
Querying Multiple Tables:
Subqueries
SELECT SUM (ExtendedPrice) AS Revenue
FROM
chapter_2.ORDER_ITEM
WHERE
SKU IN
(SELECT
SKU
FROM
chapter_2.SKU_DATA
WHERE
Department = 'Water Sports');
Note: The second SELECT statement is a subquery.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-36
Querying Multiple Tables:
Subqueries (Continued)
SELECT Buyer
FROM
chapter_2.SKU_DATA
WHERE SKU IN
(SELECT
SKU
FROM
chapter_2.ORDER_ITEM
WHERE
OrderNumber IN
(SELECT
OrderNumber
FROM
chapter_2.RETAIL_ORDER
WHERE
OrderMonth = 'January'
AND
OrderYear = 2004));
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-37
Querying Multiple Tables:
Joins
SELECT
Buyer, ExtendedPrice
FROM
Chapter_2.SKU_DATA,
Chapter_2.ORDER_ITEM
WHERE
SKU_DATA.SKU = ORDER_ITEM.SKU;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-38
Querying Multiple Tables:
Joins (Continued)
SELECT
Buyer, SUM(ExtendedPrice)
AS BuyerRevenue
FROM
Chapter_2.SKU_DATA,
chapter_2.ORDER_ITEM
WHERE
SKU_DATA.SKU = ORDER_ITEM.SKU
GROUP BY Buyer
ORDER BY BuyerRevenue DESC;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-39
Querying Multiple Tables:
Joins (Continued)
SELECT
Buyer, ExtendedPrice, OrderMonth
FROM
Chapter_2.SKU_DATA,
chapter_2.ORDER_ITEM, chapter_2.RETAIL_ORDER
WHERE
SKU_DATA.SKU = ORDER_ITEM.SKU
AND
ORDER_ITEM.OrderNumber =
RETAIL_ORDER.OrderNumber;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-40
Subqueries versus Joins
• Subqueries and joins both process multiple
tables.
• A subquery can only be used to retrieve data
from the top table.
• A join can be used to obtain data from any
number of tables, including the “top table” of the
subquery.
• In Chapter 7, we will study the correlated
subquery. That kind of subquery can do work
that is not possible with joins.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-41
David M. Kroenke’s
Database Processing
Fundamentals, Design, and Implementation
(10th Edition)
End of Presentation:
Chapter Two Part Two
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-42
CHAPTER 1:
INTRODUCTION
Oracle SQL
BORDOLOI & BOCK
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-43
SQL
• SQL, pronounced ‘Sequel’ or simply S-Q-L, is
a computer programming language that was
developed especially for querying relational
databases using a non-procedural approach.
• The term non-procedural means that you can
extract information by simply telling the
system what information is needed without
telling how to perform the data retrieval.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-44
SQL
• Extracting information from the database by
using SQL is termed querying the
database.
• SQL is a language that is fairly simple to
learn in terms of writing queries, but it has
considerable complexity because it is a
very powerful language.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-45
DATA AND INFORMATION
• Information is derived from raw facts known as
data.
• Data have little meaning or usefulness to
managers unless they are organized in some
logical manner.
• One of the most efficient ways to organize and
manage data is through the use of a database
management system (DBMS).
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-46
DBMS
• Common relational DBMS products are the
Oracle RDBMS, IBM’s DB2, Microsoft’s SQL
Server, and Microsoft’s desktop single user
RDBMS named Microsoft Access.
• A DBMS provides both systems development
professionals and information users with an
easy-to-use interface to their organization’s
database.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-47
DATA
•
Two types of data are stored within a
database:
•
User data: Data that must be stored by
an organization.
System data: Data the database needs to
manage user data to manage itself. This
is also termed metadata, or the data
about data.
•
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-48
Relational Database
• The most common type of DBMS software in
use today is termed a relational DBMS or
RDBMS.
• A relational database stores data in the form
of tables.
• A table is defined as a collection of rows and
columns.
• The tables are formally known as relations;
this is where the relational database gets its
name.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-49
TABLE
• In the figure, rows represent records and
columns represent fields in a file
processing sense.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-50
DATABASE MANAGEMENT SYSTEM
• A database management system (DBMS)
manages the data in a database.
• A DBMS is a collection of programs that
enables the users to create and maintain a
database.
• A DBMS also enables data to be shared;
information system users and managers
can get more information value from the
same amount of data when data sharing
occurs.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-51
ORACLE’S RELATIONAL DBMS
• Oracle Corporation’s RDBMS is one of the
widely used RDBMS products.
• An RDBMS performs all the basic functions of
the DBMS software along with a magnitude of
other functions that make the relational model
easier to understand and to implement.
• RDBMS provides services that allow
information systems professionals to easily
change the structure of a database.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-52
ORACLE
The significant features of Oracle are:
•
•
•
•
•
Security mechanisms
Backup and recovery
Space management
Open connectivity
Tools and applications
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-53
SQL AND ORACLE’S SQL*PLUS
• Data are manipulated by RDBMS users
through the use of special data manipulation
language.
• Database structures can also be defined by
the use of data definition language.
• SQL is the most popular database language
and has commands that enable it to be used
for both manipulation and definition of
databases.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-54
SQL
•
SQL is used by Oracle for all interaction
with the database. SQL statements fall
into two major categories:
1. Data Definition Language (DDL): Set of
SQL commands that create and define
objects in a database.
2. Data Manipulation Language (DML): Set
of SQL commands that allows users to
manipulate the data in a database.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-55
SQL
• SQL is basically a free format language.
This means that there are no particular
spacing rules that must be followed when
typing SQL commands.
• SQL is a nonprocedural language. This
means that the user only has to specify the
task for the DBMS to complete, but not
how the task is to be completed. The
RDBMS parses (converts) the SQL
commands and completes the task.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-56
SQL*PLUS
Through SQL*PLUS users can:
• Enter, edit, store, retrieve, and run SQL
commands and PL/SQL blocks.
• Format, perform calculations on, store, and
print query results in the form of reports.
• List column definitions for any table.
• Access and copy data between SQL
databases.
• Send messages to and accept responses from
an information system user.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-57
RELATIONAL OPERATIONS
• SQL operations for creating new tables,
inserting table rows, updating table rows,
deleting table rows, and querying
databases are the primary means of
interfacing with relational databases.
• The SELECT statement is used primarily to
write queries that extract information from
the database which is a collection of
related tables.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-58
The SELECT Statement
• The power of SELECT statement comes from its
ability to combine data from many tables to
produce output in the form of a result table.
• A select statement can include or exclude
specific columns from one or more tables.
SELECT emp_ssn, emp_last_name
FROM employee;
EMP_SSN
EMP_LAST_NAME
--------- ------------999666666 Bordoloi
999555555 Joyner
999444444 Zhu
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-59
Selection Operation
• A selection operation selects a subset of
rows in a table (relation) that satisfies a
selection condition. That subset can range
from no rows to all rows in a table.
SELECT emp_ssn, emp_first_name
FROM employee
WHERE emp_ssn = '999111111';
EMP_SSN
EMP_FIRST_NAME
--------- --------------999111111 Douglas
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-60
Projection Operation
• A projection operation selects only certain
columns from the table, thus producing a
subset of all available columns.
• The result table can include anything from
a single column to all the columns in the
table.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-61
EXAMPLE
• This SELECT statement selects a subset
of columns from the employee table by
specifying the columns to be listed.
SELECT emp_ssn, emp_first_name, emp_last_name
FROM employee;
EMP_SSN
--------999666666
999555555
999444444
more rows
EMP_FIRST_NAME
-------------Bijoy
Suzanne
Waiman
will be displayed
EMP_LAST_NAME
-----------------Bordoloi
Joyner
Zhu
. . .
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-62
Join Operation
• A join operation combines data from two or
more tables based upon one or more
common column values.
• The relational join is a very powerful operation
because it allows users to investigate
relationships among data elements.
• The following SELECT statement displays
column information from both the employee
and department tables.
• This SELECT statement also completes both
selection and projection operations.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-63
Example
• The tables are joined upon values stored in the department
number columns named emp_dpt_number in the employee
table and dpt_no in the department table.
SELECT emp_ssn, emp_first_name, emp_last_name, emp_dpt_number
FROM employee e, department d
WHERE e.emp_dpt_number = d.dpt_no;
EMP_SSN
--------999666666
999555555
999444444
more rows
EMP_FIRST_NAME EMP_LAST_NAME
EMP_DPT_NUMBER
--------------- --------------- -------------Bijoy
Bordoloi
1
Suzanne
Joyner
3
Waiman
Zhu
7
will be displayed . . .
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-64
SQL*PLUS
•
•
SQL*PLUS enables the user to manipulate
SQL commands and to perform many
additional tasks as well.
Through SQL*PLUS, a user can:
1. Enter, edit, store, and retrieve SQL commands.
2. Format, perform calculations on, store, and
print query results in the forms of reports.
contd
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-65
SQL*PLUS
3. List column definitions for any table.
4. Access and copy data between SQL
databases.
5. Send messages to and accept responses
from a system user.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-66
Starting SQL*PLUS
•
•
•
The most common types used are either the
standard Oracle SQL*PLUS available for a
Windows-type interface or by connecting to
an Oracle database via a telnet session.
The following Log On session is a connection
to an Oracle database stored on a Sun
Microsystems server running the Unix O/S.
An SQL*PLUS session is begun by typing the
command sqlplus at the command prompt($)
and entering the username and password
information.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
contd
1-67
Log-On Screen
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-68
Exiting SQL*PLUS
•
•
The SQL*PLUS session can be ended by
typing a command at the SQL prompt to
terminate the session.
Two commands are available: exit or quit.
Either one of these can be used to
terminate an SQL*PLUS session.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-69
Running (Executing) a Command
File
•
The command to run (execute) a
command file is:
SQL> START filename.sql
•
A form of shorthand for the word “start” can
be used when executing a command file
by replacing “start” with the @ (“at”
symbol).
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-70
Syntax Conventions
•
•
Each select statement must follow precise
syntactical and structural rules.
The following is the minimum structure and
syntax required for an SQL SELECT
statement.
SELECT [DISTINCT | ALL] {* |
select_list}
FROM {table_name [alias] | view_name}
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-71
SQL Keywords
•
•
Keywords are words that have a
predefined meaning in SQL.
In practice, keywords may be entered in
upper or lower case letters.
SELECT *
FROM employee;
select *
from employee;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-72
SQL Keywords
•
•
In some cases keywords can be
abbreviated.
The allowed abbreviation is shown in
upper case letters with the remainder
shown in lower case, which means either
the full word or only the upper case part
can be used.
DESCribe: can be entered as either DESC
or DESCRIBE.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-73
SQL Naming Conventions
•
Identifiers are the names given by
information system developers or system
users to database objects such as tables,
columns, indexes, and other objects as
well as the database itself.
• There are several rules for naming
database objects that must be followed.
1. Identifiers should contain between 1 and
30 characters.
2. The first character must be either
alphabetic (a-z, A-Z) or the @ symbol or _
(underscore).
1-74
DAVID M. KROENKE’S
DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
SQL Naming Conventions
3. After the first character, digits, letters, or
the symbols $, #, or _(underscore) must be
used.
4. No embedded spaces are allowed in
identifiers.
5. SQL keywords cannot be used as an
identifier.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
1-75