Course Assignment Overview
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 Canvas 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 Canvas 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).
Intro aka Course Orientation
A 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 https://cim.saddleback.edu/de
- Make sure you watch a Canvas intro orientation webcast details on Canvas - viewgraphs
- Take Canvas Quiz 0 Intro,
- Review class Website, read syllabus
- Print this assignment in PDF page
- Print Canvas's Welcome announcement page, use it and this assignment page to create your cimw105 notebook. Canvas also has a to do list and a calendar which are good to follow
Checklist Completion Steps:
- Complete all five orientation steps at https://cim.saddleback.edu/de
- Watch the most recent orientation intro Web-casts on Canvas,
Nota Bene, Assignment name, Canvas videos/Web-casts
and Canvas Quizzes always have the same three names, in these case they are all labeled as Intro.
- If you didn't take Canvas Quiz 0 Intro, take it now. Note you can complete entire course just using canvas - either links works https://canvas.saddleback.edu or https://cim.saddleback.edu/canvas .
- 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. Canvas quizzes and videos also follow the exact same top down organization. You need to work top down on assignment and watch the corresponding Canvasvideo and take quizzes when the assignment page tells you to.
- 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 Canvas's Welcome announcement page
submit tutorial.sql Basic SQL Introduction
Basic introduction to SQL, RDMBS, queries, submit and phpMyAdmin, develop skills at using three browser tabs at once: phpMyAdmin, video, submit, and optional DB scripts.
Method of Evaluation: submit tutorial.sql (10 points), and complete Canvas quiz tutorial.sql (10pts) - phpMyAdmin
Checklist Completion Steps (duplication of Canvas module):
- Updated Viewgraphs for 2020 Webcasts 4 Slides Per Page or 2 Slides Per Page - Updated VG for 7/1/2020 Canvas Webcasts
- Print PDF viewgraphs four slides per landscape page (right^click link to save) or PDF two slides per portrait page
- Watch first three tutorial.sql Canvas Videos, (i. Database Video, ii. MySQL SQL Video, iii Overview 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 Canvas videos or webcasts (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 a final Canvas webcast tutorial.sql last 5 queries.
- submit tutorial.sql all ten queries.
- Take tutorial.sql Canvas 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 Canvas videos.
If you have submit errors you can wait until you complete the next assignment select.sql, which goes into identifying submit errors.
submit select.sql intro to Submit Auto Grader
Objective: Become proficient with reading submit error messages, and practice writing very basic SQL queries.
Method of Evaluation: submit select.sql (10 points), and complete Canvas quiz select.sql (10pts) - phpMyAdmin
Checklist Completion Steps:
- Watch the two Canvas select.sql videos, (i select 1st 2 queries, ii select.sql 2nd 2 queries)
- Optional watch the select.sql 2/12/2014 Canvas Web-Cast
- Take Canvas select.sql quiz
- submit select.sql, (10 Points), 10 select comments listed next.
- Click Canvas quiz grades tab - verify quiz grades
- Retake Canvas 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 table:fruit with price less or equal 1.0, order by ascending price
#S6 display contents of table:fruit
#S7 display table:fruit 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 Canvas 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 Canvas Web-Cast
- Take the Canvas select.sql quiz
- submit select.sql, (10 Points), the 10 select comments are listed above the detailed completion steps.
- Click Canvas quiz grades tab - verify quiz grades.
- Retake Canvas 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.
intermediate.sql - intermediate SQL
More advance introduction to SQL, data manipulation, intrinsics, and combining tables.
Method of Evaluation: submit intermediate.sql (20 points), and complete Canvas quiz intermediate.sql (10pts) - phpMyAdmin
Checklist Completion Steps:
- Should understand phpMyAdmin and submit.
- Prerequisite intermediate browsing skills, at this stage you should have a browser that has three-five tabs that open automatically to: database Creation scripts, phpMyAdmin, submit, Canvas and assignment or class site page. If not go back and re-watch the tutorial.sql submit video.
- Watch first 2 Canvas intermediate.sql videos (i Background, ii Data Manipulation)
- submit first 6 queries for intermediate.sql,
- Watch the last three Canvas videos (iii intrinsics, iv combining tables, v language interfaces)
- Submit all 20 queries for intermediate.sql
- Take the Canvas 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, (nota bena, phpMyAdmin import has access to the same scripts), phpMyAdmin, submit, Canvas 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 Canvas 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 Canvas 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 Canvas intermediate.sql quiz.
pmaIntro.sql phpMyAdmin Intro
Objective: Review basic SQL, and become proficient with phpMyAdmin
Method of Evaluation: submit pmaIntro.sql (15 points), and complete Canvas quiz pmaIntro.sql (15pts) - phpMyAdmin - premiere
Checklist Completion Steps:
- Watch first three phpMyAdmin pmaIntro Canvas videos (i MySQL RDBMS Intro, ii phpMyAdmin tutorial, iii first lucky 7 queries ).
- Submit the first lucky 7 queries pmaIntro.sql (15 points)- make sure file is well commented.
- Watch the next three Canvas videos for pmaIntro (iv DB Synchronization, v Multi-tables, vi sub-queries)
- Take the Canvas pmaIntro quiz.
- Submit the next 4 queries for pmaIntro.sql.
- Watch the final table join Video
- Submit the final 4 table join queries. pmaIntro.sql has 15 total.
- 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.
pmaView.sql Intro to SQL Views
- Create DB that is a good stepping stone for textbook's premiere database
- Learn about views - virtual Tables
Method of Evaluation: submit pmaView.sql (15 points), two Canvas quizzes pmaView 1st 2 videos and pmaView. - phpMyAdmin
Checklist Completion Steps
- Watch the first two Canvas 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 Canvas quiz pmaView first two videos.
- Watch the next two Canvas videos (iii Altering Tables, iv views)
- Submit pmaView.sql
- Take Canvas 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
pmaReset.sql review, DB Restore & Cloud Intro
Method of Evaluation: submit pmaReset.sql (12 points) and Canvas pmaReset quiz (10 pts). - phpMyAdmin
Complete Canvas pmaReset.sql videos are listed with previous pmaView.sql video set.
We will be using phpMyAdmin, import and export tabs.
Checklist Completion Steps:
- Watch the one Canvas pmaReset Videos (v Critical Review, and pmaReset webcasts vi deprecated 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)
submit pmaTrigger.sql 15 Points
Method of Evaluation: submit pmaTrigger.sql (15 points) and Canvas quizzes pmaTrigger (20 pts) - phpMyAdmin
Complete the on-line phpMyAdmin Trigger (covers constraints and triggers), You will be using phpMyAdmin application to create a database state solution for the fruit DB.
Checklist Completion Steps:
pmaRoutine.sql Advance Topics PLSQL, Transactions, & Events
Method of Evaluation: submit pmaRoutine.sql (13 points) and two Canvas quizzes pmaRoutine( 20 pts) and pmaTransaction( 20 points). phpMyAdmin
Complete the on-line phpMyAdmin Routine (covers PLSQL routines - procedures and functions, and transactions), You will be using phpMyAdmin application to create a database state solution for the fruit DB.
Checklist Completion Steps:
- Watch the first two pmaRoutine Canvas videos (i Routines - functions, ii Routines-Procedures)
- Complete Canvas pmaRoutine quiz & update submit for pmaRoutine.sql
- Watch the next three pmaRoutine Canvas videos (iii Transactions Rollback, iv Transactions Commit, v events)
- Complete Canvas pmaTransaction quiz and finish submit pmaRoutine.sql.
- Watch Canvas video vi pmaReport, refer to PMA Report assignment, for assignment details.
pmaReport - intro to SQL reports (due same time as pmaRoutine.sql)
Checklist completion step watch the Canvas video xi PMA Report, listed at the end of the canvas pmaTrigger videos section.
You will be using features of phpMyAdmin to complete the report.
Method of Evaluation:
E-Mail: to the saddleback.edu email E-mail me your final report using: email (15 points)
Your report can be in Open Office, libre office or MSWord (Saddleback student's may have free access to Microsoft/365) format (doc or docx) Google docs (your mysite account has access to G Suite) 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.
- Local SQL creation script you created in the updated restore point video (aka pmaReset.sql).
- Create a chart view of fruit names versus price.
|Pre-book total assignment points: 120 (Note Report Points are maintain on my Spreadsheet)|
Book Assignments Introduction
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, Canvas 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.
Chapter 1 p21.sql
Read Chapter 1;
Watch canvas textbook webcast - PDF Viewgraphs, 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 on-line Once you have completed the 10 questions on page 21 (aka you have hand written answers), take Canvas 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. Use phpMyAdmin for all testing.
- 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
- Watch Canvas Textbook webcast
- Read chapter 1
- Hand write the answers to the 10 updated questions extracted from page 21.
- Take chapter 1 Canvas quiz
- Take assignment one Canvas quiz
- Assignment one Canvas quiz has all SQL queries for submitting p21.sql, if you can submit p21.sql or wait until you cover chapter 3.
Chapter 2 p58.sql
Read chapter 2, use one of your Databases for fruit (such as DBusername) and other database for Premiere (such as DB1username. Once you have selected your premiere database, delete all existing tables and use phpMyAdmin import tab, (aka Import Tab, premiere → premiereCreate.sql). You can also download the create script at /home/premiere/public_html/premiereCreate.sql (Right^Click to Save), and then import your local copy using phpMyAdmin It is also advantageous to use the phpMyAdmin designer tab to create a graphic schema. We have an existing schema at https://cim.saddleback.edu/~premiere. After you have created the premiere database, 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.
Chapter 3 p87.sql
Read chapter 3, submit p87.sql phpMyAdmin
For p87.sql, tables already exists (pma import premiere) for submit complete these 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
- # display contents of PART table ordered by increasing price
- # what is the total value of all orders, display as "Top Line Revenue"
- what is price of most expensive part display as "Highest Priced Item"
- # display REP Last Name, Customer Name, Balance and credit limit, for all customers sort lexicographically by reps last name, then by customer name
- # display order number and order date of all orders associated with Juan Perez, order by order numbers descending
You can keep submitting until you have 10/10, if you don't fix one query at a time
Chapter 4 p125.sql
Write MySQL commands to solve questions 1..21 on page 125. Proceed each command with a comment specifying the question number.
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.
Chapter 5 p157.sql
Read Chapter 5; Chapter 5 on multiple tables is one of the hardest chapters; submit p157.sql, (20 Points) this consists of all Premiere Database queries, questions 1..20 pages 157-158, the following questions have further clarification:
- 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.
Chapter 6 p177.sql
Chapter 6 is relatively easy; submit p177.sql; - phpMyAdmin
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 create table 'NONAPPLIANCE' don't prefix table name 'NONAPPLIANCE' with your database - table fields are PART_NUM, DESCRIPTION, ON_HAND, CLASS and PRICE. These will have the same types as other tables have for these columns.
- # 2 insert into NONAPPLIANCE all items that are not class 'AP'; For this one you need to use insert select command., You will be inserting into the table NONAPPLIANCE, using a select from table: PART. Nota Bene submit cannot verify correct output for 1 and 2. Insert, create, update, delete have limited output so submit can not verify them. However, #2a (aka 3), will fail, if either 1 or 2 are wrong.
- #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 Canvas 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 credit will not hurt you)
- Consider completing the extra credit assignments
Extra Credit casinoNoJoin.sql
submit - casinoNoJoin.sql, - phpMyAdmin
Casino Database creation scripts, and drop table scripts, are on phpMyAdmin using import. You can also view creation scripts, data tables and graphic schema at https://cim.saddleback.edu/~premiere. This assignment can be completed as extra credit, even before you begin the book, but first, complete the pre-book introductory fruit assignments. As the name casinoNoJoin.sql implies, you don't join tables to complete the queries. You get 1 bonus point for every two correct queries.
You can also take the canvas Chapter 7 Database Administration quiz as extra credit.
Deprecated - will not be assigned
#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