DBMS Quizzy Fo Shizzy

1. The ORDER BY clause cannot be used in a subquery

True

False

The ORDER BY clause is used to sort the result set of a query in a specific order. However, it cannot be used in a subquery. Subqueries are nested queries within a main query, and they are used to retrieve data based on certain conditions. Since the purpose of a subquery is to retrieve data, there is no need to sort it. Therefore, the ORDER BY clause is not allowed in a subquery.

Explanation

The ORDER BY clause is used to sort the result set of a query in a specific order. However, it cannot be used in a subquery. Subqueries are nested queries within a main query, and they are used to retrieve data based on certain conditions. Since the purpose of a subquery is to retrieve data, there is no need to sort it. Therefore, the ORDER BY clause is not allowed in a subquery.

2. When revoking privileges on you objects, you can

Only revoke privileges you granted or privileges granted by someone you gave "WITH GRANT OPTIONS"

Only revoke privileges you granted to others directly

Revoke anything at any time

Revoke any privilege except those given by a user with DBA privileges

You can revoke any privilege at any time, regardless of who granted it or whether it was granted with "WITH GRANT OPTIONS". The ability to revoke privileges gives you control over the access and permissions granted to users on your objects.

Explanation

You can revoke any privilege at any time, regardless of who granted it or whether it was granted with "WITH GRANT OPTIONS". The ability to revoke privileges gives you control over the access and permissions granted to users on your objects.

3. In the Non-ANSI/ISO transaction model, a transaction can be partially rolled back to a named point.

True

False

In the Non-ANSI/ISO transaction model, a transaction can be partially rolled back to a named point. This means that if there is a need to undo only a portion of the transaction, it is possible to roll back to a specific point within the transaction rather than rolling back the entire transaction. This allows for more flexibility and precision in managing transactions and can be useful in certain scenarios where only a specific part of the transaction needs to be undone.

Explanation

In the Non-ANSI/ISO transaction model, a transaction can be partially rolled back to a named point. This means that if there is a need to undo only a portion of the transaction, it is possible to roll back to a specific point within the transaction rather than rolling back the entire transaction. This allows for more flexibility and precision in managing transactions and can be useful in certain scenarios where only a specific part of the transaction needs to be undone.

4. In the ANSI/ISO transaction model, a program is always in transaction mode

True

False

In the ANSI/ISO transaction model, a program is always in transaction mode. This means that any changes made to the database by the program are treated as a single logical unit of work, and are either all committed or all rolled back. This ensures data consistency and integrity. Therefore, the statement "a program is always in transaction mode" is true in the ANSI/ISO transaction model.

Explanation

In the ANSI/ISO transaction model, a program is always in transaction mode. This means that any changes made to the database by the program are treated as a single logical unit of work, and are either all committed or all rolled back. This ensures data consistency and integrity. Therefore, the statement "a program is always in transaction mode" is true in the ANSI/ISO transaction model.

5. When granting privileges, you can only grant

One privilege at a time

To one object at a time

To one user at a time

None of the above

When granting privileges, you can only grant them to one object at a time. This means that you cannot grant privileges to multiple objects simultaneously. Each object needs to be granted privileges individually.

Explanation

When granting privileges, you can only grant them to one object at a time. This means that you cannot grant privileges to multiple objects simultaneously. Each object needs to be granted privileges individually.

6. When deleting from a table, the DELETE statement must specify the columns on the table to delete. Example:DELETE: CustNumFROM CustomersWHERE CustNum = 100;

True

False

The given statement is false. When deleting from a table, the DELETE statement does not need to specify the columns on the table to delete. The DELETE statement only needs to specify the table from which the rows should be deleted and any additional conditions for the deletion. In the given example, the correct DELETE statement would be: DELETE FROM Customers WHERE CustNum = 100;

Explanation

The given statement is false. When deleting from a table, the DELETE statement does not need to specify the columns on the table to delete. The DELETE statement only needs to specify the table from which the rows should be deleted and any additional conditions for the deletion. In the given example, the correct DELETE statement would be: DELETE FROM Customers WHERE CustNum = 100;

7. To update the column "Name" on a table to null, you use the following syntax:SET Name TO NULL;

True

False

The given statement is false. To update the column "Name" on a table to null, the correct syntax is: UPDATE table_name SET Name = NULL;

Explanation

The given statement is false. To update the column "Name" on a table to null, the correct syntax is: UPDATE table_name SET Name = NULL;

8. The INSERT, DELETE, and UPDATE statements are considered what in SQL?

DML (Data Manipulation Language)

DDL (Data Definition Language)

Transactions

All the above

The INSERT, DELETE, and UPDATE statements are considered as DML (Data Manipulation Language) in SQL. DML is used to manipulate and modify data within the database. It allows users to insert new data, delete existing data, and update existing data in the database tables. DDL (Data Definition Language) is used to define the structure and schema of the database objects, while transactions are used to ensure the atomicity, consistency, isolation, and durability of the database operations. Therefore, the correct answer is DML (Data Manipulation Language).

Explanation

The INSERT, DELETE, and UPDATE statements are considered as DML (Data Manipulation Language) in SQL. DML is used to manipulate and modify data within the database. It allows users to insert new data, delete existing data, and update existing data in the database tables. DDL (Data Definition Language) is used to define the structure and schema of the database objects, while transactions are used to ensure the atomicity, consistency, isolation, and durability of the database operations. Therefore, the correct answer is DML (Data Manipulation Language).

9. Select the statements that refer to Non-Correlated Sub-queries

A query where the sub-query portion is executed many times

The sub-query executes once for every row returned by the outer query

The DBMS executes the inner query first, then the outer query

A query where the sub-query portion is executed only once

Non-Correlated Sub-queries are sub-queries that are executed only once. In these types of sub-queries, the DBMS executes the inner query first, followed by the outer query. These sub-queries do not depend on the outer query and are independent of the outer query's result. They are usually used to retrieve a single value or set of values that are then used in the outer query. Non-Correlated Sub-queries are efficient as they are executed only once, reducing the overall execution time of the query.

Explanation

Non-Correlated Sub-queries are sub-queries that are executed only once. In these types of sub-queries, the DBMS executes the inner query first, followed by the outer query. These sub-queries do not depend on the outer query and are independent of the outer query's result. They are usually used to retrieve a single value or set of values that are then used in the outer query. Non-Correlated Sub-queries are efficient as they are executed only once, reducing the overall execution time of the query.

Submit

10. Select the statements that refer to Correlated Sub-queries

A query where the sub-query portion is executed many times

The sub-query executes once for every row returned by the outer query

The DBMS executes the inner query first, then the outer query

A query where the sub-query portion is executed only once

Correlated sub-queries are queries where the sub-query portion is executed once for every row returned by the outer query. The sub-query contains an outer reference, which means it includes a column from the sub-query that is also in a table from the outer query. This type of sub-query is executed many times, as it needs to be evaluated for each row in the outer query. The DBMS executes the inner query first, then the outer query.

Explanation

Correlated sub-queries are queries where the sub-query portion is executed once for every row returned by the outer query. The sub-query contains an outer reference, which means it includes a column from the sub-query that is also in a table from the outer query. This type of sub-query is executed many times, as it needs to be evaluated for each row in the outer query. The DBMS executes the inner query first, then the outer query.

