SQL-Lecture 4 Pages Lectures Lecture12

1. DBS201:SQL - Joins - Views - Calculated Values - Copying Data to Another Table SQL-Lecture 4 Pages Lectures Lecture12

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

3. How to Join Tables • To create a join, specify the tables that you want to be included • For each field you want, specify it’s 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

4. Joins – General Usage Notes When you join two or more tables, consider the following items: If there are common column names, you must qualify each common name with the name of the table (or alias). Column names that are unique do not need to be qualified. If you do not list the column names you want, but use SELECT *, SQL returns rows that consist of all the columns of the first table, followed by all the columns of the second table, and so on. The more joins the slower the processing You must be authorized to select rows from each table or view specified in the FROM clause.

5. Joins – Types of Several different types of joins are supported by iSeries inner join, left outer join, right outer join, left exception join, right exception join, and cross join We will only cover a very simple join method

6. How to Join Tables • CUSTOMER and SALESREP are related by a FOREIGN KEY • Sales_Rep_Numberis the field common to both tables. • TheSales_Rep_Numberon CUSTOMER points to the Sales_Rep_Numberon SALESREP Required: a listing with the following fields Customer’s First and Last Name and the corresponding Salesrep’s First and Last Name.

7. Select with Join and Aliases Last Name and First Name are found in both tables They need to be qualified PREMIERE.CUSTOMER become C PREMIERE. SALESREP becomes S If you joined also on a SUPPLIER table then their cannot be two alias named the same S Alias names can be CUST or SR

8. JOIN example 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; Notice the condition for joining. Compare rows in the first table with rows in the second table and if the common field matches, join both rows and display only the columns in the SELECT

9. Join Results Using the tables in the PREMIERE collection The result is.

10. Joining Tables – An example • Two tables: Product and Supplier • A SELECT that shows product name and supplier name • Command is: SELECT p.PRODUCTNAME, s.SUPPLIERNAME FROM DELL.PRODUCT p, DELL.SUPPLIER s WHERE p.SID = s.SUPPID Notice that in the PRODUCT table the field is called SID In the SUPPLIER table the field is called SUPPID

11. How to Join Tables • Two tables: Product and Supplier

12. How to Create a View • A view is a virtualtable • Does NOT store data • Can be used to save a Select statement for repeated execution • - don’t have to re-enter the SELECT statement every time • The user “sees” the VIEW as a table.

13. Views - more Views provide a way to divide a table or multiple tables so that you deal with only the data that you need. A view reduces complexity and, at the same time, restricts access. You can create a view using the SQL CREATE VIEW statement.

14. How to Create a View • To save a Select statement in a View Use “Create View” CREATE VIEW viewname AS (SELECT statement)

15. Using a View • To run the View (it’s 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.

16. 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

17. 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

18. How to use Calculated Values SELECT Assign_chg_hr, Assign_hours, Assign_chg_hr*Assign_hours FROM seneca.dbs201.assignment  result is a generated title SELECT Assign_chg_hr, Assign_hours, Assign_chg_hr*Assign_hoursas total_charge FROM from seneca.dbs201.assignment much more friendly report

19. 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

20. 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

21. How to copy data into a table 1 Create the table CREATE TABLE newtablename LIKE Existingtablename CREATE TABLE PREMA01.CUSTOMER LIKE PREMIERE.CUSTOMER 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

22. REVIEW • COLLECTION • CREATE COLLECTION NAMEIT • DROP COLLECTION NAMEIT

23. REVIEW TABLE CREATE TABLE PRODUCT ( PID INTEGER PRIMARY KEY, PNAME VARCHAR(40), CATEGORYID INTEGER, )

24. REVIEW TABLE – CHANGES ALTER PRODUCT ADD COLUMN WEIGHT DECIMAL(3,1) ALTER PRODUCT ADD CONSTRAINT PRODUCT_CATID_FK FOREIGN KEY (CATEGORY_ID) REFERENCES CATEGORYTABLE(CAT_ID) CAN ALSO DROP COLUMNS

25. TABLE • DROP TABLE PRODUCT

26. DATA IN THE TABLE INSERT INTO PRODUCT VALUES(100, ‘BBQ CHIPS’,16.5); INSERT INTO PRODUCT VALUES(100, ‘BBQ CHIPS’, NULL); INSERT INTO PRODUCT (PID, PNAME) VALUES(200,’BACON CHIPS’); UPDATE PRODUCT SET WEIGHT = 16.5 WHERE PID = 200; And deleting data

27. SELECTS & COPY SELECT data from tables SELECT data from more than one table via a JOIN COPY TABLE STRUCTURES COPY DATA FROM ONE TABLE TO ANOTHER