Hi, I am extremely need help with this homework. I am having trouble with this p

Question

Hi, I am extremely need help with this homework. I am having trouble with this problem because I don't understand SQL.

I have to finish and turn in this homework or my professor give me an "F" and I will be expelled from this school. I will be appreciated whoever can help me with this.

Write the SQL for the questions for the following 4 tables:

BUYER (BuyerID(PK), LastName, FirstName, Phone, Email)

PURCHASE (InvoiceNumber(PK), InvoiceDate, PreTaxAmount, BuyerID(fk))

PURCHASE_ITEM (InvoiceNumber(PK1), ItemNumber(fk), RetailPrice)

ITEM (ItemNumber(PK), Description, Cost, CompanyName)

1. Write the SQL "CREATE TABLE" statements including the primary keys

2. Write the SQL statements to add the foreign key constraints using "ALTER TABLE"

3. Write at least 3 INSERT statements (per table) to populate data all 4 tables

4. Write a separate SQL query for each of the following (a-j)

a. List LastName and FirstName of buyers who have made at least one purchase with PreTaxAmount greater than $200. Use a subquery.

b. List ItemNumber and Description for all items that cost more than $100.

c. List ItemNumber and Description for all items that cost more than $100 and were produced by an company with a name ending with the letter son.

d. List LastName and FirstName of buyers who have purchased an item that costs more than $50. Use a subquery.

e. Answer part d but use a join and alias for table names

f. List LastName and FirstName of buyers who have purchased an item that was created by an company with a name that begins with the letter J. Use a subquery.

g. Answer part f but use a join and alias for table names.

h. List the Name and sum of PreTaxAmount for each buyer.

i. List the sum of PreTaxAmount for each CompanyName. Sort the results by CompanyName, in descending order.

j. List the sum of PreTaxAmount for each CompanyName but exclude any items that were part of purchases with PreTaxAmount less than $25.

Explanation / Answer

Note::

If you SQL SERVER then take datatype numeric for number input and FOR ORACLE take number datatype

For Buyer Table :

create table BUYER
(BuyerID numeric(5) primary key,
LastName varchar(50) not null,
FirstName varchar(50) not null,
Phone numeric(10) not null,
Email varchar(50)
);

For Purchase

create table PURCHASE
(InvoiceNumber numeric(5) primary key,
InvoiceDate datetime not null,
PreTaxAmount numeric(10) not null,
BuyerID numeric(5)
);

For Item

create table ITEM
(ItemNumber numeric(5) primary key,
Description varchar(50),
Cost numeric(10),
CompanyName varchar(50)
);

For Purchase_Item

create table PURCHASE_ITEM
(InvoiceNumber numeric(5) primary key,
ItemNumber numeric(5) ,
RetailPrice numeric(10) not null
);

Answer (2)

here in Purchase table ... using alter table adding foreign key

ALTER TABLE PURCHASE

ADD FOREIGN KEY (BuyerID)

references BUYER(BuyerID);

Here in Purchase_item table ... using alter table adding foreign key

ALTER TABLE PURCHASE_ITEM

ADD FOREIGN KEY(ItemNumber)

references ITEM(ItemNumber);

Answer (3)

insert into BUYER values (1,'Flintoff','Andrew','112221','a@mail.com');
insert into BUYER values (2,'Steve','Kettew','1332221','b@mail.com');
insert into BUYER values (3,'Joseph','Andrew','112221','c@mail.com');

insert into PURCHASE values (331,'1-jan-2014',2400,2);
insert into PURCHASE values (332,'11-jun-2013',1400,3);
insert into PURCHASE values (333,'1-jan-2001',5400,1);

insert into ITEM values(101,'Nokia211','3000','Nokia');

insert into ITEM values(121,'Sumsung211','23000','Welson');

insert into ITEM values(111,'iphone6','60000','iphone');

insert into PURCHASE_ITEM values (331,101,2800);

insert into PURCHASE_ITEM values (332,121,2600);

insert into PURCHASE_ITEM values (333,101,2800);

Ans 4 (a)

select a1.lastname,a1.firstname from buyer a1,purchase i1 where a1.BuyerID=i1.BuyerID and i1.pretaxamount > 200;

Ans 4(b)

select ItemNumber,Description from ITEM where Cost>100;

Ans 4(c)

select ItemNumber,Description from ITEM where Cost>100 and Companyname like '%son%';

Ans 4(d)

select lastname,firstname from buyer where BuyerID in (Select BuyerID from ITEM where Cost>50)

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.