- Review - Joins - Views - Calculated Values - Copying Data to Another Table

1
- Review - Joins
- Views
- Calculated Values
- Copying Data to Another Table

• Lecture 4

2
Agenda

• 1. Review
• 2. Join - Selecting data from more than one
  table
• 3. View An empty table whose structure is a
• sub-set of the database
• 4. Calculated Values
• Copying data from one table to another
• Lab 4

3
Review

• What are five types of constraints
• Primary key
• Not Null
• Unique
• Check
• Foreign Key

4
Review

• Provide SQL to construct two tables Product
  first and then Supplier. Support Referential
  Integrity

Supp_Id SupplierName
1 Nabisco
2 President's Choice
3 Noname
4 Kraft
5 General Mills
6 Christie
ProductID ProductName SID

5 Pesto 2
6 Ginger Snap Cookies 2
7 Triscuits Crackers 2
8 Penne Pasta 4
9 Horse radish 4
10 Bread Butter Pickles 6
5
Review

• CREATE TABLE PRODUCT (                            
      
•            Product_id   Char (2)  not null with
  default,
•            Product_Name Char (25) not null with
  default,
•            SID          Char (2)  not null with
  default,
• Constraint Product_Product_Id_PK   
• Primary Key (Product_Id) )
• CREATE TABLE SUPPLIER (                           
        
•      Supp_id       Char (2)  not null with
  default,
•     Supplier_Name Char (25) not null with
  default,
•  Constraint Supplier_Supp_Id_PK                   
         
•  Primary Key (Supp_Id) )                          
                        

6
Review

• ALTER TABLE PRODUCT            
•  ADD CONSTRAINT PRODUCT_SID_FK  
•  FOREIGN KEY (SID)               
•  REFERENCES  SUPPLIER(SUPP_ID)  
• Put in the first row of data
• INSERT INTO SUPPLIER Values(2, Presidents
  Choice)
• INSERT INTO PRODUCT Values( '5', 'Pesto', '2') 
•                                    

7
Review

• Include a Product_Price field in the appropriate
  table (99.99)
• ALTER TABLE PRODUCT
• ADD COLUMN Product_Price numeric (4,2) not null
  with default
• ALTER completed for table PRODUCT in DBS201SQL.
• Prices must be between 3.00 and 40.00
• ALTER TABLE PRODUCT
• ADD CONSTRAINT PRODUCT_PRODUCT_PRICE_CK
• CHECK (PRODUCT_PRICE BETWEEN 3.00 AND 40.00)
• Remove this new column
• ALTER TABLE PRODUCT
• DROP COLUMN PRODUCT_PRICE

8
How to Join Tables

• To create a join, specify the tables that you
  want to be included
• For each field you want, specify its full name
• table-name.column-name. We can use aliases for
  readability.
• Must use the WHERE clause of the SELECT
  statement. The WHERE clause controls what is
  being joined

9
How to Join Tables

• CUSTOMER and SALESREP are related by a FOREIGN
  KEY
• Sales_Rep_Number is the field common to both
  tables.
• The Sales_Rep_Number on CUSTOMER points to the
  Sales_Rep_Number on SALESREP
• Lets list the Customers First and Last Name and
  the corresponding Salesreps First and Last Name.

10
Select with Join and Aliases

• We will use an alias for each table to reduce
  typing and increase readability c
  PREMIERE.CUSTOMER
• S PREMIERE.SALESREP
• SELECT c.Last_Name, c.First_Name,
• s.Last_Name, s.First_Name
• FROM PREMIERE.CUSTOMER c, PREMIERE.SALESREP
  s
• WHERE c. Sales_Rep_Number s. Sales_Rep_Number

11
How to Join Tables

• Two tables Product and Supplier

Supp_Id SupplierName
1 Nabisco
2 President's Choice
3 Noname
4 Kraft
5 General Mills
6 Christie
ProductID ProductName SID

5 Pesto 2
6 Ginger Snap Cookies 2
7 Triscuits Crackers 2
8 Penne Pasta 4
9 Horse radish 4
10 Bread Butter Pickles 6
12
Joining Tables An example

• Two tables Product and Supplier
• A SELECT that shows product name and supplier
  name
• Command is
• SELECT p.Product_ID, p.Product_Name,
• p.SID, s.Supplier_Name
• FROM DBS201SQL.PRODUCT p,
  DBS201SQL.SUPPLIER s
• WHERE p.SID s.Supp_ID

13
Joining Tables

• PRODUCT_ID PRODUCT_NAME SID
  SUPPLIER_NAME
• 5 Pesto 2
  President's Choice
• 6 Ginger Snap Cookies 2
  President's Choice
