1

MySQL Query Lab II25 Points

MySQLTU Database 3343

Dr. Thomas Hicks

2

You May Do This Lab On Your

Own Personal Computer Or On Your Database

System

Your Name

_________________

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

{Substitute Your First & Last Names}

Computer Used Is

_________________

{My Personal Computer or CS-??}

5

Your Slide Presentation Should

Include Screen Captures That Are Cropped – Clear &

Easy To Read

6

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

7

Use The Snippping Tool To Help You With Your Screen Captures

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

8

Trying To Capture SHOW FIELDS Command:

Good!

9

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

BAD!

10

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

11

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.

13

Reload The Library-Hicks

(Be Sure To Load The Copy Of The File

Attached With This Presentation)

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

The Queries Will Work. Check Your Own Output!

14

Getting Ready To Change Window Properties

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

15

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

Do Not Select Maroon Background

16

Set The Window Properties As Illustrated Below

17

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

Do Not Select Maroon Background

18

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

19

Start MySQL AdministratorConfigure & Login

20

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

21

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

22

Select Library-Hicks.sql From The Desktop

23

Start The Restore

24

Close

25

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%

26

SHOW DATABASES

27

Display The List Of All Databases;Replace The Capture Below!

28

CREATEDATABASE . . . (Database)

29

1] Display The List Of All Databases;2] Create A New Database, Called Junk3] Display The List Of All Databases; Replace The Capture Below!

30

1] Display The List Of All Databases;2] Create A New Database, Called DrEggen3] Display The List Of All Databases; Replace The Capture Below!

31

1] Display The List Of All Databases;2] Create A New Database, Called Practice?? (Replace ?? With Your Initials)3] Display The List Of All Databases; Replace The Capture Below!

32

MySQL Data-Types

33

MySQL Character Data-Types

34

MySQL Integer Data Types

35

MySQL Floating Point Data Types

36

MySQL Date Data Types

37

CREATE TABLE …(

(Variable Name) Data-Type, (Variable Name) Data-Type,...

(Variable Name) Data-Type);

38

1] Display The List Of All Tables In The Default/Open Database;2] Create A Table Called Players (First -15 chars & Last – 20 chars)3] Display The List Of All Tables In The Default/Open Database; 4] Display The Layout Of Table Players; Replace The Capture Below!

39

1] Display The Table Listing;2] Create A Table Called Books (BKName -30 chars No int)3] Display The Table Listing; 4] Display The Layout Of Table Books; Replace The Capture Below!

40

1] Display The Table Listing;2] Create A Table Called Ranks(Description -40 char, ID-int)3] Display The Table Listing; 4] Display The Layout Of Table Ranks; Replace The Capture Below!

41

1] Display The Table Listing;2] Create A Table Called Officers (FullName -40 char, RanksID –int, SerialNo-16 char )3] Display The Table Listing; 4] Display The Layout Of Table Officers ; Replace The Capture Below!

42

1] Display The Table Listing;2] Create A Table Called Students (ID-Auto , First-15 char , Last-15 char)3] Display The Table Listing; 4] Display The Layout Of Table Ranks; Replace The Capture Below!

43

1] Display The Table Listing;2] Create A Table Called Parts(ID-Auto , Description-30 char, SupplierID-int, QtyInStock-int, QtyToStock-int, 15 char, RetailCost-float)3] Display The Table Listing; 4] Display The Layout Of Table Parts; Replace The Capture Below!

44

INSERT INTO Table (field, field, …)VALUES (str/#/etc)

45

1] Display The Books Table Layout; 2] Add ‘Introduction To MySQL Database’ & 1 to Table Books3] Display all of the Books Table Information. Replace The Capture Below!

46

1] Display The Books Table Layout; 2] Add ‘Introduction To MySQL Database’ & 1 to Table Books3] Display all of the Books Table Information. Replace The Capture Below!

47

1] Add ‘SQL Web Programming’ & 2 to Table Books (With Out The INSERT INTO List)2] Display all of the Books Table Information.

If you do not specify the list, the assumption is that you are going to add fill each and every field in the table in the same order as the table listing.

Replace The Capture Below!

When You Follow The Exact Field Order

& Include All FieldsYou Need Not Include

The Field Listing!

48

1] Add ‘Software Engineering’ to Table Books (without providing An ID)2] Display all of the Books Table Information.

Since Field No can be Null, it is optional; it is not required. When an INSERT INTO Query is executed, some value will be placed in each and every field; the default value is used for any missing optional fields. No is optional; when it is missing, the default value, NULL, will be substituted automatically.

Replace The Capture Below!

When NULL = YesThe Field Is Optional!

49

1] Add 5 to Table Books (without providing An BKName)2] Display all of the Books Table Information.

Since Field BKName can be Null, it is optional; it is not required. When an INSERT INTO Query is executed, some value will be placed in each and every field; the default value is used for any missing optional fields. BKName is optional; when it is missing, the default value, NULL, will be substituted automatically.

