Databases and SQL Overview

Study Notes

Databases and DBMS

A database is a collection of interrelated data.
Database Management System (DBMS) is software to create, manage, and organize databases.
Relational Database Management System (RDBMS) is a type of DBMS that organizes data into tables (relations), consisting of rows (records) and columns (attributes).
Examples of RDBMS include MySQL, PostgreSQL, and Oracle.

Structured Query Language (SQL)

SQL, or Structured Query Language, is used to store, manipulate, and retrieve data from RDBMS.
SQL is essential for performing CRUD operations:
- CREATE: Creating databases, tables, and inserting data.
- READ: Retrieving data from the database.
- UPDATE: Modifying existing data.
- DELETE: Removing data or whole tables from the database.
SQL keywords are not case sensitive (e.g., SELECT and select are equivalent).

SQL vs MySQL

SQL is the language for interacting with relational databases, while MySQL is an RDBMS that implements SQL.

SQL Data Types

Data types define what kind of data can be stored in a column or variable.
Commonly used data types in MySQL include:
- CHAR: Fixed-length string (0-255).
- VARCHAR: Variable-length string (0-255).
- BLOB: Binary large object storage (0-65535).
- INT: Integer (-2,147,483,648 to 2,147,483,647).
- FLOAT: Decimal number with up to 23 digits of precision.
- BOOLEAN: Represents true or false values (0 or 1).
- DATE: Date format YYYY-MM-DD.
Consider using UNSIGNED with integer types for non-negative values.

Types of SQL Commands

DQL (Data Query Language): Retrieves data from databases (e.g., SELECT).
DDL (Data Definition Language): Manages database structure (e.g., CREATE, DROP, ALTER).
DML (Data Manipulation Language): Modifies data (e.g., INSERT, UPDATE, DELETE).
DCL (Data Control Language): Grants or revokes permissions (e.g., GRANT, REVOKE).
TCL (Transaction Control Language): Manages database transactions (e.g., COMMIT, ROLLBACK).

Data Definition Language (DDL)

DDL commands manage the structure of databases and their objects.
Key DDL Commands:
- CREATE TABLE: Creates a new table specifying name, columns, data types, and constraints (e.g., CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(50), salary DECIMAL(10, 2));).
- ALTER TABLE: Modifies an existing table's structure (e.g., ALTER TABLE employees ADD COLUMN email VARCHAR(100);).
- DROP TABLE: Deletes an existing table along with its data (e.g., DROP TABLE employees;).
- CREATE INDEX: Enhances query performance by creating an index (e.g., CREATE INDEX idx_employee_name ON employees (name);).

Description

Explore the concepts of databases and Database Management Systems (DBMS) with a focus on Structured Query Language (SQL). This quiz will cover the key principles of relational databases, critical SQL operations, and compare SQL with MySQL. Test your knowledge on these foundational topics in data management.