Database SQL Part 1 V1

Evaluate the following SQL statement:

SQL> select cust_id, cust_last_name "Last name"

FROM customers WHERE country_id = 10 UNION -

SELECT cust_id CUST_NO, cust_last_name FROM customers WHERE country_id = 30

Identify three ORDER BY clauses either one of which can complete the query.

• ORDER BY "Last name"

• ORDER BY 2, cust_id

• ORDER BY CUST_NO

• ORDER BY 2, 1

• ORDER BY "CUST_NO"

Which three statements are true regarding the WHERE and HAVING clauses in a SQL statement? (Choose three.)

• WHERE and HAVING clauses cannot be used together in a SQL statement.

• The HAVING clause conditions can have aggregate functions.

• The HAVING clause conditions can use aliases for the columns.

• The WHERE clause is used to exclude rows before the grouping of data.

• The HAVING clause is used to exclude one or more aggregated results after grouping data.

Which two statements are true about Data Manipulation Language (DML) statements?

• An INSERT INTO...VALUES.. statement can add multiple rows per execution to a table.

• An UPDATE... SET... statement can modify multiple rows based on multiple conditions on a table.

• A DELETE FROM..... statement can remove rows based on only a single condition on a table.

• An INSERT INTO... VALUES..... statement can add a single row based on multiple conditions on a table.

• A DELETE FROM..... statement can remove multiple rows based on multiple conditions on a table.

Examine the structure of the MEMBERS table. You want to display details of all members who reside in states starting with the letter A followed by exactly one character.

Which SQL statement must you execute?

• SELECT * FROM MEMBERS WHERE state LIKE '%A_';

• SELECT * FROM MEMBERS WHERE state LIKE 'A_';

• SELECT * FROM MEMBERS WHERE state LIKE 'A_%';

• SELECT * FROM MEMBERS WHERE state LIKE 'A%';

You want to display 5 percent of the rows from the SALES table for products with the lowest AMOUNT_SOLD and also want to include the rows that have the same even if this causes the output to exceed 5 percent of the rows.

AMOUNT_SOLD -

Which query will provide the required result?

• SELECT prod_id, cust_id, amount_sold FROM sales ORDER BY amount_sold FETCH FIRST 5 PERCENT ROWS WITH TIES;

• SELECT prod_id, cust_id, amount_sold FROM sales ORDER BY amount_sold FETCH FIRST 5 PERCENT ROWS ONLY WITH TIES;

• SELECT prod_id, cust_id, amount_sold FROM sales ORDER BY amount_sold FETCH FIRST 5 PERCENT ROWS WITH TIES ONLY;

• SELECT prod_id, cust_id, amount_sold FROM sales ORDER BY amount_sold FETCH FIRST 5 PERCENT ROWS ONLY;

Examine the structure of the MEMBERS table:

Name Null? Type

------------------ --------------- ------------------------------

MEMBER_ID NOT NULL VARCHAR2 (6)

FIRST_NAME VARCHAR2 (50)

LAST_NAME NOT NULL VARCHAR2 (50)

ADDRESS VARCHAR2 (50)

You execute the SQL statement:

SQL > SELECT member_id, ' ' , first_name, ' ' , last_name "ID FIRSTNAME LASTNAME " FROM members;

What is the outcome?

• It fails because the alias name specified after the column names is invalid.

• It fails because the space specified in single quotation marks after the first two column names is invalid.

• It executes successfully and displays the column details in a single column with only the alias column heading.

• It executes successfully and displays the column details in three separate columns and replaces only the last column heading with the alias.

You execute the following commands:

SQL > DEFINE hiredate = '01-APR-2011'

SQL >SELECT employee_id, first_name, salary

FROM employees -

WHERE hire_date > '&hiredate'

AND manager_id > &mgr_id;

For which substitution variables are you prompted for the input?

• none, because no input required

• both the substitution variables ''hiredate' and 'mgr_id'.

• only hiredate'

• only 'mgr_id'

View the Exhibit and examine the structure of CUSTOMERS table.

Using the CUSTOMERS table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed.

Which SQL statement would produce the required result?

• SELECT NVL (TO CHAR(cust_credit_limit * .15), 'Not Available') "NEW CREDIT" FROM customers;

• SELECT TO_CHAR (NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT" FROM customers;

• SELECT NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT" FROM customers;

• SELECT NVL(cust_credit_limit), 'Not Available') "NEW CREDIT" FROM customers;

View the Exhibit and examine the details of PRODUCT_INFORMATION table.

You have the requirement to display PRODUCT_NAME from the table where the CATEGORY_ID column has values 12 or 13, and the SUPPLIER_ID column has the value 102088. You executed the following SQL statement:

SELECT product_name -

FROM product_information -

WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088;

Which statement is true regarding the execution of the query?

• It would not execute because the same column has been used in both sides of the AND logical operator to form the condition.

• It would not execute because the entire WHERE clause condition is not enclosed within the parentheses.

• It would execute and the output would display the desired result.

• It would execute but the output would return no rows.

View the exhibit and examine the structure of the STORES table.

You must display the NAME of stores along with the ADDRESS, START_DATE, PROPERTY_PRICE, and the projected property price, which is 115% of property price.

The stores displayed must have START_DATE in the range of 36 months starting from 01-Jan-2000 and above.

Which SQL statement would get the desired output?

• SELECT name, concat (address| | ','| |city| |', ', country) AS full_address, start_date, property_price, property_price*115/100 FROM stores WHERE MONTHS_BETWEEN (start_date, '01-JAN-2000') <=36;

• SELECT name, concat (address| | ','| |city| |', ', country) AS full_address, start_date, property_price, property_price*115/100 FROM stores WHERE TO_NUMBER(start_date-TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;

• SELECT name, address||', '||city||', '||country AS full_address, start_date, property_price, property_price*115/100 FROM stores WHERE MONTHS_BETWEEN(start_date,TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;

• SELECT name, concat (address||','| |city| |', ', country) AS full_address, start_date, property_price, property_price*115/100 FROM stores WHERE MONTHS_BETWEEN (start_date, TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;