Paula Noone
PHPMyAdmin
SQL - Summer, 2009


#17 queries from Fruit and Inventory tables - First Part of Assignment

FRUIT TABLE


fruitID  name  price
      1 apple 2.50
      2 pear 3.00
      4 Grape 0.75
      8 pluot 4.50
      6 plum 1.25
      3 banana 1.00

INVENTORY TABLE


FruitID  Quantity
      1 100
      3 500
      2 100
      5 10
      1 250
      2 200



#1.  Show all tables in database

Your SQL query has been executed successfully
SHOW TABLES [ Edit ] [ Create PHP Code ]



Tables_in_DBpnoone0

Customer
Inventory
Order_line
Orders
Part
Rep
fruit


#2.  Use Distinct - eliminate duplications

SELECT DISTINCT fruitID
FROM Inventory
ORDER BY fruitID
LIMIT 0 , 30


 fruitID
      1
      2
      3
      5


#3. Show FruitID in Fruit and not in Inventory, order by FruitID

SELECT *
FROM `fruit`
WHERE fruitID NOT
IN (

SELECT DISTINCT fruitID
FROM Inventory
ORDER BY fruitID
)
LIMIT 0 , 30

fruitID  name  price
      4 Grape 0.75
      8 pluot 4.50
      6 plum 1.25


#4. Select FruitID from Inventory, Order by ID


SELECT fruitID
FROM Inventory
ORDER BY fruitID
LIMIT 0 , 30

fruitID
      1
      1
      2
      2
      3
      5

#5. Select all from Fruit where ID not in Inventory, elim dups in Inventory, order by ID

SELECT *
FROM `fruit`
WHERE fruitID NOT
IN (

SELECT DISTINCT fruitID
FROM Inventory
ORDER BY fruitID
)
LIMIT 0 , 30


fruitID  name  price
      4 Grape 0.75
      8 pluot 4.50
      6 plum 1.25



#6 Show fruitID, name, total from Inventory

SELECT f.fruitID, name, sum( quantity ) AS "Total Inventory"
FROM Inventory, fruit AS f
WHERE f.fruitID = Inventory.fruitID
GROUP BY Inventory.fruitID
LIMIT 0 , 30



fruitID  name  Total Inventory
1 apple 350
2 pear 300
3 banana 500


#7Show name, price x total quantity name value from Fruit and Inventory

SELECT fruit.name, fruit.price * sum( quantity ) AS value
FROM Inventory, fruit
WHERE fruit.fruitID = Inventory.fruitID
GROUP BY Inventory.fruitID
LIMIT 0 , 30

name  value
apple 875.00
pear 900.00
banana 500.00


#8Show all from Inventory and Fruit

SELECT *
FROM Inventory, fruit AS f
WHERE f.fruitID = Inventory.fruitID
LIMIT 0 , 30

FruitID  Quantity  fruitID  name  price
1 100 1 apple 2.50
3 500 3 banana 1.00
2 100 2 pear 3.00
1 250 1 apple 2.50
2 200 2 pear 3.00


#9Show Fruit ID, name, and quantity from Inventory and Fruit

SELECT f.fruitID, name, quantity
FROM Inventory, fruit AS f
WHERE f.fruitID = Inventory.fruitID
LIMIT 0 , 30

fruitID  name  quantity
1 apple 100
3 banana 500
2 pear 100
1 apple 250
2 pear 200


#10Select apples from Fruit, state are worth, add quantity and $ from Fruit and Inventory, group by ID

SELECT "apples are worth", concat( "$", sum( quantity ) * (

SELECT price
FROM fruit
WHERE name LIKE "apple"
) ) AS value
FROM Inventory
WHERE fruitID = (
SELECT fruitID
FROM fruit
WHERE name LIKE "apple" )
GROUP BY fruitID
LIMIT 0 , 30

apples are worth  value
apples are worth $875.00


#11Select all from Fruit, order by desc price

SELECT *
FROM fruit
ORDER BY price
LIMIT 0 , 30

fruitID  name  price
      4 Grape 0.75
      3 banana 1.00
      6 plum 1.25
      1 apple 2.50
      2 pear 3.00
      8 pluot 4.50


#12Select all from Fruit table

SELECT *
FROM fruit
LIMIT 0 , 30

fruitID  name  price
      1 apple 2.50
      2 pear 3.00
      4 Grape 0.75
      8 pluot 4.50
      6 plum 1.25
      3 banana 1.00


#13Select all from Inventory

SELECT *
FROM Inventory
LIMIT 0 , 30

FruitID  Quantity
      1 100
      3 500
      2 100
      5 10
      1 250
      2 200

#14Select FruitID, name, quantity from Inventory and Fruit tables where ID =

SELECT f.fruitID, name, quantity
FROM Inventory, fruit AS f
WHERE f.fruitID = Inventory.fruitID
LIMIT 0 , 30

fruitID  name  quantity
1 apple 100
3 banana 500
2 pear 100
1 apple 250
2 pear 200


#15Select all from Fruit where price is greater than 1.0

SELECT *
FROM fruit
WHERE price > 1.0
LIMIT 0 , 30


fruitID  name  price
      1 apple 2.50
      2 pear 3.00
      8 pluot 4.50
      6 plum 1.25




#16 Show tables

SHOW TABLES
LIMIT 0 , 30

Tables_in_DBpnoone0
Customer
Inventory
Order_line
Orders
Part
Rep
fruit


#17From Fruit and Inventory tables, name and Sum and group by fruitID

SELECT f.fruitID, name, sum( quantity )
FROM Inventory, fruit AS f
WHERE f.fruitID = Inventory.fruitID
GROUP BY Inventory.fruitID


fruitID  name  sum(quantity)
1 apple 350
2 pear 300
3 banana 500


SELECT *
FROM Inventory
LIMIT 0 , 30

FruitID  Quantity
      1 100
      3 500
      2 100
      5 10
      1 250
      2 200



PHPMyAdmin

SECOND PART OF ASSIGNMENT



a) Create three additional tables and add comments and relational view information to tables.

SELECT *
FROM `DeliveryCo`
LIMIT 0 , 30

  Deliver  City
      Nocal Delivery San Jose
      Socal Delivery Irvine

SELECT *
FROM `Grocerystore`
LIMIT 0 , 30


Store  City
      Albertson's San Jose
      Pavilon's Irvine

SELECT *
FROM `Vendor`
LIMIT 0 , 30


Farm  City
      Nocal Growers San Jose
      Socal Growers Irvine



b) Create a data dictionary and schema, including a relational view using designer tab.

Customer
Field Type Null Default Comments MIME
CUSTOMER_NUM  char(3) No
CUSTOMER_NAME  char(35) No
STREET  char(15) No
CITY  char(15) No
STATE  char(2) No
ZIP  char(5) No
BALANCE  decimal(8,2) No
CREDIT_LIMIT  decimal(8,2) No
REP_NUM  char(2) No

DeliveryCo
Field Type Null Default Comments MIME
Deliver  char(30) No
City  char(30) No

Grocerystore
Field Type Null Default Comments MIME
Store  char(30) No
City  char(30) No

Inventory
Field Type Null Default Links to Comments MIME
FruitID  tinyint(4) No  fruit -> fruitID Foreign key to Fruit table
Quantity  int(11) No   Shipment quantity

Order_line
Field Type Null Default Comments MIME
ORDER_NUM  char(5) No
PART_NUM  char(4) No
NUM_ORDERED  decimal(3,0) No
QUOTED_PRICE  decimal(6,2) No

Orders
Field Type Null Default Comments MIME
ORDER_NUM  char(5) No
ORDER_DATE  date No
CUSTOMER_NUM  char(3) No

Part
Field Type Null Default Comments MIME
PART_NUM  char(4) No
DESCRIPTION  char(15) No
ON_HAND  decimal(4,0) No
CLASS  char(2) No
WAREHOUSE  char(1) No
PRICE  decimal(6,2) No

Rep
Field Type Null Default Comments MIME
REP_NUM  char(2) No
LAST_NAME  char(15) No
FIRST_NAME  char(15) No
STREET  char(15) No
CITY  char(15) No
STATE  char(2) No
ZIP  char(5) No

Vendor
Field Type Null Default Comments MIME
Farm  char(30) No
City  char(30) No

fruit
Field Type Null Default Links to Comments MIME
fruitID  tinyint(11) No  fruit -> fruitID Primary key
name  varchar(8) Yes NULL  Fruit name
price  double(5,2) Yes NULL  Fruit price




c) Generate output showing contents of five tables.  I renamed these current five tables FruitCustomer, etc., because I had another table titled Customer.)


#FruitCustomer
SELECT *
FROM FruitCustomer;
LIMIT 0 , 30

 FruitCustomerID Primary key FruitCustomerName  FruitEmail  FruitCustomerPassword
      100 Minnie Mouse mmouse0@cim.saddleback.edu da39a3ee5e6b4b0d3255bfef95601890afd80709
      120 Joe Student jstudent0@cim.saddleback.edu da39a3ee5e6b4b0d3255bfef95601890afd80709
      127 Premiere premiere0@cim.saddleback.edu da39a3ee5e6b4b0d3255bfef95601890afd80709



#FruitOrder Table

SELECT *
FROM FruitOrder;

FruitOrderID Primary key FruitCustomerID Foreign Key to Customer FruitDate
      23 120 2008-08-30
      24 130 2008-09-01



#FruitOrderLine Table

SELECT *
FROM FruitOrderLine
LIMIT 0 , 30


FruitOrderID Foreign Key to Order FruitID Foreign key to Fruit table Quantity
      23 1 100
      23 2 50
      24 3 100
      24 5 20
      24 2 50



#Fruit Table

SELECT *
FROM fruit
LIMIT 0 , 30



fruitID Primary key name Fruit name price Fruit price
      1 apple 2.50
      2 pear 3.00
      4 grape 0.75
      8 pluot 4.50
      6 plum 1.25
      3 banana 1.00

#Inventory Table

SELECT *
FROM Inventory
LIMIT 0 , 30



FruitID Foreign key to Fruit table Quantity Shipment quantity
      1 100
      3 500
      2 100
      5 10
      1 250
      2 200






D)  COMPLETE LIST OF 27 QUERIES:



#1.  Show tables


SHOW TABLES;


Tables_in_DBpnoone0
Customer
DeliveryCo
FruitCustomer
FruitOrder
FruitOrderLine
Grocerystore
Inventory
OrderInvoice
Order_line
Orders
Part
Rep
Vendor
fruit


#2.  Select all orders

SELECT count( * )
FROM FruitOrder


count(*)
2



#3.  Create alias c from FruitCustomer table to simplify naming convention FruitCustomerName.  Retrieve customer names.

SELECT c.FruitCustomerName
FROM FruitCustomer AS c
LIMIT 0 , 30



FruitCustomerName
      Minnie Mouse
      Joe Student
      Premiere



#4.  Use alias c for FruitCustomerName, o as FruitOrder, join tables and select Customer and ID that are related in both tables


SELECT c.FruitCustomerName, c.FruitEmail
FROM FruitCustomer AS c, FruitOrder AS o
WHERE c.FruitCustomerID = o.FruitCustomerID
LIMIT 0 , 30


FruitCustomerName  FruitEmail
      Joe Student jstudent0@cim.saddleback.edu



#5.  Select Amt Ordered from FruitOrderline where FruitID = 1


SELECT sum( quantity ) AS "Amount Ordered"
FROM FruitOrderLine
WHERE FruitID =1



Amount Ordered
100



#6.  Query to display contents of Inventory

SELECT *
FROM Inventory
LIMIT 0 , 30


FruitID Foreign key to Fruit table Quantity Shipment quantity
      1 100
      3 500
      2 100
      5 10
      1 250
      2 200




#This displays the structure of Inventory

DESCRIBE Inventory




 Field  Type  Null  Key  Default  Extra
      FruitID tinyint(11) NO   NULL
      Quantity int(11) NO   NULL
 Check All / Uncheck All With selected:



#7.  Display FruitOrderLine contents, sort by fruitID

SELECT *
FROM FruitOrderLine
ORDER BY FruitID
LIMIT 0 , 30




  FruitOrderID Foreign Key to Order FruitID Foreign key to Fruit table Quantity
      23 1 100
      23 2 50
      24 2 50
      24 3 100
      24 5 20



#8.  Display Inventory contents, sort by fruitID

SELECT *
FROM Inventory
ORDER BY FruitID
LIMIT 0 , 30



  FruitID Foreign key to Fruit table Quantity Shipment quantity
      1 100
      1 250
      2 100
      2 200
      3 500
      5 10




#9.  Select name from FruitCustomer where customerID in FruitCustomer and FruitOrderline (ID not found in FruitOrder)

SELECT FruitCustomerName
FROM FruitCustomer, FruitOrderLine
WHERE FruitCustomerID
IN (
FruitCustomerID
)
LIMIT 0 , 30


 FruitCustomerName
      Minnie Mouse
      Joe Student
      Premiere
      Minnie Mouse
      Joe Student
      Premiere
      Minnie Mouse
      Joe Student
      Premiere
      Minnie Mouse
      Joe Student
      Premiere
      Minnie Mouse
      Joe Student
      Premiere




#10. Display fruit name and price in fruit table, sort by price


SELECT name, price
FROM fruit
ORDER BY price
LIMIT 0 , 30



name Fruit name price Fruit price
      grape 0.75
      banana 1.00
      plum 1.25
      apple 2.50
      pear 3.00
      pluot 4.50




#11. Select fruitID from orderLine where orderID = (select orderID from 'order', limit first record only

SELECT FruitID
FROM FruitOrderLine
WHERE FruitOrderID
IN (

SELECT FruitOrderID
FROM FruitOrder
)
LIMIT 1


FruitID Foreign key to Fruit table
      1





#12.  Select name as alias from Orderline and alias as fruit where Orderline and Fruit table have same ID's.

SELECT o.FruitID
FROM FruitOrderLine o, fruit AS f
WHERE o.FruitID = f.fruitID
LIMIT 0 , 30



FruitID
      1
      2
      3
      2



#13.  Count all records from Orderline

SELECT count( FruitID )
FROM FruitOrderLine

count

(FruitID)
5



#14.  Count all ID records from OrderLine, and limit output to first record.

SELECT count( * ) FruitOrderID
FROM FruitOrderLine
WHERE FruitOrderID = (
SELECT FruitOrderID
FROM FruitOrder
LIMIT 1 )


FruitOrderID
2



#15.  Display Fruit names not in Inventory

SELECT *
FROM `fruit`
WHERE fruitID NOT
IN (

SELECT DISTINCT fruitID
FROM Inventory
ORDER BY fruitID
)
LIMIT 0 , 30

fruitID Primary key name Fruit name price Fruit price
      4 grape 0.75
      8 pluot 4.50
      6 plum 1.25




#16.  Display the value of Apple in Inventory

SELECT name, fruit.price * Inventory.Quantity
FROM fruit, Inventory
WHERE fruit.fruitID = '1'
AND Inventory.fruitID = '1'
LIMIT 0 , 30



name Fruit name fruit.price * Inventory.Quantity
apple 250.00
apple 625.00



#17.  Value of @ fruit in Inventory - price*inventory.quantity

SELECT fruit.name, fruit.price * sum( quantity ) AS value
FROM Inventory, fruit
WHERE fruit.fruitID = Inventory.fruitID
GROUP BY Inventory.fruitID
LIMIT 0 , 30


name Fruit name value
apple 875.00
pear 900.00
banana 500.00



#This is one fruit

SELECT "apples are worth", concat( "$", sum( quantity ) * (

SELECT price
FROM fruit
WHERE name LIKE "apple"
) ) AS value
FROM Inventory
WHERE fruitID = (
SELECT fruitID
FROM fruit
WHERE name LIKE "apple" )
GROUP BY fruitID
LIMIT 0 , 30



apples are worth  value
apples are worth $875.00




#18.  Display FruitID, name and price sorted by FruitID

SELECT fruitID, name, price
FROM fruit
ORDER BY fruitID
LIMIT 0 , 30



fruitID Primary key name Fruit name price Fruit price
      1 apple 2.50
      2 pear 3.00
      3 banana 1.00
      4 grape 0.75
      6 plum 1.25
      8 pluot 4.50



#19.  Check all tables in database

CHECK TABLE `Customer` , `DeliveryCo` , `fruit` , `FruitCustomer` , `FruitOrder` , `FruitOrderLine` , `Grocerystore` , `Inventory` , `OrderInvoice` , `Orders` , `Order_line` , `Part` , `Rep` , `Vendor` [ Edit ] [ Create PHP Code ]


Table  Op  Msg_type  Msg_text
DBpnoone0.Customer check status OK
DBpnoone0.DeliveryCo check status OK
DBpnoone0.fruit check status OK
DBpnoone0.FruitCustomer check status OK
DBpnoone0.FruitOrder check status OK
DBpnoone0.FruitOrderLine check status OK
DBpnoone0.Grocerystore check status OK
DBpnoone0.Inventory check status OK
DBpnoone0.OrderInvoice check status OK
DBpnoone0.Orders check status OK
DBpnoone0.Order_line check status OK
DBpnoone0.Part check status OK
DBpnoone0.Rep check status OK
DBpnoone0.Vendor check status OK




#20.  Create Invoice Report for @ Order

SELECT *
FROM OrderInvoice
ORDER BY CustomerID
LIMIT 0 , 30


CustomerID  fruitID  name  Quantity  price
      120 1 apple 100 2.50
      120 3 banana 100 1.00
      120 4 grape 100 0.75
      120 4 grape 50 0.75
      120 8 pluot 100 4.50
      120 4 grape 100 0.75
      120 6 plum 50 1.25
      120 4 grape 20 0.75
      120 4 grape 50 0.75
      120 6 plum 20 1.25
      120 3 banana 50 1.00
      120 6 plum 100 1.25
      120 8 pluot 50 4.50
      120 8 pluot 100 4.50
      120 6 plum 50 1.25
      120 8 pluot 20 4.50
      120 8 pluot 50 4.50
      120 2 pear 50 3.00
      120 6 plum 100 1.25
      120 1 apple 50 2.50
      120 3 banana 50 1.00
      120 1 apple 100 2.50
      120 1 apple 20 2.50
      120 2 pear 100 3.00
      120 1 apple 50 2.50
      120 3 banana 20 1.00
      120 2 pear 100 3.00
      120 2 pear 50 3.00
      120 3 banana 100 1.00
      120 2 pear 20 3.00




#21.  Select OrderID from Order, limit to first record

SELECT fruitID
FROM `OrderInvoice`
LIMIT 1


fruitID
      1



#22. Select name, email with time (now) where ID = 23 from Order table

SELECT FruitCustomerName, FruitEmail, now( )
FROM FruitCustomer
WHERE FruitCustomerID = (
SELECT FruitCustomerID
FROM FruitOrder
WHERE FruitOrderID =23 )
LIMIT 0 , 30


FruitCustomerName  FruitEmail  now()
Joe Student jstudent0@cim.saddleback.edu 2009-07-13 15:51:47



#23.  Select records where ID = 23 from FruitOrder and FruitOrderLine



SELECT *
FROM FruitOrder, FruitOrderLine
WHERE FruitOrder.FruitOrderID = FruitOrderLine.FruitOrderID
AND FruitOrderLine.FruitOrderID =23
LIMIT 0 , 30


FruitOrderID Primary key FruitCustomerID Foreign Key to Customer FruitDate  FruitOrderID Foreign Key to Order FruitID Foreign key to Fruit table Quantity
23 120 2008-08-30 23 1 100
23 120 2008-08-30 23 2 50




#24.  Using alias, select ID, name, quantity, price from fruit and Orderline where ID = 23

SELECT f.fruitID, name, price, o.Quantity, price * o.Quantity
FROM fruit AS f, FruitOrderLine AS o
WHERE f.fruitID = o.FruitID
AND FruitOrderID =23
LIMIT 0 , 30


fruitID  name  price  Quantity  price * o.Quantity
1 apple 2.50 100 250.00
2 pear 3.00 50 150.00




#25.  Create View for OrderInvoice table

Create view of OrderInvoice table

CREATE VIEW OrderInvoice(
fruitID,
name,
quantity,
price,
ExtendedPrice
) AS SELECT f.fruitID, name, price, o.Quantity, price * o.Quantity
FROM fruit AS f, FruitOrderLine AS o
WHERE f.fruitID = o.FruitID
AND FruitOrderID =23;




#26.  Select name, Quantity concat ("$", price) as Unit Price, concat ("$" ExtendedPrice) as Extended Price from OrderInvoice2 (I set up another OrderInvoiceTable for View);

SELECT name, Quantity, concat( "$", price ) AS UnitPrice, concat( "$", ExtendedPrice ) AS ExtendedPrice
FROM OrderInvoice2
LIMIT 0 , 30



name Fruit name Quantity Fruit price UnitPrice  ExtendedPrice
apple 2.50 $100 $250.00
pear 3.00 $50 $150.00




#27.

SELECT sum( price )
FROM OrderInvoice2



sum(price)
150

SQL result
Host: localhost
Database: DBpnoone0
Generation Time: Jul 13, 2009 at 04:53 PM
Generated by: phpMyAdmin 3.1.3 / MySQL 5.0.51a
SQL query: SELECT sum(price) FROM OrderInvoice2;
Rows: 1

sum(price)
150

 
Re-Muliple Times - last attempt is recorded