MySQL for Web Applications

A website without a database is a brochure. MySQL is the most widely used open-source relational database on the web; it stores the users, products, posts and results behind PHP pages.

The theory of relations and SQL was the subject of Semester III's DBMS book; this unit is the working subset a web developer uses every day.

Which server ran these examples. Every SQL block below was executed on MariaDB 10.11, a drop-in, MySQL-compatible server (it is what mysql means on Debian and Ubuntu); the output tables are what the mysql client printed.

MariaDB and Oracle MySQL 8 agree on everything used in this unit except that some error-message wording and EXPLAIN output differ slightly.

3.1 MySQL basics and data types

MySQL is a client-server database: the server (mysqld) owns the data; clients (the mysql command, phpMyAdmin, your PHP script) connect over a socket or TCP port 3306 with a user name and password. Commands end with ;. A server hosts several databases, each with tables.

CREATE TABLE student (
    roll   INT AUTO_INCREMENT PRIMARY KEY,
    name   VARCHAR(50) NOT NULL,
    dept   VARCHAR(10) NOT NULL DEFAULT 'BCA',
    marks  DECIMAL(5,2) CHECK (marks BETWEEN 0 AND 100),
    email  VARCHAR(80) UNIQUE
) DEFAULT CHARSET = utf8mb4;
INSERT INTO student (name, dept, marks, email) VALUES
    ('Ram',    'BCA', 78.50, 'ram@tu.edu.np'),
    ('Sita',   'BCA', 91.00, 'sita@tu.edu.np'),
    ('Hari',   'BIT', 64.25, NULL),
    ('Gita',   'BIT', 85.50, 'gita@tu.edu.np'),
    ('Bikash', 'BCA', 55.00, 'bikash@tu.edu.np');
SHOW TABLES;
DESCRIBE student;
SELECT * FROM student;

+----------------+
| Tables_in_demo |
+----------------+
| student        |
+----------------+
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| roll  | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50)  | NO   |     | NULL    |                |
| dept  | varchar(10)  | NO   |     | BCA     |                |
| marks | decimal(5,2) | YES  |     | NULL    |                |
| email | varchar(80)  | YES  | UNI | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
+------+--------+------+-------+------------------+
| roll | name   | dept | marks | email            |
+------+--------+------+-------+------------------+
|    1 | Ram    | BCA  | 78.50 | ram@tu.edu.np    |
|    2 | Sita   | BCA  | 91.00 | sita@tu.edu.np   |
|    3 | Hari   | BIT  | 64.25 | NULL             |
|    4 | Gita   | BIT  | 85.50 | gita@tu.edu.np   |
|    5 | Bikash | BCA  | 55.00 | bikash@tu.edu.np |
+------+--------+------+-------+------------------+

3.2 Querying: SELECT, WHERE, ORDER BY

CREATE TABLE student (roll INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), dept VARCHAR(10), marks DECIMAL(5,2));
INSERT INTO student (name, dept, marks) VALUES ('Ram','BCA',78.50),('Sita','BCA',91.00),('Hari','BIT',64.25),('Gita','BIT',85.50),('Bikash','BCA',55.00);
SELECT name, marks FROM student WHERE marks >= 70 ORDER BY marks DESC;
SELECT name FROM student WHERE dept = 'BCA' AND marks BETWEEN 50 AND 80;
SELECT name FROM student WHERE name LIKE '%a' OR dept IN ('BIT');
SELECT name, marks FROM student ORDER BY marks DESC LIMIT 2;
SELECT name, marks + 5 AS grace_marks, UPPER(dept) AS d FROM student WHERE roll <= 2;

