Question

QUESTION 1

For the salesdb tables loaded in your environment, the table specifications are below:

CUSTOMER (custno, lname, fname, mname, email, mobile, addrln1, addrln2, addrln3, city, state, zip, status)

PRODUCT (prodno, sku, unit, long_desc, short_desc, unitprice, qty_on_hand, qty_on_order)

SALES (salesno, saledate, custno, prodno, salesqty, unitprice, lineamount, taxable, taxrate, discountrate)

Write SQL that will list the sales to custno=11, Eva Santis. The resultset should include:

custno, fname, lname, salesno, prodno, long_desc, product unitprice, salesqty, totalcost for each line item.

Test your SQL and upload the SQL to this question.

QUESTION 2

For the salesdb tables loaded in your environment, the table specifications are below:

CUSTOMER (custno, lname, fname, mname, email, mobile, addrln1, addrln2, addrln3, city, state, zip, status)

PRODUCT (prodno, sku, unit, long_desc, short_desc, unitprice, qty_on_hand, qty_on_order)

SALES (salesno, saledate, custno, prodno, salesqty, unitprice, lineamount, taxable, taxrate, discountrate)

Write SQL that will create a mailing list for a flyer that will be sent to residents of Harlem. The resultset should include:

fname, lname, addrln1, addrln2, addrln3, city, state, zip

Test your SQL and upload the SQL to this question.

QUESTION 3

For the salesdb tables loaded in your environment, the table specifications are below:

CUSTOMER (custno, lname, fname, mname, email, mobile, addrln1, addrln2, addrln3, city, state, zip, status)

PRODUCT (prodno, sku, unit, long_desc, short_desc, unitprice, qty_on_hand, qty_on_order)

SALES (salesno, saledate, custno, prodno, salesqty, unitprice, lineamount, taxable, taxrate, discountrate)

Write SQL that will list the SKU, and LONG_DESC of items where the quantity on hand is 20 items or less so that it can be ordered from the supplier. The resultset should include:

SKU, UNIT, LONG_DESC, QTY_ON_HAND

Test your SQL and upload the SQL to this question.

QUESTION 4

For the salesdb tables loaded in your environment, the table specifications are below:

CUSTOMER (custno, lname, fname, mname, email, mobile, addrln1, addrln2, addrln3, city, state, zip, status)

PRODUCT (prodno, sku, unit, long_desc, short_desc, unitprice, qty_on_hand, qty_on_order)

SALES (salesno, saledate, custno, prodno, salesqty, unitprice, lineamount, taxable, taxrate, discountrate)

Write SQL that will report the total sales amount for all the data in the sales table. The resultset should include:

SUM of unitprice from the product table times sales quantity from the sales table.

Note: relate the prodno of SALES with prodno of PRODUCT.

Test your SQL and upload the SQL to this question.

/*salesdb Exercise to create salesdb

Connect to MySQL using your root account. In MySQL, we only use root account which has all the privileges.
*/

create database salesdb;

/* Set default schema to salesdb, then create the following tables: */

use salesdb;
create table customer
( custno int(9)
, lname varchar(30)
, fname varchar(30)
, mname varchar(30)
, email varchar(35)
, mobile varchar(10)
, addrln1 varchar(30)
, addrln2 varchar(30)
, addrln3 varchar(30)
, city varchar(30)
, state varchar(2)
, zip varchar(10)
, status varchar(10)
);

alter table customer add primary key (custno);
create unique index cust_ix on customer (email);

create table product
( prodno int(9)
, sku varchar(10)
, unit varchar(10)
, long_desc varchar(30)
, short_desc varchar(15)
, unitprice decimal(16,2)
, qty_on_hand int(9)
, qty_on_order int(9)
);

alter table product add primary key (prodno);
create unique index prod_ix on product (sku);

create table sales
(
salesno    int(9)
,saledate timestamp
,custno       int(9)
,prodno       int(9)
,salesqty   int(16)
,unitprice decimal(16,2)
,lineamount decimal(16,2)
,taxable   char(1)
,taxrate   decimal(5,2)
,discountrate decimal(5,2)
);

alter table sales add primary key (salesno);
use salesdb;
alter table sales add constraint sales_cust foreign key (custno) references customer (custno);
use salesdb;
alter table sales add constraint sales_prod foreign key (prodno) references product (prodno);

/*
Inserting data
*/

insert into customer
( custno
, lname
, fname
, mname
, email
, mobile
, addrln1
, addrln2
, addrln3
, city
, state   
, zip   
, status
) values
(1,'Doe','John','M','[email protected]','2125551212','1 Main St.','Apt 1','','New York','NY','10000','A');

