oracle_20180502202112 Flashcards Preview

DSO > oracle_20180502202112 > Flashcards

Flashcards in oracle_20180502202112 Deck (500):
1

List the 2 main types of Joins?

1. INNER2. OUTER

2

What is another name for simple join?

INNER

3

List the 3 types of OUTER joins?

1. LEFT OUTER JOIN2. RIGHT OUTER JOIN3. FULL OUTER JOIN

4

What type of join returns all rows from multiple tables where the join condition is met?

INNER

5

Will this statement execute without errors? SELECT a.name, b.name, a.id FROM table1 a, table2 b WHERE a.id = b.id

Yes.This is an implicit Inner Join with aliases.

6

Rewrite the statement below using an INNER JOIN? SELECT a.name, b.name, a.id FROM table1 a, table2 b WHERE a.id = b.id

SELECT a.name, b.name, a.idFROM table1 aINNER JOIN table2 bON a.id = b.id

7

Rewrite the statement below using an INNER JOIN but add another join column named lastname in which is in both tables. Also add lastname to the select clause. SELECT a.name, b.name, a.id FROM table1 a, table2 b WHERE a.id = b.id

SELECT a.name, b.name, a.id, a.lastnameFROM table1 aINNER JOIN table2 bON a.id = b.idAND a.lastname = b.lastname

8

Rewrite the statement below using an INNER JOIN adding another table3 which has both an id and a lastname column. SELECT a.name, b.name, a.id, a.lastname FROM table1 a INNER JOIN table2 b ON a.id = b.id AND a.lastname = b.lastname

SELECT a.name, b.name, a.id, a.lastnameFROM table1 aINNER JOIN table2 bON a.id = b.idAND a.lastname = b.lastnameINNER JOIN table3 cON a.id = c.idAND a.lastname = c.lastnameNote: in this case the order of tables does not matter and table3 could be joined to table2 instead

9

Will this statement execute with errors? SELECt a.name, b.name, a.id FROM table1 a JOIN table2 b ON a.id = b.id

No[INNER] JOINThe keyword INNER is not necessary in Oracle.Also case letters do not matter in the keyword SELECt

10

What type of join is needed if you want all rows returned from the left table an only those rows that match the Join from the right table?

LEFT [OUTER] JOINThe keyword OUTER is not necessary in Oracle.

11

What type of join is needed if you want all rows returned from the right table an only those rows that match the Join from the left table?

RIGHT [OUTER] JOINThe keyword OUTER is not necessary in Oracle

12

What type of join will return all rows from both tables with nulls where the join condition is not met?

FULL [OUTER] JOINThe keyword OUTER is not necessary in Oracle

13

What 3 clauses of a SQL statement can have a subquery?

1. SELECT2. FROM3. WHERE

14

If JOINING 2 tables would you use the ON clause or USING clause if you are joining fields with different names, but the same data types?

ON clause joins columns with different names, but the same data types.

15

If columns are joined with the ON clause and have the same name between the tables how are the columns differentiate in other clauses of the SQL statement?

Either a table prefix or a alias prefix will differentiate the columns that are the same names between different tables.Example using table prefix to differentiate:SELECT table1.id, table2.id, table1.code, table2.codeFROM table1JOIN table2ON table1.id = table2.idAND table1.code = table2.codeExample using aliases prefix to differentiate:SELECT a.id, b.id, a.code, b.codeFROM table1 aJOIN table2 bON a.id = b.idAND a.code = b.code

16

If columns are joined with the USING clause and have the same name between the tables how are the columns differentiate in other clauses of the SQL statement?

The columns defined in the USING at not prefixed.Example:SELECT id, codeFROM table1JOIN table2USING (id, code)

17

Inner Joins can use the JOIN..ON clause or the WHERE clause. What is the syntax of this statement using the WHERE clause to join. SELECT table1.id, table2.id, table1.code, table2.code FROM table1 JOIN table2 ON table1.id = table2.id AND table1.code = table2.code

SELECT table1.id, table2.id, table1.code, table2.codeFROM table1, table2WHERE table1.id = table2.idAND table1.code = table2.code

18

What is the syntax if the following code was changed to a NATURAL JOIN? SELECT table1.id, table2.id, table1.code, table2.code FROM table1 JOIN table2 ON table1.id = table2.id AND table1.code = table2.code

SELECT id, codeFROM table1NATURAL JOIN table2

19

What are results from this SQL Statement? SELECT promo_name, cust_name FROM promotions p JOIN sales s ON (time_id BETWEEN begin_date AND end_date) JOIN customer c ON (s.cust_id = c.cust_id) AND time_id < '30-oct-2007'

It will execute without errorsstrange how the time_id is reference between tables and also allowing the BETWEEN clause?

20

If a SQL statement has many join what order are they executed?

Left to right, unless parenthesis are involved

21

List 3 main categories of Joins?

1. Natural or Inner2. Outer3. Cross

22

List 3 main clauses for the Natural Joins.

1. NATURAL JOIN2. USING3. JOIN ON

23

If 2 tables are joined using a NATURAL JOIN what must exist between the 2 tables?

At least one column name is the same in each table which also need to be the same data type.

24

If 2 tables are to be joined that have several columns with matching names, what can be used to limit the number of column being join through a natural join?

SELECT*FROM tablename1JOIN tablename2USING (fieldname1, fieldname2)

25

Equijoin are also called?

INNERalso Simple Joins

26

What can you not used on the column name that is joins using the USING clause?

an AliasSince the same field is in both tables, the column name does not (cannot) have an Alias.

27

If a table name has an alias can you still use the table name in other clauses of the SQL statement?

No

28

What is the syntax of a inner join when the column names between 2 tables that are to be join are named differently?

SELECT *FROM tablename1JOIN tablename2ON (ColumnameA = ColumnNameB)NOTE: In this example Aliases are not needed since the column names are differentAlso Note: The Parenthesis are optional

29

How can you modify the following statement to add a WHERE clause without modifying the results? SELECT * FROM tablename1 a JOIN tablename2 b ON (a.columnName1 = b.ColumnName1) AND a.number = 1

SELECT *FROM tablename1 aJOIN tablename2 bON (a.columnName1 = b.ColumnName1)WHERE a.number = 1

30

A CROSS JOIN is also called a?

Cartesian Join

31

What happens in a CROSS JOIN?

Each record from one table is matched to a record from the 2nd table.So if Table1 has 10 records and Table2 has 10 records the results are 100 records.

32

What happen in an Inner Join?

The return consists of record that match between the two tables based on the joined fields.

33

What is an LEFT OUTER JOIN?

The return consists of All records from the left table and only matching records from the right table?SELECT *FROM table1 aLEFT OUTER JOIN Table2 bON a.ID = b.IDAll records from table1 will appear

34

What is an RIGHT OUTER JOIN?

The return consists of All records from the right table and only matching records from the left table?SELECT *FROM table1 aRIGHT OUTER JOIN Table2 bON a.ID = b.IDAll records from table2 will appear

35

What is a FULL OUTER JOIN?

The return consists of all matching record and then all unmatched records from the Left table and then all unmatched records from the Right table.SELECT *FROM table1 aRIGHT OUTER JOIN Table2 bON a.ID = b.IDAll records from each table will appear but the matching records will appear only once.

36

In the following select statement how many records will be returned? SELECT * FROM table1 a INNER JOIN Table2 b on a.ID = b.ID Table1 has 10 records Table2 has 12 records 2 records IDs match between the tables

2 records will be returned

37

In the following select statement how many records will be returned? SELECT * FROM table1 a LEFT OUTER JOIN Table2 b ON a.ID = b.ID Table1 has 10 records Table2 has 12 records 2 records IDs match between the tables

10 records will be returned

38

In the following select statement how many records will be returned? SELECT * FROM table1 a RIGHT OUTER JOIN Table2 b ON a.ID = b.ID Table1 has 10 records Table2 has 12 records 2 records IDs match between the tables

12 records will be returned

39

In the following select statement how many records will be returned? SELECT * FROM table1 a FULL OUTER JOIN Table2 b ON a.ID = b.ID Table1 has 10 records Table2 has 12 records 2 records IDs match between the tables

20 records will be returned2 matched records8 unmatched records from table110 unmatched records from table2

40

Can a NATURAL JOIN also be an OUTER JOIN?

Yes.Syntax: NATURAL LEFT OUTER JOIN NATURAL RIGHT OUTER JOIN NATURAL FULL OUTER JOIN

41

A self join query has to have what?

a subquery of the same table

42

Is this an example of a Left Outer Join or a Right Outer Join? SELECT a.cust_first_name, a.cust_last_name, b.order_date, b.order_mode, b.order_status, b.order_total FROM customers a, orders b WHERE a.customer_id=b.customer_id (+) ORDER BY a.customer_id;

