Francisco?
A. SELECT city_address, COUNT(*) FROM customers
WHERE city_address IN ('Los Angeles', 'San Francisco'); B. SELECT city_address, COUNT(*) FROM customers
WHERE city_address IN ('Los Angeles', 'San Francisco') GROUP BY city_address;
C. SELECT city_address, COUNT(customer_id) FROM customers
WHERE city_address IN ('Los Angeles', 'San Francisco') GROUP BY city_address, customer_id;
D. SELECT city_address, COUNT(customer_id) FROM customers
GROUP BY city_address IN ('Los Angeles', 'San Francisco');
Q: 69 You need to perform certain data manipulation operations through a view called EMP_DEPT_VU, which you previously created. You want to look at the definition of the view (the
SELECT statement on which the view was created.) How do you obtain the definition of the view?
A. Use the DESCRIBE command on the EMP_DEPT_VU view. B. Use the DEFINE VIEW command on the EMP_DEPT_VU view. C. Use the DESCRIBE VIEW command on the EMP_DEPT_VU view.
D. Query the USER_VIEWS data dictionary view to search for the EMP_DEPT_VU view.
E. Query the USER_SOURCE data dictionary view to search for the EMP_DEPT_VU view.
F. Query the USER_OBJECTS data dictionary view to search for the EMP_DEPT_VU view.
Q: 70 Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER NOT NULL EMP_NAME VARCHAR2(30) JOB_ID VARCHAR2(20) SAL NUMBER
MGR_ID NUMBER
DEPARTMENT_ID NUMBER
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 statement should pass values to the table columns as specified below: EMPLOYEE_ID: Next value from the sequence EMP_ID_SEQ
EMP_NAME and JOB_ID: As specified by the user during run time, through substitution variables
SAL: 2000
MGR_ID: No value
DEPARTMENT_ID: Supplied by the user during run time through substitution variable. The INSERT statement should fail if the user supplies a value other than 20 or 50.
Which INSERT statement meets the above requirements? A. INSERT INTO employees
VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did); B. INSERT INTO employees
VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did IN (20,50)); C. INSERT INTO (SELECT * FROM employees
WHERE department_id IN (20,50))
VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did); D. INSERT INTO (SELECT * FROM employees
WHERE department_id IN (20,50) WITH CHECK OPTION)
VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
E. INSERT INTO (SELECT * FROM employees
WHERE (department_id = 20 AND department_id = 50)
WITH CHECK OPTION )
VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
Q: 71 Which three are DATETIME data types that can be used when specifying column definitions? (Choose three.) A. TIMESTAMP
B. INTERVAL MONTH TO DAY C. INTERVAL DAY TO SECOND D. INTERVAL YEAR TO MONTH
E. TIMESTAMP WITH DATABASE TIMEZONE
Q: 72 Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER NOT NULL, Primary Key EMP_NAME VARCHAR2(30) JOB_ID NUMBER SAL NUMBER
MGR_ID NUMBER References EMPLOYEE_ID column
DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID column of the DEPARTMENTS table
You created a sequence called EMP_ID_SEQ in order to populate sequential values for the
EMPLOYEE_ID column of the EMPLOYEES table.
Which two statements regarding the EMP_ID_SEQ sequence are true? (Choose two.)
A. You cannot use the EMP_ID_SEQ sequence to populate the JOB_ID column.
B. The EMP_ID_SEQ sequence is invalidated when you modify the EMPLOYEE_ID column.
C. The EMP_ID_SEQ sequence is not affected by modifications to the EMPLOYEES table.
D. Any other column of NUMBER data type in your schema can use the EMP_ID_SEQ sequence.
E. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEES table.
F. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEE_ID column.
Q: 73 Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE
You issue these statements:
CREATE table new_emp ( employee_id NUMBER, name VARCHAR2(30)); INSERT INTO new_emp SELECT employee_id , last_name from employees; Savepoint s1;
UPDATE new_emp set name = UPPER(name); Savepoint s2;
Delete from new_emp; Rollback to s2;
Delete from new_emp where employee_id =180; UPDATE new_emp set name = 'James'; Rollback to s2;
UPDATE new_emp set name = 'James' WHERE employee_id =180; Rollback;
At the end of this transaction, what is true? A. You have no rows in the table.
B. You have an employee with the name of James.
C. You cannot roll back to the same savepoint more than once.
D. Your last update fails to update any rows because employee ID 180 was already deleted.
Q: 74 Which two tasks can you perform using only the TO_CHAR function? (Choose two.)
A. convert 10 to 'TEN' B. convert '10' to 10 C. convert 10 to '10' D. convert 'TEN' to 10
E. convert a date to a character expression
F. convert a character expression to a date
Q: 75 You need to create a table named ORDERS that contains four columns: 1. an ORDER_ID column of number data type 2. a CUSTOMER_ID column of number data type
3. an ORDER_STATUS column that contains a character data type 4. a DATE_ORDERED column to contain the date the order was placed
When a row is inserted into the table, if no value is provided when the order was placed, today's date should be used instead.
Which statement accomplishes this? A. CREATE TABLE orders ( order_id NUMBER(10), customer_id NUMBER(8), order_status VARCHAR2 (10), date_ordered DATE = SYSDATE); B. CREATE TABLE orders ( order_id NUMBER(10), customer_id NUMBER(8), order_status VARCHAR2 (10),
date_ordered DATE DEFAULT SYSDATE); C. CREATE OR REPLACE TABLE orders ( order_id NUMBER(10), customer_id NUMBER(8), order_status VARCHAR2 (10),
date_ordered DATE DEFAULT SYSDATE); D. CREATE OR REPLACE TABLE orders ( order_id NUMBER(10), customer_id NUMBER(8), order_status VARCHAR2 (10), date_ordered DATE = SYSDATE); E. CREATE TABLE orders ( order_id NUMBER(10), customer_id NUMBER(8), order_status NUMBER (10),
date_ordered DATE = SYSDATE);
F. CREATE TABLE orders ( order_id NUMBER(10), customer_id NUMBER(8), order_status NUMBER (10),
date_ordered DATE DEFAULT SYSDATE);
Q: 76 In which three cases would you use the USING clause? (Choose three.) A. You want to create a nonequijoin.
B. The tables to be joined have multiple NULL columns.
C. The tables to be joined have columns of the same name and different data types.
D. The tables to be joined have columns with the same name and compatible data types.
E. You want to use a NATURAL join, but you want to restrict the number of columns in the join condition.
Q: 77 The EMP table contains these columns: LAST_NAME VARCHAR2 (25) SALARY NUMBER (6,2)
DEPARTMENT_ID NUMBER (6)
You need to display the employees who have not been assigned to any department. You write the SELECT statement:
SELECT LAST_NAME, SALARY, DEPARTMENT_ID FROM EMP
WHERE DEPARTMENT_ID = NULL; What is true about this SQL statement ?
A. The SQL statement displays the desired results.
B. The column in the WHERE clause should be changed to display the desired results. C. The operator in the WHERE clause should be changed to display the desired results.
D. The WHERE clause should be changed to use an outer join to display the desired results.
Q: 78 Examine the SQL statement that creates ORDERS table: CREATE TABLE orders
(SER_NO NUMBER UNIQUE, ORDER_ID NUMBER,
ORDER_DATE DATE NOT NULL, STATUS VARCHAR2(10)
CHECK (status IN ('CREDIT', 'CASH')), PROD_ID NUMBER
REFERENCES PRODUCTS(PRODUCT_ID), ORD_TOTAL NUMBER,
PRIMARY KEY (order_id, order_date));
For which columns would an index be automatically created when you execute the above SQL statement? (Choose two.) A. SER_NO B. ORDER_ID C. STATUS D. PROD_ID E. ORD_TOTAL
F. composite index on ORDER_ID and ORDER_DATE Q: 79 Examine the description of the MARKS table: STD_ID NUMBER(4)
STUDENT_NAME VARCHAR2(30)