+------+-------+
| name | marks |
+------+-------+
| Sita | 91.00 |
| Gita | 85.50 |
| Ram  | 78.50 |
+------+-------+
+--------+
| name   |
+--------+
| Ram    |
| Bikash |
+--------+
+------+
| name |
+------+
| Sita |
| Hari |
| Gita |
+------+
+------+-------+
| name | marks |
+------+-------+
| Sita | 91.00 |
| Gita | 85.50 |
+------+-------+
+------+-------------+------+
| name | grace_marks | d    |
+------+-------------+------+
| Ram  |       83.50 | BCA  |
| Sita |       96.00 | BCA  |
+------+-------------+------+

WHERE filters rows: comparison (= <> < >), AND OR NOT, BETWEEN, IN (…), LIKE (% any characters, _ one), IS NULL (never = NULL). ORDER BY col [ASC|DESC], LIMIT n [OFFSET m] (pagination), DISTINCT removes duplicates, AS renames.

3.3 Updating and deleting

CREATE TABLE student (roll INT PRIMARY KEY, name VARCHAR(50) NOT NULL, marks DECIMAL(5,2), email VARCHAR(80) UNIQUE);
INSERT INTO student VALUES (1,'Ram',78.5,'ram@tu.edu.np'),(2,'Sita',91,'sita@tu.edu.np'),(3,'Hari',64.25,NULL);
UPDATE student SET marks = marks + 5 WHERE roll = 3;
SELECT roll, name, marks FROM student;
DELETE FROM student WHERE marks < 70;
SELECT COUNT(*) AS remaining FROM student;

+------+------+-------+
| roll | name | marks |
+------+------+-------+
|    1 | Ram  | 78.50 |
|    2 | Sita | 91.00 |
|    3 | Hari | 69.25 |
+------+------+-------+
+-----------+
| remaining |
+-----------+
|         2 |
+-----------+

Constraints protect integrity; the server refuses a bad change with a precise error (real messages from MariaDB):

-- raises
CREATE TABLE student (roll INT PRIMARY KEY, name VARCHAR(50) NOT NULL, marks DECIMAL(5,2) CHECK (marks BETWEEN 0 AND 100), email VARCHAR(80) UNIQUE);
INSERT INTO student VALUES (1,'Ram',78.5,'ram@tu.edu.np');
INSERT INTO student VALUES (2,'Sita',91,'ram@tu.edu.np');

--------------
INSERT INTO student VALUES (2,'Sita',91,'ram@tu.edu.np')
--------------

ERROR 1062 (23000) at line 4 in file: 'p.sql': Duplicate entry 'ram@tu.edu.np' for key 'email'

-- raises
CREATE TABLE student (roll INT PRIMARY KEY, name VARCHAR(50) NOT NULL, marks DECIMAL(5,2) CHECK (marks BETWEEN 0 AND 100));
INSERT INTO student VALUES (1,'Ram',178);

--------------
INSERT INTO student VALUES (1,'Ram',178)
--------------

ERROR 4025 (23000) at line 3 in file: 'p.sql': CONSTRAINT `student.marks` failed for `demo`.`student`

-- raises
CREATE TABLE student (roll INT PRIMARY KEY, name VARCHAR(50) NOT NULL);
INSERT INTO student (roll) VALUES (1);

--------------
INSERT INTO student (roll) VALUES (1)
--------------

ERROR 1364 (HY000) at line 3 in file: 'p.sql': Field 'name' doesn't have a default value

3.4 Aggregate functions, GROUP BY and HAVING

CREATE TABLE student (roll INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), dept VARCHAR(10), marks DECIMAL(5,2));
INSERT INTO student (name, dept, marks) VALUES ('Ram','BCA',78.50),('Sita','BCA',91.00),('Hari','BIT',64.25),('Gita','BIT',85.50),('Bikash','BCA',55.00);
SELECT COUNT(*) AS n, SUM(marks) AS total, AVG(marks) AS mean, MIN(marks) AS lo, MAX(marks) AS hi FROM student;
SELECT dept, COUNT(*) AS n, ROUND(AVG(marks), 2) AS mean FROM student GROUP BY dept ORDER BY mean DESC;
SELECT dept, MAX(marks) AS best FROM student GROUP BY dept HAVING MAX(marks) > 90;

