mysql query lab iii 25 points

MySQL Query Lab III25 Points

MySQLTU Database 3343

Dr. Thomas Hicks

You May Do This Lab On Your

Own Personal Computer Or On Your Database

System

Your Name

_________________

Name This Presentation:Tom-Hicks-MySQL-Query-3.pptx

{Substitute Your First & Last Names}

Computer Used Is

_________________

{My Personal Computer or CS-??}

Your Slide Presentation Should

Include Screen Captures That Are Cropped – Clear &

Easy To Read

If This Bar Is Light Blue – It Is Info That You Should Read!

Helpful HintThere Will Often Be A Colored Title Bar At The Top Of A Slide

If This Bar Is Red – You Will Have Screen Captures To Replicate And/Or Questions To Answer

If This Bar Is Green – You Will Have To Create Original Screen Captures And/Or Questions To Answer

Use The Snippping Tool To Help You With Your Screen Captures

Use Both The Rectangular Snip & The Window Snip As Is Appropriate!

Trying To Capture SHOW FIELDS Command:

Use Good/Neat/Readable Captures Crop Your CapturesDon’t Include Extraneous Stuff! This Is Not Good!

SELECT …. First Line

FROM … Next Line

WHERE … Next Line If There

ORDER BY … Next Line If There

Use The Document Query Format For All Slides!

Document Query Format

Use The MySQL Command Line

Client

Answer All QuestionsReplace Screen Captures

The Basics Of SQL Are Generic

Many Implementations Of MySQL Offer Additional Enhancements

(i.e. MySQL Might Have A String Function, Or Mathematical Function, That Might Not Be

Available In MSSQL)

You Can Generally Add Your Own Custom Functions To Most SQL Environments.

Your Slide Presentation Should

Include Screen Captures That Are Cropped – Clear &

Easy To Read

If This Bar Is Light Blue – It Is Info That You Should Read!

Helpful HintThere Will Often Be A Colored Title Bar At The Top Of A Slide

If This Bar Is Red – You Will Have Screen Captures To Replicate And/Or Questions To Answer

If This Bar Is Green – You Will Have To Create Original Screen Captures And/Or Questions To Answer

Trying To Capture SHOW FIELDS Command:

Use The Snippping Tool To Help You With Your Screen Captures

Use Both The Rectangular Snip & The Window Snip As Is Appropriate!

Use Good/Neat/Readable Captures Crop Your CapturesDon’t Include Extraneous Stuff! This Is Not Good!

SELECT …. First Line

FROM … Next Line

WHERE … Next Line If There

ORDER BY … Next Line If There

Use The Document Query Format For All Slides!

Document Query Format

Use The MySQL Command Line

Client

Answer All QuestionsReplace Screen Captures

The Basics Of SQL Are Generic

Many Implementations Of MySQL Offer Additional Enhancements

(i.e. MySQL Might Have A String Function, Or Mathematical Function, That Might Not Be

Available In MSSQL)

You Can Generally Add Your Own Custom Functions To Most SQL Environments.

Structured Query Language

ConfigureThe Command

Window

Getting Ready To Change Window Properties

Using the mouse, Right Mouse Click On The Top Of The Window and Select Properties

Set The Window Screen Text To WhiteSet Your Background To Red, Black, Green, Or Navy

Do Not Select Maroon Background

Set The Window Properties As Illustrated Below

Set The Window Screen Text To WhiteSet The Background To Red, Black, Green, Or Navy

Do Not Select Maroon Background

Put File Library-Hicks.sql On Your DesktopShow The Size In General Properties

LOADTHE

Reload The Library-Hicks-1.sql

Don't Necessarily Expect Your Output To Be Exactly Like Mine!

The Queries Will Work. Check Your Own Output!

Start MySQL AdministratorConfigure & Login

Select CatalogsMake Sure That Your Library Database Is Included In The List

Select Restore. Select Another Schema. Select Your Library??Push Open Backup File

Select Library-Hicks.sql From The Desktop

Start The Restore

Select/Open A

Database

The Open Database Is Often Called The Default Database

All Of The Fields In Your Database Will Match Those In My Database 100%

I Reserve The Right To Use Slightly Different Data In An Effort To Ensure That You Carefully Examine Your Own Queries!

All Of The Relationships In Your Database Will Match Those In My Database 100%

All Of The Table & Field Names Will Match 100%

Show All Databases On Your System. Open Your Library?? Database. Only One Database May Be Open At A Time.

Replace The Capture Below!

Display TheDatabase

Tables

Show Tables (Open Database)

