Stored Routine
Explore the Flashcards:
1 of 78
A set of SQL statements saved and executed as a unit.
2 of 78
Query Flow
The sequence or order in which SQL statements are executed.
3 of 78
IN Parameter
A parameter in a stored procedure that doesn't allow input values to be passed.
4 of 78
OUT Parameter
A parameter in a stored procedure that returns a value to the caller.
5 of 78 
Venn Diagram
A diagram representing mathematical or logical sets pictorially as circles.
6 of 78
Aggregated Data (SQL)
A single value (such as an average or a sum) obtained after combining a set of values.
7 of 78
Base Tables
Tables from a database storing data physically (i.e. on the disk).
8 of 78
Invoking a Procedure
The process of executing a stored procedure in the database.
9 of 78
User-Defined Functions
Functions created by the user to perform specific tasks and return values.
10 of 78
Delimiter
A character or sequence of characters used to specify the boundary between elements. The default delimiter in MySQL is ;.
11 of 78
Temporary Delimiter
A delimiter used temporarily to define and specify the boundary between elements when the default delimiter ( ; ) is part of the given SQL statement.
12 of 78
BEGIN – END Block
A block of code in MySQL where global variables are often defined and used.
13 of 78
MySQL Indexes
Database objects that improve the speed of data retrieval operations on a database table.
14 of 78
Inner SELECT Statement (a subquery)
A SELECT statement employed within a larger query or subquery.
15 of 78
DISTINCT keyword
An SQL keyword used for returning unique values from a query result set.
16 of 78
AS Keyword
An SQL keyword used for renaming a column or table in the query result.
17 of 78
ADD
An SQL keyword used for adding new elements, such as columns to a table.
18 of 78
Function
A stored routine that returns a value.
19 of 78
Aggregate Functions
Functions in SQL that perform a calculation on a set of values and return a single value.
20 of 78
COUNT() Function
Returns the number of rows that match a specified criterion.
21 of 78
SUM() Function
An aggregate function in SQL that returns the sum of a set of values or an expression.
22 of 78
MIN() Function
An aggregate function that returns the smallest value in a set of values.
23 of 78
MAX() Function
An aggregate function that returns the largest value in a set of values.
24 of 78
AVG() Function
An aggregate function that returns the average value of a numeric column.
25 of 78
COALESCE() Function
Returns the first non-null value in a list.
26 of 78
ALTER statement
An SQL Statement used to alter existing objects.
27 of 78
DROP statement
An SQL Statement used for deleting a database object.
28 of 78
TRUNCATE statement
An SQL Statement used for removing data from a table without deleting the table.
29 of 78
SELECT statement
Retrieves data from database objects like tables.
30 of 78
Outer SELECT Statement
The main query in which a subquery (inner SELECT) is nested.
31 of 78
INSERT statement
An SQL Statement used to insert data into tables.
32 of 78
UPDATE statement
An SQL Statement used to renew existing data in tables.
33 of 78
DELETE statement
An SQL Statement used for removing specified data from a table.
34 of 78
CREATE statement
An SQL Statement used for creating databases and database objects.
35 of 78
USE
SQL command to select a particular database as a default (current) database to work within your session.
36 of 78
TRUNCATE vs. DELETE
The TRUNCATE Statement is used to remove all the records contained in a table, while with DELETE,
you can specify precisely which records you would like to remove.
37 of 78
Permissions
Rights granted to users or roles in a database that define the level of access
and types of operations they are allowed to perform.
38 of 78
GRANT statement
An SQL Statement used for providing certain permissions to users.
39 of 78
REVOKE statement
An SQL Statement used for revoking permissions and privileges of database users.
40 of 78
COMMIT statement
An SQL Statement used for saving changes made in a database transaction.
41 of 78
ROLLBACK clause
An SQL clause used for reverting changes made in a transaction to the last committed state.
42 of 78
SAVEPOINT
An SQL Statement that sets a point within a transaction to which you can later roll back.
43 of 78
FROM
An SQL clause used in a statement to specify the table (or tables) to retrieve data from.
44 of 78
WHERE
An SQL clause used for filtering the results returned by the SELECT statement.
45 of 78
CASCADE
An SQL keyword used to automatically propagate specific changes through the database.
46 of 78
CASCADE DELETE
A referential action for a foreign key that deletes records in child tables
when the corresponding record in the parent table is deleted.
47 of 78
GROUP BY Clause
An SQL Clause used with aggregate functions (such as COUNT()) to group the result set by one or more columns.
48 of 78
ORDER BY Clause
An SQL clause that allows you to sort the result set of a query by one or more columns.
It can sort the data in ascending or descending order.
49 of 78
ASC or DESC in ORDER BY
By default, the ORDER BY clause sorts the data in ascending order. To specify the sorting order explicitly,
you can use either the ASC or the DESC keyword to ascertain an ascending order of the output values or change it into a descending order, respectively.
50 of 78
HAVING Clause
An SQL clause used with GROUP BY to filter groups based on certain conditions.
51 of 78
WHERE vs. HAVING
The WHERE clause allows us to set conditions referring to subsets of individual rows applied before re-organizing the output into groups, while HAVING is applied after the organization of the output into groups. Also, you cannot have aggregated conditions in the WHERE clause.
52 of 78
LIMIT Clause
An SQL clause used to specify the maximum number of records to return.
53 of 78
SET Clause (in UPDATE)
An SQL clause specifying the columns to be updated, as well as their new values.
54 of 78
Wildcard Characters
Special characters used in SQL for pattern matching.
55 of 78
Star Symbol (*)
A special character in SQL used for selecting all columns in a table.
56 of 78
Join Types
Different methods used in SQL queries to combine rows from two or more tables based on related columns.
57 of 78
Related Column
A table column that is used to establish a relationship with the column of another table through related data. 'Related columns' are fundamental to creating relational databases.
58 of 78
Matching Values
Actual data entries in different tables or columns that are identical or related based on certain conditions.
59 of 78
JOIN
A SQL clause used to combine rows from two or more tables, based on one or more related columns.
60 of 78
JOIN Condition
The condition specified in the JOIN clause to determine how to join the designated tables.
61 of 78
INNER JOIN
An SQL clause returning all relevant rows when there is a match in both tables.
62 of 78 
LEFT JOIN
An SQL clause returning all records from the left table and matching records from the right table, or NULL if there is no match.
63 of 78 
RIGHT JOIN
An SQL clause returning all records from the right table and matched records from the left table, or NULL if there is no match.
64 of 78
NULL in LEFT JOIN
A returned value indicating no matching records found in the right table for a record in the left table.
65 of 78
NULL in RIGHT JOIN
A returned value indicating no matching records found in the left table for a record in the right table.
66 of 78 
Full Join
A JOIN operation that returns all the rows from both designated tables regardless if there is a match in any of the tables.
67 of 78
Self Join
Applied when a table must join itself.
68 of 78
Alias
An alternate name given to a column or table in SQL queries.
69 of 78
IN Operator
An SQL operator used for checking if a value is within a set of values.
70 of 78
LIKE Operator
An SQL operator used for pattern matching in string comparison.
71 of 78
BETWEEN… AND… Operator
An SQL operator used for filtering results within a specified range.
72 of 78
EXISTS
A logical operator used to test for the existence of rows in a subquery.
73 of 78
NOT EXISTS
A logical operator used to test for the absence of rows in a subquery.
74 of 78
UNION Operator
An SQL operator that combines the result set of two or more SELECT statements excluding duplicate records.
75 of 78
SQL UNION ALL Operator
An SQL operator that combines the results of two SELECT statements including duplicate records.
76 of 78
At' Sign (@) in MySQL
Used to define user-defined variables.
77 of 78
SQL Engine (also Database Engine)
The part of a database that executes queries and retrieves data.
78 of 78
Procedure
A stored routine that performs a specific action but does not return a value.