MySQL Query Lab II 25 Points

1. MySQL Query Lab II • 25 Points • MySQLTU Database 3343 • Dr. Thomas Hicks

2. You May Do This Lab On Your Own Personal Computer Or On Your Database System

3. Your Name _________________ Name This Presentation: Tom-Hicks-MySQL-Query-2.pptx{Substitute Your First & Last Names}

4. Computer Used Is _________________ {My Personal Computer or CS-??}

5. Your Slide Presentation Should Include Screen Captures That Are Cropped – Clear & Easy To Read

6. Helpful HintThere Will Often Be A Colored Title Bar At The Top Of A Slide If This Bar Is Light Blue – It Is Info That You Should Read! 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. Use The Document Query • Format For All Slides! • SELECT ….  First LineFROM …  Next Line • WHERE …  Next Line If ThereORDER BY … Next Line If There Document Query Format

11. Use The MySQL Command Line ClientAnswer All QuestionsReplace Screen Captures

12. 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 NavyDo 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 NavyDo 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!