Transcript Structured Query Language

CIT 214 Introduction to
Database Management
Structured Query Language:
Introductions, Data Definitions
and Data Administration
1
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
View table data
Understand and use Integrity Constraints
Understand and use System Commands
2
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.
3
Oracle Environment




SQL*Plus 
Query Builder
PL/SQL & Procedure
Builder
Developer




Enterprise Manager




Security Manager
Storage Manager
Instance Manager
Oracle Web Application
Server
Form Builder
Report Builder
Graphic Builder
If watching archived, click on the Notes tab for additional info.
4
SQL Commands



SQL commands are free format
Press the Enter key at the end of each line
and then continue typing the command on
the next line
Indicate the end of a command line by typing
a semicolon
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 


Date Data Type
LOB Data Type
Number Data Type



Integer 
Fixed-Point
(Precision, Scale) 
Floating-Point 
If watching archived, click on the Notes tab for additional info.
7
Data Type Examples

Varchar2(10) Entering the word Bill


Char(10) Entering the word Bill


Storing the word Bill = Bill
(The Space is Reallocated from 10 to 4 characters)
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.
Examples:
Integer = Number(5) - Biggest number would be 99999
Fixed point = Number(5,2) - Biggest number would be 999.99
Floating Point = Number - Could be 99.9 or 99999 or 9.999
8
SQL Editing Commands
•LIST: Run the command currently in the buffer (L)
<line number>: Changes the current line and displays the
number.
APPEND <text>: 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 <text>: Insert line following current line (I)
LIST <line number>: 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
Creating a Table

Describe the layout of each table in the
database

Use CREATE TABLE command

TABLE is followed by the table name

Follow this with the names and data types of the
columns in the table

Data types define type and size of data
11
Create Table Command
CREATE TABLE LOCATION
(locid Number(5) PRIMARY KEY,
bldg_code Varchar2(10) ,
room Varchar2(6) ,
capacity Number(5) );
12
Integrity Rules in SQL


Integrity Constraints are used to apply
business rules for the database tables.
The constraints available in SQL are Foreign
Key, Not Null, Unique, Check.
http://beginner-sql-tutorial.com/sql-integrity-constraints.htm
If watching archived, click on the Notes tab for additional info.
13
Integrity Rules in SQL
http://beginner-sql-tutorial.com/sql-integrity-constraints.htm

Constraints can be defined in two ways



The constraints can be specified immediately after
the column definition. This is called column-level
definition.
The constraints can be specified after all the
columns are defined. This is called table-level
definition.
The Chapter on Database Administration focuses
on table-level definitions, but we will focus on
column-level definitions in this lecture.
If watching archived, click on the Notes tab for additional info.
14
General Integrity Constraints
(Syntax)


The <Constraint Name> is arbitrary, but we
will adhere to the following naming
convention when possible.
Syntax:


TableName_ColumnName_ConstraintID
The Constraint IDs are as follows:





Primary Key = PK
Foreign Key = FK
Not Null = NN
Legal Values (Check)= CK
Unique = UN
15
Primary Key Integrity
Constraint

This constraint defines a column or
combination of columns which uniquely
identifies each row in the table.

CREATE TABLE employee
( id number(5) CONSTRAINT employee_id_pk PRIMARY KEY,
name char(20),
dept char(10),
Primary Key Integrity Constraint
age number(2),
salary number(10),
location char(10)
);
http://beginner-sql-tutorial.com/sql-integrity-constraints.htm
16
Foreign Key Integrity
Constraint

This constraint identifies any column
referencing the PRIMARY KEY in another
table. It establishes a relationship between
two columns in the same table or between
different tables. For a column to be defined
as a Foreign Key, it should be defined as a
Primary Key in the table which it is referring.
http://beginner-sql-tutorial.com/sql-integrity-constraints.htm
17
Foreign Key Integrity
Constraint http://beginner-sql-tutorial.com/sql-integrity-constraints.htm

Main Table

CREATE TABLE product
( product_id number(5) CONSTRAINT product_pd_id_pk PRIMARY KEY,
product_name char(20),
supplier_name char(20),
unit_price number(10));

Secondary Table

CREATE TABLE items
( order_id number(5) CONSTRAINT items_od_id_pk PRIMARY KEY,
product_id number(5) CONSTRAINT items_pd_id_fk REFERENCES,
product(product_id),
Foreign Key Integrity Constraint
product_name char(20),
supplier_name char(20),
unit_price number(10));
If watching archived, click on the Notes tab for additional info.
18
Not Null Integrity Constraint

This constraint ensures all rows in the table
contain a definite value for the column which
is specified as not null. This means a null
value is not allowed in that field.

