Section 23: An Introduction To SQL - Can You Relate With It?

The goals

💪🏻How SQL Database Are Designed & Work

✌🏻Defining Data Schemas & Tables

👍🏻Querying Data (CRUD Operations)

 

What is SQL?

SQL Databases

SQL stands for Structured Query Language and it therefore is not a database itself but only a query language you can use to interact with a specific type of database.

SQL allows you to store, update, delete and of course retrieve data from relational database management systems (RDBMS).

Such databases have two key characteristics:

• Data is stored in database tables by following a strict data schema (= structure)
• Data is distributed across multiple tables which are connected via relations

Strict Schema

Data is stored as records in tables and each table has a clearly defined structure - a set of fields which defines which data may go into the table and which data may not.

The structure is defined regarding the names of the fields as well as the datatypes.

You can't add records which don't adhere to this schema. Got more fields? Sorry, you have to pick another table. Missing some field data? Not your table then!

 

 

What's our goal through this section?

Here we will study with MySQL

 

 

Installing MySQL and MySQL Workbench

 

Database System vs Database

 

MySQL syntax

In SQL database, schema and structure are really important. And we usually set our table name plural since we will save not only one but lots of data.

PK: Primary Key, the value is required mandatorily and the value is unique.

NN: Not Null, the value is required and it shouldn't be null

UQ: Unique, the value should be unique but if we already set it as primary key then we don't need this option.

Binary: Binary

UN: Unsigned

ZF: Zero Fill

AI: Auto Increment, the value is automatically increased.

G: Generated Column

 

 

Try to insert data with writing our own SQL

Here, I had this error cause I didn't set schema first..

So, I double clicked schema I wanna use (here it is 'restaurant_finder') and clicked 'creare new SQL tab' and wrote again.

We can also specify the data using condition.

We don't have to specify the column for 'Count' because anyway it will count the rows of data.

But, if we use 'SUM' then we need to specify the column has the number value we want to calculate.

 

We can check what we made here

 

The data has been deleted!

 

Designing more complicated Database Tables & Schema / Structure

 

Modify the table

We can also drop the table and remake it.

Here the tricky part is that we need to deal with the data already exist.

 

Since we are connecting 'restaurants' table and 'address' table, we name a column can reveal it by the convention.

 

 

And the one thing we should check is 'Foreign Key' which is a field (column) that references a field in another table. Like address_id in our case here.

 

Here is the new feature 'CURRENT_TIMESTAMP' which is the exact time when someone makes some records on our database. And it will be set automatically.

 

 

Now let's connect each table

We can also choose the data we wanna bring.

 

 

 

 

Sources

https://academind.com/tutorials/sql-vs-nosql

SQL vs NoSQL