Login to blackboard watch the orientatiation video, then take Quiz 0, you may want to also watch the Web-Cast buzzword video, and Complete this questionnaire. Consult this file for login information

submit tutorial.sql, login in to blackboard; you will need to watch all tutorial.sql assignment videos.

Useful software putty and filezilla

submit fruitcmds.sql, login in to blackboard; you will need to watch all fruitcmds.sql assignment videos.

The videos will assume you can login to your cim.saddleback.edu account, the file fruitcmds.sql is in your cim home directory, (you also have a backup copy of the file fruitcmds.sql.bak); This file has 20 MySQL commands you are to figure out what each command does; Blackboard has a set of videos to help you with this assignment. Once you have figured out what a command does add a comment line before the MySQL command. You may add blank lines, and # comment lines. When you have entered comments for all 20 commands (I have done the first one); type 'submit fruitcmds.sql' at a linux prompt. Note you can cut and paste commands one at a time into MySQL client interface but you must do each command sequentially (i.e., command n + 5 may depend on command n)

phpMyAdmin Advance, complete the two online blackboard video sets: phpMyAdmin and PHPMyAdmin Advance. CIM225 students complete only the first phpMyAdmin video set. This is a report style assignment, you will be using the phpMyAdim application to create a fruit DB which is similar to book's premiere database. You must complete all steps outline in the viewgraph/videos. E-mail me your finished work in text or MSWord format (version 2003 or earlier). You will have to create a database graphic schema, which is done using the designer tab in PHPMyAdmin, use Alt^PrintScreen on the PC to capture the finished image. The steps you need to complete...

1. Create the first two tables fruit Tables using PHPMyAdmin and run a few queries
   Step One is identical to the CIM225 PHP/MySQL PHPMyAdmin assignment.
   
2. Create three additional tables and add comments & relational view information to your tables.
3. Create a data dictionary and schema. Make sure you include a relational view Schema using PHPMyAdmin's designer tab.
4. Generate Output illustrating the contents of the five tables.
5. Complete the list of 27 queries and comments.

submit select.sql, watch blackboard videos select.sql which teaches you how to use the submit command. The submit command includes an automatic grader for query based assignments. Video also complete queries 1 and 3 for the 11 queries you need to write. For this assignent you need to create 11 queries for these 11 comments...

1. # display contents of table:fruit
2. # display first three rows in table:inventory
3. # display fruit name, inventory quantity sum, price and sum quantity*price displayed as value for each row in fruit
4. # display number of records in table:fruit as count(*)
5. # display table:fruit sorted by price in descending order
6. # write a query to output the fruitID for fruit banana
7. # display total number of fruits in inventory
8. # display fruit table ordered by fruitID in ascending order
9. # display fruitID for each row in table:inventory
10. # display fruit name, and price from table:fruit
11. # describe the field names and types for table:inventory

If you want to work with a more complex database before starting the book, you can attempt this extra credit assignment.

Read Chapter 1; Complete the premiere product exercises on page 21 questions 1..10, The ten questions in the book are listed after the next paragraph.

The database we are using is online. Oonce you have completed it take Assignment Quiz One; which will verify that you have completed the assignment. Assignment one Quiz can not be repeated it will count as part of your quiz gradees. Using the results of Assignment quiz one and/or wait to you have completed chapter 3, submit p21.sql. (p21.sql is optional for Summer/09 Class)
Updated versions of questions adopted from book...

1. List names of all customers that have credit limit of 7500 or less
2. List order numbers for orders placed by customer number 608 on 10/23/2007
3. List part number, description and value (PRICE*Quantity) as "On-hand Value" of all parts in item class SG.
4. List part number and description of all parts in class HW
5. How many customers have a balance that exceeds their credit limit?
6. What is part number, description and price for least expensive part in DB
7. For each order, list order number, order date, customer number and customer name.
8. For each order placed on October 21, 2007 list order number, customer number and customer name.
9. List sales rep number and sales rep last name for every sales rep who represents at least one customer with credit limit of $10,000.00
10. For each order placed on October 21, 2007 list the order number, part number, part description, and item class for each part ordered

submit p58.sql -- Read chapter 2, drop all your fruit tables, then source the script /home/premiere/public_html/MySQL-Premiere, then using PHPMyAdmin designer tab, create the relationships between tables. finally submit p58.sql consisting of these seven Queries...

