Oracle__4. Oracle 1Z0-051 Exam - Select Statement Flashcards
What is the simplest syntax for the SELECT clause to return all fields of a table?
SELECT *
What is the simplest syntax for the SELECT clause to return all unique fields of a table?
SELECT DISTINCT *
What default keyword in the SELECT clause will return all rows including duplicates?
ALLSyntax: SELECT ALL *The keyword ALL is default
What is a clause?
a subset of a command that modifies the command.
What is the correct order of the following clauses in a select statement? ORDER BY SELECT WHERE HAVING GROUP BY
SELECTFROMWHEREGROUP BYHAVINGORDER BY
If table aliases are created in the FROM clause what other clauses can use those aliases?
SELECTWHEREGROUP BYORDER BY
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
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.
What is the keyword in the ORDER BY clause meaning to sort from lowest to highest?
ASCmeaning AscendingASC is the default
What keyword will only display the first 10 rows of a SELECT statement?
WHERE ROWNUM <= 10
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
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.
What is the FROM clause syntax for creating an alias ‘X’ for a table named Table1
FROM table1 AS XThe keyword AS is optional
If column aliases are created in the SELECT clause which other clause CANNOT use those aliases?
GROUP BY
What is the only clause that you can create table aliases?
FROM
What is the only clause that you can create column aliases?
SELECT
What will be the difference of the returned data between these 2 clauses? ORDER BY column1, column2 ORDER BY 1, 2
Identical results
If column aliases are created in the SELECT clause which other clause CAN use those column aliases?
ORDER BY
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.
Which clause from a SELECT statement is used to eliminate rows from the results?
WHERE
Which clause from a SELECT statement is used to eliminate groups from the results?
HAVING
what is returned from the following statement? SELECT 2*5 + 7 - 6/2 FROM DUAL
142*5 = 106/2 = 310+7-3 = 14
What is DUAL?
a table in ORACLE that has one column named DUMMY with a value of X
Are the following statement equal? SELECT * FROM EMPLOYEES; Select * FROM EMPLOYEES; select * FROM EMPLOYEES;
Yes. Keyword capitalization of keywords has no affect.
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”
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
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
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
“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’
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”
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”
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
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.
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
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
Column aliases can only be defined in which clause of a SQL Statement?
The SELECT Clause
Table aliases can only be defined in which clause of a SQL Statement?
The FROM Clause
What is the scope of an alias?
only within the SQL Statement
What is needed if the alias has a space in the name?
Quote around the entire Alias name
“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%’
“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’
In a LIKE comparison, what is the wildcard symbol for matching a string of any length including zero length?
% (percentage sign)
In a LIKE comparison. what is the wildcard symbol for matching a single character?
_ (underscore)
“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’
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
The HAVING clause is used in combination of with what other clause?
GROUP BY
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.
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
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
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
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
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’
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
“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
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’{……..}’
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: { … )
What is returned from this statement? SELECT ‘Ted’ || q’[’s Car]’ || ‘ is red.’ FROM dual
Ted’s Car is red
What is returned from this statement? SELECT ‘Ted’ || q’’ || ‘ is red.’ FROM dual
Ted’s (Car) is red
What will be returned with the following statement? SELECT ‘Ted’ || q’
an error because the bracket types to not match< does not match with ]
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
Can a constraint be defined for a column whose data type is LONG?
No
What data type should a LONG be converted in order to be more modern?
CLOB
Can a CLOB be used in a GROUP BY and an ORDER BY clause?
Yes
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
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.
What is the syntax for sorting a column of numbers named cost from highest to lowest?
ORDER BY cost DESC
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
Define numeric literal?
A numeric value stored as a string
“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”
Is a character sort case-sensitive by default?
Yes.
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.
Are dates formats case sensitive when sorted?
Yes.January sorts before JANUARY
What clause can exclude rows before dividing them into groups?
The WHERE clause
What clause can exclude groups?
The HAVING clause
The simplest form of a Select Statement must include which 2 clauses?
- SELECT clause2. FROM clause
What answer will you get if you divide a number by zero?
error
What answer will you get if you divide a number by Null?
NULL
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”
What is need if you have special characters such as $ or # in an Alias?
The Alias name needs to be surrounded by quotes.
What is need if the Alias has a space?
The Alias name needs to be surrounded by quotes.
Dates and literal characters must be enclosed by what?
single quotes.
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’
What keyword in a SELECT clause eliminates duplicate records?
DISTINCT
You can use arithmetic operators in any clause of a SQL statement except?
FROM clause
What are the 2 main categories of conditions used in a WHERE clause?
- logical2. comparison
What category of conditions are the following: =, <=, BETWEEN, IN, LIKE, NULL
comparison conditions
What category of conditions are the following: AND, OR, NOT
logical conditions
The WHERE clause restricts what?
Rows
What can be used in a SELECT clause that cannot be used in a WHERE clause?
a column alias
What is the default date display format of Oracle date type?
DD-MON-RR
What 2 Symbols can represent NOT?
- ! (exclamation point)2. ^ (carat)!= is not equal
Using the BETWEEN operator, which limit is specified first?
lower limit then upper limitExample: BETWEEN 1000 AND 2000Example: BETWEEN ‘King’ AND ‘Smith’
The IN operator is equivalent to what?
The OR conditionThere is no performance benefits between the IN or the OR
Are lower case values return with this condition: LIKE ‘S%’
NoLetter between quote are case sensitive
What 2 wild card symbols can be used to construct a search string?
- % (percentage)2. _ (underscore)
What words would be return when using LIKE ‘_o%’
The second letter of the word has an lower case o.
What is the escape identifier in this: LIKE ‘%SA_%’ ESCAPE ‘'
ESCAPE
What is the ESCAPE identify as the escape character in this: LIKE ‘%SA_%’ ESCAPE ‘'
\ (backslash)
What are the two conditions test for null in the where clause?
- IS NULL2. IS NOT NULL
What are the 3 logical operators available in SQL?
- AND2. OR3. NOT
If there are no parenthesis, which is evaluated first: addition or multiplication
Multiplication then addition
If there are no parenthesis, which is evaluated first: AND logical or OR logical
AND logical then OR logical
If there are no parenthesis, which is evaluated first: NOT logical or AND logical
NOT logical then AND logical
Are column aliases defined in the SELECT clause allowed in the ORDER BY clause?
Yes
Are column aliases defined in the SELECT clause allowed in the WHERE clause?
No
How are nulls sorted by default?
Null values are last in a sort
What are 2 other keywords allowed in the ORDER BY clause?
- ASC2. DESC
How are nulls sorted when using an ORDER BY?
nulls are sorted last
What key words can be used in the ORDER BY clause to sort NULLs?
- NULLS FIRST2. NULLS LAST
What symbol is used to indicate the variable will be a user input at runtime?
& (ampersand)
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)
What is the only column in the table named DUAL?
DUMMY
What is the only value in the DUMMY column in the table named DUAL?
X
What year is interpreted by RR if the date is 10-Oct-1949?
1949
What year is interpreted by RR if the date is 10-Oct-1950?
2050
What are the 2 main types of Data type conversions?
- Implicit data type conversion2. Explicit data type conversion
What type of conversion is done by the Oracle server?
implicit
The CASE is classified as what type of function?
a general single-row function
What function is the CASE expression equivalent to?
NULLIFCASE WHEN exp1 = exp2THEN NULLELSE exp1 END
What is return if expr1 is 12? CASE expr1 WHEN 10 THEN ‘A’ WHEN 20 THEN ‘B’ ELSE ‘C’ END
C
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
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.
What 2 clauses can Group functions can be used?
- SELECT2. HAVING3. ORDER BY
What is the correct order of the following clauses in a select Statement? FROM WHERE SELECT ORDER BY GROUP BY HAVING
- SELECT2. FROM3. WHERE4. GROUP BY5. HAVING6. ORDER BY
Can aliases save on memory?
Yes
Can aliases speed up database access?
Yes
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
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
What is returned from this statement? SELECT 12 + Null + 19 FROM dual
Null
What is the default date format for Oracle SQL Developer?
DD/MON/RRExample: 10/AUG/03
What is the default date format for Oracle SQL *PLUS?
DD-MON-RRExample: 10-AUG-03
What century is returned from the date 03-AUG-07 in the DD-MON-RR format?
20
What century is returned from the date 03-AUG-94 in the DD-MON-RR format?
19
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
What is the last keyword of a CASE statement?
ENDCASE….WHEN…THEN…END
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
What is the maximum number of WHEN..THEN levels in a CASE statement?
255 levels of WHEN…THEN levels
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.
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.
What is the maximum number of characters allowed in a table alias?
30
What is returned from this statement? SELECT ‘Tutorial’’s Point compiles technical tutorials’ FROM DUAL;
Tutorial’s Point compiles technical tutorials
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”
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
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
What is the result of this statement? WHERE A = ‘’
This will always return a falseThis is similar to A = null
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.
TOP N analysis requires what?
An ORDER BY clausean inline view and an outer query
What 2 keywords can be used to suppress duplicates in the SELECT clause
DISTINCTUNIQUEBoth are synonymous
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.
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;
A column alias names cannot be used in which clause? SELECT clause WHERE clause ORDER BY clause FROM clause
WHERE clause or FROM clause
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.
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.
Will this statement cause and error? SELECT empno, DISTINCT ename, salary FROM emp;
Yes.DISTINCT must directly follow SELECT in this statement.
What are the 2 literals in this SELECT statement? SELECT ‘Employee Name: ‘ || ename FROM emp where deptno = 10;
- Employee Name:2. 10
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.
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.
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
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.
A TOP N analysis requires what?
- An ORDER BY clause2. an inline view3. an outer query
