SQL 101: Introduction to Structured Query Language

Introduction to DBMS
Database Management Systems are software's that host Databases.

Introduction to Databases
A database is data/information collected and organized for fast retrieval at any given time by a computer.
There are two types of Databases that is;
1.Relational Databases(Stored in tables rows and columns).
2.Non-relational databases(stored in graphical form).

Introduction to SQL
SQL(Structured Query Language) is a standard language used to create, store and manipulate data in relational databases.

Data Types
Data in SQL is stored in different modes(types) that is;

1. String/text/varchar - to store names and written inside quotes ("Hello").
2. Int - whole numbers that is integers(3,4,56)
3. Float - numbers with decimals(1.24, 7.8)
4. Bool - Boolean(True/False).

Relationships
In SQL, Columns are fields and the Rows are entries.
There are three main relationships exhibited by the tables;

1. One to One - One field is directly linked to another and the reverse.
2. One to Many - One field is directly related to many fields in the table and many fields are directly linked to only one field.
3. Many to Many - Many fields in the table are directly linked to many other fields in the table and vice versa.

Composition
Fields in SQL exhibit a couple of functionalities that uniquely identify them that is;

1. Primary Key-unique identity in a table.
2. Foreign Key-a primary key of another table.
3. Composite Key- combination of two keys to form one field in a table.

Sample Codes
All code lines in SQL end in a semi-colon(;)
Creating a database: create database database_name;
comment in sql: -- this is a comment
Show databases: show databases;
Switch/use a different database: use database_name;
Creating tables: create table table_name(
table_id INT
name TEXT);
Inserting data into a table: Insert into table_name(table_id, name)
values(1, 'one'),
(2, two)
(3, three);

Checking table information
select * from table_name; - all entries from a table
select name from table_name; - all column entries from a table

Data manipulation codes
-- To get a glimpse of the table contents.
select * from table_name limit 5;
-- To return distinct - different, unique(avoid duplicates)
select distinct(name) from table_name;
-- Using group by
select count(*) from table_name
group by name;
-- use of where - filter data
select * from table_name
where name = 'one';

Aggregate functions

1. Sum: sum(column_name)
2. Average: avg(column_name)
3. Count: count(column_name)

Select sum(column_name) as total from table_name;
-- as brings the return with a customized field/column name

CTE - Common Table Expression
-- stores the results of a certain query or statement
with cte_name as(
select * from table_name
where column_name = 'name');