Using the ch08_saleco database write a T-SQL query to display the customer code, invoice number invoice...
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 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');
/* -- */
CREATE TABLE INVOICE (
INV_NUMBER int,
CUS_CODE int,
INV_DATE datetime,
INV_SUBTOTAL float(8),
INV_TAX float(8),
INV_TOTAL float(8)
);
INSERT INTO INVOICE
VALUES('1001','10014','1/16/2010','24.90','1.99','26.89');
INSERT INTO INVOICE
VALUES('1002','10011','1/16/2010','9.98','0.80','10.78');
INSERT INTO INVOICE
VALUES('1003','10012','1/16/2010','153.85','12.31','166.16');
INSERT INTO INVOICE
VALUES('1004','10011','1/17/2010','34.97','2.80','37.77');
INSERT INTO INVOICE
VALUES('1005','10018','1/17/2010','70.44','5.64','76.08');
INSERT INTO INVOICE
VALUES('1006','10014','1/17/2010','397.83','31.83','429.66');
INSERT INTO INVOICE
VALUES('1007','10015','1/17/2010','34.97','2.80','37.77');
INSERT INTO INVOICE
VALUES('1008','10011','1/17/2010','399.15','31.93','431.08');
/* -- */
CREATE TABLE LINE (
INV_NUMBER int,
LINE_NUMBER int,
P_CODE varchar(10),
LINE_UNITS float(8),
LINE_PRICE float(8),
LINE_TOTAL float(8)
);
INSERT INTO LINE
VALUES('1001','1','13-Q2/P2','1','14.99','14.99');
INSERT INTO LINE
VALUES('1001','2','23109-HB','1','9.95','9.95');
INSERT INTO LINE
VALUES('1002','1','54778-2T','2','4.99','9.98');
INSERT INTO LINE
VALUES('1003','1','2238/QPD','1','38.95','38.95');
INSERT INTO LINE
VALUES('1003','2','1546-QQ2','1','39.95','39.95');
INSERT INTO LINE
VALUES('1003','3','13-Q2/P2','5','14.99','74.95');
INSERT INTO LINE
VALUES('1004','1','54778-2T','3','4.99','14.97');
INSERT INTO LINE
VALUES('1004','2','23109-HB','2','9.95','19.90');
INSERT INTO LINE
VALUES('1005','1','PVC23DRT','12','5.87','70.44');
INSERT INTO LINE
VALUES('1006','1','SM-18277','3','6.99','20.97');
INSERT INTO LINE
VALUES('1006','2','2232/QTY','1','109.92','109.92');
INSERT INTO LINE
VALUES('1006','3','23109-HB','1','9.95','9.95');
INSERT INTO LINE
VALUES('1006','4','89-WRE-Q','1','256.99','256.99');
INSERT INTO LINE
VALUES('1007','1','13-Q2/P2','2','14.99','29.98');
INSERT INTO LINE
VALUES('1007','2','54778-2T','1','4.99','4.99');
INSERT INTO LINE
VALUES('1008','1','PVC23DRT','5','5.87','29.35');
INSERT INTO LINE
VALUES('1008','2','WR3/TT3','3','119.95','359.85');
INSERT INTO LINE
VALUES('1008','3','23109-HB','1','9.95','9.95');
/* -- */
CREATE TABLE PRODUCT (
P_CODE varchar(10),
P_DESCRIPT varchar(35),
P_INDATE datetime,
P_QOH int,
P_MIN int,
P_PRICE float(8),
P_DISCOUNT float(8),
V_CODE int
);
INSERT INTO PRODUCT VALUES('11QER/31','Power painter, 15 psi.,
3-nozzle','11/3/2009','8','5','109.99','0','25595');
INSERT INTO PRODUCT VALUES('13-Q2/P2','7.25-in. pwr. saw
blade','12/13/2009','32','15','14.99','0.05','21344');
INSERT INTO PRODUCT VALUES('14-Q1/L3','9.00-in. pwr. saw
blade','11/13/2009','18','12','17.49','0','21344');
INSERT INTO PRODUCT VALUES('1546-QQ2','Hrd. cloth, 1/4-in.,
2x50','1/15/2010','15','8','39.95','0','23119');
INSERT INTO PRODUCT VALUES('1558-QW1','Hrd. cloth, 1/2-in.,
3x50','1/15/2010','23','5','43.99','0','23119');
INSERT INTO PRODUCT VALUES('2232/QTY','B&D jigsaw, 12-in.
blade','12/30/2009','8','5','109.92','0.05','24288');
INSERT INTO PRODUCT VALUES('2232/QWE','B&D jigsaw, 8-in.
blade','12/24/2009','6','5','99.87','0.05','24288');
INSERT INTO PRODUCT VALUES('2238/QPD','B&D cordless drill,
1/2-in.','1/20/2010','12','5','38.95','0.05','25595');
INSERT INTO PRODUCT VALUES('23109-HB','Claw
hammer','1/20/2010','23','10','9.95','0.1','21225');
INSERT INTO PRODUCT VALUES('23114-AA','Sledge hammer, 12
lb.','1/2/2010','8','5','14.40','0.05','');
INSERT INTO PRODUCT VALUES('54778-2T','Rat-tail file, 1/8-in.
fine','12/15/2009','43','20','4.99','0','21344');
INSERT INTO PRODUCT VALUES('89-WRE-Q','Hicut chain saw, 16
in.','2/7/2010','11','5','256.99','0.05','24288');
INSERT INTO PRODUCT VALUES('PVC23DRT','PVC pipe, 3.5-in.,
8-ft','2/20/2010','188','75','5.87','0','');
INSERT INTO PRODUCT VALUES('SM-18277','1.25-in. metal screw,
25','3/1/2010','172','75','6.99','0','21225');
INSERT INTO PRODUCT VALUES('SW-23116','2.5-in. wd. screw,
50','2/24/2010','237','100','8.45','0','21231');
INSERT INTO PRODUCT VALUES('WR3/TT3','Steel matting, 4''x8''x1/6",
.5" mesh','1/17/2010','18','5','119.95','0.1','25595');
/* -- */
CREATE TABLE VENDOR (
V_CODE int,
V_NAME varchar(15),
V_CONTACT varchar(50),
V_AREACODE varchar(3),
V_PHONE varchar(8),
V_STATE varchar(2),
V_ORDER varchar(1)
);
INSERT INTO VENDOR VALUES('21225','Bryson,
Inc.','Smithson','615','223-3234','TN','Y');
INSERT INTO VENDOR VALUES('21226','SuperLoo,
Inc.','Flushing','904','215-8995','FL','N');
INSERT INTO VENDOR VALUES('21231','D&E
Supply','Singh','615','228-3245','TN','Y');
INSERT INTO VENDOR VALUES('21344','Gomez
Bros.','Ortega','615','889-2546','KY','N');
INSERT INTO VENDOR VALUES('22567','Dome
Supply','Smith','901','678-1419','GA','N');
INSERT INTO VENDOR VALUES('23119','Randsets
Ltd.','Anderson','901','678-3998','GA','Y');
INSERT INTO VENDOR VALUES('24004','Brackman
Bros.','Browning','615','228-1410','TN','N');
INSERT INTO VENDOR VALUES('24288','ORDVA,
Inc.','Hakford','615','898-1234','TN','Y');
INSERT INTO VENDOR VALUES('25443','B&K,
Inc.','Smith','904','227-0093','FL','N');
INSERT INTO VENDOR VALUES('25501','Damal
Supplies','Smythe','615','890-3529','TN','N');
INSERT INTO VENDOR VALUES('25595','Rubicon
Systems','Orton','904','456-0092','FL','Y');
Homework Answers
Request Answer!
We need at least 10 more requests to produce the answer.
0 / 10 have requested this problem solution
The more requests, the faster the answer.
Add Answer to:
Using the ch08_saleco database write a T-SQL query to display
the customer code, invoice number invoice...
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...
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...
Create SQL statements based on the given tables: Code to create the tables: CREATE DATABASE LAB...
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');
/*...
CUSTOMER EMP INVOICE LINE P CODE V CODE CUS CODE EMP CODE NV NUMBER NV NUMBER,...
CUSTOMER EMP INVOICE LINE P CODE V CODE CUS CODE EMP CODE NV NUMBER NV NUMBER, LINE NUMBER Create SQL queries for displaying the following results 5. List the customers who have ordered product bearing product code "23109-HB. Display first names, last names of customers, invoice numbers and product codes 6. Display Vendor details (V_CODE, V NAME) and product details (P CODE, P DESCRIPT) and product quantity on hand in excess of product min quantity (give alias of Quantity above...
* myCompany.SQL Introduction to SQL Script file for ORACLE DBMS This script file creates the following...
* 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...
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 produ...
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...
We will use a simple database composed of the following tables: CUSTOMER, INVOICE, LINE, PRODUCT, and...
We will use a simple database composed of the following tables:
CUSTOMER, INVOICE, LINE, PRODUCT, and VENDOR.
This database model is shown in Figure 1. The database model in
Figure 1 reflects the following business rules: - A customer may
generate many invoices. Each invoice is generated by one customer.
- An invoice contains one or more invoice lines. Each invoice line
is associated with one invoice. - Each invoice line references one
product. A product may be found in...
Question 1. For each table, identify the primary key and the foreign key(s). If a table...
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...
SQL I have a database CREATE TABLE vendor ( vid CHAR(2) NOT NULL, vname VARCHAR(25) NOT...
SQL
I have a database
CREATE TABLE vendor
( vid CHAR(2) NOT NULL,
vname VARCHAR(25) NOT NULL,
PRIMARY KEY (vid) );
CREATE TABLE category
( catid CHAR(2) NOT NULL,
catname VARCHAR(25) NOT NULL,
PRIMARY KEY (catid) );
CREATE TABLE product
( pid CHAR(3) NOT NULL,
pname VARCHAR(25) NOT NULL,
price NUMERIC (7,2) NOT NULL,
vid CHAR(2) NOT NULL,
categoryid CHAR(2) NOT NULL,
PRIMARY KEY (pid));
CREATE TABLE region
( rid CHAR NOT NULL,
rname VARCHAR(25) NOT NULL,
PRIMARY KEY (rid)...
Using the Premier Products database answer the following questions 1. Using a union, display all customers...
Using the Premier Products database answer the following questions 1. Using a union, display all customers that have rep number 65 or live in the town of Sheldon. Display all customer information. Insert your snip of the query and resultset together here: 2. Using a subquery, list the rep information for all customers who have the same credit limit as customer number 725. To receive credit for this question, do not explicitly query for rep number 35. Let MySQL do...
Please help we are suppose to explain what the C code does. This is the electronics database (6 pts) This problem is...
Please help we are suppose to explain what the C code does.
This is the electronics database
(6 pts) This problem is based on Electronics database. Add some annotations to the following table to explain what the C program is doing in detail. Note that "void" is how C declares a function that does not return anything. You are expected to generalize or research svntaxes not mentioned in the lecture C Program Annotation void newPCproduct EXEC SQL BEGIN DECLARE SECTION;...
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');
/*...
CUSTOMER EMP INVOICE LINE P CODE V CODE CUS CODE EMP CODE NV NUMBER NV NUMBER, LINE NUMBER Create SQL queries for displaying the following results 5. List the customers who have ordered product bearing product code "23109-HB. Display first names, last names of customers, invoice numbers and product codes 6. Display Vendor details (V_CODE, V NAME) and product details (P CODE, P DESCRIPT) and product quantity on hand in excess of product min quantity (give alias of Quantity above...
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...
We will use a simple database composed of the following tables:
CUSTOMER, INVOICE, LINE, PRODUCT, and VENDOR.
This database model is shown in Figure 1. The database model in
Figure 1 reflects the following business rules: - A customer may
generate many invoices. Each invoice is generated by one customer.
- An invoice contains one or more invoice lines. Each invoice line
is associated with one invoice. - Each invoice line references one
product. A product may be found in...
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...
SQL
I have a database
CREATE TABLE vendor
( vid CHAR(2) NOT NULL,
vname VARCHAR(25) NOT NULL,
PRIMARY KEY (vid) );
CREATE TABLE category
( catid CHAR(2) NOT NULL,
catname VARCHAR(25) NOT NULL,
PRIMARY KEY (catid) );
CREATE TABLE product
( pid CHAR(3) NOT NULL,
pname VARCHAR(25) NOT NULL,
price NUMERIC (7,2) NOT NULL,
vid CHAR(2) NOT NULL,
categoryid CHAR(2) NOT NULL,
PRIMARY KEY (pid));
CREATE TABLE region
( rid CHAR NOT NULL,
rname VARCHAR(25) NOT NULL,
PRIMARY KEY (rid)...
Please help we are suppose to explain what the C code does.
This is the electronics database
(6 pts) This problem is based on Electronics database. Add some annotations to the following table to explain what the C program is doing in detail. Note that "void" is how C declares a function that does not return anything. You are expected to generalize or research svntaxes not mentioned in the lecture C Program Annotation void newPCproduct EXEC SQL BEGIN DECLARE SECTION;...
Most questions answered within 3 hours.
-
1. Explain how a fractionating column increases the separation
of two liquids.
2. What is an...
asked 1 minute from now -
One of two hydrogen atoms has electrons at n = 1 Bohr orbit
while the other...
asked 32 seconds ago -
On January 1, Year 1, Sheffield Co. issued bonds with a face
value of $200,000, a...
asked 10 minutes ago -
Write a short application that computes the perimeter of a
rectangle. It should allow the user...
asked 10 minutes ago -
An inventory of Product A has to be maintained at NiceCare using
the EOQ model, based...
asked 18 minutes ago -
Define the 2
24.Moral Imperialism
25.Selective Service Act
26.Tulsa Riot
27.Red Scare
28.League of Nations
29....
asked 27 minutes ago -
A laser beam is incident at an angle of 30.6° to the vertical
onto a solution...
asked 34 minutes ago -
NO VECTORS PLEASE
Start a simple Matrix template class
In this lab, you’ll be writing a...
asked 36 minutes ago -
5. Why might the two alleles, taster and non-taster, persist in
human populations over many generations?...
asked 28 minutes ago -
1- What is the energy of a photon with a frequency of 7.01 ×
10¹⁴ s⁻¹?...
asked 53 minutes ago -
Cesar needs to make a decision whether to fertilize his garden
or not. His profit depends...
asked 56 minutes ago -
Illustrate the Substitution Effect, Income Effect and Total
Effect of a normal good and an inferior...
asked 54 minutes ago