Question
List the SQL to answer this query: For the store with the highest net profit, list the store id, store name, total sales, total commission,
List the SQL to answer this query: For the store with the highest net profit, list the store id, store name, total sales, total 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. Company 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%).
Based on:
Style
Styleno (PK) varchar(6) not null,
vendorid (FK) varchar(3),
deptid (FK) varchar(2),
sdesc varchar(20),
cost DECIMAL(5,2),
retail DECIMAL(5,2)
Region
Regioncode (PK) varchar(2) not null,
rdesc varchar(9)
Store
storeid (PK) varchar(2) not null,
storename varchar(15),
state varchar(2),
typecode (FK) varchar(1),
rent integer(4),
pctcredit integer(4),
regioncode (FK) varchar(2),
storesize integer(4));
Salesperson
Salesid (PK) varchar(2) not null,
sname varchar(20)
storeid varchar(2)
commission integer(2)
Typecode
typecode (PK) varchar(1) not null
typedesc varchar(12)
Vendor
vendorid (PK) varchar(3) not null
vname varchar(10)
vaddress varchar(15)
Department
deptid (PK) varchar(2) not null
deptname varchar(12)
Sale
weekno integer(1)
styleno (FK) varchar(6)
saleid (FK) varchar(2)
numsold integer(2)
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started