Lab 2: Basic

DDL and DML

in MySQL
Subtitle: CSIT115 - Winter 2024
University of Wollongong Dubai

This Photo by Unknown Author is licensed under CC BY-NC

Introduction • What is MySQL Workbench?
to MySQL • A graphical tool for MySQL database
design and query execution.
Workbench • Useful for writing and executing SQL
commands.
 What is DDL and DML?

DDL (Data Definition DML (Data Manipulation

Language): Commands for Language): Commands for
defining the structure of managing data in the
the database. database.
• Example: Creating a • Example: Inserting a
students table. student record.
 Creating a Database (DDL)

Syntax: Example:

•CREATE DATABASE •CREATE DATABASE

database_name; lab2;
•USE •USE lab2;
database_name;
Creating Tables (DDL)

Syntax:

• CREATE TABLE table_name ( column_name data_type constraints

);

Example:

• CREATE TABLE students ( student_id INT PRIMARY KEY,

student_name VARCHAR(50) NOT NULL, degree VARCHAR(50) );
Data Types in MySQL
 • CHAR(n): Fixed-length string, up to 255
characters.
• VARCHAR(n): Variable-length string, up to
String Data 65,535 characters.
Types: • TEXT: Large text data.
• LONGTEXT: Extremely large text data, up
to 4 GB.
 • INT: Integer values.
Numeric • FLOAT: Approximate decimal values.
Data Types: • DECIMAL(p, s): Maximum decimal values
with precision.
 • DATE: Stores date values (YYYY-MM-DD).
Date and • DATETIME: Stores date and time values.
Time Types: • YEAR: Store year in YYYY format
 • BOOLEAN: Stores a true or false value.
Typically stored as TINYINT(1) internally by
MySQL.
• ENUM: Stores a predefined set of values.
Only one value can be selected from the
Additional set.
Data Types: • job_role ENUM('Manager', 'Developer',
'Designer') );
• SET: Stores multiple values from a
predefined set.
• SET('Python', 'Java', 'C++', 'Ruby') );
 • NOT NULL: Ensures the column cannot
have NULL values.
Basic • PRIMARY KEY: Ensures unique
identification for each row.
Constraints • UNIQUE: Ensures all values in the column
on Columns are unique.
• DEFAULT: Assigns a default value to a
column if no value is provided.
 • Syntax:
• INSERT INTO table_name
(column1, column2, ...) VALUES
(value1, value2, ...);
Inserting
Data (DML)
 • Insert specific values:
• INSERT INTO students (student_id, student_name,
degree) VALUES (101, 'Alice Smith', 'Computer
Science’);
• You must give value to all columns with only one
exception for default values
• Insert with default values:
Examples: • INSERT INTO students (student_id, student_name)
VALUES (102, 'Bob Brown');
• If the degree column has a default value (e.g.,
'Undeclared'), it will be automatically assigned.
• Insert multiple rows:
• INSERT INTO students (student_id, student_name,
degree) VALUES (103, 'Charlie Davis', 'Engineering'),
(104, 'Diana Evans', 'Mathematics');
 • Basic Syntax:
• SELECT column1, column2 FROM
Retrieving table_name WHERE condition;

Data (DML -
SELECT
Statement)
 • Retrieve all columns:
• SELECT * FROM students;
• Retrieve specific columns:
• SELECT student_name, degree FROM
students;
Examples: • Filter results with conditions:
• SELECT * FROM students WHERE degree =
'Computer Science’;
• Use comparison operators:
• SELECT * FROM students WHERE age > 20;
 • Use logical operators:
• SELECT * FROM students WHERE degree =
'Computer Science' AND age > 18;
• SELECT * FROM students WHERE degree =
'Computer Science' OR age > 20;
More • SELECT * FROM students WHERE age
Examples BETWEEN 18 AND 25;
• Use arithmetic operators:
• SELECT Stu_id, Stu_gpa, Stu_gpa + 0.5 AS
'GPA After Bonus' FROM students;
• SELECT Stu_id, YEAR(CURDATE()) -
YEAR(Stu_dob) AS 'Age' FROM students;
 • Common Functions:
• COUNT(), SUM(), AVG(), MIN(),
MAX()

Aggregate • Syntax:
• SELECT
Functions function_name(column_name)
FROM table_name;
 • Count rows:
• SELECT COUNT(student_id) FROM students;
• Calculate average:
• SELECT AVG(age) FROM students;
• Find minimum and maximum:
Examples: • SELECT MIN(age), MAX(age) FROM students;
• Calculate the total of a column (e.g.,
SUM):
• SELECT SUM(salary) FROM employees;
• Finding distinct values:
• SELECT DISTINCT degree FROM students;
 • Syntax:
• SELECT * FROM table_name
ORDER BY column_name
ASC/DESC;

Sorting Data • Example:

• SELECT * FROM students ORDER
BY student_name ASC;
• SELECT * FROM students ORDER
BY Stu_city ASC, Stu_gpa DESC;
 • Update Syntax:
• UPDATE table_name SET column_name =
value WHERE condition;
• Example:
Updating • UPDATE students SET degree = 'Data Science'
and Deleting WHERE student_id = 101;
• Delete Syntax:
Data (DML) • DELETE FROM table_name WHERE condition;
• Example:
• DELETE FROM students WHERE student_id =
101;
 • Safe Mode Purpose:
• It prevents DELETE or UPDATE queries that
could alter or remove all rows unintentionally.
• This is especially useful for novice users or in
production environments where accidental
data loss can have significant consequences.
Safe Mode • Disabling Safe Mode:
• To allow such operations, you can disable
safe mode temporarily using:
• SET SQL_SAFE_UPDATES = 0;
• This command must be used cautiously and
sparingly, as it removes the safeguard against
accidental changes.
Lab Tasks Summary
TASK 1: Create the Boxing_Events table with proper constraints.
TASK 2: Insert sample data.
TASK 3: Display data sorted by ticket price.
TASK 4: List unique venues and countries.
TASK 5: Count events in Dubai and non-UAE locations.
TASK 6: Show boxers and venue details for UAE weight scale 150kg events.
TASK 7: Show combined condition
TASK 8: show with function
TASK 9: Reduce Price
TASK 10: Conditional delete
Key Takeaways

• DDL for defining database structure.

• DML for managing data.
• Ensure proper use of primary keys and
constraints.
• Practice SQL queries regularly.
Q&A Session

• Feel free to ask any questions!

• Hands-on practice recommended for better
understanding.