5. SELECT c.CUS_LNAME, c.CUS_FNAME, i.INV_NUMBER, l.P_CODE FROM CUSTOMER c, INVOICE i, LINE l WHERE c.CUS_CODE = i.CUS_CODE AND i.INV_NUMBER, l.INV_NUMBER AND i.P_CODE = '23109-HB';
6. SELECT v.V_CODE, v.V_NAME, p.P_CODE, p.P_DESCRIPT, (p.QOH-p.MIN) AS 'Quantity over Minimum' FROM PRODUCT p, VENDOR v WHERE p.V_CODE = v.V_CODE AND (p.QOH-p.MIN) < 10 ORDER BY (p.QOH-p.MIN);
7. SELECT v.V_CODE, v.V_NAME, v.V_STATE, i.INV_DATE, sum(l.LINE_UNITS) AS TotalItemsSold, sum(l.LINE_UNITS*l.LINE_PRICE) AS TotalSalesAmount FROM PRODUCT p, INVOICE i, LINE l WHERE p.P_CODE = l.P_CODE AND l.INV_NUMBER = i.INV_NUMBER AND i.INV_DATE = '01-16-2010 GROUP BY l.P_CODE ORDER BY TotalSalesAmount;
8. SELECT p.P_CODE, p.P_DESCRIPT, sum(l.LINE_UNITS) AS TotalItemsSold, sum(l.LINE_UNITS*l.LINE_PRICE) AS TotalSalesAmount FROM PRODUCT p, VENDOR v, INVOICE i, LINE l WHERE p.P_CODE = l.P_CODE AND l.INV_NUMBER = i.INV_NUMBER AND p.V_CODE = v.V_CODE AND v.V_STATE = 'TN' GROUP BY p.V_CODE ORDER BY TotalSalesAmount;
NOTE: As per Chegg policy, I am allowed to answer only 4 questions (including sub-parts) on a single post. Kindly post the remaining questions separately and I will try to answer them. Sorry for the inconvenience caused.
CUSTOMER EMP INVOICE LINE P CODE V CODE CUS CODE EMP CODE NV NUMBER NV NUMBER,...
Using the ch08_saleco database write a T-SQL query to display the customer code, invoice number invoice date, and invoice subtotal from invoice conditioned on the invoice subtotal is greater than $100 and from only customer codes 10011 and 10012. (hint: in) /* Database Systems, 8th Ed., Rob/Coronel */ /* Type of SQL : SQL Server */ CREATE TABLE CUSTOMER ( CUS_CODE int, CUS_LNAME varchar(15), CUS_FNAME varchar(15), CUS_INITIAL varchar(1), CUS_AREACODE varchar(3), CUS_PHONE varchar(8), CUS_BALANCE float(8) ); INSERT INTO CUSTOMER VALUES('10010','Ramas','Alfred','A','615','844-2573','0'); INSERT...
Create SQL statements based on the given tables: Code to create the tables: CREATE DATABASE LAB4DB; USE LAB4DB; CREATE TABLE CUSTOMER ( CUS_CODE int primary key, CUS_LNAME varchar(15), CUS_FNAME varchar(15), CUS_INITIAL varchar(1), CUS_AREACODE varchar(3), CUS_PHONE varchar(8), CUS_BALANCE float(8) ); INSERT INTO CUSTOMER VALUES('10010','Ramas','Alfred','A','615','844-2573','0'); INSERT INTO CUSTOMER VALUES('10011','Dunne','Leona','K','713','894-1238','0'); INSERT INTO CUSTOMER VALUES('10012','Smith','Kathy','W','615','894-2285','345.86'); INSERT INTO CUSTOMER VALUES('10013','Olowski','Paul','F','615','894-2180','536.75'); INSERT INTO CUSTOMER VALUES('10014','Orlando','Myron','','615','222-1672','0'); INSERT INTO CUSTOMER VALUES('10015','O''Brian','Amy','B','713','442-3381','0'); INSERT INTO CUSTOMER VALUES('10016','Brown','James','G','615','297-1228','221.19'); INSERT INTO CUSTOMER VALUES('10017','Williams','George','','615','290-2556','768.93'); INSERT INTO CUSTOMER VALUES('10018','Farriss','Anne','G','713','382-7185','216.55'); INSERT INTO CUSTOMER VALUES('10019','Smith','Olette','K','615','297-3809','0'); /*...
All the required files are attached to this question here in the form of images. Please refer the attached images and answer the questions. List the names and codes of vendors and the number of products each vendor has supplied, i.e. vendor AA has supplied xxx products, and vendor BB has supplied yyy products etc. Show the total value of invoices for each customer with the customer the Last Name, and a column heading showing ‘Total_invoice_value’ in the results. List...
Question 1. For each table, identify the primary key and the foreign key(s). If a table does not have a foreign key, write None. Question 2. Do the tables exhibit entity integrity? Answer yes or no, and then explain your answer. Question 3. Do the tables exhibit referential integrity? Answer yes or no, and then explain your answer. Write NA (Not Applicable) if the table does not have a foreign key. Question 4. Describe the type(s) of relationship(s) between CUSTOMER...
Create a stored procedure named prc_inv_amounts to update the INV_SUBTOTAL, INV_TAX, and INV_TOTAL. The procedure takes the invoice number as a parameter. The INV_SUBTOTAL is the sum of the LINE_TOTAL amounts for the invoice, the INV_TAX is the product of the INV_SUBTOTAL and the tax rate (8 percent), and the INV_TOTAL is the sum of the INV_SUBTOTAL and the INV_TAX /* Database Systems, Coronel/Morris */ /* Type of SQL : SQL Server */ /* WARNING: */ /* WE HIGHLY RECOMEND...
* myCompany.SQL Introduction to SQL Script file for ORACLE DBMS This script file creates the following tables: VENDOR, PRODUCT, CUSTOMER, INVOICE, LINE EMPLOYEE and loads the default data rows */ set echo on; set serveroutput on; select systimestamp from dual; show user; ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY'; DROP TABLE LINE CASCADE CONSTRAINTS; DROP TABLE INVOICE CASCADE CONSTRAINTS; DROP TABLE CUSTOMER CASCADE CONSTRAINTS; DROP TABLE PRODUCT CASCADE CONSTRAINTS; DROP TABLE VENDOR CASCADE CONSTRAINTS; DROP TABLE EMPLOYEE CASCADE CONSTRAINTS; CREATE TABLE...
Present Value of : SI Revival Paid On l'uint in Time Print Value of Series of qual Paymenis (Army) over Time 1 Kallangany is considerine an interent that is an outy 2016 au: SOL.023 oli ilu w Ch ow Thucy's Colapital Freets Requel Preservat Forma P-1-1/2'IN Couple NPV ulleres meul. E--- i N N N N 12 R 1 6 3 03 05 06 07 08 09 053 05158 OSIN 0.9791 292 089978850 WE 28686 2 0360 0900343 098 0.900...