Advanced SQL

Section 0: Module Objectives or Competencies

Section 1: Changing Table Structure

The ALTER TABLE command can be used to change a table's structure:

ALTER TABLE tableName

Section 2: Updates

The UPDATE command can be used to make data entries in only part of a row.

Recall that the INSERT command can only be used to enter a full row of data.

To enter the P_SALECODE value '2' in the fourth row:

UPDATE PRODUCT
SET P_SALECODE = '2'
WHERE P_CODE = '1546-QQ2';

SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE, P_SALECODE
FROM PRODUCT
WHERE P_CODE = '1546-QQ2';

To enter the P_SALECODE value '1' in specific rows:

UPDATE PRODUCT
SET P_SALECODE = '1'
WHERE P_CODE IN ('2232/QWE', '2232/QTY');

SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE, P_SALECODE
FROM PRODUCT
WHERE P_CODE IN ('2232/QWE', '2232/QTY');

To assign a sales code based on the P_INDATE:

UPDATE PRODUCT
SET P_SALECODE = '2'
WHERE P_INDATE < '2009-12-25';

UPDATE PRODUCT
SET P_SALECODE = '1'
WHERE P_INDATE >= '2010-01-16' AND P_INDATE <= '2010-02-10';

SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE, P_SALECODE
FROM PRODUCT;

Here is a script for table creation: link. The P_SALECODE field has already been added and populated.

Calculations

Calculations can easily be embedded in UPDATE queries.

• All of the arithmetic operators (+-*/) as well as parentheses can be used.

After restocking, to add 20 units of product 2232/QWE:

UPDATE PRODUCT
SET P_QOH = P_QOH + 20
WHERE P_CODE = '2232/QWE';

To add 10 percent to the price for all products that have current prices below $50:

UPDATE PRODUCT
SET P_PRICE = P_PRICE * 1.10
WHERE P_PRICE < 50.00;

The following query calculates the value of the complete inventory of rat-tail files (assuming P_PRICE is purchase price):

SELECT P_CODE, P_DESCRIPT, (P_QOH * P_PRICE) AS `INVENTORY VALUE`
FROM PRODUCT
WHERE P_CODE = '54778-2T';

Notice the use of AS to give the results a meaningful name. This is referred to as a "column alias".

• If your column alias is one word only, delineators are not required.
• However, if it consists of multiple words it must be enclosed in back quotes.
  • (The back quote is on the upper left of most keyboards.)
• Note that field names are delineated by back quotes, and strings are delineated using quotation marks.

Section 3: Deleting a Table from the Database

A table can be deleted from the database using the DROP TABLE command.

DROP TABLE table_name;

For example, to delete the PART table:

DROP TABLE PART;

A table can be dropped only if it is not the "one" side of a relationship.

• That is, you cannot use DROP TABLE to remove a table that is referenced by a FOREIGN KEY constraint. To drop the table, you must drop referencing table first.

If you want to remove table in a different database, you need to use the following syntax:

DROP TABLE database_name.table_name;

You should be very careful while using this command because once a table is deleted then all the information available in that table will also be lost forever.

Section 4: Ordering a Listing

The ORDER BY clause is used to order the data sets retrieved from a SQL database.

• Listings can be ordered in ascending (ASC) or descending (DESC) order.
• The sorting process is a logical process only; the actual table contents are not affected.

The ORDER BY clause must always be listed last in the command sequence.

To produce a list in ascending order, use the clause:

ORDER BY columnList

To produce a list in descending order, use the clause:

ORDER BY columnList DESC

For example, to list PRODUCT by P_PRICE in ascending order:

SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE
FROM PRODUCT
ORDER BY P_PRICE;

To list PRODUCT by P_PRICE in descending order:

SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE
FROM PRODUCT
ORDER BY P_PRICE DESC;

You can use the ORDER BY clause in conjunction with other SQL commands:

SELECT P_DESCRIPT, V_CODE, P_INDATE, P_PRICE
FROM PRODUCT
WHERE P_INDATE < '2010-01-21' AND P_PRICE <= 50.00
ORDER BY V_CODE, P_PRICE DESC;

• Note both that V_CODE is in ascending order and P_PRICE is in descending order, sorted by V_CODE first.
• According to the standards, NULLs are grouped before known values in ascending order, and after known values in descending order. That is RDBMS-dependent.

Here is a slightly altered example, but with V_CODE in descending order as well:

SELECT P_DESCRIPT, V_CODE, P_INDATE, P_PRICE
FROM PRODUCT
WHERE P_INDATE < '2010-01-21' AND P_PRICE <= 50.00
ORDER BY V_CODE DESC, P_PRICE DESC;

• Note that the NULL now appears after known values because of descending order.

Section 5: Eliminating Duplicates

The DISTINCT clause will yield a list of only those values that are different from one another, i.e., duplicates are eliminated.

For example, to create a list of vendor codes from the PRODUCT table with no duplication:

SELECT DISTINCT V_CODE
FROM PRODUCT;

To get a sorted list of vendor codes from the PRODUCT table with no duplication, you must use the ORDER BY clause:

SELECT DISTINCT V_CODE
FROM PRODUCT
ORDER BY V_CODE;

Section 6: Aggregate Functions

SQL can perform various mathematical summaries for you, such as counting the number of rows that meet a specified condition, finding the minimum or maximum values for some specified attribute, summing the values in a specified column, or averaging the values in a specified column.

Aggregate functions perform a variety of actions such as counting all the rows in a table, averaging a column's data, and summing numeric data.

• Aggregates can also search a table to find the highest "MAX" or lowest "MIN" values in a column.
• As with other types of queries, you can restrict, or filter out the rows these functions act on with the WHERE clause.

Notes:

• All of the aggregate (numeric) functions can be used with or without specifying conditions.
• Aggregate functions can be used in conjunction with more complex logical restrictions.
• Aggregate functions yield only one value based on all the values in the table.

Use aggregate functions as expressions only in the following situations:

• The select list of a SELECT statement (either a subquery or an outer query).
• A HAVING clause.

In other words,

• Aggregate functions can be used in both the SELECT and HAVING clauses.
• Aggregate functions cannot be used in a WHERE clause.

COUNT: counts the number of specific values of an attribute.

To count the number of vendors in the PRODUCT table:

SELECT COUNT( DISTINCT V_CODE )
FROM PRODUCT;

• Do not insert a space between COUNT and the following open parenthesis in MySQL.

Recall that aggregate functions can be used with or without a WHERE clause specifying conditions.

To find the number of vendors referenced in the PRODUCT table who have supplied products with prices that are less than, or equal to, $10.00:

SELECT COUNT( DISTINCT V_CODE )
FROM PRODUCT
WHERE P_PRICE <= 10.00;

SELECT COUNT(*) will return the number of total rows returned by the query, including the rows that contain NULLs.

SELECT COUNT( * )
FROM PRODUCT
WHERE P_PRICE <= 10.00;

MAX: finds the highest (maximum) value for a given attribute.

To find the highest P_PRICE in the PRODUCT table:

SELECT MAX( P_PRICE )
FROM PRODUCT;

It was pointed out earlier that aggregate functions cannot be used in a WHERE clause. Hence, the following query is invalid:

SELECT P_CODE, P_DESCRIPT, P_PRICE
FROM PRODUCT
WHERE P_PRICE = MAX( P_PRICE );

We'll see how to find the product that has the highest price in the next section.

MIN: finds the lowest (minimum) value for a given attribute.

To find the product with the oldest date:

SELECT MIN( P_INDATE )
FROM PRODUCT;

To find the product that has the lowest inventory value:

SELECT *
FROM PRODUCT
WHERE P_QOH*P_PRICE = (SELECT MIN(P_QOH*P_PRICE) FROM PRODUCT);

SUM: computes the total for any specified attribute.

To find the total value of all items carried in inventory:

SELECT SUM ( P_QOH * P_PRICE ) AS `TOTAL VALUE`
FROM PRODUCT;

Again, notice the use of AS to give the results an alias, and note the back quotes that are required in MySQL.

AVG: determines the average value of a specific attribute.

To determine the average P_PRICE value:

SELECT AVG ( P_PRICE )
FROM PRODUCT;

Section 7: Grouping Data

The GROUP BY clause specifies how to group rows from a data table when aggregating information, while the HAVING clause filters out rows that do not belong in specified groups.

The GROUP BY clause can be used to create frequency distributions.

• A frequency distribution is an overview of all distinct values in some variable and the number of times they occur. That is, a frequency distribution tells how frequencies are distributed over values. Frequency distributions are mostly used for summarizing categorical variables.
• The GROUP BY clause can be used with any of the aggregate functions.

