1. Fundamental Database and Table Management
1.1 Schema and Table Structure Operations
1.1.1 Renaming Tables and Inspecting Structure
This task demonstrates how to rename an existing table and subsequently verify its new structure.
USE Company;
-- Rename the 'tb_emp' table to 'employee_records'
ALTER TABLE tb_emp RENAME TO employee_records;
-- Display all tables within the current database to confirm the rename
SHOW TABLES;
-- Describe the structure of the newly renamed table
DESCRIBE employee_records;
1.1.2 Modifying Column Definitions
This task covers altering a column's name and changing its data type within a table.
USE Company;
-- Change the column 'Id' to 'staff_id' and set its type to INT(11)
ALTER TABLE tb_emp CHANGE COLUMN Id staff_id INT(11);
-- Modify the data type of the 'Name' column to VARCHAR(30)
ALTER TABLE tb_emp MODIFY COLUMN Name VARCHAR(30);
DESCRIBE tb_emp;
1.1.3 Adding and Removing Columns
This task illustrates adding new columns at various positions (last, first, after a specified colum) and removing an existing column from a table.
USE Company;
-- Add a new column 'Country' of type VARCHAR(20) after the 'Name' column
ALTER TABLE tb_emp ADD COLUMN Country VARCHAR(20) AFTER Name;
-- Remove the 'Salary' column from the table
ALTER TABLE tb_emp DROP COLUMN Salary;
DESCRIBE tb_emp;
1.1.4 Reordering Table Columns
This task demonstrates how to change the positional order of columns within a table, moving them to the first position or after another specified column.
USE Company;
-- Move the 'Name' column to the first position in the table and set its type to VARCHAR(25)
ALTER TABLE tb_emp MODIFY COLUMN Name VARCHAR(25) FIRST;
-- Move the 'DeptId' column after the 'Salary' column and set its type to INT(11)
ALTER TABLE tb_emp MODIFY COLUMN DeptId INT(11) AFTER Salary;
DESCRIBE tb_emp;
1.1.5 Removing Foreign Key Constraints
To remove a parent table, it's essential to first dissolve any existing foreign key relationships. This task focuses on deleting a foreign key constraint.
USE Company;
-- Drop the foreign key constraint named 'emp_dept' from the 'tb_emp' table
ALTER TABLE tb_emp DROP FOREIGN KEY emp_dept;
SHOW CREATE TABLE tb_emp \G;
1.2 Data Manipulation Language (DML) Basics
1.2.1 Inserting Records
This task involves inserting multiple specified records into a table simultaneously.
USE Company;
-- Insert multiple new employee records into the 'tb_emp' table
INSERT INTO tb_emp (id, Name, DeptId, Salary) VALUES
(1, 'Nancy', 301, 2300.00),
(2, 'Tod', 303, 5600.00),
(3, 'Carly', 301, 3200.00);
SELECT * FROM tb_emp;
1.2.2 Updating Existing Data
This task focuses on using the UPDATE statement to modify existing data entries within a table based on specified conditions.
USE Company;
-- Update the name 'Carly' to 'Tracy'
UPDATE tb_emp SET Name = 'Tracy' WHERE Name = 'Carly';
-- Update the 'DeptId' for the employee named 'Tracy' to 302
UPDATE tb_emp SET DeptId = 302 WHERE Name = 'Tracy';
-- Update the 'Salary' for the employee named 'Tracy' to 4300.00
UPDATE tb_emp SET Salary = 4300.00 WHERE Name = 'Tracy';
SELECT * FROM tb_emp;
1.2.3 Deleting Records
This task demonstrates how to remove specific rows from a table based on a given condition using the DELETE statement.
USE Company;
-- Delete all employee records where the 'Salary' is greater than 3000
DELETE FROM tb_emp WHERE Salary > 3000;
SELECT * FROM tb_emp;
2. Basic Data Retrieval (Single Table Queries)
2.1 Core SELECT Statements
2.1.1 Retrieving Specific and All Columns
This task covers using SELECT to retrieve data for specific columns and to retrieve all columns from a table.
USE Company;
-- Retrieve only the 'Name' and 'Salary' columns from 'tb_emp'
SELECT Name, Salary
FROM tb_emp;
-- Retrieve all columns from the 'tb_emp' table
SELECT * FROM tb_emp;
2.1.2 Filtering with IN Clause
This task uses the IN keyword to filter results, retrieving data based on whether a column's value is (or is not) present in a specified list.
USE Company;
-- Retrieve 'Name' and 'Salary' for employees whose 'Id' is NOT 1
SELECT Name, Salary
FROM tb_emp
WHERE Id NOT IN (1);
2.1.3 Range Queries using BETWEEN AND
This task demonstrates using the BETWEEN AND keywords to retrieve data within a specified numerical range.
USE Company;
-- Retrieve 'Name' and 'Salary' for employees with a salary between 3000 and 5000 (inclusive)
SELECT Name, Salary
FROM tb_emp
WHERE Salary BETWEEN 3000 AND 5000;
2.2 Advanced Single-Table Filtering
2.2.1 Pattern Matching with LIKE
This task uses the LIKE operator with the wildcard character % to perform pattern-based searches on string data.
USE Company;
-- Retrieve 'Name' and 'Salary' for employees whose 'Name' starts with 'c'
SELECT Name, Salary
FROM tb_emp
WHERE Name LIKE 'c%';
2.2.2 Handling NULL Values and Unique Results
This task demonstrates how to query for NULL values using IS NULL and how to retrieve only unique values from a column using DISTINCT.
USE Company;
-- Retrieve all columns for employees where 'DeptId' is NULL
SELECT *
FROM tb_emp
WHERE DeptId IS NULL;
-- Retrieve unique 'Name' values from the 'tb_emp' table
SELECT DISTINCT Name
FROM tb_emp;
2.2.3 Combining Conditions with AND/OR
This task illustrates using AND to combine multiple conditions, requiring all to be true, and IN for checking against a list of values.
USE Company;
-- Retrieve all columns for employees in 'DeptId' 301 AND with a 'Salary' greater than 3000
SELECT *
FROM tb_emp
WHERE DeptId = 301 AND Salary > 3000;
-- Retrieve all columns for employees whose 'DeptId' is either 301 or 303
SELECT *
FROM tb_emp
WHERE DeptId IN (301, 303);
2.3 Result Set Ordering and Limiting
2.3.1 Sorting Query Results
This task focuses on ordering the query results based on a specified column in descending order.
USE School;
-- Retrieve all information for students in class_id 1, ordered by 'score' in descending order
SELECT * FROM tb_score WHERE class_id = 1 ORDER BY score DESC;
2.3.2 Grouping Data
This task demonstrates grouping data based on a specific column to summarize information for each unique group.
USE School;
-- Group student records by 'class_id' and retrieve 'stu_id', 'class_id', and 'name' for each group
SELECT stu_id, class_id, name FROM tb_class GROUP BY class_id;
2.3.3 Limiting Result Set Size
This task uses the LIMIT clause to retrieve a specific range of records from a sorted result set.
USE School;
-- Retrieve student information for the 2nd to 5th highest scores (offset 1, 4 records)
SELECT * FROM tb_score ORDER BY score DESC LIMIT 1, 4;
3. Advanced Query Techniques
3.1 Data Joining
3.1.1 Inner Joins
An inner join combines rows from two tables only when there is a match in both tables based on a specified join condition.
USE School;
-- Retrieve student names and their corresponding class names using an INNER JOIN
SELECT s.name AS studentName, c.name AS className
FROM tb_student AS s
JOIN tb_class AS c ON c.id = s.class_id;
3.1.2 Outer Joins
Outer joins retrieve all records from one table (the