Introduction

The Purpose of This Chapter

In Class XI, you learned the fundamentals of databases — how to create them in MySQL, populate them with data, and retrieve that data using SQL queries. This chapter builds directly on that foundation. You will now learn more advanced SQL commands that let you perform richer queries: using single-row and multiple-row functions, sorting records in ascending or descending order, grouping records based on criteria, and working with multiple tables in a single query.

What You Will Learn in This Chapter

The chapter is organised around these major topics:

• Functions in SQL — both single-row functions (which work on one row at a time) and multiple-row functions (which work on groups of rows).
• Group By in SQL — how to group records based on a criterion and apply functions to each group.
• Operations on Relations — working with two or more tables in a query, including joins and set operations.
• Using Two Relations in a Query — practical examples of combining data from multiple tables.

The CARSHOWROOM Database

To illustrate these ideas, the chapter introduces a database called CARSHOWROOM, with the schema shown in Figure 1.1. It has four relations:

• INVENTORY — Stores details of each car in the showroom's inventory: Car ID, Car Name, Price, Model, Year of Manufacture, and Fuel Type.
• CUSTOMER — Stores customer information: Customer ID, Customer Name, Customer Address, Phone number, and Email.
• SALE — Records each sale transaction: Invoice Number, Car ID, Customer ID, Sale Date, Payment Mode, Employee ID of the salesperson, and Selling Price.
• EMPLOYEE — Stores employee details: Employee ID, Employee Name, Date of Birth, Date of Joining, Designation, and Salary.

The records of these four relations are shown in Tables 1.1, 1.2, 1.3 and 1.4 respectively — the exact sample data used in every worked example and exercise in this chapter. Make sure you are comfortable with the structure of these four tables before moving on.