DBS201: More on SQL

1
DBS201 More on SQL

• Lecture 2

2
Agenda

• Review
• How to create a table
• How to insert data into a table
• Terms
• Lab 2

3
Review

• Data Anomalies
• What is a database? What is a DBMS?
• What are three categories of SQL statements
• Use three letters to describe each category
• On IBM i platform
• What command is used to initiate an interactive
  SQL session?
• How do you construct a container for Tables and
  Views
• A collection is also called two other names - ?

4
How to Create a Table

• First write a Database Structure chart.
• We can also call this a Data Dictionary of your
  table.

Column Type Length PK FK reference Reqd ? Unique ? Validation


5
How to Create a Table

• Type
• Numeric
• Decimal
• Character
• Varchar
• Date
• yyyy-mm-dd
• Do not specify a length
• Time
• Length
• Specify positions to the left of the decimal
  point and positions to the right of the decimal
  point

6
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 part of the
  PK, otherwise leave blank

7
How to Create a Table

• FK Reference
• Specify the table name and the column name where
  this field is a PK
• Reqd?
• 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

8
How to Create a Table

• Unique?
• Means only that the value can only appear once in
  this column
• Validation
• Specify the range of values or the specific
  values that are allowed for this column

9
Definition of a Table

• Format for defining the table PAINTER

Column Type Len PK FK Reqd Unique Validation
PTR_NUM CHAR 4 Y Y Y
PTR_LNAME CHAR 15 Y
PTR_FNAME CHAR 15 Y
PTR_CITY CHAR 20
PTR_PHONE DEC 10
10
Creating a Database

• CREATE COLLECTION
• Used to create a database
• Syntax
• CREATE COLLECTION database name

11
Deleting a Database

• To remove a database, use the DROP COLLECTION
  statement
• Syntax
• DROP COLLECTION database name

12
Creating Tables in SQL

• CREATE TABLE
• Used to create a table
• Syntax
• CREATE TABLE tablename
• (field1 datatype fieldsize,
• field2 datatype fieldsize, .
• CHECK ,
• PRIMARY KEY (fieldname(s)),
• FOREIGN KEY (fieldname) REFERENCES tablename
  (PKfieldname))

13
Creating Tables in SQL

• Create statement for painter might look like
• CREATE TABLE painter (
• p_num char (4) not null with default
  primary key,
• p_lname char (15) not null with default,
• p_fname char (15) not null with default,
• p_city char (20),
• p_phone dec (10))

14
Creating Tables in SQL

• Use this version on tests and exam
• CREATE TABLE painter (
• p_num char (4) not null with default,
• p_lname char (15) not null with default,
• p_fname char (15) not null with default,
• p_city char (20),
• p_phone dec (10),
• Constraint Painter_p_num_PK
  
• Primary Key (p_num) )

15
Creating Tables in SQL

• Primary Key Constraint initially not done
• Table created without a primary key constraint
• p_phone dec (10) )
• ALTER TABLE PAINTER
• ADD CONSTRAINT PAINTER_p_num_PK
• PRIMARY KEY (p_num)

16
Dropping Tables in SQL

• DROP TABLE
• Used to remove a table
• Syntax
• DROP TABLE tablename

17
Inserting Data into a Table

• INSERT
• Used to insert data into a table
• Syntax
• INSERT INTO tablename (fieldname1, fieldname2,.)
  VALUES (value1, value2)
• or
• INSERT INTO tablename VALUES (value1, value2),
  (value1, value2)

Single row added
Multiple rows added
18
Inserting Data into a Table

• Rules for Inserting
• Include all column names and provide values for
  each column
• Or Ignore column names and provide values in the
  same order as column names
• If table was created allowing NULLs, then
  indicate the word NULL in place of that column
  when specifying the values

19
Primary Keys in SQL

• Primary Key
• Primary Key must always be NOT NULL (and unique)
• What happens if you create the table without
  specifying a primary key, insert rows and then
  apply the primary key constraint?

