Intro
In this unit we are going to discuss these topics the way I understood them. You can take them as notes for a revision as I am going to use very less words but simple language.
Macro Topics
- Relational Databases & SQL Basics
- Data Types and Table Structures
Micro Topics
- What is a database, table, row, column
- RDBMS concepts (MySQL, PostgreSQL, SQL Server)
- Primary key vs foreign key
- NULL values and NULL handling
- Data types:
- Numeric (INT, FLOAT, DECIMAL)
- String (VARCHAR, CHAR, TEXT)
- Date & Time (DATE, TIME, TIMESTAMP)
- Boolean
- Schema and database structure
- Case sensitivity in SQL
- SQL keywords vs identifiers
- SQL execution order
Let us start from the basics, we will move one step at a time.
What is SQL: Standard Query Language (SQL) is the standard language used to interact with relational databases.
Database: A database is an organised collection of data.
Relational Database: A relational database is an organised collection of data in tables. These tables are made up of rows and columns
Rows: Rows represent individual records in a table.
Columns: Columns represent the attributes or fields that describe each record.
 |
| This is a table in a database with 7 rows (horizontal) and 4 columns (vertical). |
Schema: The schema defines the overall structure of the database, including its tables, columns, data types, relationships and constraints.
So in simple terms SQL is a tool to manage data which is in the form of tables in a database.
What are MySQL, PostgreSQL, SQLite, etc?
SQL is a language to interact with a relational DBMS, and there are different database management systems available in the market that are used to manage data. So MySQL, PostgreSQL, SQLite etc are different database management systems. The key is that every DBMS uses a slightly different SQL syntax. For instance Microsoft Excel and Google Sheets, in each you can work with data but some functions are slightly different.
Primary Key and Foreign Key
NULL values and NULL handling
What I understood?
What is a data type?
A data type in SQL means the kind of data stored in a database. There are different kinds of data that we store in a database for example integers, numbers, text, date etc. So each kind of data is a data type.
In another way a column with an int data type can only accept integer numbers.
Common Data Types in SQL
int/integer : integer numbers
serial: autoincrementing integer
char/varchar/text : all store character text but in a slightly different way
date : stores date in yyyy-mm-dd format
time : stores time in hh-mm-ss format
timestamp: stores data and time
boolean/bool : logical bollean (true/false)