SophiaPretty

(5)

$14/per page/Negotiable

About SophiaPretty

Levels Tought:
Elementary,Middle School,High School,College,University,PHD

Expertise:
Accounting,Algebra See all
Accounting,Algebra,Applied Sciences,Architecture and Design,Art & Design,Biology,Business & Finance,Calculus,Chemistry,Communications,Computer Science,Economics,Engineering,English,Environmental science,Essay writing Hide all
Teaching Since: Jul 2017
Last Sign in: 313 Weeks Ago, 6 Days Ago
Questions Answered: 15833
Tutorials Posted: 15827

Education

  • MBA,PHD, Juris Doctor
    Strayer,Devery,Harvard University
    Mar-1995 - Mar-2002

Experience

  • Manager Planning
    WalMart
    Mar-2001 - Feb-2009

Category > Computer Science Posted 06 Jan 2018 My Price 7.00

maximum price of any item ordered in the items

Aggregation Part of the Exercise：

1. Select the maximum price of any item ordered in the items_ordered table. Hint: Select the maximum price only.

2. Select the average price of all of the items ordered that were purchased in the month of Dec.

3.What is the total number of rows in the items_ordered table?

4.For all of the tents that were ordered in the items_ordered table, what is the price of the lowest tent? Hint: Your query should return the price only.

Group By Exercises：

5.How many people are in each unique state in the customers table? Select the state and display the number of people in each. Hint:countis used to count rows in a column,sumworks on numeric data only.

6.From the items_ordered table, select the item, maximum price, and minimum price for each specific item in the table. Hint: The items will need to be broken up into separate groups.

7.How many orders did each customer make? Use the items_ordered table. Select the customerid, number of orders they made, and the sum of their orders.

Having Clause Exercise：

8.How many people are in each unique state in the customers table that have more than one person in the state? Select the state and display the number of how many people are in each if it's greater than 1.

9. From the items_ordered table, select the item, maximum price, and minimum price for each specific item in the table. Only display the results if the maximum price for one of the items is greater than 190.00.

10. How many orders did each customer make? Use the items_ordered table. Select the customerid, number of orders they made, and the sum of their orders if they purchased more than 1 item.

Ordered By Exercises：

11.Select the lastname, firstname, and city for all customers in the customers table. Display the results in Ascending Order based on the lastname.

12.Same thing as exercise #1, but display the results in Descending order.

13.Select the item and price for all of the items in the items_ordered table that the price is greater than 10.00. Display the results in Ascending order based on the price.

IN an BETWEEN Exercises：

14.Select the date, item, and price from the items_ordered table for all of the rows that have a price value ranging from 10.00 to 80.00.

15.Select the first name, city, and state from the customers table for all of the rows where the state value is either: Arizona, Washington, Oklahoma, Colorado, or Hawaii.


CREATE TABLE  Customer
(

custID          VARCHAR(20),
fname           VARCHAR(20),
lname            VARCHAR(20),
city                 VARCHAR(15),
state              VARCHAR(15),
CONSTRAINT   customer_PK PRIMARY KEY (custID)

);


INSERT INTO Customer (custID, fname, lname, city, state)
                            Values ("10101" , "John" ,"Gray", "Lynden", "Washington");

INSERT INTO Customer (custID, fname, lname, city, state)
                            Values ("10298" , "Leroy", "Brown", "Pinetop", "Arizona");

INSERT INTO Customer (custID, fname, lname, city, state)
                            Values ("10299" , "Elroy", "Keller", "Snoqualmie", "Washington");

INSERT INTO Customer (custID, fname, lname, city, state)
                            Values ("10315" , "Lisa", "Jones", "Oshkosh", "Wisconsin");

INSERT INTO Customer (custID, fname, lname, city, state)
                            Values ("10325" , "Ginger", "Schultz", "Pocatello", "Idaho");

INSERT INTO Customer (custID, fname, lname, city, state)
                            Values ("10329" , "Kelly", "Mendoza" "Kailua", "Hawaii");

INSERT INTO Customer (custID, fname, lname, city, state)
                            Values ("10330" , "Shawn", "Dalton", "Cannon Beach", "Oregon");

INSERT INTO Customer (custID, fname, lname, city, state)
                            Values ("10338" , "Michael", "Howell", "Tillamook", "Oregon");

INSERT INTO Customer (custID, fname, lname, city, state)
                            Values ("10339" , "Anthony", "Sanchez", "Winslow", "Arizona");

INSERT INTO Customer (custID, fname, lname, city, state)
                            Values ("10408" , "Elroy", "Cleaver", "Globe", "Arizona");

INSERT INTO Customer (custID, fname, lname, city, state)
                            Values ("10410" , "Mary Ann", "Howell", "Charleston", "South Carolina");

INSERT INTO Customer (custID, fname, lname, city, state)
                            Values ("10413" , "Donald", "Davids", "Gila Bend", "Arizona");

INSERT INTO Customer (custID, fname, lname, city, state)
                            Values ("10419" , "Linda", "Sakahara", "Sakahara", "Arizona");

INSERT INTO Customer (custID, fname, lname, city, state)
                            Values ("10429" , "Sarah", "Graham", "Greensboro", "North Carolina");

INSERT INTO Customer (custID, fname, lname, city, state)
                            Values ("10438" , "Kevin", "Smith", "Durango", "Colorado");

INSERT INTO Customer (custID, fname, lname, city, state)
                            Values ("10449" , "Isabela", "Moore", "Yuma", "Yuma");

INSERT INTO Customer (custID, fname, lname, city, state)
                            Values ("10439" , "Conrad", "Giles", "Telluride", "Colorado");