CREATE TABLE employee
( id number(5),
name char(20) CONSTRAINT employee_name_nn Not Null,
dept char(10),
Not Null Integrity Constraint
age number(2),
salary number(10),
location char(10)
);
http://beginner-sql-tutorial.com/sql-integrity-constraints.htm
19
Unique Key Integrity
Constraint

This constraint ensures that a column or a
group of columns in each row have a distinct
value. The column(s) can have null values
but the values cannot be duplicated.

CREATE TABLE employee
( id number(5),
name char(20),
dept char(10),
age number(2),
Unique Key Integrity Constraint
salary number(10),
location char(10) CONSTRAINT employee_location_un Unique );
http://beginner-sql-tutorial.com/sql-integrity-constraints.htm
20
Check Integrity Constraint

This constraint defines a business rule on a
column. All the rows must satisfy this rule.
The constraint can be applied for a single
column or a group of columns.
CREATE TABLE employee
( id number(5),
name char(20),
dept char(10),
age number(2),
gender char(1) CONSTRAINT emp_gender_ck Check (gender in (‘M’, ‘F’)),
salary number(10),
Check Integrity Constraint
location char(10));
http://beginner-sql-tutorial.com/sql-integrity-constraints.htm
21
Create Table Command
Illustrating the Use Of Multiple
Integrity Constraint
CREATE TABLE employee
( id number(5) CONSTRAINT employee_id_pk PRIMARY KEY,
name char(20) CONSTRAINT employee_name_nn Not Null,
dept char(10),
age number(2),
gender char(1) CONSTRAINT employee_gender_ck Check (gender in (‘M’, ‘F’)),
salary number(10),
location char(10) CONSTRAINT employee_location_un Unique );
If watching archived, click on the Notes tab for additional info.
22
Adding Rows to a Table



Once tables are created in a database, data can be
loaded into them by using the INSERT command
The INSERT command adds rows to a table
To use this command:


Type INSERT INTO followed by the name of the table into
which data is being added
Type the VALUES command followed by the specific
values to be inserted in parentheses
23
The INSERT Command
with Nulls


To enter a null value into a table, a special
format of the INSERT command must be
used
Identify the names of the columns that will
accept non-null values, and then list only
these non-null values after the VALUES
command
24
The Insert Command
1.
2.
3.
INSERT INTO Location
VALUES (53, ‘BUS’, ‘424’, 1);
INSERT INTO Faculty
(fid, flname, ffname, fmi, locid)
VALUES (1, ‘Cox’, ‘Kim’, ‘J’, 53);
INSERT INTO Student
VALUES (101, ‘Umato’, ‘Brian’, ‘D’, ‘454
St. John’’s Street’, ‘Eau Claire’, ‘WI’,
‘54702’, ‘7155552345’, ‘SR’);
If watching archived, click on the Notes tab for additional info.
25
Viewing Table Data

Use SELECT command


Can display all the rows and columns in a table
SELECT * FROM followed by the name of the
table and end with a semicolon

Select * From Employee;
26
System Catalog





Information about tables in the database is kept in
the system catalog or the data dictionary
The system catalog is a relational database
Information can be retrieved by using the same
types of queries which are used to retrieve data in a
relational database
The DBMS updates the system catalog
automatically
Users should not use SQL queries to update the
catalog directly because this might produce
inconsistent information
27
Constraint Names

List all Constraints that you set up in your
tables:

Example:
Select CONSTRAINT_NAME
From USER_CONSTRAINTS
Where TABLE_NAME = ‘CUSTOMER’;
Note: All Words can be typed in either uppercase or lowercase
and should be typed as they appear above. The word
“CUSTOMER” listed in red is the name of an existing table and
should be typed in ALL Uppercase letters.
If watching archived, click on the Notes tab for additional info.
28
Table Names

List all Table Names that you created in your
database:

Example:
Select TABLE_NAME
From USER_TABLES;
29
Table Structure

List the Columns and data types, along with
all other table structures that are set up on a
particular Table that you set up in your
database:

Example:
DESCRIBE Customer;
Note: All Words can be typed in either uppercase or lowercase.
The word “Customer“ listed in red is the name of an existing table.
30
Summary








Created and ran SQL Commands
Created tables Using SQL
Identified and used data types to define the columns
in SQL tables
Introduced and used nulls
Added rows of data to tables
Viewed table data
Introduced and used Integrity Constraints
Introduced and used System Commands
http://beginner-sql-tutorial.com/sql-commands.htm
31
THE END
You have completed the Lecture for:
Structured Query Language: Introductions, Data
Definitions and Data Administration
32