Exam Code: 1Z0-051 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Oracle Database: SQL Fundamentals I
Certification Provider: Oracle
Free Today! Guaranteed Training- Pass 1Z0-051 Exam.
2021 May 1Z0-051 Study Guide Questions:
Q21. - (Topic 1)
Which is an iSQL*Plus command?
A. INSERT
B. UPDATE
C. SELECT
D. DESCRIBE
E. DELETE
F. RENAME
Answer: D
Explanation: Explanation:
The only SQL*Plus command in this list: DESCRIBE. It cannot be used as SQL command.
This command returns a description of tablename, including all columns in that table, the
datatype for each column and an indication of whether the column permits storage of NULL
values.
Incorrect Answer:
A INSERT is not a SQL*PLUS command
B UPDATE is not a SQL*PLUS command
C SELECT is not a SQL*PLUS command
E DELETE is not a SQL*PLUS command
F RENAME is not a SQL*PLUS command
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 7
Q22. - (Topic 2)
Examine the structure of the EMP_DEPT_VU view:
Which SQL statement produces an error?
A. SELECT *
FROM emp_dept_vu;
B. SELECT department_id, SUM(salary)
FROM emp_dept_vu
GROUP BY department_id;
C. SELECT department_id, job_id, AVG(salary)
FROM emp_dept_vu
GROUP BY department_id, job_id;
D. SELECT job_id, SUM(salary)
FROM emp_dept_vu
WHERE department_id IN (10,20)
GROUP BY job_id
HAVING SUM(salary) > 20000;
E. None of the statements produce an error; all are valid.
Answer: E
Explanation: Explanation: None of the statements produce an error. Incorrect Answer: AStatement will not cause error BStatement will not cause error CStatement will not cause error DStatement will not cause error
Q23. - (Topic 2)
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 CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL;
C. ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn NOT NULL;
D. ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn IS NOT NULL;
E. ALTER TABLE customers MODIFY name CONSTRAINT cust_name_nn NOT NULL;
F. ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK customer_name NOT NULL;
Answer: C
Up to the immediate present 1z0-051 dumps free download:
Q24. - (Topic 2)
View the Exhibit; e xamine the structure of the PROMOTIONS table.
Each promotion has a duration of at least seven days.
Your manager has asked you to generate a report, which provides the weekly cost for each promotion done to l date.
Which query would achieve the required result?
A. SELECT promo_name, promo_cost/promo_end_date-promo_begin_date/7 FROM promotions;
B. SELECT promo_name,(promo_cost/promo_end_date-promo_begin_date)/7 FROM promotions;
C. SELECT promo_name, promo_cost/(promo_end_date-promo_begin_date/7) FROM promotions;
D. SELECT promo_name, promo_cost/((promo_end_date-promo_begin_date)/7) FROM promotions;
Answer: D
Q25. - (Topic 1)
View the Exhibit and examine the structure of the CUSTOMERS table.
You have been asked to produce a report on the CUSTOMERS table showing the customers details sorted in descending order of the city and in the descending order of their income level in each city. Which query would accomplish this task?
A. SELECT cust_city, cust_income_level, cust_last_name FROM customers ORDER BY cust_city desc, cust_income_level DESC;
B. SELECT cust_city, cust_income_level, cust_last_name FROM customers ORDER BY cust_income_level desc, cust_city DESC;
C. SELECT cust_city, cust_income_level, cust_last_name
FROM customers
ORDER BY (cust_city, cust_income_level) DESC;
D. SELECT cust_city, cust_income_level, cust_last_name FROM customers ORDER BY cust_city, cust_income_level DESC;
Answer: A
Q26. - (Topic 2)
What are two reasons to create synonyms? (Choose two.)
A. You have too many tables.
B. Your tables names are too long.
C. Your tables have difficult names.
D. You want to work on your own tables.
E. You want to use another schema's tables.
F. You have too many columns in your tables.
Answer: B,C
Explanation:
Create a synonyms when the names of the tables are too long or the table names are difficult.
Breathing 1z0-047 vs 1z0-051:
Q27. - (Topic 2)
Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:
Which DELETE statement is valid?
A. DELETE FROM employees WHERE employee_id = (SELECT employee_id FROM employees);
B. DELETE * FROM employees WHERE employee_id = (SELECT employee_id FROM new_employees);
C. DELETE FROM employees WHERE employee_id IN(SELECT employee_id FROM new_employees WHERE name = 'Carrey');
D. DELETE * FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE last_name = 'Carrey');
Answer: C
Explanation:
The correct syntax for DELETE statement
DELETE [ FROM ] table
[ WHERE condition ];
Incorrect Answers :
A. '=' is use in the statement and sub query will return more than one row.
Error Ora-01427: single-row sub query returns more than one row.
B. Incorrect DELETE statement
D. Incorrect DELETE statement
Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Manipulating Data,
p. 8-19
Q28. - (Topic 1)
View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables.
There is only one customer with the CUST_LAST_NAME column having value Roberts. Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600?
A.
INSERT INTO orders VALUES (1,'10-mar-2007', 'direct', (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
B.
INSERT INTO orders (order_id,order_date,order_mode,
(SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND
credit_limit=600),order_total)
VALUES(1,'10-mar-2007', 'direct', &&customer_id, 1000);
C.
INSERT INTO(SELECT o.order_id, o.order_date,o.order_mode,c.customer_id, o.order_total FROM orders o, customers c WHERE o.customer_id = c.customer_id AND c.cust_last_name='Roberts' ANDc.credit_limit=600 ) VALUES (1,'10-mar-2007', 'direct',(SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
D.
INSERT INTO orders (order_id,order_date,order_mode,
(SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND
credit_limit=600),order_total)
VALUES(1,'10-mar-2007', 'direct', &customer_id, 1000);
Answer: A
Q29. - (Topic 1)
Evaluate the following query:
What would be the outcome of the above query?
A. It produces an error because flower braces have been used.
B. It produces an error because the data types are not matching.
C. It executes successfully and introduces an 's at the end of each promo_name in the output.
D. It executes successfully and displays the literal" {'s start date was} " for each row in the output.
Answer: C
Explanation:
So, how are words that contain single quotation marks dealt with? There are essentially
two mechanisms available. The most popular of these is to add an additional single
quotation mark next to each naturally occurring single quotation mark in the character
string
Oracle offers a neat way to deal with this type of character literal in the form of the
alternative quote (q) operator. Notice that the problem is that Oracle chose the single quote
characters as the special pair of symbols that enclose or wrap any other character literal.
These character-enclosing symbols could have been anything other than single quotation
marks.
Bearing this in mind, consider the alternative quote (q) operator. The q operator enables
you to choose from a set of possible pairs of wrapping symbols for character literals as
alternatives to the single quote symbols. The options are any single-byte or multibyte
character or the four brackets: (round brackets), {curly braces}, [squarebrackets], or <angle
brackets>. Using the q operator, the character delimiter can effectively be changed from a
single quotation mark to any other character
The syntax of the alternative quote operator is as follows:
q'delimiter'character literal which may include the single quotes delimiter' where delimiter
can be any character or bracket.
Alternative Quote (q) Operator
Specify your own quotation mark delimiter.
Select any delimiter.
Increase readability and usability.
SELECT department_name || q'[ Department's Manager Id: ]'
|| manager_id
AS "Department and Manager"
FROM departments;
Alternative Quote (q) Operator
Many SQL statements use character literals in expressions or conditions. If the literal itself contains a single quotation mark, you can use the quote (q) operator and select your own quotation mark delimiter. You can choose any convenient delimiter, single-byte or multibyte, or any of the following character pairs: [ ], { }, ( ), or < >. In the example shown, the string contains a single quotation mark, which is normally interpreted as a delimiter of a character string. By using the q operator, however, brackets [] are used as the quotation mark delimiters. The string between the brackets delimiters is interpreted as a literal character string.
Q30. - (Topic 1)
You need to calculate the number of days from 1st January 2007 till date . Dates are stored in the default format of dd-mon-rr. Which two SQL statements would give the required output? (Choose two.)
A. SELECT SYSDATE - '01-JAN-2007' FROM DUAL:
B. SELECT SYSDATE - TOJDATE(X)1/JANUARY/2007") FROM DUAL:
C. SELECT SYSDATE - TOJDATE('01-JANUARY-2007') FROM DUAL:
D. SELECT TO_CHAR(SYSDATE. 'DD-MON-YYYY') - '01-JAN-2007' FROM DUAL:
E. SELECT TO_DATE(SYSDATE. *DD/MONTH/YYYY') - '01/JANUARY/2007' FROM DUAL:
Answer: B,C