Write SQL queries for (i) to (iv), which are based on the tables: CUSTOMERS and PURCHASES given in the questio

Question 94 of 95

Q. Write SQL queries for

(i) to (iv), which are based on the tables: CUSTOMERS and PURCHASES given in the question 4(g):

(i) To display details of all CUSTOMERS whose CITIES are neither Delhi nor Mumbai.

(ii) To display the CNAME and CITIES of all CUSTOMERS in ascending order of their CNAME.

(iii) To display the number of CUSTOMERS along with their respective CITIES in each of the CITIES.

(iv) To display details of all PURCHASES whose Quantity is more than 15.

Yanam BieapCBSE Class XII Board 2020Subjective· 4mImportance★★★★★

99% · 94/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 →

These four queries are based on the CUSTOMERS table (columns CNO, CNAME, CITIES) and the PURCHASES table (columns SNO, QTY, PUR_DATE, CNO) from the previous question, so they must use those exact column names.

(i) Customers whose city is neither Delhi nor Mumbai — filter with NOT IN on the CITIES column:

SELECT * FROM CUSTOMERS
WHERE CITIES NOT IN ('Delhi', 'Mumbai');

(ii) CNAME and CITIES in ascending order of CNAME — use ORDER BY (ascending is the default):

SELECT CNAME, CITIES FROM CUSTOMERS
ORDER BY CNAME;

(iii) Number of customers in each city — group by CITIES and use COUNT(*):

SELECT CITIES, COUNT(*) AS NO_OF_CUSTOMERS
FROM CUSTOMERS
GROUP BY CITIES;
``` …

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

Four SQL queries on the CUSTOMERS (CNO, CNAME, CITIES) and PURCHASES (SNO, QTY, PUR_DATE, CNO) tables: filter with WHERE ... NOT IN, sort with ORDER BY, group with GROUP BY ... COUNT(*), and filter a numeric column with WHERE QTY > 15.

Understanding the task

These queries use the same CUSTOMERS and PURCHASES tables as the previous question. CUSTOMERS has columns CNO, CNAME and CITIES; PURCHASES has SNO, QTY, PUR_DATE and CNO. Each query must use these exact column names — referring to a column that does not exist (for example CITY instead of CITIES, or Quantity instead of QTY) would raise an error.

(i) Customers not in Delhi or Mumbai

"Neither Delhi nor Mumbai" means exclude both cities. NOT IN is the cleanest way to exclude a list of values:

SELECT * FROM CUSTOMERS
WHERE CITIES NOT IN ('Delhi', 'Mumbai');

An equivalent form uses two conditions joined by AND:

SELECT * FROM CUSTOMERS
WHERE CITIES <> 'Delhi' AND CITIES <> 'Mumbai';

Both return every customer whose CITIES value is not Delhi and not Mumbai (the Chennai, Indore and Bangalore customers).

(ii) Names and cities, sorted by name

Select just CNAME and CITIES and sort by name. ORDER BY sorts the result; ascending is the default, but stating ASC makes the intent clear:

SELECT CNAME, CITIES FROM CUSTOMERS
ORDER BY CNAME ASC;

(iii) Customer count per city

To count how many customers belong to each city, GROUP BY CITIES collapses all rows of the same city into one group, and COUNT(*) tallies the rows in each group:

SELECT CITIES, COUNT(*) AS NO_OF_CUSTOMERS
FROM CUSTOMERS
GROUP BY CITIES;
``` …

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

←Previous questionQ36 — Write outputs for SQL queries (i) to (iii), which are based on the fol…→Next questionQ38 — Write SQL queries for (i) to (iv) and write outputs for SQL queries (v…

Q.Write SQL queries for

Understanding the task

(i) Customers not in Delhi or Mumbai

(ii) Names and cities, sorted by name

(iii) Customer count per city

🎓Unlock everything free for 14 days

Unlock everything free for 14 days