Matthew M.

Matthew M.

SQL Command: SELECT

Slide Duration:

Table of Contents

Section 1: Advanced PHP with MySQL
Course Introduction

13m 36s

Intro
0:00
Advanced PHP w/ MySQL
0:13
Course Introduction
0:14
Course Content
0:59
Web Application: Educator Store
1:45
Web Application: Educator Store
1:46
Object-Oriented Programming
6:09
Object-Oriented Programming Overview
6:10
MySQL
6:50
MySQL Overview
6:51
Example: Command Prompt & MySQL
8:34
What You Will Learn
9:34
What You Will Learn
9:35
Course Prerequisites
11:52
Course Prerequisites
11:53
Advanced Course Development Environment

18m 46s

Intro
0:00
Lesson Overview
0:11
Lesson Overview
0:12
Development Environment Setup
1:36
Firefox Web Browser, XAMPP, and PSPad Text Editor
1:37
Course Directory Structure
4:36
Course Directory Structure
4:37
phpDocumentor
7:23
phpDocumentor Overview
7:24
Parsing Source Code
7:44
docs Subdirectory
9:06
Asvanced PHP Course Documentation Example
10:21
Previous Educator PHP Students
14:14
Lib Directory Changes
14:38
Images Directory Changes
14:57
VERSION Constant
15:40
Comments Noting a Specific Version Number
17:04
Homework Challenge
17:30
Homework Challenge
17:31
Introduction to File I/O

35m 37s

Intro
0:00
Lesson Overview
0:10
Lesson Overview
0:11
File I/O
1:34
File I/O Overview
1:35
Performing file I/O: 3 Steps
1:52
File Permissions
2:28
Read Permission & Write Permission
2:29
fopen()
4:47
fopen()
4:48
Two Required Parameters
5:01
Successful fopen(): File Handle and Resource
5:21
FALSE and E_WARNING Error
6:00
Example
6:39
File Paths
7:10
File Paths
7:11
File Access Modes
9:18
File Access Modes
9:19
Coding Example: fopen()
10:31
Using Relative and Absolute Path & Non-Existent File
10:32
File Pointers
15:55
File Pointers
15:56
Example File
16:11
Opening a File with fopen() Using 'r' as the Access Mode
16:22
fgets()
17:55
fgets() Overview
17:56
Coding Example: fgets() - Reading a Line at a Time
18:26
feof()
20:10
feof() Overview
20:09
Coding Example: feof()
20:50
fclose()
23:43
fclose() Overview
23:44
Coding Example: fclose()
25:02
Coding Example: Current Stock
26:10
Coding Example: Current Stock
26:11
trim ()
31:39
trim () Overview and Example
31:40
Homework Challenge
33:35
Homework Challenge
33:36
Web Application Development

36m 36s

Intro
0:00
Lesson Overview
0:16
Lesson Overview
0:17
Version 1.0 Changelog
0:31
catalog.php
0:32
Function Library: fileLIB.php
5:00
Version 1.1 Changelog
27:12
createItemDataArray() and createDeptDataArray()
27:13
outputSimpleItemLink() and outputSimpleDeptLink()
32:06
HTTP & the POST Method

34m 36s

Intro
0:00
Lesson Overview
0:10
Lesson Overview
0:11
HTTP Model
1:28
HTTP Model
1:29
Client-Server Model 2 Steps Process
2:00
HTTP Messages
2:23
HTTP Messages
2:24
HTTP Requests
3:09
HTTP Requests
3:10
Example: HTTP Request Header
3:17
Coding Example: HTTP Requests
4:29
HTTP Responses
7:15
HTTP Responses
7:16
Example: HTTP Response (Header & Body)
8:00
header()
11:47
header() Overview
11:48
Coding Example: header
14:02
Coding Example: header() Error
14:52
GET Method
18:55
GET Method
18:56
Coding Example: GET Method
20:13
POST Method
21:09
POST Method
21:10
Coding Example: Sample Form Using the POST Method
23:50
Accessing POST Data in PHP
26:38
Accessing POST Data in PHP
26:39
Coding Example: Accessing POST Data via $_POST
28:15
GET vs. POST
29:54
GET vs. POST
29:55
Example: Accessing POST Data via $_POST
31:20
Example: Accessing POST Data via $_POST
31:21
Homework Challenge
33:00
Homework Challenge
33:01
Web Application Development

14m 11s

Intro
0:00
Version 2.0 Changelog
0:09
Updating the Site's Current Form to Use the POST Method
0:10
Creating Admin Website
2:55
Dynamically Generated Department List
12:40
Writing to Files

17m 38s

Intro
0:00
Lesson Overview
0:09
Lesson Overview
0:10
Review of File I/O
0:40
Review of File I/O
0:41
fwrite()
1:38
fwrite()
1:39
fputs()
2:20
'r+'
2:31
Coding Example: fwrite()
2:59
fopen() Access Modes
8:41
'a' and 'a+'
8:42
'w' and 'w+'
11:34
Homework Challenge
14:47
Homework Challenge
14:48
Web Application Development

28m 14s

Intro
0:00
Lesson Overview
0:08
Lesson Overview
0:09
Updated Open File Functions
0:19
openItemsDataFile ( ), openDeptsDataFile ( ), and openLastAddedFile ( )
0:20
insertItem()
6:25
insertItem() Overview
6:26
Functions: createItemDataString ( ) and updateLastItemAdded ( )
7:07
addItemToDepartment ()
16:03
addItemToDepartment () Overview
16:04
Functions: createDeptDataString ( ) and updateDepartment ( )
17:55
HTTP POST: File Uploads

22m 51s

Intro
0:00
Lesson Overview
0:10
Lesson Overview
0:11
POST Data Encoding
1:13
POST Data Encoding
1:14
When Data is Included in the Body of the HTTP POST Request and is of the Content Type
1:31
When Uploading Files Using HTTP POST
3:17
Uploading Files
6:22
Uploading Files
6:23
If Encoding Type is Not Set to Multipart/Formdata
9:27
$_FILES Superglobal
10:59
$_FILES Superglobal
11:00
Structure of $_FILES
12:15
Structure of $_FILES: Name, Type, tmp_name, Error, and Size
12:16
Coding Example: $_FILES Superglobal
13:34
Moving an Uploaded File
15:39
Introduction to Moving an Uploaded File
15:40
move_uploaded_file ( ): Definition and Example
16:40
Homework Challenge
21:19
Homework Challenge
21:20
Web Application Development

15m 30s

Intro
0:00
Lesson Overview
0:09
Lesson Overview
0:10
Uploading Item Image File
1:22
Uploading Item Image File
1:23
Uploading Item Image File (cont.)
2:43
Altering insertItem ( )
2:44
Helpfer Functions: getFileExtension ( ) and moveUploadedFile ( )
4:30
isValidItem ( )
13:03
Introduction to Object-Oriented Programming

