Question

Here is listing of the tables for the database for this assignment.

SELLERS(SellerNum, SellerLastName, SellerFirstName, SellerStreet, SellerCity, SellerState, SellerZip, CRate)

BUYERS(BuyerNum, BuyerLastName, BuyerFirstName, BuyerStreet, BuyerCity, BuyerState, BuyerZip, BonusLevel, SellerNum)

PRODUCTS(ProductNum, Description, NumberInStock)

STATEMENT(StatementNum, StatementDate, BuyerNum)

STATEMENTLINE(StatementNum, ProductNum, Quantity, ItemPrice)

Using the above database’s tables, respond to the following questions:

1. Write SQL CREATE TABLE statements for each of the above tables. Be sure to include the Primary and Foreign keys.

Note: The id numbers for each table should be a numeric (integer) value, the commission rate (Crate) and the ItemPer (individual quoted price per item) should be numeric with 2 decimal places, and the quantity (quantity of the products ordered) and NumberInStock (number of items in stock) should be numeric (integer) values. Additionally, the states should use the common 2 character state abbreviation and the statement date should be a date value. All other values are character values.

2. Write SQL statements to insert onecomplete row of data into each table. You make up the values that go in the rows.
3. Write SQL statements to list all columns of each table -- one statement per table.
4. Write a SQL statement to list all of the sellers (first and last names and commission rates) that have commission rates of 4.25% (as a decimal value) or higher
5. Write a SQL statement to list all of the buyers (first and last names) that have a bonus level of “Green”.
6. Write a SQL statement to list the all of the Arkansas buyers’ first and last names in alphabetical order.
7. One of the sellers has a last name of “Lee”, another has the last name of “Caswell”. Write a SQL statement to list all of the buyers (first and last names) that have been assigned to them.
8. One of the buyers has the last name of “Gadea”. Write a SQL statement to list the descriptions of all of the items that she purchased.

Answer 1

Question1 : Write SQL CREATE TABLE statements for each of the above tables. Be sure to include the Primary and Foreign keys.

Answer :

• SELLERS(SellerNum, SellerLastName, SellerFirstName, SellerStreet, SellerCity, SellerState, SellerZip, CRate)

CREATE TABLE SELLER (

SellerNum int NOT NULL,

SellerLastName varchar(100),

SellerFirstName varchar(100),

SellerStreet varchar(255),

SellerCity varchar(255),

SellerState varchar(255),

SellerZip int,

CRate varchar(255)

PRIMERY KEY(SellerNum)

);

• BUYERS(BuyerNum, BuyerLastName, BuyerFirstName, BuyerStreet, BuyerCity, BuyerState, BuyerZip, BonusLevel, SellerNum)

CREATE TABLE BUYERS(

BuyerNum int NOT NULL,

BuyerLastName varchar(255),

BuyerFirstName varchar(255),

BuyerStreet varchar(255),

BuyerCity varchar(255),

BuyerState varchar(255),

BuyerZip varchar(255),

BonusLevel varchar(255),

SellerNum varchar(255),

PRIMARY KEY(BuyerNum),

FOREIGN KEY(SellerNum)

);

• PRODUCTS(ProductNum, Description, NumberInStock)

CREATE TABLE PRODUCTS (

ProductNum int NOT NULL,

Description varchar(255),

NumberInStock int,

PRIMARY KEY(ProductNum)

);

• STATEMENT(StatementNum, StatementDate, BuyerNum)

CREATE TABLE STATEMENT(

StatementNum int NOT NULL,

StatementDate date,

BuyerNum int ,

PRIMARY KEY(StatementNum),

FOREIGN KEY(BuyerNum)

);

• STATEMENTLINE(StatementNum, ProductNum, Quantity, ItemPrice)

CREATE TABLE STATEMENTLINE(

StatementNum int NOT NULL,

ProductNum int,

Quantity int,

ItemPrice float,

PRIMARY KEY(StatementNum),

FOREIGN KEY(ProductNum)

);

Question 2 : Write SQL statements to insert onecomplete row of data into each table.

Answer :

INSERT INTO SELLER

(SellerNum, SellerLastName, SellerFirstName, SellerStreet, SellerCity, SellerState, SellerZip, CRate)

VALUES

(1,'SINGH','RANVEER','STREET NO-3','LUDHIANA','PUNJAB','140141','1440');

INSERT INTO BUYERS(BuyerNum, BuyerLastName, BuyerFirstName, BuyerStreet, BuyerCity, BuyerState, BuyerZip, BonusLevel, SellerNum) VALUES (1,'DEEP','AMAN','SHIVAJI STREET','RAJPURA','PUNJAB','140401' , 'GREEN',1);

INSERT INTO PRODUCTS(ProductNum, Description, NumberInStock) VALUES (1,'Skin Glowing Creame',87);

INSERT INTO STATEMENT(StatementNum, StatementDate, BuyerNum) VALUES (1,2020/09/26,1);

INSERT INTO STATEMENTLINE(StatementNum, ProductNum, Quantity, ItemPrice) VALUES (1,1,10,49);

Question 3 :  Write SQL statements to list all columns of each table.

Answer :

SELECT * FROM SELLER;

SELECT * FROM BUYERS;

SELECT * FROM PRODUCTS;

SELECT * FROM STATEMENT;

SELECT * FROM STATEMENTLINE;

Question 4 : Write a SQL statement to list all of the sellers (first and last names and commission rates) that have commission rates of 4.25% (as a decimal value) or higher

Answer :

SELECT * FROM SELLERS WHERE CRate>=4.25;

Question 5 :Write a SQL statement to list all of the buyers (first and last names) that have a bonus level of “Green”

Answer :

SELECT * FROM BUYERS WHERE BonusLevel = 'GREEN';

Question 6: Write a SQL statement to list the all of the Arkansas buyers’ first and last names in alphabetical order.

Answer :

SELECT BuyerLastName, BuyerFirstName FROM BUYERS ORDER BY BuyerFirstName

Question 7: One of the sellers has a last name of “Lee”, another has the last name of “Caswell”. Write a SQL statement to list all of the buyers (first and last names) that have been assigned to them.

Answer:

DECLARE @Temp1 AS int, @Temp2 AS int;

SET @Temp1 = (Select SellerNum From SELLERS WHERE  SellerLastName = 'Lee';

SET @Temp2 = (Select SellerNum From SELLERS WHERE  SellerLastName = 'Caswell';

SELECT BuyerLastName, BuyerFirstName FROM BUYERS WHERE SellerNum = @Temp1;

SELECT BuyerLastName, BuyerFirstName FROM BUYERS WHERE SellerNum = @Temp2;

Question 8 : One of the buyers has the last name of “Gadea”. Write a SQL statement to list the descriptions of all of the items that she purchased.

Answer:

DECLARE @ID AS INT;

SET @ID = (SELECT BuyerNum FROM BUYERS WHERE BuyerLastName = 'Gadea');

DECLARE @ID2 AS INT;

SET @ID = (SELECT StatementNum FROM STATEMENT WHERE BUYERNUM = ID1);

I am not clearly sure about the last question so i am very sorry for this . Please rate

Thankyou