SQL Select: The Basics

1. Looking at the image below, please select what kind of key term_key is in the term_dimension.

Primary

Foreign

It is a primary key because it is the uniquely identifying field for that table as opposed to just the key that links to a dimension with more data associated with a primary key.

Explanation

It is a primary key because it is the uniquely identifying field for that table as opposed to just the key that links to a dimension with more data associated with a primary key.

2. Fill in the Blank. SQL stands for ____________________________.

Standard Quantifying Logistics

Systematic Query Language

Structured Query Language

Standard Query Linguistics

SQL is a programming language designed for managing data held in relational databases

Explanation

SQL is a programming language designed for managing data held in relational databases

3. Benefits of SQL includes being flexible, universal, and requires learning only a very few and simple commands

True

False

All statements are true. SQL is very flexible and can be used in any number of SQL applications that can connect to a relational database...and as you have learned, it takes very few commands to get started in executing a SQL select statement

Explanation

All statements are true. SQL is very flexible and can be used in any number of SQL applications that can connect to a relational database...and as you have learned, it takes very few commands to get started in executing a SQL select statement

4. Which of the following are OPTIONAL parts of a SQL select statement?

ORDER BY

GROUP BY

WHERE

AND/OR

ALL are valid optional parts

All of the selections are possible as optional parts of the SQL. It is important that any special conditions for using the optional components are met to execute the query.

Explanation

All of the selections are possible as optional parts of the SQL. It is important that any special conditions for using the optional components are met to execute the query.

5. Looking at the image below, please determine what kind of key is course_eval_department_key

Primary

Foreign

It is a foreign key because it is listed in a FACT table along with a number of other foreign keys. These are "links" to other tables to create joins so the dimensional data can be included with numerical type data often existing in a FACT table.

Explanation

It is a foreign key because it is listed in a FACT table along with a number of other foreign keys. These are "links" to other tables to create joins so the dimensional data can be included with numerical type data often existing in a FACT table.

6. Please review the SQL statement below. Will the SELECT statement execute successfully?

Yes

No

Even though this looks like a very complex statement, it will not execute because it is missing a semi-colon. Something as simple as a missing semi-colon can cause even a simple SQL statement to fail to execute. It is very important to ensure you have ALL parts of the statement every time you want to execute it.

Explanation

Even though this looks like a very complex statement, it will not execute because it is missing a semi-colon. Something as simple as a missing semi-colon can cause even a simple SQL statement to fail to execute. It is very important to ensure you have ALL parts of the statement every time you want to execute it.

7.

Looking at the datamodel above, please select the column you would NOT use in a join between multiple tables if the select statement had the following columns: department_desc, course_title, instructor_full_name, essay_response_text

Course_eval_section_key

Course_eval_question_key

Course_eval_department_key

Course_eval_instructor_key

Because there are no columns being selected from the course_eval_question_dimension, and we do not know if there is a filter using it, there is no reason to create a join using the course_eval_question_key.

Explanation

Because there are no columns being selected from the course_eval_question_dimension, and we do not know if there is a filter using it, there is no reason to create a join using the course_eval_question_key.

8.

Looking at the datamodel above, select which tables would be used to return data from the columns: term_code, question_id, question_text, and scaled_response_number.

Course_eval_responses_detail, term_dimension

Course_eval_section_dimension, term_dimension, response_date_dimension

Course_eval_responses_detail, response_date_dimension, course_eval_section_attr_dim

Course_eval_responses_detail, term_dimension, course_eval_question_dimension

The columns dictate that there are three tables to use. Question_id and question_text can be gleaned from course_eval_question_dimension. Term_code will come from the _term_dimension...and finally, scaled_response_number will be pulled from the course_eval_responses_detail fact table.

Explanation

The columns dictate that there are three tables to use. Question_id and question_text can be gleaned from course_eval_question_dimension. Term_code will come from the _term_dimension...and finally, scaled_response_number will be pulled from the course_eval_responses_detail fact table.

9. Which of the following statements, including both required and optional elements, will execute successfully?

SELECT department_desc WHERE department_code = '0012';

SELECT * FROM department_dimension;

SELECT department_desc FROM department_dimension ORDER BY department_code;

SELECT department_code, department_desc FROM department_dimension WHERE college_code in ('01','08') ORDER BY department_code;

One answer only has required parts,one answer has no FROM element, and one has an order by that has a column NOT in the select.

Explanation

One answer only has required parts,one answer has no FROM element, and one has an order by that has a column NOT in the select.

10. What are the 5 parts of a SELECT statement that are required to execute successfully?

SELECT, FROM, column, semi-colon, table

SELECT, FROM, table, WHERE, semi-colon

Column, FROM, table, ORDER BY, semi-colon

Column, table, WHERE, ORDER BY, GROUP BY

At a minimum, these 5 parts MUST be present to execute a SQL select statement. There are some applications that may let the user eliminate the need for a semi-colon, but it is rare. Good practice is to use it to correctly define the end of a SQL statement

Explanation

At a minimum, these 5 parts MUST be present to execute a SQL select statement. There are some applications that may let the user eliminate the need for a semi-colon, but it is rare. Good practice is to use it to correctly define the end of a SQL statement

11. Which of the following is NOT a commonly accepted rule for joining multiple tables?

It is commonly accepted to list joins between tables before other items in the WHERE clause

It is recommended that aliases be keywords to keep the statement uniform throughout

It is recommended that joins between tables are based on Primary/Foreign keys

Any table listed in a join statement MUST exist in the FROM element

Using keywords as an alias is NOT allowed under relational database rules. Keywords are reserved words (such as FROM, WHERE, SELECT, etc) that will return an error if attempted to be used. Most SQL editors will warn/notify the user that a keyword has been selected.

Explanation

Using keywords as an alias is NOT allowed under relational database rules. Keywords are reserved words (such as FROM, WHERE, SELECT, etc) that will return an error if attempted to be used. Most SQL editors will warn/notify the user that a keyword has been selected.

12. Which of the following statements include ONLY the required elements needed to execute?

SELECT * FROM department_dimension

SELECT department_code FROM department_dimension WHERE department_code = '0012';

SELECT department_desc FROM department_dimension;

SELECT department_desc WHERE department_code = '0012';

Of the options that are incorrect, one has no semi-colon, one has no FROM element, and one includes optional parts in the statement. Only the select where department_desc is being selected from department_dimension (ending in a semi-colon) contains all 5 required elements, and ONLY those elements.

Explanation

Of the options that are incorrect, one has no semi-colon, one has no FROM element, and one includes optional parts in the statement. Only the select where department_desc is being selected from department_dimension (ending in a semi-colon) contains all 5 required elements, and ONLY those elements.