32m 44s

Intro
0:00
Lesson Overview
0:11
Lesson Overview
0:12
Object-Oriented Programming
0:56
Introduction to Object-Oriented Programming
0:57
Associative Arrays
5:27
Associative Arrays
5:28
Classes
7:27
Classes Overview
7:28
Defining Classes
8:24
Defining Classes
8:25
Declaring Class Properties
9:25
Coding Example: Classes
10:11
Objects
12:18
Objects Overview
12:19
Classes vs. Objects
12:49
Classes vs. Objects
12:50
Instantiating Classes
14:58
Instantiating Classes
14:59
Coding Example: Instantiate an Item Object
16:30
Object Properties
19:21
Access and Set an Object's Property
19:22
Coding Example: Set & Access the Properties of an Item Object
24:23
Homework Challenge
30:22
Homework Challenge
30:23
Web Application Development

17m 47s

Intro
0:00
Lesson Overview
0:11
Lesson Overview
0:12
Version 5.0 Overview
0:50
Version 5.0 Overview and Examples
0:51
Outputting Object Properties
12:38
Outputting Object Properties
12:39
Array Object Properties
14:18
Access and Set Array Value
14:19
Object Methods

39m 57s

Intro
0:00
Lesson Overview
0:12
Lesson Overview
0:13
Object Methods
0:52
Definition of Object Methods
0:53
Calling Object Methods
3:25
Calling Object Methods
3:26
Coding Example: Simple Object Methods
4:44
Object Methods and Return Values
7:02
$this Variable
10:06
$this Variable: Definition and Example
10:07
$this Variable (cont.)
15:51
$this Variable (cont.)
15:52
Getters & Setters
21:21
Getters & Setters Methods
21:22
Object Methods In Strings
25:46
Object Methods In Strings
25:47
Coding Example: Outputting Method Return Values in Strings
27:41
Using $this to Call Other Methods
28:49
Using $this to Call Other Methods
28:50
Homework Challenge
34:18
Homework Challenge: 1-3
34:19
Homework Challenge (cont.)
36:20
Homework Challenge: 4-6
36:21
Homework Challenge (cont.)
37:52
Homework Challenge: 7-10
37:53
Web Application Development

17m 30s

Intro
0:00
Lesson Overview
0:11
Lesson Overview
0:12
Version 6.0 Overview
0:40
Version 6.0 Changes & Examples
0:41
Item Methods
0:53
Item Class Definition: getImageFilename()
0:54
Coding Example: getImageFilename()
1:58
Department Methods
7:33
addItem(), removeItem(), and isItemInDept() Method
7:34
addItemtToDepartment() & buildDeptObject() Functions
11:46
A Default Value Needed to Be Set for $items
16:26
Object Constructors

22m 20s

Intro
0:00
Lesson Overview
0:11
Lesson Overview
0:12
Object Constructors
0:32
Introduction to Object Constructors
0:33
Coding Example: Object Constructors
1:43
Defining Constructors
3:26
Defining Constructors
3:27
Coding Example: Constructors with No Arguments
4:24
Constructor Arguments
8:09
Constructor Arguments
8:10
Coding Example: Constructor Arguments
9:49
Important Notes
16:13
Important Notes
16:14
Homework Challenge
17:35
Homework Challenge: 1 - 4
17:36
Homework Challenge (cont.)
19:13
Homework Challenge: 5 - 9
19:14
Homework Challenge (cont.)
21:40
Homework Challenge: 10 and 11
21:41
Web Application Development

16m 19s

Intro
0:00
Lesson Overview
0:11
Lesson Overview
0:12
Version 7.0 Overview
0:39
Version 7.0 Overview
0:40
Item Constructor
2:19
Item Constructor
2:20
Department Constructor
7:47
Department Constructor
7:48
Customer Constructor
12:23
Customer Constructor
12:24
More Magic Methods

53m 37s

Intro
0:00
Lesson Overview
0:13
Lesson Overview
0:14
Magic Methods
1:12
Magic Methods
1:13
Destructors
2:45
Destructors Overview
2:46
Coding Example: Calling Destructors
4:30
Coding Example: Object Destructor
9:19
_to String ()
16:12
_to String () Overview
16:13
Coding Example: _to String () Magic Method
18:10
Access Modifiers
21:23
Introduction to Access Modifiers
21:24
Access Modifiers: Public
21:39
Access Modifiers: Private
22:18
Access Modifiers: Protected
22:41
Object Properties and Methods
23:06
Coding Example: Public Access Modifiers
26:48
Coding Example: Private Access Modifiers
28:30
_get()
31:37
_get() Overview
31:38
Coding Example: _get () Magic Method
33:30
_set ()
36:23
_set () & the Magic Method
36:24
Using Getters & Setters
44:37
Coding Example: Using Getters & Setters
44:38
Homework Challenge
50:33
Homework Challenge: 1 - 6
50:34
Homework Challenge (cont.)
51:41
Homework Challenge: 7 - 12
51:42
Web Application Development

31m 12s

Intro
0:00
Version 8.0 Review
0:12
Version 8.0 Review
0:13
private Object Properties
1:15
private Object Properties
1:16
Coding Example
2:54
_toString() Methods
6:51
_toString() Methods
6:52
Coding Example
8:09
DataFile Class
13:27
DataFile Class & I/O Operations
13:28
Using DataFile Class: Instantiate a DataFile, DataFile open () Method, and DataFile close () Method
18:09
Homework Challenge
29:35
Homework Challenge
29:36
Classes vs. Objects

37m 49s

Intro
0:00
Lesson Overview
0:09
Lesson Overview
0:10
Class vs. Object Review
0:58
Class vs. Object Review
0:59
Static Properties
2:42
Static Properties
2:43
Scope Resolution Operator (::)
5:26
Scope Resolution Operator (::) & Static Property
5:27
Coding Example: Accessing a Static Property Using the Scope Resolution Operator
7:57
Coding Example: Accessing a Static Property in a Class Definition
10:33
Coding Example: Using a Static Property as an Instance Counter
14:18
Static Methods
18:51
Static Methods Overview
18:52
Coding Example: Static Methods
21:39
Classes vs. Objects
26:28
Classes vs. Objects: Diagrams
26:29
Class Constants
30:24
Class Constants Definition
30:25
Coding Example: Using Class Constants
31:55
Homework Challenge
34:01
Homework Challenge: 1 - 5
34:02
Homework Challenge (cont.)
35:25
Homework Challenge: 6 - 10
35:26
Web Application Development

21m 7s

