Constraints and Queries

MySQL

Session 5

Dr. Hetal Gandhi

Senior Facilitator
Email: [email protected]
Welcome to the Digital Regenesys course in

R Programming Session 5
Constraints and Queries

We will be starting shortly …

 REGENESYS INTEGRATED LEADERSHIP AND
MANAGEMENT MODEL
Holistic focus on the individual (SQ,
EQ, IQ, and PQ)

Interrelationships are dynamic

between individual, team, institution
and the external environment
(systemic)

Strategy affects individual, team,

organisational, and environmental
performance

Delivery requires alignment of strategy,

structure, systems and culture
REGENESYS GRADUATE ATTRIBUTES
 Recap

Keys in MySQL

7
 Summary of SQL keys

• Primary key: Uniquely identifies each row in a table.

There can only be one primary key per table.
• Candidate key: A set of columns that uniquely
identifies each row in a table. There can be multiple
candidate keys per table.
• Alternate key: A set of columns that uniquely identifies
each row in a table, but is not the primary key.
• Foreign key: A column or set of columns that
references the primary key of another table.
 Agenda

 Constraints
 Queries in MySQL

9
 Resources needed

 MySQL Workbench and server

 Portal resources

10
 Try it

Which of the following is true about a Primary Key?

a) It allows duplicate values
b) It cannot have NULL values
c) A table can have multiple primary keys
d) It is used only for indexing
 Try it

Which of the following is true about a Primary Key?

a) It allows duplicate values
b) It cannot have NULL values
c) A table can have multiple primary keys
d) It is used only for indexing

ANSWER: b
 Try it

A Foreign Key must always reference which type of

key in another table?
a) Any column
b) Unique Key
c) Primary Key
d) Composite Key
 Try it

A Foreign Key must always reference which type of

key in another table?
a) Any column
b) Unique Key
c) Primary Key
d) Composite Key

ANSWER: c
Constraints
 SQL Constraints

SQL constraints are rules that the database enforces to ensure

the integrity and accuracy of data.

They can be used to prevent duplicate values, ensure that data

is within a certain range, and require that certain fields are not
empty.
 Types of SQL constraints
 NOT NULL: Prevents a column from accepting null values.
 UNIQUE: Ensures that all values in a column are unique.
 PRIMARY KEY: Uniquely identifies each record in a table.
 FOREIGN KEY: Links one table to another table by referencing the
primary key of the other table.
 CHECK: Ensures that data in a column meets a certain condition.
 DEFAULT: Specifies a default value for a column if no value is provided
when a record is inserted.
 NOT NULL constraint
The following SQL statement creates a table with a NOT NULL
constraint on the name column:

CREATE TABLE customers (

id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
);

This prevents users from inserting records into the customers table
without providing a value for the name column.
 UNIQUE constraint
The following SQL statement creates a table with a UNIQUE
constraint on the email column:

CREATE TABLE users (

id INT NOT NULL AUTO_INCREMENT,
email VARCHAR(255) UNIQUE,
PRIMARY KEY (id)
);

This prevents users from inserting records into the users table
with duplicate email addresses.
 PRIMARY KEY constraint
The following SQL statement creates a table with a PRIMARY KEY constraint on the id column:

CREATE TABLE products (

id INT NOT NULL AUTO_INCREMENT,

name VARCHAR(255) NOT NULL,

PRIMARY KEY (id)

);
This ensures that each product in the products table has a unique identifier.
 FOREIGN KEY constraint
The following SQL statement creates a table with a FOREIGN KEY constraint on the
customer_id column:

CREATE TABLE orders (

id INT NOT NULL AUTO_INCREMENT,
customer_id INT NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers (id)
);

This ensures that each order in the orders table is associated with a valid
customer in the customers table.
 CHECK constraint
The following SQL statement creates a table with a CHECK constraint on the age column:

CREATE TABLE users (

id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
age INT NOT NULL CHECK (age >= 18),
PRIMARY KEY (id)
);
This prevents users from inserting records into the users table with an age less than 18.
 DEFAULT constraint
The following SQL statement creates a table with a DEFAULT constraint on the country column:

CREATE TABLE customers (

id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
country VARCHAR(255) DEFAULT 'USA',
PRIMARY KEY (id)
);

This specifies that the default value for the country column is USA. If a user does not provide a value
for the country column when inserting a record into the customers table, the database will
automatically insert the value USA.
Benefi ts of using Keys and SQL constraints

 Improved data integrity: Constraints help to ensure that data is

accurate and consistent.
 Reduced data entry errors: Constraints can help to prevent users
from entering invalid data.
 Easier database maintenance: Constraints can make it easier to
maintain and update database tables.
 Enhanced database performance: Constraints can help to
improve database performance by optimizing queries.
DEMO

25
Queries in MySQL
 Queries in MySQL

Query:
• It is a request for data retrieval or manipulation in a database using
SQL (Structured Query Language).
• Queries help in fetching, inserting, updating, and deleting data from
MySQL databases.
 Queries with multi ple conditi ons
