Docstoc

CENG 356- Database Management

Document Sample
CENG 356- Database Management Powered By Docstoc
					                    Sample Project Solutions
TABLES: To try the solutions use the database store.mdb

QUERIES: All sorts below are "ascending." The state is changed to region and the
sample names are changed according to the sample database “store”

      Query1 Show the names of all of the suppliers and their region. Sort by
      the name of the supplier.

      SELECT name, region

      FROM suppliers

      ORDER BY name ASC;



      Query2 Show the names of all of the suppliers who are in a certain region
      (e.g., "marmara"). Sort by the name of the supplier.

      SELECT name

      FROM suppliers

      WHERE region=”marmara”

      ORDER BY name ASC;



      Query3 Show all of the fields in the Suppliers table for those suppliers
      who are in a certain city (e.g., "ankara"). Sort by name of the supplier.
      Note: Make sure the city field and supplier name field do not appear twice
      in the answer.

      SELECT DISTINCT *

      FROM suppliers

      WHERE city=”ankara”

      ORDER BY name ASC;
Query4 Show the names of all of those products that begin with a given
letter.

SELECT name

FROM products

WHERE name LIKE “c*”

ORDER BY name ASC;



Query5 Show the names of all of those products that appear
alphabetically before a given name AND that have the number of units in
stock greater than a certain number. Also show the number of units in
stock. Order by ProductName.

SELECT name, stock

FROM products

WHERE name <“dede” AND stock>45

ORDER BY name ASC;



Query6 Show the names of all of those products that have a retail price
below a certain amount AND that have the number of units in stock
greater than a certain number. Also show the unit retail price and the
number of units in stock. Order by Retail Price.

SELECT name, price, stock

FROM products

WHERE price <1,5 AND stock>45

ORDER BY price ASC;
Query7 Show the names of all of those products that have a price below a
certain amount OR that have the number of units in stock greater than a
certain number. Also show the unit retail price and the number of units in
stock.

SELECT name, stock, price AS retail_price

FROM products

WHERE price<1,5 OR stock>45;



Query8 Show the names of all of those products whose name begins with
either of two letters.

SELECT name

FROM products

WHERE name LIKE “c*” OR “k*”;



Query9 Show the names of all of those products that have a price below a
certain amount AND that have the number of units in stock greater than a
certain number. Also show those products whose names begin with a
given letter. Show the unit retail price and the number of units in stock.

SELECT name, stock, price

FROM products

WHERE price<1,5 AND stock>45 AND name LIKE “c*”;



Query10 Show a count of the number of suppliers in each state. Sort by
state.

SELECT COUNT(*), region

FROM suppliers

GROUP BY region;

				
DOCUMENT INFO