Transcript is not null

IS 380
Introduction to SQL
This lectures covers material from:
database textbook chapter 3
Oracle chapter: 3,14,17
Objective
• Understand the basics of database
languages
• Learn how to manipulate and manage
a database using SQL
IS 380, A. M. Thomason
2
Structure query Language
• SQL meets ideal database language
requirements:
– SQL coverage fits into several categories:
•
•
•
•
•
(1) Data Definition (DDL): Ex: Create Table
(2) Data Management (DML).Ex: Delete
(3) Data Query.
(4) Transaction Control
(5) Data Control Language (DCL)
– SQL is relatively easy to learn. 4 th Gen. Language
– ANSI prescribes a standard SQL.
IS 380, A. M. Thomason
3
Why Study SQL?
• The ANSI standardization effort has led
to a de facto query standard for
relational databases.
• SQL has become the basis for present
and expected future DBMS integration
efforts.
• SQL has become the catalyst in the
development of distributed databases
and database client/server architecture.
IS 380, A. M. Thomason
4
SQL
• Data Definition
• Data Management
• Data Query.
IS 380, A. M. Thomason
5
Data Definition
• Create a database
• Create a Table
• columns names, data types
• Delete a table structure
• Modify a columns data type
IS 380, A. M. Thomason
6
Data Management
• Insert New records
• Modify existing records
• delete records
IS 380, A. M. Thomason
7
Query
Allows you to ask the database
questions or view the table from
different perspectives and
criterion without modifying the
content.
IS 380, A. M. Thomason
8
Creating Tables
CREATE TABLE [user.]table
(column datatype [DEFAULT expr]
[column_constraint],
…
[table_constraint]);
Rules:
name can be no longer than 18 characters
the name must start with a letter
No spaces allowed
Underscores are alright to use.
IS 380, A. M. Thomason
9
Creating a Simple Table
Create table t
(fd_name1 fd_type,
fd_name2 fd_type, …);
Example:
Create table customer (ssn char(9),
customername char(30),
customeraddress char(30), amount
number(10,2));
IS 380, A. M. Thomason
10
Data types
•
•
•
•
Char(n) - SQL specific
varchar(2) - Text field variable lengh
date
number(x,n)
• See page 1060 in Oracle book for
Oracle specific
IS 380, A. M. Thomason
11
Deleting a Table
Drop Table t
Example:
drop table customer
IS 380, A. M. Thomason
12
Deleting records but not
definition
Truncate table t
Example:
Truncate table customer
IS 380, A. M. Thomason
13
Adding new attributes (Columns)
• Alter table t add (fx fd_type, fy fd_type);
Example:
alter table customer add (zip char(5),
phone char(10));
IS 380, A. M. Thomason
14
Modify column definition
Alter table table_name modify (fl newtype,
f2 new_type,…);
Example:
alter table customer modify
(customername char(50));
IS 380, A. M. Thomason
15
Display the table structure
Describe t;
Example:
describe customer;
IS 380, A. M. Thomason
16
Data Management- insert records
Insert into t values (f1value, f2value,, …);
insert into t (f1, f2,…) values (f1value,
f2value,…);
IS 380, A. M. Thomason
17
Data Management- delete
Delete from t where [conditions];
IS 380, A. M. Thomason
18
Data Management- Update
update t set f1=newvalue,
f2=newvalue where [conditions];
IS 380, A. M. Thomason
19
Data Management- save and Undo
Commit; saves your date
rollback; undo, save to last last
commit status
IS 380, A. M. Thomason
20
Copy contents from one table to another
Insert into to-tableselect * from
from-table;
insert into to-table (column1,
column2, …) select column1,
column2,… from from table;
IS 380, A. M. Thomason
21
Data Queries-display records
• Select * from t; selects all records
from table t
• Select * from t where
[conditions];
• Select * from t f1, f2 , ….
Where [conditions];
IS 380, A. M. Thomason
22
Conditions
Mathematical and logical operators
• =
• <>
• >
• <
• >=
• <=
IS 380, A. M. Thomason
23
Conditions-continued
Mathematical and logical operators
• and
• or
• not
Example:
….where price > 10;
where price is > 10 and code <> 1234;
IS 380, A. M. Thomason
24
Nulls and Not Nulls
– Nulls: Is used when the values for one or more
columns are unknown. This is not the same as
spaces. SQL handles the problem of not having a
value by allowing this special value to represent
an unknown or nonapplicable. It is known as a
null data value. When defining the table structure
it can be assigned.
– Not nulls is used for such situations a a key field.
Where you do want to make sure a value is
entered.
IS 380, A. M. Thomason
25
Constraints - advance
• Column Constraints
column [CONSTRAINT constraint_name] constraint_type,
• Table Constraints
[CONSTRAINT constraint_name] constraint_type
(column, …),
If constraint is a foreign key, follow this syntax:
[FOREIGN KEY] (column, …)
REFERENCES table [ (column, …) ]
[ON DELETE CASCADE]
• Constraint Types:
–
–
–
–
–
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
IS 380, A. M. Thomason
26
Sample Tables
• VENDOR and PRODUCT
• 1:M relationship
• Some characteristics to note
– The VENDOR table contains vendors who are not
referenced in the PRODUCT table. PRODUCT is
optional to VENDOR.
– All V_CODE values in the PRODUCT table must
have a match in the vendor table.
– A few products are supplied factory-direct, a few
are made in-house, and a few may have been
bought in a special warehouse sale. That is, a
product is not necessarily supplied by a vendor.
VENDOR is optional to PRODUCT.
IS 380, A. M. Thomason
27
Create Table Examples - vendor Table
CREATE TABLE VENDOR
(VEN_CODE
INTEGER,
VEN_NAME
VARCHAR2(35)
CONSTRAINT VENDOR_VEN_NAME_NN NOT NULL,
VEN_CONTACT
VARCHAR(15)
CONSTRAINT VENDOR_VEN_CONTACT_NN NOT NULL,
VEN_AREACODE CHAR(3)
CONSTRAINT VENDOR_VEN_AREACODE_NN NOT NULL,
VEN_PHONE
CHAR(8)
CONSTRAINT VENDOR_VEN_PHONE_NN NOT NULL,
VEN_STATE
CHAR(2)
CONSTRAINT VENDOR_VEN_STATE_NN NOT NULL,
VEN_ORDER
CHAR(1)
CONSTRAINT VENDOR_VEN_ORDER_NN NOT NULL,
CONSTRAINT VENDOR_VEN_CODE_PK PRIMARY KEY (VEN_CODE));
IS 380, A. M. Thomason
28
Create Table Examples - Product Table
CREATE TABLE PRODUCT
(PROD_CODE
VARCHAR(10),
PROD_DESCRIPT
VARCHAR(35)
CONSTRAINT PRODUCT_PROD_DESCRIPT_NN NOT NULL,
PROD_INDATE
DATE
CONSTRAINT PRODUCT_PROD_INDATE_NN NOT NULL,
PROD_ONHAND
INTEGER
CONSTRAINT PRODUCT_PROD_ONHAND_NN NOT NULL,
PROD_MIN
INTEGER
CONSTRAINT PRODUCT_PROD_MIN_NN NOT NULL,
PROD_PRICE
NUMBER(8,2)
CONSTRAINT PRODUCT_PROD_PRICE_NN NOT NULL,
PROD_DISCOUNT
NUMBER(4,1)
CONSTRAINT PRODUCT_PROD_DISCOUNT_NN NOT NULL,
VEN_CODE
INTEGER,
CONSTRAINT PRODUCT_PROD_CODE_PK PRIMARY KEY (PROD_CODE),
CONSTRAINT PRODUCT_VENDOR_VEN_CODE_FK FOREIGN KEY (VEN_CODE) REFERENCES VENDOR);
IS 380, A. M. Thomason
29
Additional SQL commands
•
•
•
•
•
•
•
Between
like
Order by
Views
In
Null
Union, Intersect, Minus (Page 292 in
Oracle book)
IS 380, A. M. Thomason
30
Between
Select pnum from product where price
between 10 and 20;
IS 380, A. M. Thomason
31
Like
Compares to similar character string
% represents any group of characters
_ represents one character
Ex: … where name like ‘s%’;
….where name like ‘_llen’;
IS 380, A. M. Thomason
32
Like
Compares to similar character string
% represents any group of characters
_ represents one character
Ex: … where name like ‘s%’;
….where name like ‘_llen’;
IS 380, A. M. Thomason
33
Order by
Default is ascending
… order by f1;
For descending:
….order by f1 desc;
IS 380, A. M. Thomason
34
Views
A definition of a restricted portion of a
table.
Create view accounting as
select column1 column2
where price < 100 from Payroll;
IS 380, A. M. Thomason
35
In
Select employee payrollno phone from
accounting where department in (‘IS’,
‘Purchasing’, ‘Benefits’);
The statement above selects those
employees who work in either IS,
Purchasing or benefits. You may also
use not in.
IS 380, A. M. Thomason
36
Null
Select employee payroll phone from
accounting where department is not
null;
Select employee payroll phone from
accounting where department is null;
The following would be incorrect-----Select employee payroll phone from
accounting where department =null;
.
IS 380, A. M. Thomason
37