1. 2 5 6 if this bar is light blue – it is info that you should read! helpful hint there will...
Post on 26-Dec-2015
214 Views
Preview:
TRANSCRIPT
Your Name
_________________
Name This Presentation:Tom-Hicks-MySQL-Query-1.pptx
{Substitute Your First & Last Names}
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!
9
Use Good/Neat/Readable Captures Crop Your CapturesDon’t Include Extraneous Stuff! This Is Not Good!
BAD!
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.
17
Open MySQL Command Line ClientReplace The Capture Below!
We Are Using MySQL Server Version _?_ {4.4.3-beta below}
19
Put File Library-Hicks.sql On Your DesktopShow The Size In General Properties
Replace The Capture Below!
23
Getting Ready To Change Window Properties
Using the mouse, Right Mouse Click On The Top Of The Window and Select Properties
24
Set The Window Screen Text To WhiteSet Your Background To Red, Black, Green, Or Navy
Do Not Select Maroon Background
26
Set The Window Screen Text To WhiteSet The Background To Red, Black, Green, Or Navy
Do Not Select Maroon Background
28
Create Database Library?? [Replace ?? With Your Initials]
Show All Databases On Your SystemReplace The Capture Below!
46
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%
47
A Database Is More Than A Collection Of Tables
Tables, Programs, Indexes, Views, Forms, Reports, Relationships,
Etc.
48
Definitions of Database on the Web:
A database is an organized body of related information wordnetweb.princeton.edu/perl/webwn
A database is a structured collection of records or data that is stored in a computer system. The structure is achieved by organizing the data ...en.wikipedia.org/wiki/Database
A database is a systematically arranged collection of computer data, structured so that it can be automatically retrieved or manipulatedvalidwebdesigns.com/glossary/
A database is a collection of data organized for search and retrieval.summitschool.com/8review/dbssterms.doc
A collection of interrelated data items stored together without unnecessary redundancy, to serve one or more applications.www.4pcb.com/index.php
50
Show All Databases On Your System. Open Your Library?? Database. Only One Database May Be Open At A Time.
Replace The Capture Below!
53
1] Open Your Library?? Database2] Display The Tables The MySQL Database Without Opening It
Replace The Capture Below!
The MySQL Database contains information about Databases, Tables, Users, Permissions, etc. Although you could alter it with the superuser (root) account, it is quite easy to destroy your MySQL system in the process! Don’t Change The MySQL Database!
54
DESCRIBE …. (Table) SHOW FIELDS IN …. (Table)
Display TheDatabase Table Layout
(Fields, DataTypes, Etc.)
55
1] Open Your Library?? Database2] Display The Tables In The Open Database3] Show The Table Layout For Authors
Replace The Capture Below!
64
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
69
SELECT … (Fields)
FROM … (Table(s))
WHERE … (Conditions)
Conditional Operators>, <, <>, >=, <=, =, !=
Between
70
1] Display The ID, First, Last, & Email Of All Of The Users Whose ID Is Greater Than Or Equal To 5
Replace The Capture Below!
71
1] Display The ID, First, Last, & Email Of All Of The Users Whose ID Is Less Than Or Equal To 3
Include The Screen Capture Below!
72
1] Display The ID, First, Last, & Email Of All Of The Users Whose ID Is Equal To 2
Replace The Capture Below!
74
1] Display The ID, First, Last, & Email Of All Of The Users Whose ID Is In The Range 7 to 9 (Three Different Ways)
Replace The Capture Below!
75
1] Display The ID, First, Last, & Email Of All Of The Users Whose ID Is In The Range 13 to 15 (Three Different Ways)
Include Screen Capture Below!
76
1] Display The ID, First, Last, & Email Of All Of The Users Whose ID Is Not Equal To 5
Replace The Capture Below!
77
1] Display The ID, First, Last, & Email Of All Of The Users Whose ID Is Not Equal To 1
Include The Capture Below!
78
1] Display The ID, First, Last, & Email Of All Of The Users Whose ID Is Not Equal To 5. Do Not Use <>
Replace The Capture Below!
79
1] Display The ID, First, Last, & Email Of All Of The Users Whose ID Is Not Equal To 5. Do Not Use <> Or !=
Replace The Capture Below!
80
1] Display The ID, First, Last, Administrator & Email Of All Of The Users Whose ID Is Not An Administrator.
Include The Capture Below!
81
1] Display The ID, First, Last, & Email Of All Of The Users Whose ID 1, 3, or 5 (Two Different Ways)
Replace The Capture Below!
82
1] Display The ID, First, Last, & Email Of All Of The Users Whose ID 2, 4, 6, or 8 (Two Different Ways)
Include The Capture Below!
84
1] Display The ID, First, Last, & Email Of All Of The Users; order the data by First Name
Replace The Capture Below!
85
1] Display The ID, First, Last, & Email Of All Of The Users; order the data by Last Name
Include The Screen Capture Below!
86
1] Display The ID, First, Last, UserName, Email & FullName Of All Of The Users Whose ID Is In The Range 7 to 13; Order By FullName
Replace The Capture Below!
We Would Normally Prefer Not To Order By First Or Last
87
1] Display The ID, First, Last, UserName, Email & FullName Of All Of The Users Whose First Name Is Either “Jordan” or “JORDAN”
Replace The Capture Below!
When Using The MySQL Query Language, Searching For Strings __??__ {Is/Is Not} Case
Sensitive!
88
1] Display The ID, First, Last, UserName, Email & FullName Of All Of The Users Whose First Name Is Either ‘Jordan’ or ‘JORDAN’
Replace The Capture Below!
_____ {T/F} When Searching For Strings Using The MySQL Query Language, Strings May Be Delimited By Either Single Or Double Quotes.
_____ {T/F} When Searching For Strings Using The MySQL Query Language, A String May Begin With A Single Quote And End With A Double Quote.
89
1] Display The ID, First, Last, UserName, Email & FullName Of All Of The Users Whose Last Name Is Wong
Include The Capture Below!
91
1] Display The ID, First, Last, UserName, Email & FullName Of All Of The Users Whose Last Name Begins With A ‘W’
Replace The Capture Below!
92
1] Display The ID, First, Last, UserName, Email & FullName Of All Of The
Users Whose Last Name Or First Name Contains ‘AN’ Replace The Capture Below!
93
1] Display The ID, First, Last, UserName, Email & FullName Of All Of The Users Whose First Name Ends With An ‘M’
Replace The Capture Below!
94
1] Display The ID, First, Last, UserName, Email & FullName Of All Of The
Users Whose Last Name Or First Name Contains An ‘O’ Include The Capture Below!
95
1] Display The ID, First, Last, & Phone Of All Of The Users Whose Phone
Number Contains Either A ‘3’ Or A ‘4’ Include The Capture Below!
96
SELECT … (Fields)
FROM … (Table(s))
WHERE … (Conditions)
ORDER BY …
UPPER(str) - UCASE(str)LOWER(str) - LCASE(str)
97
1] Display The First, Last & Of All Of The Users; Make Sure The First And Last Name Are Capital (Upper Case)
Replace The Capture Below!
98
1] Display The First, Last & Of All Of The Users; Make Sure The First And Last Name Are Capital (Upper Case) And Email Is Non-Capital (Lower Case)Do Not Use Upper Or Lower!
Replace The Capture Below!
99
1] Display The First, Last & Of All Of The Users; Make Sure All The Fields Are Non-Capital (Lower Case)
Replace The Capture Below!
100
1] Display The String ‘tOm hICkS’ in Non-Capitals2] Display The String ‘tOm hICkS’ in Capitals3] Display The String ‘tOm hICkS’ in Non-Capitals And Capitals
Replace The Capture Below!
101
1] Display The String ‘tOm hICkS’ in Non-Capitals2] Display The String ‘tOm hICkS’ in Capitals3] Display The String ‘tOm hICkS’ in Non-Capitals And Capitals
Replace The Capture Below!
102
SELECT … (Fields)
FROM … (Table(s))
WHERE … (Conditions)
ORDER BY …
NumericOrder Operations(+,-,*,/)
POW(#,#)
103
1] Display 1232] Display 12 + 343] Display 2 * 3 + 4 * 5
Replace The Capture Below!
_____ {T/F} It Appears That MySQL Is Processing The Order Of Operations Correctly In The Examples Above.
104
1] Display 2^32] Display 2 + 2^33] Display 2 ^3^2
Replace The Capture Below!
_____ {T/F} It Appears That MySQL Is Processing The Order Of Operations Correctly In The Examples Above.
105
1] Display (2 * 3 / 4) * 10 – (4 / 2 + 1)2] Display 2 + 3 ^ 2
Include The Capture Below!
_____ {T/F} It Appears That MySQL Is Processing The Order Of Operations Correctly In The Examples Above.
106
1] Display “12” + “34”2] Display “12” / 4
Replace The Capture Below!
_____ {T/F} It Appears That MySQL Automatically Converts The Character Data “12” to Numeric When Applying Mathematical Operators +, -, /, and *.
108
1] Display “Community” + “Baptist” + “Church” Combined2] Display First And Last Names Of All Users Combined In The Form ‘TomHicks’
Replace The Capture Below!
109
1] Display The Last Name + “, “ + First Name Of All Users; [Hicks, Tom]
Include The Capture Below!
110
SELECT … (Fields)
FROM … (Table(s))
WHERE … (Conditions)
ORDER BY …
SUBSTRING(str,#,#)MID(str,#,#)
111
1] Display Characters 1, 2, and 3 of ‘CBCAlamoHeights.com’2] Display Characters 4, 5, 6, 7, and 8 of ‘CBCAlamoHeights.com’3] Display Characters 17, 18, and 19 of ‘CBCAlamoHeights.com’
Replace The Capture Below!
112
1] Display Characters 1, 2, and 3 of ‘CBCAlamoHeights.com’2] Display Characters 4, 5, 6, 7, and 8 of ‘CBCAlamoHeights.com’3] Display Characters 17, 18, and 19 of ‘CBCAlamoHeights.com’Do Not Use SubString
Replace The Capture Below!
113
1] Display The Combination of a) First Letter Of Each First Name (Lower Case)b) Last Name (Lower Case) c) “@trinity.edu”
Include The Capture Below!
115
1] Start A Trace To File C:\Output.txt2] Display The ID, First, Last, UserName, and Email Of All User In Order By Last Name.3] Stop The Trace.
Replace The Capture Below!
117
1] Start A Trace To File C:\Trinity.txt2] Display The ID, First, Last, Phone, and Email Of All User In Order By FullName.3] Stop The Trace.
Include The Capture Below!
120
1] Display A Vertical Listing Of The First, Last, Phone, and Email Of All Users Whose ID is Greater Than Or Equal To 13
Replace The Capture Below!
121
1] Display A Vertical Listing Of The ID, First, Last, Phone, and Email Of All Users Whose ID is Between 7 and 10
Include The Capture Below!
122
SELECT … (Fields)
FROM … (Table(s))
WHERE … (Conditions)
ORDER BY …
TRIM(str), RTRIM(str),
LTRIM(str)
123
1] Display The Combination Of “*” + RRIM(“ Happy Days “) + “*”
2] Display The Combination Of “*” + LRIM(“ Happy Days “) + “*”
3] Display The Combination Of “*” + TRIM(“ Happy Days “) + “*”
[There Are Times That Users Accidently Place Leading And/Or Trailing Blanks In Data Input – Trim May Be Used To Remove Those Blanks]
Replace The Capture Below!
124
SELECT … (Fields) AS ..
FROM … (Table(s))
WHERE … (Conditions)
ORDER BY …
AVG(fl), SUM(fl) MAX(fl), MIN
125
1] Display Average ID’s Of All Users;2] Display The Sum Of The ID’s Of All Users;
Replace The Capture Below!
126
1] Display Average ID’s Of All Users;2] Display The Sum Of The ID’s Of All Users;3] Display String “AVG(ID) = ??”
Replace The Capture Below!
127
1] Assign 2 * 3 to X And Display;2] Assign The Largest User ID To MAX_ID And Display; 3] ] Assign The Smallest User ID To MAX_ID And Display;
Replace The Capture Below!
130
1] Display The Character Whose ASCII Value Is 65;2] Display The Characters Whose ASCII Value Are 32, 97, and 48; 3] Display The ASCII Value Of Character ‘A’; 4] Display The ASCII Values Of Characters ‘ ’, ‘a’, and ‘0’
Replace The Capture Below!
131
1] Display The Character Whose ASCII Value Is 80;2] Display The Characters Whose ASCII Value Are 35, 94, and 100; 3] Display The ASCII Value Of Character ‘+’; 4] Display The ASCII Values Of Characters ‘b’, ‘c’, and ‘d’
Include The Capture Below!
132
SELECT … (Fields) AS ..
FROM … (Table(s))
WHERE … (Conditions)
ORDER BY …
CEIL(#), FLOOR(#) ROUND(#), ROUND(#,#)
134
1] Display Floor and Ceiling 123.4567 & 123.56702] Display Floor and Ceiling 1.499999, 1.50001, & 1.500
Replace The Capture Below!
135
1] Display The Position Of ‘Maury’ In ‘Dr. Maury Eggen’; Assign It To Loc2] Display The Position Of ‘Egg’ In ‘Dr. Maury Eggen’; Assign It To Pos3] Display The Position Of ‘Dr’ In ‘Dr. Maury Eggen’; Assign It To Loc 4] Display The Position Of ‘Doc’ In ‘Dr. Maury Eggen’; Assign It To Loc Replace The Capture Below!
136
SELECT … (Fields) AS ..
FROM … (Table(s))
WHERE … (Conditions)
ORDER BY …
LEFT(str, #) RIGHT(str, #)
137
1] Display The First 5 Characters Of The First Name and The First 7 Characters Of The Last Name. Replace The Capture Below!
138
1] Display The Last 5 Characters Of The First Name and The Last 7 Characters Of The Last Name. Replace The Capture Below!
140
1] Display The Position Of ‘Maury’ In ‘Dr. Maury Eggen’; Assign It To Loc2] Display The Position Of ‘Egg’ In ‘Dr. Maury Eggen’; Assign It To Pos3] Display The Position Of ‘Dr’ In ‘Dr. Maury Eggen’; Assign It To Loc 4] Display The Position Of ‘Doc’ In ‘Dr. Maury Eggen’; Assign It To Loc Replace The Capture Below!
141
1] Display The Position Of ‘Egg’ In ‘Dr. Maury Eggen’; Assign It To Pos Do It Three Different Ways Replace The Capture Below!
142
1] Display First Name And The Position Of ‘A’ In The First Name For All Users Replace The Capture Below!
144
1] The Number Of Users2] Display The First, Last, and Administrator For All Non-Administrators3] Display The Number Of Non-Administrators Replace The Capture Below!
145
1] Display The Number Of Users That Have An ‘A’ In Their First Name2] Display The Number Of Users That Have An ‘A’ As One Of The First Three Letters In Their First Name 3] Display The Number Of Users That Have An ‘.’ In Their Email Include The Capture Below!
147
1] Display The Length Of ‘Tom’2] Display The Length Of ‘Dr. Thomas Hicks’; Assign That Value To LEN_NAME Replace The Capture Below!
148
1] Display The Length Of ‘Tom’2] Display The Length Of ‘Dr. Thomas Hicks’ 3] Display The First Name and The Length Of The First Name For All Users Whose ID Is Greater Than Or Equal To 8 Replace The Capture Below!
149
1] Display The Number Of User Whose First Name Is 4 Characters In Length; Assign That Value To No_4_First 2] Display The Number Of User Whose First Name Is 5 Characters In Length; Assign That Value To No_5_First 3] Display The Number Of User Whose Last Name Is 7 Characters In Length; Assign That Value To No_7_Last Include The Capture Below!
150
SELECT … (Fields) AS ..
FROM … (Table(s))
WHERE … (Conditions)
ORDER BY …
LPAD(str,#,str)RPAD(str,#,str)
151
1] Display All Of The User IDs; Pad Them On The Left With Blanks, .’s, and 0’sWe Often Want To LPAD Numeric
Replace The Capture Below!
152
1] Combine The First And Last Names Of All Users; Pad Them With 10 .’s On The Right
We Often Want To LPAD Strings On The Right With Blanks For Spacing On Reports
Replace The Capture Below!
154
1] We Often Combine The Concat, Padding, Left, Right, Upper, Lower, etc. To Produce Single-Line Output For Reports. Replace The Capture Below!
Ignore Collateral Output!It Would Not Appear In The Report!
156
1] Display ‘Tom Hicks’ In Reverse Order2] Display The First Name Of All Of The Users In Reverse Order. Replace The Capture Below!
160
1] Display The Replacing Of All ‘W’s in WWW.trinity.edu with ‘w’s2] Display The Replacing Of All ‘W’s in WWW.trinity.edu with ‘*’s 3] Display The Replacing Of All ‘.’s in WWW.trinity.edu with nothing Replace The Capture Below!
162
1] Display 20 ‘ ‘s2] Display The First Name + 5 ‘ ‘s + Last Name of All Users; Assign It To Info. Replace The Capture Below!
163
SELECT … (Fields) AS ..
FROM … (Table(s))
WHERE … (Conditions)
ORDER BY …
BIN(#), OCT(#), HEX(#)
164
1] Display Binary, Octal, and Hex Values of 15, 65, 255, 512, and 256 Replace The Capture Below!
166
1] Display 15 Mod 6 Using Both The Mod and % Functions2] Display 1000 Mod 10 Replace The Capture Below!
170
1] Display PI2] Display The Area Of A Circle Whose Radius Is 3; Assign It To Area3] Display The Log (Base 2) Of 15 and 1,000,000 Replace The Capture Below!
172
1] Display The Square Root of 2, 4, 9, and 12] Display The Square Root of15,248,792,196 Replace The Capture Below!
173
SELECT … (Fields) AS ..
FROM … (Table(s))
WHERE … (Conditions)
ORDER BY …
NOW(), DAY(d), MONTH(d)YEAR(d)
174
1] Display The Date Today2) Display The Day Today3) Display The Month Today4] Display The Year Today Replace The Capture Below!
175
SELECT … (Fields) AS ..
FROM … (Table(s))
WHERE … (Conditions)
ORDER BY …
HOUR(d), MINUTE(d)
SECOND(d)
176
1] Display Now and The Current Hour2) ] Display Now and The Current Minute3) ] Display Now and The Current Second Replace The Capture Below!
177
SELECT … (Fields) AS ..
FROM … (Table(s))
WHERE … (Conditions)
ORDER BY …
DAYOFWEEK(d), DAYNAME(d)
MONTHNAME(d), DAYOFYEAR(d)
178
1] Display Day Of Week And Day Name For Christmas of 20102] Display Day Of Week And Day Name For Dec 26th of 20103] Display Day Of Month And Month Name For Christmas of 20104] Display Day Of Year And Day Of Year For Christmas of 2010
Replace The Capture Below!
180
From The Capture Below, We Can See That The Big Integer Calculation Is Accurate To At Least 5 Digits; Continue This Process, Increase It By 1 Until It Is Goes Into Scientific Notation. Screen Capture Your Last Three SELECT Statements. Replace The Capture Below!
184
Questions
_____ {T/F} The Foundations Of SQL Are Generic.
_____ {T/F} Each And Every Implementation Of SQL Have Exactly The Same Function Set.
_____ {T/F} Some Implementations Of SQL Have Added Some Function Enhancements That May Not Be Available On Other SQL Systems.
SQL is an Acronym For _________________________________
186
MySQL String & Numeric & Time Functions
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
http://dev.mysql.com/doc/refman/5.0/en/numeric-functions.html
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
top related