Tiny Tots, Inc. Case

create table style (

styleno varchar(6) not null,

vendorid varchar(3),

deptid varchar(2),

sdesc varchar(20),

cost DECIMAL(5,2),

retail DECIMAL(5,2));

create table region (

regioncode varchar(2) not null,

rdesc varchar(9));

create table store (

storeid varchar(2) not null,

storename varchar(15),

state varchar(2),

typecode varchar(1),

rent integer(4),

pctcredit integer(4),

regioncode varchar(2),

storesize integer(4));

create table salesperson (

salesid varchar(2) not null,

sname varchar(20),

storeid varchar(2),

commission integer(2));

create table typecode (

typecode varchar(1) not null,

typedesc varchar(12));

create table vendor (

vendorid varchar(3) not null,

vname varchar(10),

vaddress varchar(15));

create table department (

deptid varchar(2) not null,

deptname varchar(12));

create table sale (

weekno integer(1),

styleno varchar(6),

saleid varchar(2),

numsold integer(2));

Complete the following queries using the SQL code above:

a. For each style, list the style number, description, department name, vendor ID, vendor name, cost and retail. Sort the listing in style number, starting with the highest number.

b. For each boys' style (departments 00, 20 & 40), list the style number, description, and vendor name. Display the listing in alphabetical order by vendor name.

c. For each store, list the store ID, store name, state, region name, monthly rent, and re nt per square foot. Display the listing in alphabetical order by store name.

d. List the store ID, store name and monthly rent of all stores with monthly rent higher than the chain's average monthly rent. Display the listing from the store with the highest rent to the lowest.

e. For each region, list the region code, the number of mall stores and the number of strip center stores in the region. Display the listing in reverse alphabetical order by region code.

f. For each week, list the week number, the number of units sold and the total dollar sales for that week (number of garments sold * retail).

g. For each salesperson, list the sales ID, name, total dollar sales for the first month (weeks 1 - 4), and the total commission earned by the salesperson. Sort the listi ng in order from highest to lowest monthly sales.

h. For each style, list the department name, style number, style description, cost, retail, and gross profit margin (the difference between retail and cost divided by retail - expressed as a percentage, i.e. 4 7.38%). Display the listing in alphabetical order by department name.

i. For each vendor, list the vendor name, average cost, average retail, and average gross profit margin of styles provided by the vendor. Display the listing in alphabetical order by vendor name.

j. List the sales ID, name and total monthly dollar sales for each salesperson whose monthly sales were higher than the average salesperson's monthly sales. Display the listing in alphabetical order by salesperson name.

k. For each vendor that supplies T TI with more than 5 styles, list the vendors name, and average cost and average retail of the vendor's styles. Display the listing in alphabetical order by vendor name.

l. For each department, list the department number and total dollar sales. Display the listing in descending order by department number. m. For each store type, list the description, number of stores of that type and the average rent for that store type.

n. For the store with the highest net profit, list the store id, store name, total sales, tota l commission, rent, credit card fees, and net profit. (A stores net profit is computed as its total sales less cost of goods sold less monthly rent less commission owed to sales staff less credit card fees owed. TTI pays the credit card companies 3% of all credit card sales, so credit card fees owed is calculated as the stores total sales * the stores credit percent * 3%).

o. For the region with the highest net profit, list the region code, region description, net profit and the percent of overall corpora te profits represented by the region. (Round the percent to the nearest tenth percent).

p. For all sales staff who earned at least $50 in commission for the month, list the salespersons id, name, commission earned and the percentage of the chains total com mission. (Round the percent to the nearest tenth of a percent.) Display the listing starting with the best salesperson (most commission earned).

q. For all styles with at least $500 in sales and a gross margin of at least 46%, list the style number, descrip tion, vendor name, cost, retail, and gross margin. Display the listing in ascending order by retail. (Profit margin is the difference between retail and cost divided by retail expressed as a percentage.)

r. For the two infant departments combined, list the total infant profits and the percent of the chains profits represented by the infant departments rounded to the nearest hundredth of a percent. (Department profits are calculated as the total dollar sales less cost of goods sold less commission less cred it fees).