ZuoTi.Pro
Question

--Create procedure for GetPartNums in SQL --GetPartNums- retrieve all part nums from the database --Database if...

--Create procedure for GetPartNums in SQL

--GetPartNums- retrieve all part nums from the database

--Database

if db_id('TestPremierProducts') is not null
begin
   use master
   alter database TestPremierProducts set SINGLE_USER with rollback immediate
   drop database TestPremierProducts
end

create database TestPremierProducts
go

USE TestPremierProducts;
go

/* create the tables */

CREATE TABLE Rep
(RepNum int Identity(1,1),
LastName VARCHAR(15),
FirstName VARCHAR(15),
Street VARCHAR(15),
City VARCHAR(15),
State VARCHAR(2),
Zip VARCHAR(5),
Commission MONEY,
Rate DECIMAL(3,2),
PRIMARY KEY (RepNum));

go

CREATE TABLE Customer
(CustomerNum int Identity(1,1) PRIMARY KEY,
CustomerName VARCHAR(35) NOT NULL,
Street VARCHAR(15),
City VARCHAR(15),
State VARCHAR(2),
Zip VARCHAR(5),
Balance MONEY,
CreditLimit MONEY,
RepNum INT,
FOREIGN KEY (RepNum) REFERENCES Rep)
;

go

CREATE TABLE Orders
(OrderNum int Identity(10000,1) PRIMARY KEY,
OrderDate DATETIME,
CustomerNum INT references Customer,
Billed BIT)
;

go

CREATE TABLE Part
(PartNum int Identity(100,5) PRIMARY KEY,
Description VARCHAR(15),
OnHand INT,
Class VARCHAR(2),
Warehouse VARCHAR(1),
Price MONEY )
;

go

CREATE TABLE OrderLine
(OrderNum INT references Orders,
PartNum INT references Part,
NumOrdered INT,
NumShipped INT,
QuotedPrice MONEY,
PRIMARY KEY (OrderNum, PartNum) )
;

go

/* Populate the database with initial data */

INSERT INTO Rep (LastName, FirstName, Street, City, State, Zip, Commission, Rate)
VALUES ('DaMan','Foncey','121 Palindrome','TacocaT','LA','10101',5202.50,0.11);
Declare @FonceyNum int
select @FonceyNum = @@Identity

INSERT INTO Rep (LastName, FirstName, Street, City, State, Zip, Commission, Rate)
VALUES ('Kaiser','Valerie','624 Randall','Grove','FL','33321',20542.50,0.05);
Declare @KaiserNum int
select @KaiserNum = @@Identity

INSERT INTO Rep (LastName, FirstName, Street, City, State, Zip, Commission, Rate)
VALUES ('Hull','Richard','532 Jackson','Sheldon','FL','33553',39216.00,0.07);
Declare @HullNum int
select @HullNum = @@Identity

INSERT INTO Rep (LastName, FirstName, Street, City, State, Zip, Commission, Rate)
VALUES ('Perez','Juan','1626 Taylor','Fillmore','FL','33336',23487.00,0.05);
Declare @PerezNum int
select @PerezNum = @@Identity

INSERT INTO Customer (CustomerName, Street, City, State, Zip, Balance, CreditLimit, RepNum)
VALUES('Al''s Appliance and Sport','2837 Greenway','Fillmore','FL','33336',6550.00,7500.00,@KaiserNum);
Declare @AlApplNum int
select @AlApplNum = @@Identity

INSERT INTO Customer (CustomerName, Street, City, State, Zip, Balance, CreditLimit, RepNum)
VALUES('Brookings Direct','3827 Devon','Grove','FL','33321',431.50,10000.00,@HullNum);
Declare @BrookingsNum int
select @BrookingsNum = @@Identity

INSERT INTO Customer (CustomerName, Street, City, State, Zip, Balance, CreditLimit, RepNum)
VALUES('Ferguson''s','382 Wildwood','Northfield','FL','33146',5785.00,7500.00,@PerezNum);
Declare @FergusonNum int
select @FergusonNum = @@Identity

INSERT INTO Customer (CustomerName, Street, City, State, Zip, Balance, CreditLimit, RepNum)
VALUES('The Everything Shop','1828 Raven','Crystal','FL','33503',5285.25,5000.00,@HullNum);
Declare @EverythingNum int
select @EverythingNum = @@Identity

INSERT INTO Customer (CustomerName, Street, City, State, Zip, Balance, CreditLimit, RepNum)
VALUES('Bargains Galore','3829 Central','Grove','FL','33321',3412.00,10000.00,@PerezNum);
Declare @BargainsNum int
select @BargainsNum = @@Identity

INSERT INTO Customer (CustomerName, Street, City, State, Zip, Balance, CreditLimit, RepNum)
VALUES('Kline''s','838 Ridgeland','Fillmore','FL','33336',12762.00,15000.00,@KaiserNum);
Declare @KlinesNum int
select @KlinesNum = @@Identity

INSERT INTO Customer (CustomerName, Street, City, State, Zip, Balance, CreditLimit, RepNum)
VALUES('Johnson''s Department Store','372 Oxford','Sheldon','FL','33553',2106.00,10000.00,@PerezNum);
Declare @JohnsonNum int
select @JohnsonNum = @@Identity

