WorksheetsOracle ПЗ-9
Total questions: 10
Worksheet time: 5mins
Name
Class
Date
1.
Какой SQL-запрос вернёт фамилию, должность и дату найма сотрудников, нанятых с 20 февраля 1998 по 1 мая 1998?
a)
SELECT last_name, job_id, hire_date FROM employees WHERE hire_date BETWEEN '20-FEB-1998' AND '01-MAY-1998' ORDER BY hire_date;
b)
SELECT last_name, job_id, hire_date FROM employees WHERE hire_date > '20-FEB-1998' AND hire_date < '01-MAY-1998';
c)
SELECT last_name, job_id, hire_date FROM employees WHERE hire_date >= '20-FEB-1998' AND hire_date <= '01-MAY-1998' ORDER BY last_name;
d)
SELECT last_name, job_id, hire_date FROM employees WHERE hire_date BETWEEN '01-MAY-1998' AND '20-FEB-1998';
e)
SELECT last_name, job_id, hire_date FROM employees WHERE hire_date >= '20-FEB-1998' AND hire_date <= '01-MAY-1998' ORDER BY job_id;
2.
Какой SQL-запрос вернёт количество месяцев с момента найма сотрудника?
a)
SELECT last_name, hire_date, ROUND(MONTHS_BETWEEN(SYSDATE, hire_date)) AS months_worked FROM employees ORDER BY months_worked;
b)
SELECT last_name, hire_date, ROUND(DATE_DIFF(SYSDATE, hire_date, 'month')) AS months_worked FROM employees;
c)
SELECT last_name, hire_date, TRUNC(DATE_DIFF(SYSDATE, hire_date, 'months')) AS months_worked FROM employees;
d)
SELECT last_name, hire_date, MONTHS_DIFFERENCE(hire_date, SYSDATE) AS months_worked FROM employees;
e)
SELECT last_name, hire_date, ROUND(TIMESTAMPDIFF(MONTH, hire_date, SYSDATE)) AS months_worked FROM employees;
3.
Какой SQL-запрос вернёт фамилии сотрудников в формате: первая буква заглавная, остальные строчные?
a)
SELECT INITCAP(last_name) FROM employees WHERE last_name LIKE 'J%' OR last_name LIKE 'A%' OR last_name LIKE 'M%' ORDER BY last_name;
b)
SELECT UPPER(SUBSTRING(last_name, 1, 1)) || LOWER(SUBSTRING(last_name, 2)) FROM employees;
c)
SELECT FORMAT(last_name, 'Title Case') FROM employees;
d)
SELECT FIRST_UPPER(last_name) FROM employees;
e)
SELECT INITCAP(last_name) WHERE last_name LIKE 'J%' OR last_name LIKE 'A%' OR last_name LIKE 'M%';
4.
Как вывести фамилии сотрудников и их зарплату в формате "Фамилия зарабатывает X, но хочет Y"?
a)
SELECT last_name || ' earns ' || salary || ' but wants ' || (salary*3) AS dream_salaries FROM employees;
b)
SELECT last_name, salary, FORMAT(salary*3, 'MONEY') FROM employees;
c)
SELECT CONCAT(last_name, ' earns ', salary, ' but wants ', salary * 3) FROM employees;
d)
SELECT last_name + ' earns ' + salary + ' but wants ' + (salary*3) FROM employees;
e)
SELECT FORMAT_SALARY(last_name, salary) FROM employees;
5.
Какой SQL-запрос правильно выведет фамилии сотрудников и их зарплаты в формате звездочек (1 звезда = $1000)?
a)
SELECT last_name, RPAD('', salary / 1000, '') AS employee_and_their_salaries FROM employees ORDER BY salary DESC;
b)
SELECT last_name, REPEAT('*', salary / 1000) FROM employees ORDER BY salary DESC;
c)
SELECT last_name, LPAD(salary, 10, '*') FROM employees;
d)
SELECT last_name, FORMAT(salary, '*') FROM employees;
e)
SELECT last_name, '*' * (salary / 1000) FROM employees;
6.
Как правильно вывести фамилии сотрудников, работающих в должностях sales representative или stock clerk, но с зарплатой НЕ 2500, 3500 или 7000?
a)
SELECT last_name, job_id, salary FROM employees WHERE job_id IN ('SA_REP', 'ST_CLERK') AND salary NOT IN (2500, 3500, 7000);
b)
SELECT last_name, job_id, salary FROM employees WHERE job_id = 'SA_REP' OR job_id = 'ST_CLERK' AND salary <> 2500 OR salary <> 3500 OR salary <> 7000;
c)
SELECT last_name, job_id, salary FROM employees WHERE job_id LIKE '%REP%' OR job_id LIKE '%CLERK%' AND salary NOT BETWEEN 2500 AND 7000;
d)
SELECT last_name, job_id, salary FROM employees WHERE job_id IN ('SA_REP', 'ST_CLERK') AND salary != 2500 AND salary != 3500 AND salary != 7000;
e)
SELECT last_name, job_id, salary FROM employees WHERE job_id IN ('SA_REP', 'ST_CLERK') AND salary NOT BETWEEN 2500 AND 7000;
7.
Какой SQL-запрос корректно использует DECODE для присвоения категорий должностям?
a)
SELECT last_name, job_id, DECODE(job_id, 'AD_PRES', 'A', 'ST_MAN', 'B', 'IT_PROG', 'C', 'SA_REP', 'D', 'ST_CLERK', 'E', '0') AS job_grade FROM employees;
b)
SELECT last_name, job_id, CASE job_id WHEN 'AD_PRES' THEN 'A' ELSE '0' END AS job_grade FROM employees;
c)
SELECT last_name, job_id, FORMAT_CATEGORY(job_id) FROM employees;
d)
SELECT last_name, job_id, JOB_CATEGORY(job_id) FROM employees;
e)
SELECT last_name, job_id, JOB_CODE(job_id) FROM employees;
8.
Какой SQL-запрос выведет фамилии сотрудников и их комиссионные, заменяя NULL на "No Commission"?
a)
SELECT last_name, NVL(commission_pct, 'No Commission') AS commission FROM employees;
b)
SELECT last_name, COALESCE(commission_pct, 'No Commission') FROM employees;
c)
SELECT last_name, IFNULL(commission_pct, 'No Commission') FROM employees;
d)
SELECT last_name, NULLIF(commission_pct, 'No Commission') FROM employees;
e)
SELECT last_name, commission_pct ELSE 'No Commission' FROM employees;
9.
Как вывести фамилию каждого сотрудника, дату найма и день пересмотра зарплаты через 6 месяцев?
a)
SELECT last_name, hire_date, NEXT_DAY(ADD_MONTHS(hire_date, 6), 'MONDAY') AS review FROM employees;
b)
SELECT last_name, hire_date, DATE_ADD(hire_date, INTERVAL 6 MONTH) FROM employees;
c)
SELECT last_name, hire_date, TIMESTAMPADD(MONTH, 6, hire_date) FROM employees;
d)
SELECT last_name, hire_date, DATE_NEXT(hire_date, 6) FROM employees;
e)
SELECT last_name, hire_date, FIRST_DAY_OF_NEXT_PERIOD(hire_date, 6) FROM employees;
10.
Какой SQL-запрос корректно сортирует сотрудников по дню недели их найма, начиная с понедельника?
a)
SELECT last_name, hire_date, TO_CHAR(hire_date, 'DAY') AS day FROM employees ORDER BY TO_CHAR(hire_date, 'D');
b)
SELECT last_name, hire_date, FORMAT(hire_date, 'WEEKDAY') FROM employees ORDER BY hire_date;
c)
SELECT last_name, hire_date, DAYNAME(hire_date) FROM employees ORDER BY hire_date;
d)
SELECT last_name, hire_date, WEEKDAY(hire_date) FROM employees ORDER BY hire_date;
e)
SELECT last_name, hire_date, DATEPART(WEEKDAY, hire_date) FROM employees ORDER BY hire_date;
100 %