Intro
0:00
Version 9.0 Overview
0:12
Version 9.0 Changes & Examples
0:13
Output Class
1:08
Output Class & Static Methods
1:09
outputHtmlSelector ( ) Method
2:43
outputItemSelector ( ) Method
4:53
outputDeptSelector ( ) Method
6:42
buildObject() Methods
11:54
buildObject() Methods & Examples
11:55
Exceptions

22m 43s

Intro
0:00
Lesson Overview
0:12
Lesson Overview
0:13
try Blocks
0:44
try Blocks Definition
0:45
catch Blocks
2:03
catch Blocks: Definition and Syntax
2:04
Coding Example: try/catch Blocks
3:48
When an Exception Object is Caught & Error Handling
5:59
Exception Class
7:02
Exception Class
7:03
Coding Example: Using try/catch to Catch an Exception Thrown from a Method
8:04
Re-Throwing Exceptions
16:39
Re-Throwing Exceptions
16:40
Coding Example: Re-throwing an Exception
17:21
Homework Challenge
20:40
Homework Challenge: 1 - 5
20:41
Homework Challenge (cont.)
22:17
Homework Challenge: 6
22:18
Web Application Development

22m

Intro
0:00
Version 10.0 Overview
0:11
Updating addItem.php, addItemToDept.php, and error.php
0:12
Updating DataFile Class: open ( ) Method & try/catch blocks
7:45
Version 10.1 Overview
13:37
Version 10.1 Changes & Examples
13:38
Updating DataFile Class: close ( ) Method & logWarning ( )
20:17
Cookies

39m 47s

Intro
0:00
Lesson Overview
0:09
Lesson Overview
0:10
What is a Cookie?
1:15
Definition of Cookie
1:16
HTTP - A Stateless Protocol
2:17
HTTP - A Stateless Protocol
2:18
Purpose of Cookies
4:10
Set - Cookie Header
6:21
Introduction to Set - Cookie Header
6:22
Coding Example: Set - Cookie Header
7:38
Cookie Header
9:45
Introduction to Cookie Header
9:46
Example: Cookie Header
10:27
setcookie()
12:33
Introduction to setcookie()
12:34
Example: Using setcookie
13:54
Cookies Attributes
15:48
Cookies Attributes Overview
15:49
Common Cookies Attributes: Domain
16:24
Common Cookies Attributes: Path
17:04
Common Cookies Attributes: Expires
19:34
Coding Example: Setting Cookie Attributes
20:58
$_COOKIE Superglobal
27:03
$_COOKIE Superglobal
27:04
Coding Example: Accessing Cookies from PHP Using $_COOKIE
29:45
Security Implications
34:44
Security Implications
34:45
Homework Challenge
36:10
Homework Challenge: 1 - 4
36:11
Homework Challenge (cont.)
37:47
Homework Challenge: 5 - 7
37:48
Web Application Development

39m 11s

Intro
0:00
Lesson Overview
0:17
Lesson Overview
0:18
instanceof Operator
1:34
instanceof Operator
1:35
Objects with Object Properties
2:53
Objects with Object Properties
2:54
Version 11.0 Overview
6:41
Version 11.0 Changes: Cart and CartItem Classes
6:42
Version 11.0 Coding Example
10:40
Version 11.1 Overview
31:03
Version 11.1 Changes & Examples
31:04
Introduction to Sessions

46m 8s

Intro
0:00
Lesson Overview
0:08
Lesson Overview
0:09
What is a Session?
0:53
What is a Session?
0:54
Client-Side Sessions
3:51
Client-Side Sessions
3:52
Server-Side Sessions
5:04
Server-Side Sessions
5:05
Session IDs
6:26
Session IDs
6:27
The SID is Used by the Server to:
7:08
A Client Passes an SID to the Server with Each HTTP Request Via:
8:15
Sessions in PHP
12:19
Sessions in PHP
12:20
session_start()
14:40
session_start() Overview
14:41
Coding Example: Using session_start () to Continue a Session
18:03
$_SESSION Superglobal
22:06
$_SESSION Superglobal Overview
22:07
Coding Example: Setting a Session Variable via $_SESSION
25:22
Accessing Session Data
28:08
Accessing Session Data
28:09
Deleting Session Data
31:43
Deleting Session Data
31:44
Coding Example: Deleting Session Data
32:16
Configuring PHP Sessions
36:14
session.cookie_lifetime, session.cookie_domain, and session.cookie_path
36:15
Coding Example: Configuring PHP Sessions
37:28
Configuring PHP Sessions (cont.)
40:56
session.use_cookies, session.use_trans_sid, and session.use_only_cookies
40:57
Coding Example: Configuring PHP Sessions
42:25
Homework Challenge
42:55
Homework Challenge: 1 - 5
42:56
Homework Challenge (cont.)
44:10
Homework Challenge: 6 - 10
44:11
Homework Challenge (cont.)
45:27
Homework Challenge: 11 - 13
45:28
Web Application Development

11m

Intro
0:00
Version 12.0 Overview
0:12
Version 12.0 Changes Part I: Updating 'viewCart.php' & 'checkout.php'
0:13
Version 12.0 Changes Part II
5:03
Destroying Sessions

29m 59s

Intro
0:00
Lesson Overview
0:12
Lesson Overview
0:13
Destroying Sessions
1:02
Destroying Sessions
1:03
session_destroy()
2:10
session_destroy() Overview
2:11
Coding Example: Setting a Session Variable and Destroying a Session
3:18
Deleting Session Cookies
8:38
Deleting Session Cookies
8:39
Coding example: Deleting Session Cookies
9:17
Review of Steps
21:07
Review of Steps
21:08
Garbage Collection
21:50
Garbage Collection Overview
21:51
Coding Example: Garbage Collection
24:30
Homework Challenge
26:28
Homework Challenge: 1 - 4
26:29
Homework Challenge (cont.)
28:16
Homework Challenge: 5 - 9
28:17
Web Application Development

19m 14s

Intro
0:00
Lesson Overview
0:13
Lesson Overview
0:14
_autoload()
0:46
Introduction to _autoload ()
0:47
Version 13.0 Overview
4:06
_autoload () and SessionManager Class
4:07
Updating 'viewCart.php' & 'checkout.php'
11:16
Introduction to Databases

21m 24s

Intro
0:00
Lesson Overview
0:16
Lesson Overview
0:17
Flat Files
1:09
Flat Files: Definition and Example
1:10
Problems Associated with Using Flat Files as a Database
2:15
Relational Databases
3:29
Relational Databases
3:30
Relational Database Management System (RDBMS)
3:50
Tables
7:43
Tables
7:44
Columns
9:24
Columns
9:25
What is SQL?
10:45
Introduction to Structured Query Language
10:46
Standard Data Types of SQL
12:23
Primary Keys
13:19
Primary Keys
13:20
Primary Key Examples
16:36
Primary Key Examples
16:37
Introduction to MySQL

