MODULE 3

MySQL SQL, INSERT INTO, SELECT, WHERE, AND, OR, NOT, ORDER BY and
NULL Values

A. Rationale

MySQL SQL provides a powerful toolset for managing and querying databases
efficiently. The INSERT INTO command allows for seamless addition of new records into
database tables, while the SELECT statement facilitates precise retrieval of data based on
specified criteria. Incorporating clauses like WHERE, AND, OR, and NOT enhances query
precision by filtering results according to specific conditions. Additionally, the ORDER BY
clause organizes query results in ascending or descending order, offering flexibility in data
presentation. Understanding how to handle NULL values ensures comprehensive data
management and accurate representation within MySQL databases.

B. Objectives

1. Employ MySQL SQL syntax, encompassing INSERT INTO and SELECT statements,
to proficiently manage and extract data from relational databases within web
applications.
2. Assess the functionality of MySQL SQL clauses like WHERE, AND, OR, and NOT,
examining their efficacy in refining query results based on specified conditions.
3. Evaluate the effectiveness of the ORDER BY clause in MySQL SQL queries,
determining its ability to organize query results for enhanced data presentation and
user experience.
4. Devise strategies for managing NULL values within MySQL databases, implementing
techniques to ensure data integrity and completeness throughout database
management processes.

C. Pre-Test

1. What prior knowledge or experience do you have with relational database

management systems and SQL syntax before delving into MySQL SQL, including
INSERT INTO, SELECT, WHERE, AND, OR, NOT, ORDER BY, and NULL Values?
2. Have you encountered similar concepts of querying, data manipulation, and
conditional statements in other database management systems or programming
languages?
3. What specific objectives or goals are you aiming to achieve by studying MySQL SQL
and its associated clauses like WHERE, AND, OR, NOT, and ORDER BY, and how
do you plan to apply them?
4. Are there any challenges or areas of confusion you anticipate in understanding the
intricacies of MySQL SQL syntax, query optimization, and handling NULL values,
and how do you intend to address them?
5. How do you envision integrating MySQL SQL skills, including INSERT INTO,
SELECT, WHERE, AND, OR, NOT, ORDER BY, and NULL Values, into your
current or future database management projects to enhance data retrieval,
manipulation, and presentation capabilities?

D. Learning Activities

1|Module 3 - MySQL
MySQL SQL

What is SQL?

SQL is the standard language for dealing with Relational Databases.

SQL is used to insert, search, update, and delete database records.

How to Use SQL

The following SQL statement selects all the records in the "Customers" table:

Keep in Mind That...

 SQL keywords are NOT case sensitive: select is the same as SELECT

In this tutorial we will write all SQL keywords in upper-case.

Semicolon after SQL Statements?

Some database systems require a semicolon at the end of each SQL statement.

Semicolon is the standard way to separate each SQL statement in database systems that allow
more than one SQL statement to be executed in the same call to the server.

In this tutorial, we will use semicolon at the end of each SQL statement.

Some of The Most Important SQL Commands

1. SELECT - extracts data from a database
2. UPDATE - updates data in a database
3. DELETE - deletes data from a database
4. INSERT INTO - inserts new data into a database
5. CREATE DATABASE - creates a new database
6. ALTER DATABASE - modifies a database
7. CREATE TABLE - creates a new table
8. ALTER TABLE - modifies a table
9. DROP TABLE - deletes a table
10. CREATE INDEX - creates an index (search key)
11. DROP INDEX - deletes an index

The MySQL INSERT INTO Statement

2|Module 3 - MySQL
The INSERT INTO statement is used to insert new records in a table.

INSERT INTO Syntax

It is possible to write the INSERT INTO statement in two ways:

1. Specify both the column names and the values to be inserted:

2. If you are adding values for all the columns of the table, you do not need to specify
the column names in the SQL query. However, make sure the order of the values is in
the same order as the columns in the table. Here, the INSERT INTO syntax would be
as follows:

INSERT INTO Example

The following SQL statement inserts a new record in the "Customers" table:

Did you notice that we did not insert any number into the CustomerID field?

The CustomerID column is an auto-increment field and will be generated automatically when
a new record is inserted into the table.

Insert Data Only in Specified Columns

It is also possible to only insert data in specific columns.

The following SQL statement will insert a new record, but only insert data in the
"CustomerName", "City", and "Country" columns (CustomerID will be updated
automatically):

The MySQL SELECT Statement

3|Module 3 - MySQL
The SELECT statement is used to select data from a database.

The data returned is stored in a result table, called the result-set.

SELECT Syntax

Here, column1, column2, ... are the field names of the table you want to select data from. If
you want to select all the fields available in the table, use the following syntax:

SELECT Columns Example