insert into customer
values
(2,'Doe','Ann','F','[email protected]','2125551313','2 Main St.','Apt 2','','New York','NY','10000','A');

insert into customer
values
(3,'Smith','Bill','A','[email protected]','2125551413','4 Grove St.','','','Brooklyn','NY','11231','A');

insert into customer
values
(4,'Consuelo','Cristina','A','[email protected]','7185551213','543 86 St.','1F','','Brooklyn','NY','11209','A');

insert into customer
values
(5,'Chu','Coney','C','[email protected]','7185551555','43 72 Ave.','3F','','Woodbridge','NY','11101','A');

insert into customer
values
(6,'James','Bob','L','[email protected]','7185552255','580 Smith Ave.','','','Floral Park','NY','11040','A');

insert into customer
values
(7,'Brick','Gib','B','[email protected]','7185556655','25 Delaware Ave.','','','Floral Park','NY','11040','A');

insert into customer
values
(8,'Hussein','Jimmy','B','[email protected]','7185557775','300 Fields Ave.','','','Rockaway','NY','11430','A');

insert into customer
values
(9,'Jamal','Joey','J','[email protected]','718555875','34 89th Ave.','','','Rockaway','NY','11430','A');

insert into customer
values
(10,'Desantis','John','J','[email protected]','2125554445','544 152th St.','','','Harlem','NY','10032','A');

insert into customer
values
(11,'Santis','Eva','J','[email protected]','2125555451','644 152th St.','','','Harlem','NY','10032','A');

insert into customer
values
(12,'Yates','Tony','T','[email protected]','2125552221','663 148th St.','','','Harlem','NY','10032','A');

insert into product
( prodno
, sku   
, unit
, long_desc
, short_desc
, unitprice
, qty_on_hand
, qty_on_order
)
values
(1,'123-000-10','QTY','Stainless Steel 9in Pan w/ Lid','SS 9in Pan wLid',35.50,10,0);
insert into product
values
(2,'150-020-20','QTY','8 oz Glass Tall','8 oz Glass Tall',2.50,100,0);
insert into product
values
(3,'160-030-10','QTY','5 oz Glass Short','5 oz Glass Sh',2.50,100,0);
insert into product
values
(5,'150-010-20','QTY','Place Mat','Place Mat',12.75,200,0);
insert into product
values
(17,'170-050-20','QTY','Flower Vase','Vase',15.25,10,0);
insert into product
values
(21,'210-000-20','QTY','Long Broom','L Broom',8.50,20,0);
insert into product
values
(22,'220-000-21','QTY','Vacuum Cleaner','Vac Cl',55.00,10,0);
insert into product
values
(23,'230-000-23','QTY','Table Lamp','Tab Lamp',35.00,20,0);
insert into product
values
(24,'240-000-24','QTY','Ligh Bulbs','Light Bl',5.00,20,0);
insert into product
values
(25,'250-000-25','QTY','Wall Clock','Wall Bl',15.00,10,0);
insert into product
values
(29,'290-000-01','BOX','24 Piece Dining Set','24 Pc DN Set',85.00,24,0);
insert into product
values
(30,'300-000-02','DOZ','Cloth Rose Flowers','Cloth Rose',85.00,24,0);
insert into product
values
(101,'600-101-01','SET','Combination Box Wrench','Box Wr Comb',65.00,100,0);
insert into product
values
(102,'600-102-01','SET','Screwdriver Set','ScrwDrvr Set',35.00,100,0);
insert into product
values
(103,'600-103-01','SET','Pliers Set','Pliers Set',25.00,100,0);
insert into product
values
(150,'600-150-01','BOX','2 Inch Nails','2in Nails',7.00,1000,0);
insert into product
values
(151,'600-151-01','BOX','2 Inch Drywall Screws','2in Dryw Scrw',7.00,1000,0);
insert into product
values
(152,'600-152-01','BOX','1 Inch Nails','1in Nails',7.00,1000,0);
insert into product
values
(153,'600-153-01','BOX','1 Inch Drywall Screws','1in Dryw Scrw',7.00,1000,0);
insert into product
values
(2001,'700-200-01','QTY','Large Down Pillow','Dwn Pillow LG',16.00,100,0);
insert into product
values
(2002,'700-200-02','QTY','Medium Down Pillow','Dwn Pillow MD',14.00,100,0);
insert into product
values
(2003,'700-200-03','QTY','Small Down Pillow','Dwn Pillow SM',12.00,100,0);
insert into product
values
(2011,'700-201-01','QTY','Large Pillow Case','Pillow Cs LG',11.00,100,0);
insert into product
values
(2012,'700-201-02','QTY','Medium Pillow Case','Pillow Cs MD',10.00,100,0);
insert into product
values
(2013,'700-201-03','QTY','Small Pillow Case','Pillow Cs SM',9.00,100,0);