28m 11s

Intro
0:00
Lesson Overview
0:11
Lesson Overview
0:12
What is MySQL?
1:33
MySQL
1:34
MySQL Monitor
5:16
MySQL Monitor Overview
5:17
XAMPP & MySQL
5:58
In the MySQL Command: hostname, username and -p
7:05
Connecting to a MySQl Server
9:52
Connecting to a MySQl Server
9:53
Using MySQl Monitor
13:48
Using MySQl Monitor
13:49
GRANT Command
15:10
GRANT SQL Command
15:11
Privileges & Objects
18:18
Username, Hostname, and Password
19:37
Specifying Objects
20:05
Creating a PHP User Account
21:41
Creating a PHP User Account Overview
21:42
Meaning of all & *.*
23:35
Example: Creating a PHP User Account
23:48
Required Homework
26:27
Required Homework: 1 - 5
26:28
Required Homework (cont.)
27:28
Required Homework: 6
27:29
Creating Databases & Tables

23m 1s

Intro
0:00
Lesson Overview
0:08
Lesson Overview
0:09
Creating a Database
1:40
Introduction to Creating a Database
1:41
Example: Creating a Database
3:33
CREATE TABLE Command
6:43
CREATE TABLE Command
6:44
Data Types
7:39
Numeric Data Types: INT, INT UNSIGNED, and DECIMAL
7:40
String Data Types: CHAR (M), VARCHAR (M), and TEXT
9:23
Colum Attributes
11:00
PRIMARY KEY Attribute
11:01
AUTO INCREMENT Attribute
11:35
Items Table
13:07
Items Table
13:08
Useful Commands
17:04
SHOW TABLES Command & DESCRIBE Utility Statement
17:05
Example: Creating an Items Table
17:58
Required Homework
20:51
Required Homework: 1 - 6
20:52
Required Homework (cont.)
21:55
Required Homework: 7 - 9
21:56
SQL Command: INSERT

27m 11s

Intro
0:00
Lesson Overview
0:11
Lesson Overview
0:12
INSERT Command
1:20
SQL INSERT Command
1:21
Specifying Values
3:16
Columns with Numeric Data Types
3:17
Columns with String Data Types
3:34
Columns with AUTO INCREMENT
4:07
Inserting Items
5:21
Inserting Items
5:22
Example: Inserting Items
7:03
SQL Text Files
14:04
SQL Monitor Client
14:05
MySQL Monitor & the < Operator
15:15
Example
17:35
Required Homework
22:32
Required Homework: 1 - 6
22:33
Required Homework (cont.)
24:19
Required Homework: 7 - 10
24:20
SQL Command: SELECT

24m 57s

Intro
0:00
Lesson Overview
0:10
Lesson Overview
0:11
SELECT Command
0:42
SELECT Command: Definition and Syntax
0:43
Results of a SELECT Query
1:53
To Specify the Retrieval of All Columns from a Table
3:06
Example: SELECT Command
4:18
WHERE Clause
7:11
WHERE Clause
7:12
A SELECT Query with WHERE Clause has the Following Syntax
8:10
A Where Condition can Contain both Logical and Comparison Operators
9:44
Literal Values
13:24
Literal Values
13:25
Example: Literal Values
14:18
ORDER BY Clause
15:31
ORDER BY Clause & SELECT Query
15:32
Ascending and Descending Order
17:30
An ORDER BY Clause can Specify Multiple Columns to Order the Rows by
18:01
Example: SELECT Query with WHERE and ORDER BY Clause
20:32
Homework Challenge
21:59
Homework Challenge
22:00
Homework Challenge (cont.)
23:54
Homework Challenge
23:55
Using PHP from MySQL

32m 43s

Intro
0:00
Lesson Overview
0:10
Lesson Overview
0:11
PHP/MySQL Architecture
0:56
PHP/MySQL Architecture
0:57
MySQLi Extension
3:05
MySQLi Extension
3:06
Steps to Using MySQL from PHP
5:53
Four Steps to Using MySQL from PHP
5:54
Step # 3
7:18
Connecting to a Database
8:12
To Connect to a MySQL Database
8:13
To Test for Connection Errors
10:20
Running an SQL Statement
11:54
Running an SQL Statement
11:55
Example
13:30
Processing the Results
15:58
For non-SELECT Queries
15:59
MySQLi Object Property: affected_rows
16:19
For INSERT Statements on Tables with an AUTO_INCREMENT Column
18:07
Closing the Connection
19:45
Closing the Connection
19:46
Coding Example
21:24
Coding Example: Using MySQL from PHP
21:25
Homework Challenge
29:00
Homework Challenge: 1 - 4
29:01
Homework Challenge (cont.)
31:10
Homework Challenge: 5 - 9
31:11
Web Application Development

30m 30s

Intro
0:00
Lesson Overview
0:14
Lesson Overview
0:15
sprintf()
1:28
sprintf() Overview
1:29
Example
1:56
INSERT Statements with sprintf()
5:15
INSERT Statements with sprintf()
5:16
Version 14.0 Overview
10:00
Version 14.0 Changes & Examples Part 1
10:01
Version 14.0 Changes & Examples Part 2
21:58
SELECT QUERIES from PHP

24m 22s

Intro
0:00
Lesson Overview
0:14
Lesson Overview
0:15
SELECT Queries
1:21
SELECT Queries
1:22
MySQLi_Result Class
3:17
MySQLi_Result Class: num_rows
3:18
MySQLi_Result Class: fetch_assoc ( ) and fetch_object ( )
3:59
fetch_assoc()
5:17
fetch_assoc() Overview
5:18
Coding Example: Using fetch_assoc()
9:00
stdClass Objects
12:46
stdClass Objects
12:47
fetch_object()
14:19
fetch_object() Overview
14:20
Coding Example: Using fetch_object()
16:45
Freeing Result Set Memory
18:42
Freeing Result Set Memory
18:43
Coding Example: Using free ( )
20:01
Homework Challenge
22:07
Homework Challenge: 1 - 5
22:08
Web Application Development

17m 6s

Intro
0:00
Version 15.0 Overview
0:13
Version 15.0 Changes & Examples Part 1
0:14
Version 15.0 Changes & Examples Part 2
8:43
Linking Tables

24m 32s

Intro
0:00
Lesson Overview
0:16
Lesson Overview
0:17
A Departments Table
1:08
Departments Table
1:09
How Could We Model This in a Table: Option 1
1:26
How Could We Model This in a Table: Option 2
3:12
Database Normalization
4:37
Database Normalization
4:38
Foreign Keys
8:47
Foreign Keys
8:48
Linking Table
11:26
Linking Table
11:27
Multiple Column Primary Keys
15:30
Multiple Column Primary Keys
15:31
Example
18:51
Required Homework
22:34
Required Homework: 1 - 3
22:35
Required Homework (cont.)
24:10
Required Homework: 4
24:11
Web Application Development

