Outline the differences between data language types within SQL

• Data Definition Language (DDL)
  • is used to create and modify the structure of the database
  • it is used to create the schema, tables, and constraints
  • common DDL commands:
    • CREATE
      • used to create a database or table
    • ALTER
      • used to change the structure of a table or database
    • DROP
      • used to delete a table or database structure
    • TRUNCATE
      • removes all rows from a table but does not remove the table itself
    • RENAME
      • used to rename a table
    • COMMENT
      • used to add comments when creating structures
• Data Manipulation Language (DML)
  • is used to access and manipulate the data inside the database
  • this includes querying, inserting, updating, and deleting records
  • common DML commands:
    • SELECT
    • INSERT
    • UPDATE
    • DELETE
• Data Control Language (DCL)
  • is used to control access to the database
  • this is related to security and permissions
  • common DCL commands:
    • GRANT
      • gives a user privileges
    • REVOKE
      • removes privileges from a user
• Transaction Control Language (TCL)
  • is used to manage transactions
  • a transaction is a group of operations that must either all succeed or all fail
  • common TCL commands:
    • START TRANSACTION
    • COMMIT
      • permanently saves the transaction
    • ROLLBACK
      • reverses all changes made since the transaction started

SQL examples for database/table development

• Create database and table

CREATE DATABASE demoDB;

CREATE TABLE table_name (
    Attribute1 datatype NOT NULL PRIMARY KEY,
    Attribute2 datatype,
    Attribute3 datatype
);

CREATE TABLE table_name (
    Attribute1 datatype NOT NULL PRIMARY KEY,
    Attribute2 datatype,
    Attribute3 datatype,
    Attribute4 datatype,
    FOREIGN KEY (Attribute4) REFERENCES other_table(Attribute4)
);

• Drop table

DROP TABLE table_name;

• Alter table

ALTER TABLE table_name
ADD COLUMN attribute datatype AFTER existing_attribute;

ALTER TABLE table_name
DROP COLUMN attribute;

ALTER TABLE students
MODIFY COLUMN age VARCHAR(50);

ALTER TABLE students
CHANGE COLUMN old_name new_name VARCHAR(50);

ALTER TABLE students RENAME TO pupils;

ALTER TABLE students
ADD PRIMARY KEY (id);

ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id);

ALTER TABLE students
ADD CONSTRAINT pk_student_id PRIMARY KEY (student_id);

ALTER TABLE students
ADD CONSTRAINT unique_email UNIQUE (email);

ALTER TABLE students
ADD CONSTRAINT check_age CHECK (age >= 0 AND age <= 120);

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

ALTER TABLE students
MODIFY COLUMN name VARCHAR(50) NOT NULL;

ALTER TABLE students
ALTER COLUMN status SET DEFAULT 'active';

• Modifying data in a table

INSERT INTO table_name (Attribute1, Attribute2, Attribute3)
VALUES (value1, value2, value3);

UPDATE table_name
SET Attribute1 = value1, Attribute2 = value2
WHERE condition;

DELETE FROM table_name
WHERE condition;