Consider the following relations Tables People (ID, Name, YearofBirth, Gender, C

Question

Consider the following relations Tables People (ID, Name, YearofBirth, Gender, Country)Pittsburgh Account (ID, createdDate, bankID) own (PeoplelD, AccountiD) Bank (ID, Name, Country) Table is stored in the city: NYC DC Specify SQL expressions, and describe at least two strategies for processing of each of the following query. The queryis submitted in Chicago, and thus the results should be returned to Chicago (a) Find the numberof accounts in "PNC bank" (bank name) (b) Find the names of people who owns PNC account.

Explanation / Answer

a) FInd the Number of accounts in "PNC bank"

SQL expression:

SELECT count(Account.ID)

FROM Account, Bank

WHERE Account.bankID=Bank.ID AND Bank.Name='PNC bank';

Explanation:

Count is a function used in SQL expression to count unique no of enteries in the provided field, the

number of accounts can easily be obtained from the Account table, and thus the next condition of Bank name must have to be "PNC bank" will be compared using the foreing key that links Account and Bank tables.

b) Find the names of people who owns PNC account

SQL Expression:

SELECT People.Name

From People, Bank, Own, Account

Where People.ID=own.PeopleID AND own.AccountID=Account.ID AND Account.bankID=Bank.ID AND Bank.Name= 'PNC';

Explanation:

The Natural Join of the tables is obtained and then the condition Bank Name must be PNC is applied and the reults with the Names of people having the desired account is shown.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.