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.

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 • 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

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:

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_phonedec (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?

27. Table Exercise 1: • Create a table called AGENT using the following data dictionary: • Insert the following data into your table:

28. Table Exercise 2: • Create a table using the following data dictionary: • Insert the following data into your table:

29. Agenda • Lab 2