24m

Intro
0:00
Version 16.0 Overview
0:11
Version 16.0 Changes
0:12
Version 16.0 Coding Examples
4:12
Version 16.0 (cont.)
18:30
Version 16.0 Changes & Examples Part 2
18:31
SQL Command: DELETE

10m 33s

Intro
0:00
Lesson Overview
0:12
Lesson Overview
0:13
DELETE Statement
0:30
DELETE Statement & Its WHERE Clause
0:31
Delete All Rows From a Table
2:57
Using DELETE from PHP
4:04
Using DELETE from PHP Overview
4:05
Coding Example: Using DELETE from PHP
6:37
Homework Challenge
8:53
Homework Challenge: 1 - 4
8:54
Web Application Development

16m 2s

Intro
0:00
Version 17.0 Overview
0:11
Version 17.0 Changes
0:12
Version 17.0 Coding Example
2:51
Transactions
12:50
Database Transaction
12:51
Steps in Using Transactions
14:00
SQL Command: UPDATE

32m 14s

Intro
0:00
Lesson Overview
0:10
Lesson Overview
0:11
UPDATE Statement
0:54
UPDATE Statement & Its WHERE Clause
0:55
Example: UPDATE Statement
3:32
Using UPDATE from PHP
5:21
Using UPDATE from PHP Overview
5:22
Coding Example: Using UPDATE from PHP
6:53
htmlspecialchars()
9:04
htmlspecialchars()
9:05
Encoding Special Characters
9:23
Coding Example: htmlspecialchars()
13:41
addslashes()
20:40
addslashes()
20:41
Coding Example: addslashes()
22:52
Homework Challenge
27:55
Homework Challenge: 1 - 2
27:56
Homework Challenge (cont.)
30:14
Homework Challenge: 3 - 5
30:15
Homework Challenge (cont.)
30:41
Homework Challenge: 6 - 9
30:42
Web Application Development

12m 33s

Intro
0:00
Version 18.0 Overview
0:12
Version 18.0 Changes
0:13
Version 18.0 Coding Example
1:24
SQL: Joins

42m 16s

Intro
0:00
Lesson Overview
0:11
Lesson Overview
0:12
DATETIME Data Type
1:52
DATETIME Data Type
1:53
Modeling Orders
5:12
Modeling Orders
5:13
Customers Table
10:46
Customers Table
10:47
Example
16:18
Example: Order Table in MySQL
16:19
Joins
22:26
Joins Overview
22:27
Inner Join
23:09
Example: Joins
24:11
Join Conditions
28:14
Join Conditions
28:15
Example
28:32
Join Queries
30:00
Example: Join Conditions in MySQL
30:36
Implicit Joins
34:42
Implicit Joins
34:43
Example: Implicit Joins
35:57
Aliases
37:28
Introduction to Aliases
37:29
Example: Aliases
38:55
Required Homework
40:32
Problem 1
40:33
Problem 2
41:01
Problem 3
41:17
Web Application Development

29m 34s

Intro
0:00
Lesson Overview
0:12
Lesson Overview
0:13
Inserting Orders
0:44
Inserting Orders
0:45
Version 19.0 Overview
3:45
Version 19.0 Changes & Example Part 1
3:46
Version 19.0 Changes & Example Part 2
13:18
Version 19.1 Overview
21:10
Version 19.1 Changes
21:11
Version 19.1 Coding Example
22:18
User Authentication

26m 13s

Intro
0:00
Lesson Overview
0:10
Lesson Overview
0:11
User Authentication
1:31
User Authentication
1:32
Authentication with Sessions
2:34
Authentication with Sessions
2:35
Four Steps in Authentication via Sessions
2:50
Using Sessions to Restrict Access
3:58
Using Sessions to Restrict Access
3:59
Coding Example: Restricted Access Area
4:47
Authentication Methods
5:54
Authentication Methods Overview
5:55
Coding Example: Authentication Methods
7:31
Logging Out
9:57
Logging Out
9:58
Coding Example: Log Out
10:47
Users Table
13:50
Users Table
13:51
Example: Creating a Users Table
15:08
Password Hashing
17:30
Password Hashing
17:31
PHP and MySQL Built-in Hash Functions: sha1 ( ) and md5 ( )
18:43
Coding Example: Password Hashing
19:27
Required Homework
24:41
Required Homework: 1 - 4
24:42
Web Application Development

14m 36s

Intro
0:00
Version 20.0 Overview
0:13
Version 20.0 Changes & Examples Part 1
0:14
Version 20.0 Changes & Examples Part 2
5:05
Version 20.0 Changes & Examples Part 3
7:29
Version 20.0 (cont.)
8:31
Version 20.0 Changes & Examples Part 4
8:32
Loading...
This is a quick preview of the lesson. For full access, please Log In or Sign up.
For more information, please see full course syllabus of Advanced PHP
Bookmark & Share Embed

Share this knowledge with your friends!

Copy & Paste this embed code into your website’s HTML

Please ensure that your website editor is in text mode when you paste the code.
(In Wordpress, the mode button is on the top right corner.)
  ×
  • - Allow users to view the embedded video in full-size.
Since this lesson is not free, only the preview will appear on your website.
  • Discussion

  • Study Guides

  • Download Lecture Slides

  • Table of Contents

  • Transcription

  • Related Services

Start Learning Now

Our free lessons will get you started (Adobe Flash® required).
Get immediate access to our entire library.

Sign up for Educator.com

Membership Overview

  • Unlimited access to our entire library of courses.
  • Search and jump to exactly what you want to learn.
  • *Ask questions and get answers from the community and our teachers!
  • Practice questions with step-by-step solutions.
  • Download lesson files for programming and software training practice.
  • Track your course viewing progress.
  • Download lecture slides for taking notes.
  • Learn at your own pace... anytime, anywhere!

SQL Command: SELECT

  • The SELECT command is used to retrieve rows from a database whose data match specified criteria. A SELECT command has the following basic syntax:
    SELECT col1Name, col2Name FROM tblName;
  • The results of a SELECT query are returned in tabular format with columns ordered by how they are listed in the query.
  • To specify the retrieval of all columns from a table, the wildcard character, '*', can be used in place of the list of column names.
    • Note: the columns of the data returned are ordered according to the order of the columns in the table when using the wildcard character.
  • WHERE clauses can be used to refine the results of a query. A row will be returned if the expression specified in a WHERE clause, known as a where condition, evaluates TRUE for that row.
  • A SELECT query with a WHERE clause has the following syntax:
    SELECT * FROM items WHERE itemID=1001;
  • A where condition can contain both logical and comparison operators:
    • Logical: OR, AND, NOT
    • Comparison: =, !=, >, >=, <, <=
  • When specifying string literals in where conditions, strings should be enclosed in quotation marks (single or double).
    • Note: be sure to properly escape quotation marks
  • An ORDER BY clause can be added to a SELECT query to specify how the rows of data retrieved should be ordered. A SELECT query containing an ORDER BY clause has the following syntax:
    SELECT * FROM items ORDER BY price;
  • The default sort order is ascending. Descending order can be specified with the DESC keyword:
    SELECT * FROM items ORDER BY price DESC;
  • An ORDER BY clause can specify multiple columns to order the rows by:
    SELECT * FROM items ORDER BY imageFileExt, price;
  • Additional Resources:

SQL Command: SELECT

Lecture Slides are screen-captured images of important points in the lecture. Students can download and print out these lecture slide images to do practice problems as well as take notes while watching the lecture.

  • Intro 0:00
  • Lesson Overview 0:10
    • Lesson Overview
  • SELECT Command 0:42
    • SELECT Command: Definition and Syntax
    • Results of a SELECT Query
    • To Specify the Retrieval of All Columns from a Table
    • Example: SELECT Command
  • WHERE Clause 7:11
    • WHERE Clause
    • A SELECT Query with WHERE Clause has the Following Syntax
    • A Where Condition can Contain both Logical and Comparison Operators
  • Literal Values 13:24
    • Literal Values
    • Example: Literal Values
  • ORDER BY Clause 15:31
    • ORDER BY Clause & SELECT Query
    • Ascending and Descending Order
    • An ORDER BY Clause can Specify Multiple Columns to Order the Rows by
    • Example: SELECT Query with WHERE and ORDER BY Clause
  • Homework Challenge 21:59
    • Homework Challenge
  • Homework Challenge (cont.) 23:54
    • Homework Challenge

Transcription: SQL Command: SELECT

Hello again, and welcome back to Educator.com's Advanced PHP with MySQL course.0000

In today's lesson, we are going to learn how to retrieve data from our database using the SQL SELECT command.0005

We are going to go over the syntax of the SELECT command, and then we are going to talk about two different clauses that you can add,0011

which are additions you can add to the SELECT command.0018

The WHERE clause will allow you to restrict what rows from a table you want to retrieve.0023

And the ORDER BY clause is going to allow us to specify, when we retrieve rows from a table using the SELECT command, how we want the results ordered.0031

The SELECT command (or query, as it is often called) is used to retrieve rows from a database that match specified criteria that we choose.0044

Now, it is sometimes called a SELECT command, sometimes called a SELECT statement, sometimes called a SELECT query or just a query.0056

I will use those terms interchangeably; mostly, I will be using the term 'query.'0062

So, if I say, "We are executing a query on the database," that is going to be saying0065

that we are executing a SELECT command on the database, which is going to retrieve data from the database.0070

What the syntax of the SELECT command is: you have the SELECT keyword, and then you specify the names of the columns0077

that you want to retrieve data for, separated by commas.0086

We are saying, "Give me the values for column 1 and column 2" and then the FROM keyword--"from table name" and then you specify the table name.0091

So, this would say, "Give me all the values for column 1 and all the values for column 2 from the table named by Table Name."0101

And what happens is: the SELECT query returns its results in a tabular format ordered by how the columns are listed in the query.0113

For example, if we ran this query, we would get what is called a result set.0125

It is a table of data, and it would have two columns in it, because we specified two columns that we want to retrieve data for.0131

And then, it would have the values of each row in the table Table Name.0147

So, if we have a table called (in this case) Table Name, this query would retrieve the values0155

for the column1 name column and the column2 name column for every row in Table Name table.0162

And it would list them in a tabular format.0169

The order in which these columns are going to appear is the order in which we specify them here.0171

If we would switch it around, then these would be reversed in the result set.0174

And again, the term result set is used to refer to the table of data that is returned when you execute a SELECT query.0179

If you want to retrieve all of the columns from the table (which is something you often want to do),0188

you can use the wildcard in place of the list of column names.0192

