不定项选择题,共6题,全部答对20分,答对部分10分,选错、选多0分;满分120分。
下列哪一对查询语句将返回完全相同的结果集?
SELECT * FROM employees WHERE hire_date BETWEEN '2020-01-01' AND '2020-12-31' VS SELECT * FROM employees WHERE hire_date >= '2020-01-01' OR hire_date <= '2020-12-31'
SELECT * FROM employees WHERE hire_date BETWEEN '2020-01-01' AND '2020-12-31'
SELECT * FROM employees WHERE hire_date >= '2020-01-01' OR hire_date <= '2020-12-31'
SELECT * FROM employees WHERE manager_id IS NULL VS SELECT * FROM employees WHERE manager_id = NULL
SELECT * FROM employees WHERE manager_id IS NULL
SELECT * FROM employees WHERE manager_id = NULL
SELECT * FROM employees WHERE NOT (department = 'HR' OR department = 'Finance') VS SELECT * FROM employees WHERE department != 'HR' AND department != 'Finance'
SELECT * FROM employees WHERE NOT (department = 'HR' OR department = 'Finance')
SELECT * FROM employees WHERE department != 'HR' AND department != 'Finance'
SELECT * FROM employees WHERE department = 'Sales' AND salary > 50000 VS SELECT * FROM employees WHERE salary > 50000 AND department = 'Sales'
SELECT * FROM employees WHERE department = 'Sales' AND salary > 50000
SELECT * FROM employees WHERE salary > 50000 AND department = 'Sales'
以下哪些函数可以用于提取字符串的某一部分?
INSTR(str, 'sub')
SUBSTR(str, 2, 3)
LEFT(str, 2)
REPLACE(str, 'old', 'new')
认识矩阵。
记矩阵M=
a23 = ?
3
1
2
5
关于SQL中的LEFT JOIN操作,下列陈述哪些是正确的?
LEFT JOIN
在使用LEFT JOIN时,可以确保左表中的每一行都至少出现在一次结果集中,即便它在右表中没有对应的匹配行。
如果左表的某一行在右表中没有匹配项,那么该行的结果将包括左表的所有列以及右表对应位置上的NULL值。
LEFT JOIN会移除左表中那些在右表找不到匹配项的行。
LEFT JOIN只返回两个表中完全匹配的记录。
考虑有一个名为sales的表,包含字段employee_id, sale_amount, sale_date。为了找出每个员工每个月销售额最高的记录,并且需要确保即使有多个相同最高销售额的记录也能被选中,应该使用下列哪个窗口函数?
sales
employee_id
sale_amount
sale_date
RANK() OVER (PARTITION BY employee_id, MONTH(sale_date) ORDER BY sale_amount DESC)
DENSE_RANK() OVER (PARTITION BY employee_id, MONTH(sale_date) ORDER BY sale_amount DESC)
ROW_NUMBER() OVER (PARTITION BY employee_id, MONTH(sale_date) ORDER BY sale_amount DESC)
DENSE_RANK() OVER (PARTITION BY MONTH(sale_date) ORDER BY sale_amount DESC)
用SQL计算月度新用户的次月留存率,以下说法正确的是:
计算次月留存率时,应该只考虑那些在第一个月之后恰好一个月内有重复活动的用户。
留存率可以用公式表示为:(下个月活跃的新用户数量 / 第一个月的新用户总数) * 100。
识别新用户需要使用MAX()聚合函数来找到每个用户的首次活动日期。
无需使用窗口函数,也能完成次月留存率的计算。