Functions - Scalar & Aggregate

MCQ

ANSWERS:

1)Select a suitable option for fetching the output of the following query. select substr("Oracle World",1,6) from dual;

   Select one:

   a. racle W

   b. racle

   c. Oracle

   d. racle Wo

2)To display the names of employees that are not assigned to a department.

Evaluate this SQL statement:

 

SELECT last_name, first_name

FROM employee

WHERE dept_id = NULL;

 

Which change should you make to achieve the desired result?

 

Select one:

 a. Change the operator in the WHERE condition.

 b. Change the column in the WHERE condition.

 c. Create an outer join.

 d. Add a second condition to the WHERE condition.

3) Generate a list of all customer last names with their credit limits from the CUSTOMERS table. Customers who do not have a credit limit should appear last in the list. kindly note that customers who do not have a credit card will have NULL against the credit limit.

 

Which query would achieve the required result?

 

 

Select one:

 a. SELECT cust_last_name,cust_credit_limit

FROM customers

ORDER BY cust_last_name,cust_credit_limit NULLS LAST;

 b. SELECT cust_last_name,cust_credit_limit

FROM customers;

 c. SELECT cust_last_name,cust_credit_limit

FROM customers

ORDER BY cust_credit_limit;

 d. SELECT cust_last_name,cust_credit_limit

FROM customers

ORDER BY cust_credit_limit DESC;

4) To calculate the number of days from 1st Jan 2007 till date:

 

Dates are stored in the default format of dd-mm-yy.

 

Which SQL statements would give the required output?

Select one or more:

 a. SELECT SYSDATE - TO_DATE('01/JANUARY/2007') FROM DUAL;

 b. SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY')-'01-JAN-2007' FROM DUAL;

 c. SELECT SYSDATE - '01-JAN-2007' FROM DUAL ;

 d. SELECT TO_DATE(SYSDATE,'DD/MONTH/YYYY')-'01/JANUARY/2007' FROM DUAL;

 e. SELECT SYSDATE - TO_DATE('01-JANUARY-2007) FROM DUAL;

5) To display the names of all promos done after January

1, 2001 starting with the latest promo.

Which query would give the required result? (Choose all that apply.)

 

Select one or more:

 a. SELECT promo_name,promo_begin_date "START DATE"

FROM promotions

WHERE promo_begin_date > '01-JAN-01'

ORDER BY "START DATE" DESC;

 b. SELECT promo_name,promo_begin_date

FROM promotions

WHERE promo_begin_date > '01-JAN-01'

ORDER BY 1 DESC;

c. SELECT promo_name,promo_begin_date

FROM promotions

WHERE promo_begin_date > '01-JAN-01'

ORDER BY 2 DESC;

 d. SELECT promo_name,promo_begin_date

FROM promotions

WHERE promo_begin_date > '01-JAN-01'

ORDER BY promo_name DESC;

6) The CUSTOMERS table has these columns:

 

CUSTOMER_ID NUMBER(4) NOT NULL

 

CUSTOMER_NAME VARCHAR2(100) NOT NULL

 

CUSTOMER_ADDRESS VARCHAR2(150)

 

CUSTOMER_PHONE VARCHAR2(20)

 

You need to produce output that states "Dear Customer customer_name, ".

 

The customer_name data values come from the CUSTOMER_NAME column in the

CUSTOMERS table.

 

Which statement produces this output?

Select one:

 a. SELECT dear customer, customer_name, FROM customers;

 b. SELECT 'Dear Customer ' || customer_name || ',' || FROM customers;

 c. SELECT 'Dear Customer ' || customer_name || ',' FROM customers;

 d. SELECT "Dear Customer", customer_name || ',' FROM customers;

 e. SELECT 'Dear Customer ' || customer_name ',' FROM customers;

7) 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 one:

 a. SELECT NVL(cust_credit_limit*.15,'Not Available')"NEW CREDIT"

FROM customers;

 b. SELECT NVL(cust_credit_limit,'Not Available')*.15 "NEW CREDIT"

FROM customers;

 c. SELECT TO_CHAR(NVL(cust_credit_limit*.15,'Not Available')) "NEW CREDIT"

FROM customers;

 d. SELECT NVL(TO_CHAR(cust_credit_limit*.15),'Not Available') "NEW CREDIT"

FROM customers;

8) Which statement is true regarding the default behavior of the ORDER BY clause?

Select one:

 a. In a character sort, the values are case-sensitive

 b. Only those columns that are specified in the SELECT list can be used in the ORDER BY clause.

 c. NULL values are not considered at all by the sort operation.

 d. Numeric values are displayed from the maximum to the minimum value if they have decimal positions

9) ABC company wants to give each employee a $100 salary increment. You need to evaluate the

results from the EMP table prior to the actual modification. If you do not want to store the results in the database, which statement is valid?

Select one:

 a. You need to give the arithmetic expression that involves the salary increment in the DISPLAY

the clause of the SELECT statement. 

 b. You need to add a column to the EMP table.

 c. You need to give the arithmetic expression that involves the salary increment in the SET clause

of the UPDATE statement.

 d. You need to give the arithmetic expression that involves the salary increment in the UPDATE

clause of the SELECT statement.

10) To update the CUST_CREDIT_LIMIT column to NULL for all the customers, where

CUST_INCOME_LEVEL has NULL in the CUSTOMERS table. Which SQL statement will

accomplish the task?

 

Select one:

 a. UPDATE customers

SET cust_credit_limit = TO_NUMBER(‘ ‘,9999)

WHERE cust_income_level IS NULL;

 b. UPDATE customers

SET cust_credit_limit = TO_NUMBER(NULL)

WHERE cust_income_level = TO_NUMBER(NULL);

c. UPDATE customers

SET cust_credit_limit = NULL

WHERE cust_income_level IS NULL;

 d. UPDATE customers

SET cust_credit_limit = NULL

WHERE cust_income_level = NULL;

  • Loginhackers Blogger
  • Contact us via Twitter Loginhackers
  • Contact us via Instagram
  • loginhackers facebook contact id

Free Premium | Spotify premium for free | Grammarly free premium | Netflix free accounts | Netflix mod | Spotify mod | Hotstar mod | Spotify premium mod apk | free amazon prime account