All assignments due 5/20 11:59PM

Prep Work for First On-line Lecture Schedule 1/16 and 1/18

Administration guide on setting winscp and Putty will be discussed at lunch time On-line lecture. Prior to this lunch-time on line lecture download and install WinSCP, and putty. If you missed both lunch-time online lectures go to cim.saddleback.edu, click the help tab, then step through WinSCP on how to copy files back and forth from cim, read the powerpoint guide covering Putty and basic linux commands.


You have ten assignments: two non-book assignments labeled a and b; and one assignment for each chapter labeled 1..8; Reading chapter 8 is optional. Your first assignment is C, second is A, third is B, fourth is the book assignment 1. Assignment 1 correspons to chapter 1. Note you must have at least 2 assignments done by 2 days before the drop without W grade. You must have 5 assignments and 50% of the quizzes completed by 2 days before the drop with W grade. Recommended pace: at end of fourth week, complete 2 assignments and read chapter 1. After fourth week, read 1 chapter and complete one assignment every 2 weeks.

I will be supplying a MySQL server on cim.saddleback.edu, assignment and initial lunch time on-line lecture is geared toward familiaring yourself with handing in homework. If you have access to Oracle or SQL Server 2003, with prior permission, you may hand in assignments using Oracle or SQL server 2003. However, quizzes and tests are based on the MySQL material in the book.

