Write outputs for SQL queries (i) to (iii), which are based on the following tables, CUSTOMERS and PURCHASES:

Question 93 of 95

Q. Write outputs for SQL queries

(i) to (iii), which are based on the following tables, CUSTOMERS and PURCHASES: Table : CUSTOMERS CNO CNAME CITIES C1 SANYAM DELHI C2 SHRUTI DELHI C3 MEHER MUMBAI C4 SAKSHI CHENNAI C5 RITESH INDORE C6 RAHUL DELHI C7 AMEER CHENNAI C8 MINAKSHI BANGALORE C9 ANSHUL MUMBAI Table : PURCHASES SNO QTY PUR_DATE CNO S1 15 2018-12-25 C2 S2 10 2018-11-10 C1 S3 12 2018-11-10 C4 S4 7 2019-01-12 C7 S5 11 2019-02-12 C2 S6 10 2018-10-12 C6 S7 5 2019-05-09 C8 S8 20 2019-05-09 C3 S9 8 2018-05-09 C9 S10 15 2018-11-12 C5 S11 6 2018-08-04 C7

(i) SELECT COUNT(DISTINCT CITIES) FROM CUSTOMERS;

(ii) SELECT MAX(PUR_DATE) FROM PURCHASES;

(iii) SELECT CNAME, QTY, PUR_DATE FROM CUSTOMERS, PURCHASES WHERE CUSTOMERS.CNO = PURCHASES.CNO AND QTY IN (10,20);

Uttarakhand UbseCBSE Class XII Board 2020Subjective· 3mImportance★★★★★

98% · 93/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 Query Concepts

SQL Query Concepts — A First Look

Think of a library. You walk in, and instead of wandering through endless shelves, you go to the librarian and say: "Give me all the books by Tagore published after 1950, sorted by title." The librarian knows exactly where everything is, finds what you asked for, and hands you the list. That's what SQL does — but for data stored in a computer.

SQL stands for Structured Query Language. It is the language we use to talk to databases. A database is just a collection of related information stored in tables — like an Excel workbook with multiple sheets, but far more powerful and organised.

The core idea: tables and queries

A database stores data in tables. Each table has rows (records) and columns (fields). For example, a Students table might have columns like RollNo, Name, City, and Marks. Each row is one student's complete information.

A query is simply a question you ask the database. You write it in SQL, and the database returns an answer — usually a new table made from the data that matches your question.

Note

SQL is not a programming language like Python or Java. You don't tell the computer how to find the data. You only say what data you want. The database figures out the fastest way to get it.

The most common SQL command: SELECT

Almost every query begins with SELECT. It means "show me this data." The basic structure is:

SELECT column1, column2 FROM table_name WHERE condition;

Read it like a sentence: "Select these columns from this table where this condition is true."

If you want all columns, you write SELECT *. The asterisk is a shortcut meaning "everything."

Filtering with WHERE

The WHERE clause is your filter. It keeps only the rows that satisfy a condition. For example, if you want students from Delhi only, you write:

SELECT * FROM Students WHERE City = 'Delhi';

You can combine conditions using AND (both must be true) and OR (at least one must be true). This is exactly like everyday logic: "Show me students from Delhi AND who scored above 80."

Sorting with ORDER BY

Data often comes back in no particular order. To arrange it, use ORDER BY. You can sort ascending (smallest first, which is the default) or descending (largest first) using DESC.

SELECT Name, Marks FROM Students ORDER BY Marks DESC;

This gives you a ranked list — highest marks first.

Why SQL matters for commerce and humanities

You will never calculate a formula in SQL. That is not what it does. What SQL does is organise, filter, and retrieve information — and that is the foundation of every data-driven decision. …

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

Let us analyze each SQL query to understand its purpose and determine the resulting output based on the provided CUSTOMERS and PURCHASES tables.

(i) SELECT COUNT(DISTINCT CITIES) FROM CUSTOMERS;

This query counts the number of unique city names present in the CITIES column of the CUSTOMERS table. The DISTINCT keyword ensures that each city is counted only once, even if multiple customers are from the same city. The unique cities listed are Delhi, Mumbai, Chennai, Indore, and Bangalore.

Output:

COUNT(DISTINCT CITIES)
5

(ii) SELECT MAX(PUR_DATE) FROM PURCHASES;