INSERT INTO Customer (CustomerName, Street, City, State, Zip, Balance, CreditLimit, RepNum)
VALUES('Lee''s Sport and Appliance','282 Evergreen','Altonville','FL','32543',2851.00,5000.00,@HullNum);
Declare @LeesNum int
select @LeesNum = @@Identity

INSERT INTO Customer (CustomerName, Street, City, State, Zip, Balance, CreditLimit, RepNum)
VALUES('Deerfield''s Four Seasons','282 Columbia','Sheldon','FL','33553',248.00,7500.00,@HullNum);
Declare @DeerfieldsNum int
select @DeerfieldsNum = @@Identity

INSERT INTO Customer (CustomerName, Street, City, State, Zip, Balance, CreditLimit, RepNum)
VALUES('All Season','28 Lakeview','Grove','FL','33321',8221.00,7500.00,@KaiserNum);
Declare @AllSeasonNum int
select @AllSeasonNum = @@Identity


INSERT INTO ORDERS (OrderDate, CustomerNum, Billed)
VALUES('20-OCT-2003',@AlApplNum, 1);
Declare @O1Num int
select @O1Num = @@Identity

INSERT INTO ORDERS (OrderDate, CustomerNum, Billed)
VALUES('20-OCT-2003',@FergusonNum, 1);
Declare @O2Num int
select @O2Num = @@Identity

INSERT INTO ORDERS (OrderDate, CustomerNum, Billed)
VALUES('21-OCT-2003',@EverythingNum, 0);
Declare @O3Num int
select @O3Num = @@Identity

INSERT INTO ORDERS (OrderDate, CustomerNum, Billed)
VALUES('21-OCT-2003',@BrookingsNum, 1);
Declare @O4Num int
select @O4Num = @@Identity

INSERT INTO ORDERS (OrderDate, CustomerNum, Billed)
VALUES('23-OCT-2003',@JohnsonNum, 0);
Declare @O5Num int
select @O5Num = @@Identity

INSERT INTO ORDERS (OrderDate, CustomerNum, Billed)
VALUES('23-OCT-2003',@AlApplNum, 0);
Declare @O6Num int
select @O6Num = @@Identity

INSERT INTO ORDERS (OrderDate, CustomerNum, Billed)
VALUES('23-OCT-2003',@JohnsonNum, 0);
Declare @O7Num int
select @O7Num = @@Identity



INSERT INTO PART (Description, OnHand, Class, Warehouse, Price)
VALUES('Iron',50,'HW','3',24.95);
Declare @P1Num int
select @P1Num = @@Identity
INSERT INTO PART
VALUES('Home Gym',45,'SG','2',794.95);
Declare @P2Num int
select @P2Num = @@Identity
INSERT INTO PART
VALUES('Microwave Oven',32,'AP','1',165.00);
Declare @P3Num int
select @P3Num = @@Identity
INSERT INTO PART
VALUES('Cordless Drill',21,'HW','3',129.95);
Declare @P4Num int
select @P4Num = @@Identity
INSERT INTO PART
VALUES('Gas Range',8,'AP','2',495.00);
Declare @P5Num int
select @P5Num = @@Identity
INSERT INTO PART
VALUES('Washer',12,'AP','3',399.99);
Declare @P6Num int
select @P6Num = @@Identity
INSERT INTO PART
VALUES('Stand Mixer',22,'HW','3',159.95);
Declare @P7Num int
select @P7Num = @@Identity
INSERT INTO PART
VALUES('Dryer',12,'AP','1',349.95);
Declare @P8Num int
select @P8Num = @@Identity
INSERT INTO PART
VALUES('Dishwasher',8,'AP','3',595.00);
Declare @P9Num int
select @P9Num = @@Identity
INSERT INTO PART
VALUES('Treadmill',9,'SG','2',1390.00);
Declare @P10Num int
select @P10Num = @@Identity


INSERT INTO OrderLine
VALUES(@O1Num,@P1Num,13,10,21.95);
INSERT INTO OrderLine
VALUES(@O2Num,@P5Num,2,2,495.00);
INSERT INTO OrderLine
VALUES(@O2Num,@P6Num,2,2,399.99);
INSERT INTO OrderLine
VALUES(@O3Num,@P8Num,4,4,329.95);
INSERT INTO OrderLine
VALUES(@O4Num,@P9Num,2,2,595.00);
INSERT INTO OrderLine
VALUES(@O5Num,@P2Num,2,1,794.95);
INSERT INTO OrderLine
VALUES(@O5Num,@P3Num,4,3,150.00);
INSERT INTO OrderLine
VALUES(@O6Num,@P5Num,1,1,495.00);
INSERT INTO OrderLine
VALUES(@O7Num,@P5Num,1,1,495.00);
INSERT INTO OrderLine
VALUES(@O7Num,@P10Num,2,1,1290.00);

go
engineering   Computer-Science   
0 0
Add a comment Improve this question Transcribed image text
Next > < Previous
Sort answers by oldest