+---+--------+-----------+-------+-------+
| n | total  | mean      | lo    | hi    |
+---+--------+-----------+-------+-------+
| 5 | 374.25 | 74.850000 | 55.00 | 91.00 |
+---+--------+-----------+-------+-------+
+------+---+-------+
| dept | n | mean  |
+------+---+-------+
| BIT  | 2 | 74.88 |
| BCA  | 3 | 74.83 |
+------+---+-------+
+------+-------+
| dept | best  |
+------+-------+
| BCA  | 91.00 |
+------+-------+

COUNT(*), SUM, AVG, MIN, MAX collapse rows into one value (ignoring NULL, except COUNT(*)). GROUP BY makes one group per distinct value of the column(s); WHERE filters rows before grouping, HAVING filters groups after it.

3.5 Subqueries and joins

A subquery is a query inside another. A join combines rows of two tables on a condition, normally foreign key = primary key.

CREATE TABLE dept (code VARCHAR(10) PRIMARY KEY, head VARCHAR(40));
CREATE TABLE student (roll INT PRIMARY KEY, name VARCHAR(50), dept VARCHAR(10), marks DECIMAL(5,2),
                      FOREIGN KEY (dept) REFERENCES dept(code));
INSERT INTO dept VALUES ('BCA','Dr. Shah'),('BIT','Dr. Rai'),('BBA','Dr. Thapa');
INSERT INTO student VALUES (1,'Ram','BCA',78.5),(2,'Sita','BCA',91),(3,'Hari','BIT',64.25),(4,'Gita','BIT',85.5);
SELECT name, marks FROM student WHERE marks > (SELECT AVG(marks) FROM student);
SELECT s.name, d.head FROM student s INNER JOIN dept d ON s.dept = d.code ORDER BY s.roll;
SELECT d.code, COUNT(s.roll) AS students FROM dept d LEFT JOIN student s ON s.dept = d.code GROUP BY d.code;
SELECT name FROM student WHERE dept IN (SELECT code FROM dept WHERE head LIKE '%Rai');

+------+-------+
| name | marks |
+------+-------+
| Sita | 91.00 |
| Gita | 85.50 |
+------+-------+
+------+----------+
| name | head     |
+------+----------+
| Ram  | Dr. Shah |
| Sita | Dr. Shah |
| Hari | Dr. Rai  |
| Gita | Dr. Rai  |
+------+----------+
+------+----------+
| code | students |
+------+----------+
| BBA  |        0 |
| BCA  |        2 |
| BIT  |        2 |
+------+----------+
+------+
| name |
+------+
| Hari |
| Gita |
+------+

INNER JOIN keeps only matching pairs; LEFT JOIN keeps every row of the left table, filling NULL where nothing matches (BBA has no students but still appears with 0). IN (subquery), EXISTS, and scalar subqueries (a single value) cover most nested needs.

The foreign key also stops you inserting a student of a department that does not exist:

-- raises
CREATE TABLE dept (code VARCHAR(10) PRIMARY KEY);
CREATE TABLE student (roll INT PRIMARY KEY, dept VARCHAR(10), FOREIGN KEY (dept) REFERENCES dept(code));
INSERT INTO dept VALUES ('BCA');
INSERT INTO student VALUES (1, 'MBA');

--------------
INSERT INTO student VALUES (1, 'MBA')
--------------

ERROR 1452 (23000) at line 5 in file: 'p.sql': Cannot add or update a child row: a foreign key constraint fails (`demo`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`dept`) REFERENCES `dept` (`code`))

3.6 Administration notes

