Oracle Database 12c: SQL Fundamentals - 1z1-061 FREE EXAM DUMPS QUESTIONS & ANSWERS]

Question 1

View the Exhibit and examine the structure of the PROMOTIONS table. Evaluate the following SQL statement:

The above query generates an error on execution.
Which clause in the above SQL statement causes the error?

A. SELECT B. WHERE C. GROUP BY D. ORDER BY

Discussion 0

Correct Answer: C Vote an answer

Question 2

Examine the data in the CUST_NAME column of the CUSTOMERS table.
CUST_NAME
---------------------
Lex De Haan
Renske Ladwig
Jose Manuel Urman
Jason Mallin
You want to extract only those customer names that have three names and display the * symbol in place of the first name as follows:
CUST NAME
---------------------
*** De Haan
**** Manuel Urman
Which two queries give the required output? (Choose two.)

A. SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')), LENGTH(cust_name)- INSTR(cust_name, ''), '*') "CUST NAME"FROM customersWHERE INSTR(cust_name, ' ', -
1, -2)<>0; B. SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')), LENGTH(cust_name), '*')
"CUST NAME" FROM customersWHERE INSTR(cust_name, ' ', 1, 2)<>0; C. SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')), LENGTH(cust_name)- INSTR(cust_name, ' '), '*') "CUST NAME"FROM customersWHERE INSTR(cust_name, ' ',
1, 2)<>0 ; D. SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')), LENGTH(cust_name), '*')
"CUST NAME" FROM customersWHERE INSTR(cust_name, ' ', -1, 2)<>0;

Discussion 0

Correct Answer: B,D Vote an answer

Question 3

See the Exhibit and examine the structure of ORD table:
Exhibit:

Evaluate the following SQL statements that are executed in a user session in the specified order:
CREATE SEQUENCE ord_seq;
SELECT ord_seq.nextval
FROM dual;
INSERT INTO ord
VALUES (ord_seq.CURRVAL, '25-jan-2007, 101);
UPDATE ord
SET ord_no= ord_seq.NEXTVAL
WHERE cust_id =101;
What would be the outcome of the above statements?

A. All the statements would execute successfully and the ORD_NO column would have the value 20 for the CUST_ID 101 because the default CACHE value is 20. B. The CREATE SEQUENCE command would not execute because the starting value of the sequence and the increment value have not been specified. C. All the statements would execute successfully and the ORD_NO column would contain the value 2 for the CUST_ID 101. D. The CREATE SEQUENCE command would not execute because the minimum value and maximum value for the sequence have not been specified.

Discussion 0

Correct Answer: C Vote an answer

Question 4

View the Exhibit and examine the structure of the PROMOTIONS table.
Using the PROMOTIONS table, you need to find out the names and cost of all the promos done on 'TV' and 'internet' that ended in the time interval 15th March '00 to 15th October
'00.
Which two queries would give the required result? (Choose two.)

A. SELECT promo_name, promo_costFROM promotionsWHERE (promo_category
BETWEEN 'TV' AND 'internet') AND(promo_end_date IN ('15-MAR-00', '15-OCT-00')); B. SELECT promo_name, promo_costFROM promotionsWHERE promo_category = 'TV' OR promo_category ='internet' AND promo_end_date >='15-MAR-00' OR promo_end_date
< ='15-OCT-00'; C. SELECT promo_name, promo_costFROM promotionsWHERE promo_category IN ('TV',
'internet') ANDpromo_end_date BETWEEN '15-MAR-00' AND '15-OCT-00'; D. SELECT promo_name, promo_costFROM promotionsWHERE (promo_category = 'TV' OR promo_category ='internet') AND (promo_end_date >='15-MAR-00' AND promo_end_date <='15-OCT-00');

Discussion 0

Correct Answer: C,D Vote an answer

Question 5

View the Exhibit and examine the structure of the PROMOTIONS table.

Examine the following two SQL statements:

Which statement is true regarding the above two SQL statements?

A. statement 1 gives an error, statement 2 executes successfully B. statement 2 gives an error, statement 1 executes successfully C. statement 1 and statement 2 execute successfully and give the same output D. statement 1 and statement 2 execute successfully and give a different output

Discussion 0

Correct Answer: D Vote an answer

Question 6

Examine the structure and data of the CUSTJTRANS table:
CUSTJRANS
Name Null? Type
CUSTNO NOT NULL CHAR(2) TRANSDATE DATE TRANSAMT NUMBER(6.2) CUSTNO
TRANSDATE TRANSAMT
11 01-JAN-07 1000
22 01-FEB-07 2000
33 01-MAR-07 3000
Dates are stored in the default date format dd-mon-rr in the CUSTJTRANS table. Which three SQL statements would execute successfully? (Choose three.)

A. SELECT * FROM custjrans WHERE transdate='01-JANUARY-07': B. SELECT custno - 'A' FROM custjrans WHERE transamt > 2000: C. SELECT transdate + '10' FROM custjrans; D. SELECT * FROM custjrans WHERE transdate = '01-01-07': E. SELECT transamt FROM custjrans WHERE custno > '11':

Discussion 0

Correct Answer: A,C,E Vote an answer

Question 7

You need to extract details of those products in the SALES table where the PROD_ID column contains the string '_D123'. Which WHERE clause could be used in the SELECT statement to get the required output?

A. WHERE prod_id LIKE '%\_D123%' ESCAPE '\' B. WHERE prod_id LIKE '%\_D123%' ESCAPE '\_' C. WHERE prod_id LIKE '%_D123%' ESCAPE '%_' D. WHERE prod_id LIKE '%_D123%' ESCAPE '_'

Discussion 0

Correct Answer: A Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 8

You need to produce a report where each customer's credit limit has been incremented by
$1000. In the output, the customer's last name should have the heading Name and the incremented credit limit should be labeled New Credit Limit. The column headings should have only the first letter of each word in uppercase.
Which statement would accomplish this requirement?

A. Option B B. Option A C. Option C D. Option D

Discussion 0

Correct Answer: C Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 9

Which constraint can be defined only at the column level?

A. CHECK B. PRIMARY KEY C. UNIQUE D. FOREIGN KEY E. NOT NULL

Discussion 0

Correct Answer: E Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 10

Which statement adds a constraint that ensures the CUSTOMER_NAME column of the CUSTOMERS table holds a value?

A. ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL; B. ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn IS NOT NULL; C. ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn
NOT NULL; D. ALTER TABLE customers MODIFY CONSTRAINT cust_name_nn CHECK
customer_name IS NOT NULL; E. ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK customer_name NOT NULL; F. ALTER TABLE customers MODIFY name CONSTRAINT cust_name_nn NOT NULL;

Discussion 0

Correct Answer: C Vote an answer

Question 11

The user Sue issues this SQL statement:
GRANT SELECT ON sue.EMP TO alice WITH GRANT OPTION;
The user Alice issues this SQL statement:
GRANT SELECT ON sue.EMP TO reena WITH GRANT OPTION;
The user Reena issues this SQL statement:
GRANT SELECT ON sue.EMP TO timber;
The user Sue issues this SQL statement:
REVOKE select on sue.EMP FROM alice;
For which users does the revoke command revoke SELECT privileges on the SUE.EMP table?

A. Alice and Reena B. Alice, Reena, and Timber C. Alice only D. Sue, Alice, Reena, and Timber

Discussion 0

Correct Answer: B Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 12

Which two statements about creating constraints are true? (Choose two)

A. Constraint names must start with SYS_C. B. Constraints can be created at the same time the table is created. C. Constraints can be created after the table is created. D. All constraints must be defined at the column level. E. Information about constraints is found in the VIEW_CONSTRAINTS dictionary view.

Discussion 0

Correct Answer: B,C Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 13

Which SQL statements would display the value 1890.55 as $1, 890.55? (Choose three.)

A. SELECT TO_CHAR(1890.55, '$99G999D99')FROM DUAL; B. SELECT TO_CHAR(1890.55, '$99G999D00')FROM DUAL; C. SELECT TO_CHAR(1890.55, '$0G000D00')FROM DUAL; D. SELECT TO_CHAR(1890.55, '$9, 999V99')FROM DUAL; E. SELECT TO_CHAR(1890.55, '$99, 999D99')FROM DUAL;

Discussion 0

Correct Answer: A,B,C Vote an answer

Question 14

Which three arithmetic operations can be performed on a column by using a SQL function that is built into Oracle database? (Choose three.)

A. subtraction B. raising to a power C. addition D. finding the lowest value E. finding the quotient

Discussion 0

Correct Answer: B,C,D Vote an answer

Question 15

View the Exhibit and examine the data in the EMPLOYEES table.

You want to generate a report showing the total compensation paid to each employee to date.
You issue the following query:

What is the outcome?

A. It executes successfully and gives the correct output. B. It executes successfully but does not give the correct output. C. It generates an error because the concatenation operator can be used to combine only two items. D. It generates an error because the alias is not valid. E. It generates an error because the usage of the ROUND function in the expression is not valid.

Discussion 0

Correct Answer: B Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).