Unit 3: Competency-Based Questions

Database Management competency questions usually involve providing you with one or two tables populated with data and asking you to write SQL queries to achieve specific results, or to predict the output of given SQL queries. Another common format involves debugging incomplete Python-SQL connectivity code.

1. SQL Query Writing (Based on a Table)

Consider the following table named INVENTORY.

Question 3.1 Write SQL queries for the following: (a) To display the details of all items in the ‘Hardware’ category, sorted by their price in descending order. Answer: SELECT * FROM INVENTORY WHERE Category = 'Hardware' ORDER BY Price DESC;

(b) To count the number of items in each category. Answer: SELECT Category, COUNT(*) FROM INVENTORY GROUP BY Category;

(c) To display the ItemName and total value of stock (Qty * Price) for all items where the total value is greater than 100,000. Answer: SELECT ItemName, Qty * Price FROM INVENTORY WHERE (Qty * Price) > 100000;

(d) To increase the price of all ‘Software’ category items by 10%. Answer: UPDATE INVENTORY SET Price = Price * 1.10 WHERE Category = 'Software';

2. Output Prediction (SQL)

Question 3.2 Based on the INVENTORY table above, what will be the output of the following queries?

(a) SELECT MAX(Price), MIN(Price) FROM INVENTORY; Output:

MAX(Price)  | MIN(Price)
5000.00     | 400.00

(b) SELECT ItemName FROM INVENTORY WHERE ItemName LIKE '%board'; Output:

ItemName
Motherboard
Keyboard

3. Python-SQL Connectivity Debugging

Question 3.3 Sunita has written a Python script to insert a new record into a STUDENT table in a MySQL database. However, the code has missing segments labeled Statement-1 and Statement-2. Fill in the blanks to make the code functional.

import mysql.connector

mycon = mysql.connector.connect(host="localhost", user="root", password="pwd", database="school")
cursor = mycon.cursor()

query = "INSERT INTO STUDENT (RollNo, Name) VALUES (%s, %s)"
data = (10, 'Aman')

# Statement-1: Execute the query
____________________________________

# Statement-2: Save the changes permanently to the database
____________________________________

print("Record Inserted")
mycon.close()

Answer:

• Statement-1: cursor.execute(query, data)
• Statement-2: mycon.commit()

4. Database Theory & Keys

Question 3.4 An airline company maintains two tables: PASSENGER (PnrNo, Name, Age, FlightNo) and FLIGHT (FlightNo, Start, Destination, Airline).

(a) Identify the Primary Keys in both tables. Answer: Primary Key of PASSENGER is PnrNo. Primary Key of FLIGHT is FlightNo.

(b) Which attribute acts as a Foreign Key in the PASSENGER table? How does it help? Answer: FlightNo acts as the Foreign Key in the PASSENGER table. It helps establish a relationship between the two tables, allowing us to query and link a specific passenger to the details of the flight they are travelling on.