Show All Tables In The Open Database.Replace The Capture Below!

Display TheDatabase Table

Layout

(Fields, DataTypes, Etc.)

1] Open Your Library?? Database2] Display The Tables In The Open Database3] Show The Table Layout For Users Include The Capture Below!

1] Show The Table Layout For TeamMembers Include The Capture Below!

Display A Query List, Called Names, That Contains All Of The User First Names And All Of The Server TeamNames.

Users & Teams

Start To Form A Relationship

A User May Be A Member Of One Or More Teams. Teams Shall Be A Relationship Between The Users & The Servers. This Will Be Explained More Later In The Presentation.

Entity-Relationship

E-R Diagrams

Do The Following Google Search

Google Had About _____________________________________ Results For This Search.

Go To The Wikipedia Entry. Read It Completely.As Always, The Things You Read In Your Homework Should Be Included In The List Of

Things You Study For Your Exam/Quizzes. Print Them If You Like.Replace The Capture Below!

Entity

In Your Own Words: “What Is An Entity?”

An Entity Is _______________________________________________________________

_________________________________________________________________________Suppose We Had Two Entities: Computer & Programmer. Include Two Sketches/Diagrams (Below) To Represent These Entities:

Relationship

In Your Own Words: “What Is An Relationship?”

A Relationship Is _________________________________________________________

_________________________________________________________________________

Suppose We Had Two Entities: Computer & Programmer. Suppose The Relationship Between The Computer And The Programmer Was Called Program. Include An E-R Diagram Below To Represent This Relationship.

Entity-relationship diagrams don't show single entities or single instances of relations. Rather, they show entity sets and relationship sets.

Attribute

In Your Own Words: “What Is An Relationship?”

An Attribute Is _________________________________________________________

_________________________________________________________________________

Suppose We Had An Entities: Company (whose Attributes Are Name, BusinessType, Owner, ID, and Phone. Sketch The E-R Diagram For Company (Always include the attributes)

Extended E-R Features & Primary KeyThere Have Been Several Extensions Since The Original Chen Publication

Some Programmers Underline Those Attributes That Form The Primary Key – Some Use A Double Ellipse

http://www.sis.pitt.edu/~valeriab/1022-spring08/Chapter6.pdf

E-R With An ISA

Questions #1 – Use Red Ink For Your Answers1] _____________________________ _____________________________ ER is an acronym for _?_

2] A_____________________________ C_____________________________ In software engineering, an entity-relationship model is an _?_ and _?_ representation of data.

3] D_____________________________ Entity-relationship modeling is a D_?_ modeling method, used to produce a type of conceptual schema or semantic data model of a system, often a relational database, and its requirements in a top-down fashion.

4] P_____________________________ C_____________________________ The E-R Modeling Technique was developed By P_?_ C_?_ (LSU) in 1976.

5] _____________________________ Diagrams created with the E-R Model are called Entity-Relationship Diagrams, E-R diagrams, or _?_.

6] _____________________________ A(n) _?_ is something that has a distinct, separate existence, though it need not be a material existence; it may be a physical object such as a house or a car, an event such as a house sale or a car service, or a concept such as a customer transaction or order.

Questions #2 – Use Red Ink For Your Answers7] I_____________________________ An entity-type is a category. An entity, strictly speaking, is an I_?_ of a given entity-type. There are usually many instances of an entity-type. Because the term entity-type is somewhat cumbersome, most people tend to use the term entity as a synonym for this term.

8] _____________________________ The symbol used to represent an entity in an E-R Model is a(n) _?_. 9] _____________________________ The part of speech often associated with an entity is the _?_. 10] Suppose the entity is an artist. Sketch the E-R diagram to represent this artist.

11] Suppose the entity is a song. Sketch the E-R diagram to represent this song.

12] _____________________________ A _?_ captures how two or more entities are related to one another.

Questions #3 – Use Red Ink For Your Answers13] _____________________________ The symbol used to represent a relationship in an E-R Model is a(n) _?_.

14] _____________________________ The part of speech often associated with an relationship is the _?_.

15] Sketch the E-R Diagram for owns relationship between a company and a land. Land might be owned jointly between two companies.

16] Sketch the E-R Diagram for worksfor relationship between a company and a people. A person might work for two companies.

17]. _____________________________ Entities and relationships can both have _?_. Examples: an employee entity might have a Social Security Number (SSN).

Questions #4 – Use Red Ink For Your Answers18] _____________________________ The symbol used to represent an attribute in an E-R Model is a(n) _?_.

