IT C175 FINAL EXAM SEMESTER 1 UPDATED 2021
Final Exam Semester 1
The text below is an example of what constraint type:
The value in the manager_id column of the EMPLOYEES table must match a value in the
employee_id column in the EMPLOYEES table.
(4) Referential integrity
A table must have at least one candidate key, as well as its primary key. True or False?
(2) False
A table must have a primary key. True or False?
(2) False
If a primary key is a set of columns then one column must be null. True or False?
(2) False
Attributes become tables in a database. True or False?
(2) False
In a physical data model, an attribute becomes a _____________.
(4) Column
The transformation from an ER diagram to a physical design involves changing terminology.
Primary Unique Identifiers in the ER diagram become __________ and relationships become
____________.
(2) Primary keys, Foreign keys
The Oracle Database can implement a many to many relationship. You simply create two
foreign keys between the two tables. True or False?
(2) False
Many to many relationships are implemented via a structure called a: ________________
(2) Intersection Table
An "Arc Implementation" can be done just like any other Relationship - you simply add the
required Foreign Keys. True or False?
(2) False
What command will return data from the database to you?
(3) SELECT
The DESCRIBE command returns all rows from a table. True or False?
(2) False
What command can be used to create a new row in a table in the database?
(4) INSERT
The f_customers table contains the following data:
ID Name Address City State Zip
1 Cole Bee 123 Main Street Orlando FL 32838
2 Zoe Twee 1009 Oliver Avenue Boston MA 02116
,3 Sandra Lee 22 Main Street Tampa FL 32444
If you run the following statement:
DELETE FROM F_CUSTOMERS WHERE ID <= 2;
How many rows will be left in the table?
(3) 1
The SQL statement ALTER TABLE EMPLOYEES DELETE COLUMN SALARY is a valid
statement. True or False?
(2) False
Systems are always just rolled out as soon as the programming phase is finished. No further
work is required once the development is finished. True or False?
(2) False
System Documentation is developed right at the end once the system has gone live and users
have been using it for a little while. You are more likely to get it correct that way. True or False?
(2) False
Which SQL statement will return an error?
(1) SEL * FR sky;
When you use the SELECT clause to list one or two columns only from a table and no WHERE
clause, which SQL capability is used?
(3) Projection only
Which statement best describes how arithmetic expressions are handled?
(4) Division and multiplication operations are handled before subtraction and addition
operations.
In a SELECT clause, what is the result of 2 + 3 * 2?
(2) 8
Evaluate this SELECT statement:
SELECT (salary * raise_percent) raise
FROM employees;
If the RAISE_PERCENT column only contains null values, what will the statement return?
(2) Only null values
Which SQL keyword specifies that an alias will be substituted for a column name in the output of
a SQL query?
(1) AS
You need to display employees with salaries that are at least 30000 or higher. Which
comparison operator should you use?
(3) >=
You need to display employees whose salary is in the range of 30000 and 50000. Which
comparison operator should you use?
(3) BETWEEN...AND...
,Which symbol represents the not equal to condition?
(3) !=
The Concatenation Operator does which of the following?
(2) Links two or more columns or literals to form a single output column
You need to display all the rows in the EMPLOYEES table that contain a null value in the
DEPARTMENT_ID column. Which comparison operator should you use?
(4) IS NULL
What does the DISTINCT keyword do when it is used in a SELECT clause?
(3) Eliminates duplicate rows in the result
You need to display all the values in the EMAIL column that contains the underscore (_)
character as part of that email address. The WHERE clause in your SELECT statement
contains the LIKE operator. What must you include in the LIKE operator?
(4) The ESCAPE option (\)
You need write a SELECT statement that should only return rows that contain 34, 46, or 48 for
the DEPARTMENT_ID column. Which operator should you use in the WHERE clause to
compare the DEPARTMENT_ID column to this specific list of values?
(3) IN
You want to determine the orders that have been placed by customers who reside in Chicago.
You write this partial SELECT statement:
SELECT orderid, orderdate, total
FROM orders;
What should you include in your SELECT statement to achieve the desired results?
(3) WHERE city = 'Chicago';
What will be the result of the SELECT statement and what will display?
SELECT last_name, salary, salary + 300
FROM employees;
(1) Display the last name, salary and the results of adding 300 to each salary for all the
employees
When is an entity in 2nd Normal Form?
(1) When all non-UID attributes are dependent upon the entire UID.
Would it be a good idea to model age as an attribute of STUDENT?
(4) No - it breaks the Normalization rules
Which comparison condition means "Less Than or Equal To"?
(4) "<="
Which of the following best describes the meaning of the LIKE operator?
(3) Match a character pattern.
You need to change the default sort order of the ORDER BY clause so that the data is displayed
in reverse alphabetical order. Which keyword should you include in the ORDER BY clause?
, (1) DESC
Which clause would you include in a SELECT statement to sort the rows returned by the
LAST_NAME column?
(1) ORDER BY
The ORDER BY clause always comes last. True or False?
(1) True
Which of the following are TRUE regarding the logical AND operator?
(4) TRUE AND FALSE return FALSE
Evaluate this SELECT statement:
SELECT last_name, first_name, salary
FROM employees;
How will the results of this query be sorted?
(1) The database will display the rows in whatever order it finds it in the database, so no
particular order.
Evaluate this SELECT statement:
SELECT *
FROM employees
WHERE department_id = 34
OR department_id = 45
OR department_id = 67;
Which operator is the equivalent of the OR conditions used in this SELECT statement?
(1) IN
Evaluate this SELECT statement:
SELECT first_name, last_name, email
FROM employees
ORDER BY last_name;
Which statement is true?
(2) The rows will be sorted alphabetically by the LAST_NAME values.
What value will the following SQL statement return?
SELECT employee_id
FROM employees
WHERE employee_id BETWEEN 100 AND 150 OR employee_id IN(119, 175, 205) AND
(employee_id BETWEEN 150 AND 200);
(3) 100, 101, 102, 103, 104, 107, 124, 141, 142, 143, 144, 149
Evaluate this SELECT statement:
SELECT employee_id, last_name, first_name, salary 'Yearly Salary'
FROM employees
WHERE salary IS NOT NULL
ORDER BY last_name, 3;
Which clause contains an error?
(1) SELECT employee_id, last_name, first_name, salary 'Yearly Salary'
The EMPLOYEES table contains these columns:
Final Exam Semester 1
The text below is an example of what constraint type:
The value in the manager_id column of the EMPLOYEES table must match a value in the
employee_id column in the EMPLOYEES table.
(4) Referential integrity
A table must have at least one candidate key, as well as its primary key. True or False?
(2) False
A table must have a primary key. True or False?
(2) False
If a primary key is a set of columns then one column must be null. True or False?
(2) False
Attributes become tables in a database. True or False?
(2) False
In a physical data model, an attribute becomes a _____________.
(4) Column
The transformation from an ER diagram to a physical design involves changing terminology.
Primary Unique Identifiers in the ER diagram become __________ and relationships become
____________.
(2) Primary keys, Foreign keys
The Oracle Database can implement a many to many relationship. You simply create two
foreign keys between the two tables. True or False?
(2) False
Many to many relationships are implemented via a structure called a: ________________
(2) Intersection Table
An "Arc Implementation" can be done just like any other Relationship - you simply add the
required Foreign Keys. True or False?
(2) False
What command will return data from the database to you?
(3) SELECT
The DESCRIBE command returns all rows from a table. True or False?
(2) False
What command can be used to create a new row in a table in the database?
(4) INSERT
The f_customers table contains the following data:
ID Name Address City State Zip
1 Cole Bee 123 Main Street Orlando FL 32838
2 Zoe Twee 1009 Oliver Avenue Boston MA 02116
,3 Sandra Lee 22 Main Street Tampa FL 32444
If you run the following statement:
DELETE FROM F_CUSTOMERS WHERE ID <= 2;
How many rows will be left in the table?
(3) 1
The SQL statement ALTER TABLE EMPLOYEES DELETE COLUMN SALARY is a valid
statement. True or False?
(2) False
Systems are always just rolled out as soon as the programming phase is finished. No further
work is required once the development is finished. True or False?
(2) False
System Documentation is developed right at the end once the system has gone live and users
have been using it for a little while. You are more likely to get it correct that way. True or False?
(2) False
Which SQL statement will return an error?
(1) SEL * FR sky;
When you use the SELECT clause to list one or two columns only from a table and no WHERE
clause, which SQL capability is used?
(3) Projection only
Which statement best describes how arithmetic expressions are handled?
(4) Division and multiplication operations are handled before subtraction and addition
operations.
In a SELECT clause, what is the result of 2 + 3 * 2?
(2) 8
Evaluate this SELECT statement:
SELECT (salary * raise_percent) raise
FROM employees;
If the RAISE_PERCENT column only contains null values, what will the statement return?
(2) Only null values
Which SQL keyword specifies that an alias will be substituted for a column name in the output of
a SQL query?
(1) AS
You need to display employees with salaries that are at least 30000 or higher. Which
comparison operator should you use?
(3) >=
You need to display employees whose salary is in the range of 30000 and 50000. Which
comparison operator should you use?
(3) BETWEEN...AND...
,Which symbol represents the not equal to condition?
(3) !=
The Concatenation Operator does which of the following?
(2) Links two or more columns or literals to form a single output column
You need to display all the rows in the EMPLOYEES table that contain a null value in the
DEPARTMENT_ID column. Which comparison operator should you use?
(4) IS NULL
What does the DISTINCT keyword do when it is used in a SELECT clause?
(3) Eliminates duplicate rows in the result
You need to display all the values in the EMAIL column that contains the underscore (_)
character as part of that email address. The WHERE clause in your SELECT statement
contains the LIKE operator. What must you include in the LIKE operator?
(4) The ESCAPE option (\)
You need write a SELECT statement that should only return rows that contain 34, 46, or 48 for
the DEPARTMENT_ID column. Which operator should you use in the WHERE clause to
compare the DEPARTMENT_ID column to this specific list of values?
(3) IN
You want to determine the orders that have been placed by customers who reside in Chicago.
You write this partial SELECT statement:
SELECT orderid, orderdate, total
FROM orders;
What should you include in your SELECT statement to achieve the desired results?
(3) WHERE city = 'Chicago';
What will be the result of the SELECT statement and what will display?
SELECT last_name, salary, salary + 300
FROM employees;
(1) Display the last name, salary and the results of adding 300 to each salary for all the
employees
When is an entity in 2nd Normal Form?
(1) When all non-UID attributes are dependent upon the entire UID.
Would it be a good idea to model age as an attribute of STUDENT?
(4) No - it breaks the Normalization rules
Which comparison condition means "Less Than or Equal To"?
(4) "<="
Which of the following best describes the meaning of the LIKE operator?
(3) Match a character pattern.
You need to change the default sort order of the ORDER BY clause so that the data is displayed
in reverse alphabetical order. Which keyword should you include in the ORDER BY clause?
, (1) DESC
Which clause would you include in a SELECT statement to sort the rows returned by the
LAST_NAME column?
(1) ORDER BY
The ORDER BY clause always comes last. True or False?
(1) True
Which of the following are TRUE regarding the logical AND operator?
(4) TRUE AND FALSE return FALSE
Evaluate this SELECT statement:
SELECT last_name, first_name, salary
FROM employees;
How will the results of this query be sorted?
(1) The database will display the rows in whatever order it finds it in the database, so no
particular order.
Evaluate this SELECT statement:
SELECT *
FROM employees
WHERE department_id = 34
OR department_id = 45
OR department_id = 67;
Which operator is the equivalent of the OR conditions used in this SELECT statement?
(1) IN
Evaluate this SELECT statement:
SELECT first_name, last_name, email
FROM employees
ORDER BY last_name;
Which statement is true?
(2) The rows will be sorted alphabetically by the LAST_NAME values.
What value will the following SQL statement return?
SELECT employee_id
FROM employees
WHERE employee_id BETWEEN 100 AND 150 OR employee_id IN(119, 175, 205) AND
(employee_id BETWEEN 150 AND 200);
(3) 100, 101, 102, 103, 104, 107, 124, 141, 142, 143, 144, 149
Evaluate this SELECT statement:
SELECT employee_id, last_name, first_name, salary 'Yearly Salary'
FROM employees
WHERE salary IS NOT NULL
ORDER BY last_name, 3;
Which clause contains an error?
(1) SELECT employee_id, last_name, first_name, salary 'Yearly Salary'
The EMPLOYEES table contains these columns:
