Oracle 1Z0-051 問題集

質問 1：
Which two statements are true about WHERE and HAVING clauses? (Choose two)
A. A HAVING clause can be used to restrict both rows and groups.
B. A HAVING clause can be used to restrict groups only.
C. A HAVING clause CANNOT be used in sub queries.
D. A WHERE clause can be used to restrict both rows and groups.
E. A WHERE clause can be used to restrict rows only.
F. A WHERE clause CANNOT be used in a query of the query uses a HAVING clause.
正解：B,E
解説: (Topexam メンバーにのみ表示されます)

質問 2：
Which tasks can be performed using SQL functions that are built into Oracle database? (Choose three.)
A. checking whether a specified character exists in a given string
B. finding the remainder of a division
C. comparing two expressions to check whether they are equal
D. removing trailing, leading, and embedded characters from a character string
E. adding a number to a date for a resultant date value
正解：A,B,C

質問 3：
You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty.
Which statement accomplishes this task?
A. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
B. ALTER TABLE students ADD PRIMARY KEY student_id;
C. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;
D. ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id);
E. ALTER TABLE students MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
正解：A
解説: (Topexam メンバーにのみ表示されます)

質問 4：
See the Exhibit and Examine the structure of SALES and PROMOTIONS tables: Exhibit:

You want to delete rows from the SALES table, where the PROMO_NAME column in the PROMOTIONS table has either blowout sale or everyday low price as values.
Which DELETE statements are valid? (Choose all that apply.)
A. D DELETE FROM sales WHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo_name IN ('blowout sale','everyday low price'));
B. DELETE FROM sales WHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo_name = 'blowout sale' OR promo_name = 'everyday low price');
C. DELETE FROM sales WHERE promo_id = (SELECT promo_id FROM promotions WHERE promo_name = 'blowout sale') AND promo_id = (SELECT promo_id FROM promotions WHERE promo_name = 'everyday low price');
D. DELETE FROM sales WHERE promo_id = (SELECT promo_id FROM promotions WHERE promo_name = 'blowout sale') OR promo_id = (SELECT promo_id FROM promotions WHERE promo_name = 'everyday low price');
正解：A,B,D

質問 5：
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_income_level desc, cust_city DESC;
B. SELECT cust_city, cust_income_level, cust_last_name FROM customers ORDER BY cust_city desc, cust_income_level 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;
正解：B

質問 6：
Exhibit contains the structure of PRODUCTS table:

Evaluate the following query:

What would be the outcome of executing the above SQL statement?
A. It produces an error
B. It shows the names of all products in the table
C. It shown the names of all products whose list price is less than the maximum list price
D. It shows the names of products whose list price is the second highest in the table.
正解：D

質問 7：
Examine the data in the CUST_NAME column of the CUSTOMERS table.
CUST_NAME
Renske Ladwig
Jason Mallin
Samuel McCain
Allan MCEwen
Irene Mikkilineni
Julia Nayer
You need to display customers' second names where the second name starts with "Mc" or "MC."
Which query gives the required output?
A. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
FROM customers
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) LIKE 'Mc%';
B. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
FROM customers
WHERE SUBSTR(cust_name, INSTR(cust_name,' ')+1) LIKE INITCAP('MC%');
C. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
FROM customers
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) = INITCAP('MC%');
D. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
FROM customers
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1))='Mc';
正解：A