• Users and privileges: never connect the website as root; create a user with only the rights needed (CREATE USER 'web'@'localhost' IDENTIFIED BY '…'; GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'web'@'localhost';).
• Indexes speed up WHERE and JOIN on a column (CREATE INDEX idx_dept ON student(dept);); primary keys and UNIQUE columns have them already.
• Backup: mysqldump -u root -p dbname > backup.sql; restore with mysql -u root -p dbname < backup.sql.
• Engine: use InnoDB (the default) for transactions and foreign keys.
• phpMyAdmin is a web interface for all of the above, bundled in XAMPP.

3.7 Seeing the ideas: how MySQL stores, filters and joins data

A web application is only as good as the database behind it. The plate shows the man who wrote MySQL and the kind of disk hardware on which a database file really lives.

The figures then run the Unit 3 queries on a real MariaDB 10.11.19 server, which speaks the same SQL as MySQL, and show the true row counts, the true numbers and the true error messages.

(a) Monty Widenius, author of MySQL

(b) IBM RAMAC disk, 1956

(c) Many clients, one server

3.7.1 Eight advantages of MySQL for web applications

1. It is free and open source. The community edition of MySQL, and the compatible MariaDB, cost nothing to use. A small business in Biratnagar can run its billing system without a database licence. This keeps the cost of a new web project low.
2. It is fast for common web workloads. Most websites read data far more often than they write it, and MySQL is tuned for this. An index on a column such as roll finds a student without scanning the whole table. A result page for thousands of students therefore opens quickly.
3. It works with PHP out of the box. The mysqli and PDO extensions are included with almost every PHP installation. A few lines connect a script to the server (Unit 4). This is why PHP and MySQL are taught together.
4. It is easy to learn and use. SQL reads almost like English, so SELECT name FROM student WHERE marks > 80 is understood at once. Free tools such as phpMyAdmin give a graphical view of tables. A beginner can create a working database in an afternoon.
5. It enforces data integrity. Primary keys, UNIQUE, NOT NULL, CHECK and foreign keys refuse wrong data before it is stored (Fig. 3.4). A fee table cannot hold a payment for a student who does not exist. The database therefore protects the data even if the PHP code has a bug.
6. It supports transactions and many users. The InnoDB engine keeps a table consistent when several visitors write at once, and a transaction can group several changes into one all-or-nothing step. An online ticket booking can take money and reserve the seat together. If one step fails, both are cancelled.
7. It scales and is available everywhere. MySQL runs on Linux, Windows and macOS, from a laptop to a large server cluster, and cloud providers offer it as a managed service. A site can start small and grow. Moving to a bigger machine does not require rewriting the queries.
8. It has a large community and good tools. Backups with mysqldump, replication, users and privileges, and plenty of tutorials are available. Most hosting companies in Nepal offer MySQL with every plan. When something goes wrong, an answer is usually easy to find.

Formula and fact sheet

• CREATE TABLE with PRIMARY KEY, AUTO_INCREMENT, NOT NULL, UNIQUE, DEFAULT, CHECK, FOREIGN KEY.
• SELECT cols FROM t WHERE … GROUP BY … HAVING … ORDER BY … LIMIT n.
• Aggregates: COUNT SUM AVG MIN MAX. WHERE before grouping, HAVING after.
• INNER JOIN (matches only), LEFT JOIN (all of the left table). IS NULL, never = NULL.
• UPDATE/DELETE always with WHERE. Money in DECIMAL.

Practice

1. Create a table book(id, title, price, stock) with suitable types and constraints; insert four rows.
2. Write queries: books above NPR 500; the three most expensive; the average price.
3. Explain WHERE versus HAVING with an example.
4. Differentiate INNER JOIN and LEFT JOIN.
5. What happens on INSERT of a duplicate value into a UNIQUE column? Quote the error type.
6. Why must DECIMAL be used for money?
7. Write a query listing each department with the number of students, including departments with none.

CREATE TABLE book (id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(80) NOT NULL, price DECIMAL(8,2) NOT NULL CHECK (price >= 0), stock INT NOT NULL DEFAULT 0);
INSERT INTO book (title, price, stock) VALUES ('C Programming', 450, 10), ('Java', 650, 4), ('Python', 700, 8), ('DBMS', 550, 0);
SELECT title FROM book WHERE price > 500;
SELECT title, price FROM book ORDER BY price DESC LIMIT 3;
SELECT AVG(price) AS average_price FROM book;

