DBS201 Lab 6 Purpose - In this lab you will learn how : - to create a report which contains data from more than 1 table (called a join) to create a virtual table called a VIEW. It is virtual in the sense that it is not an actual table that stores data, but rather it is a way of presenting data from other tables to you in table format. You can also think of it as a way of "saving" a complicated select statement so you don't have to re-enter it every time you want that report. *** Reminder: In general each lab builds on the work done in the previous lab. It is important that you complete each lab whether you do it in time for marks or not! 1. In previous labs, you have retrieved data from the Customer table and from the Salesrep table using the Select command. You produced a Customer report containing data from the Customer table and you produced a Sales Rep report containing data from the Salesrep table. What if you wanted to produce one report that contained data from both of those tables? Say for example we wanted to see a query which listed the Customer Number, the Customer First and Last Names as well as the First and Last Name of their respective Sales representative. In SQL, any common column used in two tables can be used to provide a temporary result table called a join. In the PREMIERE collection, the salesperson’s full name is in the SALESREP table and the customer’s full name is in the Customer table. The salesperson number is in both tables. The column Sales_Rep_Number is the primary key in the SALESREP table and is a foreign key in the Customer table. The Sales_Rep_Number is a common column in both tables, therefore we can join these two tables by matching the Sales Rep Numbers. A join on the Sales_Rep_Number column in the PREMIERE.Customer table and the PREMIERE.Salesrep tables produces the following output in SQL: 2. Let's create this report. Signon (make sure that your output is directed to the screen…use F13) and enter the SELECT command and press F4 to prompt. To select data from more than one table, simply list the tables separated by a comma. Therefore, DBS201 Lab 6 on the FROM tables line, prompt and select the PREMIERE collection. Then choose the CUSTOMER and SALESREP tables by placing a 1 beside the CUSTOMER table and a 2 beside the SALESREP table. Press Enter. 3. To choose which fields you wish to select for your report, tab to the SELECT columns line and prompt. Select the following fields: Customer_Number, Customer First_name, Customer Last_Name, Salesrep First_Name, Salesrep Last_Name. (Note: to have the fields appear in a specific order, enter numbers in ascending order beside the fields you want, in the sequence you wish to see them.) 4. Now we must tell it which columns we want to match or join on. With your cursor on the WHERE conditions line, prompt and select the Sales_Rep_Number in the Customer table. Then enter an equal sign (=) and prompt again. This time choose the Sales_Rep_Number in the Salesrep table. Press Enter to execute the select statement. Your report should look something like this: 5. What is the problem with this report? Which is the Customer Name and which is the Sales Rep Name? We can fix this and make our report more user-friendly. The DBS201 Lab 6 concatenation operator joins two character strings together and is represented by a || (“||” is the pipe key or the “|” key pressed twice). For example, ‘ABC’ || ‘/’ || ‘def’ will produce a result of ‘ABC/def’. Retrieve the previous command (F9) and prompt (F4). Replace the comma that is after PREMIERE.CUSTOMER.FIRST_NAME with the concatenation character (||). Do the same after PREMIERE.SALESREP.FIRST_NAME. Press enter to execute the command. 6. Now what is wrong with the report? "String expression" is not a very meaningful column heading. We need to give our concatenated fields a new column name. Retrieve and prompt the previous command. After the PREMIERE.CUSTOMER.LAST_NAME column and BEFORE the comma, insert " as CUST_NAME". (Do NOT insert the quotation marks!) Insert "as REP_NAME" after PREMIERE.SALESREP.LAST_NAME. Execute the command. The report should look just right now. In other words you can easily identify which is the customer name and which is the salesrep name. PART B: Creating a View 1. In the previous section, you went to a lot of trouble to generate a report using multiple files, concatenation, etc. What if you or others had to run that execute that statement many, many times? It is possible to save that select statement as a View. The general syntax to create a view is: CREATE VIEW viewname AS SELECT …..(insert your select statement here). 2. Find and retrieve the SELECT that you used to create the previous report. With your cursor at the beginning of the command, press F15 (Split line). This should insert a new line at the beginning of the command. Now we can enter our CREATE VIEW AS command before the SELECT statement and saves having to re-enter the whole thing! 3. On the new line enter the command CREATE VIEW PREMxxxAB.CUST_REP AS (where PREMxxxAA is the name of your collection created in Lab 7). Your command should now look something like this: ===> CREATE VIEW PREMxxxAB.CUST_REP AS SELECT PREMIERE.CUSTOMER.CUSTOMER_NUMBER, PREMIERE.CUSTOMER.FIRST_NAME || PREMIERE.CUSTOMER.LAST_NAME as CUST_NAME, PREMIERE.SALESREP.FIRST_NAME || PREMIERE.SALESREP.LAST_NAME as REP_NAME FROM PREMIERE.CUSTOMER, PREMIERE.SALESREP WHERE PREMIERE.CUSTOMER.SALES_REP_NUMBER = PREMIERE.SALESREP.SALES_REP_NUMBER 4. Press enter to execute the command. You should receive the following message: View CUST_REP created in PREMxxxAB. 5. Once a view is created, it can be used in much the same way as a TABLE. You can use SELECT statements to see the data. Run the select statement to look at all the data in the view. (SELECT * FROM PREMxxxAB.CUST_REP.) Your report should look the same as shown in step 1. DBS201 Lab 6 To Hand In: Create a view in your PREMxxxAB collection called ORDER_INFO that shows the order information for each customer in the Premiere collection. The report should contain the Customer Number and the Order Number from the Order table and the Part Number, Number Ordered and Quoted Price from the Orderline table. Please hand in a printed report of the data in the view sorted by Customer Number (use Order By). Hand in the printed report. REMEMBER: when you select columns from more than 1 table, you must tell it which column to match on using the "WHERE" clause. The report should look like this:
© Copyright 2024