(A) (i) Define cartesian product with respect to RDBMS. (ii) Sunil wants to write a program in Python to updat

Question 21 of 29

Q. (A)

(i) Define cartesian product with respect to RDBMS.

(ii) Sunil wants to write a program in Python to update the quantity to 20 of the records whose item code is 111 in the table named shop in MySQL database named Keeper. The table shop in MySQL contains the following attributes: Item_code: Item code (Integer) Item_name: Name of item (String) Qty: Quantity of item (Integer) Price: Price of item (Integer) Consider the following to establish connectivity between Python and MySQL: Username: admin Password: Shopping Host: localhost

(OR)

(B)

(i) Give any two features of SQL.

(ii) Sumit wants to write a code in Python to display all the details of the passengers from the table flight in MySQL database, Travel. The table contains the following attributes: F_code: Flight code (String) F_name: Name of flight (String) Source: Departure city of flight (String) Destination: Destination city of flight (String) Consider the following to establish connectivity between Python and MySQL: Username: root Password: airplane Host: localhost

West Bengal WbchseCBSE Class XII Board 2024Subjective· 5mImportance★★★★★

72% · 21/29 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 →

Part (a)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. …

Part (b)Concept understanding — SQL Query Concepts

SQL Query Concepts — A First Look

The core idea: tables and queries

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 →

Part (a)

(i) Cartesian product in RDBMS is the operation that pairs every row of one table with every row of another. If table A has m rows and B has n rows, the result has m x n rows. In SQL it is produced by a CROSS JOIN or by listing tables in FROM with no join condition.

(ii) Update Qty to 20 where Item_code = 111 in table shop (database Keeper).

import mysql.connector

con = mysql.connector.connect(host="localhost", user="admin",
                              password="Shopping", database="Keeper")
cur = con.cursor()
cur.execute("UPDATE shop SET Qty = 20 WHERE Item_code = 111")
con.commit()          # required to save changes
print(cur.rowcount, "record(s) updated")
con.close() …

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

Part (a): Cartesian product pairs every row of one table with every row of another (m x n rows); Python code UPDATEs shop.Qty to 20 where Item_code=111 and commits. Part (b): two SQL features (declarative; DDL+DML support) and Python code that SELECTs and prints all flight rows.

Part (a)

(i) Cartesian product (cross product) with respect to an RDBMS combines two relations so that each row of the first table is joined with every row of the second table. If the first has m tuples and the second has n tuples, the Cartesian product has m x n tuples, and the number of columns is the sum of the two tables' columns. In SQL it results from a CROSS JOIN, or from SELECT * FROM A, B with no WHERE join condition. It is the basis on which joins are built, though used unfiltered it produces an explosion of rows.

(ii) The Python-MySQL workflow is: import the connector, connect, get a cursor, execute the SQL, commit (mandatory for INSERT/UPDATE/DELETE), then close.

import mysql.connector

con = mysql.connector.connect(host="localhost", user="admin",
                              password="Shopping", database="Keeper")
cur = con.cursor()
cur.execute("UPDATE shop SET Qty = 20 WHERE Item_code = 111")
con.commit()          # without commit the change is not saved
print(cur.rowcount, "record(s) updated")
con.close() …

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

←Previous questionQ6 — Peter has created a table named Account in MySQL database, SCHOOL, hav…→Next questionQ8 — Differentiate between the terms Attribute and Domain in the context of…

Q.(A)

Part (a)

🎓Unlock everything free for 14 days

Unlock everything free for 14 days