Write the output of any three SQL queries (i) to (iv) based on the tables COMPANY and CUSTOMER given below : T

Question 89 of 95

Q. Write the output of any three SQL queries

(i) to

(iv) based on the tables COMPANY and CUSTOMER given below : Table : COMPANY CID C_NAME CITY PRODUCTNAME 111 SONY DELHI TV 222 NOKIA MUMBAI MOBILE 333 ONIDA DELHI TV 444 SONY MUMBAI MOBILE 555 BLACKBERRY CHENNAI MOBILE 666 DELL DELHI LAPTOP Table : CUSTOMER CUSTID CID NAME PRICE QTY C01 222 ROHIT SHARMA 70000 20 C02 666 DEEPIKA KUMARI 50000 10 C03 111 MOHAN KUMAR 30000 5 C04 555 RADHA MOHAN 30000 11

(i) SELECT PRODUCTNAME, COUNT() FROM COMPANY GROUP BY PRODUCTNAME HAVING COUNT()> 2;

(ii) SELECT NAME, PRICE, PRODUCTNAME FROM COMPANY C, CUSTOMER CU WHERE C.CID = CU.CID AND C_NAME = 'SONY';

(iii) SELECT DISTINCT CITY FROM COMPANY;

(iv) SELECT * FROM COMPANY WHERE C_NAME LIKE '%ON%';

Puducherry CbseCBSE Class XII Board 2023Subjective· 3mImportance★★★★★

94% · 89/95 Questions

🔒 Locked · start free trial →

You're viewing a preview — the full solution, concept, methods & PYQ mapping are locked.

Start your 14-day free trial to unlock the full solution →

Concept understanding — SQL Aggregate Functions

Imagine you have a giant box of receipts from a month of sales at a small shop. You don't want to look at every single receipt one by one — you want a single, useful number that summarises the whole pile. How much total money came in? What was the average sale? How many customers bought something? That instinct — to collapse a pile of raw details into one meaningful number — is exactly what an SQL aggregate function does.

In a database, a table might hold thousands of rows of data: one row for every sale, every student, every product. An aggregate function takes all those rows in a column and boils them down to a single value. It doesn't change the data; it answers a question about the whole set. The most common ones are COUNT, SUM, AVG, MIN, and MAX. Each one has a clear, everyday meaning.

COUNT tells you how many rows there are — like counting how many students are in a class list. SUM adds up all the numbers in a column — like totalling the marks of every student in an exam. AVG gives the average of those numbers. MIN and MAX find the smallest and largest values — the lowest temperature recorded or the highest sale of the day.

Note

Aggregate functions work on a column of data, not across rows. You always write them as FUNCTION_NAME(column_name). For example, AVG(price) gives the average of all prices in that column.

The real power comes when you combine them with the GROUP BY clause. Without GROUP BY, an aggregate function summarises the entire table. With GROUP BY, you split the table into groups first, then apply the function to each group separately. Think of it like this: you don't just want the average sale for the whole year — you want the average sale per month. GROUP BY month creates twelve groups, and AVG(sale_amount) runs once for each group, giving you twelve averages.

This is why aggregate functions matter in business, economics, and any data-driven decision. A manager doesn't ask "what was every single transaction?" They ask "what was the total revenue last quarter?" or "which product category had the highest average rating?" Aggregate functions turn raw, overwhelming data into actionable summaries. …

Start your 14-day free trial to unlock the full solution →

The first query groups companies by their product and counts how many rows exist for each product type, then filters to show only those products offered by more than two companies. Since MOBILE appears three times (NOKIA, SONY, BLACKBERRY) and TV appears twice (SONY, ONIDA), only MOBILE satisfies the HAVING condition. The output will be:

PRODUCTNAME  COUNT(*)
MOBILE       3

The second query joins COMPANY and CUSTOMER on the common CID column, retrieves customer names, prices, and product names, but only for rows where the company name is 'SONY'. Two companies have CID 111 and 444 (both SONY), but only CID 111 appears in CUSTOMER (Mohan Kumar). The result:

