GTU MCA RDBMS Set 3 Solution: Salesmen Customers Orders SQL Queries in Oracle (46 Questions)

Official Set 3

Set 3 — SALESMEN, CUSTOMERS and ORDERS (46 SQL Queries: Joins, Subqueries, Set Operators)

• Substitution variables
• DISTINCT
• GROUP BY
• Self join
• Correlated subquery
• ANY / ALL
• UNION / UNION ALL
• UPDATE / DELETE

Subject

MC01094031 RDBMS

Tool

Oracle 10g or above

Questions

46

Status

Question list: official GTU PDF

Source check: All 46 questions are copied from the official GTU PDF in its numbering. The PDF does not give table data, so the rows below are sample data built around the examples the PDF uses — Surat, London, Rome, Rajkot and Anand, the salesmen Sejal (1002) and Piyush, salesmen 1002 and 1007, the customer Chirag, and orders on 3, 4 and 6 October 2023. Where the PDF says "e.g.", the example value is used in the WHERE clause; change it to test other values.

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 3 is the largest practical. Three related tables — salesmen, the customers they look after, and the orders those customers place — let you practise almost every SELECT feature in the syllabus: filtering, sorting, aggregates, grouping, joins, subqueries, ANY/ALL, set operators, and finally DELETE and UPDATE.

Learning objectives

• Load data interactively using SQL*Plus substitution variables (&).
• Summarise data with COUNT, SUM, AVG, MAX, GROUP BY.
• Write equi and self joins and compare join and subquery solutions.
• Write simple and correlated subqueries with IN, ANY, ALL and EXISTS.
• Combine results with UNION and UNION ALL.

Original question list

The data-changing questions (44–46) come last, so they do not change the answers to the earlier queries.

1. Write an Insert script for insertion of rows with substitution variables and insert appropriate data.
2. Produce the order no, amount and date of all orders.
3. Give all the information about all the customers with a specific salesman number.
4. Display the following information in the order of city, sname, snum and commission.
5. List of rating followed by the name of each customer in particular one city e.g. Surat.
6. List of snum of all salesmen with orders in order table without any duplicates.
7. List of all orders for more than certain amount e.g. more than Rs. 1000.
8. List of names and cities of all salesmen in one city e.g. London with commission above 10%.
9. List all customers whose names begins with a letter 'C'.
10. List all customers whose names begins with letter 'A' to'G'.
11. List all orders with zero or NULL amount.
12. Find out the largest orders of salesman from two value e.g. 1002 and 1007.
13. Count all orders of particular date e.g. October 3, 2023
14. Calculate the total amount ordered.
15. Calculate the average amount ordered.
16. Count the no. of salesmen currently having orders.
17. List all salesmen with their % of commission.
18. Assume each salesperson has a 15% commission. Write a query on the order table that will produce the order number, salesman no and the amount of commission for that order
19. Find the highest rating in each city in the form : For the city (city), the highest rating is : (rating)
20. List all in descending order of rating.
21. Calculate the total of orders for each day and place the result in descending order.
22. Show the name of all customers with their salesman's name.
23. List all customers and salesmen who shared a same city.
24. List all orders with the names of their customer and salesman.
25. List all orders by the customers not located in the same city as their salesman.
26. List all customers serviced by salespeople with commission above 15%.
27. Calculate the amount of the salesman commission on each order by a customer with rating above 100.
28. Find all pairs of customers having the same rating without duplication.
29. List all orders that are greater than the average of October 4,2023.
30. Find the average commission of salesmen in London.
31. Find all orders attributed to salesmen in 'London' using both the subquery and join methods.
32. List the commission of all salesmen serving customers in 'London'.
33. Find all customers whose cnum is e.g. 1000 above than the snum of name e.g. Sejal.
34. Count the no. of customers with the rating above than the average of one city e.g. 'Surat'.
35. Find all salesmen with customers located in their cities using ANY and IN.
36. Find all salesmen for whom there are customers that follow them in alphabetical order.
37. Find all customers having rating greater than any customer in particular city e.g. 'Rajkot'.
38. List all orders that has amount greater than atleat one of the orders from 6th October, 2023.
39. Find all orders with amounts smaller than any amount for a customer in 'London'.
40. Find all the customers who have greater rating than every customer in one city e.g. 'Anand’
41. Create a union of two queries that shows the names, cities and ratings of all customers. Those with rating of >=200 should display 'HIGH RATING' and those with < 200 should display 'LOW RATING'.
42. Produce the name and number of each salesman and each customer with more than one current order in the alphabetical order of names.
43. Create union of three queries. First select snum of all salesman in Surat, second, the cnum of all customers in 'Surat' and third, the onum of all orders of 3rd Oct. Retain duplicates between the last two queries but remove the duplicates between either of them and the first.
44. Remove all orders from customer Chirag from the orders table.
45. Set the ratings of all the customers of Piyush to 400.
46. Increase the rating of all customers in Rome by 100.

Database schema

SALESMEN

Column	Oracle type	Meaning / constraint
snum	NUMBER(4)	A unique number assigned to each salesman (primary key)
sname	VARCHAR2(15)	The name of the salesman
city	VARCHAR2(15)	The location of the salesman
commission	NUMBER(4,2)	The salesman's commission on orders, as a fraction (.12 = 12%)

CUSTOMERS

Column	Oracle type	Meaning / constraint
cnum	NUMBER(4)	A unique number assigned to each customer (primary key)
cname	VARCHAR2(15)	The name of the customer
city	VARCHAR2(15)	The location of the customer
rating	NUMBER(3)	A level of preference indicator given to this customer
snum	NUMBER(4)	The number of the salesman assigned to this customer (FK → SALESMEN)

ORDERS

