Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

DSO SQL > Oracle__4. Oracle 1Z0-051 Exam - Select Statement > Flashcards

Oracle__4. Oracle 1Z0-051 Exam - Select Statement Flashcards

1
Q

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

A

SELECT *

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

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

A

SELECT DISTINCT *

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

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

A

ALLSyntax: SELECT ALL *The keyword ALL is default

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What is a clause?

A

a subset of a command that modifies the command.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

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

A

SELECTFROMWHEREGROUP BYHAVINGORDER BY

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

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

A

SELECTWHEREGROUP BYORDER BY

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

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

A

ORDER BY column2 DESC, column1alsoORDER BY 2 DESC, 1

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What does ORDER BY 1, 2 mean?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

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

A

ASCmeaning AscendingASC is the default

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

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

A

WHERE ROWNUM <= 10

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

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

A

SELECT column1 + column2 AS SUMthe as is optional but recommended

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

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

A

FROM table1 AS XThe keyword AS is optional

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

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

A

GROUP BY

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

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

A

FROM

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

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

A

SELECT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

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

A

Identical results

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

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

A

ORDER BY

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

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

A

WHERE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

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

A

HAVING

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What is DUAL?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

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

A

Yes. Keyword capitalization of keywords has no affect.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

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

A

SELECT *FROM Table1WHERE column1 IS NULL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

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

A

SELECT *FROM table1WHERE column1 IS NOT NULL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

“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””?”

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

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

A

“YesThe column name ““owner”” after the CASE keyword is the expression clause”

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

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

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

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

A

The date and time in Chicago

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

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

A

The date and time in California

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

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

A

The SELECT Clause

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

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

A

The FROM Clause

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

What is the scope of an alias?

A

only within the SQL Statement

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

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

A

Quote around the entire Alias name

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

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

A

WHERE last_name LIKE ‘Sm%’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q

“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””.”

A

WHERE last_name LIKE ‘S%h’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
42
Q

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

A

% (percentage sign)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
43
Q

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

A

_ (underscore)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
44
Q

“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.”

A

LIKE last_name LIKE ‘Sm_th’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
45
Q

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.

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
46
Q

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

A

GROUP BY

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
47
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
48
Q

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.

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
49
Q

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

A

The performance will be the same

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
50
Q

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

a numeric value

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
51
Q

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

A

NoThe ‘10’ will be implicitly converted from a string to a numeric

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
52
Q

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

A

NoThe ‘10-JANUARY-07’ will be implicitly converted to ‘10-JAN-07’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
53
Q

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

A

NoThe ‘11’ will implicitly be converted to a numeric

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
54
Q

“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”

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
55
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
56
Q

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

A

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: { … )

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
57
Q

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

A

Ted’s Car is red

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
58
Q

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

A

Ted’s (Car) is red

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
59
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
60
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
61
Q

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

A

No

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
62
Q

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

A

CLOB

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
63
Q

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

A

Yes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
64
Q

What is the results of this statement? SELECT part_name FROM employees WHERE part_name LIKE ‘%SA_’ ESCAPE ‘'

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
65
Q

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

A

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

66
Q

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

A

ORDER BY cost DESC

67
Q

What is returned from this statement? SELECT * FROM employees WHERE initials LIKE ‘A’

A

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

68
Q

Define numeric literal?

A

A numeric value stored as a string

69
Q

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

A

“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”

70
Q

Is a character sort case-sensitive by default?

A

Yes.

71
Q

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

A

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.

72
Q

Are dates formats case sensitive when sorted?

A

Yes.January sorts before JANUARY

73
Q

What clause can exclude rows before dividing them into groups?

A

The WHERE clause

74
Q

What clause can exclude groups?

A

The HAVING clause

75
Q

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

A
  1. SELECT clause2. FROM clause
76
Q

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

A

error

77
Q

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

A

NULL

78
Q

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

A

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

79
Q

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

A

The Alias name needs to be surrounded by quotes.

80
Q

What is need if the Alias has a space?

A

The Alias name needs to be surrounded by quotes.

81
Q

Dates and literal characters must be enclosed by what?

A

single quotes.

82
Q

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

A

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

83
Q

What keyword in a SELECT clause eliminates duplicate records?

A

DISTINCT

84
Q

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

A

FROM clause

85
Q

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

A
  1. logical2. comparison
86
Q

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

A

comparison conditions

87
Q

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

A

logical conditions

88
Q

The WHERE clause restricts what?

A

Rows