Submit

11. What does the "WITH GRANT OPTION" clause of the GRANT statement do?

It turns on or off a user's ability to grant privileges to their own tables

It allows a user to grant privileges received on someone else's table to another user

GRANT is the userid. It allows this user to select and update his own tables

GRANT is the userid. It allows this user to grant privileges received on someone else's table to another user

The "WITH GRANT OPTION" clause of the GRANT statement allows a user to grant privileges received on someone else's table to another user. This means that a user who has been granted privileges on a table can pass on those privileges to another user, giving them the same level of access to the table.

Explanation

The "WITH GRANT OPTION" clause of the GRANT statement allows a user to grant privileges received on someone else's table to another user. This means that a user who has been granted privileges on a table can pass on those privileges to another user, giving them the same level of access to the table.

12. A subquery can return many columns of data as its query result

True

False

A subquery is a query nested within another query. It is used to retrieve data that will be used by the main query. However, a subquery can only return a single value or a single column of data, not multiple columns. Therefore, the statement that a subquery can return many columns of data is false.

Explanation

A subquery is a query nested within another query. It is used to retrieve data that will be used by the main query. However, a subquery can only return a single value or a single column of data, not multiple columns. Therefore, the statement that a subquery can return many columns of data is false.

13. Select the true statements about HAVING clauses from the list below. (There are more than one)

HAVING is used to eliminate groups from the result set

HAVING clause normally consists of one or more of the column functions applied to each of the groups

HAVING is used to eliminate rows from the result set

The HAVING clause is used to eliminate groups from the result set based on a condition. It is applied after the GROUP BY clause and can include one or more column functions that are applied to each group. This means that if a column function is used in the HAVING clause, it will be calculated after the grouping is done, which can result in additional processing. The HAVING clause is not used to eliminate individual rows from the result set, but rather entire groups that do not meet the specified condition.

Explanation

The HAVING clause is used to eliminate groups from the result set based on a condition. It is applied after the GROUP BY clause and can include one or more column functions that are applied to each group. This means that if a column function is used in the HAVING clause, it will be calculated after the grouping is done, which can result in additional processing. The HAVING clause is not used to eliminate individual rows from the result set, but rather entire groups that do not meet the specified condition.

Submit

14. A SELECT statement that uses the column functions can also include a WHERE clause

True

False

The given statement is true because a SELECT statement can include both column functions and a WHERE clause. Column functions allow us to perform calculations or manipulations on the values in a column, while the WHERE clause is used to filter the rows based on certain conditions. By combining these two elements, we can retrieve specific data from a table that meets the specified criteria.

Explanation

The given statement is true because a SELECT statement can include both column functions and a WHERE clause. Column functions allow us to perform calculations or manipulations on the values in a column, while the WHERE clause is used to filter the rows based on certain conditions. By combining these two elements, we can retrieve specific data from a table that meets the specified criteria.

15. Select the true statement about column functions:

When using column functions, the WHERE clause cannot be used

A column function takes the values of two columns and adds them together to create a new value

A column function takes an entire row of data and summarizes it into a single data item

A column function takes an entire column of data and summarizes it into a single data item

A column function takes an entire column of data and summarizes it into a single data item. This means that the function performs a calculation or operation on every value in the column and returns a single result. The function can be used to calculate the sum, average, minimum, maximum, or any other aggregate value of the column. The WHERE clause, on the other hand, is used to filter the rows based on certain conditions and is not directly related to column functions.

Explanation

A column function takes an entire column of data and summarizes it into a single data item. This means that the function performs a calculation or operation on every value in the column and returns a single result. The function can be used to calculate the sum, average, minimum, maximum, or any other aggregate value of the column. The WHERE clause, on the other hand, is used to filter the rows based on certain conditions and is not directly related to column functions.

16. When using the AVG() column function, NULL values are included in the calculation

True

False

The AVG() column function calculates the average of a column's values. By default, NULL values are not included in the calculation. Therefore, the given statement is false.

Explanation

The AVG() column function calculates the average of a column's values. By default, NULL values are not included in the calculation. Therefore, the given statement is false.

17. Which column function will count all records in a result set, regardless of the presence of null values?

COUNT(ColumnName)

Count(%)

Count()

Count(*)

The COUNT(*) function will count all records in a result set, regardless of the presence of null values. This is because the asterisk (*) is a wildcard character that represents all columns in the result set. So, using COUNT(*) will count all rows, including those with null values.

Explanation

The COUNT(*) function will count all records in a result set, regardless of the presence of null values. This is because the asterisk (*) is a wildcard character that represents all columns in the result set. So, using COUNT(*) will count all rows, including those with null values.

18. If there is a RIGHT-OUTER JOIN between two tables, what will be returned?

Return all rows where there is a match in both tables

Return all the rows from both tables whether they have a match in the other or not

A RIGHT-OUTER JOIN between two tables will return all rows where there is a match in both tables, plus any rows in the right table on the FROM clause that do not have a match on the left table. This means that all the rows from the right table will be included in the result, regardless of whether they have a match in the left table or not.

Explanation

A RIGHT-OUTER JOIN between two tables will return all rows where there is a match in both tables, plus any rows in the right table on the FROM clause that do not have a match on the left table. This means that all the rows from the right table will be included in the result, regardless of whether they have a match in the left table or not.

19. If DeptCode and DeptName are both non-key columns in a table and, given that logically the Deptname can be obtained if one knows the DeptCode, this is an example of a(n) ______ dependancy.

Partial

Derived

Transitive

Inverse

Relational

This is an example of a transitive dependency because the DeptName can be derived from the DeptCode. In other words, knowing the value of the DeptCode allows us to determine the value of the DeptName.

Explanation

This is an example of a transitive dependency because the DeptName can be derived from the DeptCode. In other words, knowing the value of the DeptCode allows us to determine the value of the DeptName.

20. What types of loops can you code in PL/SQL?

Only WHILE and DO-WHILE loops

Only FOR and DO-WHILE loops

Only FOR and WHILE loops

WHILE, DO-WHILE and FOR loops

In PL/SQL, you can code both FOR and WHILE loops. The FOR loop is used when you know the number of iterations in advance, and it iterates a specific number of times. The WHILE loop is used when you want to repeat a block of code until a certain condition is met. Therefore, the correct answer is "Only FOR and WHILE loops."

Explanation

In PL/SQL, you can code both FOR and WHILE loops. The FOR loop is used when you know the number of iterations in advance, and it iterates a specific number of times. The WHILE loop is used when you want to repeat a block of code until a certain condition is met. Therefore, the correct answer is "Only FOR and WHILE loops."

21. How often does a statement trigger execute?

It fires once when an INSERT, UPDATE, or DELETE occurs, regardless of how many rows are affected

It fires once for each row changed by an INSERT, UPDATE, or DELETE

The statement trigger executes once when an INSERT, UPDATE, or DELETE occurs, regardless of how many rows are affected. This means that even if multiple rows are changed by a single INSERT, UPDATE, or DELETE statement, the trigger will still only fire once.

Explanation

The statement trigger executes once when an INSERT, UPDATE, or DELETE occurs, regardless of how many rows are affected. This means that even if multiple rows are changed by a single INSERT, UPDATE, or DELETE statement, the trigger will still only fire once.

