Transcript Field1, Field 2

DBS201: More on SQL
Lecture 2
1
Agenda



Select command review
How to create a table
How to insert data into a table
2
SQL Select Command

SELECT statement


Used to list contents of table
Syntax

SELECT Field1, Field 2,…
FROM tablename
WHERE Condition 1 AND/OR Condition 2
ORDER BY Field1, Field 2,…
3
SQL Select Command

Book Table:
BOOK_CODE
TITLE
PUBLISHER_CODE
TYPE
PRICE
PAPERBACK
180
A Deepness in the Sky
TB
SFI
7.99
Y
189
Magic Terror
FA
HOR
7.99
Y
200
The Stranger
VB
FIC
8.00
Y
378
Ve nice
SS
ART
24.50
N
790
Second Wind
PU
MYS
24.50
N
808
The Edge
JP
MYS
6.99
Y
1351
Dreamcatcher
SC
HOR
19.60
N
1382
Treasure Chests
TA
ART
24.46
N
1390
Beloved
PL
FIC
12.95
Y
4
SQL Select Command
1.
Write the select command to display all
records
2.
Write the select command to list all books
that cost $24.50
3.
Write the select command to list all books
that are paperback and sort them by title
5
How to Create a Table

Now ready to convert an ER Model into a
Database Structure
 Create a Data Dictionary of your table
Column
Type
Length
PK
FK reference Req’d
?
Unique
?
Validation
6
How to Create a Table

Type:







Numeric
Decimal
Character
Varchar
Date
Time
Length:

Specify positions to the left of the decimal point
and positions to the right of the decimal point
7
How to Create a Table

Primary Keys



For concatenated Primary Keys, pay close attention
to the order of the attributes within the Primary Key
Indexing starts with the first attribute mentioned,
then proceeds to the next attribute and so on
PK:

Specify ‘Y’ if this column name is a PK, otherwise
leave blank
8
How to Create a Table

FK Reference:


Specify the table name and the column name
where this field is a PK
Req’d?:



PKs, by default are required
If this column must be present, specify ‘Y’
Means that this column can not be left blank or
NULL – enforces data integrity
9
How to Create a Table

Unique?:


Means only that the value in this column can only
appear once
Validation:

Specify the range of values or the specific values
that are allowed for this column
10
Definition of a Table

Format for defining the table PAINTER:
Column
Type
Len
PK
Y
FK
Req’d
Unique
Y
Y
PTR_NUM
CHAR
4
PTR_LNAME
CHAR
15
Y
PTR_FNAME
CHAR
15
Y
PTR_AREACODE
CHAR
3
PTR_PHONE
CHAR
8
Validation
11
Creating a ‘Database’

CREATE COLLECTION


Used to create a ‘database’
Syntax

CREATE COLLECTION ‘database name’
12
Creating a ‘Database’

To remove a ‘database’, use the DROP
COLLECTION statement

Syntax

DROP COLLECTION ‘database name’
13
Creating Tables in SQL

CREATE TABLE


Used to create a table
Syntax

CREATE TABLE tablename
column 1 data type,
column 2 data type, ….
CONSTRAINT …
14
Definition of a Table

Format for defining the table PAINTER:
Column
Type
Len
PK
Y
FK
Req’d
Unique
Y
Y
PTR_NUM
CHAR
4
PTR_LNAME
CHAR
15
Y
PTR_FNAME
CHAR
15
Y
PTR_AREACODE
CHAR
3
PTR_PHONE
CHAR
8
Validation
15
Creating Tables in SQL
Create statement for painter might look like:
CREATE TABLE painter (
ptr_num
char (4) not null with default ,
ptr_lname
char (15) not null with default,
ptr_fname
char (15) not null with default,
ptr_areacode char (3),
ptr_phone
char (8),
Constraint ptr_num_pk Primary Key(ptr_num)
Constraint ptr_num_un Unique(ptr_num))
16
Creating Tables in SQL

Constraints:


CHECK option when creating a table
Specified as follows:



Use comparison operators (i.e. < > =)
Can use AND as well as OR
Can use BETWEEN (to specify a range of values)




i.e. ….CHECK (fieldname BETWEEN 1 AND 50)
(assumes fieldname is a numeric type)
i.e. ….CHECK (fieldname BETWEEN ‘1’ AND ’50’)
(assumes fieldname is a char type)
17
Dropping Tables in SQL

DROP TABLE


Used to delete a table
Syntax

DROP TABLE tablename
18
DATE Data Type

On the AS/400, date type is expected in the
format:

‘mm/dd/yy’

Do not specify a length
19
Creating Tables in SQL

Rules for creating tables

Parent tables must be defined before child
tables

Conversely, child tables must be deleted
before parent tables

Primary Key must always be NOT NULL
(and unique)
20
Inserting Data into a Table

INSERT


Used to insert data into a table
Syntax

INSERT into tablename (fieldname1,
fieldname2,….) VALUES (value1, value2…)
21
Inserting Data into a Table

Rules for Inserting:

Include all column names and provide values for
each column

If table was created allowing NULLs, then indicate
the word NULL in place of that column when
specifying the values
22
More on Creating Tables

Constraints:

Specified as follows:



Use comparison operators (i.e. < > =)
Can use AND as well as OR
Can use BETWEEN (to specify a range of values)




i.e. ….CHECK (fieldname BETWEEN 1 AND 50)
(assumes fieldname is a numeric type)
i.e. ….CHECK (fieldname BETWEEN ‘1’ AND ’50’)
(assumes fieldname is a char type)
23
More on Selecting Tables

Using WHERE clause:

Specifying conditions to be met:

Can use comparison operators (i.e. < > =)

Can use AND as well as OR

Can use BETWEEN (to specify a range of values)

i.e. ….WHERE fieldname BETWEEN 1 AND 50

(assumes fieldname is a numeric type)

i.e. ….WHERE fieldname BETWEEN ‘1’ AND ’50’

(assumes fieldname is a char type)
24