This query retrieves the latest purchase date from the PUR_DATE column in the PURCHASES table. The MAX aggregate function identifies the chronologically highest date among all entries.

Output:

MAX(PUR_DATE)
2019-05-09

(iii) SELECT CNAME, QTY, PUR_DATE FROM CUSTOMERS, PURCHASES WHERE CUSTOMERS.CNO = PURCHASES.CNO AND QTY IN (10,20); …

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

The queries count distinct cities, find the latest purchase date, and list customer names, quantities, and purchase dates for specific quantities.

In database management, understanding the structure of tables and how they relate is fundamental to extracting meaningful information. Each table typically has a Primary Key, a column (or set of columns) that uniquely identifies each record. This key ensures data integrity and allows for efficient retrieval and linking of information across different tables.

For instance, in the CUSTOMERS table, CNO (Customer Number) serves as the Primary Key. Each customer has a unique CNO, ensuring that we can distinguish between SANYAM (C1) and SHRUTI (C2), even if they share the same city. This CNO then appears in the PURCHASES table as a Foreign Key, linking each purchase record back to the specific customer who made it. This relationship is crucial for queries that involve data from both tables, such as finding out which customer made a particular purchase.

Important

A Primary Key uniquely identifies each record in a table, while a Foreign Key establishes a link between two tables by referencing the Primary Key of another table. This relational structure is the backbone of relational databases.

Let's now examine the specific SQL queries and their outputs, understanding the logic behind each operation.

Query (i): `SELECT COUNT(DISTINCT CITIES) FROM CUSTOMERS;`

This query aims to determine the number of unique cities from which our customers originate. The DISTINCT keyword is vital here; without it, the query would simply count every entry in the CITIES column, including duplicates. By specifying DISTINCT CITIES, we instruct the database to consider only the unique city names before counting them.

Looking at the CUSTOMERS table:

DELHI appears multiple times (for C1, C2, C6).
MUMBAI appears twice (for C3, C9).
CHENNAI appears twice (for C4, C7).
INDORE appears once (for C5).
BANGALORE appears once (for C8).

The unique cities are DELHI, MUMBAI, CHENNAI, INDORE, and BANGALORE. Counting these distinct entries gives us the total number of unique cities.

Output for Query (i):

COUNT(DISTINCT CITIES)
5

Query (ii): `SELECT MAX(PUR_DATE) FROM PURCHASES;`

This query seeks to identify the latest date on which a purchase was made. The MAX() aggregate function is used to find the highest value within a specified column. For date columns, MAX() returns the most recent date.

Examining the PUR_DATE column in the PURCHASES table:

2018-12-25
2018-11-10
2018-11-10
2019-01-12
2019-02-12
2018-10-12
2019-05-09
2019-05-09
2018-05-09
2018-11-12
2018-08-04

By comparing all these dates, the latest date recorded is 2019-05-09.

Output for Query (ii):

MAX(PUR_DATE)
2019-05-09

Query (iii): `SELECT CNAME, QTY, PUR_DATE FROM CUSTOMERS, PURCHASES WHERE CUSTOMERS.CNO = PURCHASES.CNO AND QTY IN (10,20);`

This query is more complex, involving a join between two tables and a filtering condition. …

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

←Previous questionQ35 — Write the full form of the following abbreviations: (i) DDL (ii) DML →Next questionQ37 — Write SQL queries for (i) to (iv), which are based on the tables: CUST…

Q.Write outputs for SQL queries

Query (i): SELECT COUNT(DISTINCT CITIES) FROM CUSTOMERS;

Query (ii): SELECT MAX(PUR_DATE) FROM PURCHASES;

Query (iii): SELECT CNAME, QTY, PUR_DATE FROM CUSTOMERS, PURCHASES WHERE CUSTOMERS.CNO = PURCHASES.CNO AND QTY IN (10,20);

🎓Unlock everything free for 14 days

Query (i): `SELECT COUNT(DISTINCT CITIES) FROM CUSTOMERS;`

Query (ii): `SELECT MAX(PUR_DATE) FROM PURCHASES;`

Query (iii): `SELECT CNAME, QTY, PUR_DATE FROM CUSTOMERS, PURCHASES WHERE CUSTOMERS.CNO = PURCHASES.CNO AND QTY IN (10,20);`

Unlock everything free for 14 days