Column	Oracle type	Meaning / constraint
onum	NUMBER(4)	A unique number assigned to each order (primary key)
amount	NUMBER(9,2)	The amount of the order
odate	DATE	The date of the order
cnum	NUMBER(4)	The customer making the order (FK → CUSTOMERS)
snum	NUMBER(4)	The salesman credited with the sale (FK → SALESMEN)

Oracle note: Commission is stored as a fraction (.12 means 12%). The salesman credited with an order (ORDERS.SNUM) is not always the salesman assigned to the customer (CUSTOMERS.SNUM) — for example order 3001 of Bhavin is credited to Mehul. Questions 22–27 depend on this difference.

Prerequisites

• Sets 1 and 2 (WHERE, ORDER BY, joins).
• Create the tables in the order SALESMEN → CUSTOMERS → ORDERS because of the foreign keys.

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 orders    CASCADE CONSTRAINTS;
DROP TABLE customers CASCADE CONSTRAINTS;
DROP TABLE salesmen  CASCADE CONSTRAINTS;

Table creation & sample data

Create the three tables, then load the sample data. Question 1 shows how rows are entered interactively with substitution variables; this ready-made script loads the complete sample data in one go so that your results match the expected outputs.

Step 1 Create the SALESMEN, CUSTOMERS and ORDERS tables

Oracle SQL

CREATE TABLE salesmen (
    snum        NUMBER(4)     CONSTRAINT pk_salesmen PRIMARY KEY,
    sname       VARCHAR2(15)  NOT NULL,
    city        VARCHAR2(15),
    commission  NUMBER(4,2)   CONSTRAINT ck_sal_comm CHECK (commission BETWEEN 0 AND 1)
);

CREATE TABLE customers (
    cnum    NUMBER(4)     CONSTRAINT pk_customers PRIMARY KEY,
    cname   VARCHAR2(15)  NOT NULL,
    city    VARCHAR2(15),
    rating  NUMBER(3),
    snum    NUMBER(4)     CONSTRAINT fk_cust_sal REFERENCES salesmen (snum)
);

CREATE TABLE orders (
    onum    NUMBER(4)     CONSTRAINT pk_orders PRIMARY KEY,
    amount  NUMBER(9,2),
    odate   DATE,
    cnum    NUMBER(4)     CONSTRAINT fk_ord_cust REFERENCES customers (cnum),
    snum    NUMBER(4)     CONSTRAINT fk_ord_sal  REFERENCES salesmen (snum)
);

Explanation

Parent tables are created before the tables that reference them. The CHECK on commission keeps it between 0 and 1 because it is stored as a fraction.

Expected Output

Table created.  (3 times)

Step 2 Insert the sample data

Oracle SQL

INSERT INTO salesmen  VALUES (1001, 'Piyush', 'London', .12);
INSERT INTO salesmen  VALUES (1002, 'Sejal', 'Surat', .13);
INSERT INTO salesmen  VALUES (1003, 'Anil', 'Anand', .10);
INSERT INTO salesmen  VALUES (1004, 'Rajesh', 'London', .11);
INSERT INTO salesmen  VALUES (1005, 'Kiran', 'Ahmedabad', .26);
INSERT INTO salesmen  VALUES (1007, 'Mehul', 'Rajkot', .15);

INSERT INTO customers VALUES (2001, 'Hardik', 'London', 100, 1001);
INSERT INTO customers VALUES (2002, 'Gita', 'Rome', 200, 1003);
INSERT INTO customers VALUES (2003, 'Chirag', 'Surat', 200, 1002);
INSERT INTO customers VALUES (2004, 'Dhaval', 'Rajkot', 300, 1002);
INSERT INTO customers VALUES (2006, 'Ankita', 'London', 100, 1001);
INSERT INTO customers VALUES (2007, 'Pooja', 'Rome', 100, 1004);
INSERT INTO customers VALUES (2008, 'Bhavin', 'Surat', 300, 1007);
INSERT INTO customers VALUES (2009, 'Esha', 'Anand', 200, 1003);
INSERT INTO customers VALUES (2010, 'Farhan', 'Rajkot', 100, 1007);
INSERT INTO customers VALUES (2011, 'Jay', 'Ahmedabad', 200, 1005);

INSERT INTO orders    VALUES (3001, 18.69, TO_DATE('03-10-2023','DD-MM-YYYY'), 2008, 1007);
INSERT INTO orders    VALUES (3002, 1900.10, TO_DATE('03-10-2023','DD-MM-YYYY'), 2007, 1004);
INSERT INTO orders    VALUES (3003, 767.19, TO_DATE('03-10-2023','DD-MM-YYYY'), 2001, 1001);
INSERT INTO orders    VALUES (3005, 5160.45, TO_DATE('03-10-2023','DD-MM-YYYY'), 2003, 1002);
INSERT INTO orders    VALUES (3006, 1098.16, TO_DATE('03-10-2023','DD-MM-YYYY'), 2008, 1007);
INSERT INTO orders    VALUES (3007, 75.75, TO_DATE('04-10-2023','DD-MM-YYYY'), 2004, 1002);
INSERT INTO orders    VALUES (3009, 1713.23, TO_DATE('04-10-2023','DD-MM-YYYY'), 2002, 1003);
INSERT INTO orders    VALUES (3013, 1500.00, TO_DATE('04-10-2023','DD-MM-YYYY'), 2003, 1002);
INSERT INTO orders    VALUES (3008, 4723.00, TO_DATE('05-10-2023','DD-MM-YYYY'), 2006, 1001);
INSERT INTO orders    VALUES (3012, 3455.78, TO_DATE('05-10-2023','DD-MM-YYYY'), 2011, 1005);
INSERT INTO orders    VALUES (3010, 1309.95, TO_DATE('06-10-2023','DD-MM-YYYY'), 2004, 1002);
INSERT INTO orders    VALUES (3011, 9891.88, TO_DATE('06-10-2023','DD-MM-YYYY'), 2006, 1001);

