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 *
2
Q
What is the simplest syntax for the SELECT clause to return all unique fields of a table?
A
SELECT DISTINCT *
3
Q
What default keyword in the SELECT clause will return all rows including duplicates?
A
ALLSyntax: SELECT ALL *The keyword ALL is default
4
Q
What is a clause?
A
a subset of a command that modifies the command.
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
6
Q
If table aliases are created in the FROM clause what other clauses can use those aliases?
A
SELECTWHEREGROUP BYORDER BY
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
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.
9
Q
What is the keyword in the ORDER BY clause meaning to sort from lowest to highest?
A
ASCmeaning AscendingASC is the default
10
Q
What keyword will only display the first 10 rows of a SELECT statement?
A
WHERE ROWNUM <= 10
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
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.
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
14
Q
If column aliases are created in the SELECT clause which other clause CANNOT use those aliases?
A
GROUP BY
15
Q
What is the only clause that you can create table aliases?
A
FROM
16
Q
What is the only clause that you can create column aliases?
A
SELECT
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
18
Q
If column aliases are created in the SELECT clause which other clause CAN use those column aliases?
A
ORDER BY
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.
20
Q
Which clause from a SELECT statement is used to eliminate rows from the results?
A
WHERE
21
Q
Which clause from a SELECT statement is used to eliminate groups from the results?
A
HAVING
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
23
Q
What is DUAL?
A
a table in ORACLE that has one column named DUMMY with a value of X
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.
25
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"
26
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
27
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
28
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
29
"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'
30
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"
31
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"
32
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
33
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.
34
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
35
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
36
Column aliases can only be defined in which clause of a SQL Statement?
The SELECT Clause
37
Table aliases can only be defined in which clause of a SQL Statement?
The FROM Clause
38
What is the scope of an alias?
only within the SQL Statement
39
What is needed if the alias has a space in the name?
Quote around the entire Alias name
40
"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%'
41
"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'
42
In a LIKE comparison, what is the wildcard symbol for matching a string of any length including zero length?
% (percentage sign)
43
In a LIKE comparison. what is the wildcard symbol for matching a single character?
_ (underscore)
44
"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'
45
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
46
The HAVING clause is used in combination of with what other clause?
GROUP BY
47
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.
48
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
49
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
50
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
51
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
52
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'
53
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
54
"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
55
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'{........}'
56
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: { ... )
57
What is returned from this statement? SELECT 'Ted' || q'['s Car]' || ' is red.' FROM dual
Ted's Car is red
58
What is returned from this statement? SELECT 'Ted' || q'' || ' is red.' FROM dual
Ted's (Car) is red
59
What will be returned with the following statement? SELECT 'Ted' || q'
an error because the bracket types to not match< does not match with ]
60
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
61
Can a constraint be defined for a column whose data type is LONG?
No
62
What data type should a LONG be converted in order to be more modern?
CLOB
63
Can a CLOB be used in a GROUP BY and an ORDER BY clause?
Yes
64
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
65
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.
66
What is the syntax for sorting a column of numbers named cost from highest to lowest?
ORDER BY cost DESC
67
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
68
Define numeric literal?
A numeric value stored as a string
69
"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"
70
Is a character sort case-sensitive by default?
Yes.
71
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.
72
Are dates formats case sensitive when sorted?
Yes.January sorts before JANUARY
73
What clause can exclude rows before dividing them into groups?
The WHERE clause
74
What clause can exclude groups?
The HAVING clause
75
The simplest form of a Select Statement must include which 2 clauses?
1. SELECT clause2. FROM clause
76
What answer will you get if you divide a number by zero?
error
77
What answer will you get if you divide a number by Null?
NULL
78
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"
79
What is need if you have special characters such as $ or # in an Alias?
The Alias name needs to be surrounded by quotes.
80
What is need if the Alias has a space?
The Alias name needs to be surrounded by quotes.
81
Dates and literal characters must be enclosed by what?
single quotes.
82
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'
83
What keyword in a SELECT clause eliminates duplicate records?
DISTINCT
84
You can use arithmetic operators in any clause of a SQL statement except?
FROM clause
85
What are the 2 main categories of conditions used in a WHERE clause?
1. logical2. comparison
86
What category of conditions are the following: =, <=, BETWEEN, IN, LIKE, NULL
comparison conditions
87
What category of conditions are the following: AND, OR, NOT
logical conditions
88
The WHERE clause restricts what?
Rows
89
What can be used in a SELECT clause that cannot be used in a WHERE clause?
a column alias
90
What is the default date display format of Oracle date type?
DD-MON-RR
91
What 2 Symbols can represent NOT?
1. ! (exclamation point)2. ^ (carat)!= is not equal
92
Using the BETWEEN operator, which limit is specified first?
lower limit then upper limitExample: BETWEEN 1000 AND 2000Example: BETWEEN 'King' AND 'Smith'
93
The IN operator is equivalent to what?
The OR conditionThere is no performance benefits between the IN or the OR
94
Are lower case values return with this condition: LIKE 'S%'
NoLetter between quote are case sensitive
95
What 2 wild card symbols can be used to construct a search string?
1. % (percentage)2. _ (underscore)
96
What words would be return when using LIKE '_o%'
The second letter of the word has an lower case o.
97
What is the escape identifier in this: LIKE '%SA\_%' ESCAPE '\'
ESCAPE
98
What is the ESCAPE identify as the escape character in this: LIKE '%SA\_%' ESCAPE '\'
\ (backslash)
99
What are the two conditions test for null in the where clause?
1. IS NULL2. IS NOT NULL
100
What are the 3 logical operators available in SQL?
1. AND2. OR3. NOT
101
If there are no parenthesis, which is evaluated first: addition or multiplication
Multiplication then addition
102
If there are no parenthesis, which is evaluated first: AND logical or OR logical
AND logical then OR logical
103
If there are no parenthesis, which is evaluated first: NOT logical or AND logical
NOT logical then AND logical
104
Are column aliases defined in the SELECT clause allowed in the ORDER BY clause?
Yes
105
Are column aliases defined in the SELECT clause allowed in the WHERE clause?
No
106
How are nulls sorted by default?
Null values are last in a sort
107
What are 2 other keywords allowed in the ORDER BY clause?
1. ASC2. DESC
108
How are nulls sorted when using an ORDER BY?
nulls are sorted last
109
What key words can be used in the ORDER BY clause to sort NULLs?
1. NULLS FIRST2. NULLS LAST
110
What symbol is used to indicate the variable will be a user input at runtime?
& (ampersand)
111
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)
112
What is the only column in the table named DUAL?
DUMMY
113
What is the only value in the DUMMY column in the table named DUAL?
X
114
What year is interpreted by RR if the date is 10-Oct-1949?
1949
115
What year is interpreted by RR if the date is 10-Oct-1950?
2050
116
What are the 2 main types of Data type conversions?
1. Implicit data type conversion2. Explicit data type conversion
117
What type of conversion is done by the Oracle server?
implicit
118
The CASE is classified as what type of function?
a general single-row function
119
What function is the CASE expression equivalent to?
NULLIFCASE WHEN exp1 = exp2THEN NULLELSE exp1 END
120
What is return if expr1 is 12? CASE expr1 WHEN 10 THEN 'A' WHEN 20 THEN 'B' ELSE 'C' END
C
121
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
122
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.
123
What 2 clauses can Group functions can be used?
1. SELECT2. HAVING3. ORDER BY
124
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
125
Can aliases save on memory?
Yes
126
Can aliases speed up database access?
Yes
127
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
128
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
129
What is returned from this statement? SELECT 12 + Null + 19 FROM dual
Null
130
What is the default date format for Oracle SQL Developer?
DD/MON/RRExample: 10/AUG/03
131
What is the default date format for Oracle SQL *PLUS?
DD-MON-RRExample: 10-AUG-03
132
What century is returned from the date 03-AUG-07 in the DD-MON-RR format?
20
133
What century is returned from the date 03-AUG-94 in the DD-MON-RR format?
19
134
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
135
What is the last keyword of a CASE statement?
ENDCASE....WHEN...THEN...END
136
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
137
What is the maximum number of WHEN..THEN levels in a CASE statement?
255 levels of WHEN...THEN levels
138
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.
139
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.
140
What is the maximum number of characters allowed in a table alias?
30
141
What is returned from this statement? SELECT 'Tutorial''s Point compiles technical tutorials' FROM DUAL;
Tutorial's Point compiles technical tutorials
142
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"
143
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
144
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
145
What is the result of this statement? WHERE A = ''
This will always return a falseThis is similar to A = null
146
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.
147
TOP N analysis requires what?
An ORDER BY clausean inline view and an outer query
148
What 2 keywords can be used to suppress duplicates in the SELECT clause
DISTINCTUNIQUEBoth are synonymous
149
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.
150
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;
151
A column alias names cannot be used in which clause? SELECT clause WHERE clause ORDER BY clause FROM clause
WHERE clause or FROM clause
152
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.
153
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.
154
Will this statement cause and error? SELECT empno, DISTINCT ename, salary FROM emp;
Yes.DISTINCT must directly follow SELECT in this statement.
155
What are the 2 literals in this SELECT statement? SELECT 'Employee Name: ' || ename FROM emp where deptno = 10;
1. Employee Name:2. 10
156
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.
157
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.
158
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
159
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.
160
A TOP N analysis requires what?
1. An ORDER BY clause2. an inline view3. an outer query