To hand in assignments, you complete the assignment.sql file, for the first assignment I have completed most of it. You will find assignments a.sql, b.sql and 1.sql in your home cim.saddleback.edu directory. Finish the filling in anew.sql and hand them in by typing submit anew.sql at the cim.saddleback.edu command line. You need to use putty to login to cim and then type submit a.sql. The submit command sends me and your Saddleback email a copy of your assignment.

  1. Install putty and winscp on your home computer. Instructions on using putty and winscp are available at cim.saddleback.edu under the help tab. Attend an lunch time on-line introduction to linux, putty, sftp, and pico. Once putty, winSCP are working, do assignment c first. You don't hand in anything for c. A file a.sql has been placed in your home directory. For assignment A, questions 1..5 have both the query and statement before query, on what the query does. For Questions, 6..10 your are to tell me what each query does. You do this by filling in the top question before each query. For this assignment all you need do is tell me what queries 6..10 do.

    For extra credit, on questions, 11..16 I have supplied the question you need to write the query.

    To test queries, one would start up MySQL and cut and paste one query into the system at a time. You should have each query working before moving on to the next query.

    Nota Bene, all assignments have the exact same format as a.sql. After stripping comments and blank lines, Odd lines list the problem numbers (i.e., select "#1"; this is the first problem of the assignment); Even number lines have the SQL query answer (i.e., 'show variables'; To create the sql file, one should enter the mysql client system and turn on a tee; A tee creates a tee that writes to both the screen and a file. To create temp.sql one would enter mysql command 'tee temp.sql' at the MySQL prompt. Once you have correctly executed each assignment problem. You should turn off the tee with notee. Exit MySQL and edit the file you just created, paying close attention to the required format. Once the script file is complete you should test the file from the command line, (i.e., mysql -u username -p DBusername < temp.sql ); Once the file is working on cim.saddleback.edu type 'submit a.sql'. Submit will send a copy of your homework to me and a cc copy to your Saddleback email account.

  2. Do the fruit assignment, by filling in the partially completed file b.sql. In b.sql you are given 17 queries, tell me what each query does.
  3. Look over the viewgraphs pages 1..31 (viewgraph pages 1..62) work through the tutorial on pages 22..31. You should do C before a and b; You have nothing to hand in for c. This assignment doesn't count towards the grade.
Chapter Homework Assignments...
  1. Read Chapter 1, Work through the questions and answers, do the exercises on pages 21..23; E-mail me a copy of your answers in MSWord, text or email format. On blackboard if you click the "premiere database guide" you will see an online copy of the Premiere's tables.

    If you want, when completed Login on to cim.saddleback.edu, start up mysql. Verify that your answers are right by runnng the script 1.sql. In the script 1.sql question 9 has been modified to only list the sales rep number and last name for every sales rep who has a customer with a credit of $10,000. You have access to on-line text based Premiere tables with blackboard link

  2. Read chapter 2 and e-mail me the answer to these 6 questions:

    1) Source the premiere databases stored in /home/premiere/public_html/MySQL-Premiere.
    2) How many tables do you have?
    3) List the contents of the Customer table.
    4) Show column information for the Rep table.
    5) List the contents of the Orders table.
    6) Briefly stating the difference between our Premiere database and the book's Premiere database on page 5 figure 1-2.

  3. Read chapter 3, generate a p100.sql script that has an SQL command for questions 1..21 located on pages 100-101. Note on questions 7, you have a parts a and part b, #7a, and #7b.

  4. Chapter 4 is one of the hardest chapter; Generate a p134.sql script that has a SQL Command for Questions 1..20 on page 134-135, on questions 14..17 don't even attempt to join more than 3 tables, use sub queries to determine customer_num from customer_name, and part_num from description. "For question 12, output the only one with a pair. Update my sql file"

  5. Chapter 5 is relatively easy; The command insert into tbleName requires a values keyword unless you use a select clause. For assignment P155.sql, end each command with select * from NON_APPLIANCE; Questions 9 and 10 don't require this, but end 9 and 10 with select * from NON_APPLIANCE in order to maintain consistency.

  6. In chapter 6 some of the questions in the quizzes come from phpMyAdmin. For assignment P187.sql, note all the major changes itemized below, each question should be identified with the number and letter for example "#1a", and "#1b". All views will use default column names. On 3 you will get duplicate order_num lines, At the end of 3, drop view ORDER_TOTAL and create a new ORDER_TOTAL representing the order_num, TOTAL_AMOUNT of each order, this is 3e, 3f just display the entire ORDER_TOTAL view. Use this as question, 4, four users have been created Ashton, Kelly, Morgan and James. You need to be in the mysql database in order to use the select command. Use mysql; 4a User Ashton must be able to retrieve orders from the PART tables. 4b. Write a query or preferrably use phpMyAdmin to verify this has occurred. 4c User Kelly must be able to add new orders in ORDERS table. 4d. Write a query to verify this has occurred. 4e User Kelly must be able to add new items in ORDER_LINE. 4f Write a query to verify this has occurred. 4g User James must be able to change the price of all parts. 4h Write a query to verify this has occurred. 4i User Ashton must be able to delete customers. 4j write a query to verify this has occurred. 4k User Morgan needs to be able to retrieve customers name, street, city, state and zip code. 4l write a query to verify this has occurred. 4m User James must have all privileges on the REP table. 4n write a query to verify this has occurred. 4n Turns all James is a fluke, revoke all his rights on the Sales Rep tablese. 4o write a query to verify this has occurred. The users only have access on the local machine, ( i.e., Ashton@localhost ). Hint you may want to use phpMyAdmin, to generate the queries, you can always execute them twice. On Five actually execute the command. On 6 after each command, execute a 'show index from tableName where key_name="IndexName"; On 7) execute a query 'show index from PART" On 8a the MySQL system catalog is the database mysql. On 8b..8e, you be using your PREMIERE databases. Unable to do ; Skip 8c..8e; 8f. Refer to MySQL documentation write a sql command that will list all the global variables; Note phpMyAdmin always has a tab for listing global variables. on 10 add check seems to be be implemented in MySQL 5.0; you can do the check but nothing comes back. Also views were added to MySQL 5.0 you may need to update your view priveleges.

    7. Chapters 7 and 8 and respective homework assignments are optional...
  7. Chapter 7 is relatively brief especially since you are skipping pages 206-218 inclusive; The pages 206-218 are not supported by MySQL. In MySQL (UPPER, UCASE), (LOWER, LCASE), (adddate, date_add), (now(), curDate()) are all aliases. Also, all MySQL functions, and field names are case insensitive (i.e., upper( "Tom" ) == UpPEr( "Tom" ). Note on page 198, since are database has future dates, you will get very large negative numbers for the days between the present (now() or CURDATE()) and order_date. The query in mysql for example 7, figure 7.8 is... " create view SLSR_REPORT( SLSR, CUST, BAL, CRED, AVAIL ) AS select concat( REP.rep_num, "-", trim( first_name), " ", trim( last_name )), concat( customer_num, "-", trim( customer_name ) ), balance, credit_limit, credit_limit-balance from REP, CUSTOMER where REP.rep_num = CUSTOMER.rep_num;" Assignment p222.sql exercises 1-4 in book, for 5-7 substitute these problems: 5) List the part_num, description and price for the highest priced part. 6a) List order_number, part_number, rep_num, and profit loss (quoted_price - price as profit loss) for all items sold below price. 6b) Repeat 6a but sum the profit loss totals for each sales rep 7) List the order totals for each sales rep, you should list the rep_num, and sum of numbered ordered & quoted price as order total. 8) List the customer number, customer name, and balance as Balance (The balance should be proceeded with a $ only show the rounded off dollar amount and be right justified occupying 15 spaces, hint use lpad after concat );

  8. PL/SQL (Procedure Language SQL) is well supported by Oracle, support for MySQL has just started with version 5.0. The book's PL/SQL code is significantly different than MySQL. In addition, it is often prudent to use the right tool for the job. Extending SQL into a procedure language is not wise. When you want to do procedure oriented programming you should use a procedural oriented programming language like PHP, Perl or C. Therefore I don't feel that mastering advance PL/SQL is that important. As a result, reading chapter 8 is optional; In addition chapter 8 doesn't have a quiz. However, you still have an assignment for chapter 8. Also, if interested in learning about PL/MySQL this is a good link.

    For this chapter work through these Queries, and then write the following five MySQL procedures. 1) Write a procedure (getCustomerInfo) that when passed a customer_num will print out the customers name, and credit_limit. 2) Write a procedure (getCustomerInfo2) which will print out customer with number doesn't exist, when the customer number doesn't exist and will print out the same output as (1) when the customer number exists. 3) Write a procedure getCustomerOrders when passed a customer_number will print out a table listing order_number, part_num, num_ordered and quoted price. 4) Write a procedure checkCustomerBalance that when passed a customer_number will verify that balance equals the sum of the orders. 5) Write a procedure getRepCommission, which when passed rep_num, will output the current commission due the salesman. Assume that the commission is based on the customers order totals multiple by the Rep's Rate. Put all procedures in a file names 8.sql; My grading program will load all your procedures and then call each one.