Csc Data Bases Essay

380 Words2 Pages
CSC 453 Exam II 10 points each All results must be SQL generated. Use formatting/landscape for output readability. For submission, include both the SQL statement and the output rows in a Word document (include the problem number with each statement) and submit it to D2L. Simply highlight the output and copy & paste it. Only submit one Word document. The SQL is required. No credit will be given for the output alone. 1. Use UNION to list the vendors supplying a line item containing 'CICS' or 'MVS'. Include the vendor ID and name. 2. Use INTERSECT to list the vendors supplying a line item containing 'CICS' and a line item containing 'MVS'. Include the vendor ID and name. 3. Using a subquery or an outer join, give a count of the number of vendors that have no invoices at all. 4. Recreate Problem 3 using MINUS. 5. Create a function Percentage accepting two numbers, and returning the ratio of the first number to the second one as a percentage. Passing 20 and 80 would return 25 (not .25). Return zero if the denominator is zero. 6. Use CREATE TABLE ... AS ... and NATURAL JOIN to create a table named InvoiceVendors containing all of the attributes from both invoices and vendors (other than eliminating the duplicate vendor id). Use InvoiceVendors for the remaining exam problems. 7. Use ALTER to add a column to InvoiceVendors containing the total invoice amount for all invoices in the InvoiceVendors table for that vendor. Use UPDATE to populate the column. 8. Create a PL/SQL block listing all invoices that represent more than 50% of the total invoice amounts for that vendor. Format and align the output. Invoke the function to obtain the percentage. Order the output by the vendor name. Include the vendor name, total amount and due date (full month name, no extra spaces). The output must be generated from within the

More about Csc Data Bases Essay

Open Document