tutorial.sql Assignment

(1)

tutorial.sql

Assignment

Complete tutorial.sql using phpMyAdmin

Web Based Standard for MySQL Access

All Web-Hosting Companies Provide phpMyAdmin

Its Not for Active Production Systems

The System for Testing – Development

2

tutorial Assignment Videos

•

MySQL Pre-tutorial – PHPMyAdmin: testing Database

• http://cim.saddlebck.edu/phpMyAdmin Most Recent Version

• http://cim.saddleback.edu/phpMyAdmin4.4 Video Version

• Deprecated Closed Caption

http://cim.saddleback.edu/phpMyAdmin3.5

• http://cim.saddleback.edu/~premiere (Import in PHPMyAdmin)

• Import fruit.sql to create fruit tables

•

Intro to PHPmyAdmin and tutorial.sql 1st _{5 Queries}

•

Video 2: Intro to Submit and Submit tutorial.sql 1st _{5 Queries}

• http://cim.saddleback.edu/submit

•

Video 3: 2nd _{Five Queries for tutorial.sql}

(2)

MySQL Pre

tutorial

Login to PHPMyAdmin

Select Your Database

Import fruit.sql – Creates Fruit Tables

4

4 Tutorial – PHPMyAdmin Import

Accomplished Goal Fruit Tables

1) Login to phpMyAdmin http://cim.saddleback.edu/phpMyAdmin

2) Username: sbusername, password Student Number/ID (not mysite pswd)

3) Select your database DB0username

4) Click Import tab,

5) Select premiere fruit.sql import file – will create these tables

Table: fruit

fruitID name price 1 apple 2.50 2 pear 3.00 3 banana 1.00 4 grpe .75 8 pluot 4.50 6 plum 1.25 Table: inventory fruitID quantity 1 100 3 500 2 100 1 250 2 200

(3)

5 Tutorial – PHPMyAdmin Import

After fruit.sql import

MySQL 1st

Tutorial

Goal Create First 5 Queries for

tutorial.sql

To Do This…

#T1 SQL Tabl for Show tables

#T2, #T3 Navigator for Displaying Contents

#T4 #T5 SQL Tab for Describe Query

(4)

7 Tutorial – 1st_{5 Queries}

Part 1: Tutorial Assignment Objectives



Five Queries to Complete w Comments

 #T1) show tables; #T2) select * from fruit;

 #T3) select * from inventory;

 #T4) describe fruit; #T5) describe inventory;

 Add # comment before each Query



Click SQL Tab,Write Query to Display Tables

• show tables;



Use Navigator Display Table Contents

• select * from tableName;



USE SQL Tab enter describe Tablename Query

• describe tableName;

8

#T1 Show Table Names Just Created

I.

Login to phpMyAdmin

http://cim.saddleback.edu/pma

II.

Select Your Database

III.

Click SQL Tab

IV.

Type Script show tables;

V.

Add # Comment

VI.

Optional Save Queries Locally using Text Editor

(5)

#T2,3) Display Content fruit, inventory

•

)

1. Left Nav-Bar Navigator 2. Click fruit Table

3. Notice Main Window Pane

4. Edit select * fruit;

5. Add Comment #T3 display… newline 6. Repeat for Inventory

10 10 Tutorial – 1st_{5 Queries}

#T4 & #T5)

Describe

tableName;

•

Describe table

name;

•

Also Click

Tab Structure

•

Add comments

(6)

Keypoint Summary Quiz

•

Can Save local version of tutorial.sq

Can Also use command line interface



PHPMyAdmin has become the defacto standard

for MySQL/Database Web-Based Interface

(T or F)?

•

Unravel the location of key information

(phpMyAdmin, database scripts, submit) don’t

let the Web Guide

You (T or F)?

submit

Skills to Memorize!!!

Learn to Set Browser Startup Pages

Always Let Web Guide You

Do Not Decipher Short-cuts

http://cim.saddleback.edu/submit

Ctrl^C Copy …. Ctrl^V Paste

(7)

13 Tutorial – Critical Browsing Skills

21

st

Century Browsing

•

Critical Skill Advance Surfer Level

•

Pick a Browser You don’t Use

 Chrome, Opera, IE, Safari, Konqueror

 FireFox, CyberFox (64 bit FirFox)

 I’m using Chrome minimum default Real Estate

•

Let Web Guide (Create Tab for 5 Key Sites)

 Open initial site either class site, blackboard, or DB

•

http://cim.saddleback.edu/cimw105

 Set Other Tabs: submit, premiere, blackboard, phpMyAdmin

•

Save Default Pages as Starting Home Page

14

Setting CIMW105 Browser

•

Install a new Browser (I’m using Chrome)

•

Go to Class Site http://cim.saddleback.edu/cimw105

•

Right^Click Submit Assignment Link Open in New Tab



Login Using your SBUsername, Student Number



•

Go Back Right^Click New Tab Premiere – DB Scripts



http://cim.saddleback.edu/~premiere

•

Video/Quizzes Right^Click new tab Blackboard Link

•

Testing DB Right^Click new tab PHPMyAdmin Link



CIM Login SBUsername, Student Number

(8)

I’m Using Chrome for Class

You can use whatever

•

Student Question ( I Just Open Chrome )

•

CyberFox is my main browser

Click Control Menu Setting Login to your gmail (android)

Click Set Pages

16

submit 24/7 Auto Grader

•

Creates the File tutorial.sql in your Home Folder

•

This is not your Web Site (public_html is a sub-folder)

•

Queries Executed using a default user/database

•

Make sure your Queries are Working