19] _____________________________ The parts of speech often associated with an attribute are the _?_ and the _?_.

20] P_____________________________ K_____________________________ Every entity (unless it is a weak entity) must have a minimal set of uniquely identifying attributes, which is called the entity's P_?_ K_?_.

21] _____________ _____________ The E-R Diagram above has _?_ Entity and _?_ Attributes;

22] __________________________________________________List the fields/attributes that form the primary key for Account.

Questions #5 – Use Red Ink For Your Answers

23] __________________________________________________List the fields/attributes that form the primary key for Employee (if any) 24] ______________________________Employee is a _?_ {Strong/Weak} Entity. 25] __________________________________________________List the attributes of Employee. 26] __________________________________________________List the attributes of Mechanic.

27] _______________________________________________________List the fields/attributes that form the primary key for Client (if any) 28] ______________________________Client is a _?_ {Strong/Weak} Entity. 29] _______________________________________________________List the attributes of Client. 30] _______________________________________________________List all of the Entities

31] _______________________________________________________List all of the Relationships 32] __________ {T/F} Relationships do not have attributes. 33] _______________________________________________________List the attributes of Sells.

Do A Google Search For A Commercial Graphical SoftwarePackage Designed To Do E-R Diagrams

Include Both A Picture Of The Box & The PriceInclude The Screen Capture(s) Below!

Entity-Relationship

E-R Diagrams

Extra CreditFind A Free (Not Just A Trial) Graphical Software

Package Designed To Do E-R DiagramsInclude The URL & Title

Install On Your System – Open - Include The Screen Capture(s) Below!

Table Users Has 8 Fields & 15 RecordsCreate An E-R Diagram For Users

Maybe Use Your Free Software? Use Power Point Tools If You Like!Include The Sketch It Below!

Table Users Has 2 Fields & 21 Records

SIMPLE JOIN

FROM Tab1, Tab2

1] Display the Join Of Users & Team Members. Display Page 1 Do This Query On Your System!

Note That The Display Has 10 Fields (8 For Users & 2 For TeamMembers)

Note That The First Team Member Record (UserID = 6, TeamID = 1) Has Been Appended To Each & Every One Of The 15 User Records.

Note That The Second Team Member Record (UserID = 2, TeamID = 1) Has Been Appended To Each & Every One Of The 15 User Records.

1] Display the Join Of Users & Team Members. Display Page 2

Table B = TeamMembers (21 Rows-Records)Table A = Users (15 Records)

Join Users, TeamMembers (21 x 15 = 315 Records)

Table B = TeamMembers (2 Fields)Table A = Users (8 Fields)

Join Users, TeamMembers (8 + 2 Fields)

In Reality, This Join As-Is, Is Pretty Worthless!

Extracting Relationship Data!The Join Makes Sense When We Include The

Relationship In The Query!Replace The Screen Capture Below With Yours!

SELECT …

Fully Qualified Field Names

Fully Qualified Fields Descriptions Include Both The Table Name & The Field Name (Separated By A Period)

Note that the U.ID = T.UserID!We Often Want To Use ‘AS’ To Create More Meaningful Discriptors In

The Title Line Of Our Queries

We Generally Restrict The Use Of Fully Qualified Fields To Those Times That They Are Absolutely Necessary!

Fully Qualified Names Are Necessary Only When Two Tables, Involved In A Join, Have The Exactly The Same Field Name!

Several Of Our Tables Have A Field Name Of ‘ID’. If A Query Joins Two Such Tables, It Is Necessary To Fully Qualify Which ID You Wish To Be Involved In The Query – It Is Not Necessary To Fully Qualify The Other Unique Field Names In

That Same Query!

33] __________ {T/F} We should always use fully qualified Field Names in all queries.

34] Write a Fully-Qualified Query that will display the ID, Name, and Phone of all Clients.SELECTFROM

34] __________ {T/F} We will normally use fully qualified Field Names in all queries that involve a join.

35] In your own words, tell when it is necessary to use fully qualified field names._________________________________________________________________________

_________________________________________________________________________

IMPLEMENTING COMPLEX/LONG

QUERIES

Helpful Hint (Slide 1)I Am Guessing That Some Of You May Have Made A Mistake Typing In

The Previous Query! (I Did!) . Complex/Long Queries Are Often Difficult To Get Exactly Right The First Time.

1] I Suggest You Enter The Query Into Notepad (or some other text editor). Copy It To The Clipboard (ctrl-c)

Helpful Hint (Slide 2)

2] Right Mouse Click Any Where In Your Command Window Select Paste