COMMIT;

Explanation

Six salesmen, ten customers and twelve orders, all in October 2023.

Expected Output

1 row created.  (28 times)

Commit complete.

Solutions

Q1 Write an Insert script for insertion of rows with substitution variables and insert appropriate data.

SQL*Plus command

-- insert_salesmen.sql
INSERT INTO salesmen  VALUES (&snum, '&sname', '&city', &commission);

-- insert_customers.sql
INSERT INTO customers VALUES (&cnum, '&cname', '&city', &rating, &snum);

-- insert_orders.sql
INSERT INTO orders    VALUES (&onum, &amount, TO_DATE('&odate', 'DD-MM-YYYY'), &cnum, &snum);

-- after the first row, type / and press Enter to run the same INSERT again

Explanation

&name is a **SQL*Plus substitution variable**. Before the statement is sent to the database, SQL*Plus asks *Enter value for snum:* and replaces &snum with what you type. Text and date variables are written inside quotes ('&sname') so you do not have to type the quotes yourself.

Save each INSERT in a small script and run it with @insert_salesmen, or type / at the SQL> prompt to re-run the last statement for the next row. &&name asks only once per session, and SET VERIFY OFF hides the *old/new* lines.

Oracle note: Substitution variables are a client feature of SQL*Plus and SQL Developer (run as script, F5). They do not work in tools that send SQL straight to the database, such as some web consoles.

Expected Output

SQL> INSERT INTO salesmen VALUES (&snum, '&sname', '&city', &commission);
Enter value for snum: 1001
Enter value for sname: Piyush
Enter value for city: London
Enter value for commission: .12
old   1: INSERT INTO salesmen VALUES (&snum, '&sname', '&city', &commission)
new   1: INSERT INTO salesmen VALUES (1001, 'Piyush', 'London', .12)

1 row created.

SQL> /
Enter value for snum: 1002
...

Q2 Produce the order no, amount and date of all orders.

Oracle SQL

SELECT onum, amount, odate
FROM   orders
ORDER  BY onum;

Explanation

Only the listed columns are returned, in the listed order. Oracle shows dates in the session's default format (usually DD-MON-YY).

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

ONUM	AMOUNT	ODATE
3001	18.69	03-OCT-23
3002	1900.1	03-OCT-23
3003	767.19	03-OCT-23
3005	5160.45	03-OCT-23
3006	1098.16	03-OCT-23
3007	75.75	04-OCT-23
3008	4723	05-OCT-23
3009	1713.23	04-OCT-23
3010	1309.95	06-OCT-23
3011	9891.88	06-OCT-23
3012	3455.78	05-OCT-23
3013	1500	04-OCT-23

12 rows selected.

Q3 Give all the information about all the customers with a specific salesman number.

Oracle SQL

SELECT *
FROM   customers
WHERE  snum = &salesman_no;

Explanation

SQL*Plus asks for the salesman number. The output below is for 1001.

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

CNUM	CNAME	CITY	RATING	SNUM
2001	Hardik	London	100	1001
2006	Ankita	London	100	1001

2 rows selected.

Output after entering 1001 at the prompt.

Q4 Display the following information in the order of city, sname, snum and commission.

Oracle SQL

SELECT city, sname, snum, commission
FROM   salesmen
ORDER  BY city, sname;

Explanation

The column order of the result is decided by the SELECT list, not by the CREATE TABLE order. The rows are also sorted by city to make the output easy to read.

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

CITY	SNAME	SNUM	COMMISSION
Ahmedabad	Kiran	1005	.26
Anand	Anil	1003	.1
London	Piyush	1001	.12
London	Rajesh	1004	.11
Rajkot	Mehul	1007	.15
Surat	Sejal	1002	.13

6 rows selected.

Q5 List of rating followed by the name of each customer in particular one city e.g. Surat.

Oracle SQL

SELECT rating, cname
FROM   customers
WHERE  city = 'Surat';

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

RATING	CNAME
200	Chirag
300	Bhavin

2 rows selected.

Row order can differ in your Oracle session because this query has no ORDER BY.

Q6 List of snum of all salesmen with orders in order table without any duplicates.

Oracle SQL

SELECT DISTINCT snum
FROM   orders
ORDER  BY snum;

Explanation

DISTINCT removes duplicate rows. Without it, 1001, 1002 and 1007 would appear several times.

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

SNUM
1001
1002
1003
1004
1005
1007

6 rows selected.

Q7 List of all orders for more than certain amount e.g. more than Rs. 1000.

Oracle SQL

SELECT *
FROM   orders
WHERE  amount > 1000
ORDER  BY amount;

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

ONUM	AMOUNT	ODATE	CNUM	SNUM
3006	1098.16	03-OCT-23	2008	1007
3010	1309.95	06-OCT-23	2004	1002
3013	1500	04-OCT-23	2003	1002
3009	1713.23	04-OCT-23	2002	1003
3002	1900.1	03-OCT-23	2007	1004
3012	3455.78	05-OCT-23	2011	1005
3008	4723	05-OCT-23	2006	1001
3005	5160.45	03-OCT-23	2003	1002
3011	9891.88	06-OCT-23	2006	1001

9 rows selected.

Q8 List of names and cities of all salesmen in one city e.g. London with commission above 10%.

Oracle SQL

SELECT sname, city
FROM   salesmen
WHERE  city = 'London'
AND    commission > .10;

Explanation

Commission is stored as a fraction, so 10% is written .10. Both conditions must be true because they are joined with AND.

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

SNAME	CITY
Piyush	London
Rajesh	London

2 rows selected.

Row order can differ in your Oracle session because this query has no ORDER BY.

Q9 List all customers whose names begins with a letter 'C'.

Oracle SQL

