Advanced SQL Review Questions
Course Introduction
Advanced SQL delves into sophisticated concepts and techniques for managing and querying relational databases. The course covers advanced querying methods such as complex joins, subqueries, window functions, and common table expressions (CTEs). Students learn to optimize queries for performance, work with stored procedures, triggers, and user-defined functions, and address security and transaction management. Real-world scenarios, including data aggregation, pivoting, and handling large datasets, are explored through hands-on assignments. By mastering these advanced SQL features, students are equipped to handle challenging database tasks in professional environments.
Recommended Textbook
A Guide to SQL 8th Edition by Philip J. Pratt
Available Study Resources on Quizplus
8 Chapters
600 Verified Questions
600 Flashcards
Source URL: https://quizplus.com/study-set/1123 Page 2
Chapter 1: Introduction to Premiere Products, Henry Books, and Alexamara Marina Group
Available Study Resources on Quizplus for this Chatper
75 Verified Questions
75 Flashcards
Source URL: https://quizplus.com/quiz/22071
Sample Questions
Q1) Alexamara owns ____ marinas.
A) 2
B) 3
C) 4
D) 5
Answer: A
Q2) A(n) ____________________ is a structure that contains different categories of information and the relationships between these categories.
Answer: database
Q3) Each row in the ORDER_LINE table has an order number, part number, number ordered, quoted price, and total amount.
A)True
B)False
Answer: False
Q4) Why does the Premiere Products database use the table ORDERS instead of ORDER?
Answer: In some database systems, the word order has a special purpose. Having a table named ORDER could cause problems in such systems.
To view all questions and flashcards with answers, click on the resource link above. Page 3
Chapter 2: Database Design Fundamentals
Available Study Resources on Quizplus for this Chatper
75 Verified Questions
75 Flashcards
Source URL: https://quizplus.com/quiz/22072
Sample Questions
Q1) Because there is a one-to-many relationship between sales reps and customers in the Premiere Products database, one sales rep can be associated with zero, one, or more customers.
A)True
B)False
Answer: True
Q2) A relation is a characteristic or property of an entity.
A)True
B)False
Answer: False
Q3) A determinant is any column (or collection of columns) that determines another table.
A)True
B)False
Answer: False
Q4) The definition for a primary key really defines a candidate key as well.
A)True
B)False
Answer: True
To view all questions and flashcards with answers, click on the resource link above. Page 4
Chapter 3: Creating Tables
Available Study Resources on Quizplus for this Chatper
75 Verified Questions
75 Flashcards
Source URL: https://quizplus.com/quiz/22073
Sample Questions
Q1) In Access, use the Documenter tool to describe the tables in a database.
A)True
B)False
Answer: True
Q2) Microsoft SQL Server 2005 is a DBMS designed for use in client-server applications.
A)True
B)False
Answer: True
Q3) The correct data type for the BALANCE column in the CUSTOMER table is ____.
A) CHAR
B) INT
C) SMALLINT
D) DECIMAL
Answer: D
Q4) Should a user be allowed to enter null values for the primary key?
Answer: No, The primary key should uniquely identify a given row. This would be impossible if nulls were allowed.
Q5) SQL is Structured ____________________ Language.
Answer: Query
To view all questions and flashcards with answers, click on the resource link above. Page 5
Chapter 4: Single-Table Queries
Available Study Resources on Quizplus for this Chatper
75 Verified Questions
75 Flashcards
Source URL: https://quizplus.com/quiz/22074
Sample Questions
Q1) It is possible to use both a WHERE clause and a HAVING clause in a SELECT statement.
A)True
B)False
Q2) A query is a question represented in a way that the DBMS can understand.
A)True
B)False
Q3) You use the ____________________ clause to retrieve rows that satisfy some condition.
Q4) Describe simple and compound conditions.
Q5) The WHERE clause is used to limit the rows that are included in a query's result.
A)True
B)False
Q6) The GROUP BY clause sorts the data in a particular order.
A)True
B)False
Q7) To sort records in descending order, follow the name of the sort key with the ____________________ operator.
Q8) How do you construct the SELECT query?
Page 6
To view all questions and flashcards with answers, click on the resource link above.
Chapter 5: Multiple-Table Queries
Available Study Resources on Quizplus for this Chatper
75 Verified Questions
75 Flashcards
Source URL: https://quizplus.com/quiz/22075
Sample Questions
Q1) You can use the EXISTS operator to retrieve data from more than one table.
A)True
B)False
Q2) A join that compares the tables in the FROM clause and lists only those rows that satisfy the condition in the WHERE clause is called a(n) ____________________ join.
Q3) You can use the IN operator to retrieve data from multiple tables..
A)True
B)False
Q4) In a multiple-table query, you list all the desired columns in the ____________________ clause and qualify any columns that appear in more than one table.
Q5) When using a DBMS without an optimizer, the formulation of a query can make a difference in the speed with which the query is executed..
A)True
B)False
Q6) There are three set operations: union, intersection, difference. Define each of these operations. Which are supported by Oracle?
Q7) What steps should you take to construct the SQL command to join (relate) tables?
Page 7
To view all questions and flashcards with answers, click on the resource link above.
Chapter 6: Updating Data
Available Study Resources on Quizplus for this Chatper
75 Verified Questions
75 Flashcards
Source URL: https://quizplus.com/quiz/22076
Sample Questions
Q1) If you specified ____ for a column when you created a table, then changing a value in a column to null is prohibited.
A) NOT NULL
B) NULL
C) CHAR
D) NUMBER
Q2) There are table structure changes that are beyond the capabilities of SQL.
A)True
B)False
Q3) The ____ command is permanent.
A) SELECT
B) COMMIT
C) SAVE
D) UPDATE
Q4) Which of the following is a valid SQL command?
A) DROP TABLE LEVEL1_CUSTOMER;
B) DROP LEVEL1_CUSTOMER;
C) DELETE LEVEL1_CUSTOMER;
D) All of the above
Q5) Describe the format of the ALTER TABLE command to add a new column.
Page 8
To view all questions and flashcards with answers, click on the resource link above.
Chapter 7: Database Administration
Available Study Resources on Quizplus for this Chatper
75 Verified Questions
75 Flashcards
Source URL: https://quizplus.com/quiz/22077
Sample Questions
Q1) In a business organization, a person or an entire group known as the ____ is charged with managing the database.
A) database analyst
B) developer
C) database administrator
D) programmer
Q2) To ensure the uniqueness of values in a nonprimary key column, use the ____ command to create an index.
A) CREATE UNIQUE INDEX
B) CREATE NONPRIMARY INDEX
C) CREATE INDEX
D) CREATE ONLY INDEX
Q3) When you specify a foreign key, the table referenced by the foreign key is the ____.
A) super
B) sibling
C) parent
D) child
Q4) When a view is no longer needed, you can remove it using the ____________________ command.
To view all questions and flashcards with answers, click on the resource link above. Page 9
Chapter 8: SQL Functions and Procedures
Available Study Resources on Quizplus for this Chatper
75 Verified Questions
75 Flashcards
Source URL: https://quizplus.com/quiz/22078
Sample Questions
Q1) PL/SQL commands have a certain format and cannot include any blank lines in the code.
A)True
B)False
Q2) In PL/SQL, you can ensure that a variable has the same data type as a particular column by using the ____ attribute.
A) @VARIABLE
B) %VARIABLE
C) @DATATYPE
D) %TYPE
Q3) To call a procedure in SQL Server, use the ____ command.
A) CALL
B) RUN
C) DO
D) EXEC
Q4) Oracle uses ____________________ as an extension of SQL to accomplish procedural tasks.
Q5) A(n) ____________________ is a procedure that is executed automatically in response to an associated database operation, such as an INSERT, UPDATE, or DELETE command.
To view all questions and flashcards with answers, click on the resource link above. Page 10