SQL Bootcamp
You execute this query: SELECT TO_CHAR (NEXT_DAY (LAST_DAY (SYSDATE), 'MON'), 'dd "Monday for" fmMonth rrrr') FROM DUAL; What is the result? A) It returns the date for the first Monday of the next month. B) It executes successfully but does not return any result. C) It generates an error. D) It returns the date for the last Monday of the current month.
A
Examine the description of the CUSTOMERS table: Name Null? Type ----------------------------------------------------------------------------- CUST_ID NOT NULL. NUMBER CUST_FIRST_NAME NOT NULL VARCHAR2 (20) CUST_LAST_NAME. NOT NULL VARCHAR2 (30) CUST_INCOME_LEVEL VARCHAR2 (30) CUST_CREDIT_LIMIT NUMBER For customers whose income level has a value, you want to display the first name and due amount as 5% of their credit limit. Customers whose due amount is null should not be displayed. Which query should be used? A) SELECT cust_first_name, cust_credit limit *.05 AS DUE_AMOUNT FROM customers WHERE cust income_level IS NOT NULL AND due_amount IS NOT NULL; B) SELECT cust_first_name, cust_credit_limit *.05 AS DUE_AMOUNT FROM customers WHERE cust_income_level != NULL AND cust_credit_level != NULL; C) SELECT cust_first_name, cust_credit_limit *.05 AS DUE_AMOUNT FROM customers WHERE cust_income level <> NULL AND due_amount <> NULL; D) SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE cust_income_level != NULL AND due_amount != NULL;
A
Examine the description of the EMPLOYEES table: Name Null? Type EMP_ID NOT NULL NUMBER EMP_NAME VARCHAR2 (40) DEPT_ID NUMBER (2) SALARY NUMBER (8, 2) JOIN_DATE DATE Which query is valid? A) SELECT dept_id, join_date, SUM (salary) FROM employees GROUP BY dept_id, join_date; B) SELECT dept_id, MAX (AVG (salary)) FROM employees GROUP BY dept_id; C) SELECT dept_id, join_date, SUM (salary) FROM employees GROUP BY dept_id: D) SELECT dept_id, AVG (MAX (salary)) FROM employees GROUP BY dept_id;
A
Examine the description of the PRODUCT INFORMATION table: Name Null? Туре PROD_ID NOT NULL NUMBER (2) PROD_NAME VARCHAR2 (10) LIST_PRICE NUMBER (6, 2) Which query retrieves the number of products with a null list price? A) SELECT COUNT (NVL (list_price, 0)) PROM product_information WHERE list_price is NULL; B) SELECT COUNT (list_price) FROM product_information WHERE list_price = NULL; C) SELECT COUNT (DISTINCT list_price) FROM product_information WHERE list_price IS NULL; D) SELECT COUNT (list_price) FROM product_information WHERE list price IS NULL;
A
Examine the description of the SALES1 table: Name Null Type SALES_ID NOT NULL NUMBER STORE_ID NOT NULL NUMBER ITEMS_ID NUMBER QUANTITY NUMBER SALES_DATE DATE SALES2 is a table with the same description as SALES1. Some sales data is duplicated in both tables. You want to display the rows from the SALES1 table which are not present in the SALES2 table. Which set operator generates the required output? A) MINUS B) INTERSECT C) UNION D) SUBTRACT E) UNION ALL
A
Which statement is true about TRUNCATE and DELETE? A) For large tables TRUNCATE is faster than DELETE. B) You can never DELETE rows from a table if foreign key constraints will be violated. C) For tables with multiple indexes and triggers DELETE is faster than TRUNCATE. D) You can never TRUNCATE a table if foreign key constraints will be violated.
A
The SALES table has columns PROD_ID and QUANTITY_SOLD of data type NUMBER. Which two queries execute successfully? A) SELECT COUNT (prod_id) FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id; B) SELECT prod_id FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id HAVING COUNT(*) > 10; C) SELECT prod_id FROM sales WHERE quantity sold > 55000 AND COUNT(*) > 10 GROUP BY prod_id HAVING COUNT(*) > 10; D) SELECT COUNT (prod_id) FROM sales GROUP BY prod_id WHERE quantity_sold > 55000; E) SELECT prod_id FROM sales WHERE quantity_sold > 55000 AND COUNT(*) > 10 GROUP BY COUNT(*) > 10;
AB
Which two statements are true? A) Both CASE and DECODE are functions. B) All conditions evaluated using DECODE can also be evaluated using CASE. C) DECODE is a function and CASE is not. D) Neither CASE nor DECODE is a function. E) All conditions evaluated using CASE can also be evaluated using DECODE. F) CASE is a function and DECODE is not
AB
Which three statements are true about Data Manipulation Language (DML)? A) UPDATE statements can have different subqueries to specify the values for each updated column. B) INSERT statements can insert NULLS explicitly into a column. C) DML statements require a primary key be defined on a table. D) INSERT INTO... SELECT... FROM statements automatically commit. E) DELETE statements can remove multiple rows based on multiple conditions.
ABE
Examine the description of the ORDER_ITEMS table:Name Null? TypeORDER_ID NUMBER (38)PRODUCT ID NUMBER (38)QUANTITY NUMBER (38)UNIT_PRICE NUMBER (10,2)Examine this incomplete query:SELECT DISTINCT quantity * unit_price total paidFROM order_itemsORDER BY <clause>; Which two can replace <clause> so the query completes successfully? A) total_paid B) quantity, unit_price C) quantity * unit_price D) quantity E) product_id
AC
Which two statements are true about TRUNCATE and DELETE? A) DELETE can use a WHERE clause to determine which row(s) should be removed. B) TRUNCATE leaves any indexes on the table in an UNUSABLE state. C) The result of a DELETE can be undone by issuing a ROLLBACK. D) TRUNCATE can use a WHERE clause to determine which row(s) should be removed. E) The result of a TRUNCATE can be undone by issuing a ROLLBACK.
AC
You need to calculate the number of days from 1st January 2019 until today. Dates are stored in the default format of DD-MON-RR. Which two queries give the required output? A) SELECT ROUND (SYSDATE - TO DATE ( '01/JANUARY/2019' )) FROM DUAL; B) SELECT TO_DATE (SYSDATE, 'DD/MONTH/YYYY') - '01/JANUARY/2019' FROM DUAL; C) SELECT SYSDATE - TO_DATE ( '01-JANUARY-2019' ) FROM DUAL; D) SELECT ROUND (SYSDATE - '01-JAN-2019' ) FROM DUAL; E) SELECT TO_CHAR (SYSDATE, 'DD-MON-YYYY' ) - '01-JAN-2019' FROM DUAL;
AC
Examine the description of the CUSTOMERS table: CUSTOMER_ID CUSTOMER NAME 10 MARK 20 Mandy 30 Mary 40 MARVIN 50 MARTIN Which two SELECT statements will return these results? CUSTOMER_NAME Mandy Mary A) SELECT customer name FROM customers WHERE customer name LIKE '%a%'; B) SELECT customer_name FROM customers WHERE customer_name LIKE 'Ma''; C) SELECT customer_name FROM customers WHERE UPPER (customer_name) LIKE "MA"; D) SELECT customer_name FROM customers WHERE customer_name LIKE 'Ma%'; E) SELECT customer_name FROM customers WHERE customer_name LIKE ' *Ma*'; F) SELECT customer name FROM customers WHERE UPPER (customer_name) LIKE 'MA'; G) SELECT customer_name FROM customers WHERE customer_name = ' *Ma**;
AD
Which two are true about a SQL statement using SET operators such as UNION? A)The data type of each column returned by the second query must exactly match the data type of the corresponding column returned by the first query. B)The data type of each column returned by the second query must be implicitly convertible to the data type of the corresponding column returned by the first query. C)The names and number of columns must be identical for all SELECT statements in the query. D)The number, but not names, of columns must be identical for all SELECT statements in the query. E)The data type group of each column returned by the second query must match the data type group of the corresponding column returned by the first query.
AD
Which two statements are true about an Oracle database? A) A VARCHAR2 column without data has a NULL value. B) A table can have multiple primary keys. C) A column definition can specify multiple data types. D) A table can have multiple foreign keys. E) A NUMBER column without data has a zero value.
AD
Which two statements are true regarding the UNION and UNION ALL operators? A) The number of columns selected in each SELECT statement must be identical. B) The output is sorted by the UNION ALL operator. C) Duplicates are eliminated automatically by the UNION ALL operator. D) NULLS are not ignored during duplicate checking. E) The names of columns selected in each SELECT statement must be identical.
AD
Which three are key components of an Entity Relationship Model? A) a relationship B) an activity C) a unique identifier D) an attribute E) a table F) an entity
ADF
Examine the description of the BOOKS table: Name Null? Type BOOK_ID NOT NULL NUMBER (4) BOOK_TITLE VARCHAR2 (250) PRICE NUMBER (5, 2) PURCHASE_DATE DATE AUTHOR_NAME VARCHAR2 (30) Examine these requirements: 1. Display book titles for books purchased before January 17, 2007 costing less than 500 or more than1000. 2. Sort the titles by date of purchase, starting with the most recently purchased book. Which two queries can be used? A) SELECT book_title FROM books WHERE (price <500 OR price 1000)AND (purchase date < 17-JAN-2007) ORDER BY purchase date DESC: B) SELECT book title FROM books WHERE (price BETWEEN 500 AND 1000)AND (purchase_date< '17-JAN-2007') ORDER BY purchase_date; C) SELECT book_titleFROM booksWHERE (price IN (500, 1000)}AND (purchase_date< "17-JAN-2007")ORDER BY purchase_date ASC; D) SELECT book_title FROM books WHERE (price < 500 OR > 1000) AND (purchase_date < '17-JAN-2007") ORDER BY purchase_date DESCA; E) SELECT book_title FROM books WHERE (price NOT BETWEEN 500 AND 1000) AND (purchase_date < '17-JAN-2007')ORDER BY purchase_date DESC;
AE
Which two are true about savepoints? A) After issuing a savepoint, you can roll back to the savepoint name within the current transaction. B) After issuing a savepoint, you cannot roll back the complete transaction. C) You can commit updates done between two savepoints without committing other updates in the current transaction. D) A ROLLBACK TO SAVEPOINT Command issued before the start of a transaction results in an error. E) They make uncommitted updates visible to other sessions owned by the same user. F) They make uncommitted updates visible to sessions owned by other users.
AE
Examine the data in the INVOICES table: INVOICE_ID CURRENCY_CODE RAISED_DATE 1 EUR 01-JAN-2019 2 USD 01-FEB-2019 3 JPY 01-MAR-2019 Examine the data in the CURRENCIES table: CURRENCY_CODE JPY GPB CAD EUR USD Which query returns the currencies in CURRENCIES that are not present in INVOICES? A) SELECT * FROM currencies WHERE NOT EXISTS ( SELECT NULL FROM invoices WHERE currency_code = currency_code); B) SELECT currency_code FROM currencies MINUS SELECT currency_code FROM invoices; C) SELECT currency_code FROM currencies INTERSECT SELECT currency_code FROM invoices; D) SELECT * FROM currencies MINUS SELECT FROM invoices;
B
Examine this data in the EMPLOYEES table: ID LAST_NAME SALARY DEPT_ID 1 Smith 1000 10 2 Jones 2000 10 3 Markham 1500 20 4 Black 1300 20 Which statement will execute successfully? A) SELECT dept_id, STDDEV (last_name), SUM (salary) FROM employees GROUP BY dept_id: B) SELECT dept_id, MAX (last_name), SUM (salary) FROM employees GROUP BY dept_id: C) SELECT dept_id, LENGTH (last_name), SUM (salary) FROM employees GROUP BY dept_id; D) SELECT dept_id, INSTR (last_name, 'A'), SUM (salary) FROM employees GROUP BYdept_id;
B
Examine this description of the EMP table: Name Null? Type EMPNO NOT NULL NUMBER (4) ENAME VARCHAR2 (10) SAL NUMBER (7, 2) DEPTNO NUMBER (2) You execute this query: SELECT deptno AS "departments", SUM (sal) AS "salary" FROM emp GROUP BY 1 HAVING SUM (sal) > 3000; What is the result? A) only departments where the total salary is greater than 3000, ordered by department B) an error C) all departments and a sum of the salaries of employees with a salary greater than 3000 D) only departments where the total salary is greater than 3000, returned in no particular order
B
Examine this description of the EMP table: Name Null? Type EMPNO NOT NULL NUMBER (4) ENAME VARCHAR2 (10)SAL NUMBER (7, 2) DEPTNO NUMBER (2) You execute this query: SELECT deptno AS "departments", SUM (sal) AS "salary" FROM emp GROUP BY 1 HAVING SUM (sal) > 3000;What is the result? A) only departments where the total salary is greater than 3000, ordered by department B) an error C) all departments and a sum of the salaries of employees with a salary greater than 3000 D) only departments where the total salary is greater than 3000, returned in no particular order
B
Examine this statement, which executes successfully: SELECT d. department_name, ROUND (AVG (NULLIF (e. salary,0))) AS avgsal, MAX(e. salary) AS maxsal FROM employees e JOIN departments d ON (e. department_id = d.department_id) GROUP BY d. department_name ORDER BY 2; In which order are the rows displayed? A) sorted by MAXSAL B) sorted by AVGSAL C) sorted by DEPARTMENT NAME and AVGSAL D) sorted by DEPARTMENT NAME and MAXSAL E) sorted by DEPARTMENT_NAME
B
Which is the default column or columns for sorting output from compound queries using SET operators such as INTERSECT in a SQL statement? A) the first NUMBER Or VARCHAR2 column in the last SELECT of the compound query B) the first column in the first SELECT of the compound query C) the first column in the last SELECT of the compound query D) the first VARCHAR2 column in the first SELECT of the compound query E) the first NUMBER column in the first SELECT of the compound query
B
Which statement will execute successfully? A) SELECT FROM DUAL UNION SELECT 2 FROM DUAL ORDER BY 1, 2; B) SELECT 1, 2 FROM DUAL UNION SELECT 3, 4 FROM DUAL ORDER BY 1, 2; C) SELECT 1, 2 FROM DUAL UNION SELECT 3, 4 PROM DUAL ORDER BY 3, 4; D) SELECT 3 FROM DUAL UNTON SELECT FROM DUAL ORDER BY 3;
B
PRODUCTS PROD_NAME CHAR (4) EXP_DATE TIMESTAMP (6) NEW_PRODUCTS: PROD_ID CHAR (4) PROD NAME VARCHAR2 (10) EXP_DATE DATE Which two queries execute successfully? A) SELECT prod_id FROM products UNION ALL SELECT prod_id, prod_name FROM new products; B) SELECT prod_id, prod_name FROM products INTERSECT SELECT 100, prod_name FROM new products; C) SELECT prod_id, exp_date FROM products UNION ALL SELECT prod_id, NULL FROM new_products; D) SELECT * FROM products UNION SELECT * FROM new products; E) SELECT * FROM products MINUS SELECT prod_id FROM new_products;
BC
Table ORDER_ITEMS contains columns ORDER_ID, UNIT PRICE and QUANTITY, of data type NUMBER. Examine these SQL statements: Statement 1: SELECT MAX (unit_price* quantity) "Maximum Order" FROM order_items; Statement 2: SELECT MAX (unit_price* quantity) "Maximum Order" FROM order_items GROUP BY order_id; Which two statements are true? A) Both statements will return NULL If either UNIT_PRICE or QUANTITY contains NULL. B) Statement 1 returns only one row of output. C) Statement 2 may return multiple rows of output. D) Both the statements give the same output. E) Statement 2 returns only one row of output.
BC
Which two are true about queries using set operators (UNION, UNION ALL, INTERSECT and MINUS)? A) None of the set operators can be used when selecting CLOB columns B) The FOR UPDATE clause cannot be specified. C) There must be an equal number of columns in each SELECT list. D) The name of each column in the first SELECT list must match the name of the corresponding column in each subsequent SELECT list, E) Each SELECT statement in the query can have an ORDER BY clause
BC
Examine this SQL statement: SELECT cust_id, cust_last name "Last Name" FROM customers WHERE country id = 10 UNION SELECT cust_id CUSTNO, cust_last_name FROM customers WHERE country_id = 30. Identify three ORDER BY clauses, any one of which can complete the query successfully. A) ORDER BY "CUST_NO" B) ORDER BY 2, 1 C) ORDER BY "Last Name" D) ORDER BY 2, cust_id E) ORDER BY CUST_NO
BCD
Examine these statements executed in a single Oracle session: CREATE TABLE product (pcode NUMBER (2), pname VARCHAR2 (20)); INSERT INTO product VALUES (1, 'pen'); INSERT INTO product VALUES (2, 'pencil'); INSERT INTO product VALUES (3, 'fountain pen'); SAVEPOINT a; UPDATE product SET pcode = 10 WHERE pcode = 1; COMMIT; DELETE FROM product WHERE pcode = 2; SAVEPOINT b; UPDATE product SET pcode = 30 WHERE pcode = 3; SAVEPOINT C; DELETE FROM product WHERE pcode = 10; ROLLBACK TO SAVEPOINT b ; COMMIT; Which three statements are true? A) There is no row containing pen. B) There is no row containing pencil. C) The code for fountain pen is 3. D) The code for pen is 1. E) The code for pen is 10. F) There is no row containing fountain pen.
BCE
No user-defined locks are used in your database. Which three are true about Transaction Control Language (TCL)? A) ROLLBACK without the TO SAVE POINT clause undoes all the transaction's changes but does not erase its savepoints. B) COMMIT ends the transaction and makes all its changes permanent. C) COMMIT erases all the transaction's savepoints and releases its locks. D) ROLLBACK TO SAVEPOINT undoes the transaction's changes made since the named savepoint and then ends the transaction. E) ROLLBACK without the TO SAVE POINT clause undoes all the transaction's changes, releases its locks, and erases all its savepoints. F) ROLLBACK without the TO SAVE POINT clause undoes all the transaction's changes but does not release its locks.
BCE
Which two statements are true about Entity Relationships? A) A many-to-many relationship can be implemented only by using foreign keys. B) A relationship can be mandatory for both entities. C) A one-to-one relationship is always a self-referencing relationship. D) A one-to-many relationship in one direction is a one-to-one relationship in the other direction. E) A table name can be specified just once when selecting data from a table having a self referencing relationship.
BD
Which two statements are true about the WHERE and HAVING clauses in a SELECT statement? A) WHERE and HAVING clauses can be used in the same statement only if applied to different table columns. B) The WHERE clause can be used to exclude rows before dividing them into groups. C) The WHERE clause can be used to exclude rows after dividing them into groups. D) The HAVING clause can be used with aggregating functions in subqueries. E) Aggregating functions and columns used in HAVING clauses must be specified in the SELECT list o
BD
You execute this command: TRUNCATE TABLE depts; Which two are true? A) A ROLLBACK statement can be used to retrieve the deleted data. B) It retains the indexes defined on the table. C) It drops any triggers defined on the table. D) It retains the integrity constraints defined on the table. E) A FLASHBACK TABLE statement can be used to retrieve the deleted data. F) It always retains the space used by the removed rows.
BD
Examine the data in the Emp table: ENO ENAME SAL DEPTNO 1001 John 12000 10 1002 Sam 40000 20 1003 Daniel 12000 20 1004 Andrea 5000 10 You execute this query: SELECT deptno AS "Department", AVG(sal) AS AverageSalary, MAX (sal) AS "Max Salary"FROM emp WHERE sal >= 12000 GROUP BY "Department" ORDER BY Average Salary: Why does an error occur? A) An alias name must always be specified in quotes. B) An alias name must not contain space characters. C) An alias name must not be used in a GROUP BY clause. D) An alias name must not be used in an ORDER BY clause.
C
Which two are true about rollbacks? A) Data consistency is not guaranteed after a rollback. B) The ROLLBACK statement does not release locks resulting from table updates. C) If the ROLLBACK statement is used without TO SAVE POINT, then all savepoints in the transaction are deleted. D) A transaction Interrupted by a system failure is automatically rolled back. E) Data Control Language (DCL) statements, such as GRANT and REVOKE, can be rolled back.
CD
Examine the description of the PROMOTIONS table: Name Null? Type PROMO_ID NOT NULL NUMBER (6) PROMO_NAME NOT NULL VARCHAR2 (30) PROMO_CATEGORY NOT NULL VARCHAR2 (30) PROMO_COST NOT NULL NUMBER (10, 2) You want to display the unique promotion costs in each promotion category. Which two queries can be used? A) SELECT DISTINCT promo_cost l l ' in ' I l DISTINCT promo_category FROM promotions ORDER BY 1; B) SELECT promo_category, DISTINCT promo_cost FROM promotions ORDER BY 2; C) SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1; D) SELECT promo_cost, promo_category FROM promotions ORDER BY by 1; E) SELECT DISTINCT promo_category I I 'has ' l I promo_cost AS COSTS FROM promotions ORDER BY 1;
CE
Which two are true about queries using set operators such as UNION? A) An expression in the first SELECT list must have a column alias for the expression. B) All set operators are valid on columns of all data types. C) Queries using set operators do not perform implicit conversion across data type groups (e.g. character, numeric). D) In a query containing multiple set operators, INTERSECT always takes precedence over UNION and UNION ALL. E) CHAR Columns of different lengths used with a set operator return a VARCHAR2 whose length equals the longest CHAR value.
CE
Which two statements are true about the ORDER BY clause? A) NULLS are not included in the sort operation. B) Numeric values are displayed in descending order if they have decimal positions. C) In a character sort, the values are case-sensitive. D) Only columns that are specified in the SELECT list can be used in the ORDER BY clause. E) Column aliases can be used in the ORDER BY clause.
CE
Which two statements are true regarding a SAVEPOINT? A) Rolling back to a SAVEPOINT can undo a TRUNCATE statement. B) Rolling back to a SAVE POINT can undo a CREATE INDEX statement. C) Rolling back to a SAVE POINT can undo a DELETE statement D) Only one SAVEPOINT may be issued in a transaction. E) A SAVEPOINT does not issue a COMMIT.
CE
Which two statements will convert the string Hello world to ello world? A) SELECT INITCAP (TRIM('H' FROM 'Hello World')) FROM DUAL; B) SELECT LOWER (SUBSTR ('Hello World', 2, 1)) FROM DUAL; C) SELECT LOWER (TRIM('H' FROM 'Hello World')) FROM DUAL; D) SELECT LOWER (SUBSTR('Hello World', 2)) FROM DUAL; E) SELECT SUBSTR('Hello World', 2) FROM DUAL;
CE
Examine the description of the MEMBERS table: Name Null? Type MEMBERID NOT NULL VARCHAR2 (6) FIRST_NAME VARCHAR2 (50) LAST_NAME NOT NULL VARCHAR2 (50) ADDRESS VARCHAR2 (50) CITY VARCHAR2 (25) Examine the partial query: SELECT city, last_name LNAME FROM members. You want to display all cities that contain the string AN. The cities must be returned in ascending order, with the last names further sorted in descending order. Which two clauses must you add to the query? A) WHERE city = "%AN%' B) ORDER BY last_name DESC, city ASC C) ORDER BY 1, LNAME DESC D) WHERE city IN ('%AN%')E) ORDER BY 1, 2 F) WHERE city LIKE '%AN%'
CF
Examine the description of the CUSTOMERS table: Name Null? Type CUST_ID NOT NULL VARCHAR2 (6) FIRST_NAME NOT NULL VARCHAR2 (50) LAST_NAME VARCHAR2 (50) ADDRESS VARCHAR2 (50) CITY VARCHAR2 (25) You want to display details of all customers who reside in cities starting with the letter D followed by at least two characters. Which query can be used? A) SELECT * FROM customers WHERE city LIKE 'D__' ; B) SELECT * FROM customers WHERE city = ' %D__' ; C) SELECT * FROM customers WHERE city = 'D__%' ; D) SELECT * FROM customers WHERE city LIKE 'D__%' ;
D
Examine the description of the EMPLOYEES table: Name Null? Type EMPLOYEE_ID NOT NULL NUMBER (4) EMPLOYEE_NAME NOT NULL VARCHAR2 (100) SALARY NOT NULL NUMBER (6,2) MANAGER_ID NUMBER (4) Examine these requirements: 1. Display the manager id and salary of the lowest paid employee for that manager. 2. Exclude anyone whose manager is not known. 3. Exclude any managers where the minimum salary is 6000 or less. 4. Sort the output by minimum salary with the highest salary shown first. Which statement will do this? A) SELECT manager_id, MIN (salary) FROM employees WHERE manager_id IS NOT NULL AND MIN (salary) > 6000 GROUP BY manager_id ORDER BY MIN (salary) DESC: B) SELECT manager_id, MIN (salary) FROM employees WHERE manager_id IS NOT NULL GROUP BY managerid HAVING MIN (salary) > 6000 ORDER BY MIN (salary); C) SELECT manager_id, MIN (salary) EROM employees HAVING MIN (salary) > 6000 WHERE manager id IS NOT NULL GROUP BY manager_id ORDER BY MIN (salary) DESC; D) SELECT manager id, MIN (salary) FROM employees WHERE manager_id IS NOT NULL GROUP BY manager_id HAVING MIN (salary) > 6000 ORDER BY MIN (salary) DESC;
D
Examine the description of the EMPLOYEES table: Name. Null? Type EMPLOYEE_ID. NOT NULL NUMBER (3) FIRST_NAME. VARCHAR2 (15) LAST_NAME. NOT NULL VARCHAR2 (15) SALARY. NUMBER (6,2) Which statement will execute successfully, returning distinct employees with non-null first names? A) SELECT DISTINCT * FROM employees WHERE first_name <> NULL; B) SELECT first_name, DISTINCT last_name FROM employees WHERE first_name IS NOT NULL; C) SELECT first_name, DISTINCT last_name FROM employees WHERE first_name <> NULL; D) SELECT DISTINCT * FROM employees WHERE first_name IS NOT NULL;
D
Examine these statements: CREATE TABLE alter_test (cl VARCHAR2 (10), c2 NUMBER (10)); INSERT INTO alter_test VALUES ('123', 123); COMMIT; Which is true about modifying the columns in ALTER_TEST? A) c1 can be changed to NUMBER (10) and c2 can be changed to VARCHAR2 (10). B) c2 can be changed to NUMBER (5) but ci cannot be changed to VARCHAR2(S). C) c1 can be changed to NUMBER (10) but c2 cannot be changed to VARCHAR2 (10). D) c1 can be changed to VARCHAR2 (5) and c2 can be changed to NUMBER (12,2). E) c2 can be changed to VARCHAR2 (10) but ci cannot be changed to NUMBER (10)
D
Examine this statement:SELECT 1 AS id, John' AS first_nameFROM dual UNION SELECT 1, 'John' AS nameFROM dual ORDER BY 1: What is returned upon execution? A) an error B) 2 rows C) O rows D) 1 row
D
Which two statements are true about the ORDER BY clause when used with a SQL statement containing a SET operator such as UNION? A) Each SELECT statement in the compound query must have its own ORDER BY clause.B) Each SELECT statement in the compound query can have its own ORDER BY clause. C) Column positions must be used in the ORDER BY clause. D) Only column names from the first SELECT statement in the compound query are recognized. E) The first column in the first SELECT of the compound query with the UNION operator is used by default to sort output in the absence of an ORDER BY clause.
DE
Which three statements are true about an ORDER BY clause? A) AN ORDER BY clause will always precede a HAVING clause if both are used in the same top-level query. B) An ORDER BY clause always sorts NULL values last. C) By default an ORDER BY clause sorts rows in descending order. D) By default an ORDER BY clause sorts rows in ascending order. E) AN ORDER BY clause can perform a linguistic sort. F) An ORDER BY clause can perform a binary sort.
DEF