SELECT *
FROM   customers
WHERE  cname LIKE 'C%';

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

CNUM	CNAME	CITY	RATING	SNUM
2003	Chirag	Surat	200	1002

1 row selected.

Q10 List all customers whose names begins with letter 'A' to'G'.

Oracle SQL

SELECT cnum, cname, city
FROM   customers
WHERE  cname >= 'A' AND cname < 'H'
ORDER  BY cname;

Explanation

Strings are compared alphabetically. cname BETWEEN 'A' AND 'G' would miss 'Gita', because 'Gita' is greater than 'G'. Using < 'H' includes every name starting with G. Another solution is SUBSTR(cname, 1, 1) BETWEEN 'A' AND 'G'.

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

CNUM	CNAME	CITY
2006	Ankita	London
2008	Bhavin	Surat
2003	Chirag	Surat
2004	Dhaval	Rajkot
2009	Esha	Anand
2010	Farhan	Rajkot
2002	Gita	Rome

7 rows selected.

Q11 List all orders with zero or NULL amount.

Oracle SQL

SELECT *
FROM   orders
WHERE  amount = 0
OR     amount IS NULL;

Explanation

NULL means *unknown*, so amount = NULL is never true — always use IS NULL. No order in the sample data has a zero or missing amount, so the correct output is *no rows selected*. WHERE NVL(amount, 0) = 0 is a shorter equivalent.

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

no rows selected

Q12 Find out the largest orders of salesman from two value e.g. 1002 and 1007.

Oracle SQL

SELECT snum, MAX(amount) AS largest_order
FROM   orders
WHERE  snum IN (1002, 1007)
GROUP  BY snum
ORDER  BY snum;

Explanation

WHERE keeps only the orders of the two salesmen, GROUP BY makes one group for each, and MAX returns each one's largest order.

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

SNUM	LARGEST_ORDER
1002	5160.45
1007	1098.16

2 rows selected.

Q13 Count all orders of particular date e.g. October 3, 2023

Oracle SQL

SELECT COUNT(*) AS orders_on_3_oct
FROM   orders
WHERE  odate = TO_DATE('03-10-2023', 'DD-MM-YYYY');

Explanation

An Oracle DATE also stores a time. These dates were inserted without a time (midnight), so equality works. If dates contain times, use TRUNC(odate) = TO_DATE(...).

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

ORDERS_ON_3_OCT
5

1 row selected.

Q14 Calculate the total amount ordered.

Oracle SQL

SELECT SUM(amount) AS total_amount
FROM   orders;

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

TOTAL_AMOUNT
31614.18

1 row selected.

Q15 Calculate the average amount ordered.

Oracle SQL

SELECT ROUND(AVG(amount), 2) AS average_amount
FROM   orders;

Explanation

AVG ignores NULL amounts. ROUND(..., 2) keeps two decimal places.

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

AVERAGE_AMOUNT
2634.52

1 row selected.

Q16 Count the no. of salesmen currently having orders.

Oracle SQL

SELECT COUNT(DISTINCT snum) AS salesmen_with_orders
FROM   orders;

Explanation

COUNT(*) would count orders (12). COUNT(DISTINCT snum) counts each salesman once.

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

SALESMEN_WITH_ORDERS
6

1 row selected.

Q17 List all salesmen with their % of commission.

Oracle SQL

SELECT sname, TO_CHAR(commission * 100) || '%' AS commission_pct
FROM   salesmen
ORDER  BY sname;

Explanation

commission * 100 turns the fraction into a percentage and || adds the % sign.

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

SNAME	COMMISSION_PCT
Anil	10%
Kiran	26%
Mehul	15%
Piyush	12%
Rajesh	11%
Sejal	13%

6 rows selected.

Q18 Assume each salesperson has a 15% commission. Write a query on the order table that will produce the order number, salesman no and the amount of commission for that order

Oracle SQL

SELECT onum, snum, ROUND(amount * .15, 2) AS commission_amt
FROM   orders
ORDER  BY onum;

Explanation

The fixed 15% is written as the constant .15, so no join with SALESMEN is needed.

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

ONUM	SNUM	COMMISSION_AMT
3001	1007	2.8
3002	1004	285.02
3003	1001	115.08
3005	1002	774.07
3006	1007	164.72
3007	1002	11.36
3008	1001	708.45
3009	1003	256.98
3010	1002	196.49
3011	1001	1483.78
3012	1005	518.37
3013	1002	225

12 rows selected.

Q19 Find the highest rating in each city in the form : For the city (city), the highest rating is : (rating)

Oracle SQL

SELECT 'For the city ' || city || ', the highest rating is : ' || MAX(rating) AS result
FROM   customers
GROUP  BY city
ORDER  BY city;

Explanation

Text literals, the grouped column and the aggregate are joined with || into one output column. city may be used because it is in GROUP BY.

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

RESULT
For the city Ahmedabad, the highest rating is : 200
For the city Anand, the highest rating is : 200
For the city London, the highest rating is : 100
For the city Rajkot, the highest rating is : 300
For the city Rome, the highest rating is : 200
For the city Surat, the highest rating is : 300

6 rows selected.

Q20 List all in descending order of rating.

Oracle SQL

SELECT *
FROM   customers
ORDER  BY rating DESC, cname;

Explanation

The question refers to customers (only they have a rating). Customers with the same rating are listed alphabetically.

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

CNUM	CNAME	CITY	RATING	SNUM
2008	Bhavin	Surat	300	1007
2004	Dhaval	Rajkot	300	1002
2003	Chirag	Surat	200	1002
2009	Esha	Anand	200	1003
2002	Gita	Rome	200	1003
2011	Jay	Ahmedabad	200	1005
2006	Ankita	London	100	1001
2010	Farhan	Rajkot	100	1007
2001	Hardik	London	100	1001
2007	Pooja	Rome	100	1004

