RakibOFC: SQL Query Questions

1. Consider a database used to record the marks that students get in different exams of different course offerings (sections).
Draw an E-R diagram that models exams as entities, and uses a ternary relationship, for the database. Define the primary keys and foreign keys in the ERD.

Answer:

E-R diagram for marks database

Primary keys and foreign keys: Primary and foreign keys are the most basic components on which relational database theory is based. Primary keys enforce entity integrity by uniquely identifying entity instances. Foreign keys enforce referential integrity by completing an association between two entities.

2. Consider the bank database of given figure. Prepare an expression in the relational algebra for each of the following queries:
   branch(branch name, branch city, assets)
   customer (customer name, customer street, customer city)
   loan (loan number, branch name, amount)
   borrower (customer name, loan number)
   account (account number, branch name, balance)
   depositor (customer name, account number)

a. Find all loan numbers with a loan value greater than $10,000.

Answer: Relational algebra:

π_{loan_number} (δ_{amount > 10000} (loan))

Query

SELECT loan_number, amount
FROM loan
WHERE amount > 10000;

b. Find the names of all depositors who have an account with a value greater than $6,000.

Answer: Relational algebra:

π_{customer_name} (δ_{balance > 6000} (depositor ⋈ account))

Query

SELECT depositor.customer_name, account.balence
FROM depositor, account
WHERE account.balence > 6000;

c. Find the names of all depositors who have an account with a value greater than $6,000 at the “Uptown” branch.

Answer: Relational algebra:

π_{customer_name} (δ_{branch_name = “Uptown”} (depositor ⋈ account))

Query

SELECT depositor.customer_name, account.branch_name
FROM depositor, account
WHERE branch_name = "Uptown";

SQL Questions and Answers for Practice