# Difference between revisions of "Using Null Quiz"

Using Null Quiz

teacher
id dept name phone mobile
101 1 Shrivell 2753 07986 555 1234
102 1 Throd 2754 07122 555 1920
103 1 Splint 2293
104 Spiregrain 3287
105 2 Cutflower 3212 07996 555 6574
dept
id name
1 Computing
2 Design
3 Engineering

<quiz shuffle=none display=simple> {Which of the following uses of JOIN is correct? |type="()"} - SELECT teacher.name, dept.name FROM teacher JOIN dept ON (dept = id) - SELECT teacher.name, dept.name FROM teacher, dept INNER JOIN ON (teacher.dept = dept.id) - SELECT teacher.name, dept.name FROM teacher, dept JOIN WHERE(teacher.dept = dept.id) - SELECT teacher.name, dept.name FROM teacher OUTER JOIN dept ON dept.id + SELECT teacher.name, dept.name FROM teacher LEFT OUTER JOIN dept ON (teacher.dept > dept.id)

{Select the correct statement that shows the name of department which employs Cutflower |type="()"} - SELECT dept.name FROM teacher JOIN dept ON (dept.id = (SELECT dept FROM teacher WHERE name = 'Cutflower')) - SELECT dept.name FROM teacher JOIN dept ON (dept.id = teacher.dept) WHERE dept.id = (SELECT dept FROM teacher HAVING name = 'Cutflower') + SELECT dept.name FROM teacher JOIN dept ON (dept.id = teacher.dept) WHERE teacher.name = 'Cutflower' - SELECT dept.name FROM teacher JOIN dept WHERE dept.id = (SELECT dept FROM teacher WHERE name = 'Cutflower') - SELECT name FROM teacher JOIN dept ON (id = dept) WHERE id = (SELECT dept FROM teacher WHERE name = 'Cutflower')

{Which of following uses of JOIN shows list of all the departments and number of employed teachers? |type="()"} - SELECT dept.name, COUNT(*) FROM teacher LEFT JOIN dept ON dept.id = teacher.dept - SELECT dept.name, COUNT(teacher.name) FROM teacher, dept JOIN ON dept.id = teacher.dept GROUP BY dept.name - SELECT dept.name, COUNT(teacher.name) FROM teacher JOIN dept ON dept.id = teacher.dept GROUP BY dept.name - SELECT dept.name, COUNT(teacher.name) FROM teacher LEFT OUTER JOIN dept ON dept.id = teacher.dept GROUP BY dept.name + SELECT dept.name, COUNT(teacher.name) FROM teacher RIGHT JOIN dept ON dept.id = teacher.dept GROUP BY dept.name

{Using `SELECT name, dept, COALESCE(dept, 0) AS result FROM teacher` on `teacher` table will: |type="()"} - display 0 in result column for all teachers + display 0 in result column for all teachers without department - do nothing - the statement is incorrect - set dept value of all teachers to 0 - set dept value of all teachers without department to 0

</quiz>
{"ft_lang_label":"__label__en","ft_lang_prob":0.69417614,"math_prob":0.77531075,"size":2719,"snap":"2019-26-2019-30","text_gpt3_token_len":719,"char_repetition_ratio":0.27366483,"word_repetition_ratio":0.28297362,"special_character_ratio":0.29790363,"punctuation_ratio":0.14450867,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.96913147,"pos_list":[0],"im_url_duplicate_count":[null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2019-07-20T01:07:15Z\",\"WARC-Record-ID\":\"<urn:uuid:47078bf8-469e-49b3-b269-fbbee7ca70bb>\",\"Content-Length\":\"22022\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:ce867602-71cc-4897-939e-8b548d4c5f7b>\",\"WARC-Concurrent-To\":\"<urn:uuid:4e13ecd6-3af8-44d2-bdb3-092c47bbb591>\",\"WARC-IP-Address\":\"146.176.166.58\",\"WARC-Target-URI\":\"http://zh.sqlzoo.net/w/index.php?title=Using_Null_Quiz&diff=1779&oldid=1778\",\"WARC-Payload-Digest\":\"sha1:SGX7VPMDXTWROQZGW6VRXGCQSNKYFJY6\",\"WARC-Block-Digest\":\"sha1:A4J7PA5ZOILXIWUMGJ3KK3IACFKEQ3BJ\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2019/CC-MAIN-2019-30/CC-MAIN-2019-30_segments_1563195526401.41_warc_CC-MAIN-20190720004131-20190720030131-00161.warc.gz\"}"}