SQL Language

SQL Data Definition Language (DDL)

DDL is used to define and manage schema objects such as tables, views, and indexes. It includes commands like CREATE, ALTER, DROP, TRUNCATE, etc.

---

Creating Tables

The CREATE TABLE statement is used to define a new table with its columns and datatypes.

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  ...
);

Example:

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

CREATE TABLE Orders (
  OrderID int NOT NULL,
  OrderNumber int NOT NULL,
  PersonID int,
  PRIMARY KEY (OrderID),
  FOREIGN KEY (PersonID) REFERENCES Persons(ID)
);

Create a Table from an Existing Table:

CREATE TABLE new_table_name AS
SELECT column1, column2, ...
FROM existing_table_name
WHERE ...;

Key Points:

• PRIMARY KEY uniquely identifies a row.

• FOREIGN KEY enforces referential integrity between tables.

• Use NOT NULL to disallow null values in a column.

---

Altering Tables

The ALTER TABLE statement is used to modify an existing table’s structure.

ALTER TABLE table_name 
ADD column_name datatype;

ALTER TABLE table_name 
DROP COLUMN column_name;

ALTER TABLE table_name 
MODIFY COLUMN column_name datatype;

Add Constraints:

ALTER TABLE Persons 
ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

Add Foreign Key Later:

ALTER TABLE Orders 
ADD FOREIGN KEY (PersonID) REFERENCES Persons(ID);

---

Dropping Schema Objects

The DROP command is used to delete database objects permanently.

DROP TABLE table_name;
DROP SCHEMA schema_name CASCADE;
DROP SCHEMA schema_name RESTRICT;

• CASCADE: Deletes dependent objects as well.

• RESTRICT: Fails if dependencies exist.

---

Truncating and Renaming

Truncate a Table (Delete All Rows):

TRUNCATE TABLE table_name;

Rename a Database:

RENAME DATABASE old_database_name TO new_database_name;

---

Other Alter Table Examples

ALTER TABLE COMPANY.EMPLOYEE 
ADD COLUMN Job VARCHAR(12);

ALTER TABLE COMPANY.EMPLOYEE 
DROP COLUMN Address CASCADE;

ALTER TABLE COMPANY.DEPARTMENT 
ALTER COLUMN Mgr_ssn DROP DEFAULT;

ALTER TABLE COMPANY.DEPARTMENT 
ALTER COLUMN Mgr_ssn SET DEFAULT '333445555';

ALTER TABLE COMPANY.EMPLOYEE 
DROP CONSTRAINT EMPSUPERFK CASCADE;

---

Viewing Table Structure

Use DESCRIBE to see the structure of a table:

DESCRIBE table_name;
DESC table_name;

SQL Data Manipulation Language (DML)

DML is used to insert, update, and delete data in tables.

---

Inserting Data

With Column Names:

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

Without Column Names (All Columns):

INSERT INTO table_name
VALUES (value1, value2, value3);

Examples:

INSERT INTO Persons (ID, Lastname, Firstname, Age)
VALUES (1001, 'Bobby', 'Brown', 29);

INSERT INTO Persons VALUES (1002, 'Stella', 'Green', 36);
INSERT INTO Orders VALUES (101, 1011, NULL);

---

Updating Data

Update modifies existing rows in a table.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example:

UPDATE Orders 
SET Personid = 1001 
WHERE OrderId = 101;

---

Deleting Data

DELETE FROM table_name
WHERE condition;

Note: Omitting the WHERE clause will delete all rows from the table.

SQL Data Query Language (DQL)

DQL is primarily used to query data using the SELECT statement.

---

Basic SELECT Queries

SELECT column1, column2
FROM table_name;

SELECT * FROM table_name;

SELECT DISTINCT column 
FROM table_name;

---

SELECT – FROM – WHERE Structure

This is the fundamental structure of a query.

SELECT <attribute_list>
FROM <table_list>
WHERE <condition>;

---

Filtering Rows with WHERE

SELECT * FROM Persons 
WHERE age = 36;

SELECT * FROM Persons 
WHERE age BETWEEN 20 AND 30;

---

Retrieve Specific Attributes

Get the birth date and address of an employee named John B. Smith:

SELECT Bdate, Address
FROM EMPLOYEE
WHERE Fname = 'John' 
  AND Minit = 'B' 
  AND Lname = 'Smith';

---

Pattern Matching with LIKE

Used for filtering text with wildcards.

Wildcards:

• % – Any sequence of characters

• _ – A single character

SELECT * FROM persons 
WHERE lastname LIKE 'S%';   -- Starts with 'S'

SELECT * FROM persons 
WHERE lastname LIKE '%n';   -- Ends with 'n'

SELECT * FROM persons 
WHERE lastname LIKE '%o%';  -- Contains 'o'

---

Aliasing

Used to rename columns or tables temporarily in a query.

For Columns:

SELECT id AS Identity_number 
FROM persons;

SELECT lastname || firstname AS name 
FROM persons;

SELECT lastname || ', ' || firstname AS name 
FROM persons;

For Tables:

SELECT p.id, o.ordernumber
FROM persons AS p, orders AS o
WHERE p.id = o.person_id;

---

Joining Tables

Combining rows from two or more tables based on related columns.

Simple Join:

SELECT persons.id, persons.age, orders.ordernumber
FROM persons, orders
WHERE persons.id = orders.person_id;

Multi-table Join:

Get project number, department number, and manager’s details for projects in Stafford:

SELECT Pnumber, Dnum, Lname, Address, Bdate
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE Dnum = Dnumber 
  AND Mgr_ssn = Ssn 
  AND Plocation = 'Stafford';

Join with Filtering:

Get names and addresses of employees in the Research department:

SELECT Fname, Lname, Address
FROM EMPLOYEE, DEPARTMENT
WHERE Dname = 'Research' 
  AND Dnumber = Dno;

Join with Common Column Names:

When tables have overlapping column names, qualify with table names:

SELECT Fname, EMPLOYEE.Name, Address
FROM EMPLOYEE, DEPARTMENT
WHERE DEPARTMENT.Name = 'Research'
  AND DEPARTMENT.Dnumber = EMPLOYEE.Dnumber;

SQL Comments

Used to annotate code.

-- Single-line comment

/* Multi-line
   comment */

Key Points

• Use DDL for schema design.

• Use DML for managing table data.

• Use DQL (SELECT) for retrieving data.

• Use aliases and joins to write cleaner and more efficient queries.

• Always use WHERE clause in UPDATE and DELETE to avoid accidental data loss.

• Use DESC or DESCRIBE to check table structure.