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 ...;

• PRIMARY KEY uniquely identifies a row.

• FOREIGN KEY enforces referential integrity between tables.

• NOT NULL is used 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 * FROM table_name;

SELECT column1, column2 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.