Database Management System

A database is an organized collection of data, stored so that it can be found, used and changed easily. You already use many:

• your school keeps every student’s name, class, address, attendance and marks;
• your phone keeps contacts, messages and the list of calls;
• a hospital keeps each patient’s visits, tests and medicines;
• a bank keeps every account and every deposit and withdrawal.

In each, the data is arranged so that any piece of it can be reached when it is needed. A database lets you add new data, search for exactly what you want, update what has changed and remove what is no longer needed, without everything getting mixed up. You can think of it as a well-kept digital cupboard: everything has its place, nothing is lost.

Today almost every computer system has a database working behind it. When you book a bus ticket online, log in to a social media app, recharge a phone or watch a video, a database finds your record, checks it and changes it in a moment.

Here is how databases are used around us:

A cloud database runs on a company’s servers in a data center, not on a computer in your office. You don’t buy or look after a server: you use the database over the Internet, pay for what you use, and it can grow as your data grows. That is why cloud databases are becoming popular with businesses of every size.

Data and information

Data is a collection of raw facts and figures: numbers, words, dates, pictures or sounds, as they were collected, before anyone has worked on them. Your name, your age, your phone number and your marks in each subject are data. On its own, a piece of data may mean little: what is “85”? Marks? A weight? A price?

Information is what we get when data is organized or processed so that it means something and helps us understand or decide. If a student’s three terminal marks are 80, 85 and 90 (data), then “Her average this year is 85, the best in her class” is information.

A computer takes data as its input, processes it, and gives information as its output. So data is the raw material, and information is the useful product.

Database

A database is an organized collection of related data that can be easily accessed, managed and updated. In the kind of database you will meet in this unit, the data is kept in tables, which makes it easy to find and use. A school database, for example, may have a table of students, a table of teachers, a table of subjects and a table of marks.

Before computers, offices kept their data in a traditional file system: registers, paper files and folders in cabinets, written and searched by hand. Today most organizations keep it in a computerized database, which stores, finds and changes data far faster and more reliably.

Database Management System (DBMS)

A database management system (DBMS) is software that is used to create a database, and to store, organize, find, change and protect its data. With a DBMS we can create, read (retrieve), update and delete data, the four basic jobs often called CRUD, in an orderly way. It keeps the data correct (data integrity), secure and easy to reach.

A DBMS stands between the users and the data. You never open the database’s files yourself: you ask the DBMS, and it does the work, checking each request on the way.

Some well-known DBMS software:

• a.MySQL: free and open source; very widely used behind websites and web apps.
• b.Oracle Database: a powerful commercial DBMS used by large companies, banks and governments.
• c.Microsoft SQL Server: Microsoft’s DBMS, used in many businesses.
• d.PostgreSQL: free and open source, known for its many advanced features and its reliability.
• e.MongoDB: a NoSQL database, which keeps data as documents rather than tables, for large amounts of data that doesn’t fit neatly into rows and columns.
• f.SQLite: a small database that lives inside an app instead of on a separate server; it is in every Android phone and iPhone, and in web browsers.

Recent trends in DBMS

1. Cloud databases: databases run on cloud providers’ servers and used over the Internet. They can grow or shrink with the need (scalability), are quick to set up, and you pay only for what you use.
2. NoSQL databases: databases that don’t use tables, such as MongoDB (documents) and Cassandra (spread over many servers). They handle very large amounts of loosely structured data, such as social media posts, which makes them popular for big data.
3. In-memory databases: such as Redis (Remote Dictionary Server) and SAP HANA, which keep data in the computer’s main memory (RAM) instead of on disk, so they answer much faster.
4. Distributed databases: one database spread across many servers, often in different places. If one server fails, the others carry on (fault tolerance), and users are served by a server near them.

Features of DBMS

