Recap and the Sample Tables Used in This Chapter

In the first-year Information Technology course you learned the foundations of a Relational Database Management System (RDBMS) and the basics of MySQL — how data is stored in tables (relations) made of rows (tuples) and columns (attributes), how to create a database and tables with DDL, how to insert, update, delete and read data with DML, and how to filter rows with a WHERE clause. This second-year chapter takes those foundations further. It shows how MySQL keeps data safe during a group of changes using transactions (COMMIT and ROLLBACK), how missing values (NULL) are handled, how to summarise data using aggregate functions and GROUP BY, how MySQL's built-in string, mathematical and date-time functions work, and finally how data from more than one table can be brought together using the cartesian product, equi-join and UNION.

MySQL is a popular, open-source RDBMS (free to download and use), operated with SQL (Structured Query Language). As before, SQL keywords are written in capitals by convention and every statement ends with a semicolon (;). Because Odisha's +2 Information Technology syllabus draws on the same standard, well-established database principles used throughout computing, everything you learn here applies directly to any relational database system you meet later.

Throughout the chapter we will work with two small sample tables so that every example refers to real data you can picture. The first is an Employee table:

Here EmpNo is the primary key, Salary is a DECIMAL money value (note that employee 106's salary is NULL — not yet fixed), and JoinDate is a DATE. The second table, Dept, lists each department and the city it operates from:

The two tables are related: the Dept column of Employee matches the DeptName column of Dept. That shared value is what will let us join the two tables later in the chapter. For reference, the Employee table could be created and filled like this:

CREATE TABLE Employee (
    EmpNo INT PRIMARY KEY,
    Name VARCHAR(50),
    Dept VARCHAR(20),
    Salary DECIMAL(10, 2),
    JoinDate DATE
);

With these two tables in mind, we can now study each new topic in turn.