Department of Computer and Information Science, School of Science, IUPUI

1
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 droberts_at_cs.iupui.edu

2
Relational Data Model

• RDBMSs 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)
3
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
Waynes 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)

4
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.

5
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.

6
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.
• Underlined attributes identify the key. The
  attributes title and year together can be used to
  identify a single Movies tuple.

Movies(title, year, length, genre)
7
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.

8
Defining a Relational Schema in SQL

• The CREATE keyword is used in SQL to create
  tables and views.

CREATE TABLE Movies( title CHAR(100), year
INT, length INT, genre CHAR(10),
studioName CHAR(30), producerC INT,
PRIMARY KEY (title, year) )
CREATE TABLE Movie( title_TXT VARCHAR2(100),
year_NBR NUMBER(4), length_NBR NUMBER(3),
genre_CD VARCHAR2(10), studio_Name
VARCHAR2(30), producer_CERT_NBR NUMBER(9),
PRIMARY KEY (title_TXT , year_NBR ) )
Standard SQL
Typical Enterprise Oracle SQL
9
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.

N
R1
R3
SELECT
M
R2
M x N
10
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
R3
SELECT
R2
R1
R5
SELECT
R4
11
Relation Compatibility

• SELECT statements return a result set with shape
  M x N.
• If M1, the result set is compatible with a list
  (IN keyword).
• If N1, M1, the result set is compatible with a
  single value.

1
M
1
1
12
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 Oracles procedural language extension
  to SQL. It allows you to link several SQL
  commands through procedural language.
• SQLPlus (SQLPLUS from command line) is a tool
  that allows users to interact with Oracle.
  SQLPlus enables you to manipulate SQL commands
  and PL/SQL blocks, and to perform many additional
  tasks as well. Through SQLPlus, 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

13
The Basic Parts of Speech in SQL

• SQLPlus, very quick overview
• first, create/run a script to start an Oracle
  database instance
• from the command linesqlplus or sqlpluswSQLgt
• Common commands in SQLPlus
• get ltfilenamegt
• _at_ltfilenamegt
• save ltfilenamegt
• list or l
• change or c
• edit
• save
• ! (shell to OS)
• Capitalization generally does not matter

14
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 gt 500
• ... with a calculation
• SELECT CUSTOMER_NUMBER, LAST, (CREDIT_LIM -
  BALANCE)
• FROM CUSTOMER
• WHERE CUSTOMER_NUMBER gt 500
• ... multiple where conditions (and)
• SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE
• FROM CUSTOMER
• WHERE BALANCE gt 500
• AND BALANCE lt 1000

15
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
  tables name . more on this in multi-table
  queries.

16
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 querys
  output.
• where clause comparison operators
• equal to
• lt, gt less than, greater than
• lt, gt less than or equal, greater than or equal
• ltgt, ! , 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_NUMBER3)

17
The Basic Parts of Speech in SQL

• The WHERE Clause (continued)
• BETWEEN keyword is the same as gt and lt. For
  example, these 2 statements are the
  same SELECT CUSTOMER_NUMBER, LAST, FIRST,
  BALANCE
• FROM CUSTOMER
• WHERE 500 lt BALANCE AND BALANCE lt 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_

18
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 keywordsNULL 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)

19
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 gt 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 gt 500) because subqueries return sets
  of values, not a single value.

20
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)

21
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 views 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

22
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.