Core SQL Commands

Create database and tables:

CREATE DATABASE company_db DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

CREATE TABLE department (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(16) NOT NULL
) DEFAULT CHARSET=utf8;

CREATE TABLE employee (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(16) NOT NULL,
  email VARCHAR(32) NOT NULL,
  age INT,
  dept_id INT
) DEFAULT CHARSET=utf8;

Data Insertion

INSERT INTO department(name) VALUES ("Engineering");
INSERT INTO department(name) VALUES ("Operations");
INSERT INTO department(name) VALUES ("Sales");

INSERT INTO employee(full_name, email, age, dept_id) 
VALUES ("John Smith", "john.smith@company.com", 30, 1),
       ("Sarah Johnson", "sarah.j@company.com", 28, 1),
       ("Michael Brown", "m.brown@company.com", 35, 2),
       ("Emily Davis", "emily.d@company.com", 42, 3);

Exact Queries

SELECT * FROM employee WHERE age > 30;
SELECT id, full_name FROM employee WHERE dept_id = 1;
SELECT * FROM employee WHERE age BETWEEN 25 AND 40;

Pattern Matching

SELECT * FROM employee WHERE full_name LIKE '%John%';
SELECT * FROM employee WHERE email LIKE '%@company.com';

Data Transformation

SELECT id, full_name, 
  CASE 
    WHEN age < 30 THEN 'Junior'
    WHEN age < 40 THEN 'Mid-level'
    ELSE 'Senior'
  END AS experience_level
FROM employee;

Result Ordering

SELECT * FROM employee ORDER BY age DESC;
SELECT * FROM employee ORDER BY dept_id ASC, age DESC;

Result Limiting

SELECT * FROM employee LIMIT 3;
SELECT * FROM employee ORDER BY age DESC LIMIT 2;

Data Aggregation

SELECT dept_id, COUNT(id) AS staff_count 
FROM employee 
GROUP BY dept_id 
HAVING COUNT(id) > 1;

Table Joins

SELECT e.id, e.full_name, d.name AS department 
FROM employee e 
LEFT JOIN department d ON e.dept_id = d.id;

Data Combination

SELECT id, name FROM department
UNION
SELECT id, full_name FROM employee;

Dataabse Table Relationships

One-to-Many Relationship

CREATE TABLE department (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(16) NOT NULL
) DEFAULT CHARSET=utf8;

CREATE TABLE employee (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(16) NOT NULL,
  dept_id INT NOT NULL,
  CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES department(id)
) DEFAULT CHARSET=utf8;

Many-to-Many Relationship

CREATE TABLE student (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(16) NOT NULL
) DEFAULT CHARSET=utf8;

CREATE TABLE course (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(16) NOT NULL
) DEFAULT CHARSET=utf8;

CREATE TABLE enrollment (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  course_id INT NOT NULL,
  CONSTRAINT fk_enroll_student FOREIGN KEY (student_id) REFERENCES student(id),
  CONSTRAINT fk_enroll_course FOREIGN KEY (course_id) REFERENCES course(id)
) DEFAULT CHARSET=utf8;

User Access Control

Account Management

CREATE USER 'dev_user'@'localhost' IDENTIFIED BY 'secure_pass123';
RENAME USER 'dev_user'@'localhost' TO 'developer'@'192.168.1.%';
ALTER USER 'developer'@'192.168.1.%' IDENTIFIED BY 'new_password_456';

Privilege Assignment

GRANT SELECT, INSERT ON company_db.* TO 'developer'@'192.168.1.%';
REVOKE INSERT ON company_db.employee FROM 'developer'@'192.168.1.%';
FLUSH PRIVILEGES;

Permission Verification

SHOW GRANTS FOR 'developer'@'192.168.1.%';