+--------+
| title  |
+--------+
| Java   |
| Python |
| DBMS   |
+--------+
+--------+--------+
| title  | price  |
+--------+--------+
| Python | 700.00 |
| Java   | 650.00 |
| DBMS   | 550.00 |
+--------+--------+
+---------------+
| average_price |
+---------------+
|    587.500000 |
+---------------+

Model answers (5 and 10 marks)

Q1. What is MySQL? Explain its main data types and write SQL to create a table with constraints. Show how the constraints protect the data. (10 marks)

Introduction. MySQL is an open-source relational database server. It stores data in tables of rows and columns and is used by programs through the language SQL.

A client such as a PHP script, phpMyAdmin or the mysql command connects to the server on port 3306 with a user name and password (Plate 3.1). MariaDB is a compatible fork, and every query in this unit was run on it.

Main data types.

Constraints.

1. PRIMARY KEY identifies each row uniquely and is never null.
2. NOT NULL forces a value to be given.
3. UNIQUE refuses a repeated value (for example an e-mail).
4. DEFAULT supplies a value when none is given.
5. CHECK (condition) refuses values outside a rule, such as marks above 100.
6. FOREIGN KEY ... REFERENCES keeps a child row linked to an existing parent row.

CREATE TABLE dept (code VARCHAR(10) PRIMARY KEY, head VARCHAR(40));
CREATE TABLE student (
    roll  INT AUTO_INCREMENT PRIMARY KEY,
    name  VARCHAR(50) NOT NULL,
    dept  VARCHAR(10) NOT NULL DEFAULT 'BCA',
    marks DECIMAL(5,2) CHECK (marks BETWEEN 0 AND 100),
    email VARCHAR(80) UNIQUE,
    FOREIGN KEY (dept) REFERENCES dept(code)
) DEFAULT CHARSET = utf8mb4;
INSERT INTO dept VALUES ('BCA','Dr. Shah'),('BIT','Dr. Rai');
INSERT INTO student (name, dept, marks, email) VALUES ('Ram','BCA',78.50,'ram@tu.edu.np');
SELECT roll, name, dept, marks FROM student;

+------+------+------+-------+
| roll | name | dept | marks |
+------+------+------+-------+
|    1 | Ram  | BCA  | 78.50 |
+------+------+------+-------+

Protection in action (Fig. 3.4). A second student with the e-mail ram@tu.edu.np is refused with error 1062; marks of 150 are refused by CHECK (error 4025 in MariaDB); a student in the department MBA is refused with error 1452 because no such parent row exists.

Conclusion. MySQL gives a web application a safe, shared store.

Choosing the right type for each column and declaring constraints in the table definition let the database itself refuse wrong data, so the integrity of the records does not depend on every PHP script being correct.

For a college this means that a mark list, a fee table and a library register stay consistent even when several teachers and clerks use the web pages at the same time.

Q2. Differentiate between WHERE and HAVING. Explain with a GROUP BY example. (5 marks)

Both clauses filter, but at different stages of a query (Fig. 3.1).

1. WHERE filters individual rows before grouping. It cannot use aggregate functions such as COUNT or AVG, because the groups do not exist yet.
2. HAVING filters groups after GROUP BY. It can use aggregate functions.
3. Order of execution: FROM, then WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.
4. Speed: a condition on a plain column is better placed in WHERE, since it removes rows early and the grouping has less work.
5. Together: the same query can use both, as in the example below.

In the example the students with marks of 60 or more are kept by WHERE, they are grouped by department, and HAVING keeps only the departments whose average is above 80.

