Exam Notes on MySQL

MySQL Server Setup

• Ensure MySQL Server is running.
• 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).
• Utilize examples from books and online resources.