1. Data storage: data is stored in tables of rows and columns, and every piece of data has its own place.
2. Data retrieval: any data can be found quickly with a short command (a query).
3. Data manipulation: data can be added, changed and deleted easily.
4. Data integrity: data is kept correct and consistent by rules; for example, a DBMS can refuse a mark above 100 or a phone number with letters in it.
5. Data redundancy control: redundancy means the same data stored again and again. A DBMS keeps each fact in one place and links to it, so data isn’t repeated without need. (Organizing tables to remove repetition is called normalization.)
6. Security: a DBMS controls who can see the data and what each person may do with it, so private information stays private.
7. Backup and recovery: data can be backed up, and recovered after an accident such as a disk failure or a wrong delete.

Benefits of DBMS

• i.Data is easy to store and quick to find.
• ii.Redundancy (repeated data) is reduced.
• iii.Data stays consistent and accurate, because rules check it.
• iv.Many users can use the same data at the same time.
• v.Data can be backed up and recovered.
• vi.Data is secure: each user gets only the access they need.

Challenges of DBMS

• i.Setting up and managing it needs technical knowledge and trained people.
• ii.The first cost (software, servers, training) can be high.
• iii.If it isn’t managed properly, a lot of valuable data is at risk in one place.
• iv.Large systems need regular maintenance, updates and backups.
• v.It can become slow with very large amounts of data unless it is designed well.

Relational Database Management System (RDBMS)

A relational database management system (RDBMS) is a DBMS that stores data in tables of rows and columns, and links the tables to each other through common fields (keys). The data about students is in one table, about classes in another, and a column in the students table that holds the class links each student to a class. MySQL is an RDBMS, and so are Oracle Database, PostgreSQL, Microsoft SQL Server and SQLite. RDBMSs are used almost everywhere: in websites, apps, banks, schools and businesses.

Every column of a table holds one kind of data, and its data type says what kind: whole numbers, decimals, text, dates or true/false. Age and roll number are whole numbers (INT), a price has decimals (DECIMAL), a name is text (VARCHAR), a date of birth is a date (DATE). Choosing the right type means the data is stored correctly, takes no more space than it needs, and can be checked: a DATE column refuses “2082-13-45”, and an INT column refuses “ten”.

Here are the data types used most often in MySQL, grouped by the kind of data they hold.

1. Numeric data types

Used for numbers.

2. Character (text) data types

Used for letters, words and text.

For example, in a CHAR(10) column, “Sita” takes the space of 10 characters; in a VARCHAR(10) column it takes the space of 4 (plus a byte for its length). A value longer than n is refused by both.

3. Date and time data types

Used for dates and times.

4. Boolean data type

Used for values that can only be true or false: yes or no.

(In MySQL, BOOLEAN is stored as a tiny whole number: TRUE is 1 and FALSE is 0.)

A table is how a relational database stores data: in rows and columns, much like a spreadsheet. Each table holds one kind of thing: students, books, products. Each column is one kind of fact (name, age, price), and each row is one complete entry. Since all the data of a relational database is kept in tables, the table is called its basic building block.

Features of a table:

• a.Rows and columns: data is arranged in horizontal rows and vertical columns.
• b.One record per row: each row holds one complete entry, usually told apart from the others by a unique ID (its primary key).
• c.A defined structure (schema): each column has a name and a data type: text, number or date.
• d.Organized data: tables keep data neat and easy to understand.
• e.Easy to search and change: rows can quickly be found, added, updated or deleted.
• f.Relationships: tables can be linked to other tables by keys, so the data is connected.

Fields

A field, also called a column, holds one kind of data about every entity in the table. In a students table, the fields might be ID, Name, Class and Date of Birth. Each field has a name and a data type, and holds that one kind of fact for every student.

In this table:

• a.Student_ID, Name, Class and DOB are the fields.
• b.Each field holds one type of data: Student_ID and Class hold whole numbers, Name holds text, and DOB (date of birth) holds dates.

Key points about fields:

• i.A field is a vertical column of a table.
• ii.A field holds one kind of data for every record in the table.
• iii.The field’s name says what it holds: Name, Class, DOB.
• iv.The field’s data type decides what values it can store: text, numbers or dates.

Records

A record, also called a row or a tuple, is all the data about one entity in a table: one student, one employee, one product. A record has a value for each field of the table. In short, a record is one complete entry.

In this table:

• a.The whole row (101, Binay Adhikari, 10, 2010-05-02) is one record.
• b.It holds the complete information about one student, Binay Adhikari.

Key points about records:

• i.A record holds all the information about one entity (one student, one employee or one product).
• ii.A record is made up of fields, and each field holds one kind of data.
• iii.Records run horizontally: each row is the complete data for one entry.

Two students in one school can have the same name, the same class and even the same date of birth. How does the database tell them apart, and how does another table point to exactly one of them? With keys. A key is a field (or a set of fields) used to identify each record uniquely and to link tables together. Keys keep the data organized, accurate and connected.

1. Primary key

A primary key is a field (or a combination of fields) that uniquely identifies each record in a table. No two rows may have the same value in it, and it may not be left empty (NULL, which means “no value”).

Features of a primary key:

• a.It uniquely identifies each record.
• b.It can’t contain duplicate values.
• c.It can’t be NULL (empty).
• d.A table has only one primary key (though it may be made of more than one field: a composite key, below).

Here, StudentID is the primary key: each student has a different one. Name can’t be the key (two students may share a name), nor can Class or Age.

Sometimes more than one field could be the key: in a students table, both StudentID and a student’s unique registration number. Each such field is a candidate key (a candidate to become the primary key), and the designer chooses one as the primary key.

2. Foreign key

A foreign key is a field in one table that refers to the primary key of another table. It creates a relationship between the two tables, and makes sure the link is valid: the DBMS refuses a value that doesn’t exist in the other table. A foreign key is often called the linking pin between two tables.

For example, a bank lends money to people to buy cars. A Bank table has BankID as its primary key; a Car table has a field FinancedBy, which holds the BankID of the bank that paid for the car. FinancedBy is a foreign key: it links each car to one row of the Bank table.

(The banks here are made up.) Two cars point to B2, so one bank can finance many cars, while each car has one bank.

Features of a foreign key:

• a.It creates a relationship between two tables.
• b.It refers to the primary key of another table.
• c.It enforces the relationship: a value that isn’t in the other table is refused.
• d.It keeps data valid (referential integrity).
• e.It can be one column or more.
• f.Unlike a primary key, its values can repeat (many cars can have the same bank).

Another example: a school library lends books to students.

Table: Students

Table: Library

• In the Library table, StudentID is a foreign key that refers to StudentID in the Students table.
• So only real students’ IDs can be entered in the Library table: a book can’t be lent to student 299 if there is no student 299.

3. Composite key

A composite key is a key made by combining two or more fields, used when no single field is unique on its own.

• a.Neither StudentID nor Subject alone can identify a row: 301 appears twice, and so does Social.
• b.But StudentID + Subject together is unique: each student has one mark in each subject. Together they form a composite key.

Practice yourself

Types of relationship

A relationship is a link between two tables, made through a common field: a key. Relationships let data be stored in separate tables without repeating it, keep it accurate, and let us bring related data back together when we need it. Counting how many records on each side can be linked gives three types:

1. One-to-one (1 : 1): each record in one table is linked to exactly one record in the other, and the other way round. Example: a person has one passport, and each passport belongs to one person. This type is not common; it is used to keep closely related information apart, such as a student’s private health details in a separate table from her other details.
2. One-to-many (1 : M): one record in the first table is linked to many records in the second, but each record in the second is linked to only one in the first. Example: a teacher may teach many classes, but each class is taught (in that subject) by one teacher. This is the most common type. The foreign key goes on the “many” side: each class’s row holds its teacher’s ID.
3. Many-to-many (M : M): many records in one table can be linked to many records in the other. Example: a student can enroll in many courses, and each course has many students. No single foreign key can hold this, so a third table, a junction table (or linking table), is added between them: each of its rows links one student to one course.