20
Inserting a Row

• INSERT INTO PAINTERA40/PAINTER VALUES('111',
  'Smith', 'Bill', 'Oakville', 905477333)
• INSERT INTO PAINTERA40/PAINTER VALUES('222',
  'Brown', 'Nancy', 'Mississauga', 9055666633)

21
Inserting Data into a Table

• INSERT INTO PAINTERA40/PAINTER (p_num, p_lname,
  p_fname, p_city, p_phone) VALUES('111','Wong',
  'Ben', 'Newmarket', 9058876644)
• Can we add the primary key constraint ?
• ALTER TABLE PAINTER
• ADD CONSTRAINT Painter_p_Num_PK
• PRIMARY KEY (P_Num)
• Unique index cannot be created because of
  duplicate keys.

22
SQL Terminology

• S C H E M A
  
  
  
• A group of related objects that consists of a
  library, a journal, a journal receiver, an SQL
  catalog, and an optional data dictionary.
  
• A schema enables the user to find the table, view
  and index objects by name. Another name for a
  schema is collection.

23
SQL Terminology

• T A B L E
  
• A set of columns and rows.
• R O W
• The horizontal part of a table containing a
  serial set of columns.
• C O L U M N
• The vertical part of a table of one data type

24
Equivalent SQL Terminology

• L I B R A R Y
  
• A group of related objects that enables the user
  to find the objects by name.
  
  
• A schema is treated the same as a library by
  some native commands.
• P H Y S I C A L F I L E
• A set of records.
• A Table is treated the same as a Physical File
  by native and SQL commands

25
Equivalent SQL Terminology

• R E C O R D
• A set of fields.
• F I E L D
• One or more bytes of related information of one
  data type.
• In SQL this is referred to as a column.

26
PART Table What can you identify?
PARTNUMBER PARTDESC ONHAND CLASS WAREHOUSE PRICE
AX12 Iron 104 HW 3 23.95
AZ52 Dartboard 20 SG 2 12.95
BA74 Basketball 40 SG 1 29.95
BH22 Cornpopper 95 HW 3 24.95
BT04 Gas Grill 11 AP 2 149.99
BZ66 Washer 52 AP 3 399.99
CA14 Griddle 78 HW 3 39.99
CB03 Bike 44 SG 1 299.99
CX11 Blender 112 HW 3 22.95
CZ81 Treadmill 68 SG 2 349.99
27
Table Exercise 1

• Create a table called AGENT using the following
  data dictionary
• Insert the following data into your table

Column Type Len PK FK Reqd Unique Validation
AGT_NUM CHAR 3 Y Y
AGT_LNAME CHAR 25 Y
AGT_FNAME CHAR 20 Y
AGT_AREACODE CHAR 3
AGT_PHONE CHAR 7
AGT_NUM AGT_LNAME AFT_FNAME AGT_AREACODE AGT_PHONE
5 Andrews Mark 416 123-3456
10 Chen Terry 250 333-4059
15 DaSilva Lila 416 234-5837
20 Edwards George 416 444-5959
25 Eustace Gina 519 948-8494
28
Table Exercise 2

• Create a table using the following data
  dictionary
• Insert the following data into your table

Column Type Len PK FK Reqd Unique Validation
CUST_NUM DEC 3 Y Y Y 1-100
CUST_LNAME CHAR 25 Y
CUST_FNAME CHAR 20 Y
CUST_AREACODE CHAR 3
AGENT_NUM CHAR 3 AGENT (AGENT_NUM)
CUST_NUM CUST_LNAME CUST_FNAME CUST_AREACODE CUST_PHONE
5 Andrews Mark 416 123-3456
10 Chen Terry 250 333-4059
15 DaSilva Lila 416 234-5837
20 Edwards George 416 444-5959
25 Eustace Gina 519 948-8494
29
Agenda

• Lab 2