GTU MCA RDBMS Set 7 Solution: CUSTOMER and ORDER Queries, Trigger, Function and Procedure in Oracle

Official Set 7

Set 7 — CUSTOMER and ORDER (GROUP BY/HAVING, Trigger, Function, Procedure)

• GROUP BY
• HAVING
• Reserved word ORDER
• AFTER UPDATE OF trigger
• :OLD / :NEW
• Function
• Procedure
• TO_CHAR month

Subject

MC01094031 RDBMS

Tool

Oracle 10g or above

Questions

6

Status

Question list: official GTU PDF

Source check: The six tasks and both table structures are copied from the official GTU PDF. Sample data is illustrative. Task 3's *average orders* is read as the average order amount per customer (the alternative, average number of orders, makes no sense with rupee limits). Task 4 does not say what the trigger should do, so this solution logs the old and new country in an audit table and prints a message.

On this page

1. Introduction
2. Learning objectives
3. Original question list
4. Database schema
5. Prerequisites
6. Table creation & sample data
7. Solutions
8. Important concepts
9. Common mistakes
10. Viva questions
11. Summary
12. Download script
13. Related practicals

Introduction

Set 7 mixes reporting queries with three kinds of stored program. It also raises a practical design problem: the second table is called ORDER, which is a reserved word in Oracle SQL.

Learning objectives

• Filter groups with HAVING and sort by an aggregate.
• Handle a reserved-word table name safely.
• Write an AFTER UPDATE OF column trigger using :OLD and :NEW.
• Return a text value from a function.
• Use TO_CHAR to extract month names.

Original question list

1. List the number of customers in each country. Only include countries with more than 100 customers.
2. List the number of customers in each country, except China, sorted high to low. Only include countries with 5 or more customers.
3. List all customers with average orders between Rs.5000 and Rs.6500.
4. Create a trigger that executes whenever country is updated in CUSTOMER table.
5. Create a function to return customer with maximum orders.
6. Create a procedure to display month names of dates of ORDER table. The month names should be unique.

Database schema

CUSTOMER

Column	Oracle type	Meaning / constraint
cid	NUMBER(5)	Primary key
fname	VARCHAR2(20)
lname	VARCHAR2(20)
city	VARCHAR2(20)
country	VARCHAR2(20)
phone	VARCHAR2(15)

CUSTOMER_ORDER (source name: ORDER)

Column	Oracle type	Meaning / constraint
oid	NUMBER(5)	Primary key
odate	DATE	Order date
onumber	VARCHAR2(10)	Order number, UNIQUE
cid	NUMBER(5)	FK → CUSTOMER
ototalamount	NUMBER(10,2)	Order total

Oracle note: ORDER is a reserved word (it is part of ORDER BY), so CREATE TABLE order (...) fails with *ORA-00903: invalid table name*. Two options exist: quote it — CREATE TABLE "ORDER" (...) — and then write "ORDER" (in capitals, with double quotes) in every statement, or choose a different name. This solution uses the name CUSTOMER_ORDER, which avoids errors everywhere. Column names oDate/oNumber/oTotalAmount are written without quotes, so Oracle stores them as ODATE, ONUMBER, OTOTALAMOUNT.

Prerequisites

• Sets 3 (GROUP BY/HAVING) and 4 (functions, procedures).
• SET SERVEROUTPUT ON for the PL/SQL tasks.

Optional clean-up — run this first if you have attempted the practical before. Errors such as *ORA-00942: table or view does not exist* are harmless on the first run.

Optional clean-up

DROP TABLE customer_country_log PURGE;
DROP TABLE customer_order CASCADE CONSTRAINTS;
DROP TABLE customer CASCADE CONSTRAINTS;

Table creation & sample data

Step 1 Create the CUSTOMER and CUSTOMER_ORDER tables

Oracle SQL

CREATE TABLE customer (
    cid      NUMBER(5)    PRIMARY KEY,
    fname    VARCHAR2(20) NOT NULL,
    lname    VARCHAR2(20),
    city     VARCHAR2(20),
    country  VARCHAR2(20),
    phone    VARCHAR2(15)
);