Left outer join.The (+) in the WHERE clause indicates an outer join on the ORDERS table, giving the list of customers without orders.

43

What is a materialized view?

The following are characteristics of materialized views: 1. They are refreshed when the data gets changed. 2. They are used to replicate data from another database. 3. They are used in data-warehouse environments where data needs to be aggregated and stored so that queries and reports run faster.

44

What would be a reason to use a Cartesian Join?

A Cartesian product can be helpful for testing purposes when there is a need to generate a large number of rows to simulate a reasonable amount of data.

45

What will be the difference in results between these two SELECT clauses? SELECT a, count(a) FROM table GROUP BY a; SELECT a, count(*) FROM table GROUP BY a;

The first SELECT will not count any nulls in column a, while the second SELECT will count all rows

46

What is the simplest syntax to return all columns of a table as well as AVG(column1)?

SELECT t1.*, AVG(column1)FROM T1GROUP BY T1.*

47

What function can be used in the WHERE clause if you want all records from column1 from table1 ending with an 'n'?

WHERE SUBSTR(column,-1,1) = 'n'

48

"What is the syntax if you wanted to select columns1 and column2 from table1 where column4 is greater than zero and column7 is begins with an ""A"" or ""b"" or ""C""?"

SELECT column1, column2FROM table1WHERE column4 > 0AND(SUBSTR(column7,1) = 'A' ORSUBSTR (column7,1) = 'b'ORSUBSTR(column7, 1) = 'C')Between cannot be used because of upper/lower caseParenthesis are needed because of the AND/OR combinations

49

What is returned from the following statement? SELECT SUBSTR( 'ABCdefgh', 2, 4) FROM dual

BCde

50

What is returned from the following statement? SELECT SUBSTR( 'ABCdefgh', 5) FROM dual

efghIf the length is not specific all remaining characters are returned

51

What is returned from the following statement? SELECT SUBSTR( 'ABCdefgh', -3, 2) FROM dual

fg

52

Notice the spaces before and after the string, what is returned from the following statement? SELECT TRIM( ' Tech ') FROM dual

Tech spaces in front and after have been removed

53

What is returned from the following statement? SELECT TRIM( 'T' FROM 'Tech') FROM dual

ech

54

What is returned from the following statement? SELECT TRIM(LEADING '0' FROM '0001240') FROM dual

1240

55

What is returned from the following statement? SELECT TRIM(TRAILING 'x' FROM 'xTechx') FROM dual

xTech

56

What is returned from the following statement? SELECT TRIM( BOTH '1' FROM '1Tech111') FROM dual

Tech

57

What is returned from the following statement? SELECT RTRIM( 'techxyxyxyxxx', 'xyz') FROM dual

techThe RTRIM remove all characters that match regardless of pattern

58

What is returned from the following statement? SELECT LTRIM( '1443Tech1545', '431') FROM dual

Tech1545

59

What is returned from the following statement? SELECT INITCAP( 'GEORGE BURNS') FROM dual

George BurnsInitCap capitalizes the first letter of each word

60

What is returned from the following statement? SELECT UPPER( 'GeorGe Burns 123') FROM dual

GEORGE BURNS 123All letters are capitalized

61

What is returned from the following statement? SELECT LOWER( 'GeorGe Burns 123') FROM dual

george burns 123All letters are lower case

62

What is returned from the following statement? SELECT LENGTH( null) FROM dual

null

63

What is returned from the following statement? SELECT LENGTH( '') FROM dual

null

64

What is returned from the following statement? SELECT LENGTH( ' ') FROM dual

1There is one space

65

What is returned from the following statement? SELECT LENGTH( 'test') FROM dual

4

66

What is returned from the following statement? SELECT CONCAT( 'A', 'B') FROM dual

AB

67

What is returned from the following statement? SELECT 'A' || 'B' FROM dual

AB

68

Using the CONTACT function what is the syntax for concatenating 'A', 'B' and 'C'?

CONCAT( CONCAT( 'A', 'B'), 'C')2 nested CONCAT Functions, because the CONCAT function can only take 2 arguments

69

Using the || operator what is the syntax for concatenating 'A','B' and 'C'?

'A' || 'B' || 'C'

70

What is returned from the following statement? SELECT REPLACE( '123123Tech', '123', '2') FROM dual

222222Tech

71

What is returned from the following statement? SELECT REPLACE( '123123Tech', '123') FROM dual

TechIf the 3rd argument of the REPLACE function is empty then the search string is deleted from the 1st argument

72

What is returned from the following statement? SELECT INSTR( 'abcabc', 'bc') FROM dual

2INSTR function finds the first occurrence of the string

73

What is returned from the following statement? SELECT INSTR( 'abcabc', 'bc', 1, 2) FROM dual

5INSTR functions finds the 2nd occurrence of the string starting at the 1 position.

74

