6. Joining tables – Test Yourself

Please find the answers at the end of the test.

Q1. Which of the following is a major database design principle?

  1. One of the main goals of good database design is to remove data redundancy
  2. One of the main goals of good database design is to allow multiple Primary Keys on a table
  3. One of the goals of good database design is to allow duplicate data across tables
  4. One of the goals of good database design is to move duplicate data into larger tables

Q2. What is used to allow data to be retrieved from more than one table in a single SQL statement?

  1. A UNITE
  2. A UNIFIER
  3. A SUMMARISER
  4. A JOIN

Q3. Where do you define which columns you wish to retrieve in a JOIN?

  1. After the SELECT keyword
  2. In the WHERE clause
  3. In the HAVING clause
  4. After the FROM keyword

Q4. For a JOIN query, where are the tables that are to be joined defined?

  1. After the SELECT keyword
  2. In the WHERE clause
  3. In the HAVING clause
  4. After the FROM keyword

Q5. Where do you specify the actual JOIN in an SQL statement?

  1. After the SELECT keyword
  2. In the WHERE clause
  3. In the HAVING clause
  4. After the FROM keyword

Q6. What columns would you normally JOIN on?

  1. One table’s column linked to another table’s Primary Key
  2. Any column in a table that looks like the data in another table’s column
  3. A column in a table linked to another column in that same table
  4. One table’s column linked to many other tables’ Primary Keys

Q7. What keyword is used to create an alias for a table?

  1. AS
  2. IS
  3. OF
  4. AT

Q8. What type of join is the following?

SELECT acct.account_name, trans.income, trans.expense 
FROM transaction AS trans, account AS acct 
WHERE acct.account_id = trans.account_id;
  1. An OUTER LEFT JOIN
  2. An OUTER RIGHT JOIN
  3. An INNER JOIN
  4. An OUTER FULL JOIN

And now the final question of our Tutorial!

This is a little trickier than the other questions. Just take your time and look at the options very carefully. Get this right…and it shows that you have great attention to detail 🙂 !

Q9. Which of the following 2 are correct?

1.

SELECT trans.trans_id
       , acct.account_name
       , account_id
FROM account_transaction trans
     , account acct
WHERE trans.trans_id IN (3, 4, 5)
AND   acct.account_id = trans.account_id
;

2.

SELECT trans.trans_id
       , acct.account_name
       , acct.account_id
FROM account_transaction trans
     , account acct
WHERE trans.trans_id IN (3, 4, 5)
AND   acct.account_id = trans.account_id
;

3.

SELECT trans.trans_id
       , acct.account_name
       , acct.account_id
FROM account_transaction trans
     , account acct
WHERE trans.trans_id IN (3, 4, 5)
;

4.

SELECT trans.trans_id
       , acct.account_name
       , acct.account_id
FROM account_transaction trans
     , account acct
WHERE  acct.account_id = trans.account_id
;
ANSWERS:

Q1. Which of the following is a major database design principle?

  1. One of the main goals of good database design is to remove data redundancy
  2. One of the main goals of good database design is to allow multiple Primary Keys on a table
  3. One of the goals of good database design is to allow duplicate data across tables
  4. One of the goals of good database design is to move duplicate data into larger tables

Answer:

1

Q2. What is used to allow data to be retrieved from more than one table in a single SQL statement?

  1. A UNITE
  2. A UNIFIER
  3. A SUMMARISER
  4. A JOIN

Answer:

4

Q3. Where do you define which columns you wish to retrieve in a JOIN?

  1. After the SELECT keyword
  2. In the WHERE clause
  3. In the HAVING clause
  4. After the FROM keyword

Answer:

1

Q4. For a JOIN query, where are the tables that are to be joined defined?

  1. After the SELECT keyword
  2. In the WHERE clause
  3. In the HAVING clause
  4. After the FROM keyword

Answer:

4

Q5. Where do you specify the actual JOIN in an SQL statement?

  1. After the SELECT keyword
  2. In the WHERE clause
  3. In the HAVING clause
  4. After the FROM keyword

Answer:

2

Q6. What columns would you normally JOIN on?

  1. One table’s column linked to another table’s Primary Key
  2. Any column in a table that looks like the data in another table’s column
  3. A column in a table linked to another column in that same table
  4. One table’s column linked to many other tables’ Primary Keys

Answer:

1

Q7. What keyword is used to create an alias for a table?

  1. AS
  2. IS
  3. OF
  4. AT

Answer:

1

Q8. What type of join is the following?

SELECT acct.account_name, trans.income, trans.expense 
FROM transaction AS trans, account AS acct 
WHERE acct.account_id = trans.account_id;
  1. An OUTER LEFT JOIN
  2. An OUTER RIGHT JOIN
  3. An INNER JOIN
  4. An OUTER FULL JOIN

Answer:

3

Q9. Which of the following are correct?

1.

SELECT trans.trans_id
       , acct.account_name
       , account_id
FROM account_transaction trans
     , account acct
WHERE trans.trans_id IN (3, 4, 5)
AND   acct.account_id = trans.account_id
;

2.

SELECT trans.trans_id
       , acct.account_name
       , acct.account_id
FROM account_transaction trans
     , account acct
WHERE trans.trans_id IN (3, 4, 5)
AND   acct.account_id = trans.account_id
;

3.

SELECT trans.trans_id
       , acct.account_name
       , acct.account_id
FROM account_transaction trans
     , account acct
WHERE trans.trans_id IN (3, 4, 5)
;

4.

SELECT trans.trans_id
       , acct.account_name
       , acct.account_id
FROM account_transaction trans
     , account acct
WHERE  acct.account_id = trans.account_id
;

Answer:

2 and 4

Number 1: is incorrect, as account_id exists on 2 tables, so you must identify which table account_id belongs to after the SELECT keyword

Number 2: is correct. Transactions with a trans_id equal to 3, 4 and 5 will be returned, along with the corresponding account name + id from the account table

Number 3: is incorrect. No join has been specified – therefore, every transaction in account_transaction will attempt to be matched with every row in account, bringing back meaningless information

Number 4: is correct. Every row in account_trans will be returned with its corresponding account name + id from the account table

Return To Lesson ⇒ 6. Joining tables