Data is everywhere today, from school records to online shopping and social media, and it needs a dependable tool to manage it. One of the most popular in the world is MySQL.

MySQL is a relational database management system (RDBMS) that stores, manages and organizes data in tables, and that you talk to in SQL (Structured Query Language). It was first made by a Swedish company, MySQL AB, and released in 1995; Sun Microsystems bought the company in 2008, and since 2010 MySQL has been owned and developed by Oracle Corporation. Its free version, MySQL Community Server, is open source. It is used in websites, web apps, schools, banks and offices because it is fast, reliable and free.

With MySQL you work with the data through SQL queries, without needing to know how the data is stored inside. A query is a command sent to the database: to retrieve data, or to insert, update or delete it. SQL has statements for defining the structure of the data, for working with the data, and for controlling who may use it.

Features of MySQL:

• i.Simple language: it uses SQL, whose commands read almost like English.
• ii.Data retrieval: data is found quickly with SELECT queries.
• iii.Data manipulation: rows are added, changed and deleted with INSERT, UPDATE and DELETE.
• iv.Data definition: databases and tables are created and changed with CREATE, ALTER and DROP.
• v.Multiple users: many users and programs can use the same database at the same time.
• vi.Security: users need a password, and each can be given only the access they need.
• vii.Used in modern applications: websites, web and mobile apps, and many well-known sites run on it; the free Community Server is open source.

Download and install MySQL

To practice on your own computer, you install MySQL Server (the DBMS itself) and MySQL Workbench (a program with windows and buttons for typing queries and seeing the results). The steps below install both on Windows with MySQL Installer. Your teacher may set it up for you in the lab.

Using MySQL Workbench

At the end of the installation, MySQL Workbench should open by itself; if it doesn’t, start it from the Start menu. Its first screen is the welcome page, and under MySQL Connections at the bottom there is one tile for the server on your own computer, called something like Local instance MySQL80.

• a.Double-click that tile.
• b.In the window that opens, type the root password you chose, and click OK.
• c.You are now connected to your MySQL server, and the SQL editor opens, ready for you to work with your databases.

Check that MySQL is installed

After installing MySQL, check that it really works. Open the Command Prompt (or a terminal) and type:

mysql --version

If MySQL is installed properly, it shows the version, for example:

mysql  Ver 8.0.34 for Win64 on x86_64 (MySQL Community Server - GPL)

This means MySQL is installed and Windows can find it. You can also log in to the server from the command line:

mysql -u root -p

Here -u root says the user name (root) and -p makes it ask for the password. Type the password (nothing shows on the screen as you type, which is normal). If you see the mysql> prompt, the server is running and you can type SQL straight into it. To leave, type exit or quit (or press Ctrl + D on Linux and macOS).

2.6.1 Table in MySQL

In MySQL, as in every relational database, data is stored in tables: each row is a record, and each column is a field. All of a database’s data is kept in one or more tables, and tables are created, changed and deleted with SQL commands.

Features of a table:

• i.It stores data in rows and columns.
• ii.Each column has a data type (a number, text or a date).
• iii.Each row of a table is called a record.
• iv.Each column of a table is called a field.
• v.Tables can be linked with each other using keys (primary keys and foreign keys).
• vi.Tables can be created, changed or deleted with SQL commands.

Relational table

A relational table is a table in a relational database: data in rows and columns, each table holding one kind of entity (students, teachers, courses). What makes it relational is that it can be linked (related) to other tables by keys: a primary key in one, a foreign key in another. Each row is a unique record, and each column a field. Splitting the data into related tables avoids repeating it and keeps it easy to manage.

For example, a school might have:

• i.a Students table, with each student’s details;
• ii.a Courses table, with each course’s details;
• iii.a StudentCourses table, which records which student is enrolled in which course.

Students

Courses

StudentCourses

StudentCourses is a junction table: StudentId is a foreign key to Students, CourseId a foreign key to Courses, and the two together are its primary key (a composite key), so the same student can’t be enrolled in the same course twice. Reading its first row: student 1 (Biraj) takes course 1 (SQL Server). Biraj takes three courses; SQL Server has two students. The tables, related by keys, form a relational database.

2.6.2 Structured Query Language (SQL)

A query is a question or an order given to the database: a request to retrieve, insert, update or delete data. Queries are written in SQL (Structured Query Language), which was developed at IBM in the 1970s to work with relational databases and is now understood by almost every DBMS. With queries, users get exactly the information they want, chosen by conditions.

For example, to see only the Class 10 students from a school’s database, you write a query that picks just those records. Queries filter, sort and search large amounts of data quickly and accurately, and they change data without anyone hunting for each record by hand. A query can be very short or very long, but either way it is how users talk to a database. SQL is said either letter by letter, “ess-cue-ell”, or like the word “sequel”; both are correct.

SQL’s commands are grouped into sub-languages by their job. The three you need are:

(COMMIT and ROLLBACK, which save or undo a group of changes, are sometimes put in a fourth group, TCL, Transaction Control Language; and SELECT on its own is sometimes called DQL, Data Query Language.)

Features of a query:

• i.It retrieves exactly the data asked for from one or more tables.
• ii.It adds, updates or deletes records.
• iii.It filters and sorts data easily.
• iv.Its results are useful for reports and analysis.

MySQL constraints

A constraint is a rule set on a column of a table, which controls what data may be stored in it. Constraints keep the data correct (data integrity): only valid, consistent data gets in, and the DBMS refuses a value that breaks a rule, such as a duplicate ID, a missing name or a mark of 130.

Why constraints matter:

• i.They keep the data accurate and consistent.
• ii.They stop duplicate or wrong entries.
• iii.They make and protect the relationships between tables.
• iv.They make the database follow the organization’s rules (a class must be from 1 to 12; every student must have a name).

Here is a table with every kind of constraint at work:

CREATE TABLE members (  ID INT PRIMARY KEY,  Name VARCHAR(50) NOT NULL,  Class INT NOT NULL CHECK (Class BETWEEN 1 AND 12),  Phone VARCHAR(10) UNIQUE,  Fee_paid BOOLEAN DEFAULT FALSE);

ID must be unique and present; every member needs a name and a class from 1 to 12; no two members may have the same phone number; and if a new row doesn’t say whether the fee is paid, Fee_paid is set to FALSE.

DDL (Data Definition Language) is the part of SQL that defines and changes the structure of a database: its databases, tables, columns and other objects (such as indexes and views). DDL commands create, change and delete these structures; they don’t put data into the tables, but decide how the data will be stored and organized.

In MySQL (and Oracle), a DDL command is saved (committed) at once, as soon as it runs: it can’t be undone with ROLLBACK.

1. CREATE

The CREATE command makes new database objects. Before making tables, we need a database to keep them in, made with CREATE DATABASE. Then we choose it with USE, so that the tables we create go into it.

CREATE DATABASE database_name;

CREATE DATABASE school;USE school;

database_name is the name you give the new database. SHOW DATABASES; lists the databases on the server, and once you have made tables, SHOW TABLES; lists the tables in the database in use.

The CREATE TABLE command makes a new table in the database:

CREATE TABLE table_name (  column1 datatype,  column2 datatype,  column3 datatype,  ...);

column1, column2, column3… are the table’s columns (fields), and each datatype is the type of data that column will hold. The columns are separated by commas, with no comma after the last one, and the whole list is in brackets.

2. ALTER

The ALTER TABLE command changes the structure of a table that already exists, without losing its data. With it you can:

a. Add a new column:

ALTER TABLE table_nameADD column_name data_type;

ALTER TABLE studentsADD Address VARCHAR(50);

The new column is added at the end, and is empty (NULL) for the rows already in the table.

b. Delete (drop) a column:

ALTER TABLE table_nameDROP COLUMN column_name;

ALTER TABLE studentsDROP COLUMN Address;

c. Change a column’s data type (MySQL):

ALTER TABLE table_nameMODIFY COLUMN column_name new_data_type;

ALTER TABLE studentsMODIFY COLUMN Name VARCHAR(100);

d. Rename a column (MySQL): CHANGE gives the old name, the new name and the data type (it can change the type at the same time).

ALTER TABLE table_nameCHANGE old_column_name new_column_name data_type;

ALTER TABLE studentsCHANGE DOB Date_of_birth DATE;

MySQL 8 also has a shorter way that keeps the type: ALTER TABLE students RENAME COLUMN DOB TO Date_of_birth;.

e. Remove a constraint:

ALTER TABLE table_nameDROP CONSTRAINT constraint_name;

A constraint gets a name when it is made, either one you give it or one MySQL makes up. To remove it easily later, name it yourself with CONSTRAINT:

ALTER TABLE studentsADD CONSTRAINT chk_class CHECK (Class BETWEEN 1 AND 12); ALTER TABLE studentsDROP CONSTRAINT chk_class;

3. DROP

The DROP command deletes a database object, such as a table, a whole database or a view, permanently. Once dropped, the object and all its data are gone and can’t be brought back (except from a backup). Use DROP with great care!

To drop a database (with all its tables):

DROP DATABASE database_name;

To drop a table:

DROP TABLE table_name;

DROP TABLE IF EXISTS students; drops the table only if it is there, so it doesn’t fail when it isn’t.

4. TRUNCATE and RENAME

TRUNCATE deletes all the rows of a table at once but keeps the table, with its columns and constraints, empty and ready for new data:

TRUNCATE TABLE students;

RENAME gives a table a new name (in MySQL; the second form works in most databases, SQLite too):

RENAME TABLE students TO pupils;ALTER TABLE pupils RENAME TO students;

DML (Data Manipulation Language) is the part of SQL that works with the data stored in the tables: it inserts new records, retrieves existing data, updates values and deletes records that are no longer needed. DDL deals with the structure; DML deals with the content, and leaves the structure as it is.

DML changes can be undone with ROLLBACK if they were made inside a transaction (started with START TRANSACTION and saved with COMMIT). Outside a transaction, MySQL normally saves each statement at once (it has autocommit on), so a DELETE can’t be undone afterwards.

In this section, the students table has the Address field added in 2.7, and twelve students:

ID  Name             Class  DOB         Address1   Dikshya Pokhrel  10     2010-11-13  Bhaktapur2   Binay Adhikari   10     2010-05-02  Thimi3   Nabin Dahal      10     2011-01-22  Banepa4   Aashish Shah     9      2011-09-16  Bhaktapur5   Ashna Rimal      9      2012-02-08  Kathmandu6   Avinash Thapa    10     2010-12-30  Lalitpur7   Aman Gurung      9      2011-06-11  Bhaktapur8   Sabin Chapagain  9      2012-03-27  Thimi9   Rajesh Maharjan  10     2010-08-19  Kirtipur10  Iman Shrestha    9      2011-10-05  Bhaktapur11  Muna Tamang      10     2010-04-14  Banepa12  Ramesh Karki     9      2011-07-21  Kathmandu

2.8.1 INSERT

An INSERT query adds new records (rows) to a table: one row, or several in one go. It is how fresh data gets into the database: you give a value for each column of the new row.

INSERT INTO table_name (column1, column2, column3, ...)VALUES (value1, value2, value3, ...);

INSERT INTO table_nameVALUES (value1, value2, value3, ...);

In the first form you name the columns, and give the values in the same order. In the second you leave the column names out, so you must give a value for every column, in the order the columns are in the table.