-- ORDER is reserved, so the table is named CUSTOMER_ORDER
CREATE TABLE customer_order (
    oid           NUMBER(5)    PRIMARY KEY,
    odate         DATE         NOT NULL,
    onumber       VARCHAR2(10) UNIQUE,
    cid           NUMBER(5)    REFERENCES customer (cid),
    ototalamount  NUMBER(10,2) CHECK (ototalamount >= 0)
);

Expected Output

Table created.  (2 times)

Step 2 Insert sample customers and orders

Oracle SQL

INSERT INTO customer VALUES (1, 'John', 'Miller', 'Seattle', 'USA', '206-555-0101');
INSERT INTO customer VALUES (2, 'Mary', 'Jones', 'Boston', 'USA', '617-555-0102');
INSERT INTO customer VALUES (3, 'Robert', 'Brown', 'Chicago', 'USA', '312-555-0103');
INSERT INTO customer VALUES (4, 'Hans', 'Muller', 'Berlin', 'Germany', '030-5550104');
INSERT INTO customer VALUES (5, 'Anna', 'Schmidt', 'Munich', 'Germany', '089-5550105');
INSERT INTO customer VALUES (6, 'Klaus', 'Weber', 'Hamburg', 'Germany', '040-5550106');
INSERT INTO customer VALUES (7, 'Petra', 'Fischer', 'Cologne', 'Germany', '0221-555010');
INSERT INTO customer VALUES (8, 'Lukas', 'Becker', 'Berlin', 'Germany', '030-5550108');
INSERT INTO customer VALUES (9, 'Eva', 'Wagner', 'Frankfurt', 'Germany', '069-5550109');
INSERT INTO customer VALUES (10, 'James', 'Smith', 'London', 'UK', '020-5550110');
INSERT INTO customer VALUES (11, 'Emma', 'Taylor', 'Leeds', 'UK', '0113-555011');
INSERT INTO customer VALUES (12, 'Oliver', 'Wilson', 'Bristol', 'UK', '0117-555012');
INSERT INTO customer VALUES (13, 'Sophie', 'Evans', 'London', 'UK', '020-5550113');
INSERT INTO customer VALUES (14, 'Harry', 'Clark', 'York', 'UK', '01904-55501');
INSERT INTO customer VALUES (15, 'Amit', 'Shah', 'Ahmedabad', 'India', '079-5550115');
INSERT INTO customer VALUES (16, 'Riya', 'Patel', 'Surat', 'India', '0261-555011');
INSERT INTO customer VALUES (17, 'Vikram', 'Rao', 'Pune', 'India', '020-5550117');
INSERT INTO customer VALUES (18, 'Neha', 'Gupta', 'Delhi', 'India', '011-5550118');
INSERT INTO customer VALUES (19, 'Arjun', 'Nair', 'Kochi', 'India', '0484-555011');
INSERT INTO customer VALUES (20, 'Li', 'Wei', 'Beijing', 'China', '010-5550120');
INSERT INTO customer VALUES (21, 'Wang', 'Fang', 'Shanghai', 'China', '021-5550121');
INSERT INTO customer VALUES (22, 'Zhang', 'Min', 'Shenzhen', 'China', '0755-555012');
INSERT INTO customer VALUES (23, 'Liu', 'Yang', 'Beijing', 'China', '010-5550123');
INSERT INTO customer VALUES (24, 'Chen', 'Jie', 'Guangzhou', 'China', '020-5550124');
INSERT INTO customer VALUES (25, 'Zhao', 'Lei', 'Wuhan', 'China', '027-5550125');
INSERT INTO customer VALUES (26, 'Sun', 'Hui', 'Shanghai', 'China', '021-5550126');
INSERT INTO customer VALUES (27, 'Pierre', 'Martin', 'Paris', 'France', '01-55501270');
INSERT INTO customer VALUES (28, 'Claire', 'Bernard', 'Lyon', 'France', '04-55501280');
INSERT INTO customer VALUES (29, 'Louis', 'Dubois', 'Nice', 'France', '04-55501290');
INSERT INTO customer VALUES (30, 'Julie', 'Moreau', 'Paris', 'France', '01-55501300');
INSERT INTO customer VALUES (31, 'Carlos', 'Garcia', 'Mexico City', 'Mexico', '55-55501310');
INSERT INTO customer VALUES (32, 'Sofia', 'Lopez', 'Monterrey', 'Mexico', '81-55501320');

