DBMS Quizzy Fo Shizzy
- 1.
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.
- A.
Partial
- B.
Derived
- C.
Transitive
- D.
Inverse
- E.
Relational
Correct Answer
C. TransitiveExplanation
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.Rate this question:
-
- 2.
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.
- A.
Inverse
- B.
Transitive
- C.
Derived
- D.
Partial
- E.
Relational
Correct Answer
C. DerivedExplanation
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.Rate this question:
-
- 3.
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.
- A.
Redundancy
- B.
Insertion
- C.
Deletion
- D.
Modification
- E.
None of the above
Correct Answer
C. DeletionExplanation
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.Rate this question:
-
- 4.
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.
- A.
Redundancy
- B.
Insertion
- C.
Deletion
- D.
Modification
- E.
None of the above
Correct Answer
D. ModificationExplanation
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.Rate this question:
-
- 5.
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.
- A.
Redundancy
- B.
Insertion
- C.
Deletion
- D.
Modification
- E.
None of the above
Correct Answer
B. InsertionExplanation
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.Rate this question:
-
- 6.
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.
- A.
1st
- B.
2nd
- C.
3rd
- D.
4th
- E.
None of the above
Correct Answer
A. 1stExplanation
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.Rate this question:
-
- 7.
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.
- A.
1st
- B.
2nd
- C.
3rd
- D.
4th
- E.
None of the above
Correct Answer
B. 2ndExplanation
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.Rate this question:
-
- 8.
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.
- A.
1st
- B.
2nd
- C.
3rd
- D.
4th
- E.
None of the above
Correct Answer
C. 3rdExplanation
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.Rate this question:
-
- 9.
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.
- A.
1st
- B.
2nd
- C.
3rd
- D.
4th
- E.
None of the above
Correct Answer
C. 3rdExplanation
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.Rate this question:
-
- 10.
Select the FALSE statement
- A.
A table must have at least one column
- B.
In most commercial DBMS products, the maximum number of rows in a table is unlimited or very large
- C.
A table must have a least one row
- D.
Rows in a table are considered to be unsorted
- E.
In many commercial DMBS products, the maximum number of columns in a table is limited to 255
Correct Answer
C. A table must have a least one row -
- 11.
In a relational database, is a synonym for column is
- A.
Row
- B.
Tuple
- C.
Table
- D.
Attribute
- E.
Record
Correct Answer
D. AttributeExplanation
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.Rate this question:
-
- 12.
The order of columns in a relational table (is)
- A.
Based on the value of the foreign key
- B.
Optimized by the database engine
- C.
Based on the value of the primary key
- D.
Changes as data is inserted and deleted
- E.
Left to right as defined when the table is created
Correct Answer
E. Left to right as defined when the table is createdExplanation
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.Rate this question:
-
- 13.
In a "one-to-many" relationship between two tables, foreign keys are stored in ____ tables
- A.
Either parent or child depending on the nature of the relationship
- B.
Parent
- C.
Special key
- D.
Child
Correct Answer
D. ChildExplanation
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.Rate this question:
-
- 14.
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?
- A.
Nothing
- B.
N/A
- C.
Three spaces
- D.
High values
- E.
000 (three zeros)
Correct Answer
A. NothingExplanation
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.Rate this question:
-
- 15.
A primary key created from two or more columns is referred to as a(n) ____ primary key
- A.
Qualitative
- B.
Composite
- C.
Value added
- D.
Non unique
- E.
Indexed
Correct Answer
B. CompositeExplanation
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.Rate this question:
-
- 16.
The term "surrogate" is usually associated with
- A.
Foreign keys
- B.
Cross table relationships
- C.
Indexes
- D.
Primary keys
- E.
File systems
Correct Answer
D. Primary keysExplanation
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.Rate this question:
-
- 17.
In a "one-to-many" relationship between two tables, foreign keys are stored in ____ tables.
- A.
Either parent or child depending on the nature of the relationship
- B.
Special key
- C.
Parent
- D.
Child
Correct Answer
D. ChildExplanation
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.Rate this question:
-
- 18.
A "thing" such as a Fixed Asset would likely be considered a(n) ___ in a relational model
- A.
Value
- B.
Entity
- C.
Field
- D.
Columns
- E.
Attribute
Correct Answer
B. EntityExplanation
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.Rate this question:
-
- 19.
Based on this diagram, select the false statement
- A.
An employee may be working on several projects
- B.
A project may have more than one employee working on it
- C.
An employee may not be working on a project
- D.
An employee may be working on just one project
- E.
A project may have only one employee working on it
Correct Answer
A. An employee may be working on several projectsExplanation
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.Rate this question:
-
- 20.
This diagram is an example of which type of ERD
- A.
Key based ERD
- B.
Context ERD
- C.
Full Attribute ERD
- D.
This is not one of the defined ERD diagrams
- E.
Foreign Key ERD
Correct Answer
D. This is not one of the defined ERD diagrams -
- 21.
What is a key attribute
- A.
This central entity in an ERD
- B.
A property that uniquely describes an entity
- C.
Any property that is a Primary Key or a Foreign Key
- D.
The most common property of an entity
- E.
Any property that describes an entity
Correct Answer
C. Any property that is a Primary Key or a Foreign KeyExplanation
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.Rate this question:
-
- 22.
SQL provides
- A.
Data selection and retrieval
- B.
Data definition
- C.
Data integrity
- D.
Access control
- E.
All of the above
Correct Answer
E. All of the aboveExplanation
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.Rate this question:
-
- 23.
An application that uses SQL is portable from one DBMS to another
- A.
Rarely, since there are so many standards and levels of compliance
- B.
Usually, but will likely require significant changes
- C.
Never, since SQL is unique to IBM
- D.
Always, since SQL is standardized
- E.
Usually, with minor modifications required
Correct Answer
E. Usually, with minor modifications requiredExplanation
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.Rate this question:
-
- 24.
SQL is a(n)
- A.
OO language ilke Java
- B.
Language designed for relational databases
- C.
Database engine
- D.
Programming language unique to Microsoft
- E.
3rd generation language like COBOL
Correct Answer
B. Language designed for relational databasesExplanation
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.Rate this question:
-
- 25.
What is the maximum size of a table name?
- A.
20
- B.
30
- C.
50
- D.
200
Correct Answer
A. 20Explanation
The maximum size of a table name is 20 characters. This means that the name of a table cannot exceed 20 characters in length.Rate this question:
-
- 26.
In Oracle, when a column is declared as a CHAR(x) data type, the maximum value of x is
- A.
32,767
- B.
2000
- C.
2,147,483,648
- D.
4000
- E.
Unlimited
Correct Answer
B. 2000Explanation
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.Rate this question:
-
- 27.
What advantage is there to naming constraints?
- A.
The name can be used to assign the constraint to a second table without having to define the constraint in the second table
- B.
It makes understanding certain error messages easier
- C.
The same constraint name can be used more than once
- D.
The constraint can be selectively overridden when new rows are added to a table
- E.
It will eliminate the need to make any modification when porting SQL code from one product to another (e.g. Oracle to DB2)
Correct Answer
B. It makes understanding certain error messages easierExplanation
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.Rate this question:
-
- 28.
Select the FALSE statement
- A.
Each table can have only one primary key
- B.
Tables may have zero, one, or more foreign keys
- C.
When defining a parent/child relationship, the ON DELETE clause is specified in one child.
- D.
A table may have multiple candidate keys from which the primary key is chosen
- E.
A surrogate key is a number that is used by the data owners to identify particular rows (e.g. a student number)
Correct Answer
E. A surrogate key is a number that is used by the data owners to identify particular rows (e.g. a student number)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.Rate this question:
-
- 29.
One of the following types of constraints is not added to a table using a 'CONSTRAINT' clause. Which one is it?
- A.
CHECK
- B.
FOREIGN KEY
- C.
NULL
- D.
UNIQUE
- E.
PRIMARY KEY
Correct Answer
C. NULLExplanation
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.Rate this question:
-
- 30.
What command will list all columns (and their datatypes) of a table?
- A.
SHOW
- B.
SELECT *
- C.
LIST *
- D.
DESCRIBE
Correct Answer
D. DESCRIBEExplanation
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.Rate this question:
-
- 31.
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"?
- A.
SELECT City as "City Name Is" FROM Offices;
- B.
SELECT 'City Name Is', City FROM Offices;
- C.
SELECT "City Name Is", City FROM Offices;
- D.
SELECT City as 'City Name Is' FROM Offices;
Correct Answer
A. SELECT City as "City Name Is" FROM Offices;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.Rate this question:
-
- 32.
Given the following select statement:SELECT NameFROM SalesRepsWHERE Name LIKE '_a_';
- A.
Jan Dan Man
- B.
Fran Stan Dan
- C.
Ann Dan Man
- D.
Jan Daniel Man
Correct Answer
A. Jan Dan ManExplanation
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.Rate this question:
-
- 33.
Which of the following is an easier way to write this WHERE clause?WHERE Quota >= 2000AND Quota <= 3000
- A.
WHERE Quota BETWEEN 2000 AND 3000
- B.
WHERE Quota BETWEEN 1999 AND 3001
- C.
WHERE Quota IS BETWEEN 2000 AND 3000
- D.
WHERE Quota >= 2000 AND
Correct Answer
A. WHERE Quota BETWEEN 2000 AND 3000Explanation
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.Rate this question:
-
- 34.
What is the name of the symbolic constant that can be used in queries represent the current date?
- A.
TODAY
- B.
CURRENTDATE
- C.
CURRENT_DATE
- D.
TODAY()
Correct Answer
C. CURRENT_DATEExplanation
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.Rate this question:
-
- 35.
What is the name of the function that can convert the value of any column to upper case?
- A.
LOWER(columnname)
- B.
UPPER(columnname)
- C.
Column data cannot be converted to upper case
- D.
TO_UPPER(columnname)
Correct Answer
B. UPPER(columnname)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.Rate this question:
-
- 36.
Which of the following is not a type of join discussed in class?
- A.
Equi-Join
- B.
Full-Inner Join
- C.
Non-Equi Join
- D.
Cartesian Join
Correct Answer
B. Full-Inner JoinExplanation
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.Rate this question:
-
- 37.
To create an equi-join based on the "Manages" relationship, which primary key/foreign key combination should be used?
- A.
Mgr and Manager
- B.
SalesRep and Mgr
- C.
Offices and RepOffice
- D.
SalesRep and Manager
Correct Answer
B. SalesRep and MgrExplanation
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.Rate this question:
-
- 38.
If there is FULL-OUTER JOIN between two tables, what will be returned?
- A.
Return all the rows from both tables whether they have a match in the other or not
- B.
Return all rows where there is a match in both tables
- C.
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
- D.
Return all rows where there is a match in both tables, plus any rows in the left table on the FROM clause that do not have a match on the right table
Correct Answer
A. Return all the rows from both tables whether they have a match in the other or notExplanation
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.Rate this question:
-
- 39.
Does this FROM clauseFROM Offices LEFT OUTER JOIN SalesRepsMean the same thing as this FROM Clause?FROM SalesReps RIGHT OUTER JOIN Offices
- A.
True
- B.
False
Correct Answer
A. TrueExplanation
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.Rate this question:
-
- 40.
If there is a RIGHT-OUTER JOIN between two tables, what will be returned?
- A.
Return all rows where there is a match in both tables, plus any rows in the left table on the FROM clause that do not have a match on the right table
- B.
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.
- C.
Return all rows where there is a match in both tables
- D.
Return all the rows from both tables whether they have a match in the other or not
Correct Answer
B. 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.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.Rate this question:
-
- 41.
Which column function will count all records in a result set, regardless of the presence of null values?
- A.
COUNT(ColumnName)
- B.
Count(%)
- C.
Count()
- D.
Count(*)
Correct Answer
D. Count(*)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.Rate this question:
-
- 42.
When using the AVG() column function, NULL values are included in the calculation
- A.
True
- B.
False
Correct Answer
B. FalseExplanation
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.Rate this question:
-
- 43.
Select the true statement about column functions:
- A.
When using column functions, the WHERE clause cannot be used
- B.
A column function takes the values of two columns and adds them together to create a new value
- C.
A column function takes an entire row of data and summarizes it into a single data item
- D.
A column function takes an entire column of data and summarizes it into a single data item
Correct Answer
D. A column function takes an entire column of data and summarizes it into a single data itemExplanation
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.Rate this question:
-
- 44.
A SELECT statement that uses the column functions can also include a WHERE clause
- A.
True
- B.
False
Correct Answer
A. TrueExplanation
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.Rate this question:
-
- 45.
Select the true statements about HAVING clauses from the list below. (There are more than one)
- A.
HAVING is used to eliminate groups from the result set
- B.
If a column function is used on the HAVING clause, it will be calculated after the GROUP BY, creating additional processing.
- C.
HAVING clause normally consists of one or more of the column functions applied to each of the groups
- D.
HAVING is used to eliminate rows from the result set
Correct Answer(s)
A. HAVING is used to eliminate groups from the result set
C. HAVING clause normally consists of one or more of the column functions applied to each of the groupsExplanation
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.Rate this question:
-
- 46.
A subquery can return many columns of data as its query result
- A.
True
- B.
False
Correct Answer
B. FalseExplanation
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.Rate this question:
-
- 47.
The ORDER BY clause cannot be used in a subquery
- A.
True
- B.
False
Correct Answer
A. TrueExplanation
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.Rate this question:
-
- 48.
Select the statements that refer to Correlated Sub-queries
- A.
A query where the sub-query portion is executed many times
- B.
The sub-query executes once for every row returned by the outer query
- C.
The DBMS executes the inner query first, then the outer query
- D.
The sub-query contains an outer reference (a column from the sub-query that is also in a table from the outer query)
- E.
A query where the sub-query portion is executed only once
Correct Answer(s)
A. A query where the sub-query portion is executed many times
B. The sub-query executes once for every row returned by the outer query
D. The sub-query contains an outer reference (a column from the sub-query that is also in a table from 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.Rate this question:
-
- 49.
Select the statements that refer to Non-Correlated Sub-queries
- A.
A query where the sub-query portion is executed many times
- B.
The sub-query executes once for every row returned by the outer query
- C.
The DBMS executes the inner query first, then the outer query
- D.
The sub-query contains an outer reference (a column from the sub-query that is also in a table from the outer query)
- E.
A query where the sub-query portion is executed only once
Correct Answer(s)
C. The DBMS executes the inner query first, then the outer query
E. A query where the sub-query portion is executed only onceExplanation
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.Rate this question:
-
- 50.
The INSERT, DELETE, and UPDATE statements are considered what in SQL?
- A.
DML (Data Manipulation Language)
- B.
DDL (Data Definition Language)
- C.
Transactions
- D.
All the above
Correct Answer
A. 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).Rate this question:
-
Back to top