Text and dates go inside single quotes ('Dikshya Pokhrel', '2010-11-13'); numbers don’t (10). Dates are written year-month-day.

2.8.2 SELECT

A SELECT query retrieves data from a table. It shows you just what you need: every row, only some columns, or only the rows that meet a condition. What it gives back is itself a table, called the result set. SELECT never changes the data.

SELECT column_namesFROM table_name;

To put the result in order, add ORDER BY with a column: ORDER BY Name sorts A to Z, ORDER BY DOB DESC puts the youngest first (DESC means descending, from the largest down; ASC, ascending, is the default).

2.8.3 UPDATE

An UPDATE query changes data already in a table: correcting a mistake, or recording a change. You set new values for one or more columns, in the rows chosen by a condition.

UPDATE table_nameSET column1 = value1, column2 = value2, ...WHERE condition;

2.8.4 DELETE

A DELETE query removes records (rows) from a table: data that is wrong, out of date or no longer needed. You can delete the rows that meet a condition, or every row.

DELETE FROM table_nameWHERE condition;

WHERE and LIKE clauses

With WHERE and LIKE, a query picks only the records that meet a condition, instead of the whole table. A clause is a part of an SQL statement that begins with a keyword: FROM, WHERE, ORDER BY.

WHERE clause

The WHERE clause filters records: only the rows that meet its condition are picked. Without WHERE, SELECT returns every row of the table (and UPDATE and DELETE change every row). WHERE works the same in SELECT, UPDATE and DELETE.

A condition compares a column with a value:

LIKE clause

LIKE searches for a pattern in a column, used with WHERE to find partial matches instead of exact values. It works with two wildcard characters, special symbols that stand for unknown characters:

• % (percent sign): any number of characters, including none.
• _ (underscore): exactly one character.

Wildcards help when you know part of a value but not all of it. In the table below, each example is a whole value of the column, a single name.

(Letters in MySQL’s LIKE are matched without caring about capitals: 'a%' finds Aman too.)

2.8.5 Report

In MySQL, a report is data retrieved from one or more tables by SQL queries and presented in an organized, readable way. MySQL itself doesn’t design reports with charts or page layouts for printing, but it gives the structured data that reports are made from. Reports help people understand and analyze data, plan, and make decisions.

In simple terms, a report in MySQL is the result of a query that picks out specific information and presents it in a meaningful form. Basic reports can be made with SQL queries alone. For reports with charts and print layouts, MySQL is connected to reporting tools such as Power BI, Tableau or Looker Studio (called Google Data Studio until 2022), or to a spreadsheet.

Kinds of report you can make with MySQL queries:

• i.Summary reports: totals, such as the number of students or the total sales.
• ii.Detail reports: the records themselves, such as a list of all students with their marks.
• iii.Grouped reports: data grouped by a category, such as the number of students in each class or city.
• iv.Filtered reports: only the records that meet a condition, such as students with 80 or more.
• v.Aggregate reports: values worked out with aggregate functions, which turn many rows into one number: COUNT (how many), SUM (the total), AVG (the average), MAX (the largest) and MIN (the smallest).

Features of reports in MySQL:

• i.Organized presentation: the data is shown in a clear, readable order.
• ii.Filtering: only the records that matter are included (WHERE).
• iii.Summarizing: totals, averages and counts (aggregate functions, GROUP BY).
• iv.Combining tables: one report can bring together data from several related tables.
• v.Timely access: a report run today shows today’s data, since it is made from the database as it is now.
• vi.Easy export: results can be saved as a CSV or Excel file, or sent to reporting tools.

1. Choose the correct answer

2. Write short answers to these questions

3. Write long answers to these questions

4. Write SQL queries for these

Each query follows on from the one before it, in the same database. You can try them in the playground below, which starts with the Employees table of b. (It runs SQLite: for d. and e., use the forms the answers mark as working in SQLite.)