Homework Answers

Answer #1

create procedure GetPartNums

as

begin

select PartNum from Part //It will select all the partNums from the part table

end

0 0
Add a comment
Know the answer?
Add Answer to:
--Create procedure for GetPartNums in SQL --GetPartNums- retrieve all part nums from the database --Database if...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions

  • 2. Examine the SQL Script for this test.  Explain the purpose of each of the stored procedures...

    2. Examine the SQL Script for this test.  Explain the purpose of each of the stored procedures listed below.   Test2SP1 Test2SP2 Test2SP3 Test2SP4 Test2SP5 Test2SP6 DATABASE CODE: if db_id('Test2PremierProducts') is not null begin use master alter database Test2PremierProducts set SINGLE_USER with rollback immediate drop database Test2PremierProducts end create database Test2PremierProducts go USE Test2PremierProducts; go /* create the tables */ CREATE TABLE Rep (RepNum int Identity(10,10), LastName VARCHAR(15), FirstName VARCHAR(15), Street VARCHAR(15), City VARCHAR(15), State VARCHAR(2), Zip VARCHAR(5), Commission MONEY, Rate DECIMAL(3,2),...

  • 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...

  • Program: MYSQL (please show screenshots of the solution)  Ill give good rating! Define a view named TopLevelCust...

    Program: MYSQL (please show screenshots of the solution)  Ill give good rating! Define a view named TopLevelCust that consists of the number, name, address, balance and credit limit of all customers with credit limits that are greater than or equal to $5,000. Address is defined as street, city, state and zip. Concatenate these into one field named Address.   A. Type in your view construct here: B. Write a query to retrieve the number and name of all customers in the TopLevelCust...

  • Use the Premiere Products database (see Figure 1-2 in Chapter 1) to complete the following exercises....

    Use the Premiere Products database (see Figure 1-2 in Chapter 1) to complete the following exercises. If directed to do so by your instructor, use the information provided with the Chapter 3 Exercises to print your output. 1. List the part number and description for all parts. The part descriptions should appear in uppercase letters. 2. List the customer number and name for all customers located in the city of Grove.Your query should ignore case. For example, a customer with...

  • 8.Write the commands to obtain the following information from the systems catalog. List every table that...

    8.Write the commands to obtain the following information from the systems catalog. List every table that you own, list every column in the PART table and its associated data type, then add ORDER_NUM as a foreign key in the ORDER_LINE table. this is the SQL for the database: CREATE DATABASE PREMIERE; USE PREMIERE; CREATE TABLE REP (REP_NUM CHAR(2) PRIMARY KEY, LAST_NAME CHAR(15), FIRST_NAME CHAR(15), STREET CHAR(15), CITY CHAR(15), STATE CHAR(2), ZIP CHAR(5), COMMISSION DECIMAL(7,2), RATE DECIMAL(3,2) ); CREATE TABLE CUSTOMER...

  • (TCO 7) Write a query to display the orderid, order date, customer last name and firstname...

    (TCO 7) Write a query to display the orderid, order date, customer last name and firstname for all orders that have shipped. SalesRep Customer ReplD int PK CustomerID int LastName FirstName Commission Rate varchar(20) varchar(20) decimal(10,2) LastName FirstName Street City State Zipcode Balance ReplD varchar(20) varchar(20) varchar(20) varchar(20) char(2) char(5) HO----O decimal(10,2) Order OrderID PK int FK1 int FK1 CustomerID OrderDate ShipDate date date Part PK PartID int Orderline varchar(20) int Description UnitsOnHand Item Class Retail Cost PK,FK1 OrderID PartID...

  • Answer the following questions using mysql. Please show screenshots to make sure the queries work...

    Answer the following questions using mysql. Please show screenshots to make sure the queries work. 1. Use a sub-query and the IN operator to find the number and name of each customer that placed an order on October 23, 2007. 2. Repeat step 2, but this time use the EXISTS operator in your answer. 3. Using a sub-query, find the number and name of each customer that did not place an order on October 23, 2007. 4. Using a sub-query,...

  • True or false? True or false? Question 63 Based on the Customer table below, which command...

    True or false? True or false? Question 63 Based on the Customer table below, which command lists all information from the table concerning customer 282? CustomerNum CustomerName --- Street City State Zip 148 Al's Appliance 28 Greenway Filmore FL 33336 282 Brookings 3827 Devon Grove FL 33321 Question 37 You have your project all set locally and on the server. You want to now make changes to your site. Which of the following is the last step of the process?...

  • 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'); /*...

  • SQL Background: Premiere Products database team is working on storing the information contained in the order...

    SQL Background: Premiere Products database team is working on storing the information contained in the order invoice form in a databases table. Looking at what data is captured in the order form, they came up with the following table description using DBDL (DataBase Design Language). ORDER_INVOICE (InvoiceNum, Date, CustomerNum, CustomerName, Street, City, State, Zip, RepNum, RepFirstName, RepLastName, (ItemNum, Description, QtyOrdered, QuotedPrice)) Assumption: -Each order transaction is identified uniquely by InvoiceNum. -Customer may order multiple times and an order may contain...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT