What is an SQL function?
An SQL function is a piece of code that performs operations and return results.
What do set functions do?
Set functions turn a column of data into a computed value (e.g., min, max, average).
What is a built-in function and what does it do?
Built-in functions are readily available for the user to use. They take one value and return one value (e.g., LOWER, UPPER, TITLE).
What is a user-defined function?
A user-defined function needs to be created by the user and then can be used throughout the program.
What does the SELECT UPPER built-in function do?
SELECT UPPER makes a string uppercase.
What does the SELECT LOWER built-in function do?
SELECT LOWER makes a string lowercase.
Why would we use built-in functions in a database?SELECT * FROM SALES1WHERE LOWER(store) London;
We can use built-in functions in a WHERE clause to return results where the data matches the function.The example will show results where store = 'london' in lowercase.
What is a transaction?
A transaction is a sequence of operations that is performed against a database in a logical way.
If one operation in a transaction fails
A rollback restores the database to its original state and no changes to the database are committed.
What happens if none of the operations in a transaction fail?
All the changes caused by the transaction are committed to the database.
What does COMMIT do?
COMMIT applies changes to the database.
What does ROLLBACK do?
ROLLBACK prevents changes to the database from being made.
Does MySql automatically commit changes to the database?
Yes. We need to SET auto commit to OFF to change this.
What is the syntax for a transaction, using a booking example?
START TRANSACTION;<get latest booking number,increase it by one,insert a new booking into a table,check that it is available,insert booking info into another table,>COMMIT;
What is an SQL index?
A kind of data structure that enables the program to find a requested piece of data without checking every row of the database sequentially.
What does MySQL use indexes for?
To quickly find rows with specific column values.
What does the server have to do without an index?
Scan the whole table to locate the relevant rows. The larger the table, the longer this takes.
What are the 6 advantages of indexing?
They:1) Prevent queries from having to do a full scan of the data set.2) Access (and therefore lock) fewer rows during queries.3) Speed up queries.4) Prevent sorting records after fetching them.5) Can impose constraints (e.g., data uniqueness).6) Join datasets efficiently.
Primary Index
If the index is based on the primary key, it is called the primary index. Primary keys are unique to each record. Searching data using the primary index is efficient because it stores data in the sorted order
What is a dense primary index?
In a dense index, the number of records in the index table is similar to the number of records in the main table.
What is a sparse primary index?
In a sparse index, there are fewer index records and each index record points to a block of records in the main table. The primary memory stores the mapping of addresses. The secondary memory performs the searching for the actual data.
What is a secondary index?
The secondary index is an index type that works by selecting a range for the column(s).
How could we use an index to find 'Michael Scott' in an unordered list of marathon runners?
Rather than searching all data sequentially, add a single column index on last_name to create a subset of records where last_name = 'Scott' to search through.
What is the syntax to create an index?
CREATE INDEX index_nameON table_name(column_name);CREATE INDEX last_name_idxON marathon_registration(last_name);
What does EXPLAIN SELECT * + WHERE DO?EXPLAIN SELECT * FROM marathon WHERE phone LIKE '456%'
It is another way of searching for a subset of results.The example will return all results from the marathon table where a phone number starts with 456.
What is table locking?
Table locking is a mechanism that prevents other users from making changes to the data whilst updates/maintenance/actions are performed.It is designed to ensure the integrity of the data in the database and is essential for transaction management.
What is a read lock?
Whilst a READ lock is imposed, users can read data from the table, but cannot write to it. Write operations will be put into a queues until the read lock is released.
What happens when you end a read lock session?
If the session is normally or unexpectedly terminated, all locks will be released automatically.
What is the downside of indexing?
Indexing makes adding a row to the table slower because both the table and the index need to be updated.
What is auto increment?ALTER TABLE Persons AUTO_INCREMENT=100;
If a column is set to AUTO_INCREMENT, then it automatically generates a unique number for every new record entered into the table. It is often used in the Primary Key. It starts counting from 1 by default, but you can assign a different starting number using =.
Give an example of how we would use AUTO_INCREMENT in context?
CREATE TABLE Persons (Personid int NOT NULL AUTO_INCREMENT, LastName varchar(255) NOT NULL, FirstName varchar(255), PRIMARY KEY (Personid));