Creating a Practice Database and Tables

The standard MySQL database for common operations is db_school, while db_practice serves as a learning environment. Understanding how to create tables, modify their structure, and define constraints is fundamental for effective database management.

Setting Up the Practice Environment

# Remove existing database if present
DROP DATABASE IF EXISTS `db_practice`;

# Create a new database with GB2312 character set
CREATE DATABASE `db_practice` CHARACTER SET GB2312;

# Select the database for operations
USE `db_practice`;

Creating Supplier, Part, and Supply Tables

# Create the supplier table
CREATE TABLE `suppliers` (
    SUPPLIER_ID CHAR(5),
    SUPPLIER_NAME VARCHAR(20) NOT NULL UNIQUE,
    STATUS SMALLINT,
    CITY VARCHAR(20),
    PRIMARY KEY(SUPPLIER_ID),
    CONSTRAINT CK_SUPPLIERS CHECK(CITY != 'London' OR STATUS = 20)
);

# Create the parts table
CREATE TABLE `parts` (
    PART_ID CHAR(5) PRIMARY KEY,
    PART_NAME VARCHAR(20) NOT NULL,
    COLOR VARCHAR(20) CHECK(COLOR IN('Red', 'Yellow', 'Green', 'Blue')),
    WEIGHT FLOAT
);

# Create the supply relationship table
CREATE TABLE `supplies` (
    SUPPLIER_ID CHAR(5),
    PART_ID CHAR(5),
    QUANTITY INT,
    PRIMARY KEY(SUPPLIER_ID, PART_ID),
    CONSTRAINT FK_SUPPLIES1 FOREIGN KEY(SUPPLIER_ID) REFERENCES SUPPLIERS(SUPPLIER_ID),
    CONSTRAINT FK_SUPPLIES2 FOREIGN KEY(PART_ID) REFERENCES PARTS(PART_ID)
);

Data Insertion Operations

The INSERT INTO statement adds new records to tables. When field names are omitted, values must correspond to all fields in the table order. Null values represent missing data, with each record enclosed in parentheses.

Important: String values must be enclosed in single quotation marks. Copying SQL code directly from articles may cause issues if quotes are incorrectly formatted.

Inserting Supplier Data

# Insert data into the suppliers table
INSERT INTO `suppliers` VALUES
    ('S1', 'Smith', 20, 'London'),
    ('S2', 'Johnson', 10, 'Paris'),
    ('S3', 'Williams', 30, 'Paris'),
    ('S4', 'Brown', 20, 'London'),
    ('S5', 'Davis', 30, 'Athens'),
    ('S6', 'Miller', NULL, 'New York');

Attempting to insert duplicate primary key values results in:

ERROR 1062 (23000): Duplicate entry 'P1' for key 'PRIMARY'

Solution: Delete existing records with the same key before inserting new data.

Inserting Part Data

# Insert data into the parts table
INSERT INTO `parts` VALUES
    ('P1', 'Nut', 'Red', 12),
    ('P2', 'Bolt', 'Green', 17),
    ('P3', 'Screw', 'Blue', 17),
    ('P4', 'Screw', 'Red', 14),
    ('P5', 'Cam', 'Blue', 12),
    ('P6', 'Cog', 'Red', 19);

Inserting Supply Data

# Insert data into the supplies table
INSERT INTO `supplies` VALUES
    ('S1', 'P1', 200),
    ('S1', 'P5', 400),
    ('S2', 'P1', 200),
    ('S2', 'P2', 200),
    ('S2', 'P3', 500),
    ('S2', 'P4', 600),
    ('S2', 'P5', 400),
    ('S2', 'P6', 800),
    ('S3', 'P3', 200),
    ('S3', 'P4', 500),
    ('S4', 'P2', 300),
    ('S4', 'P5', 300),
    ('S5', 'P1', 100),
    ('S5', 'P2', 100),
    ('S5', 'P3', 200),
    ('S5', 'P5', 400);

If the table structure doesn't match the data being inserted:

ERROR 1136 (21S01): Column count doesn't match value count at row 1

Solution: Adjust the table structure to match the data or modify the insert statement.

Data Modification Operations

Updating Records

The UPDATE statement modifies existing records. The syntax is:

UPDATE `table_name` SET field_name='new_value' WHERE field_name='condition_value'

The table name can also be specified as database_name.table_name.

Example: Increasing Weight of Blue Parts

# Increase the weight of blue parts by 20%
UPDATE `parts` SET WEIGHT = WEIGHT * 1.2 WHERE COLOR = 'Blue';

Data Deletion Operations

The DELETE FROM statement removes records from tables. The syntax is:

DELETE FROM `table_name` WHERE field_name='condition_value'

Example: Removing Suppliers with Null Status

# Delete suppliers with null status values
DELETE FROM `suppliers` WHERE STATUS IS NULL;

Example: Removing Suppliers Without Parts

# Add a new supplier without any parts
INSERT INTO `suppliers` VALUES('S7', 'Wilson', 50, 'New York');

# Remove suppliers that don't supply any parts
DELETE FROM suppliers WHERE SUPPLIER_ID NOT IN(SELECT SUPPLIER_ID FROM supplies);

Data Query Operations

SELECT Statement Syntax

The SELECT statement retrieves data from tables. Its components include:

