View source for SQL Quiz

[[Category:SQL]]

 IMPORTANT NOTE for trainers only: 
 Please DO NOT change these materials without direct permission from LUKASZ SOKOLOWSKI.

{{Can I use your material}}

== SQL Quiz ==

<pre>
SQL BASICS - QUIZ (for Oracle and MSSQL)

15 questions.
Only one answer is correct.
You have 15 min.
60% to pass.
The author of this test will be your teacher on the advanced session, 
should you choose to accept the mission (-;




1. "SELECT" clause, by itself only

a) filters the rows
b) chooses the source of data
c) filters the columns
d) don't know




2. "ONLY FETCH FIRST 2 ROWS" or "2 TOP"

a) removes first 2 rows
b) gives just 2 first rows from the result
c) does nothing
d) dunno




3. "DISTINCT"

a) makes the result unique, each column separately
b) gives the non redundant result, per vector of columns
c) must be always at the end of the query
d) 've no watery idea




4. "UNION ALL" vs "UNION"

a) there is no single difference between them
b) the first one doesn't show the duplicates
c) both are SQL set operators 
d) How should I know?




5. "WHERE column_name <> 200" vs "WHERE column_name != 200"

a) they both give EXACTLY the same result
b) the '!=' is the ISO one
c) aargh.. why they made three ways to do the same thing, huh?!
d) Who the hack supposed to know that?




6. "SELECT SYSDATE FROM DUAL" or "SELECT GETDATE()"

a) will ALWAYS result into this format: '21/06/29'
b) uses the table, which has real and meaningful data
c) has a minimum amount of sql clauses to run a query
d) Am I the only one who doesn't know, huh?




7. 'I am a literal string, wrap me ALWAYS into single quotes'

a) The value above says TRUE
b) The sentence DIRECTLY above is FALSE
c) We should always wrap literal strings into double citation
d) Oh, boy - who the bell should know it?




8. "ORDER BY 3"

a) this clause must be always EXACTLY after "FROM" clause
b) it will sort the final result by the third element in "SELECT" clause, descending
c) sorts the result of the query
d) dunno




9. How to do the "right outer equi-join"?

a) "FROM t2, t3 WHERE t2.id = t3.no (+)" and "t2 OUTER JOIN t3 ON t2.id = t3.no"
b) "FROM t2 JOIN t3 ON t2.id = t3.no" and "t2 INNER t3 ON t2.id = t3.no"
c) "FROM t2, t3 WHERE t2.id (+)= t3.no" and "t2 RIGHT JOIN t3 ON t2.id = t3.no"
d) nope, nothing rings the bell yet..




10. This expresssssssion "column_name LIKE '_A%'"

a) should be used only in "SELECT" clause
b) can be used in "SELECT" clause
c) searches for the strings like: KARL, LA, GAMING, CALVIN_KL
d) (in the Treeish talk): hummmmmmm.. nommmmmmm.. naaaaahh..




11. "SELECT very_ugly_column_name AS 'So neaty now!'"

a) will result with an error in 'Oracle' but works in 'MSSQL'
b) makes an extra column with same value in each row: "So neaty now!"
c) syntax like that(with an 'AS' keyword) no longer works here in 'Oracle' and 'MSSQL'
d) Ook. Oooook.. Ook, oook. Ook..




12. "k XOR w" operator

a) does this very logic: (k OR w) AND NOT (k AND w)
b) we use it EXACTLY the way it's written here
c) there is no such operator in 'Oracle' and 'MSSQL'
d) does the wicked logic and no one knows the magic here




13. In the query like the one below:

"SELECT column_name2, column_name3 AS New nice name
FROM tab4 
GROUP BY column_name3
ORDER BY column_name3;"

a) the result will be properly first sorted and then grouped
b) the result will be properly first aggregated and then sorted
c) the result will fail
d) ..how Lucky me, I still don't know that..




14. "WHERE min(column2) >= 550"
a) such a non conditional statement will give an empty result
b) it will result with something
c) this will fail
d) dunno




15. Take a Look at this not working query:

"SELECT
   max(col1) AS "The biggest ..."
   , concat(col2, ' is a ', col3) AS "Why not to duck this name, huh?"
FROM t6 JOIN t7 ON t6.id = t7.signedID
WHERE col4 IN (1, 2, 5) AND col5 < 700
   OR
   col6 IN ( SELECT col6, col7 FROM t4 WHERE col8 BETWEEN 24 AND 127 )
GROUP BY col2, col3
HAVING avg(col1) > 2000
ORDER BY 2;"

a) it doesn't work ONLY because of the missing correlation between the inner and the outer queries
b) it's spoiled by the old Gremlin with his humongously huge fart 
c) this will fix it in 'Oracle': "(col6, col9) IN ... ", and it can have a workaround with correlation in 'MSSQL'
d) "I must unlearn what I have learned."    -|[====]|[-----------------------



------- THE END -------
</pre>