All Questions

CUBE will cross-reference the columns listed in the ______ clause to create a superset of groups.

You use GROUPING functions to:

CUBE can be applied to all aggregate functions including AVG, SUM, MIN, MAX, and COUNT. True or False?

GROUPING SETS is another extension to the GROUP BY clause and is used to specify multiple groupings of data but provide a single result set. True or False?

When using SET operators, the names of the matching columns must be identical in all of the SELECT statements used in the query. True or False?

When using SET operators, the number of columns and the data types of the columns must be identical in all of the SELECT statements used in the query. True or False.

The EMPLOYEES table contains these columns: ID_NUMBER NUMBER Primary Key NAME VARCHAR2 (30) DEPARTMENT_ID NUMBER SALARY NUMBER (7,2) HIRE_DATE DATE Evaluate this SQL statement: SELECT id_number, name, department_id, SUM(salary) FROM employees WHERE salary > 25000 GROUP BY department_id, id_number, name ORDER BY hire_date; Why will this statement cause an error?

Evaluate this SELECT statement: SELECT SUM(salary), department_id, manager_id FROM employees GROUP BY department_id, manager_id; Which SELECT clause allows you to restrict the rows returned, based on a group function?

The PRODUCTS table contains these columns: PRODUCT_ID NUMBER(9) PK CATEGORY_ID VARCHAR2(10) LOCATION_ID NUMBER(9) DESCRIPTION VARCHAR2(30) COST NUMBER(7,2) PRICE NUMBER(7,2) QUANTITY NUMBER You display the total of the extended costs for each product category by location. You need to include only the products that have a price less than $25.00. The extended cost of each item equals the quantity value multiplied by the cost value. Which SQL statement will display the desired result?

The PLAYERS and TEAMS tables contain these columns: PLAYERS PLAYER_ID NUMBER NOT NULL, PRIMARY KEY LAST_NAME VARCHAR2 (30) NOT NULL FIRST_NAME VARCHAR2 (25) NOT NULL TEAM_ID NUMBER POSITION VARCHAR2 (25) TEAMS TEAM_ID NUMBER NOT NULL, PRIMARY KEY TEAM_NAME VARCHAR2 (25) You need to create a report that lists the names of each team with more than three goal keepers. Which SELECT statement will produce the desired result?

If a select list contains both a column as well as a group function then what clause is required?

The PRODUCTS table contains these columns: PROD_ID NUMBER(4) PROD_NAME VARCHAR(20) PROD_CAT VARCHAR2(15) PROD_PRICE NUMBER(5) PROD_QTY NUMBER(4) You need to identify the minimum product price in each product category. Which statement could you use to accomplish this task?

Group functions can be nested to a depth of?

What will the following SQL Statement do? SELECT job_id, COUNT(*) FROM employees GROUP BY job_id;

The EMPLOYEES table contains the following columns: EMPLOYEE_ID NUMBER(10) PRIMARY KEY LAST_NAME VARCHAR2(20) FIRST_NAME VARCHAR2(20) DEPARTMENT VARCHAR2(20) HIRE_DATE DATE SALARY NUMBER(10) You want to create a report that includes each employee's last name, employee identification number, date of hire, and salary. The report should include only those employees who have been with the company for more than one year and whose salary exceeds $40,000. Which of the following SELECT statements will accomplish this task?

Which statement about group functions is true?

You want to write a report that returns the average salary of all employees in the company, sorted by departments. The EMPLOYEES table contains the following columns: EMPLOYEES: EMP_ID NUMBER(10) PRIMARY KEY LNAME VARCHAR2(20) FNAME VARCHAR2(20) DEPT VARCHAR2(20) HIRE_DATE DATE SALARY NUMBER(10) Which SELECT statement will return the information that you require?

Evaluate this SELECT statement: SELECT COUNT(employee_id), department_id FROM employees GROUP BY department_id; You only want to include employees who earn more than 15000. Which clause should you include in the SELECT statement?

What is the best explanation as to why this SQL statement will NOT execute? SELECT department_id "Department", AVG (salary)"Average" FROM employees GROUP BY Department;

Which comparison operator can only be used with a single-row subquery?

Which statement about the <> operator is true?

The EMPLOYEES and ORDERS tables contain these columns: EMPLOYEES EMPLOYEE_ID NUMBER(10) NOT NULL PRIMARY KEY FIRST_NAME VARCHAR2(30) LAST_NAME VARCHAR2(30) ADDRESS VARCHAR2(25) CITY VARCHAR2(20) STATE VARCHAR2(2) ZIP NUMBER(9) TELEPHONE NUMBER(10) ORDERS ORDER_ID NUMBER(10) NOT NULL PRIMARY KEY EMPLOYEE_ID NUMBER(10) NOT NULL FOREIGN KEY ORDER_DATE DATE TOTAL NUMBER(10) Which SELECT statement will return all orders generated by a sales representative named Franklin during the year 2001?

Which of the following is TRUE regarding the order of subquery execution?