• [DISTINCT] - Removes duplicate records
• Target data - fields, strings, functions, expressions
• FROM - Table or view names
• WHERE - Filtering conditions
• GROUP BY - Grouping fields
• HAVING - Group filtering conditions
• ORDER BY - Sorting results
• LIMIT - Result set size control

Aggregate Functions

Five common aggregate functions for grouped data:

• COUNT() - Record count per group
• SUM() - Sum of field values per group
• AVG() - Average of field values per group
• MAX() - Maximum value in each group
• MIN() - Minimum value in each group

Example: Querying Supplier Supply Totals

# Query total quantity supplied by each supplier
SELECT SUPPLIER_ID, SUM(QUANTITY) TOTAL_SUPPLY
FROM supplies
GROUP BY SUPPLIER_ID;

Example: Filtering by Supply Total

# Query suppliers with total supply greater than 1000
SELECT SUPPLIER_ID, SUM(QUANTITY)
FROM supplies
GROUP BY SUPPLIER_ID
HAVING SUM(QUANTITY) >= 1000;

Query Execution Order

The logical execution order of SELECT statements is:

1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause

Common Query Examples

Example 1: Suppliers for Part P1

# Select database
USE `db_practice`;

# Query suppliers for part P1
SELECT SUPPLIER_ID FROM supplies WHERE PART_ID = 'P1';

Example 2: Supplies Between 300-500

# Query supplies with quantity between 300 and 500
SELECT * FROM supplies 
WHERE QUANTITY BETWEEN 300 AND 500;

Example 3: Suppliers of Red Parts

# Query suppliers of red parts using nested query
SELECT SUPPLIER_ID, SUPPLIER_NAME FROM suppliers 
WHERE SUPPLIER_ID IN (
    SELECT s.SUPPLIER_ID 
    FROM parts p, supplies s 
    WHERE p.PART_ID = s.PART_ID AND p.COLOR = 'Red'
);

Example 4: Light Parts from Paris Suppliers

# Query parts under 15 weight from Paris suppliers
SELECT PART_ID, PART_NAME FROM parts 
WHERE WEIGHT < 15 AND PART_ID IN (
    SELECT p.PART_ID 
    FROM suppliers s, supplies p 
    WHERE s.SUPPLIER_ID = p.SUPPLIER_ID AND s.CITY = 'Paris'
);

Example 5: Parts Supplied by London Suppliers

# Query parts supplied by London suppliers
SELECT DISTINCT PART_NAME 
FROM parts p, supplies s, suppliers sup 
WHERE p.PART_ID = s.PART_ID AND sup.SUPPLIER_ID = s.SUPPLIER_ID 
AND sup.CITY = 'London';

Example 6: Suppliers Not Supplying Red Parts

# Query suppliers not supplying red parts
SELECT SUPPLIER_NAME FROM suppliers 
WHERE SUPPLIER_ID NOT IN (
    SELECT s.SUPPLIER_ID 
    FROM parts p, supplies s 
    WHERE p.PART_ID = s.PART_ID AND p.COLOR = 'Red'
);

Example 7: Parts Not Supplied by Supplier S3

# Query parts not supplied by supplier S3
SELECT PART_NAME FROM parts 
WHERE PART_ID NOT IN (
    SELECT PART_ID FROM supplies WHERE SUPPLIER_ID = 'S3'
);

Example 8: Suppliers Supplying Both P1 and P2

# Query suppliers supplying both P1 and P2 parts
SELECT SUPPLIER_NAME FROM suppliers 
WHERE SUPPLIER_ID IN (
    SELECT s1.SUPPLIER_ID 
    FROM supplies s1, supplies s2 
    WHERE s1.SUPPLIER_ID = s2.SUPPLIER_ID 
    AND s1.PART_ID = 'P1' 
    AND s2.PART_ID = 'P2'
);

Example 9: Parts with Same Color as Nut

# Query parts with same color as nut (excluding nut itself)
SELECT PART_ID, PART_NAME FROM parts 
WHERE PART_NAME != 'Nut' AND COLOR = (
    SELECT COLOR FROM parts WHERE PART_NAME = 'Nut'
);

Example 10: Suppliers Supplying All Parts

# Query suppliers supplying all available parts
SELECT SUPPLIER_NAME FROM suppliers 
WHERE NOT EXISTS (
    SELECT * FROM parts WHERE NOT EXISTS (
        SELECT * FROM supplies 
        WHERE suppliers.SUPPLIER_ID = supplies.SUPPLIER_ID 
        AND supplies.PART_ID = parts.PART_ID
    )
);

Join Techniques

When querying multiple tables, different join methods can be used:

Implicit Inner Join

SELECT DISTINCT fields FROM table1, table2 
WHERE table1.common_field = table2.common_field

Explicit Inner Join

SELECT fields FROM table1 
INNER JOIN table2 ON table1.common_field = table2.common_field

Natural Join

SELECT fields FROM table1 
NATURAL JOIN table2

Key considerations when writing complex queries:

1. For nested queries, write the inner query first, then incorporate it into the outer query
2. Use nested queries when target fields and conditions are in two related tables
3. Use three-table joins when target fields and conditions span three tables
4. Specify table prefixes when field names are ambiguous across tables
5. Use DISTINCT to eliminate duplicate records in join results