Quiz
Oracle Total Tester
Create a free account to finish this quiz
Save your progress and see your final score.
Already have an account? Sign in
Questions in this quiz (27)
1Which of the following statements accurately describes the implication of executing the following statement
(select all that apply)
UPDATE orders SET order_total = order_total + 100 WHERE product_id = 2302- AThe ORDER_TOTAL column of all rows in the ORDERS table is incremented by 100; but a commit is required to make the changes permanent
- BThe ORDER_TOTAL column of all rows in the ORDERS table with PRODUCT_ID values of 2302 are incremented by 100
- CThe update can be undone by executing a rollback command
- DA commit is not required to make the changes permanent since the UPDATE command is a DDL command
2What value is returned after executing the following statement?
SELECT INSTR(SUBSTR(decode(6/3*2,4,'abcabcabc',1, 'bcabcabca','cabcabcab'), 9), 'c')
from dual- A0
- B2
- C1
- DNone of the above
3If the current date is March 3, 2008, what format specification will return the date as the string "The Third of March 2008?"
- Aselect to_char(sysdate, '"The" fmDdspth "of" Month yyyy') from dual;
- Bselect to_char(sysdate, 'The fmDdspth of Month yyyy') from dual;
- Cselect to_char(sysdate, 'fmDdspth Month yyyy') from dual;
- Dselect to_char(sysdate,
4For which data types can the group functions MAX and MIN be used?
- ACLOB
- BDATE
- CLONG
- DNUMBER
- EVARCHAR2
5The following query is written to retrieve all those product IDs from the SALES table that have more than 55000 sold and have been ordered more than 10 times.
SELECT prod_id
FROM sales
WHERE quantity_sold > 55000 AND COUNT(*) > 10
GROUP BY prod_id
HAVING COUNT(*) > 10- AIt executes successfully and generates the required result
- BIt produces an error because count(*) should be specified in the SELECT clause also
- CIt produces an error because count(*) should be only in the HAVING clause and not in the WHERE clause
- DIt executes successfully but produces no result because COUNT (prod_id) should be used instead of COUNT(*)
6Which of the following statements are correct about undo?
(Choose all that apply.)- AOne database can have many undo tablespaces.
- BOne transaction can use many undo segments.
- CUndo segments and rollback segments cannot coexist.
- DOne undo segment can protect many transactions.
- EOne instance can have many undo tablespaces.
7Assuming SYSDATE=30-DEC-2007, what value is returned after executing the following statement?
(Choose the best answer.)
SELECT TRUNC(SYSDATE,'YEAR') FROM DUAL;- A01-JAN-2007
- B31-DEC-2007
- CNone of the above
- D01-JAN-2008
8What will happen if you query the EMP table shown below with the following?
--------------------------
EMP Table
--------------------------
Column Datatype Length
--------------------------
EMPNO NUMBER 4
ENAME VARCHAR2 30
SALARY NUMBER 14,2
COMM NUMBER 10,2
DEPTNO NUMBER 2
--------------------------
SELECT empno, DISTINCT ename, salary
FROM emp;- ADISTINCT is not a valid keyword in SQL.
- BNo values will be displayed because the statement will return an error.
- CEMPNO and unique values of the two columns, ENAME and SALARY, are displayed.
- DEMPNO, unique values of ENAME, and then SALARY are displayed.
9Which normal form is a table in if it has no multi-valued attributes and no partial dependencies?
- AThird normal form
- BFirst normal form
- CFourth normal form
- DSecond normal form
10Examine 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.)- AORD_TOTAL
- BORDER_ID
- CSTATUS
- DSER_NO
- EPROD_ID
- Fcomposite index on ORDER_ID and ORDER_DATE
11Examine the structure of the INVOICE table:
Name Null? Type
inv_no Not NULL Number(3)
inv_date DATE
inv_amount Number(10,2)
Which two SQL statements would execute successfully?
(Choose two.)- ASELECT inv_no, NVL2(inv_date, sysdate-inv_date, sysdate)
FROM invoice; - BSELECT inv_no, NVL2(inv_amt, inv_date, ‘Not Available’)
FROM invoice; - CSELECT inv_no, NVL2(inv_date, ‘Pending’, ‘Incomplete’)
FROM invoice; - DSELECT inv_no, NVL2(inv_amt, inv_amt*.25, ‘Not Available’)
FROM invoice;
- ASELECT inv_no, NVL2(inv_date, sysdate-inv_date, sysdate)
12
You issued the following command:
SQL> DROP TABLE employees;
Which three statements are true?- AThe employees table can be recovered using the rollback command.
- BAll indexes and constraints defined on the table being dropped are also dropped.
- CSequences used in the employees table become invalid.
- DThe space used by the employees table is reclaimed immediately.
- EThe employees table is moved to the recycle bin.
- FAll uncommitted transactions are committed.
13How can you move a schema from one user to another?
(Choose the best answer.)- AAttach the new user to the schema, then detach the old user from the schema.
- BUse the ALTER SCHEMA MOVE… command.
- CYou cannot move a schema from one user to another.
- DA schema can only be moved if it is empty (or if all objects within it have been dropped).
14A transaction consists of two statements. The first succeeds, but the second (which updates several rows) fails partway through because of a constraint violation.
What will happen?
(Choose the best answer.)- AThe second statement will be rolled back completely, and the first will remain uncommitted.
- BOnly the one update that caused the violation will be rolled back; everything else will remain uncommitted.
- CThe whole transaction will be rolled back.
- DOnly the one update that caused the violation will be rolled back; everything else will be committed.
- EThe second statement will be rolled back completely, and the first will be committed.
15For which action can you use the TO_DATE function?
- AFormat ’10-JAN-99’ to ‘January 10 1999’
- BConvert any date literal to a date
- CConvert any numeric literal to a date
- DConvert any date to a character literal
- EConvert any character literal to a date
16Which statement about the object type is true?
- AThey are structures that consist of only user-defined data types.
- BOnly one column in a table can be object type.
- CThey are structures that consist of only built-in data types.
- DThey are structures that consist of built-in or user-defined data types.
17What value is returned after executing the following statement?
SELECT SUM(SALARY) FROM EMPLOYEES;
Assume there are ten employee records and each contains a SALARY value of 100, except for one, which has a null value in the SALARY field.
(Choose the best answer.)- ANULL
- B900
- C1000
- DNone of the above
18Which assertion about the following queries is true?
SELECT COUNT(DISTINCT mgr), MAX(DISTINCT salary)
FROM emp;
SELECT COUNT(ALL mgr), MAX(ALL salary)
FROM emp;- AThey may return different numbers in column 1 but will always return the same number in column 2.
- BThey will always return the same numbers in columns 1 and 2.
- CThey will always return the same number in column 1 but may return different numbers in column 2.
- DThey may return different numbers in both columns 1 and 2.
19View the Exhibit; examine the structure of the PROMOTIONS table.
Promotions
promo_id NUMBER(6) NOT NULL
promo_cost NUMBER NOT NULL
promo_begin_date DATE NOT NULL
promo_end_date DATE NOT NULL
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?- ASELECT promo_name, (promo_cost/promo_end_date-promo_begin_date)/7 FROM promotions;
- BSELECT promo_name, promo_cost/promo_end_date-promo_begin_date/7 FROM promotions;
- CSELECT promo_name, promo_cost/(promo_end_date-promo_begin_date/7) FROM promotions;
- DSELECT promo_name, promo_cost/((promo_end_date-promo_begin_date)/7) FROM promotions;
20Which two statements are true about constraints?
(Choose two.)- AThe PRIMARY KEY and FOREIGN KEY constraints create a UNIQUE index.
- BThe NOT NULL constraint ensures that null values are not permitted for the column.
- CThe UNIQUE constraint does not permit a null value for the column.
- DA UNIQUE index gets created for columns with PRIMARY KEY and UNIQUE constraints.
21When using ANSI join syntax, which clause is used to specify a join condition?
- AON
- BWHERE
- CUSING
- DJOIN
22Jim executes the following SQL statement. What will be the result?
DELETE salary, commission_pct
FROM employees
WHERE department_id = 30;- AThe salary and commission_pct columns will be deleted from the employees table.
- BThe statement will produce an error.
- CAll the rows belonging to department_id 30 will be deleted from the table.
- DThe salary and commission_pct columns for all records with department_id 30 will be deleted (changed to NULL).
23What is true about sequences?
- AOnly the DBA can control which sequence is used by a certain table.
- BOnce created, a sequence is automatically available to all users.
- COnce created, a sequence is linked to a specific table.
- DOnce created, a sequence is automatically used in all INSERT and UPDATE statements.
- EOnce created, a sequence belongs to a specific schema.
24Which two are true about aggregate functions?
(Choose two.)- AYou can use aggregate functions on a table, only by grouping the whole table as one single group.
- BYou can use aggregate functions in any clause of a SELECT statement.
- CYou can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns.
- DYou cannot group the rows of a table by more than one column while using aggregate functions.
- EYou can pass column names, expressions, constants, or functions as parameter to an aggregate function.
- FYou can use aggregate functions only in the column list of the select clause and in the WHERE clause of a SELECT statement.
25Choose two statements that are true.
- AThe VARIANCE function returns the square root of the STDDEV.
- BThe LISTAGG function returns a total numeric sum of a list of data.
- CThe AVG function works on date and numeric data.
- DLISTAGG returns the concatenated values of the measure column.
- EThe STDDEV function returns the square root of the VARIANCE.
26Which of these is a defining characteristic of a complex view, rather than a simple view?
(Choose all correct answers.)- AJoining two tables
- BNaming the view’s columns with column aliases
- CRestricting the projection by selecting only some of the table’s column
- DPerforming an aggregation
- ERestricting the selection of rows with a WHERE clause
27Which three statements are true regarding sub queries?
(Choose three.)- ASub queries can contain ORDER BY but not the GROUP BY clause
- BMain query and sub query must get data from the same tables
- CSub queries can contain GROUP BY and ORDER BY clauses
- DOnly one column or expression can be compared between the main query and subqeury
- EMultiple columns or expressions can be compared between the main query and sub query
- FMain query and sub query can get data from different tables
Information
- Description
- Quiz on Oracle Total Tester, created by Brandon Gagon on 10/02/2019.
- Author
- Brandon Gagon
- Language
- English
- Views
- 6
- Rating
- No ratings yet Rate this quiz