• 7 Triscuits Crackers 2
  President's Choice
• 8 Penne Pasta 4
  Kraft
• 9 Horse Radish 4
  Kraft
• 10 Bread Butter Pickles 6
  Christie .

14
Joining Tables

• PRODUCT_ID PRODUCT_NAME SID
  SUPPLIER_NAME
• 10 Bread Butter Pickles 6
  Christie
• 6 Ginger Snap Cookies 2
  President's Choice
• 9 Horse Radish 4
  Kraft
• 8 Penne Pasta 4
  Kraft
• 5 Pesto
  2 President's Choice
• 7 Triscuits Crackers 2
  President's Choice

15
Joining Tables An example

• Product_Name sorting
• Command is
• SELECT p.Product_ID, p.Product_Name,
• p.SID, s.Supplier_Name
• FROM DBS201SQL.PRODUCT p,
  DBS201SQL.SUPPLIER s
• WHERE p.SID s.Supp_ID
• ORDER BY p.Product_Name

16
How to Create a View

• A view is a virtual table
• Does NOT store data
• Can be used to save a Select statement for
  repeated execution (dont have to re-enter the
  SELECT statement every time).
• The user sees the VIEW as a table.

17
How to Create a View

• To save a Select statement in a View
• ?Use Create View
• CREATE VIEW viewname
• AS (SELECT statement)

18
How to Create a View

• Command is
• CREATE VIEW Prod_Supplier as (
• SELECT p.Product_ID, p.Product_Name,
• p.SID, s.Supplier_Name
• FROM DBS201SQL.PRODUCT p,
  DBS201SQL.SUPPLIER s
• WHERE p.SID s.Supp_ID )
• Can not include an ORDER BY clause in a view
• ORDER BY p.Product_Name

19
Using a View

• To run the View (its really a Select statement),
  type
• Select from viewname
• Note ORDER BY can not be used in original view
  statement. We can only use ORDER BY when
  executing the view.

20
How to Create a View

• Views can not be changed
• To change a View, delete it, then re-create it
• Syntax to delete a View
• DROP VIEW viewname

21
How to use Calculated Values

• A calculated field can be added to a Select
  statement at any time
• A calculated field is a simple math or string
  expression

22
How to use Calculated Values

• We do not need to store the last column.
• Create this table

23
ASSIGNMENT TABLE

• CREATE TABLE ASSIGNMENT (
  
• ASSIGN_NUM NUMERIC (4,0),
  
• ASSIGN_DATE DATE NOT NULL WITH
  DEFAULT,
• PROJ_NUM CHAR (2) NOT NULL,
  
• EMP_NUM CHAR (3) NOT NULL,
  
• ASSIGN_JOB CHAR (3) NOT NULL,
  
• ASSIGN_CHG_HR NUMERIC (5,2) NOT NULL WITH
  DEFAULT,
• ASSIGN_HOURS NUMERIC (3,1) NOT NULL WITH
  DEFAULT,
• CONSTRAINT ASSIGNMENT_ASSIGN_NUM_PK
  
• PRIMARY KEY (ASSIGN_NUM) )

24
How to use Calculated Values

• SELECT Assign_chg_hr, Assign_hours,
  Assign_chg_hrAssign_hours
• FROM seneca.dbs201.assignment ?
  result is a generated title
• SELECT Assign_chg_hr, Assign_hours,
  Assign_chg_hrAssign_hours as
• total_charge
• FROM from seneca.dbs201.assignment
  ? much more friendly report
  

25
How to copy data into a table

• From an existing table, it is possible to copy
  data into another version of the table
• This is useful when making a backup of a table
  before making changes
• Caution constraints are not carried from
  original table to new table they would need to
  be added individually to the new table via Alter
  Table statement

26
How to copy data into a table

• Three step process
• 1. Create the table with the same definition as
  an existing table
• 2. Alter the table definition to include
  appropriate constraints
• 3. Copy the data into the new table

27
How to copy data into a table

• 1. CREATE TABLE newtablename LIKE .
  Existingtablename
• 2. Add each constraint separately
• ALTER TABLE newtablename
• ADD CONSTRAINT . blah, blah,
  blah
• 3. To copy the data the syntax is
• INSERT INTO newtablename
• SELECT
• FROM existingtablename

28
How to copy data into a table

• 1. create table assign like assignment
• 2. ALTER TABLE assign
• ADD CONSTRAINT . blah, blah,
  blah
• 3. insert into assign (select from
  assignment)

29
Alternate Copy Technique

• 1. CREATE TABLE ASSIGN1 as
• (select from assignment) with data
• 2. ALTER TABLE assign
• ADD CONSTRAINT . blah, blah, blah

30
Lab

• LAB 4