Create a database called “IS664Sample”.

IS664SampleProject
You should put your answers and the screenshots of the results for each question
1. Create a database called “IS664Sample”. You can use either the Management Studio or use script. (1 point)
2. Create all database tables in your “IS664Sample” database by running the script “Final Sample Project DB-INIT.sql”. (1 point)
3. The “IS664Sample” database is a database for a company (say Company A) reselling products purchased from various vendors to its customers. Use the database to answer the following queries:
a. What products have a price that exceeds the average product price? (2 points)
b. List all invoice data (CUS_LNAME, CUS_FNAME, INVOICE.INV_NUMBER, INV_DATE, P_DESCRIPT) for customer number 10011. (2 points)
c. Display vendors whose products have not been ordered by Company A: Do not use the column V_Order. (3 points)
d. Find the product with lowest unit price. (3 points)
e. Find the product with lowest unit price (p_price) for each vendor; include p_code and p_descript. (3 points)
4. Create a view called “CheapProduct” in your “IS664Sample” database to list all products (show these column information: P_DESCRIPT, P_QOH, P_PRICE) with price less than 50. (3 points)
5. List all the customers who have not made any purchase: display the customer name as with the heading (Customer_Name): Use the following two types of SQL.
a. Use JOIN (3 points)
b. Use EXISTS (3 points)
6. Write a stored procedure that accepts an INV_NUMBER, CUS_CODE, and INV_DATE, and inserts them as a new row in the INVOICE table in your “IS664Sample” database. Then run the procedure to add one new row. (3 points)
7. Create a non-clustered index on the P_PRICE column of the PRODUCT table in your “IS664Sample” database and then delete the index you created. (2 points)
8. Write a simple T-SQL script to output the name of the local server that the script is running from. (1 point)

Having A lot of Trouble with questions 3c, 5, and 6. I’d appreciate any help as I need it in about 2 days. Thank you! Below is the DB INIT SQL file for making tables.