10 rows selected.

Q21 Calculate the total of orders for each day and place the result in descending order.

Oracle SQL

SELECT odate, SUM(amount) AS day_total
FROM   orders
GROUP  BY odate
ORDER  BY day_total DESC;

Explanation

A column alias can be used in ORDER BY (but not in WHERE or GROUP BY).

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

ODATE	DAY_TOTAL
06-OCT-23	11201.83
03-OCT-23	8944.59
05-OCT-23	8178.78
04-OCT-23	3288.98

4 rows selected.

Q22 Show the name of all customers with their salesman's name.

Oracle SQL

SELECT c.cname, s.sname
FROM   customers c, salesmen s
WHERE  c.snum = s.snum
ORDER  BY c.cname;

Explanation

Equi-join on the foreign key customers.snum = salesmen.snum.

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

CNAME	SNAME
Ankita	Piyush
Bhavin	Mehul
Chirag	Sejal
Dhaval	Sejal
Esha	Anil
Farhan	Mehul
Gita	Anil
Hardik	Piyush
Jay	Kiran
Pooja	Rajesh

10 rows selected.

Q23 List all customers and salesmen who shared a same city.

Oracle SQL

SELECT c.cname, s.sname, c.city
FROM   customers c, salesmen s
WHERE  c.city = s.city
ORDER  BY c.city, c.cname, s.sname;

Explanation

The join condition is on city, so a customer is paired with every salesman of the same city, whether or not that salesman serves the customer.

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

CNAME	SNAME	CITY
Jay	Kiran	Ahmedabad
Esha	Anil	Anand
Ankita	Piyush	London
Ankita	Rajesh	London
Hardik	Piyush	London
Hardik	Rajesh	London
Dhaval	Mehul	Rajkot
Farhan	Mehul	Rajkot
Bhavin	Sejal	Surat
Chirag	Sejal	Surat

10 rows selected.

Q24 List all orders with the names of their customer and salesman.

Oracle SQL

SELECT o.onum, c.cname, s.sname, o.amount
FROM   orders    o
JOIN   customers c ON c.cnum = o.cnum
JOIN   salesmen  s ON s.snum = o.snum
ORDER  BY o.onum;

Explanation

A three-table join needs two join conditions. The salesman is the one credited with the order (ORDERS.SNUM).

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

ONUM	CNAME	SNAME	AMOUNT
3001	Bhavin	Mehul	18.69
3002	Pooja	Rajesh	1900.1
3003	Hardik	Piyush	767.19
3005	Chirag	Sejal	5160.45
3006	Bhavin	Mehul	1098.16
3007	Dhaval	Sejal	75.75
3008	Ankita	Piyush	4723
3009	Gita	Anil	1713.23
3010	Dhaval	Sejal	1309.95
3011	Ankita	Piyush	9891.88
3012	Jay	Kiran	3455.78
3013	Chirag	Sejal	1500

12 rows selected.

Q25 List all orders by the customers not located in the same city as their salesman.

Oracle SQL

SELECT o.onum, c.cname, c.city AS cust_city, s.sname, s.city AS sales_city
FROM   orders    o
JOIN   customers c ON c.cnum = o.cnum
JOIN   salesmen  s ON s.snum = o.snum
WHERE  c.city <> s.city
ORDER  BY o.onum;

Explanation

Here *their salesman* is the salesman credited with the order. To compare with the salesman assigned to the customer instead, join SALESMEN on s.snum = c.snum.

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

ONUM	CNAME	CUST_CITY	SNAME	SALES_CITY
3001	Bhavin	Surat	Mehul	Rajkot
3002	Pooja	Rome	Rajesh	London
3006	Bhavin	Surat	Mehul	Rajkot
3007	Dhaval	Rajkot	Sejal	Surat
3009	Gita	Rome	Anil	Anand
3010	Dhaval	Rajkot	Sejal	Surat

6 rows selected.

Q26 List all customers serviced by salespeople with commission above 15%.

Oracle SQL

SELECT c.cname, s.sname, s.commission
FROM   customers c, salesmen s
WHERE  c.snum = s.snum
AND    s.commission > .15;

Explanation

Only Kiran (26%) earns more than 15%; Mehul has exactly 15% and is therefore excluded.

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

CNAME	SNAME	COMMISSION
Jay	Kiran	.26

1 row selected.

Q27 Calculate the amount of the salesman commission on each order by a customer with rating above 100.

Oracle SQL

SELECT o.onum, c.cname, c.rating, s.sname,
       ROUND(o.amount * s.commission, 2) AS commission_amt
FROM   orders o, customers c, salesmen s
WHERE  o.cnum = c.cnum
AND    o.snum = s.snum
AND    c.rating > 100
ORDER  BY o.onum;

Explanation

The rating comes from CUSTOMERS, the commission rate from SALESMEN and the amount from ORDERS, so all three tables are joined.

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

ONUM	CNAME	RATING	SNAME	COMMISSION_AMT
3001	Bhavin	300	Mehul	2.8
3005	Chirag	200	Sejal	670.86
3006	Bhavin	300	Mehul	164.72
3007	Dhaval	300	Sejal	9.85
3009	Gita	200	Anil	171.32
3010	Dhaval	300	Sejal	170.29
3012	Jay	200	Kiran	898.5
3013	Chirag	200	Sejal	195

8 rows selected.

Q28 Find all pairs of customers having the same rating without duplication.

Oracle SQL

SELECT a.cname AS customer1, b.cname AS customer2, a.rating
FROM   customers a, customers b
WHERE  a.rating = b.rating
AND    a.cnum   < b.cnum
ORDER  BY a.rating, a.cname, b.cname;

Explanation

A self join uses the same table twice with different aliases. a.cnum < b.cnum removes pairs of a customer with himself and prevents each pair appearing twice (A–B and B–A) — that is the *without duplication* part.

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

