Informatics Practices: Class 12 — Chapter 1: Querying and SQL Functions

The CARSHOWROOM database consists of the following four relations:

• INVENTORY: Stores name, price, model, year of manufacturing, and fuel type for each car in the showroom.
• CUSTOMER: Stores customer ID, name, address, phone number, and email.
• SALE: Stores invoice number, car ID, customer ID, sale date, mode of payment, sales person's employee ID, and selling price.
• EMPLOYEE: Stores employee ID, name, date of birth, date of joining, designation, and salary.

Table 1.1: INVENTORY

Table 1.2: CUSTOMER

Table 1.3: SALE

Table 1.4: EMPLOYEE

Single row functions operate on a single value from a single row and return a single value as output. They are categorized into Numeric, String, and Date & Time functions.

(A) Numeric Functions Reference Matrix

(B) String Functions Reference Matrix

(C) Date and Time Functions Reference Matrix

Aggregate functions work on a set of records as a whole and return a single summarized value for each column on which they are applied.

Table 1.8: Differences between Single Row and Multiple Row Functions

Aggregate Functions Reference Matrix

The HAVING clause is used to apply filter conditions on grouped records. Unlike the WHERE clause, which filters rows before grouping, HAVING filters groups after GROUP BY has been applied.

  
    mysql> SELECT CustID, COUNT(*)
    FROM SALE
    GROUP BY CustID
    HAVING COUNT(*) > 1;
  

• UNION (U): Combines the selected rows of two tables, eliminating duplicate rows.
• INTERSECT (∩): Returns only the common tuples present in both tables.
• MINUS (-): Returns tuples present in the first table but absent in the second table.
• Cartesian Product (X): Combines each row of the first table with every row of the second table. The degree of the resulting relation is the sum of the degrees of both tables ($Degree = D_1 + D_2$), and the cardinality is the product of their cardinalities ($Cardinality = C_1 \times C_2$).

When multiple tables are specified in the FROM clause separated by commas without a join condition, a Cartesian product is performed.

  
  mysql> SELECT * FROM DANCE, MUSIC;

JOIN Operations:

• Equi-Join: Joins tables based on an equality condition between common columns using the WHERE clause.
• Explicit JOIN Clause: Uses the JOIN keyword along with the ON clause to specify join conditions.
• NATURAL JOIN: An automated join that matches columns with identical names in both tables and automatically eliminates duplicate columns from the output.

  
  mysql> SELECT * FROM UNIFORM NATURAL JOIN COST;