QuestionQuestion

Transcribed TextTranscribed Text

Use the attached SQL file to generate the SaleCo tables in your database (CUSTOMER, INVOICE, LINE, PRODUCT, VENDOR, EMP, and EMPLOYEE). FIGURE P7.26 THE CHO7_SALECO DATABASE Relational diagram CUSTOMER INVOICE LINE PRODUCT VENDOR 1 1 DO CUS_CODE 8 INV_NUMBER 9 INV_NUMBER 1 8 P_CODE 8 V_CODE DO CUS_LNAME CUS_CODE 9 LINE_NUMBER P_DESCRIPT DO V_NAME CUS_FNAME INV_DATE P_CODE P_INDATE V_CONTACT CUS_INITIAL LINE_UNITS P_QOH V_AREACODE CUS_AREACODE LINE_PRICE P_MIN V_PHONE CUS_PHONE P_PRICE V_STATE CUS_BALANCE P_DISCOUNT V_ORDER 00 V_CODE Week8_A1 Use the code SaleCo_SQLServer to create and populate the SaleCo tables. Write the SQL code to alter the tables and add the Primary Key and the Foreign Key constraints as shown in the attached SaleCo ERD. Submit your code here. Week8 A2 Create the following Simple SQL queries. Save the code in a .xxt file and submit here. 1. Select all product codes, descriptions, and quantity on hand. (1 point) 2. Select the products that have low quantity on hand (less than 10 items). (1 point) 3. Select the last and the first names and the phone numbers of the customers whose area code is 713. (1 point) 4. Select all vendors from Tennessee. (1 point) 5. Select all customers who have a balance greater than 0. (1 point) 6. Count the vendors from each state and display the state name and the calculated field with the number of vendors from each state. (2 points) 7. Write a query to count the number of invoices (1 point) 8. Write a query to count the number of customers with a balance of more than $500. (2 points)

Solution PreviewSolution Preview

These solutions may offer step-by-step problem-solving explanations or good writing examples that include modern styles of formatting and construction of bibliographies out of text citations and references. Students may use these solutions for personal skill-building and practice. Unethical use is strictly forbidden.

-- 1 select all product codes, description and quantity on hand
SELECT p.P_CODE, p.P_DESCRIPT, p.P_QOH FROM PRODUCT p;

-- 2. select the products that has low quantity on hand (less than 10 items)
SELECT * FROM PRODUCT p
where p.P_QOH < 10;

-- 3 select the last and first names and the phone numbers of the customer whose area code is 713
SELECT c.CUS_LNAME, c.CUS_FNAME, c.CUS_PHONE FROM CUSTOMER c
WHERE c.CUS_AREACODE = 713;

-- 4. Select all vendor from Tennessee
select * from VENDOR v
WHERE v.V_STATE = 'TN' ;

-- 5. select all customers who have balance greater than 0
select * from CUSTOMER c
WHERE c.CUS_BALANCE > 0 ;...

By purchasing this solution you'll be able to access the following files:
Solution.zip.

$35.00
for this solution

or FREE if you
register a new account!

PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

Find A Tutor

View available Database Development Tutors

Get College Homework Help.

Are you sure you don't want to upload any files?

Fast tutor response requires as much info as possible.

Decision:
Upload a file
Continue without uploading

SUBMIT YOUR HOMEWORK
We couldn't find that subject.
Please select the best match from the list below.

We'll send you an email right away. If it's not in your inbox, check your spam folder.

  • 1
  • 2
  • 3
Live Chats