Read on Twitter
Most of the systems you will build in your life will need some form of persistence.
That is why when preparing for an interview you should be ready to talk about Databases
This is the eighth thread of the series: Interview Preparation!
THREAD
That is why when preparing for an interview you should be ready to talk about Databases
This is the eighth thread of the series: Interview Preparation!
THREAD
First, let's get some definitions down.
A Database is an organized collection of data.
A Database Management System (DBMS) is a software for users to define, create, maintain & control access to the DB.
You will also need a language to program and design your DB objects.
A Database is an organized collection of data.
A Database Management System (DBMS) is a software for users to define, create, maintain & control access to the DB.
You will also need a language to program and design your DB objects.
There are very different types of Databases & also a lot of possible classifications.
I would say the most useful one is:
- SQL
- NoSQL
We are going to focus only on SQL Databases in this thread.
You can read MANY more classifications here:
https://en.wikipedia.org/wiki/Database#Classification
I would say the most useful one is:
- SQL
- NoSQL
We are going to focus only on SQL Databases in this thread.
You can read MANY more classifications here:
https://en.wikipedia.org/wiki/Database#Classification
SQL Stands for Structured Query Language, it is a language to interact with our DB, but it is also used to refer to the style of relational structured databases.
For SQL type databases some enterprise DB systems are:
- MS SQL Server
- MySql
- Oracle
- PostgreSql
For SQL type databases some enterprise DB systems are:
- MS SQL Server
- MySql
- Oracle
- PostgreSql
Concepts you need to learn about SQL:
- DDL & DML
- Tables
- Constraints
- Indexes
- Selects
- Joins
- Group by
- Views
- Stored Procedures & Functions
- Temporal Tables & CTEs
- Execution Plans
- DDL & DML
- Tables
- Constraints
- Indexes
- Selects
- Joins
- Group by
- Views
- Stored Procedures & Functions
- Temporal Tables & CTEs
- Execution Plans
Let's tackle and define some of those concepts, you can dig deeper into all of them later!
DDL: Data Definition Language. It is used to define structures: schema, database, tables, constraints, etc.
DML: Data Manipulation Language: Used for inserts, updates, deletes, etc.
DDL: Data Definition Language. It is used to define structures: schema, database, tables, constraints, etc.
DML: Data Manipulation Language: Used for inserts, updates, deletes, etc.
Tables in SQL are just like you imagine a table. Columns and rows.
They also have a structure, for each column you have to define the type of the data that will go there.
This allows us to validate and check consistency in our data.
They also have a structure, for each column you have to define the type of the data that will go there.
This allows us to validate and check consistency in our data.
Constraints validate the data that you will input to a certain column besides type.
Examples:
- not null
- unique
- primary key (one per table, a unique identifier for each row, research this)
- foreign key (link to a primary key of another table, research this)
- default
Examples:
- not null
- unique
- primary key (one per table, a unique identifier for each row, research this)
- foreign key (link to a primary key of another table, research this)
- default
Indexes
They can be clustered or non clustered (interview question).
The clustered index defines how the info will be sorted for storage. Therefore you can only have 1
You can have many non clustered. Separate structure from the data rows for indexing and searching faster
They can be clustered or non clustered (interview question).
The clustered index defines how the info will be sorted for storage. Therefore you can only have 1
You can have many non clustered. Separate structure from the data rows for indexing and searching faster
There is too much to cover on selects so I will leave this one for you to research with joins and all the good stuff
Basically, it is the way we write queries to retrieve data from the DB
Format
select columnsNames
from table
join table on condition
where conditionsToFilter
Basically, it is the way we write queries to retrieve data from the DB
Format
select columnsNames
from table
join table on condition
where conditionsToFilter
A SQL View is like a virtual table.
They have columns and rows and allow you some operations with some limitations (you can update and insert through them, kinda).
They are great to encapsulate queries that have a meaning for your system and you want others to reuse.
They have columns and rows and allow you some operations with some limitations (you can update and insert through them, kinda).
They are great to encapsulate queries that have a meaning for your system and you want others to reuse.
Stored procedures
SPs are like methods on OOP, you have a block of code encapsulated to be used from many places. SPs allow you to use variables, do updates, inserts, deletes, logging, use cursors, etc.
They have parameters and can return values too.
SPs are like methods on OOP, you have a block of code encapsulated to be used from many places. SPs allow you to use variables, do updates, inserts, deletes, logging, use cursors, etc.
They have parameters and can return values too.
To close things up, you should also research about ACID, performance, and tooling. I use SQL Server from Microsoft but feel free to pick any tech.
Remember NoSQL is a whole different world, but complementary. Both have their specific use cases.
Remember NoSQL is a whole different world, but complementary. Both have their specific use cases.
Thanks for reading and I will see you tomorrow for some CI-CD thread!