insert into sales
values (1,'2020-10-15',1,1,1,0,0,'Y',8.25,0);
insert into sales
values (2,'2020-10-15',1,2,6,0,0,'Y',8.25,0);
insert into sales
values (3,'2020-10-15',1,25,1,0,0,'Y',8.25,0);
insert into sales
values (4,'2020-10-15',1,5,1,0,0,'Y',8.25,0);
insert into sales
values (5,'2020-10-14',6,29,1,0,0,'Y',8.25,0);
insert into sales
values (6,'2020-10-14',6,22,1,0,0,'Y',8.25,0);
insert into sales
values (7,'2020-10-15',2,21,1,0,0,'Y',8.25,0);
insert into sales
values (8,'2020-10-15',2,5,1,0,0,'Y',8.25,0);
insert into sales
values (9,'2020-10-14',10,102,1,0,0,'Y',8.25,0);
insert into sales
values (10,'2020-10-14',10,103,1,0,0,'Y',8.25,0);
insert into sales
values (11,'2020-10-14',10,150,1,0,0,'Y',8.25,0);
insert into sales
values (12,'2020-10-14',10,24,2,0,0,'Y',8.25,0);
insert into sales
values (13,'2020-10-14',10,153,4,0,0,'Y',8.25,0);
insert into sales
values (14,'2020-10-12',12,24,4,0,0,'Y',8.25,0);
insert into sales
values (15,'2020-10-12',12,23,4,0,0,'Y',8.25,0);
insert into sales
values (16,'2020-10-15',11,29,1,0,0,'Y',8.25,0);
insert into sales
values (17,'2020-10-15',11,22,1,0,0,'Y',8.25,0);
insert into sales
values (18,'2020-10-15',11,24,1,0,0,'Y',8.25,0);
insert into sales
values (19,'2020-10-12',12,5,1,0,0,'Y',8.25,0);
insert into sales
values (20,'2020-10-11',3,2001,1,0,0,'Y',8.25,0);
insert into sales
values (21,'2020-10-11',3,2011,1,0,0,'Y',8.25,0);
insert into sales
values (22,'2020-10-11',3,3,6,0,0,'Y',8.25,0);
insert into sales
values (23,'2020-10-11',3,2,6,0,0,'Y',8.25,0);
insert into sales
values (24,'2020-10-11',10,22,1,0,0,'Y',8.25,0);
insert into sales
values (25,'2020-10-11',10,24,1,0,0,'Y',8.25,0);
insert into sales
values (26,'2020-10-11',10,101,1,0,0,'Y',8.25,0);
insert into sales
values (27,'2020-10-11',10,103,1,0,0,'Y',8.25,0);
insert into sales
values (28,'2020-10-11',10,102,1,0,0,'Y',8.25,0);
insert into sales
values (29,'2020-10-11',9,17,1,0,0,'Y',8.25,0);
insert into sales
values (30,'2020-10-11',9,30,1,0,0,'Y',8.25,0);

Answer 1

Answer 1:

You may see some calculations done for the totalcost. To understand how that is done please go through below formulas:

Net Price is the tag price or list price before any sales taxes are applied.

Total Price is the final amount paid including sales tax.

Sales Tax Calculations:

• Sales Tax Amount = Net Price x (Sales Tax Percentage / 100)
• Total Price = Net Price + Sales Tax Amount

SQL Query

select c.custno, c.fname, c.lname, s.salesno, p.prodno, p.long_desc,  p.unitprice, s.salesqty, (p.unitprice + (p.unitprice * (s.taxrate/100))) as totalcost
from customer c,
     product p,
     sales s
where c.custno = s.custno
and s.prodno = p.prodno
and c.custno = 11;

Output

Answer 2:

SQL Query

select fname, lname, addrln1, addrln2, addrln3, city, state, zip from customer where city = 'Harlem';

Output:

Answer 3:

SQL Query

select sku, unit, long_desc, qty_on_hand from product where qty_on_hand <= 20;

Output

Answer 4:

SQL Query

select sum(p.unitprice * s.salesqty) from product p, sales s
where p.prodno = s.prodno;

Output

All the queries were pretty much straight forward apart from answer1 all other just have single one where condition to filter the data.

Note: To join one table to another we check for equality between the primary key of one table and foreign key of another table.

Thanks for providing the data, it made the task easy for me.

Enjoy!!!!!!!!!!!