cim.saddleback.edu has a MySQL/MariaDB database available for you. We hand in all assignment using the submit command. Pay attention to what each assignment query is asking, computers do what you say not what you want. Almost all assignments also have a corresponding blackboard quiz, the quiz and assignment name always match. In all pre-book assignment you also have a set of matching videos.
Each assignment description has three parts:
- Objectives of Assignment
- Synopsis concise steps with links for completing assignment
- Checklist Completion Steps which is a detailed description of how to complete assignment.
To complete this class simply begin at the first assignment work top - down. The blackboard Welcome announcement has all key due dates; submit lists exact due dates of assignments. Quizzes expire on due dates, assignment have a 9 hour grace period. After that you can turn in assignments up to 7 days late with a 25% penalty, after that you can submit assignments at half credit. Exception, last day of class is last day of class. You cannot turn in assignments more than 9 hours after the last day of class. Key to success in any on-line course is too stay up with the work. In this logic oriented class whereby material builds on itself, you need to both stay up with the work and master the work. You need to understand the tutorial.sql videos before progressing to the next level.
I will be sending out e-mail reminders informing where you should be in the course. You can always complete the course early. Once again complete all assignments in order they listed (i.e., top down).
Basic introduction to course, you will have to gain experience in using the browser back button, and navigating three sites using browser tabs.
- Complete all steps at http://cim.saddleback.edu/de
- Make sure you watch a blackboard orientation Web-cast or attend Wednesday Noon 8/19/2015 Web-casts.
- Take blackboard Quiz 0 Intro,
- Review class Web-site, read syllabus
- Print this assignment in PDF page
- Print blackboard's Welcome announcement page, use it and this assignment page to create your cimw105 notebook.
Checklist Completion Steps...
- Complete all five orientation steps at http://cim.saddleback.edu/de
- If you don't watch or attend course specific orientation Web-casts on blackboard, login to blackboard now and watch them.
Nota Bene, Assignment name, blackboard videos/Web-casts
and blackboard Quizzes always have the same three names, in these case they are all labeled as Intro.
- If you didn't take blackboard Quiz 0 Intro, take it now. Note you can complete entire course just using blackboard (blackboard has all key links).
- review class Web-site, read syllabus and scan over this assignment list. Note this is your course guide for the entire course, you don't complete all assignments at once, you work top down on this assignment list. Blackboard quizzes and videos also follow the exact same top down organization. You need to work top down on assignment and watch the corresponding blackboard video and quizzes when the assignment page tells you.
- Print this assignment page by clicking the print button (this will be the guide for completing the course ) Nota Bene you can set you printer to Adobe pdf (for a pdf hard copy of this page)
- Print blackboard's Welcome announcement page (its 3 steps are a concise summary of course requirements). The three step announcement and assignment page should be your first few pages in your cimw105 notebook.
Basic introduction to SQL, RDMBS, queries, submit and phpMyAdmin, develop skills at using three apps at once: phpMyAdmin, Video and Submit.
Checklist Completion Steps:
- Print PDF viewgraphs four slides per landscape page (right^click link to save) or PDF two slides per portrait page
- Watch first four tutorial.sql blackboard Videos, (i. Database Video, ii. MySQL SQL Video, iii Overview Video, iv IDE Video), remember to pause, think and take notes when watching videos. This is not MTV.
- Print PDF Viewgraphs 4/page or Portrait PDF 2 Slides Per Page
- Watch the next two blackboard videos (v tutorial.sql 1st 5 and vi tutorial.sql submit)
- submit first 5 queries for tutorial.sql (10 possible points), using phpMyAdmin, and Database scripts.
- Watch 7th blackboard Video tutorial.sql (vii tutorial.sql last 5) last 5 queries.
- submit tutorial.sql all ten queries.
- Optional watch a pre-recorded blackboard Web-casts before or after tutorial.sql blackboard videos.
- Take tutorial.sql blackboard quiz
- Review consistency notes listed at end.
- all submit filenames are lower case and always end in .sql
- phpMyAdmin is mapping all functions names to upper case but we use all lowercase function names for submit
- all function arguments have a space before and after (we use sum( price ) not sum(price)
- for fruit database all tables are lower case
- for fruit tables all field names are lower case except for ID, (i.e., fruitID)
- you should use accent marks to delimit table and field names
- never include database names in your queries, we will always first select a database.
- in checklist completion steps we use roman numbers to count identify blackboard videos.
If you have submit errors you can wait until you complete the next assignment select.sql, which goes into identifying submit errors.
Objective: Become proficient with reading submit error messages, and practice writing very basic SQL queries.
Checklist Completion Steps:
- Watch the two blackboard select.sql videos, (i select 1st 2 queries, ii select.sql 2nd 2 queries)
- Optional watch the select.sql 2/12/2014 blackboard Web-Cast
- Take blackboard select.sql quiz
- submit select.sql, (10 Points), 10 select comments listed next.
- Click blackboard quiz grades tab - verify quiz grades
- Retake blackboard select.sql quiz
- Check submit grades, try to fix missing points
Ten select.sql Comments....
#s1 Display table:fruit sort by price descending
#S2 display first three rows of table:inventory
#S3 display fruit name and price for all fruit with price > 1.0
#S4 display name and cost as pennies for fruit with ID = 1
# S5 display fruit with price less or equal 1.0, order by ascending price # S6 display contents of table:fruit
# S7 display fruit table ordered by fruitID in descending order
# S8 display fruit name and price for fruit with price of $1.25
# S9 describe or explain field/column info about table:inventory
# S10 display the rows of the three most expensive fruit,
# sorted w more expensive first
Detailed Checklist Completion Steps...
- Watch the two blackboard select.sql videos which teaches you how to use the submit command. The submit command includes an automatic grader for query based assignments. Video also complete some of the quires. Assignment deals with only two fruit tables: fruit and inventory. You will be adding three more fruit tables (order, orderLine, & customer) in the phpMyAdmin video set.
- Optional watch the select.sql 2/12/2014 blackboard Web-Cast
- Take the blackboard select.sql quiz
- submit select.sql, (10 Points), the 10 select comments are listed above the detailed completion steps.
- Click blackboard quiz grades tab - verify quiz grades.
- Retake blackboard select.sql quiz, strive for full credit on this quiz.
- When you first login to submit grades, or if in grader click the black grade tab, on the grade display, if tutorial.sql and is not 10/10, you can fix it now. If you need more background in SQL before fixing them watch the next video sets covering intermediate SQL capabilities.
Some assignments have both a checklist completion steps and also a detailed check list completion section. The second version is just a detailed version of the first one.
More advance introduction to SQL, data manipulation, intrinsics, and combining tables.
Checklist Completion Steps:
- Should understand phpMyAdmin and submit.
- Prerequisite intermediate browsing skills, at this stage you should have a browser that has five tabs that open automatically to: database Creation scripts, phpMyAdmin, submit, blackboard and assignment or class site page. If not go back and rewatch the tutorial.sql submit video.
- Watch first 2 blackboard intermediate.sql videos (i Background, ii Data Manipulation)
- submit first 6 queries for intermediate.sql,
- Watch the last three blackboard videos (iii intrinsics, iv combining tables, v language interfaces)
- Submit all 20 queries for intermediate.sql
- Take the blackboard intermediate.sql quiz.
Detailed Checklist Completion Steps
- This assignment assumes you can login to both phpMyAdmin and submit. If not review the previous video set tutorial.sql.
- Prerequisite intermediate browsing skill, at this stage you should have a browser that has five tabs that open automatically to: database Creation scripts, phpMyAdmin, submit, blackboard and assignment or class site page. If not go back and re-watch the tutorial.sql submit video. You can have multiple browsers on your computer, I recommend Firefox and Chrome. My Chrome browser is set to automatically open the above 5 pages. My Cyberfox (64-bit) Firefox is used for general browsing. How to use chrome to set default home page tabs. Note safari and Opera are two other worthy browsers.
- Log in to blackboard and watch the first two intermediate.sql assignment videos (i background and ii data manipulation).
- submit first 6 queries for intermediate.sql, like always they are labeled as #i1..#i6 comment then query. The file intermediate.sql and back up intermediate.sql.bak is in your cim home folder. Note files is automatically loaded when you login and select assignment using submit. Intermediate.sql is also on-line at cim/~premiere. For readability, always add comments and blank lines. Will take off points if student don't properly comment their code.
- Watch the last three blackboard videos (iii intrinsic, iv combining tables, iv programming language interfaces).
- Submit all 20 queries for intermediate.sql. Make sure you comment all queries format is #Number comment, I do check for comments and will take off points for missing comments.
- Take the blackboard intermediate.sql quiz.
Objective: Review basic SQL, and become proficient with phpMyAdmin
Check Completion Steps...
- Watch first three phpMyAdmin pmaIntro blackboard videos (i MySQL RDBMS Intro, ii phpMyAdmin tutorial, iii luck 7 query ).
- Submit the first lucky 7 queries pmaIntro.sql (11 points)- make sure file is well commented.
- Watch the final three blackboard videos for pmaIntro (iv DB Synchronization, v Multi-tables, vi sub-queries)
- Take the blackboard pmaIntro quiz.
- Submit the final 4 queries for pmaIntro.sql.
- Remember practice makes perfect, try to write the first seven queries from scratch using only your comments. Study skill, try to write as many of the first seven queries before going to bed. Think about how to solve the ones you cannot while you are falling asleep. The solution may come to your first thing in the morning, but make sure you have pen and paper next to bed to write down the solution. In the past I was working the space based laser control system, this is a typical strategy I and other use to solve complex problems. You need to let the brain unwind while you are sleeping and its typical for solution to come to you first thing in the morning.
This assignment is identical to the CIMW245 PHP/MySQL phpMyAdmin assignment.
- Create DB that is a good stepping stone for textbook's premiere database
- Learn about views - virtual Tables
Checklist Completion Steps
- Watch the first two blackboard videos (i RDBMS, ii Creating tables). You will be using phpMyAdmin to create a fruit DB with 5 tables. Fruit DB is a simplified version of the textbook's premiere database.
- Take blackboard quiz pmaView first two videos.
- Watch the next two blackboard videos (iii Altering Tables, iv views)
- Submit pmaView.sql
- Take blackboard quiz pmaView.
- Make sure sure you have all five fruit tables.
- Make sure you Create views, you need to capture the SQL query using phpMyAdmin
Complete the on-line on blackboard these videos are listed with previous pmaView.sql video set.
Checklist Completion Steps:
- Watch the two blackboard pmaReset Videos (v Critical Review, vi DB Reset/Restore point), they are listed with the pmaView.sql videos.
- submit pmaReset.sql.
- Take the pmaReset quiz.
- Make sure you can write basic SQL queries from scratch
- Make sure you have a local file for resetting your database state (default name is db0sbusername.sql)
Checklist Completion Steps...
- Watch first 5 pmaTrigger.sql blackboard videos (i Constraint Restrict, ii Constraints Cascade, iii Updated Restore Point, iv intro Triggers, v Triggers part 2).
- Submit part of pmaTrigger.sql
- Take the blackboard pmaTrigger Quiz
- Watch the next two pmaTrigger blackboard videos (vi Routines - functions, vii Routines-Procedures)
- Complete blackboard pmaRoutine quiz & update submit for pmaTrigger.sql
- Watch the next three pmaTrigger blackboard videos (viii Transactions Rollback, ix Transactions Commit, x events)
- Complete blackboard pmaTransaction quiz and finish submit pmaTrigger.sql.
- Watch blackboard video xi pmaReport, refer to PMA Report assignment, for assignment details.
Checklist completion step watch the blackboard video xi PMA Report, listed at the end of the pmaTrigger videos.
Method of Evaluation:
E-mail me (firstname.lastname@example.org) your final phpMyAdmin report (15 points). Your report can be in Open Office, libre office or MSWord format (doc or docx) Google docs or PDF after completing the next assignment. If using Google docs, my gmail account for sharing your file is email@example.com. Your final report will have ....
- In phpMyAdmin select your database, make sure only the database is selected not a table within a database. Click the structure tab, it will show tables in selected database, select Data Dictionary button near bottom, save a PDF data dictionary of the 5 tables and two view, look for a PDF print option.
- Graphic Schema of Views and Tables, illustrating Field Names, Types & Relationships. In phpMyAdmin, select only your database, select designer tab or more → designer. Make sure you select only your database, if you select a table in the database, the designer tab will not be present. Select the phpMyAdmin graphic schema window and press alt^print screen, open your phpMyAdmin report and paste (ctrl^V) the clipboard image.
- Select only your database, then export tab, export method custom. You must select an export method that will allow you to obtain a text based image of your database with column headers both Microsoft word or open office document will work. PDF output places each table on its own page.
- Local SQL creation script you created in then updated restore point video (aka pmaReset.sql).
- Create a chart view of fruit names and total inventory quantities.
|Pre-book total assignment points: 112 (Note Report Points are maintain on my Spreadsheet)|
For both A and B grade you need to complete some text book assignments, consult Syllabus for exact details on course grading.
See schedule for the recommended pace, Blackboard quizzes maintains final due dates and syllabus has deadlines required for maintaining enrollment in class.
Nota Bene, for all assignments except Chapter one, you must submit the assignment using the cim submit command. Refer to the select.sql video set for additional help on submit.
The database we are using is on-line Once you have completed the 10 questions on page 21 (aka you have hand written answers), take blackboard Assignment Quiz One; which will verify that you have completed the written portion of the assignment. Assignment one Quiz can not be repeated it will count as part of your quiz grades. Using the results of Assignment quiz one and/or wait until you have completed chapter 3, submit p21.sql. (10 Points) Updated versions of questions adopted from book...
- List names of all customers that have credit limit of 7500 or less; remember table and field names upper case.
- List order numbers for orders placed by customer number 608 on 10/23/2007
- List part number, description and value (price of part * quantity on hand) as "On-hand Value" of all parts in item class SG.
- List part number and description of all parts in class HW
- How many customers have a balance that exceeds their credit limit?
- What is part number, description and price for least expensive part in DB
- For each order, list order number, order date, customer number and customer name.
- For each order placed on October 21, 2007 list order number, customer number and customer name.
- 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
- For each order placed on October 21, 2007 list the order number, part number, part description, and item class for each part ordered
- Nota Bene Premiere Database uses all Upper-Case letters for both table and field names.'
- Premiere Database is used in both the book and the corresponding book assignments.
- Fruit database tables are all lower-case letters. Fruit used medial caps, for table names consisting of two words, 2nd words start with capital letters.
- Premiere is using underscores to separate multi-word table or field names.
- Function names and arguments are lower case.
- all filenames for book start with p then page number then .sql
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
For p87.sql write queries for this five comments and write comments for the second 5 queries...
- #Display the first five rows in ORDER_LINE table.
- # Display all your customer names along with their city and state.
- #How many salesman do you have?
- # Display the contents of PART Table
- # Describe the PART table
- select * from PART order by PRICE;
- select sum( NUM_ORDERED*QUOTED_PRICE) from ORDER_LINE;
- select max( Price ) from PART;
- select r.LAST_NAME, Customer_name, balance, credit_limit
from CUSTOMER as c, REP as r where c.REP_NUM = r.REP_NUM;
- select Order_num, order_date from ORDERS
where CUSTOMER_NUM in (select CUSTOMER_NUM from CUSTOMER
where REP_NUM= 65);
You can keep submitting until you have 10/10, if you don't fix one query at a time
on Question 7 make sure you include both end points 10 and 25 and do this only one way.
Hint on question 20, you need to do a group by rep number right after the from table statement and then use a having clause with sum( balance ). Note the having clause enables the group by clause to be used as a where conditional, in this case sum(balance) refers to the sum of the balance relative to the group by REP_NUM. To sort the final output, just end the query with order by REP_NUM.
- Question 5 on all sub-queries you should return only one column, returning multiple columns is often logically incorrect. With the current version of MariaDB this mistake is causing both phpMyAdmin and submit to hang. You should be doing all initial testing in phpMyAdmin, submit is for submitting not debugging.
- 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 9, find rep number, rep last name, and rep first name, of reps who represent a customer with credit limit of exactly 5000.
- Question 11 you may need to add order by CUSTOMER_NUM DESC.
- 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; Sort the output by the first part_num table, then by the second part_num table, You are displaying PART_NUM, DESCRIPTION from first table, then PART_NUM, DESCRIPTION, and CLASS from second table. 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 13, make sure you include the single quote.
- Question 15 order by order_num ASC; Hint put select after union in parentheses, then add order by clause
- Question 16 MySQL doesn't have the intersect operator just use an and in the where clause. You can also consider using or having.
- Questions 20 order by part number, then by order number both in ascending order.
- 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.
- # 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 insert into NONAPPLIANCE all items that are not class 'AP'; For this one you need to use insert select command., Nota Bene I cannot check 1 and 2, they will pass automatically, but #2a will fail, if 1 or 2 are wrong make sure they are working.
- #2a Display contents of NONAPPLIANCE sort by ascending Price
- #3 change description of PART_NUM AT94 to Deluxe Iron
- #4 increase price of all items CLASS SG by 2%
- #5 add Part number TL92 description Trimmer, units on hand 11, class HW, price 29.95
- # 5a display contents of NONAPPLIANCE Table
- # 6 delete every part in NONAPPLIANCE of class SG
- # 7 in NONAPPLIANCE change PART_NUM FD21 to CLASS NULL
- #7a display contents of NONAPPLIANCE Table
- # 8 Add new last Column ON_HAND_VALUE seven digit number with 2 decimal points to NONAPPLIANCE table
- # 8b set all values of column ON_HAND_VALUE to ON_HAND*PRICE in table:NONAPPLIANCE
- # 9 in NONAPPLIANCE table increase column DESCRIPTION to 30 characters
- # 9a display contents of NONAPPLIANCE Table
- # 10 Remove NONAPPLIANCE Table
- read chapter 6
- take blackboard chapter 6 quiz
- submit p177.sql
- Verify all grades for submit, 2nd row is total possible points for each assignment (note pmaReport is maintained in my personal spreadsheet)
tutorial select intermediate pmaIntro pmaView pmaReset pmaTrigger Report 10 10 20 11 15 12 19 15 Book Page: p21 p58 p87 p125 p157 p177 Possible Points: 10 7 10 21 20 15 Possible assignment points 97 + 15 + 83(book) = 195 (you can complete extra)
- Consider completing the extra credit assignments
Chapter 7 Database Administration and Chapter 8 MySQL Special Topics Are Extra Credit;
For your person gain, can look into MySQL Admin Tools.
#S1 display contents of table:fruit #S2 display first three rows in table:inventory #S3 display fruit name, inventory quantity sum, price and sum quantity*price displayed as value for each row in fruit #S4 display number of fruit types (i.e., # rows/records) in table:fruit as count( * ) #S5 display table:fruit sorted by descending price #S6 display name and cost as pennies for fruit with ID = 1, try this out using the multiplier as both a literal (i.e., '100') and a number not enclosed in single quotes. #S7 display total number of pieces of fruit in inventory #S8 display fruit table ordered by fruitID in ascending order #S9 display fruitID for each row in table:inventory #S10 display fruit name, and price from table:fruit #S11 describe or explain field/column information about table:inventory, you should click previous two links sql has multiple commands