11g

OCP-1Z0-051 第77题 INITCAP和SUBSTR函数

一、原题 In the CUSTOMERS table, the CUST_CITY column contains the value Paris for the CUST_FIRST_NAME ABIGAIL. Evaluate the following query: SQL> SELECT INITCAP(cust_first_name|| || UPPER(SUBSTR(cust_city,-LENGTH(cust_city),2…

OCP-1Z0-051 补充题库第7题 ORDER BY 子句

一、原题 Evaluate these two SQL statements: SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC; SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC; What is true about them? A. The two statements produce identical resu…

OCP-1Z0-051 第131题 LEFT OUTER JOIN和RIGHT OUTER JOIN的用法

一、原题 View the Exhibit and examine the structure of the PRODUCT, COMPONENT, and PDT_COMP tables. In PRODUCT table, PDTNO is the primary key. In COMPONENT table, COMPNO is the primary key. In PDT_COMP table, (PDTNO,COMPNO) is the primary key, PDTNO is …

OCP-1Z0-051 补充题库第11题 SQL和SQL*Plus的区别

一、原题 Which statement correctly describes SQL and /SQL*Plus? A. Both SQL and /SQL*plus allow manipulation of values in the database. B. /SQL*Plus recognizes SQL statements and sends them to the server; SQL is the Oracle proprietary interface for execu…

OCP-1Z0-051 第79题 TRIM,REPLACE,LOWER函数

一、原题 View the Exhibit and examine the structure of the CUSTOMERS table. In the CUSTOMERS table, the CUST_LAST_NAME column contains the values Anderson and Ausson. You issue the following query: SQL> SELECT LOWER(REPLACE(TRIM(son FROM cust_last_nam…

OCP-1Z0-051 第6题 case when和decode的用法

一、原题 Examine the structure of the SHIPMENTS table:Name Null TypePO_ID NOT NULL NUMBER(3) PO_DATE NOT NULL DATE SHIPMENT_DATE NOT NULL DATE SHIPMENT_MODE VARCH…

OCP-1Z0-051 补充题库第15题子查询和join

一、原题 The COMMISSION column shows the monthly commission earned by the employee. Which two tasks would require sub queries or joins in order to be performed in a single step? (Choose two.) A. listing the employees who earn the same amount of commissi…

OCP-1Z0-051 第82题 like模糊查询

一、原题 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&q…

OCP-1Z0-051 第74题 oracle单行函数

一、原题 Which statements are true regarding single row functions? (Choose all that apply.) A. MOD : returns the quotient of a division. B. TRUNC : can be used with NUMBER and DATE values. C. CONCAT : can be used to combine any number of values. D. SYSDA…

OCP-1Z0-051 第80题 DATE数据类型

一、原题 Which two statements are true regarding working with dates? (Choose two.) A. The default internal storage of dates is in the numeric format. B. The default internal storage of dates is in the character format. C. The RR date format automatically…

OCP-1Z0-051 第117题 WHERE和HAVING子句的区别

一、原题 Which statements are true regarding the WHERE and HAVING clauses in a SELECT statement?(Choose all that apply.) A. The HAVING clause can be used with aggregate functions in subqueries. B. The WHERE clause can be used to exclude rows after dividi…

OCP-1Z0-051 补充题库第31题 subquery子查询的注意事项

一、原题 Which two statements about sub queries are true? (Choose two.) A. A sub query should retrieve only one row. B. A sub query can retrieve zero or more rows. C. A sub query can be used only in SQL query statements. D. Sub queries CANNOT be nested b…

OCP-1Z0-051 第60题

一、原题 View the Exhibit and examine the structure of CUSTOMERS table. Evaluate the following query: SQL>SELECT cust_id, cust_city FROM customers WHERE cust_first_name NOT LIKE A_%g_% AND cust_credit_limit BETWEEN 5…

OCP-1Z0-051 第75题 ROUND和RPAD函数

一、原题 The following data exists in the PRODUCTS table: PROD_ID PROD_LIST_PRICE 123456 152525.99 You issue the following query: SQL> SELECT RPAD(( ROUND(prod_list_price)), 10,*) FROM products WHERE prod_id…

日期函数months_between的用法

MONTHS_BETWEEN (date1, date2) 用于计算date1和date2之间有几个月。如果date1在日历中比date2晚，那么MONTHS_BETWEEN()就返回一个正数。如果date1在日历中比date2早，那么MONTHS_BETWEEN()就返回一个负数。如果date1和date2日期一样&a…

OCP-1Z0-051 第81题 SYSDATE函数

一、原题 You are currently located in Singapore and have connected to a remote database in Chicago. You issue the following command: SQL> SELECT ROUND(SYSDATE-promo_begin_date,0) FROM promotions WHERE (SYSDATE-promo_begin_date)/365 > 2; PROMOTIONS i…

OCP-1Z0-051 第97题 NULLIF，NVL函数用法

一、原题 View the Exhibit and evaluate the structure and data in the CUST_STATUS table. You issue the following SQL statement: SQL> SELECT custno, NVL2(NULLIF(amt_spent,credit_limit), 0, 1000) "BONUS" FROM cust_status; Which st…

OCP-1Z0-051 补充题库第28题 Date类型

一、原题 Which two statements are true regarding working with dates? (Choose two.) A. The default internal storage of dates is in the numeric format B. The RR date format automatically calculates the century from the SYSDATE function but allows the user …

OCP-1Z0-051 补充题库第29题 INSERT语句中有子查询

一、原题 View the Exhibit and examine the structure of the CUSTOMERS table. NEW_CUSTOMERS is a new table with the columns CUST_ID, CUST_NAME and CUST_CITY that have the same data types and size as the corresponding columns in the CUSTOMERS table. Evaluat…

OCP-1Z0-051 第78题 ROUND和TRUNC函数

一、原题 Evaluate the following query: SQL> SELECT TRUNC(ROUND(156.00,-1),-1) FROM DUAL; What would be the outcome?（结果是什么？） A. 16 B. 100 C. 160 D. 200 E. 150 答案：C 二、题目解析 ROUND(156…

OCP-1Z0-051 第68题 ORDER BY的默认值

一、原题 Which statement is true regarding the default behavior of the ORDER BY clause? A. In a character sort, the values are case- sensitive. B. NULL values are not considered at all by the sort operation. C. Only those columns that are specified in th…

OCP-1Z0-051 第152题 union操作的注意事项

一、原题 Which statement is true regarding the UNION operator? A. By default, the output is not sorted. B. NULL values are not ignored during duplicate checking. C. Names of all columns must be identical across all SELECT statements. D. The number of col…

OCP-1Z0-051 补充题库第16题 rownum的用法

一、原题 Examine the structure of the STUDENTS table: You need to create a report of the 10 students who achieved the highest ranking in the course INT SQL and who completed the course in the year 1999. Which SQL statement accomplishes this task? A. SEL…

OCP-1Z0-051 第90题转换函数

一、原题 Examine the structure and data in the PRICE_LIST table: name Null Type PROD_ID NOT NULL NUMBER(3) PROD_PRICE VARCHAR2(10) PROD_ID PROD_PRICE 100 $234.55 1…

OCP-1Z0-051 第103题 CASE.. WHEN,TO_CHAR,TRIM的用法

一、原题 Examine the structure of the TRANSACTIONS table: Name Null Type TRANS_ID NOT NULL NUMBER(3) CUST_NAME VARCHAR2(30) TRANS_DATE DATE TRANS_AMT NUMBER(10,2) You want to disp…

OCP-1Z0-051 补充题库第41题 nvl函数的使用

一、原题 See the structure of the PROGRAMS table: Which two SQL statements would execute successfully? (Choose two.) A. SELECT NVL(ADD_MONTHS(END_DATE,1),SYSDATE) FROM programs; B. SELECT TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE)) FRO…

OCP-1Z0-051 第22题 INTERVAL DAY TO SECOND的使用方法

一、原题 You need to create a table for a banking application.One of the columns in the table has the following requirements: 1) You want a column in the table to store the duration of the credit period. 2) The data in the column should be stored in a for…

OCP-1Z0-051 补充题库第24题 SYNONYM同义词

一、原题 Examine the statement: Create synonym emp for hr.employees; What happens when you issue the statement? A. An error is generated. B. You will have two identical tables in the HR schema with different names. C. You create a table called employees …

OCP-1Z0-051 第52题列别名

一、原题 View the Exhibit and examine the structure of the PRODUCTS table. Using the PRODUCTS table, you issue the following query to generate the names, current list price,and discounted list price for all those products whose list price falls below $10…

OCP-1Z0-051 第156题集合操作中的ORDER BY 子句

一、原题 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; …

OCP-1Z0-051 第83题 INSTR,SUBSTR,LPAD函数综合使用

一、原题 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 firs…

OCP-1Z0-051 第154题集合操作MINUS,INTERSECT

一、原题 View the Exhibit and evaluate structures of the SALES, PRODUCTS, and COSTS tables. Evaluate the following SQL statement: SQL>SELECT prod_id FROM products INTERSECT SELECT prod_id FROM sales MINUS SELECT p…

OCP-1Z0-051 补充题库第18题 DELETE语句的特性

一、原题 Evaluate the following SQL statements: DELETE FROM sales; There are no other uncommitted transactions on the SALES table. Which statement is true about the DELETE statement? A. It removes all the rows as well as the structure of the table B. It …

OCP-1Z0-051 补充题库第39题日期运算

一、原题 You need to calculate the number of days from 1st Jan 2007 till date: Dates are stored in the default format of dd-mm-rr. Which two SQL statements would give the required output? (Choose two.) A. SELECT SYSDATE - TO_DATE(01/JANUARY/2007) FROM DU…

OCP-1Z0-051 第10题

一、原题 View the Exhibit; examine 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 wou…

OCP-1Z0-051 补充题库第4题聚合函数的使用

一、原题 See the Exhibit and examine the structure and data in the INVOICE table: Exhibit: Which two SQL statements would executes successfully? (Choose two.) A. SELECT MAX(inv_date),MIN(cust_id) FROM invoice; B. SELECT MAX(AVG(SYSDATE - inv_date)) FROM…

OCP-1Z0-051 第49题 SYNONYM同义词

一、原题 The ORDERS table belongs to the user OE. OE has granted the SELECT privilege on the ORDERS table to the user HR. Which statement would create a synonym ORD so that HR can execute the following query successfully? SELECT * FROM ord; A. CREATE SYN…

OCP-1Z0-051 第155题集合操作UNOIN

一、原题 Evaluate the following SQL statement: SQL>SELECT promo_id, promo_category FROM promotions WHERE promo_category Internet ORDER BY 2 DESC UNION SELECT promo_id, promo_category FROM promotions WHERE promo_categ…

OCP-1Z0-051 补充题库第32题 ALL、ANY、IN

一、原题 View the Exhibit and examine the structure of the PROMOTIONS table. You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the INTERNET category. Which query would give you …

OCP-1Z0-051 第53题日期格式

一、原题 View the Exhibit and examine the data in the PROMOTIONS table. PROMO_BEGIN_DATE is stored in the default date format, dd-mon-rr. You need to produce a report that provides the name, cost, and start date of all promos in the POST category that wer…

OCP-1Z0-051 第21题 LONG类型

一、原题 Examine the description of the EMP_DETAILS table given below: name NULL TYPE EMP_ID NOT NULL NUMBER EMP_NAME NOT NULL VARCHAR2 (40) EMP_IMAGE LONG Which two statements are true regarding …

OCP-1Z0-051 第98题 COALESCE函数的用法

一、原题 Which statement is true regarding the COALESCE function? A. It can have a maximum of five expressions in a list. B. It returns the highest NOT NULL value in the list for all rows. C. It requires that all expressions in the list must be of the sa…

OCP-1Z0-051 补充题库第34题 COUNT函数

一、原题 Which two statements are true regarding the COUNT function? (Choose two.) A. COUNT(*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns B. COUNT(cust_id) returns the number of rows including…

OCP-1Z0-051 补充题库第12题 oracle函数分类

一、原题 Which four are types of functions available in SQL? (Choose 4) A. string B. character C. integer D. calendar E. numeric F. translation G. date H. conversion Answer: B,E,G,H二、题目翻译下面哪四种是SQL中的函数类型?(选四个) A.字符串 B.字符类型 …

OCP-1Z0-051 第65题 order by降序排序

一、原题 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 le…

OCP-1Z0-051 第160题 insert中使用子查询

一、原题 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 …

OCP-1Z0-051 第109题聚合函数的使用

一、原题 Examine the structure of the MARKS table: Name Null Type STUDENT_ID NOT NULL VARCHAR2(4) STUDENT_NAME VARCHAR2(25) SUBJECT1 NUMBER(3) SUBJECT2 …

OCP-1Z0-051 第116题 GROUP BY,HAVING子句应用

一、原题 Examine the data in the ORD_ITEMS table: ORD_NO ITEM_NO QTY 1 111 10 1 222 20 1 333 30 2 333 30 2 444 40 3 111 …

OCP-1Z0-051 第120题 NATURAL JOIN自然连接

一、原题 View the Exhibits and examine the structures of the PRODUCTS, SALES, and CUSTOMERS tables.You issue the following query: SQL>SELECT p.prod_id,prod_name,prod_list_price, quantity_sold,cust_last_name FROM products p NATURAL JOIN sal…

OCP-1Z0-051 第33题 create table 建表语句

一、原题 You want to create an ORD_DETAIL table to store details for an order placed having the following business requirement: 1) The order ID will be unique and cannot have null values. 2) The order date cannot have null values and the default should be …

OCP-1Z0-051 第167题 DELETE语句注意事项

一、原题 Evaluate the following DELETE statement: DELETE FROM sales; There are no other uncommitted transactions on the SALES table. Which statement is true about the DELETE statement? A. It would not remove the rows if the table has a primary key. B. It…

OCP-1Z0-051 第139题子查询的注意事项

一、原题 Which three statements are true about multiple-row subqueries? (Choose three.) A. They can contain a subquery within a subquery. B. They can return multiple columns as well as rows. C. They cannot contain a subquery within a subquery. D. They ca…

OCP-1Z0-051 第147题 CASE..WHEN的使用

一、原题 View the Exhibit and examine the structure of the PROMOTIONS table. Evaluate the following SQL statement: SQL>SELECT promo_name, CASE WHEN promo_cost > (SELECT AVG(promo_cost) FROM…

OCP-1Z0-051 第76题 INSTR和SUBSTR函数

一、原题 You need to display the first names of all customers from the CUSTOMERS table that contain the character e and have the character a in the second last position. Which query would give the required output? A. SELECT cust_first_name FROM cu…

OCP-1Z0-051 补充题库第30题 oracle中的NULL值

一、原题 View the Exhibit and examine the description for the CUSTOMERS table. You want 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…

OCP-1Z0-051 补充题库第33题 sql*plus命令

一、原题 Which are /SQL*Plus commands? (Choose all that apply.) A. INSERT B. UPDATE C. SELECT D. DESCRIBE E. DELETE F. RENAME 答案: D 二、题目翻译下面哪个是SQL*Plus命令?(选择所有合适的选项) 三、题目解析只有describe是sql*plus命令，其它的都…

OCP-1Z0-051 第17题 null参与运算后仍是null

一、原题 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: SQL> SELECT ename|| joined on ||hiredate|| …

OCP-1Z0-051 第129题 RIGHT OUTER JOIN的使用

一、原题 View the Exhibits and examine the structures of the PROMOTIONS and SALES tables. Evaluate the following SQL statement: SQL>SELECT p.promo_id, p.promo_name, s.prod_id FROM sales s RIGHT OUTER JOIN promotions p ON (s.promo_id p.promo_id); Whic…

OCP-1Z0-051 第172题事务savepoint的注意事项

一、原题 The SQL statements executed in a user session are as follows: SQL> CREATE TABLE product (pcode NUMBER(2), pname VARCHAR2(10)); SQL> INSERT INTO product VALUES (1, pen); SQL> INSERT INTO product VALUES (…

OCP-1Z0-051 第14题 DISTINCT去重

一、原题 Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level.Which query would give the required result?A. SELECT …

OCP-1Z0-051 第66题 ORDER BY子句应用

一、原题 View the Exhibit and examine the data in the table. You need to generate a report that displays the IDs of all products in the COSTS table whose unit price is at least 25% more than the unit cost. The details should be displayed in the descendin…

OCP-1Z0-051 第32题 create table 建表语句

一、原题 Which CREATE TABLE statement is valid? A. CREATE TABLE ord_details (ord_no NUMBER(2) PRIMARY KEY, item_no NUMBER(3) PRIMARY KEY, ord_date DATE NOT NULL); B. CREATE TABLE ord_details (ord_no NUMBER(2) UNIQUE, NOT NULL, item_no NUMBER(3), ord_date…

OCP-1Z0-051 第124题 JOIN内连接

一、原题 View the Exhibit and examine the structure of the PROMOTIONS, SALES, and CUSTOMER tables. You need to generate a report showing the promo name along with the customer name for all products that were sold during their promo campaign and before 30…

OCP-1Z0-051 第126题 LEFT OUTER JOIN,FULL OUTER JOIN,JOIN的使用

一、原题 View the Exhibit and examine the data in the PROJ_TASK_DETAILS table. The PROJ_TASK_DETAILS table stores information about tasks involved in a project and the relation between them. The BASED_ON column indicates dependencies between tasks. Some …

OCP-1Z0-051 第169题使用子查询DELETE数据

一、原题 View the Exhibit and examine the description for the PRODUCTS and SALES table.PROD_ID is a primary key in the PRODUCTS table and foreign key in the SALES table. You want to remove all the rows from the PRODUCTS table for which no sale was done fo…

OCP-1Z0-051 第54题

一、原题 View the Exhibit and examine the structure of the CUSTOMERS table. Evaluate the query statement: SQL> SELECT cust_last_name, cust_city, cust_credit_limit FROM customers WHERE cust_last_name BETWEEN A AND C …

OCP-1Z0-051 第119题 GROUP BY,HAVING子句的顺序

一、原题 Examine the data in the ORD_ITEMS table: ORD_NO ITEM_NO QTY 　　1 111 10 　　1 222 20 　　1 333 30 　　2 333 30 　　2 444 40 　　3 111 40 Evaluate the following query: SQL>SELECT item_no, AVG(qty) FROM ord_items …

OCP-1Z0-051 第130题 LEFT OUTER JOIN的用法

一、原题 View the Exhibit and examine the data in the EMPLOYEES table: You want to display all the employee names and their corresponding manager names. Evaluate the following query: SQL> SELECT e.employee_name "EMP NAME", m.employee_name &q…

OCP-1Z0-051 第107题 AVG,MAX,MIN组函数的使用

一、原题 View the Exhibit and examine the structure and data in the INVOICE table. Which two SQL statements would execute successfully? (Choose two.) A. SELECT AVG(inv_date ) FROM invoice; B. SELECT MAX(inv_date),MIN(cust_id) FROM invoic…

在Linux上安装Oracle 数据库 11g （含静默方式安装）

好久没碰11g了，今天（2023年11月16日）因为有个需求又装了一遍。在OCI上安装了一个Oracle Linux 6实例： $ uname -a Linux instance-20231116-1239-db11g 4.1.12-124.80.1.el6uek.x86_64 #2 SMP Mon Oct 9 02:32:10 PDT 2023 x86…

OCP-1Z0-051 第100题 DECODE,NULLIF函数用法

一、原题 View the Exhibit and examine the structure of the PROMOTIONS table. Which SQL statements are valid? (Choose all that apply.) A. SELECT promo_id, DECODE(NVL(promo_cost,0), promo_cost,promo_cost * 0.25, 100) "Discount" FROM promo…

OCP-1Z0-051 第121题 NATURAL JOIN自然连接

一、原题 Which two statements are true regarding the USING clause in table joins? (Choose two.) A.It can be used to join a maximum of three tables. B.It can be used to restrict the number of columns used in a NATURAL join. C.It can be used to access data…

OCP-1Z0-051 第123题 JOIN中的USING子句，ON子句

一、原题 View the Exhibits and examine the structures of the PRODUCTS, SALES, and CUSTOMERS tables. You need to generate a report that gives details of the customers last name, name of the product, and the quantity sold for all customers in Tokyo . Whic…

OCP-1Z0-051 第63题 order by 应用

一、原题 View the Exhibit and examine the structure of the PRODUCTS table. You want to display only those product names with their list prices where the list price is at least double the minimum price. The report should start with the product name having…

OCP-1Z0-051 第72题 oracle内置函数

一、原题 Which tasks can be performed using SQL functions built into Oracle Database ? (Choose three.) A. displaying a date in a nondefault format. B. finding the number of characters in an expression. C. substituting a character string in a text express…

OCP-1Z0-051 第122题 NATURAL JOIN（自然连接）和USING子句的用法

一、原题 View the Exhibit for the structure of the STUDENT and FACULTY tables. You need to display the faculty name followed by the number of students handled by the faculty at the base location. Examine the following two SQL statements: Statement 1 SQL&…

OCP-1Z0-051 第94题 MONTHS_BETWEEN,NVL函数

一、原题 Examine the structure of the PROGRAMS table: Name Null Type PROG_ID NOT NULL NUMBER(3) PROG_COST NUMBER(8,2) START_DATE NOT NULL DATE END_DATE DATE Which two SQL stat…

OCP-1Z0-051 第140题子查询中使用GROUP BY注意事项

一、原题 Examine the structure of the PRODUCTS table: Name Null Type PROD_ID NOT NULL NUMBER(4) PROD_NAME VARCHAR2(20) PROD_STATUS VARCHAR2(6) QTY_IN_HAND …

OCP-1Z0-051 第136题子查询的使用

一、原题 View the Exhibit and examine the structure of the PRODUCTS table. Which two tasks would require subqueries? (Choose two.) A. Display the minimum list price for each product status. B. Display all suppliers whose list price is less than 1000. C.…

OCP-1Z0-051 补充题库第25题 TRUNC和ROUND

一、原题 Evaluate the following SQL query; SQL> SELECT TRUNC(ROUND(156.00,-1),-1) FROM DUAL; What would be the outcome? A. 200 B. 16 C. 160 D. 150 E. 100 答案: C 二、题目解析 ROUND(156.00,-1) round表示四舍五入，-1表示保留到小数…

OCP-1Z0-051 第142题嵌套子查询的应用

一、原题 View the Exhibit and examine the structure of the PRODUCTS table. Evaluate the following query: SQL> SELECT prod_name FROM products WHERE prod_id IN (SELECT prod_id FROM products WHERE pro…

OCP-1Z0-051 补充题库第19题限制替代变量的输入值

一、原题 Examine the structure of the EMPLOYEES table:You want to create a SQL script file that contains an INSERT statement. When the script is run,the INSERT statement should insert a row with the specified values into the EMPLOYEES table.The INSERT sta…

OCP-1Z0-051 第118题 GROUP BY,HAVING子句

一、原题 View the Exhibit and examine the structure of the PROMOTIONS table. Examine the following two SQL statements: Statement 1 SQL>SELECT promo_category,SUM(promo_cost) FROM promotions WHERE promo_end_date-promo_begin…

OCP-1Z0-051 第150题集合运算MINUS,INTERSECT

一、原题 View the Exhibit and examine the data in the PROMOTIONS table. You need to display all promo categories that do not have discount in their subcategory. Which two SQL statements give the required result? (Choose two.) A. SELECT promo_category …

OCP-1Z0-051 补充题库第26题单行函数特点

一、原题 Which two statements are true regarding single row functions? (Choose two.) A. They can be nested only to two levels B. They always return a single result row for every row of a queried table C. Arguments can only be column values or constant. D…

OCP-1Z0-051 第143题子查询中ANY、ALL的应用

一、原题 View the Exhibit and examine the structure of the PROMOTIONS table. You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the INTERNET category. Which query would give you t…

OCP-1Z0-051 第149题子查询中ALL的应用

一、原题 View the Exhibits and examine the structures of the COSTS and PROMOTIONS tables. Evaluate the following SQL statement: SQL>SELECT prod_id FROM costs WHERE promo_id IN (SELECT promo_id FROM promotions …

OCP-1Z0-051 第3题转义字符

一、原题 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. WHER…

OCP-1Z0-051 第168题使用子查询DELETE数据

一、原题 View the Exhibit and examine the description of SALES and PROMOTIONS tables. 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 …

OCP-1Z0-051 第5题 to_char的数字的格式

一、原题 Which SQL statements would display the value 1890.55 as $1,890.55? (Choose three)A. SELECT TO_CHAR(1890.55,$0G000D00) FROM DUAL;B. SELECT TO_CHAR(1890.55,$9,999V99) FROM DUAL;C. SELECT TO_CHAR(1890.55,$99,999D99) FROM DUAL;D. SELECT TO_CHAR(189…

OCP-1Z0-051 第148题子查询和ANY，ALL的使用

一、原题 View the Exhibit and examine the structure of the PRODUCTS tables. You want to generate a report that displays the average list price of product categories where the average list price is less than half the maximum in each category. Which query …

OCP-1Z0-051 第71题 oracle内置函数

一、原题 Which arithmetic operations can be performed on a column by using a SQL function that is built into Oracle database ? (Choose three .) A. addition B. subtraction C. raising to a power D. finding the quotient E. finding the lowest value 答案&…

OCP-1Z0-051 第70题 order by排升序，降序

一、原题 View the Exhibit and examine the structure of the PRODUCTS table. You want to display only those product names with their list prices where the list price is at least double the minimum price. The report should start with the product name having…

OCP-1Z0-051 第69题 ORDER BY 的null值排序

一、原题 You need to generate a list of all customer last names with their credit limits from the CUSTOMERS table. Those customers who do not have a credit limit should appear last in the list. Which two queries would achieve the required result? (Choose…

OCP-1Z0-051 第35题主外键规则

一、原题 Which two statements are true regarding constraints? (Choose two.) A. A table can have only one primary key and one foreign key. B. A table can have only one primary key but multiple foreign keys. C. Only the primary key can be defined at the col…

OCP-1Z0-051 第99题 AVG函数

一、原题 View the Exhibit and examine the structure of the PROMOTIONS table. Using the PROMOTIONS table, you need to find out the average cost for all promos in the ranges $0-2000 and $2000-5000 in category A You issue the following SQL statement: SQL&g…

OCP-1Z0-051 第95题 NVL,NVL2函数用法

一、原题 The PRODUCTS table has the following structure: name Null Type PROD_ID NOT NULL NUMBER(4) PROD_NAME VARCHAR2(25) PROD_EXPIRY_DATE …

OCP-1Z0-051 第96题 NVL2函数的详细用法

一、原题 Examine the structure of the INVOICE table. name Null Type INV_NO NOT NULL NUMBER(3) INV_DATE DATE INV_AMT NUMBER(10,2) Which two SQL statements would e…

OCP-1Z0-051 第93题 NVL函数

一、原题 View the Exhibit and examine the structure of the 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 sho…

OCP-1Z0-051 第166题使用子查询update表中的多列

一、原题 View the Exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables. You want to update the EMPLOYEES table as follows: -Update only those employees who work in Boston or Seattle (locations 2900 and 2700). -Set department_id for …

OCP-1Z0-051 补充题库第35题 LONG类型

一、原题 Examine the description of the EMP_DETAILS table given below: Exhibit: Which two statements are true regarding SQL statements that can be executed on the EMP_DETAIL table? (Choose two.) A. An EMP_IMAGE column can be included in the GROUP BY c…

OCP-1Z0-051 补充题库第1题 TRUNCATE命令的使用

一、原题 Evaluate the SQL statement: TRUNCATE TABLE DEPT; Which three are true about the SQL statement? (Choose three.) A. It releases the storage space used by the table. B. It does not release the storage space used by the table. C. You can roll back t…

OCP-1Z0-051 第170题 DELETE的注意事项

一、原题 Which two statements are true regarding the DELETE and TRUNCATE commands? (Choose two.) A. DELETE can be used to remove only rows from only one table at a time. B. DELETE can be used to remove only rows from multiple tables at a time. C. DELETE …

OCP-1Z0-051 第114题 HAVING，GROUP BY子句的应用

一、原题 View the Exhibit and examine the structure of the SALES table. The following query is written to retrieve all those product ID s from the SALES table that have more than 55000 sold and have been ordered more than 10 times. SQL> SELECT prod_id…

OCP-1Z0-051 第64题 order by使用别名注意事项

一、原题 View the Exhibit and examine the data in the PROMO_CATEGORY and PROMO_COST columns of the PROMOTIONS table. Evaluate the following two queries: SQL>SELECT DISTINCT promo_category to_char(promo_cost)"code" FROM prom…

OCP-1Z0-051 补充题库第23题表连接中别名的使用

一、原题 Evaluate this SQL statement: SELECT e.emp_name, d.dept_name FROM employees e JOIN departments d USING (department_id) WHERE d.department_id NOT IN (10,40) ORDER BY dept_name; The statement fails when executed. Which change fixes the error? A. re…

OCP-1Z0-051 补充题库第22题 SYNONYM同义词

一、原题 The ORDERS TABLE belongs to the user OE. OE has granted the SELECT privilege on the ORDERS table to the user HR. Which statement would create a synonym ORD so that HR can execute the following query successfully? SELECT * FROM ord; A. CREATE SYN…

OCP-1Z0-051 补充题库第38题 nvl用法

一、原题 See the Exhibit and Examine the structure of the 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 sho…

OCP-1Z0-051 补充题库第37题

一、原题 See the exhibit and examine the structure of the CUSTOMERS and GRADES tables: You need to display names and grades of customers who have the highest credit limit. Which two SQL statements would accomplish the task? (Choose two.) A. SELECT custn…

OCP-1Z0-051 第133题子查询的用法

一、原题 Which two statements are true regarding subqueries? (Choose two.) A. A subquery can retrieve zero or more rows. B. Only two subqueries can be placed at one level. C. A subquery can be used only in SQL query statements. D. A subquery can appear o…

OCP-1Z0-051 补充题库第42题 DROP TABLE命令的影响

一、原题 You issue the following command to drop the PRODUCTS table: SQL>DROP TABLE products; What is the implication of this command? (Choose all that apply.) A. All data in the table are deleted but the table structure will remain B. All data along …

OCP-1Z0-051 第56题日期时间段

一、原题 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. Wh…

OCP-1Z0-051 补充题库第44题 NATURAL JOIN自然连接

一、原题 View the Exhibits and examine the structures of the PRODUCTS, SALES, and CUSTOMERS tables.You issue the following query: SQL>SELECT p.prod_id,prod_name,prod_list_price, quantity_sold,cust_last_name FROM products p NATURAL JOIN sal…

OCP-1Z0-051 补充题库第3题隐式格式转换

一、原题 Here is the structure and data of the CUST_TRANS table: Exhibit: Dates are stored in the default date format dd-mm-rr in the CUST_TRANS table. Which three SQL statements would execute successfully? (Choose three.) A. SELECT transdate 10 FROM c…

OCP-1Z0-051 第146题子查询中的null值

一、原题 Evaluate the following SQL statement: SQL>SELECT cust_id, cust_last_name FROM customers WHERE cust_credit_limit IN (SELECT cust_credit_limit FROM customers WHERE cust_city Singapore); Which sta…

OCP-1Z0-051 补充题库第43题子查询

一、原题 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 products whose list price is the second h…

OCP-1Z0-051 补充题库第2题添加外键FOREIGN KEY

一、原题 You need to design a student registration database that contains several tables storing academic information. The STUDENTS table stores information about a student. The STUDENT_GRADES table stores information about the students grades. Both of th…

OCP-1Z0-051 第115题 HAVING，GROUP BY子句的应用

一、原题 View the Exhibit and examine the structure of the CUSTOMERS table. Evaluate the following SQL statement: SQL> SELECT cust_city, COUNT(cust_last_name) FROM customers WHERE cust_credit_limit > 1000 GROUP BY cust_city HAVING AVG(cust_credit_l…

OCP-1Z0-051 补充题库第13题 MERGE INTO语句

一、原题 Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: Which MERGE statement is valid? A. MERGE INTO new_employees c USING employees e ON (c.employee_id e.employee_id) WHEN MATCHED THEN UPDATE SET name e.first_name || , || e.last…

OCP-1Z0-051 第159题 insert语句中使用子查询

一、原题 View the Exhibit and examine the structure of the CUSTOMERS table. NEW_CUSTOMERS is a new table with the columns CUST_ID, CUST_NAME and CUST_CITY that have the same data types and size as the corresponding columns in the CUSTOMERS table. Evaluat…

OCP-1Z0-051 第91题 TO_DATE转换函数

一、原题 View the Exhibit and examine the structure of the PROMOTIONS table. Which two SQL statements would execute successfully? (Choose two.) A. UPDATE promotions SET promo_cost promo_cost 100 WHERE TO_CHAR(promo_end_date, yyyy) >…

在Oracle 11g 数据库上设置透明数据加密（TDE）

本文回答2个问题： 11g下简明的TDE设置过程由于11g不支持在线TDE，介绍2中11g下的加密表空间的迁移方法设置表空间TDE之前表空间没有加密时，很容易探测到明文数据： create tablespace unsectbs datafile unsectbs.dbf size 10…

从哪里下载 Oracle database 11g 软件

登入My Oracle Support，选择Patches & Updates 标签页，点击下方的Latest Patchsets链接： 然后单击Oracle Database，就可以下载11g软件了： 安装单实例数据库需要1和2两个zip文件，安装GI需要第3个zip文…

Oracle 11g 多数据库环境下的TDE设置

19c的TDE wallet的设置是在数据库中设置的，也就是粒度为数据库，因此不会有冲突。而11g的设置是在sqlnet.ora中，因此有可能产生冲突。这里先将一个重要概念，按照文档的说法，wallet是不能被数据库共享的。 If there …

OCP-1Z0-051 第48题索引

一、原题 Which two statements are true regarding indexes? (Choose two.) A. They can be created on tables and clusters. B. They can be created on tables and simple views. C. You can create only one index by using the same columns. D. You can create more t…