Database Types

Relational Databases (such as MySQL, Oracle, DB2)

These databases use a structured model to represent relationships like one-to-one, one-to-many, and many-to-many.

Non-Relational Databases (NoSQL)

These are designed to handle large volumes of data with varying structures, particularly for complex data challenges in large-scale applications.

What is SQL?

SQL (Structured Query Language) is a specialized programming language used for managing and querying relational database systems. It enables data retrieval, manipulation, and management.

SQL Categories

DQL (Data Query Language)

Used for retrieving data.

DDL (Data Definition Language)

Used for defining database structures.

DML (Data Manipulation Language)

Used for inserting, deleting, and updating data.

DCL (Data Control Language)

Used for managing access and permissions.

TPL (Transaction Processing Language)

Used for handling transactions.

CCL (Cursor Control Language)

Relationship Between DBMS and SQL

DBMS uses SQL to interact with the database.

Basic MySQL Commands

CREATE DATABASE sample_db;
Create a database named sample_db;

USE sample_db;
Switch to the sample_db database;

SHOW DATABASES;
List all available databases;

DROP DATABASE sample_db;
Delete the sample_db database;

SELECT DATABASE();
Check which database is currently in use;

SHOW TABLES;
List all tables in the current database;

SELECT VERSION()
Check the current version of MySQL;

Test Data Setup

DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;
DROP TABLE IF EXISTS salary_levels;

CREATE TABLE departments(department_id INT(2) NOT NULL,
	department_name VARCHAR(14),
	location VARCHAR(13),
	PRIMARY KEY (department_id)
);
CREATE TABLE employees(employee_id INT(4) NOT NULL,
	name VARCHAR(10),
	job_title VARCHAR(9),
	manager_id INT(4),
	hire_date DATE DEFAULT NULL,
	salary DOUBLE(7,2),
	commission DOUBLE(7,2),
	PRIMARY KEY (employee_id),
	department_id INT(2)
);

CREATE TABLE salary_levels(level INT,
	min_salary INT,
	max_salary INT
);

INSERT INTO departments ( department_id, department_name, location ) VALUES ( 10, 'Accounting', 'New York'); 
INSERT INTO departments ( department_id, department_name, location ) VALUES ( 20, 'Research', 'Dallas'); 
INSERT INTO departments ( department_id, department_name, location ) VALUES ( 30, 'Sales', 'Chicago'); 
INSERT INTO departments ( department_id, department_name, location ) VALUES ( 40, 'Operations', 'Boston'); 
 
INSERT INTO employees ( employee_id, name, job_title, manager_id, hire_date, salary, commission, department_id ) VALUES ( 7369, 'Smith', 'Clerk', 7902,  '1980-12-17', 800, NULL, 20); 
INSERT INTO employees ( employee_id, name, job_title, manager_id, hire_date, salary, commission, department_id ) VALUES ( 7499, 'Allen', 'Salesman', 7698,  '1981-02-20', 1600, 300, 30); 
INSERT INTO employees ( employee_id, name, job_title, manager_id, hire_date, salary, commission, department_id ) VALUES ( 7521, 'Ward', 'Salesman', 7698,  '1981-02-22', 1250, 500, 30); 
INSERT INTO employees ( employee_id, name, job_title, manager_id, hire_date, salary, commission, department_id ) VALUES ( 7566, 'Jones', 'Manager', 7839,  '1981-04-02', 2975, NULL, 20); 
INSERT INTO employees ( employee_id, name, job_title, manager_id, hire_date, salary, commission, department_id ) VALUES ( 7654, 'Martin', 'Salesman', 7698,  '1981-09-28', 1250, 1400, 30); 
INSERT INTO employees ( employee_id, name, job_title, manager_id, hire_date, salary, commission, department_id ) VALUES ( 7698, 'Blake', 'Manager', 7839,  '1981-05-01', 2850, NULL, 30); 
INSERT INTO employees ( employee_id, name, job_title, manager_id, hire_date, salary, commission, department_id ) VALUES ( 7782, 'Clark', 'Manager', 7839,  '1981-06-09', 2450, NULL, 10); 
INSERT INTO employees ( employee_id, name, job_title, manager_id, hire_date, salary, commission, department_id ) VALUES ( 7788, 'Scott', 'Analyst', 7566,  '1987-04-19', 3000, NULL, 20); 
INSERT INTO employees ( employee_id, name, job_title, manager_id, hire_date, salary, commission, department_id ) VALUES ( 7839, 'King', 'President', NULL,  '1981-11-17', 5000, NULL, 10); 
INSERT INTO employees ( employee_id, name, job_title, manager_id, hire_date, salary, commission, department_id ) VALUES ( 7844, 'Turner', 'Salesman', 7698,  '1981-09-08', 1500, 0, 30); 
INSERT INTO employees ( employee_id, name, job_title, manager_id, hire_date, salary, commission, department_id ) VALUES ( 7876, 'Adams', 'Clerk', 7788,  '1987-05-23', 1100, NULL, 20); 
INSERT INTO employees ( employee_id, name, job_title, manager_id, hire_date, salary, commission, department_id ) VALUES ( 7900, 'James', 'Clerk', 7698,  '1981-12-03', 950, NULL, 30); 
INSERT INTO employees ( employee_id, name, job_title, manager_id, hire_date, salary, commission, department_id ) VALUES ( 7902, 'Ford', 'Analyst', 7566,  '1981-12-03', 3000, NULL, 20); 
INSERT INTO employees ( employee_id, name, job_title, manager_id, hire_date, salary, commission, department_id ) VALUES ( 7934, 'Miller', 'Clerk', 7782,  '1982-01-23', 1300, NULL, 10); 
 
INSERT INTO salary_levels ( level, min_salary, max_salary ) VALUES ( 1, 700, 1200); 
INSERT INTO salary_levels ( level, min_salary, max_salary ) VALUES ( 2, 1201, 1400); 
INSERT INTO salary_levels ( level, min_salary, max_salary ) VALUES ( 3, 1401, 2000); 
INSERT INTO salary_levels ( level, min_salary, max_salary ) VALUES ( 4, 2001, 3000); 
INSERT INTO salary_levels ( level, min_salary, max_salary ) VALUES ( 5, 3001, 9999); 
COMMIT;

DQL

Simple Queries

Conditional Queries

Sorting Operations

Data Processing Functions

Mathematical Funcsions

Generating Random Numbers

Date Functions

Group Functions

Group Queries

Join Queries

DROP TABLE IF EXISTS customer;
DROP TABLE IF EXISTS orders;

CREATE TABLE customer(
  customer_id INT,
  customer_name VARCHAR(32)
);

CREATE TABLE orders(
  order_id INT,
  order_amount DECIMAL(5,1),
  customer_id INT
);

INSERT INTO customer(customer_id,customer_name) VALUES(1,'Zhang San');
INSERT INTO customer(customer_id,customer_name) VALUES(2,'Li Si');
INSERT INTO customer(customer_id,customer_name) VALUES(3,'Wang Wu');

INSERT INTO orders(order_id, order_amount, customer_id) VALUES(10, 1000.0, 1);
INSERT INTO orders(order_id, order_amount, customer_id) VALUES(20, 2000.0, 1);
INSERT INTO orders(order_id, order_amount, customer_id) VALUES(30, 3000.0, 2);
INSERT INTO orders(order_id, order_amount, customer_id) VALUES(40, 4000.0, 2);

COMMIT;
SELECT * FROM customer;
SELECT * FROM orders;