1) Display names of all the premiere database tables;
2) Display the number of customers you have.
3) List the contents of the CUSTOMER table.
4) Describe the REP table.
5) List the contents of the ORDERS table.
6) List contents of REP table, sorted by highest commission first.
7) Display customer name with highest credit limit, hint select customer_name, order by, limit 1;

You should probably use PHPMyAdmin to help you create the queries, especially the sorted ones.

Read chapter 3, submit p87.sql; and submit p88.sql;

For p87.sql write queries for this five comments...

1. #Display the first five rows in ORDER_LINE table.
2. # Display all your customer names along with their city and state.
3. #How many salesman do you have?
4. # Display the contents of PART Table
5. # Describe the PART table

For p88.sql write comments for these five queries...

1. select * from PART order by PRICE;
2. select sum( NUM_ORDERED*QUOTED_PRICE) from ORDER_LINE;
3. select max( Price ) from PART;
4. select r.LAST_NAME, Customer_name, balance, credit_limit
   from CUSTOMER as c, REP as r where c.REP_NUM = r.REP_NUM;
5. select Order_num, order_date from ORDERS
   where CUSTOMER_NUM in (select CUSTOMER_NUM from CUSTOMER
   where REP_NUM= 65);

For p88.sql once you submit it, it is e-mailed to me for grading. On p87.sql, you can keep resubmitting it until the auto grades gives you 5/5.

Read Chapter 5; Chapter 5 on multiple tables is one of the hardest chapters; submit p157.sql, this consists of all Premiere Database queries, questions 1..20 pages 157-158, the following four questions have further clarification..

• Question 6 list customer number and customer name for all customers with active orders that were not placed on 2007-10-21.
• Question 8 the sort order becomes order by class, ORDERS.order_num and then ORDER_LINE.part_num;
• Question 12 # hint you will need to combine the part table with itself, look for the matching classes and display parts number in ascending order; If still stuck reread the section "Joining a Table to Itself" pages 140-141 and pay special attention to the Q&A sidebar on page 141.
• Question 15 order by order_num ASC; Hint put select after union in paraenthesis, then add order by clause
• Preface each MySQL command with the question number comment line.

Hints Do not even attempt to try to join more than three tables, use sub queries first to determine customer_num from customer_name, and part_num from description.

Chapter 6 is relatively easy; submit p177.sql; write a SQL query for questions 1-10; Question 8 is two queries, 8 and 8b; Also added four display content of Table:NONAPPLIANCE queries, 2a, 5a, 7a and 9a. Note #2a, #5a, #7a, #9a and #8b are the five additional queries added to this assignment, The final Query list is...

1. # 1 create table 'NONAPPLIANCE' don't prefix table name 'NONAPPLIANCE' with your database - table fields are PART_NUM, DESCRIPTION, ON_HAND, CLASS and PRICE.
2. # 2 insert into NONAPPLIANCE all items that are not class 'AP'; For this one you need to use insert select command.
3. #2a Display contents of NONAPPLIANCE sort by ascending Price
4. #3 change description of PART_NUM AT94 to Deluxe Iron
5. #4 increase price of all items CLASS SG by 2%
6. #5 add Part number TL92 description Trimmer, units on hand 11, class HW, price 29.95
7. # 5a display contents of NONAPPLIANCE Table
8. # 6 delete every part in NONAPPLIANCE of class SG
9. # 7 in NONAPPLIANCE change PART_NUM FD21 to CLASS NULL
10. #7a display contents of NONAPPLIANCE Table
11. # 8 Add new last Column ON_HAND_VALUE seven digit number with 2 decimal points to NONAPPLIANCE table
12. # 8b set all values of column ON_HAND_VALUE to ON_HAND*PRICE in table:NONAPPLIANCE
13. # 9 in NONAPPLIANCE table increase column DESCRIPTION to 30 characters
14. # 9a display contents of NONAPPLIANCE Table
15. # 10 Remove NONAPPLIANCE Table
    
    

Chapter 7 Reports and Chapter 8 Embedded/PL-SQL Are Extra Credit;

Plan on adding additon extra credit 2nd 8 weeks Spring 09, the other book databases and maybe the Casino database used in CS4B.