Replace The Capture Below!

When NULL = YesThe Field Is Optional!

50

1] Add (6 & “Networking”) to Table Books (in this order)2] Display all of the Books Table Information. 3] Display the number of records in Books; assign it to NoBooks. Include The Capture Below!

51

1] Add (7 & “C++”) to Table Books 2] Display all of the Books Table Information in order by BKName; use all capitals in the display.3] Display the number of complete records in Books; complete records have no null fields. Include The Capture Below!

52

1] Display The Ranks Table Layout; 2] Show the Insert Into Commands To Add The Following Data3] Display all of the Ranks Table Information. Include The Capture Below!

53

1] Display The Ranks Table Layout; 2] Show the Insert Into Commands To Add The Following Data Include The Capture Below!

54

1] Do Whatever You Need To Fill OfficersAs Illustrated (you need not screen capture) 2] Display all of the Ranks Table Information Include The Capture Below!

__________________________ What Rank Is Dr. Myers?

55

1] Display The Student Table Layout; 2] Add Student Mickey Mouse3] Display all of the Students Table Information. Replace The Capture Below!

The Auto Field Will Never Be NULL; It Is Automatically Generated – Starts With 1Values Never Re-Issued!

56

1] Display The Student Table Layout; 2] Add Student Minney Mouse & 23] Display all of the Students Table Information. Replace The Capture Below!

57

1] Display The Student Table Layout; 2] Try To Add Student Roger Rabbit & 2 Won’t Load Duplicate!3] Add Student Roger Rabbit & 4 Will Assign Advanced! 4] Display all of the Students Table Information. Replace The Capture Below!

58

1] Display The Student Table Layout; 2] Add Donald Duck (Wonder What Value Assigned- Did not go back and do 3!)3] Display all of the Students Table Information. Replace The Capture Below!

59

1] Display The Student Table Layout; 2] Add Daffy Duck 3 (We can go back and fill in unassigned values)2] Add Yosemite Sam (Continues Counting)3] Display all of the Students Table Information. Replace The Capture Below!

60

1] Do Whatever You Need To Fill PartsAs Illustrated (you need not screen capture) 2] Display all of the Parts Information

Include The Capture Below!

61

1] Display all of the Parts Information In Order By Description Include The Capture Below!

62

1] Special Order Items Have A Quantity To Stock Of 0; Display all of the information about Special Order Items.2] We Need To Order Parts. Do The Query Necessary To Display:

Include The Captures Below!

63

1] When The QtyInStock Is Less Than The QtyToStock, Additional Inventory Needs To Be Ordered. Do The Query Necessary To Display:

Include The Capture Below!

64

1] The investment in Tennis Balls is $(10*6) and the Investment in Soccer is (2*19.50), etc. 2] Show the Total Invested (raw data); Assign it to Total Invested! 3] Show the Total Invested (2 digits to right of decimal); Assign it to Total Invested! 4] Show the Total Invested (Dollar Sign and 2 digits); Assign it to Total Invested! Display your queries as well as the results. Replace The Capture Below!

65

1] Display all of the Parts whose ID’s are Even.2] Display all of the Parts whose ID’s are Odd.

Include The Capture Below!

66

CREATE BACKUPS

WITH MySQL

ADMINSTRATOR

67

Create, Save, & Execute A New Backup For The Library Project Replace The Capture Below!

68

As You Execute Your Backup, Save It In C:\Temp Replace The Capture Below!

69

Create, Save, & Execute A New Backup For The Practice Library Project Replace The Capture Below!

70

As You Execute Your Backup, Save It In C:\Temp Replace The Capture Below!

71

Open Directory C:\Temp & Show The Files As Illustrated Below

Replace The Capture Below!

Explain How To Read The Date From The FileName _____________________________

_______________________________________________________________________

72

ALTER TABLE … (Table) RENAME … (NewTable)

73

1] Display The Table Names2] Change The Name Of Table Users To DBAdmin3] Display The Table Names4] Change The Name Of Table DBAdmin To Users Replace The Capture Below!

ChangeThe TableName

74

1] Display The Table Names2] Change The Name Of Table Books To Texts3] Display The Table Names4] Change The Name Of Table Texts To Users Include The Capture Below!

75

ALTER TABLE … (Table) CHANGE … (Field) (Field) (Data-Type)

76

1] Display The Users Table Layout2] Change The Name Of the Users First to FName3] Display The Users Table Layout Replace The Capture Below!

ChangeThe Field

Name

77

1] Display The Users Table Layout2] Change The Name Of the Users FName to First3] Display The Users Table Layout Include The Capture Below!

78

ALTER TABLE … (Table) MODIFY … (Field) (Data-Type)

79

1] Display The Users Table Layout2] Add SSN varchar(11) To The Bottom Of The Table3] Display The Users Table Layout Replace The Capture Below!

ChangeThe Field

Data-Type

80

