An active database in pl-sql, PL-SQL Programming

Assignment Help:

Consider the following set of database tables (same tables from Assignment 6-1). Please take note of foreign keys (most of them carry the same names as the corresponding primary keys they reference): CUS_CODE in INVOICE, INV_NUMBER & P_CODE in LINE, and V_CODE in PRODUCT. The only exception to the naming convention is the EMP_MGR foreign key in EMPLOYEE which references the EMPLOYEE table in a recursive relationship.

The SQL script file myCompany.SQL (same one from Assignment 6-1) creates the tables below and inserts data into them. You WILL need to rerun the script again for this assignment since the contents of some of the files were altered during assignment 6-1. You should also run the script again before final submission of this assignment. Click HERE if you need the script again.

PS: As a reminder, your SQL statements should be generic enough to produce proper results EVEN if the data inside the database changes. For example, if I ask you to display the products provided by vendors located in TN, you can't manually extract the V_CODE for vendors in TN and use the results to search table PRODUCT for the corresponding products; instead, your query should be written using a join between the two tables or something similar.

I. Part 1: Writing More Complex SQL Queries: Save the SQL code for the following queries a single script file called Part1.sql. In addition, include each query along with its output in your report.

1. Create an SQL query that uses set operations to display the union of the last names in table CUSTOMER and the last names in table EMPLOYEE. Do not include duplicates in the output.

2. Create an SQL query that uses set operations to display the union of the last names in table CUSTOMER and the last names in table EMPLOYEE. This time, include duplicates in the output.

3. Create an SQL query that USES AN UNCORRELATED SUBQUERY1 AND NO JOINS2 to display the descriptions for products provided by a vendor in area code 615.

4. Create an SQL query that USES A CORRELATED SUBQUERY AND NO JOINS to display the descriptions for products provided by a vendor in area code 615.

II. Part 2: Creating and Executing a Stored Procedure: Save the SQL code for this stored procedure in a separate script file called Part2.sql. In addition, include the test steps - see below - along with their output in your report. Finally, answer the question below in your report.

Create an SQL Stored Procedure called prc_inv_delete that takes an invoice number as a parameter and deletes the invoice from table INVOICE. TEST STEPS: To test your procedure, do the following:

a) Run the following two SQL commands: SELECT * from INVOICE WHERE INV_NUMBER IN (1001, 1008); SELECT * from LINE WHERE INV_NUMBER IN (1001, 1008);

b) Execute your procedure to delete invoices 1001 and 1008.

c) Run the same SQL commands again: SELECT * from INVOICE WHERE INV_NUMBER IN (1001, 1008); SELECT * from LINE WHERE INV_NUMBER IN (1001, 1008);

d) Undo all changes made by issuing a rollback command.

Answer this question: Did the corresponding lines for invoices 1001 and 1008 in table LINE get deleted automatically? Can you explain why?


Related Discussions:- An active database in pl-sql

Create a procedure to tax calculation task, Complete the following steps to...

Complete the following steps to create a procedure to calculate the tax on an order. The BB_TAX table contains the states that require taxes to be submitted for Internet sales. If

Using inner join, Using INNER JOIN INNER JOIN is used to retrieve the ...

Using INNER JOIN INNER JOIN is used to retrieve the data from all tables listed based on a condition of equality listed after keyword ON. If the condition is not meet, rows ar

Exceptions are raised - Using Raise statement, How Exceptions Are Raised ...

How Exceptions Are Raised By the run-time system, the internal exceptions are raised implicitly as are user-defined exceptions that you have related with an Oracle error number

Ssrs report writing, SSRS Report Writing Project Description: This re...

SSRS Report Writing Project Description: This report is part of a larger project to make a SQL Server Reporting Service (SSRS) based reporting solution. There can be more rep

Special cases of projection, Special cases of projection This section ...

Special cases of projection This section describes the identity projection, r {ALL BUT}, and the projection on no attributes, r { }, which yields TABLE_DUM when r is empty, ot

What is a collection, What Is a Collection The collection is an ordered...

What Is a Collection The collection is an ordered group of elements, all of similar type (for e.g. the grades for a class of students). Each element has a unique subscript whic

Committing and rolling back - autonomous transaction, Committing and Rollin...

Committing and Rolling Back The COMMIT and ROLLBACK end the active autonomous transaction but do not exit the autonomous routine. As the figure shows, if one transaction ends,

Assignments in pl/sql, Assignments in pl/sql The Variables and constants...

Assignments in pl/sql The Variables and constants are initialized every time a block or subprogram is entered. By default, the variables are initialized to NULL. Therefore, unle

Theory of spontaneous generation - origin of life, THEORY OF SPONTANEOUS GE...

THEORY OF SPONTANEOUS GENERATION - ABIOGENESIS OR AUTOGENESIS - According to this theory, the existing living communities have originated from non-living organic matter with

Projection in sql - correct version, Projection in SQL - correct version ...

Projection in SQL - correct version Student StudentId is enrolled on some course. SELECT DISTINCT StudentId FROM IS_ENROLLED_ON In more complicated examples it is someti

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd