Database Constraint Conditions

Default Value Constraint

CREATE TABLE user_data(user_id INT, username VARCHAR(32) DEFAULT 'john');

Zero Fill Constraint

CREATE TABLE sample_table(id INT ZEROFILL);

Unsigned Constraint

CREATE TABLE numeric_data(value_id INT UNSIGNED);

Not Null Constraint

CREATE TABLE employee_info(emp_id INT, emp_name VARCHAR(32) NOT NULL);

Unique Constraint

-- Single column unique
CREATE TABLE user_accounts(account_id INT, email VARCHAR(32) UNIQUE);

-- Multiple columns unique
CREATE TABLE network_config(
    config_id INT,
    host_address VARCHAR(32),
    port_number VARCHAR(32),
    UNIQUE(host_address, port_number)
);

Primary Key Constraint

CREATE TABLE main_table(record_id INT PRIMARY KEY);

InnoDB storage engine requires each table to have a primary key. When not explicitly defined, InnoDB creates a hidden primary key that doesn't improve query performance. Explicit primary keys enhance query speed through indexing.

Auto Increment

CREATE TABLE auto_table(
    entry_id INT PRIMARY KEY AUTO_INCREMENT,
    item_name VARCHAR(32)
);

Integer Field Width Specification

CREATE TABLE width_test(id INT(9) ZEROFILL);
INSERT INTO width_test VALUES(7);

Table Truncation Methods

-- Method 1: Does not reset auto-increment values
DELETE FROM table_name;

-- Method 2: Clears table and resets auto-increment values
TRUNCATE table_name;

TRUNCATE is recommended as it allows potential data recovery through binlog files that record all SQL statements.

Additinoal SQL Statements

Rename Table

ALTER TABLE original_table RENAME TO new_table;

Add Column

ALTER TABLE table_name ADD column_name data_type [constraints];

ALTER TABLE table_name ADD column_name data_type [constraints] FIRST;

ALTER TABLE table_name ADD column_name data_type [constraints] AFTER existing_column;

Drop Column

ALTER TABLE table_name DROP column_name;

Modify Column

-- MODIFY changes data type and constraints only
ALTER TABLE table_name MODIFY column_name new_data_type [constraints];

-- CHANGE can modify column name and data type
ALTER TABLE table_name CHANGE old_column_name new_column_name old_data_type [constraints];

Data Preparation for Query Examples

CREATE TABLE employee_data(
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20) NOT NULL,
    gender ENUM('male','female') NOT NULL DEFAULT 'male',
    emp_age SMALLINT(3) UNSIGNED NOT NULL DEFAULT 28,
    join_date DATE NOT NULL,
    position_title VARCHAR(50),
    position_notes VARCHAR(100),
    salary_amount DOUBLE(15,2),
    office_number INT,
    department_id INT
);

WHERE Clause Filtering

-- Employees with ID between 3 and 6
SELECT emp_id, emp_name FROM employee_data WHERE emp_id >= 3 AND emp_id <= 6;
SELECT * FROM employee_data WHERE emp_id BETWEEN 3 AND 6;

-- Employees with specific salaries
SELECT * FROM employee_data WHERE salary_amount IN (20000, 18000, 17000);

-- Employees with 'o' in their name
SELECT emp_name, salary_amount FROM employee_data WHERE emp_name LIKE '%o%';

-- Employees with exactly 4-character names
SELECT emp_name, salary_amount FROM employee_data WHERE emp_name LIKE '____';
SELECT emp_name, salary_amount FROM employee_data WHERE CHAR_LENGTH(emp_name) = 4;

-- Employees with NULL position notes
SELECT emp_name, position_title FROM employee_data WHERE position_notes IS NULL;

GROUP BY Clause

Basic Grouping

SELECT position_title FROM employee_data GROUP BY position_title;

Aggregate Functions with Grouping

-- Maximum salary per department
SELECT position_title, MAX(salary_amount) FROM employee_data GROUP BY position_title;

-- Minimum salary per department
SELECT position_title, MIN(salary_amount) FROM employee_data GROUP BY position_title;

-- Average salary per department
SELECT position_title, AVG(salary_amount) FROM employee_data GROUP BY position_title;

-- Total salary per department
SELECT position_title, SUM(salary_amount) FROM employee_data GROUP BY position_title;

-- Employee count per department
SELECT position_title, COUNT(emp_id) FROM employee_data GROUP BY position_title;

Group Concatenation Functions

-- Concatenate employee names per department
SELECT position_title, GROUP_CONCAT(emp_name) FROM employee_data GROUP BY position_title;

-- Concatenate with custom separator
SELECT position_title, GROUP_CONCAT(emp_name SEPARATOR '|') FROM employee_data GROUP BY position_title;

-- Individual concatenation
SELECT CONCAT(emp_name, '|', gender) FROM employee_data;

-- Concatenation with same separator
SELECT position_title, CONCAT_WS('|', emp_name, emp_age, gender) FROM employee_data GROUP BY position_title;

HAVING Clause Filtering

-- Departments with average salary > 10000 for employees over 30
SELECT AVG(salary_amount) AS avg_salary 
FROM employee_data 
WHERE emp_age > 30 
GROUP BY position_title 
HAVING AVG(salary_amount) > 10000;

DISTINCT Clause

SELECT DISTINCT emp_id, emp_age FROM employee_data;

ORDER BY Clause

-- Ascending order by salary
SELECT * FROM employee_data ORDER BY salary_amount;

-- Descending order by salary
SELECT * FROM employee_data ORDER BY salary_amount DESC;

-- Multiple field sorting
SELECT * FROM employee_data ORDER BY emp_age DESC, salary_amount;

-- Complex query with ordering
SELECT AVG(salary_amount) 
FROM employee_data 
WHERE emp_age > 20 
GROUP BY position_title 
HAVING AVG(salary_amount) > 1000 
ORDER BY AVG(salary_amount) DESC;

LIMIT Clause

-- Limit results to 3 records
SELECT * FROM employee_data LIMIT 3;

-- Highest paid employee
SELECT * FROM employee_data ORDER BY salary_amount DESC LIMIT 1;

-- Pagination example
SELECT * FROM employee_data LIMIT 0, 5;  -- Records 1-5
SELECT * FROM employee_data LIMIT 5, 5;  -- Records 6-10

REGEXP Clause

SELECT * FROM employee_data WHERE emp_name REGEXP '^j.*(n|y)$';