Database Programming – INFT224

Spring 2009

Week 10 Assignment 

 

Due Monday, July 8

 

Objectives: 

-          To reinforce your knowledge gained from class discussions and readings.

 

Deliverables:

 

Hand in your answers (typed, not handwritten) to the following questions. 

Questions – 10 point each = 100 total

 

  1. What is a significant reason for using views?

 

  1. Why do we need to join tables in queries? 

 

The following questions are based upon a table with the following characteristics:

              Inventory(itemnum, description, qty)

 

  1. Write the SQL to create a view of all the attributes?  

 

  1. Write the SQL to create a view of itemnum and qty?  

 

  1. Write the SQL to delete all records in the Inventory table.  

 

  1. Write the SQL to delete all records that have no items in stock.  

 

  1. What is the difference in meaning from having a NULL value or a ZERO value in a table?

 

  1. Write the SQL to show all inventory items whose description begins with the letter ‘A’.

 

 

The following questions are based upon a table with the following characteristics:

              Inventory(itemnum, description, qty)

Order(orderNumber, itemnum, qtyOrdered)

 

 

  1. Using the Inventory and Order table, write a query to show ALL orders, with the following information.  Hint: you need to join 2 tables (20 points):

 

OrderNumber             ItemNumber                Description                  QtyOrdered