Crowdly

FROM department AS d, employee AS e, works_on AS w, project AS p

Marque a consulta que recupera a lista dos colaboradores e de seus projetos, ordenado de forma crescente por departamento do colaborador e, dentro de cada departamento, ordenado alfabeticamente pelo último nome e primeiro nome do colaborador.

SELECT d.Dname, e.Lname, e.Fname, p.Pname

AND w.Pno = p.Pnumber

FROM department AS d, employee AS e, works_on AS w, project AS p

✅

SELECT d.Dname, e.Lname, e.Fname, p.Pname

WHERE d.Dnumber = p.Dnum

AND w.Pno = p.Pnumber

FROM employee AS e, department AS d, works_on AS w, project AS p

SELECT d.Dname, e.Lname, e.Fname, p.Pname

FROM department AS d, employee AS e, works_on AS w, project AS p

SELECT d.Dname, e.Lname, e.Fname, p.Pname

ORDER BY d.Dname DESC, e.Lname DESC, e.Fname DESC;

AND w.Pno = p.Pnumber

FROM department AS d, employee AS e, works_on AS w, project AS p

SELECT d.Dname, e.Lname, e.Fname, p.Pname

Considere o esquema da base de dados 'Company'. A tabela works_on possui duas chaves primárias, uma para o atributo Essn e outra para o atributo Pno.

Maque a conculta que recupera o nome de todos os departamentos e, caso exista, o nome e sobrenome do gerente.

SELECT d.Dname,

INNER JOIN employee e ON e.Dno = d.Dnumber

SELECT d.Dname,

LEFT JOIN employee e ON e.Ssn = d.Mgr_ssn

✅

SELECT d.Dname,

RIGHT JOIN employee e ON e.Dno = d.Dnumber

SELECT d.Dname,

LEFT JOIN employee e ON e.Dno = d.Dnumber

SELECT d.Dname,

INNER JOIN employee e ON e.Ssn = d.Mgr_ssn

Marque a consulta que projeta o nome e salário atual dos colaboradores, bem como uma projeção de 10% de aumento no salário daqueles que trabalham no projeto 'Computerization'. Para os colaboradores que trabalham nos demais projetos ou não estão alocados em nenhum projeto, projetar um aumento de 5%.

SELECT concat(e.Fname, ' ', e.Minit , ' ', e.Lname) full_name,

WHEN 'Computerization' THEN e.salary + (e.salary * .10)

FROM employee e, works_on w, project p

ON w.Essn = e.Ssn

ON p.Pnumber = w.Pno

SELECT concat(e.Fname, ' ', e.Minit , ' ', e.Lname) full_name,

WHEN 'Computerization' THEN e.salary + (e.salary * .10)

INNER JOIN works_on w ON w.Essn = e.Ssn

INNER JOIN project p ON p.Pnumber = w.Pno

SELECT concat(e.Fname, ' ', e.Minit , ' ', e.Lname) full_name,

WHEN 'Computerization' THEN e.salary + (e.salary * .10)

LEFT JOIN works_on w ON w.Essn = e.Ssn

LEFT JOIN project p ON p.Pnumber = w.Pno

✅

SELECT concat(e.Fname, ' ', e.Minit , ' ', e.Lname) full_name,

WHEN p.Pname = 'Computerization' THEN e.salary + (e.salary * .10)

CASE

NATURAL JOIN works_on w

NATURAL JOIN project p

SELECT concat(e.Fname, ' ', e.Minit , ' ', e.Lname) full_name,

WHEN 'Computerization' THEN e.salary + (e.salary * .10)

NATURAL JOIN works_on w

LEFT JOIN project p ON p.Pnumber = w.Pno

Marque a consulta que recupera os colaboradores que residem no mesmo endereço que seu gerente.

SELECT concat(e.Fname, ' ', e.Minit, ' ', e.Lname) full_name

INNER JOIN employee m ON m.Address = e.Address;

SELECT concat(e.Fname, ' ', e.Minit, ' ', e.Lname) full_name

INNER JOIN employee m ON m.Super_ssn = e.Super_ssn

SELECT concat(e.Fname, ' ', e.Minit, ' ', e.Lname) full_name

INNER JOIN employee m ON m.Ssn = e.Super_ssn

SELECT concat(e.Fname, ' ', e.Minit, ' ', e.Lname) full_name

INNER JOIN employee m ON m.Ssn = e.Ssn

SELECT concat(e.Fname, ' ', e.Minit, ' ', e.Lname) full_name

INNER JOIN employee m ON m.Ssn != e.Super_ssn

Marque a consulta que recupera os pares de colaboradores que possuem o mesmo nome (homônimos) e números diferentes de Ssn.

SELECT concat(e1.Fname, ' ', e1.Minit, ' ', e1.Lname) full_name_1,

INNER JOIN employee AS e2 ON e1.Ssn = e2.Ssn

FROM employee AS e1

AND e1.Fname = e2.Fname

ORDER BY 1;

SELECT concat(e1.Fname, ' ', e1.Minit, ' ', e1.Lname) full_name_1,

FROM employee AS e1, employee AS e2

WHERE e1.Fname = e2.Fname

AND e1.Ssn <> e2.Ssn

GROUP BY 1;

SELECT concat(e1.Fname, ' ', e1.Minit, ' ', e1.Lname) full_name_1,

FROM employee AS e1, employee AS e2

WHERE e1.Fname = e2.Fname

AND e1.Ssn <> e2.Ssn

GROUP BY 1;

SELECT concat(e1.Fname, ' ', e1.Minit, ' ', e1.Lname) full_name_1,

NATURAL JOIN employee AS e2

FROM employee AS e1

WHERE e1.Ssn <> e2.Ssn

ORDER BY 1;

SELECT concat(e1.Fname, ' ', e1.Minit, ' ', e1.Lname) full_name_1,

INNER JOIN employee AS e2 ON e1.Ssn <> e2.Ssn

FROM employee AS e1

WHERE e1.Fname = e2.Fname

ORDER BY 1;

Marque a consulta que projeta/recupera o nome completo de todos os colaboradores que trabalham no departamento 'Research'.

SELECT concat(e.Fname, ' ', e.Minit, ' ', e.Lname) full_name

NATURAL JOIN department d

WHERE d.Dname LIKE 'Research';

SELECT concat(e.Fname, ' ', e.Minit, ' ', e.Lname) full_name

INNER JOIN department d ON d.Dnumber = e.Dno

WHERE d.Dname LIKE 'Research';

100%

SELECT concat(e.Fname, ' ', e.Minit, ' ', e.Lname) full_name

JOIN department d USING (Dno)

WHERE d.Dname LIKE 'Research';

SELECT concat(e.Fname, ' ', e.Minit, ' ', e.Lname) full_name

JOIN department d USING (Dno)

WHERE d.Dname IS 'Research';

SELECT concat(e.Fname, ' ', e.Minit, ' ', e.Lname) full_name

INNER JOIN department d ON d.Dnumber = e.Dno

WHERE d.Dname IS 'Research';

A gerência de projetos deseja monitorar quais projetos estão consumindo mais tempo da equipe. Para isso, é necessário consolidar os dados de alocação de horas em cada projeto, juntamente com o número de funcionários alocados.

Crie uma visão materializada project_hours_summary_mat_view que projete o nome dos projetos, o total de horas alocadas e o número de colaboradores alocados. Considere o esquema/banco de dados company.