CUSTOMER1	CUSTOMER2	RATING
Ankita	Farhan	100
Ankita	Pooja	100
Hardik	Ankita	100
Hardik	Farhan	100
Hardik	Pooja	100
Pooja	Farhan	100
Chirag	Esha	200
Chirag	Jay	200
Esha	Jay	200
Gita	Chirag	200
Gita	Esha	200
Gita	Jay	200
Dhaval	Bhavin	300

13 rows selected.

Q29 List all orders that are greater than the average of October 4,2023.

Oracle SQL

SELECT onum, amount, odate
FROM   orders
WHERE  amount > (SELECT AVG(amount)
                 FROM   orders
                 WHERE  odate = TO_DATE('04-10-2023', 'DD-MM-YYYY'))
ORDER  BY amount;

Explanation

The subquery returns the average of the three orders of 4 October 2023 (1096.33); the outer query lists every order larger than that.

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

ONUM	AMOUNT	ODATE
3006	1098.16	03-OCT-23
3010	1309.95	06-OCT-23
3013	1500	04-OCT-23
3009	1713.23	04-OCT-23
3002	1900.1	03-OCT-23
3012	3455.78	05-OCT-23
3008	4723	05-OCT-23
3005	5160.45	03-OCT-23
3011	9891.88	06-OCT-23

9 rows selected.

Q30 Find the average commission of salesmen in London.

Oracle SQL

SELECT AVG(commission) AS avg_commission
FROM   salesmen
WHERE  city = 'London';

Explanation

Piyush (.12) and Rajesh (.11) average to .115, i.e. 11.5%.

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

AVG_COMMISSION
.115

1 row selected.

Q31 Find all orders attributed to salesmen in 'London' using both the subquery and join methods.

Oracle SQL

-- (a) subquery method
SELECT *
FROM   orders
WHERE  snum IN (SELECT snum FROM salesmen WHERE city = 'London')
ORDER  BY onum;

-- (b) join method
SELECT o.*
FROM   orders o
JOIN   salesmen s ON s.snum = o.snum
WHERE  s.city = 'London'
ORDER  BY o.onum;

Explanation

Both statements return the same rows. The subquery returns the London salesmen's numbers and IN matches them; the join attaches each order's salesman and filters on his city.

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

ONUM	AMOUNT	ODATE	CNUM	SNUM
3002	1900.1	03-OCT-23	2007	1004
3003	767.19	03-OCT-23	2001	1001
3008	4723	05-OCT-23	2006	1001
3011	9891.88	06-OCT-23	2006	1001

4 rows selected.

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

ONUM	AMOUNT	ODATE	CNUM	SNUM
3002	1900.1	03-OCT-23	2007	1004
3003	767.19	03-OCT-23	2001	1001
3008	4723	05-OCT-23	2006	1001
3011	9891.88	06-OCT-23	2006	1001

4 rows selected.

Q32 List the commission of all salesmen serving customers in 'London'.

Oracle SQL

SELECT DISTINCT s.sname, s.commission
FROM   salesmen s
JOIN   customers c ON c.snum = s.snum
WHERE  c.city = 'London';

Explanation

DISTINCT shows each salesman once even if he serves several London customers.

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

SNAME	COMMISSION
Piyush	.12

1 row selected.

Q33 Find all customers whose cnum is e.g. 1000 above than the snum of name e.g. Sejal.

Oracle SQL

SELECT *
FROM   customers
WHERE  cnum > (SELECT snum + 1000
               FROM   salesmen
               WHERE  sname = 'Sejal')
ORDER  BY cnum;

Explanation

Sejal's snum is 1002, so the subquery returns 2002 and the customers with a cnum greater than 2002 are listed. The subquery must return a single row for > to work.

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

CNUM	CNAME	CITY	RATING	SNUM
2003	Chirag	Surat	200	1002
2004	Dhaval	Rajkot	300	1002
2006	Ankita	London	100	1001
2007	Pooja	Rome	100	1004
2008	Bhavin	Surat	300	1007
2009	Esha	Anand	200	1003
2010	Farhan	Rajkot	100	1007
2011	Jay	Ahmedabad	200	1005

8 rows selected.

Q34 Count the no. of customers with the rating above than the average of one city e.g. 'Surat'.

Oracle SQL

SELECT COUNT(*) AS customers_above_surat_avg
FROM   customers
WHERE  rating > (SELECT AVG(rating) FROM customers WHERE city = 'Surat');

Explanation

Surat's customers are rated 200 and 300, average 250; two customers have a higher rating.

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

CUSTOMERS_ABOVE_SURAT_AVG
2

1 row selected.

Q35 Find all salesmen with customers located in their cities using ANY and IN.

Oracle SQL

-- using ANY
SELECT snum, sname, city
FROM   salesmen
WHERE  city = ANY (SELECT city FROM customers)
ORDER  BY snum;

-- using IN
SELECT snum, sname, city
FROM   salesmen
WHERE  city IN (SELECT city FROM customers)
ORDER  BY snum;

Explanation

= ANY and IN are equivalent: true if the salesman's city equals at least one customer city. Both queries return the same rows.

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

SNUM	SNAME	CITY
1001	Piyush	London
1002	Sejal	Surat
1003	Anil	Anand
1004	Rajesh	London
1005	Kiran	Ahmedabad
1007	Mehul	Rajkot

6 rows selected.

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

SNUM	SNAME	CITY
1001	Piyush	London
1002	Sejal	Surat
1003	Anil	Anand
1004	Rajesh	London
1005	Kiran	Ahmedabad
1007	Mehul	Rajkot

6 rows selected.

Q36 Find all salesmen for whom there are customers that follow them in alphabetical order.