INSERT INTO customer_order VALUES (1, TO_DATE('05-01-2025','DD-MM-YYYY'), 'ORD-1001', 1, 5200);
INSERT INTO customer_order VALUES (2, TO_DATE('18-01-2025','DD-MM-YYYY'), 'ORD-1002', 1, 6100);
INSERT INTO customer_order VALUES (3, TO_DATE('03-02-2025','DD-MM-YYYY'), 'ORD-1003', 4, 3200);
INSERT INTO customer_order VALUES (4, TO_DATE('14-02-2025','DD-MM-YYYY'), 'ORD-1004', 4, 2800);
INSERT INTO customer_order VALUES (5, TO_DATE('27-02-2025','DD-MM-YYYY'), 'ORD-1005', 10, 6400);
INSERT INTO customer_order VALUES (6, TO_DATE('09-03-2025','DD-MM-YYYY'), 'ORD-1006', 15, 5000);
INSERT INTO customer_order VALUES (7, TO_DATE('21-03-2025','DD-MM-YYYY'), 'ORD-1007', 15, 7000);
INSERT INTO customer_order VALUES (8, TO_DATE('21-03-2025','DD-MM-YYYY'), 'ORD-1008', 15, 5900);
INSERT INTO customer_order VALUES (9, TO_DATE('11-04-2025','DD-MM-YYYY'), 'ORD-1009', 20, 9000);
INSERT INTO customer_order VALUES (10, TO_DATE('02-05-2025','DD-MM-YYYY'), 'ORD-1010', 27, 4500);
INSERT INTO customer_order VALUES (11, TO_DATE('15-05-2025','DD-MM-YYYY'), 'ORD-1011', 27, 4700);
INSERT INTO customer_order VALUES (12, TO_DATE('06-06-2025','DD-MM-YYYY'), 'ORD-1012', 31, 8000);
INSERT INTO customer_order VALUES (13, TO_DATE('06-06-2025','DD-MM-YYYY'), 'ORD-1013', 15, 5100);
INSERT INTO customer_order VALUES (14, TO_DATE('19-01-2025','DD-MM-YYYY'), 'ORD-1014', 10, 6600);

COMMIT;

Explanation

32 customers in 7 countries and 14 orders.

Expected Output

1 row created.  (46 times)

Commit complete.

Step 3 Generate 110 extra USA customers (needed for Question 1)

Oracle PL/SQL

-- Extra rows so that Question 1 (more than 100 customers) returns a result:
-- 110 additional customers in the USA, created with a PL/SQL loop.
BEGIN
    FOR i IN 1 .. 110 LOOP
        INSERT INTO customer (cid, fname, lname, city, country, phone)
        VALUES (1000 + i, 'Cust' || i, 'Demo', 'New York', 'USA', NULL);
    END LOOP;
    COMMIT;
END;
/

Explanation

A FOR loop inserts the rows quickly; 'Cust' || i builds names Cust1 … Cust110. Without these rows no country has more than 100 customers and Question 1 correctly returns *no rows selected*.

