Tutorial 2: SQL

1
Tutorial 2 SQL
2

• Question
• Again consider table DEPOSIT(acc-id, cust-id,
  amount). We want to retrieve the cust-id of the
  customers who ever deposited into two accounts
  with acc-id A1 and A2.
• Write an SQL query that contains only one SELECT.

3

• Again consider table DEPOSIT(acc-id, cust-id,
  amount). We want to retrieve the cust-id of the
  customers who ever deposited into two accounts
  with acc-id A1 and A2.
• Write an SQL query that contains only one SELECT.
• SELECT DISTINCT T1.cust-idFROM DEPOSIT T1,
  DEPOSIT T2WHERE T1.cust-id T2.cust-id AND
  T1.acc-id A1 AND
  T2.acc-id A2

4

• Question
• Again consider table DEPOSIT(acc-id, cust-id,
  amount). We want to retrieve the cust-id of the
  customers who ever deposited into the account
  with acc-id A1 or A2 but not both.
• Write an SQL query that contains only one SELECT.

5

• Again consider table DEPOSIT(acc-id, cust-id,
  amount). We want to retrieve the cust-id of the
  customers who ever deposited into the account
  with acc-id A1 or A2 but not both.
• Write an SQL query that contains only one SELECT.
• SELECT DISTINCT cust-id FROM DEPOSITWHERE acc-id
  A1 OR acc-id A2GROUP BY cust-idHAVING
  COUNT(DISTINCT acc-id) 1

Indispensable!
6

• Question We all know how to retrieve the
  largest tuple. Now let us see how to retrieve
  the second largest.
• ACC(acc-id, balance).
• Write an SQL query to retrieve the acc-id of the
  account with the second largest balance.

7

• ACC(acc-id, balance).
• Write an SQL query to retrieve the acc-id of the
  account with the second largest balance.
• SELECT acc-id FROM ACCWHERE balance (SELECT
  MAX(balance)
  FROM ACC WHERE
  balance ltgt
  (SELECT MAX(balance) FROM ACC))
• The next slide shows another solution.

8

• ACC(acc-id, balance).
• Write an SQL query to retrieve the acc-id of the
  account with the second largest balance.
• SELECT T1.acc-id FROM ACC T1, ACC T2WHERE
  T1.balance lt T2.balance GROUP BY
  T1.acc-idHAVING COUNT (DISTINCT T2.balance) 1
• Using this idea, we can find the account with the
  i-th largest balance, for any i.

9

• Question ACC(acc-id, balance).
• Display the median balance.
• Remark Let S be a set of n different numbers. If
  n is even, the median of S is the n/2-th largest
  number in S.
• You can assume
• ACC has an even number of tuples
• all balances are different.

10

• ACC(acc-id, balance).
• Display the median balance.
• SELECT T1.balance FROM ACC T1, ACC T2 WHERE
  T1.balance lt T2.balanceGROUP BY T1.acc-id,
  T1.balance HAVING COUNT() (SELECT COUNT()
  FROM ACC) / 2