Helpful Hint (Slide 3)

3] If It Works Great! If Not, Modify The Notepad Text And Try Again! (This Is Much Less Stressful Than Trying To Correct It Inside The MySQL Environment)

SELECT …

Fully Qualified Field Names

(Continued)

1] Display The ID, FullName, UserName, And TeamID For All Users (Fully Qualified Fields)

Replace The Capture Below With Yours!

Whenever I Provide You With A Capture Of The Query Table Titles (such as the one below), You Are To Match Those Titles (EXACTLY i.e. U-FullName, etc.) In Your Capture!

Database Development

Walk Through Some Of The Thought Process

Start Of A Simple Application

Computer Science Library Where Dr. Hicks

Server Teams Can Check Out Books

Database Development

We Have Some UsersEntity

There Are Lots Of Programs That Help Support & Automate Design To Implementation

This Is Not The Most Expensive – Look For These Tools If You Are Doing This For A Living!

------------- Systems Analysis Phase -------------

At First People Used E-R Diagrams(And Some Still Do)

Administrator

UserName

FullName

------------- Systems Analysis Phase -------------There Are Some Software Packages That Will Prompt You For Data-Typing As You Create The Entities In The E-R Diagram& Generate A Database Table (Expensive & Database Specific)

FirstLast

AdministratorUserName

FullName

------------- Systems Analysis Phase -------------E-R Get Clutsey When We Have 30-80 Fields ??

You Often Have To Turn Around And Enter Redundant Info Into The Database!

-- Effort & Time Intensive --

Administrator

UserName

FullName

------------- Systems Analysis Phase -------------There Are Many GUI’s That Make Design & Re-Design

Much Easier & Faster Than Corresponding SQL.

------------- Systems Analysis Phase -------------A Lot Of Users Are Creating The Table During The Design StageKnowing That There Will Often Be Changes & Revisions

These can Be Done Easily With The GUI - And GUI’s Often Have View!

In One Click We Can Generate The Users Table In A View!

Navicat

I Have A License For Your Server – But Not

For Your Personal Computer

Google Search: Navicat (Include Databses SupportedVersions & Pricing)Replace The Screen Capture Below With Yours!

NAVICAT CONFIGURATION

&Basics

Google Search: Navicat (Include Databses SupportedVersions & Pricing)Replace The Screen Capture Below With Yours!

Start Navicat (Yours May Be Some Different)Install Navicat On Your Home System If You Desire

Replace The Screen Capture Below With Yours!

File -> New Connection

Create A Navicat Connection & Test Replace The Screen Capture Below With Yours!

The Navicat InterfaceReplace The Screen Capture Below With Yours!

To Open The Table Designer For Users Right-Mouse Click On Users Select Design

The Navicat Table Design InterfaceReplace The Screen Capture Below With Yours!

To Open Graphical Table Interface For Users Double Click On Users

To A New View For The Library?? Database Click On View (Connections Panel Left) Push New View (Right)

Select The View Builder TabUsing The Mouse, Drag The Users Table To The View

UsersA User Can Be A Member Of One, Two, Or More Server Teams.

User Tom Hicks Is Going Is An “Access-Only” Member Of All Server Teams.

My Table Users Has 8 Fields & 15 RecordsInclude The Sketch It Below!

1] Display The Number Of Users2] Display The Number Of Users That Have An ‘H’ As The First Two Letters In The LastName 3] Display The Number Of Users Whose First Name Is ‘Tom’ Include The Capture Below!

1] Display All Of The First, Last, And E-Mail In UpperCase In Ascending Alphabetical Order By E-Mail

Include The Capture Below!

1] Display All Of The First, Last, Username, And E-Mail In LowerCase In Descending Alphabetical Order By FullName

ServersA Single Server Will Have One Team Assigned To It!

Table Servers Has All Of The Server Network Configuration Information.

My Table Servers Has 7 Fields & 7 RecordsInclude The Sketch It Below!

1] Display The Number Of Servers 2] Display The Number Of Servers That Have A ‘CS’ As The First Two Letters In The ServerName 3] Display The Number Of Servers Whose TeamName Starts With ‘S’ Include The Capture Below!

1] Display The ID, TeamName, and ServerName Of Servers 2] Display The ID, StaticIP, SubnetMask, and Gateway Of All Servers

1] Display The ID, StaticIP, SubnetMask, and Gateway Of All Servers Whose TeamName Starts With An ‘S’

1] Display All Of The Team Name In UpperCase In Ascending Alphabetical Order.

