SQL (Structured Query Language) manages data in relational database management systems (RDBMS). It supports inserting, querying, updating, deleting data, creating and modifying schemas, and controlling access.

A relational DBMS stores data in tables composed of rows and columns. Each table represents a collection of related data entries.

---

Basic Query Operations

Selecting Data

Retrieve specific columns:

SELECT col_a, col_b FROM data_table;

Retrieve all columns:

SELECT * FROM data_table;

Return unique values in specified columns:

SELECT DISTINCT col_a, col_b FROM data_table;

Filterign Rows with WHERE

Restrict results based on conditions:

SELECT col_a, col_b FROM data_table WHERE condition_expr;

Text literals require single quotes; numeric literals do not.

Combine multiple conditions using AND (both true) or OR (either true):

SELECT * FROM data_table WHERE cond_1 AND cond_2;
SELECT * FROM data_table WHERE cond_1 OR cond_2;
SELECT * FROM data_table WHERE cond_1 AND (cond_2 OR cond_3);

Sorting Results

Arrange output by one or more columns:

SELECT col_a, col_b FROM data_table ORDER BY col_a ASC, col_b DESC;

ASC for ascending (default), DESC for descending.

Inserting Records

Add new rows:

With out column list:

INSERT INTO data_table VALUES (val1, val2, val3);

With explicit column list:

INSERT INTO data_table (col_a, col_b, col_c) VALUES (val1, val2, val3);

Updating Existing Data

Modify column values:

UPDATE data_table SET col_a = val1, col_b = val2 WHERE condition_expr;

Omitting WHERE updates all rows.

Removing Rows

Delete specific rows:

DELETE FROM data_table WHERE condition_expr;

Remove all rows while keeping table structure:

DELETE FROM data_table;

---

Advanced Query Features

Limiting Result Size

Fetch a fixed number of rows (MySQL syntax):

SELECT cols FROM data_table LIMIT row_count;

Pattern Matching with LIKE

Search for patterns in text columns:

SELECT col_a, col_b FROM data_table WHERE col_name LIKE 'g%'; -- starts with g
SELECT col_a, col_b FROM data_table WHERE col_name LIKE '%g'; -- ends with g
SELECT col_a, col_b FROM data_table WHERE col_name LIKE '%g%'; -- contains g

Wildcards

_ matches exactly one character:

SELECT * FROM data_table WHERE name LIKE '_k'; -- any char followed by k

Matching Multiple Values

Use IN to test membership:

SELECT col_a, col_b FROM data_table WHERE col_name IN (val1, val2, val3);

Range Selection

Select values within an interval:

SELECT * FROM data_table WHERE col_name BETWEEN low_val AND high_val;

Column and Table Aliases

Improve readability:

Column alias:

SELECT col_name AS alias_name FROM data_table;

Table alias:

SELECT * FROM data_table AS tbl_alias;

Concatenate columns:

SELECT CONCAT(col_a, ',', col_b) AS merged_col FROM data_table;

Joining Tables

Combine rows from multiple tables via related columns.

Inner join returns matching rows:

SELECT cols FROM tbl_one JOIN tbl_two ON tbl_one.key_col = tbl_two.key_col;

Left join returns all rows from the left table and matched rows from the right:

SELECT cols FROM tbl_left LEFT JOIN tbl_right ON tbl_left.key_col = tbl_right.key_col;

Right join returns all rows from the right table and matched rows from the left:

SELECT cols FROM tbl_left RIGHT JOIN tbl_right ON tbl_left.key_col = tbl_right.key_col;

Full outer join returns rows when there is a match in either table:

SELECT cols FROM tbl_one FULL OUTER JOIN tbl_two ON tbl_one.key_col = tbl_two.key_col;

Combining Query Results

Merge results of multiple SELECT statements:

SELECT cols FROM tbl_one
UNION
SELECT cols FROM tbl_two;

Use UNION ALL to retain duplicates.

Copying Data Between Tables

Create a new table from existing data (syntax varies by DBMS):

MySQL alternative:

CREATE TABLE new_tbl AS SELECT * FROM orig_tbl;

Insert into an existing table:

INSERT INTO target_tbl SELECT * FROM source_tbl;
INSERT INTO target_tbl (col_list) SELECT col_list FROM source_tbl;

Schema Creation

Create a database:

CREATE DATABASE db_name;

Define a table:

CREATE TABLE tbl_name (
  col_name1 TYPE(size),
  col_name2 TYPE(size)
);

Constraints

Enforce rules at table or column level:

• NOT NULL: prohibits nulls
• UNIQUE: ensures uniqueness
• PRIMARY KEY: combination of NOT NULL and UNIQUE
• FOREIGN KEY: enforces referential integrity
• CHECK: restricts column values
• DEFAULT: provides default value

Example:

CREATE TABLE people (
  pid INT NOT NULL PRIMARY KEY,
  last_n VARCHAR(255) NOT NULL,
  first_n VARCHAR(255)
);

Alter constraints with ALTER TABLE.

Indexes

Speed up searches:

Simple index (allows duplicates):

CREATE INDEX idx_name ON tbl_name(col_name);

Unique index (no duplicate index values):

CREATE UNIQUE INDEX idx_name ON tbl_name(col_name);

Removing Database Objects

Drop indexes, tables, or databases:

DROP INDEX idx_name ON tbl_name;
DROP TABLE tbl_name;
DROP DATABASE db_name;

Empty a table without removing its definition:

TRUNCATE TABLE tbl_name;

Modifying Table Structure

Add column:

ALTER TABLE tbl_name ADD new_col DATA_TYPE;

Drop column:

ALTER TABLE tbl_name DROP COLUMN col_name;

Change column type:

ALTER TABLE tbl_name MODIFY COLUMN col_name NEW_TYPE;

Auto-Increment Columns

Generate unique numbers automatically:

MySQL example:

CREATE TABLE people (
  rec_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  last_n VARCHAR(255) NOT NULL
);
ALTER TABLE people AUTO_INCREMENT = 100;

Views

Virtual tables defined by queries:

Create:

CREATE VIEW view_name AS
SELECT col_a, col_b FROM tbl_name WHERE condition;

Update underlying table data to reflect changes in views.

Drop view:

DROP VIEW view_name;

Handling NULLs

Test for missing data:

SELECT cols FROM tbl_name WHERE col_name IS NULL;
SELECT cols FROM tbl_name WHERE col_name IS NOT NULL;

Handle NULLs in expressions:

MySQL:

SELECT prod_name, unit_price * (stock_qty + IFNULL(order_qty, 0)) FROM products;
SELECT prod_name, unit_price * (stock_qty + COALESCE(order_qty, 0)) FROM products;

Date and Time Types (MySQL)

• DATE: YYYY-MM-DD
• DATETIME: YYYY-MM-DD HH:MM:SS
• TIMESTAMP: seconds since 1970-01-01 UTC
• TIME: HH:MM:SS
• YEAR: YYYY or YY

Common Data Types

• CHAR(n): fixed-length string
• VARCHAR(n): variable-length string
• INT, SMALLINT, BIGINT: integer types
• DECIMAL(p,s): exact numeric
• FLOAT, DOUBLE: approximate numeric
• BOOLEAN: true/false
• DATE, TIME, TIMESTAMP

---

Functions

Aggregate functions return a single result from multiple rows:

• AVG(): average
• COUNT(): number of rows
• MAX(), MIN(): extreme values
• SUM(): total

Scalar functions transform input values:

• UCASE(), LCASE(): change letter case
• SUBSTRING(str, start, len): extract substring
• LENGTH(): string length (MySQL)
• ROUND(num, decimals): round numbers
• NOW(): current timestamp
• FORMAT(val, fmt): format display

Examples:

SELECT AVG(salary) FROM emp_tbl;
SELECT COUNT(*) FROM emp_tbl;
SELECT MAX(salary) FROM emp_tbl;
SELECT ROUND(price, 2) FROM items;
SELECT CONCAT(fname, ' ', lname) AS full_name FROM staff;

Grouping Data

Group rows and apply aggregates:

SELECT dept, AVG(salary) FROM emp_tbl GROUP BY dept;

Filter groups:

SELECT dept, AVG(salary) FROM emp_tbl GROUP BY dept HAVING AVG(salary) > 50000;

Existence Check

Test if a subquery returns rows:

SELECT cols FROM tbl_name WHERE EXISTS (
  SELECT 1 FROM other_tbl WHERE condition
);