Expected Output (illustrative, for this article's sample data)

PL/SQL procedure successfully completed.

Solutions

Q1 List the number of customers in each country. Only include countries with more than 100 customers.

Oracle SQL

SELECT country, COUNT(*) AS customers
FROM   customer
GROUP  BY country
HAVING COUNT(*) > 100;

Explanation

GROUP BY country makes one row per country; HAVING COUNT(*) > 100 keeps only large groups. With the 110 generated rows the USA has 113 customers.

Expected Output (computed from this article's sample data)

COUNTRY	CUSTOMERS
USA	113

1 row selected.

Q2 List the number of customers in each country, except China, sorted high to low. Only include countries with 5 or more customers.

Oracle SQL

SELECT country, COUNT(*) AS customers
FROM   customer
WHERE  country <> 'China'
GROUP  BY country
HAVING COUNT(*) >= 5
ORDER  BY COUNT(*) DESC, country;

Explanation

WHERE removes Chinese customers before grouping (cheaper than filtering groups afterwards); HAVING then removes countries with fewer than 5 customers. China would qualify with 7 customers, which shows the WHERE is doing its job. country is a second sort key for ties.

Expected Output (computed from this article's sample data)

COUNTRY	CUSTOMERS
USA	113
Germany	6
India	5
UK	5

4 rows selected.

Q3 List all customers with average orders between Rs.5000 and Rs.6500.

Oracle SQL

SELECT c.cid, c.fname || ' ' || c.lname AS customer_name,
       ROUND(AVG(o.ototalamount), 2) AS avg_amount
FROM   customer c
JOIN   customer_order o ON o.cid = c.cid
GROUP  BY c.cid, c.fname, c.lname
HAVING AVG(o.ototalamount) BETWEEN 5000 AND 6500
ORDER  BY c.cid;

Explanation

The average is calculated per customer, then filtered with HAVING. Customer 15 (Amit Shah) has four orders averaging 5750.

Expected Output (computed from this article's sample data)

CID	CUSTOMER_NAME	AVG_AMOUNT
1	John Miller	5650
10	James Smith	6500
15	Amit Shah	5750

3 rows selected.

Q4 Create a trigger that executes whenever country is updated in CUSTOMER table.

Oracle PL/SQL

CREATE TABLE customer_country_log (
    cid          NUMBER(5),
    old_country  VARCHAR2(20),
    new_country  VARCHAR2(20),
    changed_by   VARCHAR2(30),
    changed_on   DATE
);

CREATE OR REPLACE TRIGGER trg_customer_country
AFTER UPDATE OF country ON customer
FOR EACH ROW
WHEN (NVL(OLD.country, '~') <> NVL(NEW.country, '~'))
BEGIN
    INSERT INTO customer_country_log
    VALUES (:OLD.cid, :OLD.country, :NEW.country, USER, SYSDATE);

    DBMS_OUTPUT.PUT_LINE('Country of customer ' || :OLD.cid || ' changed from '
                         || :OLD.country || ' to ' || :NEW.country);
END;
/

-- test
UPDATE customer SET country = 'Canada' WHERE cid = 2;
SELECT cid, old_country, new_country FROM customer_country_log;
ROLLBACK;

Explanation

AFTER UPDATE OF country fires only when the COUNTRY column appears in an UPDATE's SET clause. FOR EACH ROW makes it fire once per changed row and gives access to :OLD (value before) and :NEW (value after).

The WHEN clause skips updates that set the same country again. Inside WHEN the bind prefix colon is not written (OLD.country), while in the body it is (:OLD.country). NVL handles NULL countries. USER records who made the change.

Expected Output (computed from this article's sample data)

Table created.

Trigger created.

SQL> UPDATE customer SET country = 'Canada' WHERE cid = 2;
Country of customer 2 changed from USA to Canada

1 row updated.

SQL> SELECT cid, old_country, new_country FROM customer_country_log;

       CID OLD_COUNTRY     NEW_COUNTRY
---------- --------------- ---------------
         2 USA             Canada

SQL> ROLLBACK;   -- undo the test (the log row is also rolled back)

Q5 Create a function to return customer with maximum orders.

Oracle PL/SQL

CREATE OR REPLACE FUNCTION fn_top_customer
RETURN VARCHAR2
IS
    v_result VARCHAR2(100);
BEGIN
    SELECT name_and_count
    INTO   v_result
    FROM  (SELECT c.fname || ' ' || c.lname || ' (ID ' || c.cid || ') - '
                  || COUNT(*) || ' orders' AS name_and_count
           FROM   customer c
           JOIN   customer_order o ON o.cid = c.cid
           GROUP  BY c.cid, c.fname, c.lname
           ORDER  BY COUNT(*) DESC, c.cid)
    WHERE  ROWNUM = 1;

    RETURN v_result;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN 'No orders found';
END fn_top_customer;
/

SELECT fn_top_customer AS top_customer FROM dual;

Explanation

The inline view counts orders per customer and sorts by count; ROWNUM = 1 takes the first row. If two customers tie, the lower customer ID is returned (second sort key). The function returns text, so its return type is VARCHAR2.

Expected Output (computed from this article's sample data)

Function created.

SQL> SELECT fn_top_customer AS top_customer FROM dual;

TOP_CUSTOMER
----------------------------------------
Amit Shah (ID 15) - 4 orders

Q6 Create a procedure to display month names of dates of ORDER table. The month names should be unique.

Oracle PL/SQL

CREATE OR REPLACE PROCEDURE p_order_months
IS
    v_no NUMBER := 0;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Months in which orders were placed:');
    FOR r IN (SELECT DISTINCT TO_CHAR(odate, 'MM')                AS mm,
                              TRIM(TO_CHAR(odate, 'Month'))       AS month_name
              FROM   customer_order
              ORDER  BY mm) LOOP
        v_no := v_no + 1;
        DBMS_OUTPUT.PUT_LINE(v_no || '. ' || r.month_name);
    END LOOP;
END p_order_months;
/

EXEC p_order_months

Explanation

TO_CHAR(odate, 'Month') returns the full month name, padded with spaces to the longest name (9 characters), so TRIM removes the padding. Selecting 'MM' as well lets the list be sorted in calendar order instead of alphabetical order.

DISTINCT returns each month once, even though several orders fall in the same month. Orders from different years in the same month would also be merged; add 'YYYY' to the query to keep them separate.

Expected Output (computed from this article's sample data)

Procedure created.

SQL> EXEC p_order_months
Months in which orders were placed:
1. January
2. February
3. March
4. April
5. May
6. June

PL/SQL procedure successfully completed.

Important concepts used

WHERE vs HAVING

WHERE filters rows before grouping; HAVING filters groups after aggregation.

Reserved words

Words such as ORDER, TABLE, DATE cannot be used as unquoted identifiers. Quoting makes them case-sensitive and must be repeated everywhere.

Row trigger with WHEN

WHEN (condition) restricts when the trigger body runs; OLD/NEW are written without colons inside it.

Top-N in a function

Sort in an inline view, filter with ROWNUM = 1, return with SELECT INTO.

Month names

TO_CHAR(date, 'Month') / 'MON' / 'MM'.

Common mistakes

• CREATE TABLE order → ORA-00903.
• Creating "Order" with quotes and later typing "ORDER" — quoted names are case-sensitive.
• Filtering China in HAVING (HAVING country <> 'China') — works but is less efficient and less clear.
• Writing :OLD.country inside the WHEN clause — compile error.
• Comparing TO_CHAR(odate,'Month') = 'January' without TRIM.

Viva questions

Why can't a table be named ORDER?

ORDER is a reserved SQL keyword (ORDER BY). It can only be used if enclosed in double quotes, which makes the name case-sensitive and inconvenient, so another name is preferred.

What are :OLD and :NEW?

Pseudo-records available in row-level triggers. :OLD holds column values before the change, :NEW the values after it. INSERT has only :NEW, DELETE only :OLD.

What does UPDATE OF column in a trigger mean?

The trigger fires only if that column is listed in the UPDATE statement's SET clause.

Can a function return a string?

Yes. A function can return any SQL or PL/SQL type, e.g. VARCHAR2, NUMBER, DATE or a record.

Why is TRIM needed with 'Month'?

The 'Month' format pads names to the length of the longest month name (9 characters).

Summary

Set 7 practised group filtering, a safe workaround for the reserved table name ORDER, an audit trigger with :OLD/:NEW and a WHEN clause, a top-N function and a procedure that formats dates as month names.

Download script

set-07-customer-and-order.sql

All CREATE, INSERT and solution statements of Set 7 in one file — run it in SQL*Plus with @set-07-customer-and-order.sql.

Related practicals

• Set 6 — Employee and Department PL/SQL
• Set 8 — Employee Salary Slip
• All 10 practical sets
• Viva questions

← Previous practicalSet 6: Employee and Department PL/SQLNext practical →Set 8: Employee Salary Slip

Independent educational resource — not an official GTU publication. Verify questions and solutions with your current syllabus and lab instructor. Disclaimer