CREATE TABLE Orders
(

orderNum     INT AUTO_INCREMENT,
custID            VARCHAR(5),
Date_ordered       DATE,
item                VARCHAR(20),
quantity           INT,
price                DOUBLE,
 CONSTRAINT orders_PK PRIMARY KEY (orderNum),
  CONSTRAINT orders_FK1 FOREIGN KEY (custID) REFERENCES Customer(custID)

);
                           
                           
                           
                           
                           
INSERT INTO Orders (custID, date_ordered, item, quantity, price)
                  VALUES  ("10330", "1999-6-30", "Pogo stick", 1,28.00);
                
INSERT INTO Orders (custID, date_ordered, item, quantity, price)
                  VALUES  ("10101", "1999-6-30", "Raft", 1,58.00);

INSERT INTO Orders (custID, date_ordered, item, quantity, price)
                  VALUES  ("10298", "1999-6-30", "Skateboard", 1, 33.00);

INSERT INTO Orders (custID, date_ordered, item, quantity, price)
                  VALUES  ("10101", "1999-7-01", "Life Vest", 4, 125.00);

INSERT INTO Orders (custID, date_ordered, item, quantity, price)
                  VALUES  ("10299", "1999-7-06", "Parachute", 1, 1250.00);

INSERT INTO Orders (custID, date_ordered, item, quantity, price)
                  VALUES  ("10339", "1999-7-27", "Umbrella", 1, 4.50);



INSERT INTO Orders (custID, date_ordered, item, quantity, price)
                  VALUES  ("10449", "1999-8-13", "Unicycle", 1, 180.79);

INSERT INTO Orders (custID, date_ordered, item, quantity, price)
                  VALUES  ("10439", "1999-8-14", "Ski Poles", 2, 25.50);

INSERT INTO Orders (custID, date_ordered, item, quantity, price)
                  VALUES  ("10101", "1999-8-18", "Rain Coat", 1, 18.30);

INSERT INTO Orders (custID, date_ordered, item, quantity, price)
                  VALUES  ("10449", "1999-9-01", "Snow Shoes", 1, 45.00);

INSERT INTO Orders (custID, date_ordered, item, quantity, price)
                  VALUES  ("10298", "1999-9-19", "Lantern", 2, 29.00);

INSERT INTO Orders (custID, date_ordered, item, quantity, price)
                  VALUES  ("10410", "1999-10-28", "Sleeping Bag", 1, 89.22);

INSERT INTO Orders (custID, date_ordered, item, quantity, price)
                  VALUES  ("10410", "1999-11-01", "Umbrella", 1, 6.75);

INSERT INTO Orders (custID, date_ordered, item, quantity, price)
                  VALUES  ("10438", "1999-11-02", "Pillow", 1, 8.50);

INSERT INTO Orders (custID, date_ordered, item, quantity, price)
                  VALUES  ("10298", "1999-12-01", "Helmet", 1, 22.00);

INSERT INTO Orders (custID, date_ordered, item, quantity, price)
                  VALUES  ("10449", "1999-12-15", "Bicycle", 1, 380.50);

INSERT INTO Orders (custID, date_ordered, item, quantity, price)
                  VALUES  ("10449", "1999-12-22", "Canoe", 1, 280.00);

INSERT INTO Orders (custID, date_ordered, item, quantity, price)
                  VALUES  ("10101", "1999-12-30", "Hoola Hoop", 3, 14.75);



INSERT INTO Orders (custID, date_ordered, item, quantity, price)
                  VALUES  ("10330", "2000-1-01", "Flashlight", 4, 28.00);

INSERT INTO Orders (custID, date_ordered, item, quantity, price)
                  VALUES  ("10101", "2000-1-02", "Lantern", 1, 16.00);

INSERT INTO Orders (custID, date_ordered, item, quantity, price)
                  VALUES  ("10299", "2000-1-18", "Inflatable Mattress", 1, 38.00);

INSERT INTO Orders (custID, date_ordered, item, quantity, price)
                  VALUES  ("10438", "2000-1-18", "Tent", 1, 79.99);

INSERT INTO Orders (custID, date_ordered, item, quantity, price)
                  VALUES  ("10413", "2000-1-19", "Lawn chair", 4, 32.00);

INSERT INTO Orders (custID, date_ordered, item, quantity, price)
                  VALUES  ("10410", "2000-1-30", "Unicycle", 1, 192.50);

INSERT INTO Orders (custID, date_ordered, item, quantity, price)
                  VALUES  ("10315", "2000-2-02", "Compass", 1, 8.00);

INSERT INTO Orders (custID, date_ordered, item, quantity, price)
                  VALUES  ("10449", "2000-2-29", "Flashlight", 1, 4.50);

INSERT INTO Orders (custID, date_ordered, item, quantity, price)
                  VALUES  ("10101", "2000-3-08", "Sleeping Bag", 2, 88.70);

INSERT INTO Orders (custID, date_ordered, item, quantity, price)
                  VALUES  ("10298", "2000-3-18", "Pocket Knife", 1, 22.38);



INSERT INTO Orders (custID, date_ordered, item, quantity, price)
                  VALUES  ("10298", "2000-3-18", "Pocket Knife", 1, 22.38);



INSERT INTO Orders (custID, date_ordered, item, quantity, price)
                  VALUES  ("10449", "2000-3-19", "Canoe Paddle", 2, 40.00);

INSERT INTO Orders (custID, date_ordered, item, quantity, price)
                  VALUES  ("10298", "2000-4-01", "Ear Muffs", 1, 12.50);

INSERT INTO Orders (custID, date_ordered, item, quantity, price)
                  VALUES  ("10330", "2000-4-19", "Shovel", 1, 16.75);

                       

Answers

SophiaPretty
(5)
Status NEW Posted 06 Jan 2018 06:01 AM My Price 7.00

-----------  ----------- H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly

Not Rated(0)