S6: Data Dictionary & SQL Design

What is the primary purpose of a data dictionary in a database?

• Storing user passwords

• Defining and managing data elements

• Creating backups

• Displaying data to end-users

Answer explanation

A data dictionary is crucial for defining and managing data elements by providing information about its structure and characteristics.

Which data type in SQL is suitable for storing True or False values?

• VARCHAR

• INTEGER

• BOOLEAN

• DATE

Answer explanation

BOOLEAN data type is specifically designed to store Boolean values, typically allowing TRUE, FALSE, 1 or 0 and sometimes NULL values.

Which SQL aggregate function is used to find the total sum of a numeric column?

• AVG()

• AVG()

• SUM()

• MAX()

Answer explanation

SUM() function is utilized to calculate the total sum of a numeric column.

Which constraint ensures that all values in a column are different?

• UNIQUE

• PRIMARY KEY

• FOREIGN KEY

• NOT NULL

Answer explanation

A UNIQUE constraint ensures that all values in a column are different and unique.

The HAVING clause in SQL is mainly used with which type of functions?

• String Functions

• Aggregate Functions

• Date Functions

• Numeric Functions

Answer explanation

HAVING clause is commonly used with Aggregate Functions to filter the aggregated results.

Which logical operator in SQL is used to filter the results within a specific range?

• NOT

• BETWEEN

• IN

• LIKE

Answer explanation

The BETWEEN operator is utilized to filter the result set within a specific range

Which of the following SQL queries will retrieve rows where age is not between 22 and 29?

• SELECT * FROM users WHERE NOT age BETWEEN 22 AND 29

• SELECT * FROM users WHERE age NOT BETWEEN 22 AND 29

• SELECT * FROM users WHERE age BETWEEN NOT 22 AND 29

• SELECT * FROM users WHERE NOT BETWEEN age 22 AND 29

Answer explanation

The correct query is "SELECT * FROM users WHERE age NOT BETWEEN 22 AND 29".

Which statement about subqueries in the WHERE clause is true?

• Can only retrieve one column in the subquery.

• Cannot be used alongside JOIN operations.

• Must always be enclosed with parentheses.

• Are not affected by ORDER BY in the subquery.

Answer explanation

Subqueries must always be enclosed with parentheses.

Which of the following SQL queries is correct for filtering records where country is either 'USA', 'UK', or 'Canada'?

• SELECT * FROM customers WHERE country IN 'USA', 'UK', 'Canada';

• SELECT * FROM customers WHERE country IN ('USA', 'UK', 'Canada');

• SELECT * FROM customers WHERE country = IN ('USA', 'UK', 'Canada');

• SELECT * FROM customers WHERE country = 'USA' AND 'UK' AND 'Canada';

Answer explanation

The query "SELECT * FROM customers WHERE country IN ('USA', 'UK', 'Canada');" uses the IN operator correctly to filter the records

Which SQL query correctly uses an aggregate function and the HAVING clause to select departments with more than 5 employees?

• SELECT department, COUNT(employee_id) FROM employees GROUP BY department HAVING COUNT(employee_id) > 5;

• SELECT department FROM employees HAVING COUNT(employee_id) > 5 GROUP BY department;

• SELECT department, COUNT(employee_id) FROM employees WHERE COUNT(employee_id) > 5 GROUP BY department;

• SELECT COUNT(employee_id), department FROM employees GROUP BY COUNT(employee_id) HAVING COUNT(employee_id) > 5

Answer explanation

The query "SELECT department, COUNT(employee_id) FROM employees GROUP BY department HAVING COUNT(employee_id) > 5;" correctly uses GROUP BY, an aggregate function, and the HAVING clause to retrieve departments with more than 5 employees.