SQL Flashcards
What is SQL?
Structured Query Language - a way to access and process data stored in a database.
What is a relational database?
A set of data stored in a computer, organised into tables and structured in a way that allows us to access data in relation to other data.
What is database normalisation?
Database normalisation means to organise a database into tables in a way that means a table is created about one specific topic with only supporting topics included. This allows us to minimise duplicate data and avoid data modification issues.
Wildcard characters
LIKE
%
_
LIKE - search for a specific pattern
% zero or more characters
_ one single character
Types of JOIN in SQL
INNER JOIN - joins columns in first table to second table, returns rows where there is a match in both tables, cannot deal with NULL
LEFT JOIN - all rows from left side returned with matching rows in right table, or NULL
RIGHT JOIN - all rows from right side returned with matching rows in left table, or NULL
FULL OUTER JOIN - all rows from both sides returned with matching rows, or NULL
SELF JOIN - allows you to join a table to itself
CROSS JOIN - Cartesian join, generates a paired combination of each row in first and second table
What is a transaction in SQL?
A unit of work or sequence of operations that is performed against a database.
It can either be COMMITTED (applied to the database) or ROLLED BACK (undone)
What is indexing in SQL?
Indexing allows us to quickly find rows with specific column values. This can speed up queries.
Clustered index - one per table, primary key by default, determines the order in which data is entered into the table.
Non clustered - can have multiple per table, a data structure which is stored separately from the table.
What is table locking in SQL?
Allows us to lock an object/table in a database to perform an update, maintenance etc.
READ LOCK - can be acquired for multiple sessions at the same time, other sessions cannot write data to the table until the READ lock is released.
WRITE LOCK - an exclusive lock with gives us full write access, but prevents others from doing anything, even seeing the data. Only the session that holds the lock can read and write data to the table until the WRITE lock is released.
What is a view in SQL?
A virtual table based on the result set of an SQL statement. The tables referenced are known as base tables. This allows us to summarise data from various tables.
Created using WITH CHECK - prevents modifying a row in a way that it would no longer be part of the view result.
What is a stored procedure in SQL?
A stored procedure is a set of SQL statements that you can save, so the code can be reused over and over again.
It can return many values, input and output parameters, can call functions, can read and modify data.
What is a stored function in SQL?
A stored function is a set of SQL statements that perform some operation and return a single value. Procedures can call functions.
What is a trigger in SQL?
A block of code that is executed automatically when operations like INSERT, UPDATE or DELETE are executed. This can help enforce rules for data consistency.