CREATE TABLE student (roll INT PRIMARY KEY, name VARCHAR(50), dept VARCHAR(10), marks DECIMAL(5,2));
INSERT INTO student VALUES (1,'Ram','BCA',78.5),(2,'Sita','BCA',91),(3,'Hari','BIT',64.25),(4,'Gita','BIT',85.5),(5,'Bikash','BCA',55);
SELECT dept, COUNT(*) AS n, ROUND(AVG(marks), 2) AS avg_marks
FROM student WHERE marks >= 60
GROUP BY dept HAVING AVG(marks) > 80;

+------+---+-----------+
| dept | n | avg_marks |
+------+---+-----------+
| BCA  | 2 |     84.75 |
+------+---+-----------+

Real result: five rows enter, four pass WHERE, two groups are made (BCA 84.75 and BIT 74.88), and only BCA passes HAVING. Bikash with 55 never reached the grouping, and this is exactly why a condition on a plain column is cheaper in WHERE than in HAVING.

Q3. What is a join? Explain INNER JOIN and LEFT JOIN with SQL examples, and mention the other kinds. (10 marks)

Introduction. Data in a relational database is split into several tables to avoid repetition: students in one table and departments in another, linked by a foreign key.

A join combines the rows of two tables on a condition, normally foreign key = primary key, so that a query can show a student's name together with the head of the department.

Kinds of join.

1. INNER JOIN returns only pairs of rows that match the condition. Rows without a partner are dropped.
2. LEFT JOIN returns every row of the left table and the matching rows of the right table; where there is no match, the right columns are NULL.
3. RIGHT JOIN is the mirror of the left join and keeps all rows of the right table.
4. FULL OUTER JOIN keeps unmatched rows of both sides; MySQL has no direct form and uses UNION of a left and a right join.
5. CROSS JOIN pairs every row with every row (the Cartesian product); it is rarely wanted and grows quickly.
6. Self join joins a table to itself, for example an employee with their manager.

CREATE TABLE dept (code VARCHAR(10) PRIMARY KEY, head VARCHAR(40));
CREATE TABLE student (roll INT PRIMARY KEY, name VARCHAR(50), dept VARCHAR(10));
INSERT INTO dept VALUES ('BCA','Dr. Shah'),('BIT','Dr. Rai'),('BBA','Dr. Thapa');
INSERT INTO student VALUES (1,'Ram','BCA'),(2,'Sita','BCA'),(3,'Hari','BIT'),(4,'Gita','BIT'),(5,'Bikash','BCA');
SELECT s.name, d.head FROM student s INNER JOIN dept d ON s.dept = d.code ORDER BY s.roll;
SELECT d.code, COUNT(s.roll) AS students FROM dept d LEFT JOIN student s ON s.dept = d.code GROUP BY d.code ORDER BY d.code;
SELECT d.code FROM dept d LEFT JOIN student s ON s.dept = d.code WHERE s.roll IS NULL;

+--------+----------+
| name   | head     |
+--------+----------+
| Ram    | Dr. Shah |
| Sita   | Dr. Shah |
| Hari   | Dr. Rai  |
| Gita   | Dr. Rai  |
| Bikash | Dr. Shah |
+--------+----------+
+------+----------+
| code | students |
+------+----------+
| BBA  |        0 |
| BCA  |        3 |
| BIT  |        2 |
+------+----------+
+------+
| code |
+------+
| BBA  |
+------+

Reading the result (Fig. 3.2). The inner join gives five rows and no BBA. The left join with COUNT(s.roll) keeps BBA with a count of 0, because COUNT of a column ignores NULL.

The last query finds the departments with no students by keeping the rows where the right side is NULL.

Conclusion. Joins let a database keep data in small, clean tables and still answer questions that need several of them. Use INNER JOIN when only matches matter and LEFT JOIN when every row of the main table must appear.

Q4. Explain the aggregate functions of MySQL with a GROUP BY query. (5 marks)

An aggregate function takes many rows and returns one value. They are normally used with GROUP BY, which makes one group per distinct value of a column.