Aggregate functions are normally used in conjunction with a GROUP BY clause.

The GROUP BY clause enables you to use aggregate functions to answer more complex managerial questions such as

• What is the average salary of database architects in each division?
• How many database architects work in each division?
• How many database architects are working on a particular project?

The syntax is:

SELECT columnList
FROM tableList
[WHERE conditionList ]
[GROUP BY columnList ]
[HAVING conditionList ]
[ORDER BY columnList [ASC | DESC] ];

MySQL evaluates the GROUP BY clause after the FROM and WHERE clauses and before the HAVING, SELECT, DISTINCT, ORDER BY and LIMIT clauses:

Examples

To find the minimum price for each sale code:

SELECT P_SALECODE, MIN( P_PRICE )
FROM PRODUCT
GROUP BY P_SALECODE;

To find the average price within each sales code:

SELECT P_SALECODE, AVG( P_PRICE )
FROM PRODUCT
GROUP BY P_SALECODE;

If you dislike those extraneous digits to the right of the decimal, ROUND:

SELECT P_SALECODE, ROUND( AVG( P_PRICE ), 2)
FROM PRODUCT
GROUP BY P_SALECODE;

This example lists the number of products by vendor along with the average price:

SELECT V_CODE, P_DESCRIPT, COUNT(DISTINCT(P_CODE)), AVG(P_PRICE)
FROM PRODUCT
GROUP BY V_CODE;

Note that in previous versions of MySQL, GROUP BY could be used to provide sorting as well. If a query specified GROUP BY, the result was sorted as if ORDER BY was present in the query. Newer versions will no longer support either implicit or explicit sorting for GROUP BY (link).

HAVING Clause

The HAVING clause is used to specify conditions that must be met by the records before they will be listed by the GROUP BY clause, i.e., it specifies which grouped records are displayed in a SELECT statement with a GROUP BY clause.

• After GROUP BY combines records, HAVING displays any records grouped by the GROUP BY clause that satisfy the conditions of the HAVING clause.
• HAVING is optional.
• HAVING is similar to WHERE, which determines which records are selected.
• After records are grouped with GROUP BY, HAVING determines which records are displayed.

Example:

SELECT P_DESCRIPT, SUM(P_QOH)
FROM PRODUCT
GROUP BY P_DESCRIPT
HAVING SUM( P_QOH ) > 100;

This example lists the number of products by vendor along with the average price, but includes only the rows with an average price below 10.00:

SELECT V_CODE, COUNT(DISTINCT(P_CODE)), AVG(P_PRICE)
FROM PRODUCT
GROUP BY V_CODE
HAVING AVG(P_PRICE) < 10;

The following Learn Basic SQL Commands video discusses SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY.

Additional Resources

• MySQL Tutorial: GROUP BY
• MySQL 8.0 Reference Manual: MySQL Handling of GROUP BY
• javatpoint: MySQL Group By Clause

Section 8: Nested Queries

We learned how to find the highest price in the PRODCUT table, but it is considerably more difficult to find the product associated with the highest price.

Nested queries, or subqueries or inner queries, are a tool for performing operations in multiple steps.

• For example, if you wanted to take the sums of several columns, then average all of those values, you'd need to do each aggregation in a distinct step.
• A nested query is a query within another SQL query that is embedded within the WHERE clause, and is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

To find the product that has the highest price:

SELECT P_CODE, P_DESCRIPT, P_PRICE
FROM PRODUCT
WHERE P_PRICE = (SELECT MAX( P_PRICE ) FROM PRODUCT);

• Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses. This is pointed out in red font in the query above.
• Recall that in a comparison that uses an equality symbol, you can use only a single value to the right of the equals sign.
• Therefore, a nested query is necessary to obtain the desired results.

A nested query (or subquery) is composed of two parts:

• The inner query, which is executed first.
• The outer query, which is executed last. The outer query is always the first SQL command encountered in the command sequence.

To list in descending order all products whose price exceeds the average price:

SELECT P_CODE, P_DESCRIPT, P_INDATE, P_QOH, P_MIN, P_PRICE, P_DISCOUNT, V_CODE, P_SALECODE
FROM PRODUCT
WHERE P_PRICE > ( SELECT AVG( P_PRICE ) FROM PRODUCT )
ORDER BY P_PRICE DESC;

• The inner query is denoted by the red font, and the outer query by the green font.
• This command sequence provides the outer query with a value to compare each P_PRICE value to.

Intermediate Query Approach

Alternate approach – intermediate queries instead of nested queries:

CREATE TABLE Q1a_results
SELECT AVG( P_PRICE ) AS AvgPrice
FROM PRODUCT;

SELECT P_CODE, P_DESCRIPT, P_INDATE, P_QOH, P_MIN, P_PRICE, P_DISCOUNT, V_CODE, P_SALECODE
FROM PRODUCT, Q1a_results
WHERE P_PRICE > Q1a_results.AvgPrice
ORDER BY P_PRICE DESC;

• The first query performs the same task as the nested query above, but is saved as a table.
• There is an interesting discussion about readability of nested objects and the maintainability of the code at the bottom of this page.

Section 9: Indexes

Indexes are special lookup tables that the database search engine can use to speed up data retrieval by improving the efficiency of searches.

While the DBMS automatically creates a unique index when you designate a primary key, you sometimes need additional indexes.

Indexes are special lookup tables that the database search engine can use to speed up data retrieval.

• Simply put, an index is a pointer to data in a table.
• An index is a distinct structure in the database that is built using the create index statement.
• It requires its own disk space and holds a copy of the indexed table data.
• That means that an index is pure redundancy.

SQL indexes can be created on the basis of any selected attribute.

CREATE [UNIQUE] INDEX indexName
ON tableName (column1 [, column2])

For example, to create an index named P_INDATEX based on the attribute P_INDATE of PRODUCT:

CREATE INDEX P_INDATEX ON
PRODUCT(P_INDATE);

A common practice is to create an index on any field that is used as a search key, in comparison operations in a conditional expression, or when you want to list rows in a specific order.

By default, all indexes produce results that are listed in ascending order, but you can alter that:

CREATE INDEX PROD_PRICEX
ON PRODUCT(P_PRICE DESC);

To delete an index, use DROP INDEX:

DROP INDEX PROD_PRICEX;

Although indexes are intended to enhance a database's performance, there are times when they should be avoided.

The following guidelines indicate when the use of an index should be reconsidered.

• Indexes should not be used on small tables.
• Tables that have frequent, large batch updates or insert operations.
• Indexes should not be used on columns that contain a high number of NULL values.
• Columns that are frequently manipulated should not be indexed.

For more on indexes:

• Anatomy of an SQL Index
• SQL - Indexes

Section 10: Copying Parts of Tables

If normalization requires that a table be divided into separate table structures, SQL allows selected table columns to be copied.

INSERT INTO target_tableName [(target_columnlist)]
SELECT source_columnlist
FROM source_tablename;

To copy selected table columns from a table, first create a new table structure to receive the data.

• the new table column names do not have to be identical to those of the original table.
• the new table does not have to have the same number of columns as the original table.
• column characteristics must match (character to character, numeric to numeric, etc.)

CREATE TABLE PART (
PART_CODE VARCHAR(10) NOT NULL UNIQUE,
PART_DESCRIPT VARCHAR(35),
PART_PRICE DECIMAL(8,2),
V_CODE VARCHAR(5),
PRIMARY KEY (PART_CODE));

INSERT INTO PART (PART_CODE, PART_DESCRIPT, PART_PRICE, V_CODE)
SELECT P_CODE, P_DESCRIPT, P_PRICE, V_CODE FROM PRODUCT;

SELECT * FROM PART;

SQL provides another way to rapidly create a new table based on selected columns and rows of an existing table, but the syntax varies from one RDBMS to another.

Here is the syntax in MySQL:

CREATE TABLE PART2
SELECT P_CODE AS PART_CODE, P_DESCRIPT AS PART_DESCRIPT,
P_PRICE AS PART_PRICE, V_CODE
FROM PRODUCT;

When you create a new table based on another table (using the CREATE TABLE ... SELECT approach), the new table does not include the integrity rules from the original table.

SQL Server and Access use the SELECT INTO command to accomplish a similar task.

Section 11: Primary and Foreign Key Designation

Primary and foreign keys can be specified for new tables.

Recall that when you create a new table based on another table (using the CREATE TABLE ... SELECT approach), the new table does not include a primary key.

