Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
themoney32
Starting Member
20 Posts |
 Posted - 2009-10-06 : 11:17:50
|
| I was wondering if someone could put me on the right path here and help with this project. I am a student and I am trying to get through this SQL class as I decided to change my major. Here are the instructions...---------------------------------------------------------------1. Create a table called productsonorder which consists of:a. productidb. productname c. categorynamed. supplier (i.e., name)e. unitsonorderCheck the current column definitions in the appropriate tables for sizes and data types. Define productid as the primary key. Don’t allow product name to be missing. If category name is missing fill in a value of “Look Up”. The supplier should be identified by name (which is not the name of the contact). Show the DDL in the Browser by doing a print screen (as usual). Also show the results using a DESCRIBE command for the table. 2. Populate the productsonorder table with existing data in other tables using a single insert query. Only include products that have some units on order. Show the SQL for insertion and SQL with results proving the success of the operation. 3. Find out the longest product name in the product table (show the SQL and results). Change the length of the product name column in your productsonorder table to 2 more than that length. (Using SQL of course)4. The user wishes to add a category code (called catcode) column to the productsonorder table. Depending on the product category the codes can have the following 1 letter codes.Code Code Meaning Product CategoriesP Protein Dairy Products; Meat/Poultry; SeafoodV Vitamins Produce; VegetablesS Starch Grains/CerealsO Other Beverages; Condiments; ConfectionsChange the table definition to include the catcode column. Define the column so that only the legal codes are allowed. (Show the SQL to make the change and show the table definition after the change.) (note: same as below except for the default).5. Change the default on the new column to “O”. Show the change query and the definition results.6. Populate the new catcode column using a single SQL query. (HINT: you might check out the CASE command.) Show the SQL query to add the values and the query and results showing what happened to the table.7. Create a unique index on product last name to insure that names are unique and to facilitate fast name-based retrievals. Create a non-unique index on product category names to facilitate operations on that column. Display the indexes to show that they exist.Create a non-unique index on property cities. Show the DDL and use the SHOW INDEX command to display the created index information.8. The user has decided that he/she doesn’t need to see the number of units on order in the new table. Remove the unitsonorder column from the productsonorder table. Show the SQL to do that and display the resulting table structure.9. Create and populate with a single command a new table called shippersummary. The table should contain the following shipper summary information:• Shipperid• Shippername • Shipped value (value of all products shipped by shipper: don’t bother with discounts or freight)• Number of orders shipped (by shipper)• Average value of orders shipped (by shipper; again, no discounts or freight)• Date of last shipment by the shipper10. Customers often call in to find out about outstanding orders (i.e., orders that have not shipped). Create a view called OUTORDERS that makes this easy by allowing a simple query that contains the name of the customer (and I don’t mean the contact person) to return the following information about outstanding orders:• Order identifier• Ordered date• Required date• Product name• Number of units of the product that are on orderTo avoid making users enter clumsy formatting commands, format the dates in the view to be in the form of month abbreviation, day of month, and four digit year. Show the command to create the view. Display the structure of the created view. Show how the view would be used by querying for customer Ernst Handel.----------------------------------------------------------------------- |
|
|
themoney32
Starting Member
20 Posts |
Posted - 2009-10-06 : 16:56:44
|
| No one has any advice on how to start out with this? |
 |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2009-10-07 : 07:15:14
|
| Sure. Follow the basic links in my signature. They are a positive BOON to someone just starting out with SQL.http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
|
|
|
Project Help