/* Final Sample Project DB-INIT.SQL */
/* This script file creates the following tables: */
/* VENDOR, PRODUCT, CUSTOMER, INVOICE, LINE */
/* and loads data rows for all tables */
BEGIN TRANSACTION;
USE IS664Sample
GO
CREATE TABLE VENDOR (
V_CODE INTEGER,
V_NAME VARCHAR(35) NOT NULL,
V_CONTACT VARCHAR(15) NOT NULL,
V_AREACODE CHAR(3) NOT NULL,
V_PHONE CHAR(8) NOT NULL,
V_STATE CHAR(2) NOT NULL,
V_ORDER CHAR(1) NOT NULL,
PRIMARY KEY (V_CODE));
CREATE TABLE PRODUCT (
P_CODE VARCHAR(10) CONSTRAINT PRODUCT_P_CODE_PK PRIMARY KEY,
P_DESCRIPT VARCHAR(35) NOT NULL,
P_INDATE DATETIME NOT NULL,
P_QOH INTEGER NOT NULL,
P_MIN INTEGER NOT NULL,
P_PRICE NUMERIC(8,2) NOT NULL,
P_DISCOUNT NUMERIC(4,2) NOT NULL,
V_CODE INTEGER,
CONSTRAINT PRODUCT_V_CODE_FK
FOREIGN KEY (V_CODE) REFERENCES VENDOR);
CREATE TABLE CUSTOMER (
CUS_CODE INTEGER PRIMARY KEY,
CUS_LNAME VARCHAR(15) NOT NULL,
CUS_FNAME VARCHAR(15) NOT NULL,
CUS_INITIAL CHAR(1),
CUS_AREACODE CHAR(3) DEFAULT ‘615’ NOT NULL CHECK(CUS_AREACODE IN (‘615′,’713′,’931’)),
CUS_PHONE CHAR(8) NOT NULL,
CUS_BALANCE NUMERIC(9,2) DEFAULT 0.00,
CONSTRAINT CUS_UI1 UNIQUE(CUS_LNAME,CUS_FNAME));
CREATE TABLE INVOICE (
INV_NUMBER INTEGER PRIMARY KEY,
CUS_CODE INTEGER NOT NULL REFERENCES CUSTOMER(CUS_CODE),
INV_DATE DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
CONSTRAINT INV_CK1 CHECK (INV_DATE > ‘2006-01-01′));
CREATE TABLE LINE (
INV_NUMBER INTEGER NOT NULL,
LINE_NUMBER NUMERIC(2,0) NOT NULL,
P_CODE VARCHAR(10) NOT NULL,
LINE_UNITS NUMERIC(9,2) DEFAULT 0.00 NOT NULL,
LINE_PRICE NUMERIC(9,2) DEFAULT 0.00 NOT NULL,
PRIMARY KEY (INV_NUMBER,LINE_NUMBER),
FOREIGN KEY (INV_NUMBER) REFERENCES INVOICE ON DELETE CASCADE,
FOREIGN KEY (P_CODE) REFERENCES PRODUCT(P_CODE),
CONSTRAINT LINE_UI1 UNIQUE(INV_NUMBER, P_CODE));
/* Loading data rows */
USE IS664Sample
GO
/* VENDOR rows */
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’,’Y’);
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’);
/* PRODUCT rows */
INSERT INTO PRODUCT VALUES(’11QER/31′,’Power painter, 15 psi., 3-nozzle’ ,’03-NOV-2005′, 8, 5,109.99,0.00,25595);
INSERT INTO PRODUCT VALUES(’13-Q2/P2′,’7.25-in. pwr. saw blade’ ,’13-DEC-2005′, 32, 15, 14.99,0.05,21344);
INSERT INTO PRODUCT VALUES(’14-Q1/L3′,’9.00-in. pwr. saw blade’ ,’13-NOV-2005′, 18, 12, 17.49,0.00,21344);
INSERT INTO PRODUCT VALUES(‘1546-QQ2′,’Hrd. cloth, 1/4-in., 2×50′ ,’15-JAN-2006’, 15, 8, 39.95,0.00,23119);
INSERT INTO PRODUCT VALUES(‘1558-QW1′,’Hrd. cloth, 1/2-in., 3×50′ ,’15-JAN-2006’, 23, 5, 43.99,0.00,23119);
INSERT INTO PRODUCT VALUES(‘2232/QTY’,’B&D jigsaw, 12-in. blade’ ,’30-DEC-2005′, 8, 5,109.92,0.05,24288);
INSERT INTO PRODUCT VALUES(‘2232/QWE’,’B&D jigsaw, 8-in. blade’ ,’24-DEC-2005′, 6, 5, 99.87,0.05,24288);
INSERT INTO PRODUCT VALUES(‘2238/QPD’,’B&D cordless drill, 1/2-in.’ ,’20-JAN-2006′, 12, 5, 38.95,0.05,25595);
INSERT INTO PRODUCT VALUES(‘23109-HB’,’Claw hammer’ ,’20-JAN-2006′, 23, 10, 9.95,0.10,21225);
INSERT INTO PRODUCT VALUES(‘23114-AA’,’Sledge hammer, 12 lb.’ ,’02-JAN-2006′, 8, 5, 14.40,0.05,NULL);
INSERT INTO PRODUCT VALUES(‘54778-2T’,’Rat-tail file, 1/8-in. fine’ ,’15-DEC-2005′, 43, 20, 4.99,0.00,21344);
INSERT INTO PRODUCT VALUES(’89-WRE-Q’,’Hicut chain saw, 16 in.’ ,’07-FEB-2006′, 11, 5,256.99,0.05,24288);
INSERT INTO PRODUCT VALUES(‘PVC23DRT’,’PVC pipe, 3.5-in., 8-ft’ ,’20-FEB-2006′,188, 75, 5.87,0.00,NULL);
INSERT INTO PRODUCT VALUES(‘SM-18277′,’1.25-in. metal screw, 25′ ,’01-MAR-2006’,172, 75, 6.99,0.00,21225);
INSERT INTO PRODUCT VALUES(‘SW-23116′,’2.5-in. wd. screw, 50′ ,’24-FEB-2006’,237,100, 8.45,0.00,21231);
INSERT INTO PRODUCT VALUES(‘WR3/TT3′ ,’Steel matting, 4”x8”x1/6″, .5″ mesh’,’17-JAN-2006′, 18, 5,119.95,0.10,25595);
/* CUSTOMER rows */
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’ ,NULL,’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’,NULL,’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);
/* INVOICE rows */
INSERT INTO INVOICE VALUES(1001,10014,’16-JAN-2006′);
INSERT INTO INVOICE VALUES(1002,10011,’16-JAN-2006′);
INSERT INTO INVOICE VALUES(1003,10012,’16-JAN-2006′);
INSERT INTO INVOICE VALUES(1004,10011,’17-JAN-2006′);
INSERT INTO INVOICE VALUES(1005,10018,’17-JAN-2006′);
INSERT INTO INVOICE VALUES(1006,10014,’17-JAN-2006′);
INSERT INTO INVOICE VALUES(1007,10015,’17-JAN-2006′);
INSERT INTO INVOICE VALUES(1008,10011,’17-JAN-2006′);
/* LINE rows */
INSERT INTO LINE VALUES(1001,1,’13-Q2/P2′,1,14.99);
INSERT INTO LINE VALUES(1001,2,’23109-HB’,1,9.95);
INSERT INTO LINE VALUES(1002,1,’54778-2T’,2,4.99);
INSERT INTO LINE VALUES(1003,1,’2238/QPD’,1,38.95);
INSERT INTO LINE VALUES(1003,2,’1546-QQ2′,1,39.95);
INSERT INTO LINE VALUES(1003,3,’13-Q2/P2′,5,14.99);
INSERT INTO LINE VALUES(1004,1,’54778-2T’,3,4.99);
INSERT INTO LINE VALUES(1004,2,’23109-HB’,2,9.95);
INSERT INTO LINE VALUES(1005,1,’PVC23DRT’,12,5.87);
INSERT INTO LINE VALUES(1006,1,’SM-18277′,3,6.99);
INSERT INTO LINE VALUES(1006,2,’2232/QTY’,1,109.92);
INSERT INTO LINE VALUES(1006,3,’23109-HB’,1,9.95);
INSERT INTO LINE VALUES(1006,4,’89-WRE-Q’,1,256.99);
INSERT INTO LINE VALUES(1007,1,’13-Q2/P2′,2,14.99);
INSERT INTO LINE VALUES(1007,2,’54778-2T’,1,4.99);
INSERT INTO LINE VALUES(1008,1,’PVC23DRT’,5,5.87);
INSERT INTO LINE VALUES(1008,2,’WR3/TT3′,3,119.95);
INSERT INTO LINE VALUES(1008,3,’23109-HB’,1,9.95);
COMMIT;