Assumption: 'Customers' is read as any customer. To consider only the salesman's own customers, use WHERE EXISTS (SELECT 1 FROM customers c WHERE c.snum = s.snum AND c.cname > s.sname).

Oracle SQL

SELECT snum, sname
FROM   salesmen
WHERE  sname < ANY (SELECT cname FROM customers)
ORDER  BY sname;

Explanation

A salesman qualifies if at least one customer's name comes after his name alphabetically. Rajesh and Sejal do not qualify — the alphabetically last customer is Pooja, and 'Pooja' comes before both 'Rajesh' and 'Sejal'.

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

SNUM	SNAME
1003	Anil
1005	Kiran
1007	Mehul
1001	Piyush

4 rows selected.

Q37 Find all customers having rating greater than any customer in particular city e.g. 'Rajkot'.

Oracle SQL

SELECT cname, city, rating
FROM   customers
WHERE  rating > ANY (SELECT rating FROM customers WHERE city = 'Rajkot')
ORDER  BY cname;

Explanation

Rajkot's ratings are 300 and 100. > ANY means greater than at least one of them, i.e. greater than the minimum (100).

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

CNAME	CITY	RATING
Bhavin	Surat	300
Chirag	Surat	200
Dhaval	Rajkot	300
Esha	Anand	200
Gita	Rome	200
Jay	Ahmedabad	200

6 rows selected.

Q38 List all orders that has amount greater than atleat one of the orders from 6th October, 2023.

Oracle SQL

SELECT onum, amount, odate
FROM   orders
WHERE  amount > ANY (SELECT amount FROM orders
                     WHERE  odate = TO_DATE('06-10-2023', 'DD-MM-YYYY'))
ORDER  BY amount;

Explanation

Orders on 6 October are 1309.95 and 9891.88; *greater than at least one* means greater than 1309.95.

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

ONUM	AMOUNT	ODATE
3013	1500	04-OCT-23
3009	1713.23	04-OCT-23
3002	1900.1	03-OCT-23
3012	3455.78	05-OCT-23
3008	4723	05-OCT-23
3005	5160.45	03-OCT-23
3011	9891.88	06-OCT-23

7 rows selected.

Q39 Find all orders with amounts smaller than any amount for a customer in 'London'.

Assumption: In everyday English 'smaller than any' can also mean 'smaller than all of them'; for that reading use < ALL, which returns only orders below 767.19.

Oracle SQL

SELECT onum, amount
FROM   orders
WHERE  amount < ANY (SELECT o.amount
                     FROM   orders o
                     JOIN   customers c ON c.cnum = o.cnum
                     WHERE  c.city = 'London')
ORDER  BY amount;

Explanation

London customers' orders are 767.19, 4723.00 and 9891.88. < ANY means smaller than at least one of them — smaller than the maximum (9891.88).

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

ONUM	AMOUNT
3001	18.69
3007	75.75
3003	767.19
3006	1098.16
3010	1309.95
3013	1500
3009	1713.23
3002	1900.1
3012	3455.78
3008	4723
3005	5160.45

11 rows selected.

Q40 Find all the customers who have greater rating than every customer in one city e.g. 'Anand’

Oracle SQL

SELECT cname, city, rating
FROM   customers
WHERE  rating > ALL (SELECT rating FROM customers WHERE city = 'Anand')
ORDER  BY cname;

Explanation

> ALL means greater than every value — greater than the maximum Anand rating (200).

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

CNAME	CITY	RATING
Bhavin	Surat	300
Dhaval	Rajkot	300

2 rows selected.

Q41 Create a union of two queries that shows the names, cities and ratings of all customers. Those with rating of >=200 should display 'HIGH RATING' and those with < 200 should display 'LOW RATING'.

Oracle SQL

SELECT cname, city, rating, 'HIGH RATING' AS rating_type
FROM   customers
WHERE  rating >= 200
UNION
SELECT cname, city, rating, 'LOW RATING'
FROM   customers
WHERE  rating < 200
ORDER  BY rating DESC, cname;

Explanation

Each query adds a constant text column. Both SELECTs have the same number and types of columns; the column names come from the first query and ORDER BY is written once at the end.

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

CNAME	CITY	RATING	RATING_TYPE
Bhavin	Surat	300	HIGH RATING
Dhaval	Rajkot	300	HIGH RATING
Chirag	Surat	200	HIGH RATING
Esha	Anand	200	HIGH RATING
Gita	Rome	200	HIGH RATING
Jay	Ahmedabad	200	HIGH RATING
Ankita	London	100	LOW RATING
Farhan	Rajkot	100	LOW RATING
Hardik	London	100	LOW RATING
Pooja	Rome	100	LOW RATING

10 rows selected.

Q42 Produce the name and number of each salesman and each customer with more than one current order in the alphabetical order of names.

Oracle SQL

SELECT s.sname AS name, s.snum AS num
FROM   salesmen s
WHERE  1 < (SELECT COUNT(*) FROM orders o WHERE o.snum = s.snum)
UNION
SELECT c.cname, c.cnum
FROM   customers c
WHERE  1 < (SELECT COUNT(*) FROM orders o WHERE o.cnum = c.cnum)
ORDER  BY name;

Explanation

Each half uses a correlated subquery to count the orders of the current salesman or customer. UNION combines both lists; ORDER BY on the alias sorts names alphabetically.

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

NAME	NUM
Ankita	2006
Bhavin	2008
Chirag	2003
Dhaval	2004
Mehul	1007
Piyush	1001
Sejal	1002

7 rows selected.

Q43 Create union of three queries. First select snum of all salesman in Surat, second, the cnum of all customers in 'Surat' and third, the onum of all orders of 3rd Oct. Retain duplicates between the last two queries but remove the duplicates between either of them and the first.

Oracle SQL

