TAL SQL Views - 1 (Games)

Using the TAL database, create a SQL view based on the following scenario: Francesca is interested in the item number, description, units of hand, and unit price for TAL Distributors items that are in category GME. Name the View Games. Create a SQL View in MySQL. Submit a screenshot of your statement. Make sure I can see the view listed in the Navigator Pane in the TAL Views subfolder.

TAL SQL Views - 2 (RepCust)

Using the TAL database, create a SQL view based on the following scenario: Francesca needs to know the number and name of each rep, along with the number and name of the customer represented by each rep. Create a singe table that contains the rep number, rep name, customer number and customer name. Name the view RepCust. Create a SQL View in MySQL. Submit a screenshot of your statement. Make sure I can see the view listed in the Navigator Pane in the TAL Views subfolder.

TAL SQL - Query View 2A (RepCust)

Create a query based on the REPCUST view and the following scenario. Suppose, Megan wants to know the name of the rep for customer 260. Submit a screenshot of your statement. Make sure I can see the query statement and table output in the result grid.

TAL SQL - Query View 2B (RepCust)

Create a query based on the REPCUST view and the following scenario. Suppose, Megan wants to create a list of reps by customer names alphabetically. Submit a screenshot of your statement. Make sure I can see the query statement and table output in the result grid.

TAL SQL - Query View 1 (Games)

Create a query based on the GAMES view and the following scenario. Suppose, Francesca wants to know which games have a quantity of 25 or less in stock. Submit a screenshot of your statement. Make sure I can see the query statement and table output in the result grid.

CREATE TABLE Rep(RepNum CHAR(2) PRIMARY KEY,LastName CHAR(15),FirstName CHAR(15),Street CHAR(15),City CHAR(15),State CHAR(2),PostalCode CHAR(5),Commission DECIMAL(7,2),Rate DECIMAL(3,2) );CREATE TABLE Customer(CustomerNum CHAR(3) PRIMARY KEY,CustomerName CHAR(35) NOT NULL,Street CHAR(20),City CHAR(15),State CHAR(2),PostalCode CHAR(5),Balance DECIMAL(8,2),CreditLimit DECIMAL(8,2),RepNum CHAR(2) );CREATE TABLE Orders(OrderNum CHAR(5) PRIMARY KEY,OrderDate DATE,CustomerNum CHAR(3) );CREATE TABLE Item(ItemNum CHAR(4) PRIMARY KEY,Description CHAR(30),OnHand DECIMAL(4,0),Category CHAR(3),Storehouse CHAR(1),Price DECIMAL(6,2) );CREATE TABLE OrderLine(orderNum CHAR(5),ItemNum CHAR(4),NumOrdered DECIMAL(3,0),QuotedPrice DECIMAL(6,2),PRIMARY KEY (OrderNum, ItemNum) );INSERT INTO REPVALUES

