Write the output of SQL queries (a) and (b) based on the following two tables DOCTOR and PATIENT belonging to

Question 77 of 95

Q. Write the output of SQL queries

(a) and

(b) based on the following two tables DOCTOR and PATIENT belonging to the same database: Table: DOCTOR DNO DNAME FEES D1 AMITABH 1500 D2 ANIKET 1000 D3 NIKHIL 1500 D4 ANJANA 1500 Table: PATIENT PNO PNAME ADMDATE DNO P1 NOOR 2021-12-25 D1 P2 ANNIE 2021-11-20 D2 P3 PRAKASH 2020-12-10 NULL P4 HARMEET 2019-12-20 D1

(a) SELECT DNAME, PNAME FROM DOCTOR NATURAL JOIN PATIENT;

(b) SELECT PNAME, ADMDATE, FEES FROM PATIENT P, DOCTOR D WHERE D.DNO = P.DNO AND FEES > 1000;

Puducherry TnboardCBSE Class XII Board 2022Subjective· 2mImportance★★★★★

81% · 77/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 — Relational Algebra Operations

Relational Algebra Operations: A First Look

Think of a relational database as a collection of neat, rectangular tables. Each table has rows (records) and columns (attributes). Now, suppose you want to ask questions of this data — "Which customers live in Delhi?" or "Show me all orders placed last month." Relational algebra is the set of basic operations you use to answer such questions. It is the language of queries at the most fundamental level.

You do not need to write code or do math. You only need to understand what each operation does to a table — like a set of tools in a toolbox.

The Core Operations

There are eight classic operations. They fall into two groups: those that work on one table at a time, and those that combine two tables.

Operations on a Single Table

Select (also called Restrict) — This operation picks certain rows from a table based on a condition. For example, from a table of students, you might select only those rows where the city is "Mumbai". The result is a smaller table with the same columns but fewer rows.

Project — This operation picks certain columns from a table. For example, from a student table with columns Roll No, Name, City, and Marks, you might project only Name and City. The result is a table with fewer columns. Duplicate rows are automatically removed.

Rename — This operation simply gives a new name to the resulting table or to its columns. It is useful when you need to refer to the same table more than once in a query, or when you want clearer column headings.

Note

Select and Project are the two most frequently used operations. Select narrows down rows; Project narrows down columns. Together they let you extract exactly the slice of data you need.

Operations That Combine Two Tables

Union — This combines two tables that have the same structure (same number of columns and compatible data types). The result contains all rows that appear in either table, with duplicates removed. Think of it as "add the rows of one table to the rows of another, but keep only unique ones."

Set Difference — This gives you rows that are in the first table but not in the second. For example, "Which students are enrolled in Course A but not in Course B?"

Intersection — This gives you rows that appear in both tables. For example, "Which customers have bought both a laptop and a printer?"

Cartesian Product — This pairs every row of the first table with every row of the second table. If the first table has 10 rows and the second has 5, the result has 50 rows. This operation is rarely used alone — it is the foundation for the most powerful operation of all.

Join — This is the heart of relational algebra. A join combines rows from two tables based on a related column between them. For example, you have a Customers table and an Orders table. The join operation matches each order to the customer who placed it, using the Customer ID column that appears in both tables. The result is a single table with all the customer details alongside their orders.

Important

The Join operation is what makes relational databases relational. Without it, data in separate tables would remain isolated. Joins let you connect information across tables — customers to orders, students to courses, products to suppliers — and answer questions that span multiple pieces of data.

Why Relational Algebra Matters …

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

Let’s walk through each query step by step.

(a) NATURAL JOIN

A natural join matches rows where all columns with the same name have equal values. Here, both tables share the column DNO.

D1 appears in DOCTOR and also in PATIENT (for P1 and P4).
D2 appears in DOCTOR and PATIENT (for P2).
D3 and D4 appear only in DOCTOR — no matching patient rows, so they are dropped.
The patient row with NULL DNO (P3) cannot match any doctor, so it is also dropped.

The result has three rows:

AMITABH with NOOR
ANIKET with ANNIE
AMITABH with HARMEET

(b) Cartesian product with condition

This is an equi-join followed by a filter. The condition D.DNO = P.DNO keeps only rows where doctor and patient are linked. Then FEES > 1000 keeps only those with fees above 1000.

D1 (AMITABH, fees 1500) matches P1 and P4. …

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

Natural join matches only rows with common column values, while a Cartesian product with a condition filters rows based on the given criteria.

Let us begin with the first query. A natural join is a special kind of join that automatically matches rows from two tables based on columns that have the same name. In our case, both DOCTOR and PATIENT have a column called DNO. The natural join will pair each doctor with every patient who has that doctor's DNO. Crucially, it also eliminates duplicate columns — so DNO appears only once in the result, not twice.

Now look at the data. Doctor D1 (AMITABH) appears in two patient rows: P1 (NOOR) and P4 (HARMEET). Doctor D2 (ANIKET) appears in one patient row: P2 (ANNIE). Doctor D3 (NIKHIL) and D4 (ANJANA) have no matching patients — their DNO values do not appear in the PATIENT table. Also, patient P3 (PRAKASH) has a NULL in DNO, so it cannot match any doctor. Natural join, like all joins, ignores NULL values because NULL is not equal to anything, not even another NULL.

So the natural join will produce exactly three rows:

DNAME	PNAME
AMITABH	NOOR
AMITABH	HARMEET
ANIKET	ANNIE

Note

The order of rows in the output is not guaranteed by SQL unless you use an ORDER BY clause. But in practice, most database systems return rows in the order they are processed — often the order of the left table first.

Now for the second query. This is a Cartesian product (also called cross join) followed by a condition in the WHERE clause. The query writes FROM PATIENT P, DOCTOR D — that is an old-style join that first pairs every patient with every doctor. Since there are 4 patients and 4 doctors, the intermediate result has 16 rows. Then the condition D.DNO = P.DNO AND FEES > 1000 filters these down.

The condition has two parts, both must be true:

The doctor's DNO must equal the patient's DNO.
The doctor's FEES must be greater than 1000.

Look at the FEES column: D1 has 1500, D2 has 1000, D3 has 1500, D4 has 1500. So only doctors with FEES > 1000 are D1, D3, and D4. But D3 and D4 have no matching patients (no patient has DNO = D3 or D4). So only D1 qualifies on both counts. …

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

←Previous questionQ19 — Write the output of SQL queries (a) to (d) based on the table VACCINAT…→Next questionQ21 — Differentiate between Candidate Key and Primary Key in the context of …

Q.Write the output of SQL queries

🎓Unlock everything free for 14 days

Unlock everything free for 14 days