89
Q

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

A

a column alias

90
Q

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

A

DD-MON-RR

91
Q

What 2 Symbols can represent NOT?

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

Using the BETWEEN operator, which limit is specified first?

A

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

93
Q

The IN operator is equivalent to what?

A

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

94
Q

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

A

NoLetter between quote are case sensitive

95
Q

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

A
  1. % (percentage)2. _ (underscore)
96
Q

What words would be return when using LIKE ‘_o%’

A

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

97
Q

What is the escape identifier in this: LIKE ‘%SA_%’ ESCAPE ‘'

A

ESCAPE

98
Q

What is the ESCAPE identify as the escape character in this: LIKE ‘%SA_%’ ESCAPE ‘'

A

\ (backslash)

99
Q

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

A
  1. IS NULL2. IS NOT NULL
100
Q

What are the 3 logical operators available in SQL?

A
  1. AND2. OR3. NOT
101
Q

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

A

Multiplication then addition

102
Q

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

A

AND logical then OR logical

103
Q

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

A

NOT logical then AND logical

104
Q

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

A

Yes

105
Q

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

A

No

106
Q

How are nulls sorted by default?

A

Null values are last in a sort

107
Q

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

A
  1. ASC2. DESC
108
Q

How are nulls sorted when using an ORDER BY?

A

nulls are sorted last

109
Q

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

A
  1. NULLS FIRST2. NULLS LAST
110
Q

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

A

& (ampersand)

111
Q

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

A

&& (double-ampersand)

112
Q

What is the only column in the table named DUAL?

A

DUMMY

113
Q

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

A

X

114
Q

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

A

1949

115
Q

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

A

2050

116
Q

What are the 2 main types of Data type conversions?

A
  1. Implicit data type conversion2. Explicit data type conversion
117
Q

What type of conversion is done by the Oracle server?

A

implicit

118
Q

The CASE is classified as what type of function?

A

a general single-row function

119
Q

What function is the CASE expression equivalent to?

A

NULLIFCASE WHEN exp1 = exp2THEN NULLELSE exp1 END

120
Q

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

A

C

121
Q

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

A

NoYou can group by something that is not in the select

122
Q

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?

A

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

123
Q

What 2 clauses can Group functions can be used?

A
  1. SELECT2. HAVING3. ORDER BY
124
Q

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

A
  1. SELECT2. FROM3. WHERE4. GROUP BY5. HAVING6. ORDER BY
125
Q

Can aliases save on memory?

A

Yes

126
Q

Can aliases speed up database access?

A

Yes

127
Q

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

A

ORDER BY cust_limitORDER by cust_limit NULLS LASTnulls are automatically sorted last

128
Q

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

A

ORDER BY cust_limit NULLS FIRST

129
Q

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

A

Null

130
Q

What is the default date format for Oracle SQL Developer?

A

DD/MON/RRExample: 10/AUG/03

131
Q

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

A

DD-MON-RRExample: 10-AUG-03

132
Q

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

A

20

133
Q

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

A

19

134
Q

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

A

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

135
Q

What is the last keyword of a CASE statement?

A

ENDCASE….WHEN…THEN…END

136
Q

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

A

SELECTWHEREORDER BYThe case statement cannot be used in the FROM clause

137
Q

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

A

255 levels of WHEN…THEN levels

138
Q

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;

A

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.

139
Q

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

A

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

140
Q

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

A

30

141
Q

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

A

Tutorial’s Point compiles technical tutorials

142
Q

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

A

“Result is a string but also has ““NULL”” in string.’A’ || null || ‘B’ returns ANULLB”

143
Q

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

A

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

144
Q

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

A

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

145
Q

What is the result of this statement? WHERE A = ‘’

A

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

146
Q

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

A

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

147
Q

TOP N analysis requires what?

A

An ORDER BY clausean inline view and an outer query

148
Q

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

A

DISTINCTUNIQUEBoth are synonymous

149
Q

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;

A

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.

150
Q

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

A

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

151
Q

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

A

WHERE clause or FROM clause

152
Q

What is the default escape character in Oracle?

A

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.

153
Q

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

A

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

154
Q

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

A

Yes.DISTINCT must directly follow SELECT in this statement.

155
Q

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

A
  1. Employee Name:2. 10
156
Q

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′

A

Yes.All are valid.

157
Q

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

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

158
Q

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;

A

+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

159
Q

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

A

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

160
Q

A TOP N analysis requires what?

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

DSO SQL (15 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions