SQL LMD Commands and Constraints
LMD (Language Manipulation of Data) SQL Commands:
1. SELECT:
Retrieves data from one or more tables.
Example:
SELECT name, email FROM users;
2. INSERT:
Inserts new records into a table.
Example:
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
3. UPDATE:
Modifies existing records.
Example:
UPDATE users SET email = 'alice_new@example.com' WHERE name = 'Alice';
4. DELETE:
Deletes existing records from a table.
Example:
DELETE FROM users WHERE name = 'Alice';
SQL Constraints:
1. NOT NULL:
Ensures a column cannot have a NULL value.
Example:
name VARCHAR(100) NOT NULL
2. UNIQUE:
Ensures all values in a column are different.
Example:
email VARCHAR(100) UNIQUE
3. PRIMARY KEY:
Uniquely identifies each record in a table.
Example:
id INT PRIMARY KEY
4. FOREIGN KEY:
Ensures referential integrity for a record in another table.
Example:
user_id INT,
FOREIGN KEY (user_id) REFERENCES users(id)
5. CHECK:
Ensures that all values in a column satisfy a specific condition.
Example:
age INT CHECK (age >= 18)
6. DEFAULT:
Provides a default value for a column.
Example:
status VARCHAR(20) DEFAULT 'active'
7. INDEX:
Improves the speed of data retrieval.
Example:
CREATE INDEX idx_user_name ON users(name);
Advanced SQL SELECT Features
SQL SELECT Clauses and Features:
1. Basic SELECT:
SELECT column1, column2 FROM table;
2. WHERE:
Filters records based on conditions.
Example:
SELECT * FROM users WHERE age > 18;
3. AND, OR, NOT:
Combine multiple conditions.
Example:
SELECT * FROM users WHERE age > 18 AND status = 'active';
4. IN, NOT IN:
Match values within a set.
Example:
SELECT * FROM users WHERE country IN ('USA', 'Canada');
SELECT * FROM users WHERE country NOT IN ('USA', 'Canada');
5. BETWEEN:
Select values within a range.
Example:
SELECT * FROM users WHERE age BETWEEN 18 AND 30;
6. LIKE:
Pattern matching.
Example:
SELECT * FROM users WHERE name LIKE 'A%';
7. IS NULL / IS NOT NULL:
Check for NULL values.
Example:
SELECT * FROM users WHERE email IS NULL;
8. ORDER BY:
Sort results.
Example:
SELECT * FROM users ORDER BY name ASC;
9. GROUP BY:
Group results based on one or more columns.
Example:
SELECT country, COUNT(*) FROM users GROUP BY country;
10. HAVING:
Filter groups created by GROUP BY.
Example:
SELECT country, COUNT(*) FROM users GROUP BY country HAVING COUNT(*) > 5;
11. LIMIT / OFFSET:
Restrict result size.
Example:
SELECT * FROM users LIMIT 10 OFFSET 20;
12. ALIAS (AS):
Rename columns or tables in results.
Example:
SELECT name AS username FROM users;
13. JOIN:
Combine rows from two or more tables.
Example:
SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id;
14. DATE Functions (may vary by SQL dialect):
- CURRENT_DATE / NOW():
SELECT * FROM events WHERE event_date = CURRENT_DATE;
- DATE(), YEAR(), MONTH(), DAY():
SELECT * FROM events WHERE YEAR(event_date) = 2025;
- DATE_ADD() / DATE_SUB():
SELECT * FROM tasks WHERE due_date = DATE_ADD(CURRENT_DATE, INTERVAL 7
DAY);
- DATEDIFF():
SELECT DATEDIFF(CURRENT_DATE, registration_date) AS days_active FROM users;