1. COUNT(*) counts rows; COUNT(col) counts rows where col is not NULL.
2. SUM(col) adds the values; AVG(col) gives the mean.
3. MIN(col) and MAX(col) give the smallest and the largest value.
4. GROUP BY dept makes one result row for each department; every column in SELECT must be either grouped or aggregated.
5. NULL values are ignored by SUM, AVG, MIN, MAX and COUNT(col).

CREATE TABLE student (roll INT PRIMARY KEY, name VARCHAR(50), dept VARCHAR(10), marks DECIMAL(5,2), email VARCHAR(80));
INSERT INTO student VALUES (1,'Ram','BCA',78.5,'ram@tu.edu.np'),(2,'Sita','BCA',91,'sita@tu.edu.np'),(3,'Hari','BIT',64.25,NULL),(4,'Gita','BIT',85.5,'gita@tu.edu.np'),(5,'Bikash','BCA',55,'bikash@tu.edu.np');
SELECT dept, COUNT(*) AS n, COUNT(email) AS with_email, MAX(marks) AS best, ROUND(AVG(marks),2) AS average
FROM student GROUP BY dept ORDER BY dept;

+------+---+------------+-------+---------+
| dept | n | with_email | best  | average |
+------+---+------------+-------+---------+
| BCA  | 3 |          3 | 91.00 |   74.83 |
| BIT  | 2 |          1 | 85.50 |   74.88 |
+------+---+------------+-------+---------+

Note that BIT has two students but COUNT(email) gives 1, because Hari has no e-mail and NULL is skipped; this difference between COUNT(*) and COUNT(col) is a favourite examination trap.

The average of BCA is 74.83 and the best mark is 91.00.

In a PHP report page the same query would be run once, and the rows would be printed in an HTML table, so that the teacher sees the summary of every department without writing any loop over the students.

Aggregates are also the basis of dashboards such as the number of admissions per programme.

Q5. Why must DECIMAL be used for money in MySQL? What happens with FLOAT? (5 marks)

FLOAT and DOUBLE store numbers in binary with a limited number of bits, so many decimal fractions, such as 0.1, cannot be stored exactly. DECIMAL(p,s) stores digits exactly, as decimal numbers.

1. The test. Ten rows of 0.1 were added in a FLOAT column and in a DECIMAL(10,2) column (Fig. 3.3).
2. FLOAT result. The total is 1.0000000149011612 and SUM = 1 is false.
3. DECIMAL result. The total is exactly 1.00 and SUM = 1 is true.
4. Why it matters. A fee or payroll system adds thousands of amounts; tiny errors add up, and a report that is one paisa out is rejected by an auditor.
5. Rules. Use DECIMAL(10,2) for rupees and paisa, keep the scale fixed, and in PHP pass money as strings or integers of paisa and not as float.

CREATE TABLE m (id INT AUTO_INCREMENT PRIMARY KEY, f FLOAT, d DECIMAL(10,2));
INSERT INTO m (f, d) VALUES (0.1,0.10),(0.1,0.10),(0.1,0.10),(0.1,0.10),(0.1,0.10),(0.1,0.10),(0.1,0.10),(0.1,0.10),(0.1,0.10),(0.1,0.10);
SELECT SUM(f) AS float_sum, SUM(d) AS decimal_sum, SUM(f) = 1 AS float_is_1, SUM(d) = 1 AS decimal_is_1 FROM m;

+--------------------+-------------+------------+--------------+
| float_sum          | decimal_sum | float_is_1 | decimal_is_1 |
+--------------------+-------------+------------+--------------+
| 1.0000000149011612 |        1.00 |          0 |            1 |
+--------------------+-------------+------------+--------------+

The rule is simple: if a number is a quantity of rupees, it is a DECIMAL. A wallet or fee system that stores a balance as a float may show 99.99999999 instead of 100 after many operations, and a customer will notice it at once.