Introduction to SQL (Chapter 2)

5 min readAug 2, 2023

In this blog, we’ll explore the power of Structured Query Language (SQL) and how it empowers you to efficiently manage, query, and manipulate data in relational databases. From fundamentals to advanced techniques, we’ll cover everything you need to become an SQL expert. Let’s embark on this journey together and unlock the full potential of SQL!

What is SQL?

SQL (Structured Query Language) is a programming language used for managing and manipulating data in relational databases. It allows you to insert, update, retrieve, and delete data in a database. It is widely used for data management in many applications, websites, and businesses. In simple terms, SQL is used to communicate with and control databases.

Type of SQL Commands

SQL commands can be categorized into several types based on their functionalities. Here are some common types of SQL commands:

DDL Commands

DDL stands for Data Definition Language, and it comprises SQL commands used to define and manage the structure of the database. DDL commands allow users to create, modify, and delete database objects like tables, indexes, views, and more.

Commands for Database

CREATE: This command is used to create new database objects such as databases, tables, views, indexes, or schemas.

CREATE DATABASE database_name;

DROP: This command is used to delete a database or database object.

DROP DATABASE database_name;

DDL Commands for Table

CREATE

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    department VARCHAR(50)
);

TRUNCATE: This command is used to remove all records from a table without logging individual row deletions.

TRUNCATE TABLE employees;

DROP

DROP TABLE employees;

Data Integrity

Data integrity in databases refers to the accuracy, completeness, and consistency of the data stored in a database. It is a measure of the reliability and trustworthiness of the data and ensures that the data in a database is protected from errors, corruption, or unauthorized changes.

There are various methods used to ensure data integrity, including:

Constraints: Constraints in databases are rules or conditions that must be met for data to be inserted, updated, or deleted in a database table. They are used to enforce the integrity of the data stored in a database and to prevent data from becoming inconsistent or corrupted.
Transactions: a sequence of database operations that are treated as a single unit work.
Normalization: a design technique that minimizes data redundancy and ensures data consistency by organizing data into separate tables.

Constraints in MySQL

Constraints in databases are rules or conditions that must be met for data to be inserted, updated, or deleted in a database table. They are used to enforce the integrity of the data stored in a database and to prevent data from becoming inconsistent or corrupted.

NOTNULL: The NOT NULL constraint ensures that the values in the specified column(s) cannot be NULL.

CREATE TABLE users(
    user_id INTEGER NOT NULL,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    password VARCHAR(255)
    )

UNIQUE(combo): The UNIQUE constraint ensures that the values in the specified column(s) are unique across the table.

CREATE TABLE users(
 user_id INTEGER NOT NULL,
 name VARCHAR(255) NOT NULL,
 email VARCHAR(255) NOT NULL UNIQUE,
 password VARCHAR(255)
 )

Another way of creating a constraint

CREATE TABLE users(
 user_id INTEGER NOT NULL,
 name VARCHAR(255) NOT NULL,
 email VARCHAR(255) NOT NULL,
 password VARCHAR(255) NOT NULL,

 CONSTRAINT users_email_unique UNIQUE (email)

 )

PRIMARY KEY: A primary key is a unique identifier for each record in a table. It ensures that each row has a unique value in the specified column(s). A table can have only one primary key, and the primary key column(s) cannot contain NULL values.

CREATE TABLE users(
 user_id INTEGER NOT NULL,
 name VARCHAR(255) NOT NULL,
 email VARCHAR(255) NOT NULL,
 password VARCHAR(255) NOT NULL,
 
 CONSTRAINT users_email_unique UNIQUE (email)
 CONSTRAINT users_pk PRIMARY KEY (user_id) 
 )

AUTO INCREMENT: Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.

CREATE TABLE Persons (
    Personid int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (Personid)
);

CHECK: The CHECK constraint allows you to define custom conditions that data in the specified column(s) must meet.

create table students(
 stu_id INTEGER PRIMARY KEY AUTO_INCREMENT,
 name VARCHAR(50) NOT NULL,
 age INTEGER CHECK (age>6 AND age<25)
 )

DEFAULT: In MySQL, DEFAULT is a constraint used in the context of column definitions. When you define a column with a DEFAULT value, it specifies a default value that will be assigned to that column if an explicit value is not provided during an INSERT operation.

CREATE TABLE tickets(
 ticket_id INTEGER PRIMARY KEY,
 name VARCHAR(255) NOT NULL,
 travel_date DATETIME DEFAULT CURRENT_DATE
 )

FOREIGN KEY: A foreign key is a column or a set of columns in a relational database table that establishes a link or relationship with the primary key of another table.

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);
-------------------------------------------
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department_id INT,
    hire_date DATE,
    salary DECIMAL(10, 2),
    CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments (department_id)
);

Referential Actions

Referential actions in MySQL, also known as cascading referential actions, are actions that can be defined on foreign keys to automatically perform certain actions on related rows in the referencing table when corresponding rows in the referenced table are updated or deleted.

CASCADE: When a row in the referenced table is updated or deleted, the corresponding rows in the referencing table are also updated or deleted, respectively.
RESTRICT: If there are any related rows in the referencing table when an update or delete operation is attempted on the referenced table, the operation is restricted, and an error is thrown.
SET NULL: When a row in the referenced table is updated or deleted, the corresponding foreign key values in the referencing table are set to NULL.
NO ACTION: This is similar to RESTRICT, but it allows the foreign key to be defined without explicit action.

CREATE TABLE table_name (
    column_name data_type,
    ...
    FOREIGN KEY (foreign_key_column) REFERENCES referenced_table (referenced_column) 
    ON UPDATE action 
    ON DELETE action
);

ALTER TABLE command

The “ALTER TABLE” statement in SQL is used to modify the structure of an
existing table. Some of the things that can be done using the ALTER TABLE
statement includes

Add columns

ALTER TABLE table_name
ADD column_name data_type;

Delete columns

ALTER TABLE table_name
DROP COLUMN column_name;

Modify columns

ALTER TABLE table_name
MODIFY column_name new_data_type;

Editing and Deleting Constraints

ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_definition;

Delete

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;