NAME          PRICE   PRODUCTNAME
MOHAN KUMAR   30000   TV

The third query lists all unique cities from the COMPANY table without repetition. Delhi appears three times, Mumbai twice, and Chennai once, but DISTINCT collapses them:

CITY
DELHI
MUMBAI
CHENNAI
``` …

Start your 14-day free trial to unlock the full solution →

The three SQL queries retrieve: (i) product names appearing more than twice, (ii) customer details for SONY products, (iii) distinct cities where companies operate, and (iv) companies with 'ON' anywhere in their name.

Understanding the Tables and Queries

Before we execute any SQL query, we need to understand the structure of our two tables. The COMPANY table holds information about various companies, their locations, and the products they manufacture. Each company is identified by a unique CID (Company ID). The CUSTOMER table records purchase transactions, linking customers to companies through the CID foreign key, along with the price and quantity of items purchased.

The relationship between these tables is straightforward: CUSTOMER.CID references COMPANY.CID, allowing us to join information about which customer bought from which company.

Query (i): Grouping and Filtering Products

SELECT PRODUCTNAME, COUNT(*) 
FROM COMPANY 
GROUP BY PRODUCTNAME 
HAVING COUNT(*)> 2;

This query asks: which products appear more than twice in the COMPANY table? The GROUP BY clause collects all rows with the same PRODUCTNAME together, and COUNT(*) tallies how many companies manufacture each product. The HAVING clause then filters these groups, keeping only those where the count exceeds 2.

Looking at the COMPANY table:

TV appears in rows with CID 111 and 333 (2 times)
MOBILE appears in rows with CID 222, 444, and 555 (3 times)
LAPTOP appears in row with CID 666 (1 time)

Output:

PRODUCTNAME	COUNT(*)
MOBILE	3

Only MOBILE satisfies the condition of appearing more than twice.

Query (ii): Joining Tables with a Condition

SELECT NAME, PRICE, PRODUCTNAME 
FROM COMPANY C, CUSTOMER CU 
WHERE C.CID = CU.CID AND C_NAME = 'SONY';

This query performs a join between COMPANY and CUSTOMER tables, retrieving customer names, prices paid, and product names—but only for purchases from SONY. The WHERE clause enforces two conditions: the CIDs must match (establishing the relationship), and the company name must be 'SONY'.

From COMPANY, SONY appears twice with CID 111 and CID 444. Now we check CUSTOMER for matching CIDs:

CID 111 links to customer MOHAN KUMAR who paid 30000 for a TV
CID 444 has no corresponding entry in CUSTOMER

Output:

NAME	PRICE	PRODUCTNAME
MOHAN KUMAR	30000	TV

Note

The join only returns rows where both conditions are met. Since no customer record exists for CID 444 (SONY's MOBILE), that combination doesn't appear in the result.

Query (iii): Finding Unique Cities

SELECT DISTINCT CITY 
FROM COMPANY;

This is the simplest of the four queries. It asks for all unique cities where companies are located. The DISTINCT keyword eliminates duplicates, so even though DELHI appears three times in the COMPANY table, it will appear only once in the output.

Output:

CITY
DELHI
MUMBAI
CHENNAI

The order may vary depending on the database system, as no ORDER BY clause is specified.

Query (iv): Pattern Matching with LIKE

SELECT * FROM COMPANY …

Start your 14-day free trial to unlock the full solution →

←Previous questionQ31 — (a) Consider the following tables Student and Sport : Table : Student …→Next questionQ33 — Which SQL command is used to add a new attribute in a table?

Q.Write the output of any three SQL queries

Understanding the Tables and Queries

Query (i): Grouping and Filtering Products

Query (ii): Joining Tables with a Condition

Query (iii): Finding Unique Cities

Query (iv): Pattern Matching with LIKE

🎓Unlock everything free for 14 days

Unlock everything free for 14 days