Use clauses to apply multiple conditions to your queries:
 AND
 OR
 LIKE
 IN
 Exists
 NOT
 IS NULL
 BETWEEN

Refer this: https://five.co/blog/sql-multiple-where-clauses/

 Queries with multi ple conditi ons
Sample Questions on employees table. Drop all records and insert these first.
INSERT INTO employees (PersonID, FirstName, LastName, Address, City, Age) VALUES
(105, 'Emma', 'Brown', '654 Maple St', 'Miami', 26),
(101, 'John', 'Doe', '123 Main St', 'New York', 28),
(104, 'David', 'White', '321 Pine St', 'Chicago', 30),
(107, 'Grace', 'Wilson', '543 Birch St', 'New York', 33),
(102, 'Alice', 'Smith', '456 Elm St', 'Chicago', 34),
(103, 'Bob', 'Johnson', '789 Oak St', 'New York', 40),
(106, 'Frank', 'Miller', '987 Cedar St', 'Miami', 45);
 Queries with multi ple conditi ons
Sample Questions
1. Retrieve all employees who live in New York.
2. Retrieve employees whose Age is greater than 30.
3. Retrieve employees whose FirstName starts with 'D'.
4. Retrieve employees who live in Chicago or Miami.
5. Retrieve employees whose Age is between 30 and 40.
6. Retrieve employees whose LastName contains 'son'.
7. Retrieve employees whose PersonID is not between 102 and 105.
8. Retrieve employees who do not live in Miami.
9. Retrieve employees whose Address ends with 'St'.
10. Retrieve employees who have an Age of 28, 34, or 40.
 Queries in MySQL
Basic Data Retrieval
• SELECT * FROM employees; -- Fetch all columns from employees table
• SELECT FirstName, Age FROM employees WHERE City = 'New York’;

Filtering Data using WHERE Clause

• SELECT * FROM employees WHERE Age > 30;
• SELECT * FROM employees WHERE City = 'London' AND Age < 40;

Sorting Data using ORDER BY

• SELECT * FROM employees ORDER BY Age;
• SELECT * FROM employees ORDER BY City ASC, Age DESC; # Descending order for @Age
 Queries in MySQL
Using LIMIT to Restrict Output size
• SELECT * FROM employees LIMIT 5; -- Returns first 5 rows

Retrieving Unique Values – DISTINCT

• SELECT DISTINCT City FROM employees;

• SELECT DISTINCT City, Age FROM employees;

 Aggregate functi ons
Definition: Aggregate functions perform calculations on multiple rows of data and return a single
summary value. These functions are commonly used with the GROUP BY clause.

Common Aggregate Functions are count(), sum(), avg(), min(), max()

Examples:

1. SELECT COUNT(*) FROM employees; # Return the number of rows in table

2. SELECT COUNT(Age) FROM employees; # Returns the number of non-null values in a column.

3. SELECT MAX(Age) FROM employees; # Returns the maximum value in a column.

 Aggregate Queries in MySQL
Purpose:

Grouping Data – GROUP BY

SELECT dept_name, COUNT(*) AS instr_Dept_Count
FROM instructors
GROUP BY dept_name;
 Aggregate Queries in MySQL
ACTIVITY: Retrieve the count of employees in each city, grouped by city.

Grouping Data – GROUP BY

SELECT City, COUNT(*) AS Emp_city_Count
FROM employees
GROUP BY City;
 Aggregate Queries in MySQL
APPLY AGGREGATE FUNCTION: Find the average age of employees in each city.

Grouping Data – GROUP BY

SELECT City, AVG(Age) AS AvgAge
FROM employees
GROUP BY City;
 Aggregate Queries in MySQL
FILTER the AGGREGATE RESULTS: Retrieve the count of employees in each city but only include
cities where more than one employee resides.

Grouping Data – GROUP BY + HAVING

SELECT City, COUNT(*) AS EmployeeCount
FROM employees
GROUP BY City
HAVING COUNT(*) > 1;
 Aggregate Queries in MySQL
ACTIVITY: Retrieve employees grouped by City, but only show groups where the average Age is
greater than 35.

Grouping Data – GROUP BY + HAVING

SELECT City, AVG(Age) AS AvgAge
FROM employees
GROUP BY City
HAVING AVG(Age) < 35;
 Related Queries
Sample Questions- Solve using WHERE, GROUP BY, HAVING, ORDER BY, DISTINCT, LIMIT, and clauses
on the employees table:
1. Retrieve the distinct cities where employees live.
2. Retrieve all employees sorted by Age in descending order.
3. Retrieve the youngest 3 employees.
4. Retrieve employees whose Age is greater than 30, ordered by Age in ascending order.
5. Retrieve distinct first names of employees.
6. Retrieve the total number of employees.
7. Retrieve the maximum age of employees in each city, but only include cities where the maximum age
is greater than 30.
8. Retrieve all employees sorted first by City in ascending order and then by Age in descending order.
 Summary

 Constraints

 Queries

40
Contents for next session

 Date and Time Functions

 Control Flow Statements

41
Thank You