To define a primary key for the new PART2 table, use the following:

ALTER TABLE PART2
ADD PRIMARY KEY (PART_CODE);

Other scenarios could leave a table without entity and referential integrity:

• Keys were not defined when tables were created.
• Table imports may not transfer integrity rules.

To designate the PART table's foreign key:

ALTER TABLE PART2
ADD CONSTRAINT V_CODE
FOREIGN KEY (V_CODE) REFERENCES VENDOR (V_CODE);

You can incorporate both changes at once:

ALTER TABLE PART2
ADD PRIMARY KEY (PART_CODE)
ADD CONSTRAINT V_CODE
FOREIGN KEY (V_CODE) REFERENCES VENDOR (V_CODE);

Section 12: Outer Join (Left and Right)

The image below again shows the results of inner joining the PRODUCT and VENDOR tables.

• Only 14 of the 16 PRODUCT rows are listed because two products have NULL in the V_CODE attribute.
• Because there is not a matching null "value" in the VENDOR table's V_CODE attribute, the products are not included in the final output generated by the join.
• The VENDOR table also includes several vendors with no matching V_CODE in the PRODUCT table.

Difference

Though both inner and outer joins include rows from both tables when the match condition is successful, they differ in how they handle a false match condition.

Inner joins do not include non-matching rows; whereas, outer joins do include them.

Hence, to include the missing rows in the result, an Outer Join is required.

There are two types of outer join: left and right.

• The left and right designations reflect the order in which the tables are processed by the DBMS.

The Left Outer Join returns not only the rows matching the join condition (that is, rows with matching values in the common column) but also the rows in the left-side table with unmatched values in the right-side table.

The Left Outer Join returns all records from the left table, and the matched records from the right table.

Example: To see all VENDOR rows and all matching PRODUCT rows (i.e., reveal all the vendors who supply no products) a left outer join can be used:

SELECT P_CODE, VENDOR.V_CODE, V_NAME
FROM VENDOR LEFT JOIN PRODUCT
ON VENDOR.V_CODE = PRODUCT.V_CODE;

• The Left Outer Join lists the product code, vendor code, and vendor name for all products, and includes those vendors with no matching products.

The Right Outer Join returns not only the rows matching the join condition (that is, rows with matching values in the common column) but also the rows in the right side table with unmatched values in the left side table.

The Right Outer Join returns all records from the right table, and the matched records from the left table.

Example: To see all PRODUCT rows with all matching VENDOR rows (i.e., all the products that currently have no vendor to supply them) a Right Outer Join can be used:

SELECT P_CODE, VENDOR.V_CODE, V_NAME
FROM VENDOR RIGHT JOIN PRODUCT
ON VENDOR.V_CODE = PRODUCT.V_CODE;

• The Right Outer Join lists the product code, vendor code, and vendor name for all products, and includes those products that do not have a matching vendor code.

There is also a Full Outer Join that uses the keywords FULL OUTER JOIN to return all records when there is a match in either left or right table, but not directly in MySQL.

Section 13: Recursive Join

Recall that an alias may be used to identify the source(s) from which the data are taken.

SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE
FROM PRODUCT P, VENDOR V
WHERE P.V_CODE = V.V_CODE
ORDER BY P_PRICE;

Recursive Joins

An alias is especially useful when a table must be joined to itself in a recursive query.

Suppose you have the EMPLOYEE table below:

Using the data in the EMPLOYEE table, you can generate a list of all employees with their manager's names by joining the EMPLOYEE table to itself.

• In that case, you would use aliases to differentiate the table from itself.

SELECT EMP.E_MGR AS MGR_NUM, MGR.E_LNAME AS MGR_LNAME, EMP.E_NUM, EMP.E_LNAME
FROM EMPLOYEE EMP, EMPLOYEE MGR
WHERE EMP.E_MGR = MGR.E_NUM
ORDER BY EMP.E_MGR;

Section 14: Top Clause

The Top clause tells the query engine to return the specified number of records.

Top is not ANSI/ISO compliant.

This example returns the top 3 records based on P_PRICE:

SELECT P_DESCRIPT, P_PRICE
FROM PRODUCT
ORDER BY P_PRICE
LIMIT 0,3;

• The lower bound is not required, but allows you to page through your data.
• Note that it lists the top lowest prices.

To get the top three prices (highest):

SELECT P_DESCRIPT, P_PRICE
FROM PRODUCT
ORDER BY P_PRICE DESC
LIMIT 0,3;

The TOP clause is only supported by the SQL Server and MS Access database systems.

• MySQL uses LIMIT instead of Top.
• Oracle provides ROWNUM clause for the SELECT statement to restrict the number of rows returned by a query.
• ANSI/ISO SQL:2008 introduced a simpler syntax for FETCH FIRST, which may be more analogous to Microsoft/Sybase TOP syntax:
  

  SELECT P_DESCRIPT, P_PRICE
  FROM PRODUCT
  ORDER BY P_PRICE
  FETCH FIRST 3 ROWS ONLY

Section 15: MySQL Prepared Statements (Parameterized Queries)

A parameterized query (referred to as a prepared statement in MySQL) is a query in which placeholders are used for parameters and the parameter values are supplied at execution time, typically by prompting the user to enter a parameter value.

• Instead of writing raw SQL code, you write a statement with placeholders (?) where you want variable to go, then attach variables to those placeholders.

Developing a Prepared Statement

While MySQL prepared statements are not exactly the same as parameterized queries, they are the closest alternative.

In order to use MySQL prepared statements, you use the three following statements:

• PREPARE – prepares a statement for execution.
• EXECUTE – executes a prepared statement prepared by the PREPARE statement (can be executed one or more times).
• DEALLOCATE PREPARE – releases a prepared statement.

Here is an example:

PREPARE listModels FROM
"SELECT modelNum, modelDescription, modelManufacturer, modelInventoryCount
FROM productcategory
WHERE modelDescription LIKE ?";

SET @modelDesc = 'Kiteboard%';

EXECUTE listModels USING @modelDesc;
DEALLOCATE PREPARE listModels;

The query can be enclosed in single or double quotes, but if you use a single quote as part of the query, e.g., as part of a CONCAT operation, then you need to use double quotes around the query.

• For more detail, visit Single Quote, Double Quote, and Backticks in MySQL Queries.

Saving a Prepared Statement as a Stored Procedure

In phpMyAdmin, you can create the prepared statement as a stored procedure that includes a parameter to which the placeholder can be compared.

• When the stored procedure that contains the prepared statement is executed, phpMyAdmin will open a dialog box that allows the user to provide the value to which the placeholder will be compared.

Here is an example:

DELIMITER $$
CREATE PROCEDURE `modelListPreparedStatement` (IN `modelDesc` varchar(45))
BEGIN
PREPARE listModels FROM
"SELECT modelNum, modelDescription, modelManufacturer, modelInventoryCount
FROM productcategory
WHERE modelDescription LIKE ?";
EXECUTE listModels USING modelDesc;
DEALLOCATE PREPARE listModels;
END$$
DELIMITER ;

The dialog box for this example is shown below. The user enters the comparison value in the Value input field.

Using prepared statements with placeholders for parameter values has the following benefits:

• Prepared statements improve performance. The increase in performance in prepared statements can come from a few different features:
  • First is the need to only parse the query a single time.
    • When the statement is initially prepared, MySQL will parse the statement to check the syntax and set up the query to be run.
    • Then if the query is executed many times, it will no longer have that overhead.
    • This pre-parsing can lead to a speed increase if the same query needs to be run many times, such as when doing many INSERT statements.
  • It also minimizes bandwidth usage, since upon every execution only the placeholder values need to be transmitted to the database server instead of the complete SQL statement.
• Protection against SQL injection attacks.
  • Parameter values are not embedded directly inside the SQL query string. (link)
    • The parameter values are sent to the database server separately from the query using a different protocol and thus cannot interfere with it.
    • The server uses these values directly at the point of execution, after the statement template is parsed.
    • This makes prepared statements less error-prone, and thus considered as one of the most critical elements in database security.
  • The separation of logic and data allows MySQL to automatically take into account potentially dangerous characters (such as unescaped SQL quote and delimiter characters) and they do not need to be escaped using any special function.
• Parameterized queries force user input to be a specific data type for a particular field in SQL query.
• The main advantage of a parameterized query is that the query does not need to be prepared each time it is run.

To learn more about Prepared Statements, visit these links:

• MySQL Prepared Statement
• SQL Syntax for Prepared Statements
• Prepared statements and stored procedures
• How and Why to Use Parameterized Queries