SELECT snum AS num FROM salesmen  WHERE city = 'Surat'
UNION
SELECT cnum        FROM customers WHERE city = 'Surat'
UNION ALL
SELECT onum        FROM orders    WHERE odate = TO_DATE('03-10-2023', 'DD-MM-YYYY');

Explanation

Oracle evaluates set operators left to right, all with the same precedence. The first UNION combines the Surat salesmen and customers and removes duplicates between them; the UNION ALL then appends the 3 October orders, keeping duplicates between the third query and the previous result.

Strictly, no single combination of UNION and UNION ALL can express every word of the question, because a UNION removes duplicates from its *whole* input. In this schema the three number ranges (10xx, 20xx, 30xx) never overlap, so the result is the same either way — that is the point of the exercise: explain how UNION and UNION ALL treat duplicates.

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

NUM
1002
2003
2008
3001
3002
3003
3005
3006

8 rows selected.

Row order can differ in your Oracle session because this query has no ORDER BY.

Q44 Remove all orders from customer Chirag from the orders table.

Oracle SQL

DELETE FROM orders
WHERE  cnum = (SELECT cnum FROM customers WHERE cname = 'Chirag');

COMMIT;

Explanation

The subquery finds Chirag's customer number (2003). He has two orders, 3005 and 3013.

Expected Output

2 rows deleted.

Commit complete.

Verify the change

Verification query

SELECT COUNT(*) AS chirag_orders FROM orders WHERE cnum = 2003;

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

CHIRAG_ORDERS
0

1 row selected.

Q45 Set the ratings of all the customers of Piyush to 400.

Oracle SQL

UPDATE customers
SET    rating = 400
WHERE  snum = (SELECT snum FROM salesmen WHERE sname = 'Piyush');

COMMIT;

Explanation

Piyush (1001) is assigned to Hardik and Ankita.

Expected Output

2 rows updated.

Commit complete.

Verify the change

Verification query

SELECT cnum, cname, rating, snum FROM customers WHERE snum = 1001;

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

CNUM	CNAME	RATING	SNUM
2001	Hardik	400	1001
2006	Ankita	400	1001

2 rows selected.

Row order can differ in your Oracle session because this query has no ORDER BY.

Q46 Increase the rating of all customers in Rome by 100.

Oracle SQL

UPDATE customers
SET    rating = rating + 100
WHERE  city = 'Rome';

COMMIT;

Explanation

Run SELECT * FROM customers WHERE city = 'Rome'; first to see the two rows that will change.

Expected Output

2 rows updated.

Commit complete.

Verify the change

Verification query

SELECT cnum, cname, city, rating FROM customers WHERE city = 'Rome';

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

CNUM	CNAME	CITY	RATING
2002	Gita	Rome	300
2007	Pooja	Rome	200

2 rows selected.

Row order can differ in your Oracle session because this query has no ORDER BY.

Important concepts used

Substitution variable

&name — SQL*Plus prompts for a value and substitutes it into the statement text before execution.

Aggregate functions

COUNT, SUM, AVG, MIN, MAX return one value per group; they ignore NULLs (except COUNT(*)).

GROUP BY

Forms one group per distinct value; non-aggregated SELECT columns must be grouped.

Self join

Join a table to itself with two aliases to compare rows of the same table.

Correlated subquery

A subquery that refers to a column of the outer query and is re-evaluated for each outer row.

ANY / ALL

> ANY = greater than the minimum; > ALL = greater than the maximum; = ANY = IN.

Set operators

UNION removes duplicates, UNION ALL keeps them; operators are evaluated left to right.

Common mistakes

• Writing amount = NULL instead of amount IS NULL.
• Using cname BETWEEN 'A' AND 'G' and missing names that start with G.
• Selecting a non-grouped column: SELECT snum, odate, MAX(amount) ... GROUP BY snum → *ORA-00979: not a GROUP BY expression*.
• Using = with a subquery that returns more than one row (ORA-01427).
• Forgetting a.cnum < b.cnum in a self join and getting duplicate and self pairs.
• Confusing > ANY with > ALL.
• Comparing a DATE column with a plain string and depending on the session's NLS_DATE_FORMAT.

Viva questions

What is a substitution variable?

A SQL*Plus variable written with & (or &&). SQL*Plus prompts for its value and replaces it in the statement before sending it to Oracle.

Difference between & and &&?

& prompts every time the variable is used; && prompts once and keeps the value for the rest of the session (until UNDEFINE).

What is the difference between a join and a subquery?

A join combines columns of several tables into one result row; a subquery computes a value or list that the outer query uses in its condition. Q31 solves the same problem both ways.

What is a correlated subquery?

A subquery that uses a value from the current row of the outer query, so it is executed once for each outer row.

Difference between UNION and UNION ALL?

UNION removes duplicate rows; UNION ALL keeps all rows and is faster.

What do > ANY and > ALL mean?

> ANY: greater than at least one value (the minimum). > ALL: greater than every value (the maximum).

Are = ANY and IN the same?

Yes, both are true if the value matches at least one value in the list.

What is a self join?

Joining a table to itself, using different aliases, to compare rows in the same table — for example customers with the same rating.

Summary

These 46 queries cover almost the whole SELECT statement. Remember the order in which Oracle logically processes a query — FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY — and most errors (aggregates in WHERE, non-grouped columns, aliases used too early) become easy to avoid.

Download script

set-03-salesmen-customers-and-orders.sql

All CREATE, INSERT and solution statements of Set 3 in one file — run it in SQL*Plus with @set-03-salesmen-customers-and-orders.sql.

Related practicals

• Set 2 — EMPLOYEE and DEPARTMENT
• Set 4 — PL/SQL Blocks, Functions and Procedures
• All 10 practical sets
• Viva questions

← Previous practicalSet 2: EMPLOYEE and DEPARTMENTNext practical →Set 4: PL/SQL Blocks, Functions and Procedures

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