WorksheetsOracle Database SQL Workshop
Total questions: 20
Worksheet time: 13mins
Identify the SELECT statements that execute successfully.
SELECT first_name, last_name, job_id, salary*12,
AS Yearly Sal
FROM employees;
SELECT first_name, last_name, job_id, salary*12
"yearly sal"
FROM employees;
SELECT first_name, last_name, job_id, salary AS
"yearly sal"
FROM employees;
SELECT first_name+last_name AS name, job_Id,
salary*12 yearly sal
FROM employees;
Which four of the following are valid operators for the WHERE clause?
>=
IS NULL
!=
IS LIKE
IN BETWEEN
Which four of the following statements are true about single-row functions?
Manipulate data items
Accept arguments and return one value per argument
Act on each row that is returned
Can be nested
Accept arguments that can be a column or an expression
The TO_NUMBER function converts either character strings or date values to a number in the format specified by the optional format model.
True
False
Identify the two guidelines for group functions and the GROUP BY clause.
You cannot use a column alias in the GROUP BY clause.
The GROUP BY column must be in the SELECT clause.
By using a WHERE clause, you can exclude rows before dividing them into groups.
The GROUP BY clause groups rows and ensures the order of the result set.
If you include a group function in a SELECT clause, you must include a GROUP BY
clause.
If you join a table to itself, what kind of join are you using?
Left OUTER join
Right OUTER join
Full OUTER join
Self-join
Natural join
Using a subquery is equivalent to performing two sequential queries and using the result of the first query as the search values in the second query.
True
False
Identify two set operator guidelines.
The expressions in the SELECT lists must match in number.
Parentheses cannot be used to alter the sequence of execution.
The data type of each column in the second query must match the data type of its
corresponding column in the first query.
The ORDER BY clause can be used only once in a compound query, unless a UNION
ALL operator is used.
The following statements produce the same results:
DELETE FROM copy_emp;
TRUNCATE TABLE copy_emp;
True
False
Identify three actions that you perform by using constraints.
Enforce rules on the data in a table whenever a row is inserted, updated, or deleted.
Prevent the dropping of a table.
Prevent the creation of a table.
Prevent the creation of data in a table.
The dictionary views that are based on the dictionary tables contain information such as:
Definitions of all the schema objects in the database
Default values for the columns
Integrity constraint information
Privileges and roles that each user has been granted
Indexes must be created manually and serve to speed up access to rows in a table
True
False
You cannot add data through a view if the view includes a GROUP BY clause.
True
False
A FOREIGN KEY constraint enforces the following action:
When the data in the parent key is deleted, all the rows in the child table that depend on the deleted parent key values are also deleted.
True
False
With a correlated subquery, the inner SELECT statement drives the outer SELECT statement.
True
False
Which of the following statements are true?
Subqueries are used to retrieve data by using an inline view.
Subqueries cannot be used to copy data from one table to another.
Subqueries update data in one table based on the values of another table.
Subqueries delete rows from one table based on rows in another table.
You can use a subquery in place of the table name in the ______ clause of the INSERT statement.
FROM
INTO
FOR UPDATE
VALUES
Which of the following statements are true?
After a user creates an object, the user can pass along any of the available object privileges to other users by using the GRANT statement.
A user can create roles by using the CREATE ROLE statement to pass along a collection
of system or object privileges to other users.
Users can change their own passwords.
Users can view the privileges granted to them and those that are granted on their
objects.
When you use the INSERT or UPDATE command, the DEFAULT keyword saves you from hard-coding the default value in your programs or querying the dictionary to find it.
True
False
The TIME_ZONE session parameter may be set to:
A relative offset
Database time zone
OS local time zone
A named region