1] Display All Of The Team Name In LowerrCase In Descending Alphabetical Order.

TeamsTeams Is A Relationship Between Users and Servers.

Some Folks Would Prefer That We Name This Table “Administers”: John Administers Server CS-1.

I Am Trying To Emphasize Our Server Teams.

My Table Teams Has 2 Fields & 21 RecordsReplace The Sketch It Below!

Relationship

Change Your Window Layout To Match That Below! Replace The Capture Below!

Execute Query SELECT *FROM Users, Teams, Servers

The Sketch Below Shows Part Of The Query Title Line. Screen Capture The Rest Of The Query Title Line & Include It Below. Use Multiple

Captures If Necessary To Make It Readable.

Join Users, Teams, ServersTable Users Has ____ FieldsTable Teams Has ____ FieldsTable Servers Has ____ FieldsQuery Has ____ Fields/Columns

Continue With Query SELECT *FROM Users, Teams, Servers

Replace The Sketch Below With One From The Bottom Of Your Query That Shows The Number Of Rows.

Join Users, Teams, ServersTable Users Has ____ RecordsTable Teams Has ____ RecordsTable Servers Has ____ Records Query Has ____ Records/Rows

Change Your Window Layout To Match That Below! Replace The Capture Below!

Display The First, Last, TeamName, And ServerName for Everybody!A Meaningful Query

It would be much more efficient to use an Inner Join; we will get to that later.

CHANGETHE

Reload The Library-Hicks-2.sql

Do You Understand

Once Again We Want To Execute Query SELECT *

FROM Users, Teams, Servers

But The Data Has Changed. Don't Do It Yet!

Join Users, Teams, ServersTable Users Now Has ____ FieldsTable Teams Now Has ____ FieldsTable Servers Now Has ____ Fields

I Am Guessing That The Query Will Have ____ Fields

But The Data Has Changed. Don't Do It Yet!

Join Users, Teams, ServersTable Users Now Has ____ RecordsTable Teams Now Has ____ RecordsTable Servers Now Has ____ Records

I Am Guessing That The Query Will Have ____ Records

Do The Query Now!

Join Users, Teams, Servers

The Query Has ____ FieldsThe Query Has ____ Records

_______ My Guesses {were/were not} correct

1] Display The First, Last, E-Mail, Phone, ServerName, and URL Users Whose ID <= 5 In Order By FullName

1] Display The Servers.ID, ServerName, TeamName, First, and Last Of All Systems In Order By TeamName.

1] Display “No People On The Team Whose ServerID = 4 Is ???” Use Count(*)

Database Generally

ImpliesRelationships

How Are The Library Tables RelatedReplace The View Below With Yours!

1] List the following information about all the authors that start with an HYour query is top generate exactly the same output as mine. Don't Delete My Output!BA is BookAuthors - OrdrBy A.Name

Include The Capture Below - Include Query and Output In Capture

It Looks Like Hector May Have Authored Two Books?

1] List the following information about all the authors that start with an HYour query is top generate exactly the same output as mine. List 25 characters of the Title. Don't Delete My Output! OrdrBy A.Name

Include The Capture Below - Include Query and Output In Capture Hector

Authored Two Books?

1] List the following information about books written by Joel MurachYour query is top generate exactly the same output as mine. List 25 characters of the Title. Don't Delete My Output!

1] List the following information about all of the authors of book ID = 23 Your query is top generate exactly the same output as mine. List 25 characters of the Title. Don't Delete My Output! - OrdrBy A.Name

1]Harvey's name has a leading blank. Perhaps there are others. Create a query that will get rid of leading and trailing blanks in all Author records.

mysql query lab iii 25 points

window screen text

use goodneatreadable

window snip

mysql command line client

implementations of mysql

window properties23

colored title bar

document query format11use

Documents

mysql query browser

mysql essentials - nikos...

advanced mysql query optimizations

mysql query tuning › sites › default › files ›...

mysql query browser - itistulliobuzzi.it query... · mysql...

mountain moodle moot -- mysql server & query tuing

improving mysql/mariadb query performance through...

mysql and query browser quick start and query... · 1 using...

mysql query browser · mysql query browser this is a...

mysql tutorial introduction to database. introduction of...

mysql query lab ii 25 points

mysql query cache

06 php mysql connect query

mysql query optimization (basics)

mysql query optimization

zurich2007 mysql query optimization

mysql query tuning

introduction to mysql query tuning - for dev[op]s ·...

mysql query optimization · © 2019 percona. 1 peter...

mysql query optimization