Oracle Database: SQL Fundamentals I - 1Z1-051 Free Exam Questions

A. Subqueries can contain ORDER BY but not the GROUP BY clause.

B. Only one column or expression can be compared between the main query and subquery.

C. Main query and subquery must get data from the same tables.

D. Main query and subquery can get data from different tables.

E. Subqueries can contain GROUP BY and ORDER BY clauses.

F. Multiple columns or expressions can be compared between the main query and subquery.

A. Sue, Alice, Reena, and Timber

B. Alice only

C. Alice, Reena, and Timber

D. Alice and Reena

A. It requires that at least one of the expressions in the list must have a NOT NULL value.

B. It can have a maximum of five expressions in a list.

C. It returns the highest NOT NULL value in the list for all rows.

D. It requires that all expressions in the list must be of the same data type.

A. SELECT cust_income_level ' ' cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers;

B. SELECT DISTINCT cust_income_level ' ' cust_credit_limit * 0.50 AS "50% Credit Limit"
FROM customers;

C. SELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50% Credit
Limit"
FROM customers;

D. SELECT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50% Credit Limit"
FROM customers;

A. DELETE FROM products WHERE prod_id = (SELECT prod_id FROM sales WHERE SYSDATE >= time_id - 3*365 );

B. DELETE FROM products WHERE prod_id IN (SELECT prod_id FROM sales WHERE SYSDATE - 3*365 >= time_id);

C. DELETE FROM products WHERE prod_id = (SELECT prod_id
FROM sales
WHERE time_id - 3*365 = SYSDATE );

D. DELETE FROM products WHERE prod_id IN (SELECT prod_id FROM sales WHERE time_id >= SYSDATE - 3*365 );

A. CREATE ROLE registrar; GRANT MODIFY ON student_grades TO registrar; GRANT registrar to user1, user2, user3

B. CREATE registrar; GRANT CHANGE ON student_grades TO registrar; GRANT registrar;

C. CREATE ROLE registrar; GRANT UPDATE ON student_grades TO registrar; GRANT ROLE registrar to user1, user2, user3

D. CREATE NEW ROLE registrar; GRANT ALL ON student_grades TO registrar; GRANT registrar to user1, user2, user3

E. CREATE ROLE registrar; GRANT UPDATE ON student_grades TO registrar; GRANT registrar to user1, user2, user3;

A. A subquery can be used only in SQL query statements.

B. A subquery can appear on either side of a comparison operator.

C. A subquery can retrieve zero or more rows.

D. Only two subqueries can be placed at one level.

E. There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.

A. The maximum value of descending sequence defaults to 1.

B. A sequence always increments by 1.

C. The start value of the sequence is always 1.

D. The minimum value of an ascending sequence defaults to 1.

A. DELETE FROM employees WHERE employee_id =(SELECT employee_id FROM employees);

B. DELETE FROM employees WHERE employee_id IN(SELECT employee_id FROM new_employees WHERE name = 'Carrey');

C. DELETE * FROM employees WHERE employee_id IN(SELECT employee_id FROM new_employees WHERE last_name = 'Carrey');

D. DELETE * FROM employees WHERE employee_id =(SELECT employee_id FROM new_employees);

A. You cannot use the EMP_ID_SEQ sequence to populate the JOB_ID column.

B. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEE_ID column.

C. The EMP_ID_SEQ sequence is invalidated when you modify the EMPLOYEE_ID column.

D. The EMP_ID_SEQ sequence is not affected by modifications to the EMPLOYEES table.

E. Any other column of NUMBER data type in your schema can use the EMP_ID_SEQ sequence.

F. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEES table.

A. It produces an error because the expression in the ORDER BY clause should also be specified in the SELECT clause.

B. It produces an error because the DESC option cannot be used with an expression in the ORDER BY clause.

C. It executes and produces the required result.

D. It produces an error because an expression cannot be used in the ORDER BY clause.

A. SELECT cust_city, cust_income_level, MAX(cust_credit_limit ) FROM customers GROUP BY cust_city, cust_income_level, cust_credit_limit;

B. SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customers GROUP BY cust_city, cust_income_level;

C. SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customers GROUP BY cust_credit_limit, cust_income_level, cust_city ;

D. SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customers GROUP BY cust_city, cust_income_level, MAX(cust_credit_limit);

A. SELECT student_name,subject1
FROM marks
WHERE subject1 > AVG(subject1);

B. SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1)
FROM marks
WHERE subject1 > subject2;

C. SELECT student_name,SUM(subject1)
FROM marks
WHERE student_name LIKE 'R%';

D. SELECT SUM(subject1+subject2+subject3)
FROM marks
WHERE student_name IS NULL;

A. It produces an error because conditions on the CUST_CREDIT_LIMIT column are not valid.

B. It produces an error because the condition on the CUST_CITY column is not valid.

C. It produces an error because the condition on the CUST_FIRST_NAME column is not valid.

D. It executes successfully.

A. SELECT promo_name, promo_cost FROM promotions WHERE (promo_category BETWEEN 'TV' AND 'internet') AND (promo_end_date IN ('15-MAR-00','15-OCT-00'));

B. SELECT promo_name, promo_cost FROM promotions WHERE promo_category = 'TV' OR promo_category ='internet' AND promo_end_date >='15MAR-00' ORpromo_end_date <='15-OCT-00';

C. SELECT promo_name, promo_cost FROM promotions WHERE (promo_category = 'TV' OR promo_category ='internet') AND (promo_end_date >='15MAR-00'AND promo_end_date <='15-OCT-00');

D. SELECT promo_name, promo_cost FROM promotions WHERE promo_category IN ('TV', 'internet') AND promo_end_date BETWEEN '15-MAR-00' AND '15-OCT-00';