Transcript Basic SQL and SQL Plus - Department of Computer and Information

Department of Computer and Information Science,
School of Science, IUPUI
Basic SQL and SQLPlus
- Querying using SELECT
Dale Roberts, Lecturer
Computer Science, IUPUI
E-mail: [email protected]
Dale Roberts
1
Relational Data Model
RDBMS’s are based on the Relational Data
Model.
A data model is a notation for describing data.
The notation generally covers conceptually how
data is structured (records, structures, relations,
etc.), operations on the data (queries and
modifiers), and constraints (domain, cross
references).
The two preeminent data models are
Relational – all commercial databases
XML – added feature to most databases
(This Relational Data Model discussion
comes from the Ullman text)
Dale Roberts
2
Relational Terminology
The Relational Data Model is based on relations.
title
year
length
genre
Gone With the Wind
1939
231
drama
Star Wars
1977
124
sciFi
Wayne’s World
1992
95
comedy
The relation Movies
(our class standards would name this Movie.)
Within the Relational Data Model, the columns are
called attributes, and the rows are called tuples.
The name of the relation and the set of attributes is
called the schema of the relation:
Movies(title, year, length, genre)
Dale Roberts
3
Attributes and Tuples
Attributes are a set, not a list. However, there is
a “standard” order of attributes that are used for
display when another order is not specified.
Other than the header row, the data is a tuple
where each tuple has one component per
attribute.
(Gone With the Wind, 1939, 231, drama)
Each attribute must be atomic, meaning that
each attribute must be an elementary data type
like integer or string. They cannot be structured
data types.
Dale Roberts
4
Relation Instances
The tuples that comprise a relation are not static
over time. Tuples are inserted as new movies
come out, and updated as data changes.
Each set of tuples represents an instance of the
relation.
A conventional database system only maintains
one version of the tuples that are currently in
the relation. This is the current instance.
Dale Roberts
5
Keys of a Relation
A set of attributes forms a key of a relation if no
two tuples can have the same values.
A key is an example of a constraint. The DBMS
supports many constraints, but the key
constraint is so important it is discussed as part
of the relation definition.
Movies(title, year, length, genre)
Underlined attributes identify the key. The
attributes title and year together can be used to
identify a single Movies tuple.
Dale Roberts
6
Defining a Relational Schema in SQL
Terminology shift:
Relation → Table or View
Attribute → Column
Tuple → Row
A stored relation it a table.
A dynamically constructed relation is a view.
Temporary tables are created and dropped by
the database engine as needed during queries
and data modifications.
Dale Roberts
7
Defining a Relational Schema in SQL
The CREATE keyword is used in SQL to create
tables and views.
CREATE TABLE Movies(
CREATE TABLE MOVIE(
title CHAR(100),
TITLE_TXT VARCHAR2(100),
year INT,
YEAR_NBR NUMBER(4),
length INT,
LENGTH_NBR NUMBER(3),
genre CHAR(10),
GENRE_CD VARCHAR2(10),
studioName CHAR(30),
STUDIO_NAME VARCHAR2(30),
producerC# INT,
PRODUCER_CERT_NBR NUMBER(9),
PRIMARY KEY (title, year) PRIMARY KEY (TITLE_TXT , YEAR_NBR )
);
);
Standard SQL
Typical Enterprise Oracle SQL
Dale Roberts
8
Using SELECT with Relations
The SQL SELECT statement is used to query
relations.
SELECT statements return a result set, which
itself is a relation.
Because the SELECT operation is closed over
the set relations, the SELECT statements can be
nested to perform complex queries.
R1
SELECT
M
R2
N
R3
M x N
Dale Roberts
9
Using SELECT with Relations
SELECT statements can be nested, and the
SELECT statement itself allows many tables to
participate.
SELECT always produces a single result set.
The relations can be tables or views or
temporary tables.
R1
SELECT
R3
R2
R1
R4
Dale Roberts
SELECT
R5
Relation Compatibility
SELECT statements return a result set with
shape M x N.
If M=1, the result set is compatible with a list (IN
1
keyword).
M
If N=1, M=1, the result set is compatible with a
single value.
1
1
Dale Roberts
11
The Basic Parts of Speech in SQL
•
SQL is a language. Oracle7 SQL is a superset of the American National
Standards Institute (ANSI) and the International Standards Organization (ISO)
SQL92 standard at entry level conformance.
•
PL/SQL is Oracle’s procedural language extension to SQL. It allows you to
link several SQL commands through procedural language.
•
SQL*Plus (SQLPLUS from command line) is a tool that allows users to
interact with Oracle. SQL*Plus enables you to manipulate SQL commands and
PL/SQL blocks, and to perform many additional tasks as well. Through
SQL*Plus, you 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 end user
Dale Roberts
12
The Basic Parts of Speech in SQL
SQL*Plus,… very quick overview
first, create/run a script to start an Oracle database instance
from the command line:
sqlplus or sqlplusw
SQL>
Common commands in SQL*Plus:
get <filename>
@<filename>
save <filename>
list or l
change or c
edit
save
! (shell to OS)
Capitalization generally does not matter
Dale Roberts
13
The Basic Parts of Speech in SQL
Simple Select Examples
•
Select statement, single table query, all rows:
SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE
FROM CUSTOMER
ORDER BY CUSTOMER_NUMBER;
•
... specific rows (where clause):
SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE
FROM CUSTOMER
WHERE CUSTOMER_NUMBER > 500;
•
... with a calculation:
SELECT CUSTOMER_NUMBER, LAST, (CREDIT_LIM - BALANCE)
FROM CUSTOMER
WHERE CUSTOMER_NUMBER > 500;
•
... multiple where conditions (and):
SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE
FROM CUSTOMER
WHERE BALANCE >= 500
AND BALANCE <= 1000;
Dale Roberts
14
The Basic Parts of Speech in SQL
•
•
The SELECT clause:
– can be a list of columns,
–
an asterisk * for all columns,
–
a calculation (or other expressions),
–
group functions (chapter 9),
–
distinct keyword -- removes duplicates.
SELECT DISTINCT SUPPLIER
FROM PRODUCTS;
–
column names can be renamed in the output
with an alias.
SELECT CUSTOMER_NUMBER, LAST, FIRST,
(CREDIT_LIM - BALANCE) AS REMAINING_CREDIT
FROM CUSTOMER;
The FROM clause:
– in single tables queries, just provide your table’s name …. more on this in multi-table queries.
Dale Roberts
15
The Basic Parts of Speech in SQL
•
The WHERE Clause:
– this is a logical, boolean expression which must evalute to true for each row in the query’s
output.
–
where clause comparison operators:
• =
equal to
• <, >
less than, greater than
• <=, >= less than or equal, greater than or equal
• <>, != , ^= not equal
–
compound conditions are built with the boolean operators AND, OR.
–
AND is evaluated first by default. Use parenthesis ( ) to force the order of OR/AND.
–
a compliment of a condition can be evaluated with NOT. Examples below are equivalent:
SELECT PART_DESCRIPTION FROM PART
WHERE WAREHOUSE_NUMBER != ‘3’;
SELECT PART_DESCRIPTION FROM PART
WHERE NOT (WAREHOUSE_NUMBER=‘3’);
Dale Roberts
16
The Basic Parts of Speech in SQL
• The WHERE Clause (continued):
– BETWEEN keyword is the same as >= and =<.
For example, these 2 statements are the same:
SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE
FROM CUSTOMER
WHERE 500 <= BALANCE AND BALANCE <= 1000;
SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE
FROM CUSTOMER
WHERE BALANCE BETWEEN 500 AND 1000;
– LIKE keyword is used for pattern matching.
‘%’ is for any number of characters.
‘_’ (underscore) is for one character.
SELECT CUSTOMER_NUMBER, LAST, FIRST
FROM CUSTOMER WHERE LAST LIKE ‘JONE%’;
SELECT CUSTOMER_NUMBER, LAST, FIRST
FROM CUSTOMER WHERE LAST LIKE ‘JONE_’;
Dale Roberts
17
The Basic Parts of Speech in SQL
•
The WHERE Clause (continued):
– IN keyword provides a list of numbers or strings to compare to. This is similar to using OR
with =.
For example, these 2 statements are the same:
SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE
FROM CUSTOMER
WHERE CUSTOMER_NUMBER IN (10, 11, 12);
SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE
FROM CUSTOMER
WHERE (CUSTOMER_NUMBER = 10)
OR (CUSTOMER_NUMBER = 11)
OR (CUSTOMER_NUMBER = 12);
–
NULL and NOT NULL keywords:
NULL does not mean 0, null means no value!
Example- this statement only gets customers whom we have a complete name for:
SELECT CUSTOMER_NUMBER, LAST, FIRST
FROM CUSTOMER
WHERE (FIRST IS NOT NULL)
AND (LAST IS NOT NULL);
Dale Roberts
18
The Basic Parts of Speech in SQL
• The WHERE Clause (continued):
– Values can also be compared to another query in the where clause. This is
called a subquery. For example:
SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE
FROM CUSTOMER
WHERE CUSTOMER_NUMBER IN
(SELECT CUSTOMER_NUMBER
FROM CUSTOMER
WHERE BALANCE > 500);
This however may not work sometimes with a subquery:
SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE
FROM CUSTOMER
WHERE CUSTOMER_NUMBER =
(SELECT CUSTOMER_NUMBER
FROM CUSTOMER
WHERE BALANCE > 500);
because subqueries return sets of values, not a single value.
Dale Roberts
19
The Basic Parts of Speech in SQL
• The WHERE Clause (continued):
– Tables can be joined by common attributes.
This is done in the where clause.
SELECT WEATHER.CITY, CONDITION,
TEMPERATURE, LATITUDE,. . .
FROM WEATHER, LOCATION
WHERE WEATHER.CITY = LOCATION.CITY;
– A shortcut (not in book). Tablenames can be aliased to save typing, reduce
wordiness.
SELECT W.CITY, CONDITION,
TEMPERATURE, LATITUDE,. . .
FROM WEATHER W, LOCATION L
WHERE W.CITY = L.CITY;
– Joining data from different tables is one of the more powerful parts of
SQL. It is not without its dangers.
• Joining tables the wrong way (not on common attributes),
the wrong data comes back.
• Forgetting to specify common keys in the where clause returns a “cartesian product” (each
row of both tables joined to each other, m times n rows)
Dale Roberts
20
The Basic Parts of Speech in SQL
•
The ORDER BY Clause:
– Specifies the ordering of the returned data.
– Multiple columns can be selected to sort on.
– ASC is ascending order order, default.
– DESC is descending sort order.
SELECT FEATURE, SECTION, PAGE
FROM NEWSPAPER
WHERE SECTION = ‘F’
ORDER BY PAGE DESC, FEATURE ASC;
•
Views:
– Makes a query act similar to a table.
– Views are “describe”-able, “select”-able
– The view’s select statement is run everytime rows are selected from the view--ie data is not
saved somewhere (like a table)
CREATE VIEW INVASION AS
SELECT W.CITY, CONDITION,
TEMPERATURE, LATITUDE
FROM WEATHER W, LOCATION L
WHERE W.CITY = L.CITY;
Dale Roberts
21
Acknowledgements
Loney, Kevin. Oracle Database 10g The Complete
Reference.
McFadden and Hoffer. Database Management, pp.
214-216.
Ullman, Jeffrey and Widom, Jennifer. A First Course
in Database Systems.
3/26/2016
Dale Roberts
22