•

Submit is for Submitting not Debugging

•

May turn in Assignments Multiple Times (Due Dates Matter)

•

Last Submitted Assignment is Recorded

•

Pay Attention to Input/Output Specifications

•

On Error Displays Your Output Versus Solution

(9)

Submit Grading Table

•

List Current Grade, Date of Last Submission;

•

Due Date (is Usually Monday at 9AM)



Quizzes Generally Expire Sunday 11:55PM



25% Penalty up to 7 Days Late



50% Penalty After That



Don’t Resubmit Correct Assignment after Due Date

•

Semester May End before a Sunday



You cannot submit final assignments late

•

Blackboard Welcome Announcement Key Dates

18

Submit tutorial.sql – test First 5

•

Submit Link: Blackboard, Assignments, Premiere



•

Goal submit tutorial.sql first 5 Queries

•

Use Browser’s 5 Tabs

 Use PHPMyAdmin Console History copy (Control^C) queries then

 Paste (Control^V) them into submit editing pane

•

Ctrl^C Universal Copy

•

Ctrl^V Universal Paste

•

Can Wait to Fix Errors in next Video Set

(10)

Submit

Page

Assignment

Click Accordion to Open Click for Grade Table

Edit w VI or set Themes

Control^G Hot Key Grade

MySQL 2

nd

Tutorial

Create & Comment 2

nd

Five Queries

Add Them to Submit One at a time

(11)

21 Tutorial – 2nd_{5 Queries}

Outline MySQL 2

nd

Tutorial

•

Use Browser with 5 Tab Home Page

 Ctrl^C Copy and Ctrl^V Paste

 Use PMA for Queries 6..10 & Submit One at a time

 Copy and Paste from History can be tricky

•

Comments for 2

nd

5 tutorial.sql Queries

# T6 Display only fruit Names

# T7 Display Total Inventory Quantity for all Fruit # T8 Display Fruit Name w fruitID=1

# T9 Sum All Fruit in inventory w fruitID 1 # T10 display inventory verify #7 & #9 correct

22

#T6) Select name from fruit

;

_• _Login

phpMyAdmin • Select DB SB0username • Select Table fruit • Start SQL Tab • Select * and (click name left colum)

• Go

• Add Comment

• Terminate query with ;

• Copy & Paste into submit

(12)

Accent, Single, Double Quote

•

Use ` accent for column, table &/or DB names

 On Keyboard Accent is Upper Left Corner

•

Use ' Single Quote Literal values

 Keyboard Right Side Shift Double Quote

•

Use

"

Double Quotes : Character String

•

#Display costs as pennies for fruit w ID of 1

select `price` * '100' as "pennies"

from fruit where `fruitID` = '1';

•

PHPMyAdmin Inserts Accents, Quotes Automatically

•

Critical when Variables or Views Exist

 Universal Single Quote Literal Quote

24

Write Query #T7 and #T8

7)

#T7 Display Total Inventory Quantity for all Fruit

select sum( quantity ) from inventory;

8)

#T8 Display Fruit Name w fruitID=1

select name from fruit where fruitID=1;

Total 1150

Table: fruit

fruitID Name Price 1 Apple 2.50 2 Pear 3.00 3 Banana 1.00 4 Grpe .75 8 Pluot 4.50 6 Plum 1.25 Table: inventory fruitID quantity 1 100 3 500 2 100 1 250 2 200

(13)

Write Last 2 Queries and Save

9)

#T9 Sum All Fruit in inventory w fruitID 1

select sum( quantity ) from inventory where fruitID=1;

10)

#T10 look at output from query, then

#make sure this comment verifies 7 and 9

select * from inventory;

MySQL Workbench – PC/Mac/L

inux GUI Application

 Install a Local Copy of MySQL & WorkBench

 I don’t debug Failed Installation

 But Vary Interface Frequently & it’s a Configurable Workspace

•

Supporting PHPMyAdmin Web Based Interface

•

Also Web-Casts NetBeans SQL Editor Programmers Interface

(14)

Tutorial.sql Summary

Control^C Copy Control^V Paste

Control ^G short cut key grade submit

Show Tables;

Select * from fruit Where fruitID=1;

Select * from fruit Where fruitID= 5;

How Would you list contents of table:inventory?

28

Accent Mark (Key Below ~)

Review in tutorial.sql Videos

•

Enclose Field & Table Names in Accent

 Want to use reserved keyword as Table name

 Select * from `select`; select * from `orders`;

•

If Not MySQL May Try Variable Expansion

•

When You Click to Create Query

 PHPMyAdmin Does This Automatically

•

View Present Must Delimit Table/Field Names

(15)

intermediate.sql assignment

Get intermediate.sql http://cim.saddleback.edu/database Links Also Assignments and Blackboard

Save File Locally

Import or Cut and Paste One Query at a Time Tell me What each SQL cmd does

You do this by adding a comment before Each line, I did this for the first Query. submit intermediate.sql

Your Version Must have # Comments Before Each Query

30

Assignment File Name MOVED TO

SELECT SUBMIT

•

Filenames Matters (tutorial.sql not Tutorial.sql tutorial.sql.txt)

•

Always Include # or – comments

•

Blanks Lines also increase readability

•

But Don’t Intermingle #, -- and/or ; inside Query Statements

•

Always end SQL query with ; semicolon Not Always Evident with PHPMyAdmin

•

Main Link http://cim.saddleback.edu/submit • Actual Location cim.saddleback.edu/~tdedonno/submit

• Links for Submit Everywhere, assignments, blackboard, etc.