1] Display The Users Table Layout2] Add HomePhone varchar(14) To The Bottom Of The Table3] Display The Users Table Layout Include The Capture Below!

81

ALTER TABLE … (Table) ADD … (Field) (Data-Type)

82

1] Display The Users Table Layout2] Change The Name First Data-type from varchar(20) to char(30)3] Display The Users Table Layout Replace The Capture Below!

Add AFieldAt

Bottom

83

1] Display The Users Table Layout2] Change The Name First Data-type from char(30) to varchar(20)3] Display The Users Table Layout Include The Capture Below!

84

ALTER TABLE … (Table) ADD … (Field) (Data-Type)AFTER … (Field)

85

1] Display The Users Table Layout2] Add Position varchar(20) between SSN and HomePhone3] Display The Users Table Layout Replace The Capture Below!

Add AFieldInto

Location

86

ALTER TABLE … (Table) DROP … (Field)

87

1] Display The Users Table Layout2] Delete Field Position3] Display The Users Table Layout Replace The Capture Below!

Delete AField

88

1] Display The Users Table Layout2] Delete Field HomePhone3] Display The Users Table Layout Include The Capture Below!

89

UPDATE… (Table) SET … (Variable Name) = …WHERE … (Conditions)

90

1] Display Table Books2] Change The BkName Of The Last Record : ‘Advanced Networking’3] Display Table Books Replace The Capture Below!

91

1] Display Table Books2] Change The No Of Software Engineering to 43] Display Table Books Replace The Capture Below!

92

1] Display Table Books2] Increment The No Of All Books3] Display Table Books Replace The Capture Below!

93

1] Display Table Parts In Order By Description2] Increment The Cost Of All Parts by 25 cents3] Display Table Parts In Order By Description Include The Capture Below!

94

1] Display Table Parts In Order By Description2] Increment The Quantity In Stock of all Even Part ID’s By 13] Display Table Parts In Order By Description Include The Capture Below!

95

1] Display Table Parts In Order By Description2] Double the cost of all odd Part ID’s3] Display Table Parts In Order By Description Include The Capture Below!

96

1] Display Table Parts In Order By Description2] Make all of the Part Descriptions upper case3] Display Table Parts In Order By Description Include The Capture Below!

97

1] Display Table Parts In Order By Description2] Truncate all of the Part Descriptions to exactly 4 characters3] Display Table Parts In Order By Description Include The Capture Below!

98

1] Display Table Parts In Order By Description2] Increase the cost of all parts by 10% - round to two digits to the right of decimal3] Display Table Parts In Order By Description Include The Capture Below!

99

1] Display Table Parts In Order By Description2] Set the Quantity In Stock of all Parts to 03] Display Table Parts In Order By Description Include The Capture Below!

100

DELETE FROM … (Table) WHERE … (Conditions)

101

1] Display Table Books; 2] Delete Book No 53] Display Table Books; Replace The Capture Below!

102

1] Display Table Books; 2] Delete Book No 53] Display Table Books; Replace The Capture Below!

103

1] Display Table Books; 2] Delete All The Books3] Display Table Books; Replace The Capture Below!

104

1] Display Table Parts In Order By Description2] Delete all of the Parts with an Even ID3] Display Table Parts In Order By Description Include The Capture Below!

105

1] Display Table Parts In Order By Description2] Delete all of the Parts 3] Display Table Parts In Order By Description Include The Capture Below!

106

1] Display Table Officers In Order By FullName2] Delete all of the Officers Whose RankID >= 43] Display Table Officers In Order By FullName Include The Capture Below!

107

DROP TABLE … (Table)

108

1] Display A Table Listing;2] Delete Table Parts3] Display A Table Listing; Replace The Capture Below!

109

1] Display A Table Listing;2] Delete Table Players3] Display A Table Listing; Replace The Capture Below!

110

1] Display A Table Listing;2] Delete Table Books3] Display A Table Listing; Include The Capture Below!

111

1] Display A Table Listing;2] Delete Table Ranks3] Display A Table Listing; Include The Capture Below!

112

DROP DATABASE … (Database)

113

1] Display The List Of All Databases;2] Delete Database DrEggen3] Display The List Of All Databases; Replace The Capture Below!

114

1] Display The List Of All Databases;2] Delete Database Junk3] Display The List Of All Databases; Include The Capture Below!

115

1] Display The List Of All Databases;2] Delete Your Database Library??3] Display The List Of All Databases; Replace The Capture Below!

116

RESTORE YOUR BACKUPS

WITH MySQL

ADMINSTRATOR

117

1] Use MySQL Administrator To Reload Your Backup Of Practice??2] Display The List Of All Databases;2] Open Database Practice??3] Display The List Of All Tables; Replace The Capture Below!

118

1] Use MySQL Administrator To Reload Your Backup Of Library??2] Display The List Of All Databases;2] Open Database Library??3] Display The List Of All Tables; Replace The Capture Below!