22. Which phrase causes a trigger to be a row trigger?

AFTER EACH ROW

Row Trigger is the default and no phrase is needed

ROW TRIGGER

FOR EACH ROW

The phrase "FOR EACH ROW" causes a trigger to be a row trigger. This phrase specifies that the trigger should be executed for each row affected by the triggering event. Without this phrase, the trigger would not be considered a row trigger and may not be executed for each row.

Explanation

The phrase "FOR EACH ROW" causes a trigger to be a row trigger. This phrase specifies that the trigger should be executed for each row affected by the triggering event. Without this phrase, the trigger would not be considered a row trigger and may not be executed for each row.

23. The 'NEW' and 'OLD' keywords can be used by what kind of triggers?

Statement triggers only

Row triggers only

Either statement or row triggers

The 'NEW' and 'OLD' keywords can only be used by row triggers. Row triggers are used to perform actions on individual rows that are affected by a data modification statement, such as an INSERT, UPDATE, or DELETE. The 'NEW' keyword refers to the new values being inserted or updated in the row, while the 'OLD' keyword refers to the old values of the row being updated or deleted. Therefore, these keywords are not applicable to statement triggers, which are triggered once per statement rather than per row.

Explanation

The 'NEW' and 'OLD' keywords can only be used by row triggers. Row triggers are used to perform actions on individual rows that are affected by a data modification statement, such as an INSERT, UPDATE, or DELETE. The 'NEW' keyword refers to the new values being inserted or updated in the row, while the 'OLD' keyword refers to the old values of the row being updated or deleted. Therefore, these keywords are not applicable to statement triggers, which are triggered once per statement rather than per row.

24. The proper way to specify that a trigger should fire when a table is updated and/or deleted is

UPDATE & DELETE

To write two separate triggers

UPDATE OR DELETE

UPDATE AND DELETE

UPDATE | DELETE

The correct answer is "UPDATE OR DELETE". This means that the trigger should fire when either an update or a delete operation is performed on the table. This option suggests that only one trigger needs to be written to handle both types of operations, making it the proper way to specify the trigger behavior.

Explanation

The correct answer is "UPDATE OR DELETE". This means that the trigger should fire when either an update or a delete operation is performed on the table. This option suggests that only one trigger needs to be written to handle both types of operations, making it the proper way to specify the trigger behavior.

25. What function will remove the leading and trailing spaces from a char value?

Tidy()

Clip()

Trim()

Compact()

The trim() function is used to remove any leading and trailing spaces from a char value. It is commonly used to clean up user input or to remove unnecessary spaces from strings.

Explanation

The trim() function is used to remove any leading and trailing spaces from a char value. It is commonly used to clean up user input or to remove unnecessary spaces from strings.

26. Select the three items required in order to use a cursor

Use a special form of the FOR loop to go through the rows in each cursor

Define a variable to hold the column(s) the SELECT returns

Define an array to hold the column(s) the SELECT returns

Define a select as CURSOR

Use a WHILE EXISTS loop to go through the rows in the cursor

Define the Cursor to Select Into a series of working variables

To use a cursor, you need to define a select as a cursor, which allows you to retrieve data from the database. Additionally, you need to define a variable to hold the column(s) that the SELECT statement returns, as well as use a special form of the FOR loop to iterate through the rows in each cursor. These three items are necessary to effectively use a cursor in database operations.

Explanation

To use a cursor, you need to define a select as a cursor, which allows you to retrieve data from the database. Additionally, you need to define a variable to hold the column(s) that the SELECT statement returns, as well as use a special form of the FOR loop to iterate through the rows in each cursor. These three items are necessary to effectively use a cursor in database operations.

Submit

27. How many rows can a cursor return?

Zero or more

One or more

More than one

A cursor is a database object that allows you to retrieve and manipulate data row by row. It can return zero rows if the query it is based on does not match any records in the database. On the other hand, it can also return one or more rows if the query does have matching records. Therefore, the correct answer is "Zero or more" rows that a cursor can return.

Explanation

A cursor is a database object that allows you to retrieve and manipulate data row by row. It can return zero rows if the query it is based on does not match any records in the database. On the other hand, it can also return one or more rows if the query does have matching records. Therefore, the correct answer is "Zero or more" rows that a cursor can return.