So, we could type a query (and this is something that we talked about in the homework example for the last lesson,0196

and when inserting items, we used the database to verify that they were inserted, which we didn't go over).0201

But we said SELECT * from Items, and what that is saying is, "Give me all of the values for all of the columns for all of the rows in the Items table."0207

And what it is going to do is return a table, like this, a result set that is going to have values for all of the columns for all of the different rows in the table.0221

The thing to note is that the order of these columns in the result set is going to be ordered by how the table is defined.0232

For example, when we did an INSERT statement where we just specified values and didn't specify column names,0239

we had to specify the values in the order in which the columns were defined for that particular table.0244

That is how that SELECT * statement is going to work, as well.0249

It is going to return the data and have the columns ordered by how they were defined in the table definition.0252

If we go and log into our MySQL server as phpuser (I'm going to run the MySQL Monitor program), I'm going to go ahead0260

and have it so that we are already using the advanced_php database.0279

And then, what I am going to do is: if I run DESCRIBE Items again, it is going to show us the format of this Items table.0288

It has the columns ordered in this order here: itemID, name, price, description, imageFileExt.0295

I'm going to run a SELECT query that is going to retrieve all of the information from the table.0301

But I am going to do it by specifying each of the column names individually.0309

And as happened in the last lesson, because the descriptions are longer, it is going to run off the page.0321

But I am just going to do it to show you an example of how this works, just specifying the columns.0325

We are saying, "Select all of these different columns from the table Items."0329

Oops, it looks like I have an error in that statement.0341

And we can see here: it kind of runs off the page, but it returns all of the different columns that we are looking for.0347

You can see: one thing to note is that it also says 6 rows and set, which means that there were 6 rows that were returned by this query.0351

The same way--we could run the same exact query in a similar format by using the star.0359

We could say, "Select * from Items," and we are going to get the exact same results.0365

Now, as you saw me do in the last lesson (which I didn't really explain), you can select just certain columns from the particular table.0369

Let's say we just want the item ID, name, and price of each item in the database.0376

Well, we could say, "Give me the item ID, the name, and the price for all the rows in the Items table."0381

And you can see that it returns 6 rows, and it contains the item ID, the name, and the price.0390

And so, this is your result set; and notice that the result set columns are ordered in the way that you specified them.0394

We could alternatively have specified them in a different order--for example, price, name--and now they are switched.0402

And this becomes a little important when we get to interfacing with this database using PHP,0413

because we are going to be accessing these result sets, and so the order0419

that these columns are ordered in when results are returned is going to matter.0422

So, that is why we are talking about it now.0427

The commands that we just ran select all of the rows from a particular table.0433

Typically, when you are running a query on a table, you are going to want to be getting a subset of that particular data.0438

For example, you might want to look up a particular item in the database.0443

Maybe, for example, when we have Item.php, which is our item page on our store application,0446

when we load that page up, when we convert it over to using a MySQL database,0453

we are going to be loading up that item's information from a database.0458

So, we are going to be selecting a row from the Items table that corresponds to the particular item that we want to use.0460

And we are going to use the item ID to look it up; and the item ID is the primary key of that table0466

(which is why we have the primary key--so we can uniquely look up that row).0471

The way you do that in a SELECT query is by adding what is known as a WHERE clause.0475

And a WHERE clause contains what is called a where condition, which is a boolean expression that,0480

if it evaluates true for a particular row, will return that row from the database.0486

For example, here we have a SELECT query with a WHERE statement added.0492

So, we are saying, "Give me all of the columns from the Items table for all of the rows where this where condition is true-- where itemID equals 1001."0495

And we know that itemID is a primary key, so this should only return one result--one row from our table.0506

And so, that is going to allow us to just return information about item 1001.0515

So, if we run SELECT, and I'm just going to return name and price, so that it is easier to see, from Items;0521

what you do is, after the FROM and then the table name, you specify the WHERE keyword, and then you specify your where condition.0533

And in this case, it is a comparison operation; and what it is saying is, "where itemID equals the number 1001."0541

Now, in PHP, in our code, this would actually be a double equal sign--this is a comparison operator.0549

But in SQL, it just uses a single equal sign.0553

We are saying, "Give me the item ID, the name, and the price for all the rows in the Items table where the item ID equals 1001."0557

And when we run this query, we are going to see that we only get one row, and that is because 1001 is a primary key.0569

And so, there is only one row in the table that has that, because by definition, that is what a primary key is.0580

Now, these WHERE conditions can contain both logical and comparison operators.0585

We saw a comparison operator there, where we were saying, "The itemID column is equal to this."0588

The thing to note here, also, is that this item ID here in the WHERE condition refers to a column.0595

So, you are specifying conditions on columns in the table.0602

So, we are saying "where column itemID equals 1001."0604

Something else you could do, for example: there are other comparison operators--all the typical ones you would expect:0608

greater than, less than, less than or equal to, not equal to.0613

For example, we could say, "Give us all of the ones where the price column is greater than 15--all of the items where price is greater than $15."0616

You can also use logical operators, such as or, and, and not, to say, "Give me where itemID equals 1001 or 1002."0625

And you can combine these into savings, just as you can within PHP.0635

For example, let's say we want to get the information for itemID 1001 or itemID 1002.0641

We can use this or logical operator, and say itemID equals 1002.0651

Oops, that is the equal sign.0662

Now, we can see: we get two rows, because this statement says, "Give me the item ID, the name, and the price0665

for all rows and items where this condition is true: itemID equals 1001 or itemID equals 1002."0671

That is only true for two rows in the table, so we get two rows; and these are the values that returned.0678

Now, alternatively, we could say...let's say we did item 1007.0685

We are only going to get one row back, because itemID is never equal to 1007 in the current status of our table.0692

So, we only get one row set; that is another thing to note--if the condition doesn't match anything, you will not get any rows back.0699

But we could have just said "where itemID equals 1007," and we are going to get an empty set.0710

We are going to get a result set with 0 rows in it, because no rows in the table have an itemID equal to 1007.0714

Now, let's try another comparison operator.0722

We will say, "Give me all the items in the table..."0726

Well, first of all, let's take a look at all the items in the table, their names, their prices, and their item ID's.0728

And let's say we want to get all of the items that have a price greater than $15.0736

We are going to say, "Select the itemID, the name, and the price from Items,0740

where price (which is the name of the column) is greater than 15 (which is a literal value)."0745

When we hit Enter, it is going to show that 1002 through 1006--all of those items come back.0753

Another thing we could do is: let's also have it return image file extension.0760

These are the values for the itemID, the name, the price, and the image file extension from Items.0775

And what we are going to do is show how to combine some of these operations to have a more complex query,0781

where we are going to return all of the items, for example, that are JPEG's, that have a jpg image extension, that are greater than $15.0787

Before we do that, I just want to talk about how to specify literal values within WHERE clauses.0797

And it works just like you do when we use our INSERT statement.0806

If you are running a WHERE condition on a column that is a string data type,0810

you have to include the string that you are searching for in quotation marks, because it is a string.0818

For example, let's query it, saying, "Give me all of the columns from the Items table for all of the rows where imageFileExt equals jpg."0824

One thing to note is that, as with our INSERT statement, if you are searching for string values0834

that have the quotation marks as part of the string, you need to escape them with the backslash.0842

The other thing to note that we just saw was: for numeric literals, just as in the INSERT statement,0849

you don't enclose them in quotation marks; you simply write the numeral as is.0854

For example, let's pull the itemID, the name, the price, and the imageFileExt from Items.0859

And let's do it where imageFileExt equals jpg; and notice, we are enclosing 'jpg' in single quotation marks.0868

We are going to see that we get four results, because there are four items in our table that have jpg as the imageFileExt.0883

Well, now let's make this query even a little more restrictive.0889

And we are going to add an and condition to this where condition.0895

And we are going to say "where the imageFileExt equals jpg, and the price is greater than $15."0900

We have two conditions that each row has to meet in order to show up in our result set.0907

And if we look at the result set we just got from where imageFileExt equals jpg, there are four rows, but only three of them have a price greater than $15.0910

So, when we run this query, we should get three rows; and, in fact, we do.0918

We get the three rows where the jpg is the image file extension and the price is greater than $15.0922

We learned how to select columns from a table; we have learned how to specify particular rows that we want to pull from a table.0932

We also can specify how we want the results ordered.0938

For example, when these queries are run, they just go through the table, one row at a time, and pull each row that meets these particular criteria.0942

In this case, they are all being ordered by itemID, because they are going the way they were entered into the table.0952

So, because we entered itemID 1004 before itemID 1005, when we run this query, it shows up with item 1004 listed before item 1005.0960

Well, we could change that; let's say we wanted to order everything by the price.0972

Let's say...sometimes you go to websites, and you can sort the items in a particular department by price.0976

Well, what we could do is run a query on a database that says, "Give me all the items0984

in a particular department, and order them from highest to lowest price."0986

And then, what would happen is: you can create an SQL query that is going to order them by price, in the order that you want.0990

That is what the ORDER BY clause allows us to do.0997

For example, you specify it after the from-the-table-name part of the SELECT command.1000

You specify the keywords ORDER BY, and then you specify the column name, and again, this is the column name that you want to order things by.1011

So, if you want to order things by price, we would specify SELECT * FROM Items, ORDER BY price.1018

For example (and I don't want to pull all of the information up, because it would go off the screen),1025

we are going to pull up the item ID, name, and price from the Items table, and we are going to order it1032

(we are not going to include a WHERE clause here) by price.1038

Now, we can see that they are all ordered (as opposed to 1001, 1002, 1003) in ascending order according to price.1044

One of the other things that you can do, when you specify an ORDER BY clause: after the name of the column that you are ordering by,1052

you can also specify DESC for descending, which means to order them in descending order.1058

Now, the default is ascending, which you can also explicitly specify with ASC.1064

But let's go ahead and do this in descending order by price.1069

I'm going to do it in descending order, and now we can see that they are organized in descending order by price.1075

The other thing that you can do is order by multiple columns.1082

For example, let's say we wanted to have all of the items that have the image file extension jpg show up first,1086

and then have all of those items (after they are ordered according to image file extension) ordered by price.1093

This would say, "Give us all of the columns for all of the rows and items in the Items table;1100

order them in ascending order by imageFileExt" (because imageFileExt is a string, it is going to do them in alphabetical order);1106

"and then, after they are ordered according to image file extension, then order them by price."1112

If we run this command now, SELECT itemID, name, and we add imageFileExt to our list of columns that we are going to be returning,1118

and put ORDER BY on its own line--we are going to order by imageFileExt, and then we are going to order that by price.1134

Now, what we can see is that first, it has been sorted according to the image file extension.1147

Here, g is before j in the alphabet, so all of the gif items are listed first.1152

And then, within the gif items, they are sorted according to price.1158

And within the jpg items, they are sorted according to price (15, 34.99, 59.99, and 79, and so forth).1162

Again, the default sort order is ascending; and so, that is why gif is listed before jpg, and 15 is listed before 79.99.1168

In these more complex ORDER BY queries, where we are ordering by multiple columns, we can also specify descending order1177

for each of the different columns we are ordering by, by specifying the descending modifier after each column name.1184

Let's say we want to order them both in descending order.1192

We could specify descending twice; and you have to include it directly after the name of the column that you want to specify it by.1196

And here, if I hadn't explicitly said it, you want to separate the columns that you are ordering by, by a comma.1203

And now, when we run this query, we can see that JPEG's come first, and then they are listed in descending order of price.1209

And so, there are a lot of different combinations that you can do with this to get the data in the way that you want.1217

We have a way to select columns from rows in a table.1224

We have a way to specify what rows we want from a table.1228

And then, we have a way to specify the order.1231

We can put this all together by using SELECT queries that have both WHERE and ORDER BY clauses.1234

Let's say we want the itemID, the name, the price, and the imageFileExt from Items, and we are going to specify a WHERE clause1240

where price is greater than $15, and then we simply, after that,1251

to include an ORDER BY clause, include a space, and then ORDER BY, price, DESC.1258

We have combined both a WHERE condition and an ORDER BY clause to get this result set.1265

This is going to say, "Give me the values for itemID, name, price, and imageFileExt for all rows in the Items table1270

where their price column has a value greater than 15, and then order those results (those rows) in descending order by price."1277

Now, when we run it, we can see that we have all of our items, which is 5 items out of 6, that have a price greater than $15.1286

And they are ordered in descending order by price.1292

And so, as you can see, we can get some really complex queries, and it is an extremely powerful statement1295

that we can use to do all sorts of different things for getting whatever information we want out of our database,1301

which is a lot of power--the SELECT statement is really the biggest of all SQL statements.1308

That is where a lot of the power lies in our relational databases.1315

For the homework challenge, I just want you to practice running some SELECT queries.1320

You will log into the MySQL database using MySQL Monitor, log in as the phpuser, and then run a couple of these SELECT queries.1323

And what I have done is defined in this list...described the kind of queries I want you to write.1331

And then, what you ought to do is determine whether to add a WHERE clause, whether to add an ORDER BY clause,1339

whether to use a star, whether to specify the columns individually, and so forth.1345

And then, what I have done is: after each bullet point (representing a separate query I want you to run),1351

I have specified the number of rows that you should get back.1356

In this case, these first two should both return 6 rows, assuming you have the default setup that we did in the last lesson,1359

which has the default 6 items in our store.1365

And then, that way, you can have a check to make sure that you are writing the query right--1369

at least a partial check to make sure that you are writing the right query and that you are getting the appropriate number of results.1373

For example, for the second one, I want you to get the itemID, the name, and the price values for each row in the table.1379

And in this case, because it is for each row, we are not going to specify a WHERE condition.1387

So, this one would just be a SELECT statement that would say to select itemID, name, price from Items.1391

And I told you how to answer that one, and it is going to return 6 rows back.1397

Then, we are going to do a couple of different things.1402

You are going to get practice using the OR operator: have a WHERE clause that has an or--where itemID equals 1001 or 1004.1404

You are going to use the greater than symbol.1414

We are going to combine some of these; so you are going to get to use the AND condition of a WHERE condition.1415

You are going to say, "If a price is greater than a certain amount, and the image file extension equals a certain amount..."1420

Remember to include strings in quotations.1425

And then, you will get practice with the ORDER BY clause.1428

You are going to have to order by price, order by price in descending order...1430

And then, you are just going to get more and more complex queries, just to give you practice with writing these queries,1436

because they can get really long and complex, and they are going to be prone to errors.1441

And you are going to make errors, and that is OK.1443

And I encourage you, as much as possible, to do all of these homework examples.1445

Really get your hands dirty with it, because it is something you really just need to do to get a feel for it and to become comfortable with it.1451

And I encourage you to experiment with any queries on your own.1457

Maybe make up a query: say, "I want to get the name and file extension of all items that have an item ID greater than 1002," for example.1459

And then, maybe you will specify, "I want to order them according to name in descending order."1476

So, they will be in descending alphabetical order by name; and so forth.1481

And feel free to play around with it as much as you want; you are not going to break the database.1484

And it will give you practice writing these queries, and so forth.1487

That ends today's lesson; thank you for watching Educator.com--I look forward to seeing you next time.1492

Educator®

Please sign in to participate in this lecture discussion.

Resetting Your Password?
OR

Start Learning Now

Our free lessons will get you started (Adobe Flash® required).
Get immediate access to our entire library.

Membership Overview

  • Available 24/7. Unlimited Access to Our Entire Library.
  • Search and jump to exactly what you want to learn.
  • *Ask questions and get answers from the community and our teachers!
  • Practice questions with step-by-step solutions.
  • Download lecture slides for taking notes.
  • Track your course viewing progress.
  • Accessible anytime, anywhere with our Android and iOS apps.