Structured Query Language

1
Structured Query Language

• Introductions and Data Definitions

2
Objectives

• Create and run SQL Commands
• Create tables Using SQL
• Identify and use data types to define the columns
  in SQL tables
• Understand and use nulls
• Add rows of data to tables
• Understand and use Integrity Constraints
• Understand and use System Commands

3
SQL
Data in relational database tables are inserted,
retrieved, and modified using commands called
queries. Queries are performed using high-level
query languages that use standard English
commands such as INSERT, UPDATE, DELETE, and
SELECT.
4
Oracle 8 Environment

• SQLPlus
• Query Builder
• PL/SQL Procedure Builder
• Developer
• Form Builder
• Report Builder
• Graphic Builder

• Enterprise Manager
• Security Manager
• Storage Manager
• Instance Manager
• Oracle Web Application Server

5
Naming Conventions

• Names cannot be longer than 30 Characters in
  length.
• The names must start with a letter.
• The names can contain letters, numbers, and
  underscores.
• The names cannot contain spaces

6
Data Types

• Character Data Type
• VARCHAR2
• CHAR
• NCHAR
• Long
• Number Data Type

• Date Data Type
• LOB Data Type
• BLOB
• CLOB
• BFILE
• NCLOB

• Character Data Type
• VARCHAR2
• CHAR
• NCHAR
• Long
• Number Data Type
• Integer
• Fixed-Point (Precision, Scale)
• Floating-Point

• Character Data Type
• VARCHAR2
• CHAR
• NCHAR
• Long
• Number Data Type
• Integer
• Fixed-Point (Precision, Scale)
• Floating-Point

7
Data Type Examples

• Varchar2(10) Entering the word Bill
• Storing the word Bill Bill(The Space is
  Reallocated from 10 to 4 characters)
• Char(10) Entering the word Bill
• Storing the word Bill Bill where
  Added Spaces(The Space is not reallocated, it
  takes up all 10 spaces)
• Number(Precision,Scale)
• Precision Total Number of Digits
• Scale Total Number of Digits to the right of
  the decimal place.ExamplesInteger
  Number(5) - Biggest number would be 99999Fixed
  point Number(5,2) - Biggest number would be
  999.99Floating Point Number - Could be 99.9 or
  99999 or 9.999

8
SQL Editing Commands

• LIST Run the command currently in the buffer
  (L)ltline numbergt Changes the current line and
  displays the number. APPEND lttextgt Add text at
  the end of the current line (A)CHANGE /old
  text/new text Change text in current line
  (C)DELETE Delete the current line
  (DEL)INSERT lttextgt Insert line following
  current line (I) LIST ltline numbergt Shows the
  indicated line number text. (L)/ Executes the
  contents of the edit buffer

Use these commands at your own risk, because they
are like the old DOS Commands Very cryptic and
difficult to use.
9
SQL Alternative Editing

• Easier to create all of your commands in a text
  editor such as Notepad and then copy and paste
  them into SQL Plus.
• Allows you to save your work easier and if your
  commands do not work properly in SQL, then you
  can easily edit them in Notepad and then copy
  them back into SQL Plus.

10
Create Table Command
CREATE TABLE LOCATION ( locid Number(5) PRIMARY
KEY, bldg_code Varchar2(10) NOT NULL, room
Varchar2(6) NOT NULL, capacity Number(5) NOT
NULL )
11
General Integrity Constraints (Syntax)
CONSTRAINT ltconstraint namegt CONSTRAINT
TYPEltconstraint namegt tablename_fieldname_const
raintID CONSTRAINT TYPES PRIMARY KEY ID
PK REFERENCES ID FK NOT NULL ID NN
Notes The Constraint Name and Constraint Types
(PK, FK, and NN) are arbitrary and can be
whatever you want them to be. However, we will
use the tablename_fieldname_constraintID for the
constraint name format. Secondly, the key words
Primary Key, References, and Not Null determine
what declaration is actually assigned to each
field.
We use the Integrity Constraints for
documentation, error correction detection, and
database integrity
12
Integrity Constraint Primary Keys

• Place the primary key constraint info in the
  Primary Table when you first create the table
• Example for the Sales_Rep Tablethe constraint
  info would be as followsSls_Rep Char(2)
  CONSTRAINTSales_Rep_Sls_Rep_PK PRIMARY KEY

Field Name
Data Type
Keyword
Arbitrary Constraint Name. Format
-Tablename_Fieldname_ConstraintID
Makes Sls_Rep The Primary Key of the Sales_Rep
Table
Arbitrary Constraint Name. Format
-Tablename_Fieldname_ConstraintID
13
Create Table Command Illustrating the Use of the
Primary Key Constraint
CREATE TABLE Sales_Rep (sls_rep Char(2)
CONSTRAINT Sales_Rep_sls_rep_pk PRIMARY
KEY,First_name Varchar2(15),Last_name
Varchar2(25))
14
Integrity Constraint Not Null

• Place Not Null Constraint info in either the
  Primary or Secondary Table when creating the
  tablesNOTE When you set up a Primary or
  Foreign Key you do not need to set those as Not
  Null.
• Example For the Customer Table the constraint
  info would be as follows
• Cus_LName VarChar(15) CONSTRAINTCustomer_Cus_LN
  ame_NN NOT NULL

Field Name
Data Type
Keyword
Makes Cus_LName a required field in the Customer
Table
Arbitrary Constraint Name. Format
-Tablename_Fieldname_ConstraintID
15
Create Table Command Illustrating the Use of the
Not Null Constraint
CREATE TABLE Customer (Cust_Num Char(2)
CONSTRAINT Customer_Cust_Num_pk PRIMARY
KEY,Cus_FName Varchar2(15) CONSTRAINT
Customer_Cust_FName_nn NOT NULL,Cus_FName
Varchar2(15) CONSTRAINT Customer_Cust_LName_nn
NOT NULL)
16
Integrity Constraint Foreign Keys

• Place Foreign Key Constraint info in the Many
  or Secondary Table when creating the
  tableNOTE You must create the Primary Table
  and Primary Key first.
• Example Customer Table
• Sls_Rep Char(2) CONSTRAINT
• Customer_Sls_Rep_FK REFERENCESSales_Rep(Sls_R
  ep)

17
Create Table Command Illustrating the Use of the
Foreign Key Constraint
CREATE TABLE Customer (Cust_Num Char(2)
CONSTRAINT Customer_Cust_Num_pk PRIMARY
KEY,Cus_FName Varchar2(15) CONSTRAINT
Customer_Cust_FName_nn NOT NULL,Cus_FName
Varchar2(15) CONSTRAINT Customer_Cust_LName_nn
NOT NULLSls_Rep Char(2) CONSTRAINT
Customer_Sls_Rep_fk REFERENCES Sales_Rep(Sls_Rep)
)
18
The Insert Command

• INSERT INTO LocationVALUES (53, BUS, 424,
  1)
• INSERT INTO Faculty (fid, flname, ffname, fmi,
  locid)VALUES (1, Cox, Kim, J, 53)
• INSERT INTO StudentVALUES (101, Umato,
  Brian, D, 454 St. Johns Street, Eau
  Claire, WI, 54702, 7155552345, SR,
  TO_DATE(08/19/1979, MM/DD/YYYY), 1230, 1)

19
Data Dictionary (Metadata)

• The Data Dictionary contains all of the
  information that describes the database itself.

• We can find
• Constraint Names
• Tables Names
• Primary Keys
• Column Names
• View Definitions
• Column Names and Datatypes

20
Constraint Names

• List all Constraints that you set up in your
  tables
• ExampleSelect CONSTRAINT_NAMEFrom
  USER_CONSTRAINTSWhere TABLE_NAME TABLE_NAME

Note All Orange Words should be type as listed
in either upper case or lowercase. The White
Table_Name is the name of an existing table and
should be typed in ALL Uppercase letters.
21
Table Names

• List all Table Name that you set up in your
  database
• ExampleSelect TABLE_NAMEFrom USER_TABLES

22
Table Columns

• List the columns of one or all Tables that you
  set up in your database
• ExampleSelect COLUMN_NAMEFrom
  USER_TAB_COLUMNSWhere TABLE_NAME TABLE_NAME

23
Table Structure

• List the Columns and datatypes, along with all
  other table structures that are set up on a
  particular Table that you set up in your
  database
• ExampleDESCRIBE TABLE

24
Data Dictionary Commands

• SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS
  WHERE TABLE_NAME TABLE
• SELECT TABLE_NAME FROM USER_TABLES
• SELECT FROM USER_CONS_COLUMNSNAME WHERE
  TABLE_NAME TABLE
• SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE
  TABLE_NAME TABLE
• SELECT VIEW_NAME FROM USER_VIEWS
• DESCRIBE TABLE

25
Structured Query Language

• Introductions and Data Definitions
• THE END