28. Given this function which is part of an SQL Select: to_char(Sales,'$9,990.00). If the value of Sales on the row is 4.95, the output produced would be

$4.95

$ 4.95

+$4.95

$0,004.95

$,$$4.95

The given function "to_char(Sales,'$9,990.00)" is used to format the value of the "Sales" column as a currency with a specific format. In this case, the format is set to display the dollar sign ($) followed by the value with two decimal places. Therefore, when the value of "Sales" is 4.95, the output produced would be "$4.95".

Explanation

The given function "to_char(Sales,'$9,990.00)" is used to format the value of the "Sales" column as a currency with a specific format. In this case, the format is set to display the dollar sign ($) followed by the value with two decimal places. Therefore, when the value of "Sales" is 4.95, the output produced would be "$4.95".

29. Does this FROM clause

FROM Offices LEFT OUTER JOIN SalesReps

Mean the same thing as this FROM Clause?

FROM SalesReps RIGHT OUTER JOIN Offices

True

False

The given correct answer is true because in SQL, the LEFT OUTER JOIN and RIGHT OUTER JOIN are essentially the same operation, just with the positions of the tables swapped. Both join types return all rows from the left table (Offices in this case) and the matching rows from the right table (SalesReps in this case). Therefore, the two FROM clauses mentioned in the question will produce the same result set.

Explanation

The given correct answer is true because in SQL, the LEFT OUTER JOIN and RIGHT OUTER JOIN are essentially the same operation, just with the positions of the tables swapped. Both join types return all rows from the left table (Offices in this case) and the matching rows from the right table (SalesReps in this case). Therefore, the two FROM clauses mentioned in the question will produce the same result set.

30. What is the difference between a stored procedure and a stored function?

A stored procedure cannot have an OUT parameter but a stored function can.

A stored function cannot have an OUT parameter but a stored procedure can

A stored procedure can return a value using a return statement

A stored function can return a value using a return statement

A stored function can return a value using a return statement, whereas a stored procedure cannot. Stored functions are designed to return a single value, which can be used in queries or assigned to variables. On the other hand, stored procedures are used to perform actions and can have multiple input and output parameters, but they do not have a return statement to provide a value as a result.

Explanation

A stored function can return a value using a return statement, whereas a stored procedure cannot. Stored functions are designed to return a single value, which can be used in queries or assigned to variables. On the other hand, stored procedures are used to perform actions and can have multiple input and output parameters, but they do not have a return statement to provide a value as a result.

31. What is the correct syntax for assigning a value to a variable?

WVar := 10;

WVar.equals(10);

WVar = 10;

WVar == 10;

The correct syntax for assigning a value to a variable in this case is "wVar := 10;". This is the assignment operator in some programming languages, such as Pascal or Ada. The other options are incorrect syntax for variable assignment. "wVar.equals(10);" is not a valid syntax for assigning a value, as it seems to be using a method to compare the variable to the value. "wVar = 10;" is the assignment operator in languages like Java or C++, but it is not the correct syntax given in this question. "wVar == 10;" is the equality operator used for comparison, not assignment.

Explanation

The correct syntax for assigning a value to a variable in this case is "wVar := 10;". This is the assignment operator in some programming languages, such as Pascal or Ada. The other options are incorrect syntax for variable assignment. "wVar.equals(10);" is not a valid syntax for assigning a value, as it seems to be using a method to compare the variable to the value. "wVar = 10;" is the assignment operator in languages like Java or C++, but it is not the correct syntax given in this question. "wVar == 10;" is the equality operator used for comparison, not assignment.

32. Where are working variables declared?

Anywhere within the stored procedure

Between the keywords AS and BEGIN

Working variables do not need to be declared before they are used

Before the keyword AS

Working variables are declared between the keywords AS and BEGIN in a stored procedure. These variables are used to store temporary data and can be accessed and manipulated within the scope of the stored procedure. By declaring the variables between AS and BEGIN, they are defined and initialized before being used in the logic of the stored procedure. This ensures that the variables are properly declared and can be used without any errors or issues.

Explanation

Working variables are declared between the keywords AS and BEGIN in a stored procedure. These variables are used to store temporary data and can be accessed and manipulated within the scope of the stored procedure. By declaring the variables between AS and BEGIN, they are defined and initialized before being used in the logic of the stored procedure. This ensures that the variables are properly declared and can be used without any errors or issues.

33. Some of the views in the System Catalog have a prefix "ALL_". What is found in these tables? (Select all that apply)

Information on your tables (tables in your schema)

Information on system tables

Information on tables you have been granted access to

The views in the System Catalog with the prefix "ALL_" provide information on the following:
1. Information on your tables (tables in your schema): These views contain details about the tables that you have created in your schema.
2. Information on system tables: These views provide information about the system tables in the database.
3. Information on tables you have been granted access to: These views display information about the tables for which you have been granted access permissions.

Explanation

The views in the System Catalog with the prefix "ALL_" provide information on the following:
1. Information on your tables (tables in your schema): These views contain details about the tables that you have created in your schema.
2. Information on system tables: These views provide information about the system tables in the database.
3. Information on tables you have been granted access to: These views display information about the tables for which you have been granted access permissions.

Submit

34. Some of the views in the System Catalog have a prefix "USER_". What is found in these tables ? (Select all that apply)

Information on system tables

Information on tables you have been granted access to

Information on your tables (tables in your schema)

The views in the System Catalog with the prefix "USER_" contain information on your tables, specifically the tables in your schema. These views provide details about the structure and properties of the tables that you have created in the database. They do not contain information on system tables or tables that you have been granted access to.

Explanation

The views in the System Catalog with the prefix "USER_" contain information on your tables, specifically the tables in your schema. These views provide details about the structure and properties of the tables that you have created in the database. They do not contain information on system tables or tables that you have been granted access to.

35. In Oracle's User_Constrains view, what indicates a foreign key in the Constraint_Type column?

R

P

C

V

U

In Oracle's User_Constraints view, the "R" value in the Constraint_Type column indicates a foreign key.

Explanation

In Oracle's User_Constraints view, the "R" value in the Constraint_Type column indicates a foreign key.

36. In Oracle, owner of the system catalog is

MASTER

ROOT

DBA

SYS

SYSTEM

In Oracle, the owner of the system catalog is SYS. SYS is a predefined user account that is created when Oracle is installed. It has the highest level of privileges and is responsible for managing the Oracle database. The SYS user owns all the base tables and views that make up the system catalog, which contains metadata about the database objects and their relationships. This allows SYS to have full control over the database and perform administrative tasks.

Explanation

In Oracle, the owner of the system catalog is SYS. SYS is a predefined user account that is created when Oracle is installed. It has the highest level of privileges and is responsible for managing the Oracle database. The SYS user owns all the base tables and views that make up the system catalog, which contains metadata about the database objects and their relationships. This allows SYS to have full control over the database and perform administrative tasks.

37. In Oracle, the view that displays the tables you have created is called

System Catalog

All_Tables

DBA_Tables

User_Tables

Dictionary

The view in Oracle that displays the tables created by the user is called User_Tables. This view provides information about the tables owned by the current user, including the table name, tablespace, number of rows, and other relevant details. It allows the user to easily access and manage the tables they have created in the database.

Explanation

The view in Oracle that displays the tables created by the user is called User_Tables. This view provides information about the tables owned by the current user, including the table name, tablespace, number of rows, and other relevant details. It allows the user to easily access and manage the tables they have created in the database.

38. The order of columns in a relational table (is)

Based on the value of the foreign key

Optimized by the database engine

Based on the value of the primary key

Changes as data is inserted and deleted

Left to right as defined when the table is created

The order of columns in a relational table is determined by the left to right sequence in which they are defined when the table is created. This means that the columns will appear in the table in the same order as they were specified during the table creation process.

Explanation

The order of columns in a relational table is determined by the left to right sequence in which they are defined when the table is created. This means that the columns will appear in the table in the same order as they were specified during the table creation process.

39. If there is FULL-OUTER JOIN between two tables, what will be returned?

Return all the rows from both tables whether they have a match in the other or not

Return all rows where there is a match in both tables

A FULL-OUTER JOIN returns all the rows from both tables, regardless of whether they have a match in the other table or not. This means that it includes all rows from both tables, including those that have a match in both tables, as well as any rows that do not have a match in the other table. Therefore, it returns all the rows from both tables whether they have a match in the other or not.

Explanation

A FULL-OUTER JOIN returns all the rows from both tables, regardless of whether they have a match in the other table or not. This means that it includes all rows from both tables, including those that have a match in both tables, as well as any rows that do not have a match in the other table. Therefore, it returns all the rows from both tables whether they have a match in the other or not.

40. This diagram is an example of which type of ERD

Key based ERD

Context ERD

Full Attribute ERD

This is not one of the defined ERD diagrams

Foreign Key ERD

not-available-via-ai

Explanation

not-available-via-ai

41. Based on this diagram, select the false statement

An employee may be working on several projects

A project may have more than one employee working on it

An employee may not be working on a project

An employee may be working on just one project

A project may have only one employee working on it

The given diagram shows a many-to-many relationship between employees and projects, indicating that an employee may be working on several projects. This means that the statement "An employee may be working on several projects" is true, not false. Therefore, the correct answer is that there is no false statement in the given options.

Explanation

The given diagram shows a many-to-many relationship between employees and projects, indicating that an employee may be working on several projects. This means that the statement "An employee may be working on several projects" is true, not false. Therefore, the correct answer is that there is no false statement in the given options.

42. A "thing" such as a Fixed Asset would likely be considered a(n) ___ in a relational model

Value

Entity

Field

Columns

Attribute

In a relational model, a "thing" such as a Fixed Asset would likely be considered an entity. In a database, an entity represents a real-world object or concept that can be distinguished from other objects. It is typically represented as a table, with each row in the table representing a specific instance or occurrence of that entity. Therefore, an entity is the most suitable term to describe a "thing" like a Fixed Asset in a relational model.

Explanation

In a relational model, a "thing" such as a Fixed Asset would likely be considered an entity. In a database, an entity represents a real-world object or concept that can be distinguished from other objects. It is typically represented as a table, with each row in the table representing a specific instance or occurrence of that entity. Therefore, an entity is the most suitable term to describe a "thing" like a Fixed Asset in a relational model.

43. In a "one-to-many" relationship between two tables, foreign keys are stored in ____ tables.

Either parent or child depending on the nature of the relationship

Special key

Parent

Child

In a "one-to-many" relationship between two tables, the foreign keys are stored in the child table. The child table is the table that contains multiple records that are related to a single record in the parent table. The foreign key in the child table is used to establish the relationship between the child and parent tables, allowing for the retrieval of data from both tables based on the relationship.

Explanation

In a "one-to-many" relationship between two tables, the foreign keys are stored in the child table. The child table is the table that contains multiple records that are related to a single record in the parent table. The foreign key in the child table is used to establish the relationship between the child and parent tables, allowing for the retrieval of data from both tables based on the relationship.

44. The term "surrogate" is usually associated with

Foreign keys

Cross table relationships

Indexes

Primary keys

File systems

The term "surrogate" is usually associated with primary keys. A surrogate key is a unique identifier that is added to a table to serve as the primary key, instead of using a natural key. Surrogate keys are typically generated by the system and have no inherent meaning or relationship to the data they represent. They are commonly used in database design to improve performance, simplify data management, and ensure data integrity.

Explanation

The term "surrogate" is usually associated with primary keys. A surrogate key is a unique identifier that is added to a table to serve as the primary key, instead of using a natural key. Surrogate keys are typically generated by the system and have no inherent meaning or relationship to the data they represent. They are commonly used in database design to improve performance, simplify data management, and ensure data integrity.

45. A primary key created from two or more columns is referred to as a(n) ____ primary key

Qualitative

Composite

Value added

Non unique

Indexed

A primary key created from two or more columns is referred to as a composite primary key. This type of primary key is used when a single column cannot uniquely identify a record in a table. By combining multiple columns, a composite primary key ensures that each combination of values is unique and can be used to uniquely identify a record in the table.

Explanation

A primary key created from two or more columns is referred to as a composite primary key. This type of primary key is used when a single column cannot uniquely identify a record in a table. By combining multiple columns, a composite primary key ensures that each combination of values is unique and can be used to uniquely identify a record in the table.

46. You need to insert a new product in a Product table but the classification (a grouping such as "stationary") into which this product fits is yet to be determined. What would likely be entered for the classification code assuming it is a three position character field?

Nothing

N/A

Three spaces

High values

000 (three zeros)

In this scenario, since the classification for the new product is yet to be determined, it would make sense to not enter anything in the classification code field. This would indicate that the classification is unknown or not assigned yet.

Explanation

In this scenario, since the classification for the new product is yet to be determined, it would make sense to not enter anything in the classification code field. This would indicate that the classification is unknown or not assigned yet.

47. In a "one-to-many" relationship between two tables, foreign keys are stored in ____ tables

Either parent or child depending on the nature of the relationship

Parent

Special key

Child

In a "one-to-many" relationship between two tables, the foreign keys are stored in the child table. This is because in a one-to-many relationship, each record in the child table can have multiple related records in the parent table. Therefore, the foreign key in the child table is used to establish the connection between the child and parent tables.

Explanation

In a "one-to-many" relationship between two tables, the foreign keys are stored in the child table. This is because in a one-to-many relationship, each record in the child table can have multiple related records in the parent table. Therefore, the foreign key in the child table is used to establish the connection between the child and parent tables.

48. What is a key attribute

This central entity in an ERD

A property that uniquely describes an entity

Any property that is a Primary Key or a Foreign Key

The most common property of an entity

Any property that describes an entity

A key attribute is a property that uniquely describes an entity. In an ERD (Entity-Relationship Diagram), a key attribute is essential for identifying and distinguishing one entity from another. It can be either a Primary Key or a Foreign Key, as both types of keys are used to establish relationships between entities. While it is true that a key attribute is a common property of an entity, it is more specifically defined as a property that serves as a key in the database schema design.

Explanation

A key attribute is a property that uniquely describes an entity. In an ERD (Entity-Relationship Diagram), a key attribute is essential for identifying and distinguishing one entity from another. It can be either a Primary Key or a Foreign Key, as both types of keys are used to establish relationships between entities. While it is true that a key attribute is a common property of an entity, it is more specifically defined as a property that serves as a key in the database schema design.

49. In a relational database, is a synonym for column is

Row

Tuple

Table

Attribute

Record

In a relational database, an attribute is a synonym for a column. An attribute refers to a characteristic or property of an entity or object in a database table. It represents a specific piece of information that can be stored in a column. Therefore, the correct answer is Attribute.

Explanation

In a relational database, an attribute is a synonym for a column. An attribute refers to a characteristic or property of an entity or object in a database table. It represents a specific piece of information that can be stored in a column. Therefore, the correct answer is Attribute.

50. Select the FALSE statement

A table must have at least one column

In most commercial DBMS products, the maximum number of rows in a table is unlimited or very large

A table must have a least one row

Rows in a table are considered to be unsorted

In many commercial DMBS products, the maximum number of columns in a table is limited to 255

not-available-via-ai

Explanation

not-available-via-ai

51. EmployeeAge and EmployeeDateOfBirth are both non-key columns in a table. Given that the EmployeeAge can be obtained if one knows the EmployeeDateOfBirth, this would violate ____ normal form.

1st

2nd

3rd

4th

None of the above

This would violate the 3rd normal form. The 3rd normal form states that every non-key column in a table must depend on the key column(s) only and not on any other non-key column. In this case, the EmployeeAge column depends on the EmployeeDateOfBirth column, which is a non-key column, violating the 3rd normal form.

Explanation

This would violate the 3rd normal form. The 3rd normal form states that every non-key column in a table must depend on the key column(s) only and not on any other non-key column. In this case, the EmployeeAge column depends on the EmployeeDateOfBirth column, which is a non-key column, violating the 3rd normal form.

52. A CoursesTaught table has a primary key combining both a Course code and an Instructor code. If Instructor name is an attribute of this table, it would violate _____ normal form.

1st

2nd

3rd

4th

None of the above

If the Instructor name is an attribute of the CoursesTaught table, it would violate the 2nd normal form. The 2nd normal form states that a table should not have partial dependencies, meaning that all non-key attributes should be fully dependent on the entire primary key. In this case, the Instructor name is dependent only on the Instructor code, not on the entire primary key (Course code and Instructor code). Therefore, it violates the 2nd normal form.

Explanation

If the Instructor name is an attribute of the CoursesTaught table, it would violate the 2nd normal form. The 2nd normal form states that a table should not have partial dependencies, meaning that all non-key attributes should be fully dependent on the entire primary key. In this case, the Instructor name is dependent only on the Instructor code, not on the entire primary key (Course code and Instructor code). Therefore, it violates the 2nd normal form.

53. A Medical Clinic table contains one row per clinic. Each row contains attributes about all the doctors working in the clinic. This violates ____ normal form.

1st

2nd

3rd

4th

None of the above

The given scenario violates the 1st normal form. The 1st normal form requires that each attribute in a relation must have atomic values, meaning that it should not contain multiple values or repeating groups. In this case, having multiple attributes about doctors in a single row violates this rule. To normalize the table and achieve 1st normal form, the attributes about doctors should be separated into a separate table with a foreign key referencing the clinic table.

Explanation

The given scenario violates the 1st normal form. The 1st normal form requires that each attribute in a relation must have atomic values, meaning that it should not contain multiple values or repeating groups. In this case, having multiple attributes about doctors in a single row violates this rule. To normalize the table and achieve 1st normal form, the attributes about doctors should be separated into a separate table with a foreign key referencing the clinic table.

54. A relational table has a primary key consisting of VIN and EngineCode. That is, each row in the table will contain data about a particular automobile and its engine. If a new engine is designed and built, it could not be added to the database unless it was placed in a particular automobile. This is an example of a(n) ______ anomaly.

Redundancy

Insertion

Deletion

Modification

None of the above

This is an example of an insertion anomaly because a new engine cannot be added to the database unless it is placed in a particular automobile. This means that if a new engine is designed and built, it cannot be inserted into the table without also specifying the corresponding automobile.

Explanation

This is an example of an insertion anomaly because a new engine cannot be added to the database unless it is placed in a particular automobile. This means that if a new engine is designed and built, it cannot be inserted into the table without also specifying the corresponding automobile.

55. A relational table has a primary key consisting of WareHouseNumber and ProductNumber. That is, each row in the table will contain data about a product stored in a warehouse. One of the columns is ProductDescription. If a product description changes, multiple rows will have to be changed assuming that the product is stored in more than one warehouse. This is an example of a(n) ____ anomaly.

Redundancy

Insertion

Deletion

Modification

None of the above

The given scenario describes a modification anomaly. This is because when a product description changes, it will have to be updated in multiple rows of the table, leading to redundant data and the potential for inconsistencies if the modification is not made correctly in all affected rows.

Explanation

The given scenario describes a modification anomaly. This is because when a product description changes, it will have to be updated in multiple rows of the table, leading to redundant data and the potential for inconsistencies if the modification is not made correctly in all affected rows.

56. If EmployeeAge and EmployeeDateOfBirth are both non-key columns in a table and, given that logically the EmployeeAge can be obtained if one knows the EmployeeDateOfBirth, this is an example of a(n) _____ dependency.

Inverse

Transitive

Derived

Partial

Relational

This is an example of a derived dependency because the EmployeeAge can be derived or calculated from the EmployeeDateOfBirth column. In other words, the value of EmployeeAge is not stored directly in the table, but can be obtained by performing a calculation or function using the EmployeeDateOfBirth column.

Explanation

This is an example of a derived dependency because the EmployeeAge can be derived or calculated from the EmployeeDateOfBirth column. In other words, the value of EmployeeAge is not stored directly in the table, but can be obtained by performing a calculation or function using the EmployeeDateOfBirth column.

57. An application that uses SQL is portable from one DBMS to another

Rarely, since there are so many standards and levels of compliance

Usually, but will likely require significant changes

Never, since SQL is unique to IBM

Always, since SQL is standardized

Usually, with minor modifications required

An application that uses SQL is usually portable from one DBMS (Database Management System) to another, but it may require some minor modifications. SQL (Structured Query Language) is standardized, meaning that it follows a set of rules and conventions that are widely accepted in the industry. However, different DBMS may have slight variations in their implementation of SQL or support for certain features. Therefore, when moving an application from one DBMS to another, some adjustments might be necessary to ensure compatibility and optimal performance.

Explanation

An application that uses SQL is usually portable from one DBMS (Database Management System) to another, but it may require some minor modifications. SQL (Structured Query Language) is standardized, meaning that it follows a set of rules and conventions that are widely accepted in the industry. However, different DBMS may have slight variations in their implementation of SQL or support for certain features. Therefore, when moving an application from one DBMS to another, some adjustments might be necessary to ensure compatibility and optimal performance.

58. To create an equi-join based on the "Manages" relationship, which primary key/foreign key combination should be used?

Mgr and Manager

SalesRep and Mgr

Offices and RepOffice

SalesRep and Manager

To create an equi-join based on the "Manages" relationship, the primary key/foreign key combination that should be used is SalesRep and Mgr. This means that the SalesRep attribute in one table should match the Mgr attribute in another table, indicating that a sales representative is managed by a particular manager.

Explanation

To create an equi-join based on the "Manages" relationship, the primary key/foreign key combination that should be used is SalesRep and Mgr. This means that the SalesRep attribute in one table should match the Mgr attribute in another table, indicating that a sales representative is managed by a particular manager.

59. Which of the following is not a type of join discussed in class?

Equi-Join

Full-Inner Join

Non-Equi Join

Cartesian Join

The correct answer is Full-Inner Join. In the question, we are asked to identify the type of join that was not discussed in class. However, Full-Inner Join is not a valid type of join in database terminology. Inner Join and Full Outer Join are valid types of joins, but Full-Inner Join does not exist. Therefore, Full-Inner Join is the correct answer.

Explanation

The correct answer is Full-Inner Join. In the question, we are asked to identify the type of join that was not discussed in class. However, Full-Inner Join is not a valid type of join in database terminology. Inner Join and Full Outer Join are valid types of joins, but Full-Inner Join does not exist. Therefore, Full-Inner Join is the correct answer.

60. What is the name of the function that can convert the value of any column to upper case?

LOWER(columnname)

UPPER(columnname)

Column data cannot be converted to upper case

TO_UPPER(columnname)

The function UPPER(columnname) is used to convert the value of any column to upper case. This function takes the input value from the specified column and returns the same value with all characters converted to uppercase. Therefore, it is the correct answer for the given question.

Explanation

The function UPPER(columnname) is used to convert the value of any column to upper case. This function takes the input value from the specified column and returns the same value with all characters converted to uppercase. Therefore, it is the correct answer for the given question.

61. What is the name of the symbolic constant that can be used in queries represent the current date?

TODAY

CURRENTDATE

CURRENT_DATE

TODAY()

The symbolic constant CURRENT_DATE can be used in queries to represent the current date. It is a built-in function in many database management systems that returns the current date in the format YYYY-MM-DD. By using this constant in queries, one can easily filter or retrieve data based on the current date.

Explanation

The symbolic constant CURRENT_DATE can be used in queries to represent the current date. It is a built-in function in many database management systems that returns the current date in the format YYYY-MM-DD. By using this constant in queries, one can easily filter or retrieve data based on the current date.

62. Which of the following is an easier way to write this WHERE clause?WHERE Quota >= 2000AND Quota <= 3000

WHERE Quota BETWEEN 2000 AND 3000

WHERE Quota BETWEEN 1999 AND 3001

WHERE Quota IS BETWEEN 2000 AND 3000

WHERE Quota >= 2000 AND

The correct answer is "WHERE Quota BETWEEN 2000 AND 3000". This is the easier way to write the WHERE clause because it simplifies the condition by using the BETWEEN operator. It specifies that the Quota should be between 2000 and 3000, inclusive. This is more concise and easier to read compared to the other options provided.

Explanation

The correct answer is "WHERE Quota BETWEEN 2000 AND 3000". This is the easier way to write the WHERE clause because it simplifies the condition by using the BETWEEN operator. It specifies that the Quota should be between 2000 and 3000, inclusive. This is more concise and easier to read compared to the other options provided.

63. Given the following select statement:SELECT NameFROM SalesRepsWHERE Name LIKE '_a_';

JanDanMan

FranStanDan

AnnDanMan

JanDanielMan

The given select statement is filtering the rows from the SalesReps table where the Name column has a pattern of any character, followed by 'a', followed by any character. The three names that match this pattern are Jan, Dan, and Man. Therefore, the correct answer includes these three names.

Explanation

The given select statement is filtering the rows from the SalesReps table where the Name column has a pattern of any character, followed by 'a', followed by any character. The three names that match this pattern are Jan, Dan, and Man. Therefore, the correct answer includes these three names.

64. Which of the following statements will correctly select all records from the City column of the Offices table, while changing the column header to "City Name Is"?

SELECT City as "City Name Is"FROM Offices;

SELECT 'City Name Is', CityFROM Offices;

SELECT "City Name Is", CityFROM Offices;

SELECT City as 'City Name Is'FROM Offices;

This statement correctly selects all records from the City column of the Offices table and changes the column header to "City Name Is" by using the alias "City Name Is" for the City column in the SELECT statement.

Explanation

This statement correctly selects all records from the City column of the Offices table and changes the column header to "City Name Is" by using the alias "City Name Is" for the City column in the SELECT statement.

65. What command will list all columns (and their datatypes) of a table?

SHOW

SELECT *

LIST *

DESCRIBE

The DESCRIBE command is used to display the structure of a table, including all columns and their data types. It provides a concise way to view the metadata of a table without retrieving any actual data. By using the DESCRIBE command, users can quickly understand the layout and characteristics of a table, making it a useful tool for database administrators and developers.

Explanation

The DESCRIBE command is used to display the structure of a table, including all columns and their data types. It provides a concise way to view the metadata of a table without retrieving any actual data. By using the DESCRIBE command, users can quickly understand the layout and characteristics of a table, making it a useful tool for database administrators and developers.

66. One of the following types of constraints is not added to a table using a 'CONSTRAINT' clause. Which one is it?

CHECK

FOREIGN KEY

NULL

UNIQUE

PRIMARY KEY

The correct answer is NULL. The NULL constraint is not added to a table using a CONSTRAINT clause. The NULL constraint is used to specify that a column can contain NULL values, indicating that the column does not have to have a value. It is typically specified when creating or altering a table column, rather than using a CONSTRAINT clause.

Explanation

The correct answer is NULL. The NULL constraint is not added to a table using a CONSTRAINT clause. The NULL constraint is used to specify that a column can contain NULL values, indicating that the column does not have to have a value. It is typically specified when creating or altering a table column, rather than using a CONSTRAINT clause.

67. Select the FALSE statement

Each table can have only one primary key

Tables may have zero, one, or more foreign keys

When defining a parent/child relationship, the ON DELETE clause is specified in one child.

A table may have multiple candidate keys from which the primary key is chosen

The statement "Each table can have only one primary key" is false. A table can have multiple primary keys, known as a composite key, which consists of two or more columns that uniquely identify a row in the table.

Explanation

The statement "Each table can have only one primary key" is false. A table can have multiple primary keys, known as a composite key, which consists of two or more columns that uniquely identify a row in the table.

68. What advantage is there to naming constraints?

It makes understanding certain error messages easier

The same constraint name can be used more than once

The constraint can be selectively overridden when new rows are added to a table

Naming constraints can make understanding certain error messages easier because when a constraint violation occurs, the error message will include the name of the constraint that was violated. This allows the developer to quickly identify which constraint caused the error and makes troubleshooting and debugging easier. Without named constraints, the error message would only provide generic information about the violation, making it more difficult to pinpoint the exact cause of the error.

Explanation

Naming constraints can make understanding certain error messages easier because when a constraint violation occurs, the error message will include the name of the constraint that was violated. This allows the developer to quickly identify which constraint caused the error and makes troubleshooting and debugging easier. Without named constraints, the error message would only provide generic information about the violation, making it more difficult to pinpoint the exact cause of the error.

69. What is the maximum size of a table name?

20

30

50

200

The maximum size of a table name is 20 characters. This means that the name of a table cannot exceed 20 characters in length.

Explanation

The maximum size of a table name is 20 characters. This means that the name of a table cannot exceed 20 characters in length.

70. SQL is a(n)

OO language ilke Java

Language designed for relational databases

Database engine

Programming language unique to Microsoft

3rd generation language like COBOL

SQL is a language designed specifically for managing and manipulating relational databases. It provides a standard set of commands and syntax for creating, querying, updating, and deleting data in a structured manner. While it shares some similarities with programming languages like Java, it is primarily focused on database operations rather than general-purpose programming. Therefore, SQL is considered a language designed for relational databases.

Explanation

SQL is a language designed specifically for managing and manipulating relational databases. It provides a standard set of commands and syntax for creating, querying, updating, and deleting data in a structured manner. While it shares some similarities with programming languages like Java, it is primarily focused on database operations rather than general-purpose programming. Therefore, SQL is considered a language designed for relational databases.

71. SQL provides

Data selection and retrieval

Data definition

Data integrity

Access control

All of the above

SQL provides all of the mentioned functionalities. Data selection and retrieval is one of the key features of SQL, allowing users to query and retrieve specific data from a database. Data definition involves creating and modifying database objects such as tables, views, and indexes. Data integrity ensures that the data stored in the database is accurate and consistent. Access control allows administrators to manage user permissions and restrict access to sensitive data. Therefore, SQL provides all of these functionalities.

Explanation

SQL provides all of the mentioned functionalities. Data selection and retrieval is one of the key features of SQL, allowing users to query and retrieve specific data from a database. Data definition involves creating and modifying database objects such as tables, views, and indexes. Data integrity ensures that the data stored in the database is accurate and consistent. Access control allows administrators to manage user permissions and restrict access to sensitive data. Therefore, SQL provides all of these functionalities.

72. In Oracle, when a column is declared as a CHAR(x) data type, the maximum value of x is

32,767

2000

2,147,483,648

4000

Unlimited

When a column is declared as a CHAR(x) data type in Oracle, the maximum value of x is 2000. This means that the column can store up to 2000 characters.

Explanation

When a column is declared as a CHAR(x) data type in Oracle, the maximum value of x is 2000. This means that the column can store up to 2000 characters.

73. When inserting a new row with a date, and you want to insert the date today, you can use the symbolic constant 'TODAY'

True

False

The statement is false because there is no symbolic constant 'TODAY' in SQL to insert the current date. Instead, you can use functions like GETDATE() or CURRENT_DATE() to insert the current date into a new row.

Explanation

The statement is false because there is no symbolic constant 'TODAY' in SQL to insert the current date. Instead, you can use functions like GETDATE() or CURRENT_DATE() to insert the current date into a new row.

74. A relational table has a primary key consisting of ProductNumber. On each row, non-key attributes WareHouseNumber and the warehouse address information are also found. That is, each row in the table will contain data about a product stored in a warehouse. If the last product in a particular warehouse is removed from the database, it results in the loss of all the information of the warehouse where it is located. This is an example of a(n) ______ anomaly.

Redundancy

Insertion

Deletion

Modification

None of the above

This scenario describes a deletion anomaly. A deletion anomaly occurs when removing a specific record from a table results in the loss of other related information that is still valid and necessary. In this case, if the last product in a warehouse is deleted, all the information about that warehouse, including the warehouse address, will be lost. This is an example of a deletion anomaly because the removal of one record causes the loss of other related data.

Explanation

This scenario describes a deletion anomaly. A deletion anomaly occurs when removing a specific record from a table results in the loss of other related information that is still valid and necessary. In this case, if the last product in a warehouse is deleted, all the information about that warehouse, including the warehouse address, will be lost. This is an example of a deletion anomaly because the removal of one record causes the loss of other related data.

75. DeptCode and DeptName are both non-key columns in a table. Given that logically the DeptName can be obtained if one knows the DeptCode, this would violate ____ normal form.

1st

2nd

3rd

4th

None of the above

In the given scenario, the fact that the DeptName can be obtained if one knows the DeptCode indicates a transitive dependency between these two columns. According to the rules of the third normal form (3NF), a table should not have any transitive dependencies. Therefore, if DeptCode and DeptName are both non-key columns and DeptName can be derived from DeptCode, it violates the 3NF.

Explanation

In the given scenario, the fact that the DeptName can be obtained if one knows the DeptCode indicates a transitive dependency between these two columns. According to the rules of the third normal form (3NF), a table should not have any transitive dependencies. Therefore, if DeptCode and DeptName are both non-key columns and DeptName can be derived from DeptCode, it violates the 3NF.

76. What is the correct order of execution of all the following SQL clauses?

HAVING

FROM

ORDER BY

GROUP BY

WHERE

SELECT

Submit

DBMS Quizzy Fo Shizzy

1. The ORDER BY clause cannot be used in a subquery

2.

What first name or nickname would you like us to use?

2. When revoking privileges on you objects, you can

3. In the Non-ANSI/ISO transaction model, a transaction can be partially rolled back to a named point.

4. In the ANSI/ISO transaction model, a program is always in transaction mode

5. When granting privileges, you can only grant

6. When deleting from a table, the DELETE statement must specify the columns on the table to delete. Example:DELETE: CustNumFROM CustomersWHERE CustNum = 100;

7. To update the column "Name" on a table to null, you use the following syntax:SET Name TO NULL;

8. The INSERT, DELETE, and UPDATE statements are considered what in SQL?

9. Select the statements that refer to Non-Correlated Sub-queries

10. Select the statements that refer to Correlated Sub-queries

11. What does the "WITH GRANT OPTION" clause of the GRANT statement do?

12. A subquery can return many columns of data as its query result

13. Select the true statements about HAVING clauses from the list below. (There are more than one)

14. A SELECT statement that uses the column functions can also include a WHERE clause

15. Select the true statement about column functions:

16. When using the AVG() column function, NULL values are included in the calculation

17. Which column function will count all records in a result set, regardless of the presence of null values?

18. If there is a RIGHT-OUTER JOIN between two tables, what will be returned?

19. If DeptCode and DeptName are both non-key columns in a table and, given that logically the Deptname can be obtained if one knows the DeptCode, this is an example of a(n) ______ dependancy.

20. What types of loops can you code in PL/SQL?

21. How often does a statement trigger execute?

22. Which phrase causes a trigger to be a row trigger?

23. The 'NEW' and 'OLD' keywords can be used by what kind of triggers?

24. The proper way to specify that a trigger should fire when a table is updated and/or deleted is

25. What function will remove the leading and trailing spaces from a char value?

26. Select the three items required in order to use a cursor

27. How many rows can a cursor return?

28. Given this function which is part of an SQL Select: to_char(Sales,'$9,990.00). If the value of Sales on the row is 4.95, the output produced would be

29. Does this FROM clauseFROM Offices LEFT OUTER JOIN SalesRepsMean the same thing as this FROM Clause?FROM SalesReps RIGHT OUTER JOIN Offices

30. What is the difference between a stored procedure and a stored function?

31. What is the correct syntax for assigning a value to a variable?

32. Where are working variables declared?

33. Some of the views in the System Catalog have a prefix "ALL_". What is found in these tables? (Select all that apply)

34. Some of the views in the System Catalog have a prefix "USER_". What is found in these tables ? (Select all that apply)

35. In Oracle's User_Constrains view, what indicates a foreign key in the Constraint_Type column?

36. In Oracle, owner of the system catalog is

37. In Oracle, the view that displays the tables you have created is called

38. The order of columns in a relational table (is)

39. If there is FULL-OUTER JOIN between two tables, what will be returned?

40. This diagram is an example of which type of ERD

41. Based on this diagram, select the false statement

42. A "thing" such as a Fixed Asset would likely be considered a(n) ___ in a relational model

43. In a "one-to-many" relationship between two tables, foreign keys are stored in ____ tables.

44. The term "surrogate" is usually associated with

45. A primary key created from two or more columns is referred to as a(n) ____ primary key

46. You need to insert a new product in a Product table but the classification (a grouping such as "stationary") into which this product fits is yet to be determined. What would likely be entered for the classification code assuming it is a three position character field?

47. In a "one-to-many" relationship between two tables, foreign keys are stored in ____ tables

48. What is a key attribute

49. In a relational database, is a synonym for column is

50. Select the FALSE statement

51. EmployeeAge and EmployeeDateOfBirth are both non-key columns in a table. Given that the EmployeeAge can be obtained if one knows the EmployeeDateOfBirth, this would violate ____ normal form.

52. A CoursesTaught table has a primary key combining both a Course code and an Instructor code. If Instructor name is an attribute of this table, it would violate _____ normal form.

53. A Medical Clinic table contains one row per clinic. Each row contains attributes about all the doctors working in the clinic. This violates ____ normal form.

56. If EmployeeAge and EmployeeDateOfBirth are both non-key columns in a table and, given that logically the EmployeeAge can be obtained if one knows the EmployeeDateOfBirth, this is an example of a(n) _____ dependency.

57. An application that uses SQL is portable from one DBMS to another

58. To create an equi-join based on the "Manages" relationship, which primary key/foreign key combination should be used?

59. Which of the following is not a type of join discussed in class?

60. What is the name of the function that can convert the value of any column to upper case?

61. What is the name of the symbolic constant that can be used in queries represent the current date?

62. Which of the following is an easier way to write this WHERE clause?WHERE Quota >= 2000AND Quota <= 3000

63. Given the following select statement:SELECT NameFROM SalesRepsWHERE Name LIKE '_a_';

64. Which of the following statements will correctly select all records from the City column of the Offices table, while changing the column header to "City Name Is"?

65. What command will list all columns (and their datatypes) of a table?

66. One of the following types of constraints is not added to a table using a 'CONSTRAINT' clause. Which one is it?

67. Select the FALSE statement

68. What advantage is there to naming constraints?

69. What is the maximum size of a table name?

70. SQL is a(n)

71. SQL provides

72. In Oracle, when a column is declared as a CHAR(x) data type, the maximum value of x is

73. When inserting a new row with a date, and you want to insert the date today, you can use the symbolic constant 'TODAY'

75. DeptCode and DeptName are both non-key columns in a table. Given that logically the DeptName can be obtained if one knows the DeptCode, this would violate ____ normal form.

76. What is the correct order of execution of all the following SQL clauses?

29. Does this FROM clause

FROM Offices LEFT OUTER JOIN SalesReps

Mean the same thing as this FROM Clause?

FROM SalesReps RIGHT OUTER JOIN Offices