Introduction
Hi everyone! 🤗
I’m back with a new set of SQL questions. They aren't too complicated (I think), but I've often struggled to explain them clearly 🥹. I could give examples from my own use, but I couldn't always find the right words to describe them.
I'll keep this intro short, so let's dive into the questions! 🤓
## Questions
1. What is a key in SQL, and what types do you know?
2. What is a subquery?
3. What is a transaction in SQL??
Question 1: What is a key in SQL, and what types do you know?
The short answer is...
A key is a set of attributes that we use to identify a specific row in a table or to create relationships between tables.
There are different types of keys:
- Primary Key
- Candidate Key
- Super Key
- Alternate Key
- Unique Key
- Composite Key
- Foreign Key
...
Primary Key
A primary key is the key to uniquely identifying each row (record) in a table.
- A table can have only one primary key.
- Can be made up of one or more columns.
- Cannot be NULL.
- Cannot be duplicated.
Table students
In this example, StudentID
is the primary key. Each student has only one StudentID
.
...
Candidate key
A candidate key is a set of columns that can uniquely identify each row in a table. A table can have multiple candidate keys, but only one will be chosen as the primary key. A candidate key must be minimal and cannot include extra columns.
Table vehicles
VIN
and LicensePlate
are the candidate keys for this table because both can uniquely identify a vehicle.
...
Super Key
A super key is a set of columns that can uniquely identify records in a table.
All candidate keys are super keys, but not all super keys are candidate keys because super keys may include columns that are not necessary for uniqueness.
-
{EmployeeID, Email}
and{EmployeeID, Email, PhoneNumber}
are Super Keys because the combination of those columns can uniquely identify each record.
...
Alternate Key
When a primary key is selected, any remaining key becomes an alternate key.
Primary Key => AuthorID
Alternate Key => SSN
(Since it can also uniquely identify a record but is not chosen as the Primary Key).
...
Unique Key
It also uniquely identifies records BUT allows NULL values.
Primary Key => EmployeeID
(Unique for each employee).
Unique Key => Email
(Each email is unique).
...
Composite Key
These keys are made up of two or more columns that uniquely identify a record.
The composite key is {StudentID, CourseID}
because a student can enroll in multiple courses, but this combination is unique.
...
Foreign Key
A foreign key is used to set relationships between tables. It ensures referential integrity between tables, helping to avoid duplicate data.
For this example, I need two tables:
Table orders
Table customers
The primary keys are OrderID
for the Orders
table and CustomerID
for the Customers
table.
The foreign key in Orders
: CustomerID
(References CustomerID
in Customers
).
Question 2: What is a subquery?
Answer:
A subquery is a query that appears inside another query statement. It’s also called an
inner query
ornested query
. It’s used to perform a query that returns data to be used by the“outer”
query.
An example:
If I have two tables...
Table Employees
And the table Departments
If I want to find the names of employees in the HR department. The query I should write looks like this:
SELECT Name
FROM Employees
WHERE DepartmentID = (
SELECT DepartmentID
FROM Departments
WHERE DepartmentName = 'HR'
);
Where the inner query
is
SELECT DepartmentID
FROM Departments
WHERE DepartmentName = 'HR'
And the outer query
is
SELECT Name
FROM Employees
WHERE DepartmentID = 10;
Question 3: What is a transaction in SQL?
Answer:
A transaction is a sequence of one or more SQL operations that run as a single execution.
It’s used to maintain the integrity of the database and to ensure that changes aren't applied until all queries are fully completed without errors. Otherwise, the transaction will rollback any changes.
What is ACID
?
ACID represents the key properties of transactions:
A
forAtomicity
: The transaction should be completed successfully or unsuccessfully, but not by parts. If any part fails, the transaction must rollback.C
forConsistency
: The transaction ensures data integrity. If any query in the transaction tries to violate a constraint, the entire transaction will fail, preventing changes in the database.I
forIsolation
: Ensures that transactions do not interfere with each other, even when executed simultaneously.D
forDurability
: Ensures the transaction is committed. Its change is permanently stored even in the event of a system failure.
Transactions are widely used in Fintech. For example, when working with clients' bank accounts, we use transactions to ensure the integrity of the database.
Example:
Suppose we want to transfer 100 currency units from Account 1 to Account 2. This requires two operations:
1 - Deduct 100 from Account 1's balance.
2 - Add 100 to Account 2's balance.
Here’s how our transaction query would look:
BEGIN;
UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountID = 1;
UPDATE Accounts
SET Balance = Balance + 100
WHERE AccountID = 2;
COMMIT;
-
BEGIN
starts the transaction -
UPDATES
modifies the balances of the accounts. -
COMMIT
finalizes the transaction and saves the changes, making them permanent in the database.
If any part of this process fails, we can use:
-
ROLLBACK
to undo all changes, ensuring that the database remains consistent.
Farewell & Thoughts
That's all for this post!
It’s a bit short, but I included as many examples as possible to make things easy to understand. 🫶🏻
As I always say, if you have other questions you’d like me to cover, just let me know!
See you in the next post, and have a great week!
Top comments (0)