2.

SQL DDL Commands

Contents
1. Relational Database Model
2. What is SQL?
3. Types of SQL commands
4. DDL commands for Databases
5. DDL commands for Tables
6. Data Integrity
7. Constraints in MySQL
8. ALTER TABLE command

Relational Database Model

Database Server --> DBMS --> Databases --> Tables --> Rows & Colomns

What is SQL?
SQL (Structured Query Language) is a programming language used for manage 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.

Types of SQL commands

DDL commands for Databases
1. CREATE
2. DROP
1. CREATE
CREATE DATABASE campusx

CREATE DATABASE IF NOT EXISTS campusx

2. DROP
DROP DATABASE campusx

DROP DATABASE IF EXISTS campusx

DDL commands for Tables

1. CREATE
2. TRUNCATE
3. DROP
1. CREATE
CREATE TABLE users(
col_name data_type,
col_name data_type,
col_name data_type
)

CREATE TABLE users(

user_id INTEGER,
name VARCHAR(255),
email VARCHAR(255),
password VARCHAR(255)
)

2. TRUNCATE
TRUNCATE TABLE users

3. DROP
DROP TABLE IF EXISTS users

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 of 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.
1. NOT NULL
2. UNIQUE(combo) -> Another way of creating constraint
3. PRIMARY KEY
4. AUTO INCREMENT
5. CHECK
6. DEFAULT
7. FOREIGN KEY
Referential Actions
1. RESTRICT
2. CASCADE
3. SET NULL
4. SET DEFAULT
1. NOT NULL
CREATE TABLE users(
user_id INTEGER NOT NULL,
name VARCHAR(255) NOT NULL,
email VARCHAR(255),
password VARCHAR(255)
)

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

Another Way
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 (name,email)
)

Benifits of using Constraint:

1. The combination of name and email is unique it means no two rows have same name and email, this cant acheive without using constraint.
2. We can name our constraint and delete the constraint if we dont want to apply rule on table.
3. PRIMARY KEY
CREATE TABLE users(
user_id INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL,

CONSTRAINT users_email_unique UNIQUE (name,email)

)

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 (name,email)

CONSTRAINT users_pk PRIMARY KEY (user_id,name)
)

4. AUTO INCREMENT
CREATE TABLE users(
user_id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
 email VARCHAR(255) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL
)

5. CHECK
CREATE TABLE students(
student_id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
age INTEGER CHECK (age>6 AND age<25)

CREATE TABLE students(

student_id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
age INTEGER,

CONSTRAINT students_age_check CHECK (age>6 AND age<25)

)

6. DEFAULT
CREATE TABLE ticket(
ticket_id INTEGER PRIMARY KEY,
name VARCHAR(255) NOT NULL,
travel_date DATE DEFAULT CURRENT_DATE
)

CREATE TABLE ticket(

ticket_id INTEGER PRIMARY KEY,
name VARCHAR(255) NOT NULL,
 travel_date DATETIME DEFAULT CURRENT_TIMESTAMP
)

7. FOREIGN KEY
CREATE TABLE customers(
cid INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE
)

CREATE TABLE orders(

order_id INTEGER PRIMARY KEY AUTO_INCREMENT,
cid INTEGER NOT NULL,
order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

CONSTRAINT orders_fK FOREIGN KEY (cid) REFERENCES customers(cid)

)

DROP TABLE customers;

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails

Referential Actions
RESTRICT
Prevents deletion or update of a parent row if there are any child rows referencing it. This is the default behavior if no ON DELETE or ON UPDATE clause
is specified.
CASCADE
When a parent row is deleted or updated, the corresponding changes are propagated to the child rows. If a row is deleted from the parent table, all
related rows in the child table will also be deleted. If an update is performed on the parent row, the corresponding changes will be applied to the child
rows as well.
CREATE TABLE orders(
order_id INTEGER PRIMARY KEY,
cid INTEGER NOT NULL,
order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

CONSTRAINT orders_fK FOREIGN KEY (cid) REFERENCES customers(cid)

ON DELETE CASCADE
ON UPDATE CASCADE
)

SET NULL
If a parent row is deleted, the foreign key columns in the child rows that reference the deleted row are set to NULL. This assumes that the foreign key
column is nullable (allows NULL values). If the foreign key column is NOT NULL, SET NULL cannot be used.
CREATE TABLE orders(
order_id INTEGER PRIMARY KEY,
cid INTEGER NOT NULL,
order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

CONSTRAINT orders_fK FOREIGN KEY (cid) REFERENCES customers(cid)

ON DELETE SET NULL
ON UPDATE SET NULL
)

Error Code: 1005. Can't create table campusx . orders (errno: 150 "Foreign key constraint is incorrectly formed")
SET DEFAULT
If a parent row is deleted, the foreign key columns in the child rows that reference the deleted row are set to the default value defined for that column.
If no default value is defined, it will generally be treated as NULL, but this behavior can be implementation-dependent.

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 include
1. Add columns
2. Delete columns
3. Modify columns
4. Editing and Deleting Constraint
Add Column
ALTER TABLE customers ADD COLUMN password VARCHAR(255) NOT NULL

ALTER TABLE customers ADD COLUMN surname VARCHAR(255) NOT NULL AFTER name

ALTER TABLE customers

ADD COLUMN pan_number VARCHAR(255) AFTER surname,
ADD COLUMN joining_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP

Delete Column
ALTER TABLE customers DROP COLUMN pan_number

ALTER TABLE customers

DROP COLUMN password,
DROP COLUMN joining_date
Modify columns
ALTER TABLE customers MODIFY COLUMN surname INTEGER

ALTER TABLE customers ADD COLUMN age INTEGER NOT NULL

Editing and Deleting Constraint

ALTER TABLE customers ADD CONSTRAINT customer_age_check CHECK (age > 13);

We cannot edit constraint in mysql, so first we have to drop the constraint then add it.
ALTER TABLE customers DROP CONSTRAINT customer_age_check

ALTER TABLE customers ADD CONSTRAINT customer_age_check CHECK (age > 6);