What is returned from the following statement? SELECT INSTR( 'abcabc', 'bc, -3, 1) FROM dual

5INSTR find the the first occurrence of the string starting -3 position from the end of the string

75

What is returned from the following statement? SELECT LPAD( '1234', 6, '*') FROM dual

**1234

76

What is returned from the following statement? SELECT LPAD( '1234', '2', '*') FROM dual

1234

77

What is returned from the following statement? SELECT RPAD( '1234', 6, '*') FROM dual

1234**

78

What is returned from the following statement? SELECT RPAD( '1234', '2, '*') FROM dual

1234

79

What is returned from the following statement? SELECT COALESCE (null, 1, null) FROM dual

1COALESCE returns the first non-null value in the list or column names

80

What is returned from the following statement? SELECT ASCII( 'T2') FROM dual

84The ASCII function returns the ascii code from the first letter in the string

81

What is returned from the following statement? SELECT CHR( 84) FROM dual

T

82

What is returned from the following statement? SELECT ABS( -23.65) FROM dual

23.65

83

What is returned from the following statement? SELECT ROUND( 125.612) FROM dual

126The value of zero is assumed

84

What is returned from the following statement? SELECT ROUND( 125.661, 1) FROM dual

125.7

85

What is returned from the following statement? SELECT ROUND( 125.5 ,-1) FROM dual

130The minus means round 1 digit to the left of the decimal

86

What is returned from the following statement? SELECT TRUNC( 125.8) FROM dual

125

87

What is returned from the following statement? SELECT TRUNC( 126.34, -1) FROM dual

120The minus mean truncate 1 digit to the left of the decimal

88

What is returned from the following statement? SELECT TRUNC( 125.612, 1) FROM dual

125.6

89

What is returned from the following statement? SELECT TO_CHAR( 1210.73, '9999.9') FROM dual

1210.7The TO_CHAR function will have a leading space added when a format model is used for the sign (+/-)

90

What is returned from the following statement? SELECT TO_CHAR( 1210.73, '9,999.9') FROM dual

1,210.7The TO_CHAR function will have a leading space added when a format model is used for the sign (+/-)

91

What is returned from the following statement? SELECT TO_CHAR( 1210.73, '009999.9') FROM dual

001210.7The TO_CHAR function will have a leading space added when a format model is used for the sign (+/-)

92

what is returned from the following statement? SELECT TO_NUMBER( '1210.73') FROM dual

1210.73

93

What is returned from the following statement? SELECT TO_NUMBER( '1210.73', '999.99') FROM dual

1210.73

94

If the value of ID is 5, what is returned from the following statement? SELECT DECODE ( TRUNC(( id - 1) / 10), 0, 'A', 1, 'B', 2, 'C', 'D' ) FROM table

A

95

If the value of ID is 15, what is returned from the following statement? SELECT DECODE ( TRUNC(( id - 1) / 10), 0, 'A', 1, 'B', 2, 'C', 'D' ) FROM table

B

96

If the value of ID is 66, what is returned from the following statement? SELECT DECODE ( TRUNC(( id - 1) / 10), 0, 'A', 1, 'B', 2, 'C', 'D' ) FROM table

D

97

Why is it necessary to use a formula in the DECODE statement? SELECT DECODE ( TRUNC(( id - 1) / 10), 0, 'A',, 1, 'B', 2, 'C', 'D' ) FROM table

DECODE only allows single values and no ranges

98

What is returned from the following statement? SELECT NULLIF( 12, 12) FROM dual

nullThe NULLIF function returns a null if both arguments are equal

99

What is returned from the following statement? SELECT NULLIF( 12, 13) FROM dual

12

100

What is returned from the following statement? SELECT NULLIF( null, 12) FROM dual

This will cause an errora Null value cannot be explicitly added to the NULLIF function

101

What is returned from the following statement? SELECT NULLIF( 'A', 'B') FROM dual

Anumeric, text and date data types are allowed to be compared, but both arguments must match in data types.

102

If the value of ID is null, what is returned from the following statement SELECT NVL( id, 1) FROM dual

1The 2nd argument is return from an NVL function only if the first argument is null.

103

If the value of ID has a value of 2, what is returned from the following statement SELECT NVL( id, 1) FROM dual

2The 2nd argument is return from an NVL function only if the first argument is null.

104

If the value of ID is null, what is returned from the following statement SELECT NVL2( id, 1, 3) FROM dual

3For an NLV2 function, the first value is returned if ID is not null. The second value is returned if the ID is null.

105

If the value of ID has a value of 2, what is returned from the following statement SELECT NVL2( id, 1, 3) FROM dual

1For an NLV2 function, the first value is returned if ID is not null. The second value is returned if the ID is null.

106

Using the LIKE operator and a function, create a WHERE clause that would find all records from column Parts which begin with 'h' or 'H'.

WHERE UPPER(Parts) LIKE 'H%'

107

If the SELECT clause has one or more of the functions SUM, COUNT, MIN, MAX or AVG function, what other clause if needed?

GROUP BYIf the Select Clause only has those functions without any column name or other expression, then the GROUP BY is not necessary, for example: SELECT avg(field) FROM table1

108

If hire_date is a date data type, what data type will be returned by this SQL statement? SELECT TO_NUMBER(hire_date + 7) FROM emp

A numeric data type, which can also be interpreted as a date data type

109

Create the WHERE clause that can find all records in the column customer_name where the values must have an 'e' anywhere in the string and must have an 'a' as the 2nd to last character.

WHERE INSTR(customer_name,'e') <> 0AND SUBSTR(customer_name,-2,1) = 'a'

110

What is returned from the following statement? SELECT COUNT(DISTINCT dept_id) FROM employees WHERE last_name = 'Smith'

The number of departments which has an employee with the last name of Smith

111

What is returned from the following statement? SELECT COUNT(dept_id) FROM employees WHERE last_name = 'Smith'

The total number of employees whose last name is Smith

112

Write a SQL statement which will return the value of the field ename, the total number of character of the value in the field ename, and what position is the letter 'g' in the value of the field ename from table1.

SELECT ename, LENGTH(ename), INSTR(ename, 'g') FROM table1

113

What is return from the following statement? SELECT item_no, AVG(qty) FROM table1 HAVING AVG(qty) > MIN(qty) * 2 GROUP BY item_no

It returns the item number with the average quantity, where the average quantity is more than double the minimum quantity for that item.

114

How would you modify this SQL Statement where the minimum is greater than the average for that particular item? SELECT item_no, AVG(qty) FROM table1 HAVING AVG(qty) > MIN(qty) * 2 GROUP BY item_no

SELECT item_no, AVG(qty)FROM table1HAVING min(qty) > avg(qty)GROUP BY item_no

115

What is the result of the following statement? SELECT promo_name, DECODE(NULLIF(promo_date, promo_date), NULL, 'Same Day') FROM promos

NullNULLIF(promo_date,promo_date) resolves to nullDECODE - since the expression result (first argument) is null, and the search value is a null (second argument), the third argument is returned.

116

What is the result of the following statement? SELECT promo_name, DECODE((NULLIF(promo_date, promo_date-1)), NULL, 'Same Day') FROM promos

Same DayNULLIF(promo_date,promo_date-1) does not resolve to nullDECODE - because of the extra parenthesis, the first argument is true, since the first statement is not null, the second value is returned

117

What arithmetic operation can be performed by a SQL function that is built into the Oracle database equivalent to addition?

SUM()

118

What arithmetic operation can be performed by a SQL function that is built into the Oracle database equivalent to subtraction?

No function

119

What arithmetic operation can be performed by a SQL function that is built into the Oracle database equivalent to finding the lowest value?

MIN()

120

What arithmetic operation can be performed by a SQL function that is built into the Oracle database equivalent to raising to a power?

POWER()

121

What arithmetic operation can be performed by a SQL function that is built into the Oracle database equivalent to finding a quotient?

no function

122

What arithmetic operation can be performed by a SQL function that is built into the Oracle database equivalent to finding a remainder?

MOD()

123

Can a single row function return a data type value different from the argument?

YesTO_CHAR() convert one data type to another

124

A COALESCE function returns the first non null value from its parameter list. What needs to be common to all items in the parameter list?

They need to all be the same data type

125

Are the following 2 statements equivalent? COALESCE(expr1,expr2, expr3) NVL(expr3,NVL(expr2,expr1))

NoIf the NVL expressions were in reverse order it would be equivalent to the COALESCENVL(expr1, NVL(expr2, expr3))

126

"What is the result of this statement if ename is SMITH and the date is 17-DEC-80? SELECT CONCAT(SUBSTR(INITCAP(ename),1,3), REPLACE(hiredate,'-')) ""USERID"" FROM Employees"

The result is Smi17DEC80

127

What is the maximum number of parameters allowed in the REPLACE function?

3

128

What is the mandatory number of parameters for the REPLACE function?

2

129

What is the general syntax of the REPLACE function?

REPLACE(source string, search string, [replacement string])

130

What function will compare two values and determine if they are equal?

NULLIF(string1, string2) it returns a null if the 2 parts are equal

131

How many mandatory parameters are required for the DECODE clause?

3DECODE(expression1, compare1, ifTrue[, ifFalse])

132

What is the syntax for the following statement using the DECODE function? IF field1 = 5 Then 10 Elseif field2 = 6 Then 15 else 20 end

DECODE(field1,5,10,DECODE(field2,6,15),20)

133

What function returns all lower case letters for a text string?

LOWER()

134

What function returns all upper case letters for a text string?

UPPER()

135

What function returns upper case letter for the first letter of each word and the rest lower case?

INITCAP()

136

What is the symbol for the concatenate operator?

|| (two parallel lines)

137

If there are no parenthesis, which are evaluated first: Concatenation operators or Comparison conditions

Concatenation operator then Comparison conditions

138

What are the 2 main return types (not data types) of functions?

1. single row2. multiple row

139

In what 3 clauses can single row functions be used?

1. SELECT2. WHERE3. ORDER BY

140

List 3 single row characters functions that modify the case of the string?

1. LOWER2. UPPER3. INITCAP

141

What function can be used for concatenating two strings?

CONCAT( string1, string2)

142

What 2 single row character functions return numbers?

1. LENGTH()2. INSTR()

143

What is the result of the following statement? TRIM('W' FROM 'HellowWorld')

HellowWorldThe trim only removed matching characters from the beginning or end of the phrase.

144

What function can you use so that a user does not have to remember to enter a word in all Capital letters for a condition in a WHERE clause?

UPPER('&name')The single quotes are needed

145

What function is used to retrieve all strings ending with 'n'?

SUBSTR(column_name,-1,1) = 'n'

146

What function returns the remainder of a division?

MOD()

147

What is returned from ROUND(45.926,2)?

45.93

148

What is returned from TRUNC(45.926,2)?

45.92

149

What is returned from ROUND(45.923,-1)?

50

150

What is returned from TRUNC(45.923,-1)?

40

151

What is returned from TRUNC(45.923)?

45

152

List 3 explicit data type conversions?

1. TO_CHAR2. TO_NUMBER3. TO_DATE

153

Are numerical values in the TO_CHAR function with a format element Truncated or Rounded?

Rounded

154

What is returned from NULLIF('A','A')?

null

155

What is returned from NVL(null,'A')?

A

156

What is returned from NVL2(NULL,'A','B')?

A

157

What 4 data types can be used in an NVL function?

1. Number2. Date3. Char4. Varchar2

158

What is returned from NVL2(1,null,3)?

3

159

What is returned from NVL2(null,1,2)?

1

160

What is returned from NULLIF('A','B')?

A

161

What is returned if expr1 is 12? CASE expr1 WHEN 10 THEN 'A' WHEN 20 THEN 'B' ELSE 'C' END

C

162

What is returned if expre1 is 12? DECODE( expr1, 10, 'A', 20, 'B', 'C')

C

163

List at least 5 group functions.

1. AVG2. COUNT3. MAX4. MIN5. SUMAlso6. STDDEV7. VARIENCE

164

What keyword can be used inside all Group Functions?

DISTINCT Such as AVG(DISTINCT fieldname)The Keyword ALL is default

165

How are Nulls handled by default in all group functions?

all nulls are ignored by default

166

How can you include nulls in a the AVG function?

Use NVL, NVL2 or COALESCE functionsSuch as AVG(NVL(fieldname,0)) which will replace zeros for null in the calculation.

167

All Group functions use numeric values. List 3 of those group functions which can also be used on other data types?

1. MIN2. MAX3. COUNT

168

What is the syntax of the COUNT function for counting all unique city_name with not being case sensitive?

COUNT ( DISTINCT ( UPPER (city_name)))orCOUNT ( DISTINCT ( LOWER (city_name)))

169

Date, numeric and character functions that change a date data types are called what type of functions?

conversion functions

170

What is returned from the following statement? SELECT LENGTH(TO_CHAR('012')) FROM dual

3

171

What is returned from the following statement? SELECT LENGTH(TO_CHAR('012','000')) FROM dual

4The TO_CHAR function will have a leading space added when a format model is used because of the possibility of a sign (+/-)

172

What is returned from the following statement? SELECT LENGTH(TO_CHAR('012','FM000')) FROM dual

3The FM remove the leading space

173

What is returned from the following statement? SELECT LENGTH(TO_CHAR('-012','000')) FROM dual

4The TO_CHAR function leading space is filled by the sign (+/-)

174

The is the difference in the return from the following statements? TO_CHAR(9,DDSPTH) TO_CHAR(9,DDTHSP)

The returns are the same, the order of the format element suffixes SP or TH does not matter.NINETH

175

What is returned from the following statement? SELECT 'Today' || '''s Special' FROM dual

Today's SpecialThe three quotes return one quoteAlso any extra spaces in a statement outside of quotes are ignored

176

What is returned from the following statement? SELECT NVL(Null,1) FROM dual

NullNull cannot be explicitly specified in an NVL function

177

What is returned from the following statement if the value in column1 is a null? SELECT NVL(column1,1) FROM Dual

1

178

What are the 3 character case conversion functions?

1. UPPER2. LOWER3. INITCAP

179

What is returned from the following statement? CONCAT('The number is: ',5)

The number is: 5The concat function can accept text, date or number data types.Oracle implicitly converts other data types to Char?

180

What is returned from the following statement? REPLACE(9999.0 - 1, '8', '77')

99977The expression 9999.0 - 1 is implicitly converted to text of 9998.0 then the 8 is replace with 77

181

What is returned from the following statement? MOD(3,2)

.5

182

What is returned from the following statement? MOD(3,-2)

-0.5

183

What is significant of the return of the following statement? MOD ( number, 2)

If the return is zero the number is even.If the return is 1 the number is odd.

184

What is returned from the following statement? SELECT NVL(1234,' ') FROM dual;

This statement will error, because both values must be the same data type in an NVL statement.The NVL can take numerical, date or character data types, the requirement is both arguments must match data types.

185

What is returned from the following statement? SELECT TO_NUMBER ('$3000') FROM dual;

an errorThe TO_NUMBER cannot interpret the $

186

What is returned from the following statement? SELECT TO_NUMBER('$3,000.67','$999,999.99') FROM dual;

3000.67 as a number type

187

What is returned from the following statement? SELECT NULLIF(1,2-1) FROM dual;

NullBoth values are equal

188

What is returned from the following statement? SELECT NULLIF('01-JAN-2013','01-JAN-13') FROM dual;

01-Jan-2013one string is longer than the other so they are not equal

189

When a return of a function is used as an input to another function, it is termed as?

nested functionExample: UPPER ( LOWER ('test'))

190

Which of the following can be used in the SELECT clause? MIN MAX AVG NVL

only the NVL can be used in the SELECT clause

191

Which of the following can be used in the GROUP BY clause? NVL AVG COUNT DECODE

only the AVG can be use in the GROUP BY clause

192

What is returned from the following Statement? SELECT COUNT(ALL column1) FROM table1

The count ignores the nulls in column1 but counts the duplicatesSame as: SELECT COUNT(column1) FROM Table1

193

What is restrictive of data types with the following functions? AVG() SUM() STDDEV() VARIANCE()

all take only 1 numeric data type

194

What is restrictive of the data types with the following functions? MAX() MIN()

They can take numeric, character or date data types

195

How many levels deep can Group functions be nested?

only 2

196

Can a group level function be inside a single-row function?

YesExample: TO_CHAR( MAX (12))

197

Can a single-row function be nested inside a group level function?

YesExample: MAX ( TO_CHAR (12))

198

What is the format model for converting the text '$9,784.34' to a numerical value?

TO_NUMBER( '9,784.34', '$9,999.99')orTO_NUMBER( '9,784.34', 'L9G999D99')The L signals a currency symbolThe G signals a commaThe D signals a decimal

199

What is returned from the following statement? SELECT 'this is a ' || null || 'test with nulls' from dual;

this is a test with nullsA null is treated as an empty space in a text

200

What is returned from the following statement? SELECT NULLIF( 'A', 1) FROM dual

NullThe data types do not match. A text is compare to a number

201

What is returned by this statement? SELECT NVL2(inv_amt, inv_amt * 25, 'Not Available')

errorbecause one return value is numeric and the other return value is text

202

Is this syntax correct? SELECT COUNT(DISTINCT *) FROM Table1

Yes. It will count all the unique records from table1, not counting any duplicate rows.

203

What two data types can the TRUNC function be used with?

1. NUMBER2. DATE

204

A TO_CHAR function can have what 2 data types?

Number or date

205

Will this statement execute without errors? SELECT SUM( DISTINCT NVL( subject1,0)) , MAX( subject1) FROM marks WHERE Subject1 > subject2

Yes.A DISTINCT can be within a SUM function)The GROUP BY Clause is not needed.

206

Will this statement execute without errors? SELECT AVG(int_date) FROM invoice;

No.The AVG function will not take the date data type.

207

What is the simplest syntax for the SELECT clause to return all fields of a table?

SELECT *

208

What is the simplest syntax for the SELECT clause to return all unique fields of a table?

SELECT DISTINCT *

209

What default keyword in the SELECT clause will return all rows including duplicates?

ALLSyntax: SELECT ALL *The keyword ALL is default

210

What is a clause?

a subset of a command that modifies the command.

211

What is the correct order of the following clauses in a select statement? ORDER BY SELECT WHERE HAVING GROUP BY

SELECTFROMWHEREGROUP BYHAVINGORDER BY

212

If table aliases are created in the FROM clause what other clauses can use those aliases?

SELECTWHEREGROUP BYORDER BY

213

What is the syntax of an ORDER BY clause to display column2 in alphabetical order and column1 in reverse numeric order?

ORDER BY column2 DESC, column1alsoORDER BY 2 DESC, 1

214

What does ORDER BY 1, 2 mean?

The first column in the select clause is ordered first and then the second column is order within the values of the first column.

215

What is the keyword in the ORDER BY clause meaning to sort from lowest to highest?

ASCmeaning AscendingASC is the default

216

What keyword will only display the first 10 rows of a SELECT statement?

WHERE ROWNUM <= 10

217

What is the syntax of a Select statement if you are adding column1 + column2 but want to have the column name as SUM?

SELECT column1 + column2 AS SUMthe as is optional but recommended

218

How many columns will be returned from this SELECT clause? Select column1 column2 from T1

1 column which will have the column name (Alias) of Column2.

219

What is the FROM clause syntax for creating an alias 'X' for a table named Table1

FROM table1 AS XThe keyword AS is optional

220

If column aliases are created in the SELECT clause which other clause CANNOT use those aliases?

GROUP BY

221

What is the only clause that you can create table aliases?

FROM

222

What is the only clause that you can create column aliases?

SELECT

223

What will be the difference of the returned data between these 2 clauses? ORDER BY column1, column2 ORDER BY 1, 2

Identical results

224

If column aliases are created in the SELECT clause which other clause CAN use those column aliases?

ORDER BY

225

Will this statement execute without errors? SELECT column1 A, column2 B, column1 + column2 C FROM table GROUP by column1, column2, column1 + column2

This will execute without errorsThe column1 + column2 in the GROUP BY is necessary and will error without it.

226

Which clause from a SELECT statement is used to eliminate rows from the results?

WHERE

227

Which clause from a SELECT statement is used to eliminate groups from the results?

HAVING

228

what is returned from the following statement? SELECT 2*5 + 7 - 6/2 FROM DUAL

142*5 = 106/2 = 310+7-3 = 14

229

What is DUAL?

a table in ORACLE that has one column named DUMMY with a value of X

230

Are the following statement equal? SELECT * FROM EMPLOYEES; Select * FROM EMPLOYEES; select * FROM EMPLOYEES;

Yes. Keyword capitalization of keywords has no affect.

231

What is the syntax of the SELECT clause with column1 having an alias of First Name?

"SELECT column1 AS ""First Name""The AS is optionalif there is a space in the Alias, quotes are needed"

232

What is the syntax of a SELECT statement if you want to return all rows which have nulls in column1 of table1?

SELECT *FROM Table1WHERE column1 IS NULL

233

What is the syntax of a SELECT statement if you wanted to all records without null from column1 in table1?

SELECT *FROM table1WHERE column1 IS NOT NULL

234

What is the syntax of a SELECT statement wanted to return column2 from Table1 for all values where column2 is not equal zero?

SELECT column2FROM table1WHERE column2 <> 0orSELECT column2FROM table1WHERE NOT column2 = 0

235

"What is the syntax of a SELECT statement if you wanted to select columns1 and column2 from table1 where column4 is greater than 0 and column7 begins with an ""A"" or ""B"" or ""C""?"

SELECT column1, column2FROM table1Where column4 > 0and column7 BETWEEN 'A' AND 'C'OR SELECT column1, column2FROM table1Where column4 > 0and column7 BETWEEN 'A' AND 'C'

236

Does this select statement have an expression clause? SELECT column1 CASE owner WHEN 'SYS' THEN 'The owner is SYS' WHEN 'SYSTEM' THEN 'The owner is 'SYSTEM' ELSE 'unkown Owner' END FROM table

"YesThe column name ""owner"" after the CASE keyword is the expression clause"

237

Does this select statement have an expression clause? SELECT column1 CASE WHEN owner = 'SYS' THEN 'The owner is SYS' WHEN owner = 'SYSTEM' THEN 'The owner is 'SYSTEM' ELSE 'unkown Owner' END FROM table

"NoThere is no value between CASE and WHENNotice the column name ""owner ="" is in each WHEN clause"

238

What will the results be if the value in the column named owner column is 'USER'? SELECT column1 CASE WHEN owner = 'SYS' THEN 'The owner is SYS' WHEN owner = 'SYSTEM' THEN 'The owner is SYSTEM' END FROM table

It would return a null because there is not match any value for owner and there is no ELSE clause

239

Is this a valid CASE statement? SELECT CASE WHEN a < b then 'A' WHEN d < e THEN 'B' END FROM table

Yes it is valid but the first WHEN clause is evaluated first and if true will return a result and then not evaluate the second WHEN.

240

If the computer is in Chicago and the user is in California, which date/time is returned by this statement? SELECT SYSDATE FROM dual;

The date and time in Chicago

241

If the computer is in Chicago and the user is in California, what is the result of this statement? SELECT CURRENT_DATE FROM dual;

The date and time in California

242

Column aliases can only be defined in which clause of a SQL Statement?

The SELECT Clause

243

Table aliases can only be defined in which clause of a SQL Statement?

The FROM Clause

244

What is the scope of an alias?

only within the SQL Statement

245

What is needed if the alias has a space in the name?

Quote around the entire Alias name

246

"Using the LIKE operator, create a WHERE clause that would find all records from the column last_name that begin with the letters ""Sm""."

WHERE last_name LIKE 'Sm%'

247

"Using the LIKE operator, create a WHERE clause that would find all records from the column last_name that begin with ""S"" and end with ""h""."

WHERE last_name LIKE 'S%h'

248

In a LIKE comparison, what is the wildcard symbol for matching a string of any length including zero length?

% (percentage sign)

249

In a LIKE comparison. what is the wildcard symbol for matching a single character?

_ (underscore)

250

"Using the LIKE operator, create a WHERE clause that would find all records from the column last_name beginning with ""Sm"" and ending with ""th"" but can have only 1 character in between."

LIKE last_name LIKE 'Sm_th'

251

Using the LIKE operator, create a WHERE clause that would find all records from the column Parts which begin with 'H%'. Remember % is also a wildcard.

WHERE Parts LIKE 'H%!%' ESCAPE '!'The keyword ESCAPE designates the escape character

252

The HAVING clause is used in combination of with what other clause?

GROUP BY

253

Besides the SELECT clause, what other clauses can have the SUM, COUNT, MIN, MAX or AVG functions.

only the HAVING clauseThose functions are not allowed in the WHERE clause or GROUP BY clause.

254

What is the syntax of a WHERE clause that find records that match state = 'Texas' and name = 'IBM' as well as any record where the cost is greater than 1000.

WHERE (state = 'Texas and name = 'IBM')OR cost > 1000User parenthesis when combining OR and AND keywords

255

Which will perform faster? SELECT cust_name, cust_city FROM customers WHERE cust_credit_limit IN (1000,2000,3000) SELECT cust_name, cust_city FROM customers WHERE cust_credit_limit = 1000 OR cust_credit_limit = 2000 OR cust_credit_limit = 3000

The performance will be the same

256

If hire_date is a date data type, what data type will be returned by this SQL statement? SELECT SYSDATE - hire_date FROM emp

a numeric value

257

If the column transdate is a numeric data type, will this statement cause an error? SELECT transdate = '10' FROM transactions

NoThe '10' will be implicitly converted from a string to a numeric

258

If the column transdate is a date data type, will this statement cause an error? SELECT * FROM transactions WHERE transdate = '01-JANUARY-07'

NoThe '10-JANUARY-07' will be implicitly converted to '10-JAN-07'

259

If transamount and custno are numeric data types, will this statement cause an error? SELECT transamount FROM transactions WHERE custno > '11'

NoThe '11' will implicitly be converted to a numeric

260

"Will this SQL statement create an error? SELECT promo_name, promo_date ""START DATE"" FROM promotions WHERE promo_date > '01-JAN-01' ORDER BY ""START DATE"" DESC"

The statement will execute without error.Aliases can be used in the ORDER BY clause, but aliases cannot be used in the GROUP BY clause

261

What is returned from this statement? SELECT 'Ted' || q'{'s Car}' || ' is red.' FROM dual

Ted's Car is redThe q keyword act as an escape clauseq'{........}'

262

What is returned from this statement? SELECT 'Ted' || q'('s Car)' || ' is red.' FROM dual

Ted's Car is redThe q key word can have any of 4 different pairs of brackets such as: {} [] () <>But no mix match between the pairs such as: { ... )

263

What is returned from this statement? SELECT 'Ted' || q'['s Car]' || ' is red.' FROM dual

Ted's Car is red

264

What is returned from this statement? SELECT 'Ted' || q'' || ' is red.' FROM dual

Ted's (Car) is red

265

What will be returned with the following statement? SELECT 'Ted' || q'

an error because the bracket types to not match< does not match with ]

266

Can a column of data type LONG be used in a GROUP BY or an ORDER BY clause

No.A column defined a data type LONG cannot be used in either a GROUP BY or an ORDER BY clause

267

Can a constraint be defined for a column whose data type is LONG?

No

268

What data type should a LONG be converted in order to be more modern?

CLOB

269

Can a CLOB be used in a GROUP BY and an ORDER BY clause?

Yes

270

What is the results of this statement? SELECT part_name FROM employees WHERE part_name LIKE '%SA\_' ESCAPE '\'

Any part name has SA_ as the last 3 characters.The _ (underscore) is a symbol.ESCAPE is a keyword

271

What is returned from this statement? SELECT SYSDATE - '01-JAN-2007' FROM dual

error because a string is being subtracted from a date.Dates in strings are NOT implicitly converted by Oracle.

272

What is the syntax for sorting a column of numbers named cost from highest to lowest?

ORDER BY cost DESC

273

What is returned from this statement? SELECT * FROM employees WHERE initials LIKE '_A_'

returns all rows which the initial are 3 characters with the middle character is capitalized A

274

Define numeric literal?

A numeric value stored as a string

275

"Correct this SQL statement. SELECT prod_name, prod_list, prod_list - (prod_list * .25) ""Discounted_Price"" FROM products WHERE Discounted_Price < 10"

"replace the alias in the WHERE clauseSELECT prod_name, prod_list, prod_list - (prod_list * .25) ""Discounted_Price""FROM productsWHERE prod_list - (prod_list * .25) < 10"

276

Is a character sort case-sensitive by default?

Yes.

277

Can only columns specified in the Select be used in the ORDER BY clause?

Yes, but...No column can be in the ORDER BY clause unless it is also in the SELECT clause, but expressions can be in the ORDER BY clause that are not in the SELECT clause.

278

Are dates formats case sensitive when sorted?

Yes.January sorts before JANUARY

279

What clause can exclude rows before dividing them into groups?

The WHERE clause

280

What clause can exclude groups?

The HAVING clause

281

The simplest form of a Select Statement must include which 2 clauses?

1. SELECT clause2. FROM clause

282

What answer will you get if you divide a number by zero?

error

283

What answer will you get if you divide a number by Null?

NULL

284

What is needed if you want the Alias name to be case sensitive?

"The Alias name needs to be surrounded by quotes.""ColumnA"" otherwise it will be COLUMNA"

285

What is need if you have special characters such as $ or # in an Alias?

The Alias name needs to be surrounded by quotes.

286

What is need if the Alias has a space?

The Alias name needs to be surrounded by quotes.

287

Dates and literal characters must be enclosed by what?

single quotes.

288

What operator is used if you want to have a single quote in a literal?

qExample: q'['s Manager id: ]'Usually pair: {} [] () <>Also q'x's Manager id: x'

289

What keyword in a SELECT clause eliminates duplicate records?

DISTINCT

290

You can use arithmetic operators in any clause of a SQL statement except?

FROM clause

291

What are the 2 main categories of conditions used in a WHERE clause?

1. logical2. comparison

292

What category of conditions are the following: =, <=, BETWEEN, IN, LIKE, NULL

comparison conditions

293

What category of conditions are the following: AND, OR, NOT

logical conditions

294

The WHERE clause restricts what?

Rows

295

What can be used in a SELECT clause that cannot be used in a WHERE clause?

a column alias

296

What is the default date display format of Oracle date type?

DD-MON-RR

297

What 2 Symbols can represent NOT?

1. ! (exclamation point)2. ^ (carat)!= is not equal

298

Using the BETWEEN operator, which limit is specified first?

lower limit then upper limitExample: BETWEEN 1000 AND 2000Example: BETWEEN 'King' AND 'Smith'

299

The IN operator is equivalent to what?

The OR conditionThere is no performance benefits between the IN or the OR

300

Are lower case values return with this condition: LIKE 'S%'

NoLetter between quote are case sensitive

301

What 2 wild card symbols can be used to construct a search string?

1. % (percentage)2. _ (underscore)

302

What words would be return when using LIKE '_o%'

The second letter of the word has an lower case o.

303

What is the escape identifier in this: LIKE '%SA\_%' ESCAPE '\'

ESCAPE

304

What is the ESCAPE identify as the escape character in this: LIKE '%SA\_%' ESCAPE '\'

\ (backslash)

305

What are the two conditions test for null in the where clause?

1. IS NULL2. IS NOT NULL

306

What are the 3 logical operators available in SQL?

1. AND2. OR3. NOT

307

If there are no parenthesis, which is evaluated first: addition or multiplication

Multiplication then addition

308

If there are no parenthesis, which is evaluated first: AND logical or OR logical

AND logical then OR logical

309

If there are no parenthesis, which is evaluated first: NOT logical or AND logical

NOT logical then AND logical

310

Are column aliases defined in the SELECT clause allowed in the ORDER BY clause?

Yes

311

Are column aliases defined in the SELECT clause allowed in the WHERE clause?

No

312

How are nulls sorted by default?

Null values are last in a sort

313

What are 2 other keywords allowed in the ORDER BY clause?

1. ASC2. DESC

314

How are nulls sorted when using an ORDER BY?

nulls are sorted last

315

What key words can be used in the ORDER BY clause to sort NULLs?

1. NULLS FIRST2. NULLS LAST

316

What symbol is used to indicate the variable will be a user input at runtime?

& (ampersand)

317

What symbol is used to indicate the variable will be a user input at runtime and the same value during the same session?

&& (double-ampersand)

318

What is the only column in the table named DUAL?

DUMMY

319

What is the only value in the DUMMY column in the table named DUAL?

X

320

What year is interpreted by RR if the date is 10-Oct-1949?

1949

321

What year is interpreted by RR if the date is 10-Oct-1950?

2050

322

What are the 2 main types of Data type conversions?

1. Implicit data type conversion2. Explicit data type conversion

323

What type of conversion is done by the Oracle server?

implicit

324

The CASE is classified as what type of function?

a general single-row function

325

What function is the CASE expression equivalent to?

NULLIFCASE WHEN exp1 = exp2THEN NULLELSE exp1 END

326

What is return if expr1 is 12? CASE expr1 WHEN 10 THEN 'A' WHEN 20 THEN 'B' ELSE 'C' END

C

327

Does each column name in the GROUP BY clause need a corresponding column name in the SELECT clause?

NoYou can group by something that is not in the select

328

Does each column name and expression (non-group function) in the SELECT clause need a corresponding column name and expression in the GROUP BY clause?

Yes.Everything in the SELECT clause, except group functions must be in the GROUP BY clause.

329

What 2 clauses can Group functions can be used?

1. SELECT2. HAVING3. ORDER BY

330

What is the correct order of the following clauses in a select Statement? FROM WHERE SELECT ORDER BY GROUP BY HAVING

1. SELECT2. FROM3. WHERE4. GROUP BY5. HAVING6. ORDER BY

331

Can aliases save on memory?

Yes

332

Can aliases speed up database access?

Yes

333

What is the syntax of an ORDER BY clause when sorting column Cust_limt where null values are last?

ORDER BY cust_limitORDER by cust_limit NULLS LASTnulls are automatically sorted last

334

What is the syntax of an ORDER BY clause when sorting column Cust_limt where null values are sorted first?

ORDER BY cust_limit NULLS FIRST

335

What is returned from this statement? SELECT 12 + Null + 19 FROM dual

Null

336

What is the default date format for Oracle SQL Developer?

DD/MON/RRExample: 10/AUG/03

337

What is the default date format for Oracle SQL *PLUS?

DD-MON-RRExample: 10-AUG-03

338

What century is returned from the date 03-AUG-07 in the DD-MON-RR format?

20

339

What century is returned from the date 03-AUG-94 in the DD-MON-RR format?

19

340

Which of these are implicit data conversions in Oracle? Number to VARCAR2 date to VARCHAR2

Both.Oracle will implicitly convert date type to VARCHAR2 in expressions or functions

341

What is the last keyword of a CASE statement?

ENDCASE....WHEN...THEN...END

342

Which of the following clauses can the CASE statement be used? SELECT FROM WHERE ORDER BY

SELECTWHEREORDER BYThe case statement cannot be used in the FROM clause

343

What is the maximum number of WHEN..THEN levels in a CASE statement?

255 levels of WHEN...THEN levels

344

What is the difference in return of the 2 statements? SELECT id, name FROM table1 GROUP BY id, name; SELECT id, name FROM table1 GROUP BY id, name ORDER BY id, name;

They should return the exact same results because the GROUP BY implicitly sorts by first column then 2nd column.But it is recommended by Oracles to use the ORDER BY to ensure wanted results.

345

What is the limit of groups that can be in the GROUP BY clause?

There is not limit of the number of groups which can be in the GROUP BY clause.

346

What is the maximum number of characters allowed in a table alias?

30

347

What is returned from this statement? SELECT 'Tutorial''s Point compiles technical tutorials' FROM DUAL;

Tutorial's Point compiles technical tutorials

348

What happens when you concatenate when one value is a null?

"Result is a string but also has ""NULL"" in string.'A' || null || 'B' returns ANULLB"

349

What is returned by this statement? SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a, (SELECT dept_id, MAX(sal) maxsal) FROM employees GROUP BY dept_id) b WHERE a.dept_id = b.dept_id AND a.sal < b.maxsal

All employees who earn less than the maximum salary in their department

350

What is the syntax for creating 2 BETWEENs in a WHERE clause? field X is between 'c' and 'a' field Y is between 200 and 100

WHERE x BETWEEN 'a' AND 'c' AND y BETWEEN 100 AND 200Lower value must be first. No commas are needed

351

What is the result of this statement? WHERE A = ''

This will always return a falseThis is similar to A = null

352

If the DISTINCT keyword is used in a SELECT clause where must it be placed?

immediately after SELECT and only used once since it affect all columns and expressions in the SELECT clause.

353

TOP N analysis requires what?

An ORDER BY clausean inline view and an outer query

354

What 2 keywords can be used to suppress duplicates in the SELECT clause

DISTINCTUNIQUEBoth are synonymous

355

What is the difference between these 2 statements? SELECT empno, ename, sal, comm FROM emp WHERE comm IN (0, NULL); SELECT empno, ename, sal, comm FROM emp WHERE comm = 0 OR comm IS NULL;

n the first SQL, the comm IN (0, NULL) will be treated as comm = 0 OR comm = NULL. For all NULL comparisons, you should use IS NULL instead of = NULL. The first SQL will return only one row where comm = 0, whereas the second SQL will return all the rows that have comm = NULL as well as comm = 0.

356

What should be changed to make this statement work? DEFINE V_DEPTNO = 20 SELECT LAST_NAME, SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID = V_DeptNo;

Add a % before the variable nameDEFINE V_DEPTNO = 20 SELECT LAST_NAME, SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID = &V_DeptNo;

357

A column alias names cannot be used in which clause? SELECT clause WHERE clause ORDER BY clause FROM clause

WHERE clause or FROM clause

358

What is the default escape character in Oracle?

There is no default escape character in Oracle for pattern matching. If your search includes pattern-matching characters such as _ or %, define an escape character using the ESCAPE keyword in the LIKE operator.

359

Will this statement cause an error? SELECT hire_date FROM employee ORDER BY salary, emp_name;

It is perfectly valid to use a column in the ORDER BY clause that is not part of the SELECT clause.

360

Will this statement cause and error? SELECT empno, DISTINCT ename, salary FROM emp;

Yes.DISTINCT must directly follow SELECT in this statement.

361

What are the 2 literals in this SELECT statement? SELECT 'Employee Name: ' || ename FROM emp where deptno = 10;

1. Employee Name:2. 10

362

Are all these valid TIMESTAMP values? TIMESTAMP = ′2008-03-24 03:25:34.123′ TIMESTAMP = ′2008-03-24 03:25:34.123 -7:00′ TIMESTAMP = ′2008-03-24 03:25:34.123 US/Central′ TIMESTAMP = ′2008-03-24 03:25:34.123 US/Central CDT′

Yes.All are valid.

363

What will be returned from the following statement? A%_WQ123 A%BWQ123 AB_WQ123 SELECT part_code FROM spares WHERE part_code LIKE '%\%_WQ12%' ESCAPE '\';

A%_WQ123 A%BWQ123 The _ without an escape means 1 character.

364

What is the correct output of the above query? SELECT INTERVAL '300' MONTH, INTERVAL '54-2' YEAR TO MONTH, INTERVAL '11:12:10.1234567' HOUR TO SECOND FROM dual;

+25-00 , +54-02, +00 11:12:10.123457 Datetime Data Types You can use several datetime data types: INTERVAL YEAR TO MONTH Stored as an interval of years and months INTERVAL DAY TO SECOND Stored as an interval of days, hours, minutes,and seconds +25-00 , +54-02, +00 11:12:10.123457

365

Will this statement produce and error? SELECT MAX( AVG ( SYSDATE - inv_date)) FROM invoices;

Yes.Because the MAX and AVG function are nested and so there should be a GROUP BY clause.

366

A TOP N analysis requires what?

1. An ORDER BY clause2. an inline view3. an outer query

367

Do both SQL and SQL *PLUS allow manipulation of values in the database?

Yes

368

Which of the following is a SQL *PLUS command? SELECT ALTER DESCRIBE DROP

DESCRIBE

369

What is needed in all SQL *Plus statements but not in SQL Statements?

; (semicolon)

370

What commands can be used to create substitution variables?

1. DEFINE | UNDEFINE

371

What PL *SQL command will list the structure of a table?

DESCRIBE table1

372

List some SQL *Plus command that are not supported by SQL Worksheet?

1. DESCRIBE2. APPEND3. ARCHIVE4. ATTRIBUTE5. BREAK

373

List the 3 main command types.

1. DDL - Data Definition Language2. DML - Data Manipulation Language3. Transaction Control Commands

374

List 3 main DDL commands.

1. CREATE2. DROP3. ALTER

375

List 4 main DML commands.

1. SELECT2. INSERT3. UPDATE4. DELETE

376

What object can be created but not dropped?

Database

377

When is a session created?

When a user logs into an oracle database.

378

The RENAME command can be performed on what 4 database objects?

1. Table2. View3. Sequence4. Private Synonym

379

What is the syntax of the Rename statement?

RENAME table1 TO table2

380

Name 2 main Oracle Tools for using SQL code?

1. Oracle SQL Developer2. SQL *Plus Environment

381

What are the 4 main data types?

1. NUMBER (p, s)2. VARCHAR2 (s)3. DATE4. CHAR(2)

382

What command can you use to validate that a set command worked?

VERIFY ON

383

List 5 common objects in an Oracle Database.

1. TABLE2. VIEW3. SEQUENCE4. INDEX5. SYNONYM

384

What command can be used to restore a dropped table from the recycle bin?

FLASHBACK TABLECommand first started with version 11g

385

The RENAME statement can be used on what 4 database objects?

1. TABLE2. VIEW3. SEQUENCE4. PRIVATE SYNONYM

386

The RENAME statement cannot be used on what database object?

PUBLIC SYNONYM

387

What do the letters AWR mean?

Automatic Workload Repository

388

What is an AWR Report?

A report to collect performance statistics of a database.

389

What does statistics do?

It gathers information on a table to be used by the SQL analyzer

390

What is CBO?

Cost based Optimizer

391

What is a data dictionary?

a read-only set of tables that provides information about the database.SYS is the owner of the data dictionary

392

What is Metadata?

DBMS_METADATA package provides interfaces for extracting complete definition of database objects.

393

What is the grid control.

Monitor active Oracle processes.

394

What is Oracle Partitioning?

A process for separating large tables logically so its data can be managed based on different set of keys.

395

What is Oracle Reports?

a tool for developing reports against data stored in an Oracle database.

396

What are the 2 parts of Oracle Reports?

1. Oracle Developer2. Oracle Application Server Reports Server

397

What format will Oracle Report deliver?

1. HTML2. RTF3. PDF4. XML5. MS EXCEL

398

What is Database Replay in Oracle 11g?

a new tool that captures SQL statements and let you replay them at will

399

What is Partitioning in Oracle 11g?

Referential, Internal and Virtual Column partitioning; new sub-partitioning options

400

What is Edition-based Redefinition in Oracle 11g?

Patch or update application data without interruptions

401

What is Schema Management in Oracle 11g?

Add columns and default values easily. Invisible tables, virtual columns and read only tables.

402

What is Data Warehousing and OLAP in Oracle 11g?

Cube Organized MVs, new Analytic Workspace Manager

403

What is Transaction Management in Oracle 11g?

introduce to Flashback data archive

404

What is SQL Performance Analyzer and Real-Time SQL Monitoring?

monitor SQL Performance in real time

405

What is SQL Access Advisor?

gives advice about optimal table design based on actual use of table and not just data.

406

What is a data pump.

Data Pump is a new feature introduced in Oracle 10g to move data and meta data between databases and to or from operating system files very efficiently. It provides parallel import and export utilities (impdp, expdp) on the command-line as well as the Web-based Oracle Enterprise Manager export/import interface. It is ideally beneficial for large databases and data warehousing environments. Oracle Data Pump facility runs on the server.The following are some functions performed by Oracle Data Pump: It is used to copy data from one schema to another between two databases or within a single database. It can be used to extract a logical copy of the entire database, a list of tablespaces, a list of schemas or a list of tables.

407

What is RDBMS?

relational database management system

408

What is the Oracle account that is the dictionary owner?

SYS

409

What is the Oracle account that is for the DBA?

SYSTEM

410

Are both these statements true? A table and a synonym can have the same name in the same schema. A table and a view can have the same name in the same schema.

Only a table and a synonym can have the same name in the same schema.Tables and View must have different names in the same schema.

411

What happens to transactions if a GRANT command is executed?

An implicit commit will be execute before the GRANT command.

412

What happens to transactions if an ALTER VIEW is executed?

An implicit commit will be execute before the ALTER VIEW command.

413

What privileges does the user Scott have after this statement is executed? CREATE USER scott IDENTIFIED BY tiger

no privileges

414

What is the password for scott after this statement is executed? CREATE USER scott IDENTIFIED BY tiger

tiger

415

What is allowed after this statement is executed? GRANT SELECT, INSERT, UPDATE ON student_grade TO manager WITH GRANT OPTIONS

The role/user manager can give select or insert or update privileges to the table student_grade to others

416

Can only an owner change an object?

No. Permissions can be granted to others

417

What is the syntax for creating a Role?

CREATE ROLE rolename

418

What is the syntax for granting select and update on TABLEA for user USER1?

GRANT SELECT, UPDATE ON tablea TO user1

419

What is the syntax for creating user USER1 with password1?

CREATE USER User1 IDENTIFIED BY password1.

420

What Command is the opposite of GRANT?

REVOKE

421

What is the syntax to grant SELECT access to all users for Table1?

GRANT SELECT ON table1 TO PUBLIC

422

What privilege is needed for a user to delete records or truncate a table?

DELETE ANY TABLE

423

What is the syntax to give the create table and create view to the manager role?

GRANT CREATE TABLE, CREATE VIEW TO manager

424

What does the WITH GRANT OPTION clause allow?

The grantee (user or role) can grant privileges to other users or role

425

What privileges is granted to a user/role to allow deletes and truncates on a table?

DELETE ANY TABLE

426

What 4 privileges can be granted on a view?

DELETEINSERTSELECTUPDATE

427

What is a system privilege?

permission to create, delete or alter a database object

428

What is the syntax for unlocking a user account name hr?

ALTER USER hr IDENTIFIED BY hrpassword ACCOUNT UNLOCK

429

Can a column with a UNIQUE constraint store nulls.

Yes

430

Can a foreign key contain a null value?

Yes

431

Is a constraint only enforced by an INSERT operation on the table?

NoA constraint is also enforced by an UPDATE operation on a table

432

Can a constraint be disabled even if the column contains data?

Yes

433

Can all constraints be defined at the column level as well as the table level?

NoNULL and NOT NULL are only defined at the column level

434

Can a constraint prevent deletion of a table?

YesIf there are dependencies. A parent cannot be deleted if a child exists.

435

Can a table from another schema in the same database be viewed be used in a different schema?

Yesadd prefix to the tablename to access that table from the other schema

436

Can the ALTER TABLE statement be used to modify a constraint after a table is created?

Yes

437

What is returned from this statement? CREATE TABLE order (trans_id Number(6) NOT NULL, trans_date DATE NOT NULL, cust_name CHAR, trans_Valid VARCHAR2)

an errorThe varchar2 needs a size value

438

How many characters can be store in the cust_name field? CREATE TABLE order (trans_id Number(6) NOT NULL, trans_date DATE NOT NULL, cust_name CHAR, trans_Valid VARCHAR2(5))

only 1 character because the size was not identified to the default of one is used.

439

What is the length of the cust_name field if the value is 'Ac'? CREATE TABLE order (trans_id Number(6) NOT NULL, trans_date DATE NOT NULL, cust_name CHAR(5), trans_Valid VARCHAR2(5))

5the CHAR field is right-padded with spaces

440

What is the length of the trans_valid field if the value is 'Ac'? CREATE TABLE order (trans_id Number(6) NOT NULL, trans_date DATE NOT NULL, cust_name CHAR(5), trans_valid VARCHAR2(5))

2The VARCHAR2 only uses the spaces needed

441

What is the largest value that can be stored in trans_id? CREATE TABLE order (trans_id Number(2) NOT NULL, trans_date DATE NOT NULL, cust_name CHAR(5), trans_Valid VARCHAR2(5))

99

442

What is the largest value that can be stored in trans_id? CREATE TABLE order (trans_id Number(2,1) NOT NULL, trans_date DATE NOT NULL, cust_name CHAR(5), trans_valid VARCHAR2(5))

9.9The scale of 1 takes one value from the precision

443

In defining a numeric field, precision is defined as what?

The total number of decimal digits allowed in the field, either left or right of the decimal.

444

If defining a numeric field, scale is defined as what?

The total number of digit to the right of the decimal point.

445

What is the precision of trans_id? CREATE TABLE order (trans_id Number NOT NULL, trans_date DATE NOT NULL, cust_name CHAR(5), trans_valid VARCHAR2(5))

38 digits

446

What is the maximum size number allowed for a CHAR?

2000

447

What is the maximum value for a date data type?

12-DEC-9999

448

What will result after the execution of this statement? CREATE TABLE EMP9$#_A as (empid number(2))

It will create a table name EMP9$#_A with one numeric field named empid.

449

What will result after the execution of this statement? CREATE TABLE EMP*123 as (empid number(2))

It will fail because there is an arithmetic expression in the table name

450

What will result after the execution of this statement? CREATE TABLE package as (empid number(2))

It will fail because PACKAGE is a keyword for Oracle

451

What will result after the execution of this statement? CREATE TABLE ord_details (ord_id NUMBER(2) CONSTRAINT ord_id_pd PRIMARY KEY, ord_date DATE DEFAULT SYSDATE NOT NULL, ord_amount NUMBER(5,2) CONSTRAINT ord_amount_min CHECK (ord_amount >=50), ord_status VARCHAR2(15) CONSTRAINT order_status_chk CHECK(ord_status IN ('Shipped','Not Shipped')) ord_pay_ode VARCHAR2(15) CONSTRAINT ord_pay_chk CHECK (ord_pay_mode in ('Check','Credit Card','Cash')));

It will execute successfully

452

What term is used to choose rows from a table?

Selection

453

What term is used to choose columns from a table?

Projection

454

Can a column has more than on CHECK constraint?

Yes. A single column can have multiple check constraints and there is no limit.

455

Can a CHECK constraint reference SYSDATE?

No

456

Can a DEFAULT column value be SYSDATE?

Yes

457

Can a column in a table be part of the Primary key and part of a Foreign key?

Yes

458

What constraint only be defined at the column level?

NOT NULL

459

What happens to constraints if a table is renamed?

The constraints transfer

460

What is the syntax to add a default value of 50 for a column named salary in table employees?

ALTER TABLE employeesMODIFY (salary DEFAULT 50)

461

What happens to when a column constraint NOT NULL is added to a column and there are null values already in the column?

past data is not affected but new nulls are added.

462

What is the syntax for renaming a column in a table

ALTER TABLE table1 RENAME columnold TO columnnew

463

What is the syntax of an insert statement with the values clause?

INSERT INTO table1 (field1, field3, field4)VALUES (expression1,expression2,expression3)The number of field names must match the number of values. The data types must also match.

464

What happens to an index if a DML operation is performed?

The index is updated

465

What is also created when a primary key is created?

A unique index.Primary keys cannot be duplicated

466

What is a composite index?

An index with 2 or more columns

467

What is returned with this statement? ALTER TABLE customers MODIFY cust_name CONSTRAINT cust_name NOT NULL

A constraint is added to the cust_name column that does not allow nulls

468

What is returned with this statement if the table is populated with records and the cust_name column already has null values? ALTER TABLE customers MODIFY cust_name CONSTRAINT cust_name NOT NULL

Nothing.Past values are not changed, but no new null values can be added to the cust_name column.

469

What clause can be added to a select statement which will temporary lock records from other users for updating or deleting?

FOR UPDATE

470

What command can you use to display the structure of a table?

DESCRIBE tablename

471

What is left after a TRUNCATE table statement?

The table structure is left intact, but all data has been removed

472

What is a Pseudocolumn?

values generated from commands that behave like a column of a table, but are not actually store in the table.

473

What DDL is an efficient method of emptying a table?

TRUNCATE

474

Are delete triggers fired during a TRUNCATE?

No

475

What could block a TRUNCATE?

if the table is a parent in a referential integrity constraint.

476

What is the maximum number of characters in a tablename?

30

477

Besides letters and number what 3 other symbols can be used in a tablename?

1. _ (underscore)2. $3. #

478

By default are tablenames case sensitive?

No

479

What can be used to make tablesname case sensitive?

"Add double quotes before and after the tablename.""tablename"""

480

When an AS subquery is part of a CREATE table besides data types what else is passed to the new table?

NOT NULL contraints

481

When an AS subquery is part of a CREATE table will the primary key be passed to the new table?

No

482

What is needed in a create table if one of the columns is an expression?

an Alias to name the column

483

If a table has 2 columns named ID and NAME what will be inserted into the name column with the following statement? INSERT INTO tablename (ID) VALUES (34);

null

484

When creating a table with the VARCHAR2 datatype is the size parameter needed?

Yes

485

When creating a table with the CHAR datatype is the size parameter needed?

No. The default will be 1 character

486

What data type is not copied when using the subquery in a create table?

LONG

487

What datatype cannot be used in a GROUP BY or an ORDER BY clause?

LONG

488

How many column can have the data type of LONG in a single table?

Only one column of data type LONG can be in any table.

489

Can any constraints be defined in a LONG column?

No

490

List 5 common data integrity constraints?

1. NOT NULL2. UNIQUE3. PRIMARY KEY4. FOREIGN KEY5. CHECK

491

If you do not name your constraint Oracle will automatically create a number with a prefix, what is the prefix?

SYS_CExample: SYS_C1234556

492

Functionally are table-level constraints and column-level constraints the same?

yes

493

What is the syntax of a primary key defined at the column level?

columnname datatype CONTRAINT constraintname PRIMARY KEY

494

What is the syntax of a primary key defined at the table level?

After the last column name is definedCONSTRAINT constraintname PRIMARY KEY (columnname)

495

What constraint type is used to establish and enforce referential integrity?

FOREIGN KEY

496

Can a primary key include more that one column?

Yes

497

At what level must the primary key be defined if it includes more than one column?

table level

498

What happens if you forget to name a CONSTRAINT?

Oracle will automatically assign it a Number with the prefix SYS_C

499

What other constraint is automatically created on the columns in a primary key?

UNIQUE Constraint

500

Can any of the field in a primary key contain a null?

No