The TEACHERS and CLASS_ASSIGNMENTS tables contain these columns: TEACHERS TEACHER_ID NUMBER(5) Primary Key NAME VARCHAR2 (25) SUBJECT_ID NUMBER(5) CLASS_ID NUMBER(5) CLASS_ASSIGNMENTS CLASS_ID NUMBER (5) Primary Key TEACHER_ID NUMBER (5) DATE MAX_CAPACITY NUMBER (3) All MAX_CAPACITY values are greater than 10. Which two SQL statements correctly use subqueries? (Choose two.) (Choose all correct answers)

You need to create a report to display the names of products with a cost value greater than the average cost of all products. Which SELECT statement should you use?

You need to display all the players whose salaries are greater than or equal to John Brown's salary. Which comparison operator should you use?

Using a subquery in which clause will return a syntax error?

Which statement about subqueries is true?

If you use the equality operator (=) with a subquery, how many values can the subquery return?

Which statement is false?

Which answer is INCORRECT? The parent statement of a correlated subquery can be:

The Oracle server performs a correlated subquery when the subquery references a column from a table referred to in the parent. True or False?

The WITH clause enables a SELECT statement to define the subquery block at the start of the query, process the block just once, label the results, and then refer to the results multiple times. True or False?

You need to create a SELECT statement that contains a multiple-row subquery. Which comparison operator(s) can you use?

Examine the data in the PAYMENT table: This statement fails when executed: FROM payment WHERE payment_id = (SELECT payment_id FROM payment WHERE payment_date >= '05-JAN-2002' OR payment_amount > 500.00); Which change could correct the problem?

Evaluate this SELECT statement: SELECT player_id, name FROM players WHERE team_id IN (SELECT team_id FROM teams WHERE team_id > 300 AND salary_cap > 400000); What would happen if the inner query returned a NULL value?

Assume all the column names are correct. The following SQL statement will execute which of the following? INSERT INTO departments (department_id, department_name, manager_id, location_id) VALUES (70, 'Public Relations', 100, 1700);

The PRODUCTS table contains these columns: PRODUCT_ID NUMBER NOT NULL PRODUCT_NAME VARCHAR2 (25) SUPPLIER_ID NUMBER NOT NULL LIST_PRICE NUMBER (7,2) COST NUMBER (5,2) QTY_IN_STOCK NUMBER(4) LAST_ORDER_DT DATE DEFAULT SYSDATE NOT NUL Which INSERT statement will execute successfully?

The STUDENTS table contains these columns: STU_ID NUMBER(9) NOT NULL LAST_NAME VARCHAR2 (30) NOT NULL FIRST_NAME VARCHAR2 (25) NOT NULL DOB DATE STU_TYPE_ID VARCHAR2(1) NOT NULL ENROLL_DATE DATE You create another table, named FT_STUDENTS, with an identical structure.You want to insert all full-time students who have a STU_TYPE_ID value of "F" into the new table. You execute this INSERT statement: INSERT INTO ft_students (SELECT stu_id, last_name, first_name, dob, stu_type_id, enroll_date FROM students WHERE UPPER(stu_type_id) = 'F'); What is the result of executing this INSERT statement?

You have been instructed to add a new customer to the CUSTOMERS table. Because the new customer has not had a credit check, you should not add an amount to the CREDIT column. The CUSTOMERS table contains these columns: CUST_ID NUMBER(10) COMPANY VARCHAR2(30) CREDIT NUMBER(10) POC VARCHAR2(30) LOCATION VARCHAR2(30) Which two INSERT statements will accomplish your objective? (Choose all correct answers)

Which two commands can be used to modify existing data in a database row? (Choose all correct answers)

You need to delete a record in the EMPLOYEES table for Tim Jones, whose unique employee identification number is 348. The EMPLOYEES table contains these columns: EMPLOYEE_ID NUMBER(5) PRIMARY KEY LAST_NAME VARCHAR2(20) FIRST_NAME VARCHAR2(20) ADDRESS VARCHAR2(30) PHONE NUMBER(10) Which DELETE statement will delete the appropriate record without deleting any additional records?

Which of the following represents the correct syntax for an INSERT statement?

What keyword in an UPDATE statement specifies the column that you want to change?

You need to update both the DEPARTMENT_ID and LOCATION_ID columns in the EMPLOYEES table using one UPDATE statement. Which clause should you include in the UPDATE statement to update multiple columns?

The EMPLOYEES table contains the following columns: EMPLOYEE_ID NUMBER(10) PRIMARY KEY LAST_NAME VARCHAR2(20) FAST_NAME VARCHAR2(20) DEPARTMENT_ID VARCHAR2(20) HIRE_DATE DATE SALARY NUMBER(9,2) BONUS NUMBER(9,2) You want to execute one DML statement to change the salary of all employees in department 10 to equal the new salary of employee number 89898. Currently, all employees in department 10 have the same salary value. Which statement should you execute?

When the WHERE clause is missing in a DELETE statement, what is the result?

What would happen if you issued a DELETE statement without a WHERE clause?

A column in a table can be given a default value. This option prevents NULL values from automatically being assigned to the column if a row is inserted without a specified value for the column. True or False?

Multi-table inserts can be conditional or unconditional. True or False?