find the cost of your paper

The post Create a database called “IS664Sample”. appeared first on Best Custom Essay Writing Services | EssayBureau.com.

HelpHub
Calculate your paper price
Pages (550 words)
Approximate price: -

Why Choose HelpHub

HelpHub

Quality Researched Papers

We always make sure that writers follow all your instructions precisely. You can choose your academic level: high school, college/university or professional, and we will assign a writer who has a respective degree.

HelpHub

Qualified Writers

We have hired a team of professional writers experienced in academic and business writing. Most of them are native speakers and PhD holders able to take care of any assignment you need help with.

StudyAcer

Unlimited Revisions

If you think we missed something, send your order for a free revision. You have 10 days to submit the order for review after you have received the final document. You can do this yourself after logging into your personal account.

StudyAcer

On Time Delivery

All papers are always delivered on time. In case we need more time to master your paper, we may contact you regarding the deadline extension. We will always strive to deliver on time.

StudyAcer

Original & Confidential

We use several writing tools checks to ensure that all documents you receive are free from plagiarism. Our editors carefully review all quotations in the text.

StudyAcer

24/7 Customer Support

Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.

Try it now!

Calculate the price of your order

Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Help Hub

Place your order

Fill in the order form and provide all details of your assignment.

Help Hub

Proceed with the payment

Choose the payment system that suits you most.

Help Hub

Receive the final file

Once your paper is ready, we will email it to you.

HelpHub Writing Services

No need to work on essay at night. Sleep tight, we will cover your back. We offer all kinds of essay writing services.

HelpHub HelpHub

Essays

Essay Writing Service

No matter what kind of academic paper you need and how urgent you need it, you are welcome to choose your academic level and the type of your paper at an affordable price. We take care of all your paper needs and give a 24/7 customer care support system.

HelpHub HelpHub

Admissions

Admission Essays

An admission essay is an essay or other written statement by a candidate, often a potential student enrolling in a college, university, or graduate school. You can be rest assurred that through our service we will write the best admission essay for you.

HelpHub HelpHub

Editing

Editing Support

Our academic writers and editors make the necessary changes to your paper so that it is polished. We also format your document by correctly quoting the sources and creating reference lists in the formats APA, Harvard, MLA, Chicago / Turabian.

HelpHub HelpHub

Revision

Revision Support

If you think your paper could be improved, you can request a review. In this case, your paper will be checked by the writer or assigned to an editor. You can use this option as many times as you see fit. This is free because we want you to be completely satisfied.