Store your password to access the coding environment.
Database Overview
Two tables will be used: employees and mentorships.
employees table includes fields like ID (integer), name, gender, contact number, age, and date created.
mentorships table includes status (ongoing), project, mentor ID, and mentee ID (both foreign keys).
Relationships between tables can be highlighted.
Creating Tables
Use DROP command before CREATE to avoid errors when practicing.
Create a schema (e.g., company HR) and then use it.
Comments can be added using #.
Example:
DROP SCHEMA IF EXISTS `company HR`;
CREATE SCHEMA `company HR`;
USE `company HR`;
# This is a comment
Table creation uses CREATE TABLE with parentheses to bind code.
Define fields with data types (e.g., INT, VARCHAR, TIMESTAMP).
Specify constraints like NOT NULL, PRIMARY KEY, and AUTO_INCREMENT.
Table and Field Examples
Example employees table creation:
CREATE TABLE co_employees (
ID INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
gender CHAR(1) NOT NULL,
contact_number VARCHAR(55) NULL,
age INT NOT NULL,
date_created TIMESTAMP NOT NULL DEFAULT NOW()
);
Example mentorships table creation (including foreign keys):
CREATE TABLE mentorships (
mentor_id INT NOT NULL,
mentee_id INT NOT NULL,
status VARCHAR(255) NOT NULL,
project VARCHAR(255),
PRIMARY KEY (mentor_id, mentee_id, project),
CONSTRAINT FK1 FOREIGN KEY (mentor_id) REFERENCES co_employees(ID) ON DELETE CASCADE ON UPDATE RESTRICT,
CONSTRAINT FK2 FOREIGN KEY (mentee_id) REFERENCES co_employees(ID) ON DELETE CASCADE ON UPDATE RESTRICT,
CONSTRAINT UQ_mentor_mentee UNIQUE (mentor_id, mentee_id)
);
Foreign keys use constraints to tie tables together.
Constraints can enforce business rules (e.g., UNIQUE for mentor/mentee).
Modifying Tables
Rename a table using ALTER TABLE and RENAME TO.
ALTER TABLE co_employees RENAME TO employees;
Alter tables to drop/add columns using ALTER TABLE, DROP COLUMN, ADD COLUMN.
ALTER TABLE employees DROP COLUMN age;
ALTER TABLE employees ADD COLUMN salary FLOAT NOT NULL AFTER contact_number;
ALTER TABLE employees ADD COLUMN years_in_company INT NOT NULL AFTER salary;
Drop foreign keys using ALTER TABLE and DROP FOREIGN KEY.
Data Manipulation
Insert data using INSERT INTO.
Specify field names and corresponding values.
If the order is the same as defined, field names can be skipped.
Update data using UPDATE and SET with a WHERE clause.
UPDATE employees SET contact_number = 'new_number' WHERE ID = 1;
Delete data using DELETE FROM with a WHERE clause.
DELETE FROM employees WHERE ID = 5;
Select Statements
Basic SELECT:
SELECT employee_name, salary FROM employees;
Alias columns using AS:
SELECT employee_name AS "Employee Name", salary AS "Salary" FROM employees;
Use DISTINCT to avoid duplicates.
SELECT DISTINCT years_in_company FROM employees;
Use WHERE with operators like != (not equal) and LIKE.
LIKE uses % as a wildcard.
Example:
SELECT * FROM employees WHERE employee_name LIKE '%ER%';
Use IN to filter based on multiple values.
SELECT * FROM employees WHERE ID NOT IN (7, 8);
Combine conditions with AND and OR.
Order results using ORDER BY (default is ascending).
Use DESC for descending order.
Aggregate Functions
Calculate average salary using AVG().
SELECT AVG(salary) FROM employees;
Round results using ROUND() with decimal places.
SELECT ROUND(AVG(salary), 2) FROM employees;
Find maximum and minimum values using MAX() and MIN().
Calculate the sum using SUM().
Concatenate strings using CONCAT().
Error Handling
Pay attention to error messages; they provide clues to the location and type of error.
Missing semicolons, parentheses, or misspelled keywords are common issues.
Use context help and online resources (like Google) for assistance.
Copy and paste code to a new query window to resolve random issues.
Additional Tips
Use templates from previous code for similar tasks.
Capitalize commands for readability (coding convention).