Course Introduction
Advanced SQL Final Exam Questions
Advanced SQL delves deeper into the capabilities of Structured Query Language, exploring complex query writing and advanced data manipulation techniques. Topics include subqueries, joins, window functions, common table expressions (CTEs), set operations, and query optimization strategies. The course also covers transaction management, stored procedures, triggers, and best practices in database security and performance tuning. By working through real-world scenarios and projects, students refine their problem-solving skills and prepare to tackle sophisticated data challenges in professional settings.
Recommended Textbook
Oracle 11G SQL 2nd Edition by Joan Casteel
Available Study Resources on Quizplus
13 Chapters
1685 Verified Questions
1685 Flashcards
Source URL: https://quizplus.com/study-set/1085
Page 2
Chapter 1: Overview of Database Concepts
Available Study Resources on Quizplus for this Chatper
108 Verified Questions
108 Flashcards
Source URL: https://quizplus.com/quiz/21418
Sample Questions
Q1) What represents a characteristic or attribute that is being collected about an entity?
A) record
B) row
C) field
D) both a and b
Answer: C
Q2) A file is referred to as a(n) ____________________ in an Oracle10g database.
Answer: Table
Q3) Which of the following may contain repeating groups of data?
A) unnormalized data
B) first normal form (1NF)
C) second normal form (2NF)
D) third normal form (3NF)
Answer: A
Q4) A straight line with a crow's foot at one end depicts a(n) ____________________ relationship in an E-R Model.
Answer: one-to-many one to many
To view all questions and flashcards with answers, click on the resource link above. Page 3
Chapter 2: Basic Sql Select Statements
Available Study Resources on Quizplus for this Chatper
122 Verified Questions
122 Flashcards
Source URL: https://quizplus.com/quiz/21419
Sample Questions
Q1) Based upon the structure of the ORDERS table, which of the following is a valid SQL statement?
A) SELECT customer # FROM orders;
B) SELECT "order#", "customer#" FROM orders;
C) SELECT order#, orderdate DateOrdered FROM orders;
D) none of the above
Answer: C
Q2) In a(n) \(\underline {\text { SELECT } }\)statement, keywords, table names, and column names are not case sensitive. _________________________
A)True
B)False
Answer: True
Q3) The names of the columns to be retrieved are listed in the ____________________ clause.
Answer: SELECT
Q4) When a user asks the database a question, the user is issuing a query.
A)True
B)False
Answer: True
To view all questions and flashcards with answers, click on the resource link above. Page 4
Chapter 3: Table Creation and Management
Available Study Resources on Quizplus for this Chatper
132 Verified Questions
132 Flashcards
Source URL: https://quizplus.com/quiz/21420
Sample Questions
Q1) The ____________________ of a NUMBER column indicates the total number of digits that can be stored in the column.
Answer: precision
Q2) You cannot specify the width for a(n) \(\underline { \text { NUMBER } }\) column.
A)True
B)False
Answer: False
Q3) Which of the following datatypes refers to variable-length character data, where n represents the maximum length of the column?
A) CHAR(n)
B) BFILE
C) VARCHAR2(n)
D) LONG
Answer: C
Q4) The ____________________ command can be used to modify the structure of a table.
Answer: ALTER TABLE
Q5) The default format for a DATE column is ____________________.
Answer: DD-MON-YY
To view all questions and flashcards with answers, click on the resource link above. Page 5
Chapter 4: Constraints
Available Study Resources on Quizplus for this Chatper
132 Verified Questions
132 Flashcards
Source URL: https://quizplus.com/quiz/21421
Sample Questions
Q1) If a(n) ____________________ constraint is being dropped, then only the type of constraint needs to be identified in the ALTER TABLE because there can only be one such constraint for each table.
Q2) The MODIFY clause is used with the ALTER TABLE command to add a PRIMARY KEY constraint to an existing table.
A)True
B)False
Q3) The CONSTRAINT keyword is required if the user is going to assign a name to a constraint.
A)True
B)False
Q4) A constraint is always enforced at the ____________________ level.
Q5) Any type of constraint for a single column can be created at the\(\underline { \text { column } }\) level. _________________________
A)True
B)False
Q6) A constraint is always enforced at the table level.
A)True
B)False
To view all questions and flashcards with answers, click on the resource link above. Page 6
Chapter 5: Data Manipulation and Transaction Control
Available Study Resources on Quizplus for this Chatper
132 Verified Questions
132 Flashcards
Source URL: https://quizplus.com/quiz/21422
Sample Questions
Q1) A(n) ____________________ lock will allow other users to view the data stored in the table, but it prevents anyone from altering the structure of the table.
Q2) What is the difference between a shared and exclusive lock?
Q3) When new rows are being added to a table, the actual data being added are listed in the ____ clause.
A) INSERT INTO
B) DATA
C) VALUES
D) ADD
Q4) If the WHERE clause is omitted from the UPDATE command, then all columns in the table will be changed.
A)True
B)False
Q5) Which of the following commands allows a user to "undo" uncommitted changes to data?
A) UNDO
B) ROLLBACK
C) UNCOMMIT
D) UNLOCK
To view all questions and flashcards with answers, click on the resource link above. Page 7
Chapter 6: Additional Database Objects
Available Study Resources on Quizplus for this Chatper
132 Verified Questions
132 Flashcards
Source URL: https://quizplus.com/quiz/21423
Sample Questions
Q1) When creating a sequence, which of the following is not an optional clause?
A) INCREMENT BY value
B) CYCLE | NOCYCLE
C) MAXVALUE value | NOMAXVALUE
D) CREATE SEQUENCE
Q2) The NOCACHE option specifies that the sequence can generate a number only after a request has been made by a user.
A)True
B)False
Q3) By default, the START WITH clause has a value of one.
A)True
B)False
Q4) An index can be created with the ____________________ INDEX command.
Q5) A database index allows users and application programs to quickly locate specific records.
A)True
B)False
Q6) The ____________________ value of a sequence cannot be changed if the sequence has already generated a number greater than that value.
To view all questions and flashcards with answers, click on the resource link above. Page 8
Chapter 7: User Creation and Management
Available Study Resources on Quizplus for this Chatper
132 Verified Questions
132 Flashcards
Source URL: https://quizplus.com/quiz/21424
Sample Questions
Q1) Even if a set of privileges have been assigned to a role, it does not make it any easier for the DBA since the privileges will still need to be granted individually to the users. A)True B)False
Q2) Which of the following SQL statements will change the password for user RTHOMAS to ANEW5PASSWORD
A) ALTER USER rthomas PASSWORD TO anew5password; B) CHANGE USER rthomas IDENTIFIED BY anew5password; C) MODIFY USER rthomas PASSWORD TO anew5password; D) ALTER USER rthomas IDENTIFIED BY anew5password;
Q3) The ____ keywords are used to grant object privileges to users and roles.
A) GRANT...FOR...TO
B) CREATE...WITH...TO C) GRANT...WITH...TO D) GRANT...ON...TO
Q4) A user's password is specified in the ____________________ clause of the CREATE USER command.
Q5) The ____________________ command is used to remove a user account from the Oracle10g database.
To view all questions and flashcards with answers, click on the resource link above. Page 9
Chapter 8: Restricting Rows and Sorting Data
Available Study Resources on Quizplus for this Chatper
132 Verified Questions
132 Flashcards
Source URL: https://quizplus.com/quiz/21425
Sample Questions
Q1) When two conditions are joined by the AND keyword, both conditions must be \(\underline { \text { FALSE } }\)for a record to be included in the results.
A)True
B)False
Q2) NOT, AND, and OR are \(\underline {\text { comparison } }\) operators.
A)True
B)False
Q3) The ____________________ symbol is used for determining whether or not a value is greater than a specified search condition.
Q4) When sorting the results in ascending order, which of the following values will be presented last in the output?
A) character
B) numeric
C) NULL
D) date
Q5) The ____________________ comparison operator is used to search for NULL values.
Q6) What is the purpose of the WHERE clause?
Page 10
To view all questions and flashcards with answers, click on the resource link above.
Chapter 9: Joining Data From Multiple Tables
Available Study Resources on Quizplus for this Chatper
131 Verified Questions
131 Flashcards
Source URL: https://quizplus.com/quiz/21426
Sample Questions
Q1) In which of the following examples is the ORDERS table used as a column qualifier?
A) o.order#
B) orders.order#
C) order#.o
D) order#.orders
Q2) A(n) \(\underline {\text { non-equality }}\) join is used when there are no equivalent rows of data in the tables that are being joined.
A)True
B)False
Q3) Equality, non-equality, and self-joins are all categorized as inner joins.
A)True
B)False
Q4) A(n) \(\underline { \text { inner join }}\) will only include rows that have matching rows in the other table. _________________________
A)True
B)False
Q5) A(n) ____________________ is an alternate name temporarily assigned to a table.
Q6) A full outer join cannot be created in the ____________________ clause.
Page 11
To view all questions and flashcards with answers, click on the resource link above.
Chapter 10: Selected Single Row Functions
Available Study Resources on Quizplus for this Chatper
133 Verified Questions
133 Flashcards
Source URL: https://quizplus.com/quiz/21427
Sample Questions
Q1) The \(\underline {\text { ROUND } }\) function can be used to truncate numeric data. _________________________
A)True
B)False
Q2) The\(\underline{YR}\) element can be included in a format argument of the TO_CHAR function to spell out the year of the specified date.
A)True B)False
Q3) A Julian date is the number of days that have passed between a specific date and January 1, 4712 B.C.
A)True
B)False
Q4) A Julian date represents the number of \(\underline {\text { hours } }\) that have passed since January 1, 4712 B.C. _________________________
A)True B)False
Q5) Explain the difference between the TO_CHAR and TO_DATE functions when used with date values.
To view all questions and flashcards with answers, click on the resource link above. Page 12
Chapter 11: Group Functions
Available Study Resources on Quizplus for this Chatper
134 Verified Questions
134 Flashcards
Source URL: https://quizplus.com/quiz/21428
Sample Questions
Q1) Functions that return one result per group of rows processed are called ____________________ functions.
Q2) A(n) ____ clause cannot include a group function.
A) WHERE
B) HAVING
C) SELECT
D) both a and c
Q3) The ____ function returns the smallest value in a specified column.
A) LOW
B) LOWEST
C) MIN
D) MINIMUM
Q4) If a SELECT statement contains a GROUP BY clause, but no ORDER BY clause, the results will be presented in ____________________ order based on the column(s) listed in the GROUP BY clause.
Q5) Which of the following indicates the processing order for the indicated clauses?
A) WHERE, HAVING, GROUP BY
B) GROUP BY, HAVING, WHERE
C) WHERE, GROUP BY, HAVING
D) HAVING, WHERE, GROUP BY
To view all questions and flashcards with answers, click on the resource link above. Page 13
Chapter 12: Subqueries and Merge Statements
Available Study Resources on Quizplus for this Chatper
132 Verified Questions
132 Flashcards
Source URL: https://quizplus.com/quiz/21429
Sample Questions
Q1) If a subquery references a column from the outer query, then it is a(n) ____________________ subquery.
Q2) Which of the following terms refers to a type of subquery that is processed, or executed, once for each row in the outer query?
A) correlated subquery
B) uncorrelated subquery
C) multiple-column subquery
D) single-row subquery
Q3) A(n) \(\underline {\text { correlated }}\) subquery is when the outer query is executed first, and then the inner query is executed. _________________________
A)True
B)False
Q4) IN is not a valid operator for a multiple-column subquery.
A)True
B)False
Q5) Valid single-row operators include =, >, <, >=, <=.
A)True
B)False
Q6) The results of a subquery are passed back to the ____________________ query.
To view all questions and flashcards with answers, click on the resource link above. Page 14
Chapter 13: Views
Available Study Resources on Quizplus for this Chatper
133 Verified Questions
133 Flashcards
Source URL: https://quizplus.com/quiz/21430
Sample Questions
Q1) If a view was created with the WITH CHECK OPTION constraint, to remove the constraint you will need to ____.
A) use the ALTER VIEW command
B) re-create the view without the option
C) rename the view
D) drop the option
Q2) Any DML operation can be performed on a(n) ____________________ view as long as the view was not created with the WITH READ ONLY option and existing constraints are not violated.
Q3) Values cannot be inserted through a view into columns that are based on arithmetic expressions.
A)True
B)False
Q4) Column aliases can be used in the subquery to assign new column names to the columns contained in a view.
A)True
B)False
Q5) DML operations on a view created with the ____________________ keyword that is used to suppress duplicate data are not permitted.
To view all questions and flashcards with answers, click on the resource link above. Page 15