('15','Campos','Rafael','724 Vinca Dr.','Grove','CA','90092',23457.50,0.06);INSERT INTO REPVALUES('30','Gradey','Megan','632 Liatris St.','Fullton','CA','90085',41317.00,0.08);INSERT INTO REPVALUES('45','Tian','Hui','1785 Tyler Ave.','Northfield','CA','90098',27789.25,0.06);INSERT INTO REPVALUES('60','Sefton','Janet','267 Oakley St.','Congaree','CA','90097',0.00,0.06);INSERT INTO CUSTOMERVALUES('126','Toys Galore','28 LaketonSt.','Fullton','CA','90085',1210.25,7500.00,'15');INSERT INTO CUSTOMERVALUES('260','Brookings Direct','452 ColumbusDr.','Grove','CA','90092',575.00,10000.00,'30');INSERT INTO CUSTOMERVALUES('334','The Everything Shop','342 MageeSt.','Congaree','CA','90097',2345.75,7500.00,'45');INSERT INTO CUSTOMERVALUES('386','Johnson''s Department Store','124 Main

St.','Northfield','CA','90098',879.25,7500.00,'30');INSERT INTO CUSTOMERVALUES('440','Grove Historical Museum Store','3456 CentralAve.','Fullton','CA','90085',345.00,5000.00,'45');INSERT INTO CUSTOMERVALUES('502','Cards and More','167 HaleSt.','Mesa','CA','90104',5025.75,5000.00,'15');INSERT INTO CUSTOMERVALUES('586','Almondton General Store','3345 DevonAve.','Almondton','CA','90125',3456.75,15000.00,'45');INSERT INTO CUSTOMERVALUES('665','Cricket Gift Shop','372 OxfordSt.','Grove','CA','90092',678.90,7500.00,'30');INSERT INTO CUSTOMERVALUES('713','Cress Store','12 Rising SunAve.','Congaree','CA','90097',4234.60,10000.00,'15');INSERT INTO CUSTOMERVALUES('796','Unique Gifts','786 PassmoreSt.','Northfield','CA','90098',124.75,7500.00,'45');INSERT INTO CUSTOMERVALUES('824','Kline''s','945 Gilham St.','Mesa','CA','90104',2475.99,15000.00,'30');INSERT INTO CUSTOMERVALUES('893','All Season Gifts','382 WildwoodAve.','Fullton','CA','90085',935.75,7500.00,'15');INSERT INTO ORDERSVALUES('51608','10-12-2015','126');INSERT INTO ORDERSVALUES('51610','10-12-2015','334');INSERT INTO ORDERSVALUES('51613','10-13-2015','386');INSERT INTO ORDERSVALUES('51614','10-13-2015','260');INSERT INTO ORDERSVALUES('51617','10-15-2015','586');INSERT INTO ORDERSVALUES('51619','10-15-2015','126');INSERT INTO ORDERSVALUES('51623','10-15-2015','586');INSERT INTO ORDERSVALUES('51625','10-16-2015','796');INSERT INTO ITEMVALUES('AH74','Patience',9.00,'GME','3',22.99);INSERT INTO ITEMVALUES('BR23','Skittles',21.00,'GME','2',29.99);INSERT INTO ITEMVALUES

('CD33','Wood Block Set (48 piece)',36.00,'TOY','1',89.49);INSERT INTO ITEMVALUES('DL51','Classic Railway Set',12.00,'TOY','3',107.95);INSERT INTO ITEMVALUES('DR67','Giant Star Brain Teaser',24.00,'PZL','2',31.95);INSERT INTO ITEMVALUES('DW23','Mancala',40.00,'GME','3',50.00);INSERT INTO ITEMVALUES('FD11','Rocking Horse',8.00,'TOY','3',124.95);INSERT INTO ITEMVALUES('FH24','Puzzle Gift Set',65.00,'PZL','1',38.95);INSERT INTO ITEMVALUES('KA12','Cribbage Set',56.00,'GME','3',75.00);INSERT INTO ITEMVALUES('KD34','Pentominoes Brain Teaser',60.00,'PZL','2',14.95);INSERT INTO ITEMVALUES('KL78','Pick Up Sticks',110.00,'GME','1',10.95);INSERT INTO ITEMVALUES('MT03','Zauberkasten Brain Teaser',45.00,'PZL','1',45.79);INSERT INTO ITEMVALUES('NL89','Wood Block Set (62 piece)',32.00,'TOY','3',119.75);INSERT INTO ITEMVALUES('TR40','Tic Tac Toe',75.00,'GME','2',13.99);INSERT INTO ITEMVALUES('TW35','Fire Engine',30.00,'TOY','2',118.95);INSERT INTO OrderLineVALUES('51608','CD33',5.00,86.99);INSERT INTO OrderLineVALUES('51610','KL78',25.00,10.95);INSERT INTO OrderLineVALUES('51610','TR40',10.00,13.99);INSERT INTO OrderLineVALUES('51613','DL51',5.00,104.95);INSERT INTO OrderLineVALUES('51614','FD11',1.00,124.95);INSERT INTO OrderLineVALUES('51617','NL89',4.00,115.99);INSERT INTO OrderLineVALUES('51617','TW35',3.00,116.95);INSERT INTO OrderLineVALUES('51619','FD11',2.00,121.95);INSERT INTO OrderLineVALUES('51623','DR67',5.00,29.95);

INSERT INTO OrderLineVALUES('51623','FH24',12.00,36.95);INSERT INTO OrderLineVALUES('51623','KD34',10.00,13.10);INSERT INTO OrderLineVALUES('51625','MT03',8.00,45.79);