SQL EXAM TESTING_TT PMVT (16/03/2021)

Which two statements are true regarding the USING and ON clauses in table joins?

• Both USING and ON clauses can be used for equijoins and nonequijoins.

• A maximum of one pair of columns can be joined between two tables using the ON clause

• The ON clause can be used to join tables on columns that have different names but compatible data types

• The WHERE clause can be used to apply additional conditions in SELECT statements containing the ON or the USING clause

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;

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

Evaluate the following query:

SQL> SELECT promo_name || q'{'s start date was }' || promo_begin_date AS "Promotion Launches"

FROM promotions;

What would be the outcome of the above query?

• It produces an error because flower braces have been used.

• It produces an error because the data types are not matching.

• It executes successfully and introduces an 's at the end of each promo_name in the output.

• It executes successfully and displays the literal " {'s start date was } " for each row in the output.

The following statements produce the same results:

DELETE FROM copy_emp;

TRUNCATE TABLE copy_emp;

• True

• False

Which three statements/commands would cause a transaction to end?

• COMMIT

• SELECT

• CREATE

• ROLLBACK

• SAVEPOINT

Which 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

• Return one result per set of rows

• May not modify the data type

Identify the 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 order of the result set.

• If you include a group function in a SELECT clause, you cannot select individual results as well.

When does a transaction complete?

• When a DELETE statement is executed

• When a ROLLBACK command is executed

• When a PL/SQL anonymous block is executed

• When a data definition language statement is executed

• When a TRUNCATE statement is executed after the pending transaction

Which of the following are valid operators for the WHERE clause?

• >=

• IS NULL

• !=

• IS LIKE

• IN BETWEEN