The following SQL statement selects the "CustomerName", "City", and "Country" columns
from the "Customers" table:

SELECT * Example

The following SQL statement selects ALL the columns from the "Customers" table:

The MySQL SELECT DISTINCT Statement

The SELECT DISTINCT statement is used to return only distinct (different) values.

Inside a table, a column often contains many duplicate values; and sometimes you only want
to list the different (distinct) values.

SELECT DISTINCT Syntax

SELECT Example Without DISTINCT

The following SQL statement selects all (including the duplicates) values from the "Country"
column in the "Customers" table:

Now, let us use the SELECT DISTINCT statement and see the result.

SELECT DISTINCT Examples

4|Module 3 - MySQL
The following SQL statement selects only the DISTINCT values from the "Country" column
in the "Customers" table:

The following SQL statement counts and returns the number of different (distinct) countries
in the "Customers" table:

The MySQL WHERE Clause

The WHERE clause is used to filter records.

It is used to extract only those records that fulfill a specified condition.

WHERE Syntax

Note: The WHERE clause is not only used in SELECT statements, it is also used in UPDATE,
DELETE, etc.!

WHERE Clause Example

The following SQL statement selects all the customers from "Mexico":

Text Fields vs. Numeric Fields

SQL requires single quotes around text values (most database systems will also allow double
quotes).

However, numeric fields should not be enclosed in quotes:

Operators in The WHERE Clause

5|Module 3 - MySQL
The following operators can be used in the WHERE clause:

The MySQL AND, OR and NOT Operators

The WHERE clause can be combined with AND, OR, and NOT operators.

The AND and OR operators are used to filter records based on more than one condition:
 The AND operator displays a record if all the conditions separated by AND are TRUE.
 The OR operator displays a record if any of the conditions separated by OR is TRUE.

The NOT operator displays a record if the condition(s) is NOT TRUE.

AND Syntax

OR Syntax

NOT Syntax

AND Example

6|Module 3 - MySQL
The following SQL statement selects all fields from "Customers" where country is
"Germany" AND city is "Berlin":

OR Example

The following SQL statement selects all fields from "Customers" where city is "Berlin" OR
"Stuttgart":

The following SQL statement selects all fields from "Customers" where country is
"Germany" OR "Spain":

NOT Example

The following SQL statement selects all fields from "Customers" where country is NOT
"Germany":

Combining AND, OR and NOT

You can also combine the AND, OR and NOT operators.

The following SQL statement selects all fields from "Customers" where country is
"Germany" AND city must be "Berlin" OR "Stuttgart" (use parenthesis to form complex
expressions):

The following SQL statement selects all fields from "Customers" where country is NOT
"Germany" and NOT "USA":

The MySQL ORDER BY Keyword

7|Module 3 - MySQL
The ORDER BY keyword is used to sort the result-set in ascending or descending order.

The ORDER BY keyword sorts the records in ascending order by default. To sort the records
in descending order, use the DESC keyword.

ORDER BY Syntax

ORDER BY Example

The following SQL statement selects all customers from the "Customers" table, sorted by the
"Country" column:

ORDER BY DESC Example

The following SQL statement selects all customers from the "Customers" table, sorted
DESCENDING by the "Country" column:

ORDER BY Several Columns Example

The following SQL statement selects all customers from the "Customers" table, sorted by the
"Country" and the "CustomerName" column. This means that it orders by Country, but if
some rows have the same Country, it orders them by CustomerName:

ORDER BY Several Columns Example 2

The following SQL statement selects all customers from the "Customers" table, sorted
ascending by the "Country" and descending by the "CustomerName" column:

MySQL NULL Values

8|Module 3 - MySQL
What is a NULL Value?

A field with a NULL value is a field with no value.

If a field in a table is optional, it is possible to insert a new record or update a record without
adding a value to this field. Then, the field will be saved with a NULL value.

Note: A NULL value is different from a zero value or a field that contains spaces. A field with
a NULL value is one that has been left blank during record creation!

How to Test for NULL Values?

It is not possible to test for NULL values with comparison operators, such as =, <, or <>.

We will have to use the IS NULL and IS NOT NULL operators instead.

IS NULL Syntax

IS NOT NULL Syntax

The IS NULL Operator

The IS NULL operator is used to test for empty values (NULL values).

The following SQL lists all customers with a NULL value in the "Address" field:

The IS NOT NULL Operator

9|Module 3 - MySQL
The IS NOT NULL operator is used to test for non-empty values (NOT NULL values).

The following SQL lists all customers with a value in the "Address" field:

E. Formative Test

F. References

1. [Link]
2. [Link]
3. [Link]
4. [Link]
5. [Link]
6. [Link]
7. [Link]

10 | M o d u l e 3 - M y S Q L