1. Consider the following tables in a relational database. Provide the appropriate SELECT SQL statement necessary...
Fantastic news! We've Found the answer you've been seeking!
Transcribed Image Text:
1. Consider the following tables in a relational database. Provide the appropriate "SELECT" SQL statement necessary to answer the queries that follow. Primary keys are underlined and foreign key fields have an asterisk at the end of the field. CUSTOMERS (CUST-NO, C-NAME, C-ADDRESS, BALANCE) SALESPERSONS (SP-NO, S-NAME, DATE-EMPLOYED, SALARY) SALES (INVOICE-NO, DATE, CUST-NO*, SP-NO*) a) List the salesperson name and salary for all sales to customers whose balance outstanding is greater than 20000. b) List the names and addresses of all customers who have been sold merchandise by salespersons employed before 1/196. 2. Find all violations of entity and referential integrities. CUSTOMERS CUSTOMER NAME ADDRESSI ADDRESS2 STATE STR BALANCE 456 ABC Corp. 111 Any St. Houston TX 6.25 34560.65 457 DEF Corp 222 Anywhere Dr. New York NY 6.50 2145.90 458 GHI Corp s Someplace Ct. Miani 6.45 45670.75 459 UKL Corp 56 Some Dr. Bryan TX 6.25 21009.50 460 MNO Corp Noplace Cir San Diego CA s s0 4561.00 INVOICE-ITEMS INVOICE# TEM# DESC PRICE QTY 1001 121 Widget 2.25 45 1001 $40 Bolt 0.40 25 1002 211 Gear 3.70 10 1003 121 Widget 2.25 15 INVOICE-ITEMS INVOICE# ITEM# DESC PRICE QTY 1001 121 Widget 2.25 45 1001 540 Bolt 0.40 25 1002 211 Gear 3.70 10 1003 121 Widget 2.25 15 1003 121 Widget 2.25 10 1006 348 Nut 0.25 INVOICES INVOICE# DATE CUSTOMER# AMOUNT 1001 11-1-95 456 450.75 1002 11-2-95 457 560.25 11-2-95 160 300 10 INVOICES INVOICE# DATE CUSTOMER# AMOUNT 1001 11-1-95 456 450.75 1002 11-2-95 457 560.25 11-2-95 460 300.10 1003 11-2-95 459 890.25 1004 11-3-95 450 425.50 1. Consider the following tables in a relational database. Provide the appropriate "SELECT" SQL statement necessary to answer the queries that follow. Primary keys are underlined and foreign key fields have an asterisk at the end of the field. CUSTOMERS (CUST-NO, C-NAME, C-ADDRESS, BALANCE) SALESPERSONS (SP-NO, S-NAME, DATE-EMPLOYED, SALARY) SALES (INVOICE-NO, DATE, CUST-NO*, SP-NO*) a) List the salesperson name and salary for all sales to customers whose balance outstanding is greater than 20000. b) List the names and addresses of all customers who have been sold merchandise by salespersons employed before 1/196. 2. Find all violations of entity and referential integrities. CUSTOMERS CUSTOMER NAME ADDRESSI ADDRESS2 STATE STR BALANCE 456 ABC Corp. 111 Any St. Houston TX 6.25 34560.65 457 DEF Corp 222 Anywhere Dr. New York NY 6.50 2145.90 458 GHI Corp s Someplace Ct. Miani 6.45 45670.75 459 UKL Corp 56 Some Dr. Bryan TX 6.25 21009.50 460 MNO Corp Noplace Cir San Diego CA s s0 4561.00 INVOICE-ITEMS INVOICE# TEM# DESC PRICE QTY 1001 121 Widget 2.25 45 1001 $40 Bolt 0.40 25 1002 211 Gear 3.70 10 1003 121 Widget 2.25 15 INVOICE-ITEMS INVOICE# ITEM# DESC PRICE QTY 1001 121 Widget 2.25 45 1001 540 Bolt 0.40 25 1002 211 Gear 3.70 10 1003 121 Widget 2.25 15 1003 121 Widget 2.25 10 1006 348 Nut 0.25 INVOICES INVOICE# DATE CUSTOMER# AMOUNT 1001 11-1-95 456 450.75 1002 11-2-95 457 560.25 11-2-95 160 300 10 INVOICES INVOICE# DATE CUSTOMER# AMOUNT 1001 11-1-95 456 450.75 1002 11-2-95 457 560.25 11-2-95 460 300.10 1003 11-2-95 459 890.25 1004 11-3-95 450 425.50
Expert Answer:
Related Book For
Posted Date:
Students also viewed these programming questions
-
In a relational database environment, certain accounting records (for example, journals, subsidiary, ledgers, and event general ledger accounts) may not exist. How is this possible?
-
Consider the following tables form part of a Library database held in an RDBMS: Book (ISBN, title, edition, year) BookCopy (copyNo, ISBN, available) Borrower (borrowerNo, borrowerName,...
-
Consider the following information: Probability of State Rate of Return if State Occurs Economy of Economy Stock A Stock B Recession .24 .030 .39 Normal .59 .110 .29 Boom .17 .280 .52 a. Calculate...
-
Determine whether each relation defines y as a function of x. Give the domain. Identify any linear functions. y = |x|
-
The numbers of credits being taken by a sample of 13 full-time college students for a semester Find the mean, the median, and the mode of the data, if possible. If any measure cannot be found or does...
-
What is the difference between an argument and a parameter variable?
-
On the 2017 consolidated income statement, the noncontrolling interest in net income of Starfruit is On January 1, 2015, Pomegranate Company acquired 90% of the voting stock of Starfruit Company for...
-
The master budget at Windsor, Inc., last period called for sales of 90,000 units at $36 each. The costs were estimated to be $15 variable per unit and $900,000 fixed. During the period, actual...
-
The other answers : B CapEx equals D&A in each projected year, even though the company's revenue is growing at 8-10% annually in most of the projected period. C EBIT and NOPAT are not properly adj...
-
The complexity and uniqueness of the federal government makes it difficult to conduct a meaningful analysis of its financial condition. However, conducting a financial statement analysis does provide...
-
When the market value of the dollar rises relative to other currencies around the world, we say that the supply of dollars has increased. the demand for dollars has increased. the dollar has...
-
What are the other terms used for accessibility? Explain them.
-
Are there any constraints, which need to be handled by the reputation pattern?
-
What are the challenges that exist, when trying to utilize the reputation pattern?
-
What are some of the identified nonfunctional requirements for this pattern?
-
How sustainable is the reputation pattern?
-
Alicia Trading has the following non-current assets at 31 December 2021. On 1 June 2021, the equipment which was purchased in the year 2020 was traded-in with a new equipment costs RM180,000....
-
Find the radius of convergence in two ways: (a) Directly by the CauchyHadamard formula in Sec. 15.2. (b) From a series of simpler terms by using Theorem 3 or Theorem 4.
-
What documentation techniques are used in the structured design approach to conceptual design? What is the purpose of each of these documentation techniques, and how do they vary from each other? How...
-
What is the relationship among data, information, and an information system?
-
Explain how linkages between relational tables are accomplished.
-
Taking the closing balance sheet from the example above prepare a balance sheet and profit and loss account for the third year of trading incorporating the following transactions: 1. The owner...
-
Sid's business during the next month is even better than in the first. Sid purchased a further 200 of diesel and had just filled his tank with 35 worth before finishing on the last day of the month....
-
Prepare a cash flow statement for the above business as at the end of year 2 to create the comparative figures for the above statement
Study smarter with the SolutionInn App