SQ-HAL Test Results

###############################################################################
# SELECT * FROM table
#====================
> show me all suppliers
SELECT *
FROM suppliers

> tell me all our suppliers
SELECT *
FROM suppliers

> display all merchandise
SELECT *
FROM merchandise

> list all our merchandise
SELECT *
FROM merchandise

> who are our suppliers?
SELECT *
FROM suppliers

> what is our merchandise?
SELECT *
FROM merchandise


# SELECT DISTINCT field FROM table
#=================================
> tell me our suppliers' cost
What are your trying to say?

> display suppliers' address
SELECT DISTINCT address
FROM suppliers

> list all our merchandise costs
SELECT DISTINCT cost
FROM merchandise

> what are our suppliers' names?
SELECT DISTINCT name
FROM suppliers

> show me the suppliers of the merchandise
SELECT DISTINCT supplier
FROM merchandise

> what are the addresses of our suppliers?
SELECT DISTINCT address
FROM suppliers

> what are the addresses of our merchandise?
Could you rephrase that?


# SELECT DISTINCT field1, field2 FROM table
#==========================================
> tell me our suppliers' names and addresses
SELECT DISTINCT name, address
FROM suppliers

> display suppliers' address and names
SELECT DISTINCT address, name
FROM suppliers

> list all our merchandise costs and quantities
SELECT DISTINCT cost, quantity
FROM merchandise

> what are our suppliers' names and address?
SELECT DISTINCT name, address
FROM suppliers

> what are our suppliers' names and quantities?
What are your trying to say?

> show me the suppliers and cost of the merchandise
SELECT DISTINCT supplier, cost
FROM merchandise


# SELECT * FROM table WHERE condition
#====================================
> show me all suppliers whose names are "ABC XYZ"
SELECT *
FROM suppliers
WHERE name = "abc xyz"

> tell me our suppliers with the telephone starts with 213
SELECT *
FROM suppliers
WHERE telephone LIKE "213%"

> display all merchandise which costs between $100 and $200
SELECT *
FROM merchandise
WHERE cost >= 100
	AND cost <= 200

> list all our merchandise where the cost is cheaper than $50
SELECT *
FROM merchandise
WHERE cost < 50

> what orders have the date less than yesterday?
SELECT *
FROM orders
WHERE date < #18-Oct-2000#

> tell me all our orders which has the date of today
SELECT *
FROM orders
WHERE date = #19-Oct-2000#


# SELECT DISTINCT field1 FROM table WHERE condition
#==================================================
> show me names of our suppliers whose names are "ABC XYZ"
SELECT DISTINCT name
FROM suppliers
WHERE name = "abc xyz"

> what are our supplier address whose name is "XYZ"
SELECT DISTINCT address
FROM suppliers
WHERE name = "xyz"

> display descriptions of merchandise which costs between $100 and $200
SELECT DISTINCT description
FROM merchandise
WHERE cost >= 100
	AND cost <= 200

> what are the dates of our orders which has the date less than sunday?
SELECT DISTINCT date
FROM orders
WHERE date < #22-Oct-2000#

> tell me quantities of our orders which has the date of tomorrow
SELECT DISTINCT quantity
FROM orders
WHERE date = #20-Oct-2000#


# SELECT DISTINCT field1, field2 FROM table WHERE condition
#==========================================================
> tell me names and addresses of our suppliers with the telephone starts with 213
SELECT DISTINCT name, address
FROM suppliers
WHERE telephone LIKE "213%"

> list description and cost of our merchandise where the cost is cheaper than $50
SELECT DISTINCT description, cost
FROM merchandise
WHERE cost < 50



###############################################################################
# COUNT QUERIES
# SELECT COUNT(*) AS number_of_table FROM table
#==============================================
> How many suppliers are there?
SELECT COUNT(*) AS number_of_suppliers
FROM suppliers

> tell me how many merchandise are there
SELECT COUNT(*) AS number_of_merchandise
FROM merchandise

> Show me the suppliers count
SELECT COUNT(*) AS number_of_suppliers
FROM suppliers

> What number of suppliers is there?
SELECT COUNT(*) AS number_of_suppliers
FROM suppliers

> How many suppliers do we have?
SELECT COUNT(*) AS number_of_suppliers
FROM suppliers


# SELECT COUNT(*) AS number_of_table FROM table WHERE WHERE condition
#====================================================================
> How many orders for product spam are there?
SELECT COUNT(*) AS number_of_orders
FROM orders
WHERE product = "spam"

> Tell me how many suppliers by the name of Jones we have
SELECT COUNT(*) AS number_of_suppliers
FROM suppliers
WHERE name = "jones"

> how many suppliers names starts with a
SELECT COUNT(*) AS number_of_suppliers
FROM suppliers
WHERE name LIKE "a%"

> count the number of merchandise which costs more than $250
SELECT COUNT(*) AS number_of_merchandise
FROM merchandise
WHERE cost > 250



###############################################################################
# SUM QUERIES
# SELECT SUM(field) AS total_field FROM table
#============================================
> show me the total cost of our merchandise
SELECT SUM(cost) AS total_cost
FROM merchandise

> what is the total cost of merchandise?
SELECT SUM(cost) AS total_cost
FROM merchandise


# SELECT SUM(field1) AS total_field FROM table WHERE condition
#=============================================================
> what is the total quantity of the order with the id equal to $200?
SELECT SUM(quantity) AS total_quantity
FROM orders
WHERE id = 200

> tell me the total cost of the order where the name is "Xyz - ABC"
SELECT SUM(cost) AS total_cost
FROM orders
WHERE name = "xyz - abc"

> show me the total cost of the order where the date is between 9-10-2000 and 10/22/2000
SELECT SUM(cost) AS total_cost
FROM orders
WHERE date >= #09-Oct-2000#
	AND date <= #22-Oct-2000#



###############################################################################
# AVERAGE QUERIES
# SELECT AVG(field) AS average_of_field FROM table
#=================================================
> show me the average cost of our merchandise
SELECT AVG(cost) AS average_cost
FROM merchandise

> what is the average cost of merchandise?
SELECT AVG(cost) AS average_cost
FROM merchandise


# SELECT AVG(field1) AS average_of_field1 FROM table WHERE condition
#===================================================================
> what is the average quantity of the order where the product starts with ABC?
SELECT AVG(quantity) AS average_quantity
FROM orders
WHERE product LIKE "abc%"

> tell me the average cost of the order where the quantity is more than $50
SELECT AVG(cost) AS average_cost
FROM orders
WHERE quantity > 50

> show me the average quantity of the order where the cost is cheaper than 100
SELECT AVG(quantity) AS average_quantity
FROM orders
WHERE cost < 100



###############################################################################
# SELECT table1.* table2.* FROM table1, table2 WHERE relationship
#================================================================
> show me all our suppliers and merchandise
SELECT suppliers.*, merchandise.*
FROM suppliers, merchandise WHERE merchandise.supplier = suppliers.name

> what merchandise are supplied by our suppliers?
SELECT merchandise.*, suppliers.*
FROM merchandise, suppliers WHERE merchandise.supplier = suppliers.name