PowerPoint 5 Flashcards
What are transactions?
Transactions are logical units of work that consist of one or more database operations, such as inserting, updating, or deleting data.
What is the order of clauses in a SELECT query?
SELECT
FROM
WHERE
ORDER BY
HAVING
What is a join and what kind of joins SQL supports?
A join is a method of linking data between one (self-join) or more tables based on values of the common column between the tables, which are known as FOREIGN KEY columns.
There are 4 kind of joins:
1. INNER JOIN
2. LEFT JOIN
3. RIGHT JOIN
4. CROSS JOIN
Tell me about the INNER JOIN clause
The inner join clause is used to combine rows from two tables based on a join predicate. It selects and includes only the matching rows from both tables in the result set.
The inner join only includes rows that have a matching ‘id’ in both tables
Tell me about the LEFT JOIN clause
The left join selects all rows from the left table and combines them with matching rows from the right table based on the join condition.
If no matching rows are found, the left join still includes the row from the left table in the result set with NULL values for the columns from the right table.
Tell me about the RIGHT JOIN clause
In summary, the right join clause selects data from the right table, including both the matching rows and the rows that have no match in the left table.
If there are no matching rows from the left table, the right join uses NULL values for the columns from the left table in the result set.
Tell me about the RIGHT JOIN clause
In summary, the right join clause selects data from the right table, including both the matching rows and the rows that have no match in the left table.
If there are no matching rows from the left table, the right join uses NULL values for the columns from the left table in the result set.
What are single row functions?
Single row functions are functions in SQL that operate on a single row of a table and return a single value for each row.
Give an explanation of what the following single row functions do:
- UPPER
- LOWER
- CONCAT
- LENGTH
UPPER - converts a string to upper case
LOWER - converts a string to lower case
CONCAT - function concatenates two or more string or numerical values
LENGTH - returns the length of the input string
What are views?
Views are virtual tables in a database that are derived from the result of a query. They provide a way to present data from one or more tables or views in a customized, logical structure, offering data independence, simplified queries, enhanced security, and data consistency.
Some of its downsides are that it might impact performance and that it’s dependent on underlying tables. If a table or column undergoes changes, the view will have to too.
(for instance; table name change or deletion of a column within a table)
What is Transaction Control Language(TCL)?
TCL is a set of SQL commands used to manage transactions within a database.
The primary purpose of TCL is to provide control over the transactional behavior of the database, ensuring data integrity and consistency.
What are the main TCL commands?
COMMIT, ROLLBACK, AND SAVEPOINT
What is the COMMIT command used for?
The COMMIT command is used to permanently save the changes made within a transaction to the database.
Once a COMMIT is issued, the changes become permanent and visible to other users or processes accessing the database.
What is the ROLLBACK command used for?
The ROLLBACK command is used to undo all the changes made within a transaction and restore the database to its state before the transaction began.
ROLLBACK cancels the transaction, discarding any modifications made.
What is the SAVEPOINT command used for?
SAVEPOINT allows you to create a named point within a transaction to which you can later roll back.
It provides a way to create intermediate markers within a transaction, enabling partial rollback or nested transaction handling.
