4 Prova Final de SQL - quizzezinho

Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original

Quiz:
	0. Para próxima questão nós vamos usar a tabela applications_csv  disponível na mesma área do Data World (Introduction to Data Analytics).
A tabela faz o tracking de todos os candidatos que submeteram uma aplicação para uma vaga em uma grande empresa. A tabela registra o ID (applicant_id)  da pessoa que iniciou uma aplicação, quando a aplicação foi criada (application_created_at) e quando ela foi submetida (submitted_at), como vemos no snapshot da tabela abaixo.
Toda vez que uma pessoa inicia o processo de criar uma aplicação um valor de data (dd-mm-aaaa) é registrado na tabela no campo application_created_at e assim que a aplicação um valor de data é adicionado ao campo submitted_at.
Baseado nos dados acima, responda a seguinte pergunta.
1. Quantas pessoas submeteram uma aplicação e quantas pessoas não submeteram uma aplicação? 
Dica: O campo submitted_at conta toda vez que uma aplicação é submetida. Quando o campo não está vazio (IS NOT NULL) quer dizer que um registro foi feito, quando ele não contain data (IS NULL) significa que a aplicação não foi submetida.
		Explicação: # Para saber quantas pessoas submeteram uma aplicação usamos a seguinte QUERY.SELECT COUNT(DISTINCT(applicant_id))FROM applications_csvWHERE submitted_at IS NOT NULLResultado: 1500 aplicantes # Para saber quantas pessoas NÃO submeteram uma aplicação usamos a seguinte QUERY.SELECT COUNT(DISTINCT(applicant_id))FROM applications_csvWHERE submitted_at IS NULLResultado: 500 aplicantesA Resposta é:1500 pessoas enviaram uma aplicação e 500 pessoas não enviaram uma aplicação
		Alternativas:
			--> 1230 pessoas enviaram uma aplicação e 270 não enviaram
			--> 700 pessoas enviaram uma aplicação e apenas 1 não enviou uma aplicação
			--> 1500 pessoas enviaram uma aplicação e 500 pessoas não enviaram uma aplicação
			--> 900 pessoas enviaram uma aplicação e 200 não enviaram uma aplicação
	1. Utilizando a tabela san_francisco_salaries:Para ver um snapshot da tabela, use a query abaixoSELECT * FROM san_francisco_salariesLIMIT 10Responda.Quais são os 3 job titles com os mais altos salários médios (average salary)DICA: Mais uma vez precisamos utilizar uma função de agregação para calcular o salário médio por jobtitle
		Explicação: A resposta correta é GENERAL MANAGER-METROPOLITAN TRANSIT AUTHORITY399211.275Chief Investment Officer339653.7Chief of Police329183.6467E você pode chegar a este resultado utilizando a seguinte querySELECT jobtitle, AVG(totalpay) AS finalpayFROM san_francisco_salariesGROUP BY jobtitleORDER BY finalpay DESCLIMIT 3
		Alternativas:
			--> GENERAL MANAGER-METROPOLITAN TRANSIT AUTHORITY399211.275Chief Investment Officer339653.7Chief of Police329183.6467
			--> CHIEF OF DEPARTMENT, (FIRE DEPARTMENT)302377.73Gen Mgr, Public Trnsp Dept297769.4133Asst Chf of Dept (Fire Dept)281763.4839
			--> Mayor275852.5300Adm, SFGH Medical Center270047.4467Deputy Chief 3268256.5129
	2. Executando a query abaixo, obtemos uma lista de funcionários especificados pelo employee_id (numero de identificação do funcionário, qual gerente ele responde (manager) e o seu salário (salary)SELECT a.employee_id, b.manager, a.salaryFROM employee_status a JOIN employee_state_manager b ON a.employee_id=b.employee_id O manager Timothy Kring possui 14 funcionários trabalhando em seu time. Qual o salário do funcionário com o mais alto salário em seu time?DICA: Você precisa filtrar o resultados para selecionar apenas os salários dos funcionários trabalhando para o manager Timothy Kring. Depois, existe um comando para descobrir qual o maior valor em uma coluna.
		Explicação: O maior salário no time do Timothy Kring é de $102596Nós podemos obter este valor criando uma query com o camp manager e o salário máximo entre os funcionários daquele manager utilizando o comando MAX() que descobre o maior valor dentro da coluna. Você pode utilizar a query da seguinte maneiraSELECT b.manager, MAX(a.salary)FROM employee_status aJOIN employee_state_manager bON a.employee_id=b.employee_idWHERE manager = 'Timothy Kring'GROUP BY b.manager
		Alternativas:
			--> O maior salário no time do Timothy Kring é de $98201
			--> O maior salário no time do Timothy Kring é de $102596
			--> O maior salário no time do Timothy Kring é de $75891
	3. Usando a tabela netflix, responda
Quantos shows tiveram o user_rating_score igual ou acima de 90 entre os anos de 2010 à 2017 de acordo com o rating?
A tabela Netflix está exemplificada abaixo
		Explicação: A resposta correta é Você pode encontrar o resultado usando esta querySELECT rating, count(title) FROM netflixWHERE user_rating_score >= 90AND release_year BETWEEN 2010 AND 2017GROUP BY rating
		Alternativas:
			--> TV-PG67TV-G11TV-MA32PG21R35TV-1476NR8
			--> TV-PG23TV-G1TV-MA61PG40R5TV-1491NR3
			--> TV-PG11TV-G22TV-MA87PG33R52TV-1451NR13
	4. Usando a mesma tabela netflix liste o título (title) de todas as séries ou filmes que possuem a o termo "scary images" no campo ratinglevel e que tenham sido lançadas de 2013 em diante (incluindo 2013)DICA: Nós trabalhamos na aula com a função LIKE em SQL para selecionar um termo dentro de um texto
		Explicação: O resultado éKubo and the Two StringsMarvel's Hulk: Where Monsters DwellHotel Transylvania 2Jack and the Cuckoo-Clock HeartAxel: The Biggest Little HeroSELECT DISTINCT(title)FROM netflixWHERE ratinglevel LIKE '%scary images%'AND release_year >= 2013
		Alternativas:
			--> NarutoThe HunterLottie Dottie ChickenArrowBlack Mirror
			--> How I Met Your MotherSupernaturalBreaking BadThe Vampire DiariesThe Walking Dead
			--> Kubo and the Two StringsMarvel's Hulk: Where Monsters DwellHotel Transylvania 2Jack and the Cuckoo-Clock HeartAxel: The Biggest Little Hero
	5. Temos duas tabelas, user_info (usuários) e platform_usage (uso da plataforma). A tabela de usuários (user_info) contém informações que temos sobre os usuários da nossa plataforma. A tabela de uso (platform_usage) rastreia o uso da plataforma que registra o browser (navegador) e o device_type (tipo_de_dispositivo) sempre que um usuário faz login na Plataforma.
Entre os navegadores (browser). Qual o navegador mais usado pelo total de visitas e quantas vezes ele foi usado?
Dica: para encontrar a resposta você só precisa usar a tabela platform_usage e não é necessário fazer uma JOIN
		Explicação: Você pode usar a query abaixo para descobrir quantas vezes cada navegador foi usado-- Qual o navegador mais usadoSELECT browser, COUNT(user_id)FROM platform_usageGROUP BY browser
		Alternativas:
			--> Chrome, 89 vezes
			--> Safari, 72 vezes
			--> Safari, 92 vezes
			--> Mozilla, 111 vezes
			--> Chrome, 68 vezes
	6. Temos duas tabelas, user_info (usuários) e platform_usage (uso da plataforma). A tabela de usuários (user_info) contém informações que temos sobre os usuários da nossa plataforma. A tabela de uso (plataform_usage) rastreia o uso da plataforma que registra o browser (navegador) e o device_type (tipo_de_dispositivo) sempre que um usuário faz login na Plataforma.
Quantos usuários entre 18 e 35 anos (campo age) visitaram a nossa plataforma em um dispositivo móvel (mobile) em 2019?
DICAS: 
1. Você precisa selecionar os usuários distintos  (DISTINCT) uma vez que o mesmo usuário pode acessar a plataforma diversas vezes, mas queremos os usuários únicos
2. Você seleciona o usuário pelo campo user_id
3. Você precisa fazer uma JOIN das duas tabelas para obter o resultado
		Explicação: 14 usuários entre 18 e 35 anos visitaram a nossa plataforma em um dispositivo móvel (mobile) em 2019. Para resolver esta questão precisamos criar uma join entre a tabela user_info (usuários) e platform_usage (uso), pois a idade (age) está na tabela user_info e o device_type (dispositivo), ou seja se é mobile ou desktop está na tabela platform_usage.Depois de fazermos a JOIN precisamos contar quantos usuários atendem os requisitos da selecão especificada que são:Idade (age): Entre  18 e 35Dispositivo (device_type): MobileA Query para chegar a este resultado é:SELECT COUNT(DISTINCT(ui.user_id))
-- Neste caso utilizamos DISTINCT por que o mesmo usuário pode ter acessado -- a plataforma diversas vezes e queremos selecionar os usuários únicosFROM user_info uiLEFT JOIN platform_usage pu ON ui.user_id=pu.user_idWHERE ui.age BETWEEN 18 AND 35AND pu.device_type = 'mobile'AND SUBSTRING(timestamp,1,4) = 2019
		Alternativas:
			--> 23
			--> 21
			--> 14
			--> 17
	7. Temos duas tabelas, user_info (usuários) e platform_usage (uso da plataforma). A tabela de usuários (user_info) contém informações que temos sobre os usuários da nossa plataforma. A tabela de uso (plataform_usage) rastreia o uso da plataforma que registra o browser (navegador) e o device_type (tipo_de_dispositivo) sempre que um usuário faz login na Plataforma.
Crie uma nova tabela que contenha apenas usuários que não tenham feito login na plataforma em 2019 mas fizeram o login nos outros anos.
		Explicação: Usuários com o user_id 10001 ao user_id 100074100001100002100003100004100005100006***100074Para chegar a resposta precisamos primeiro criar uma query com os usuários que acessaram a plataforma em 2019.Depois precisamos utilizar estes usuários em um subselect com um filtro dos usuários que não queremos incluir no nosso resultado. Desta forma conseguimos isso com a seguinte querySELECT ui.user_idFROM user_info uiLEFT JOIN platform_usage pu ON ui.user_id=pu.user_idWHERE ui.user_id NOT IN (SELECT ui.user_idFROM user_info uiLEFT JOIN platform_usage pu ON ui.user_id=pu.user_idWHERE pu.timestamp LIKE '2019%')
		Alternativas:
			-->  Usuários com o user_id 10001 ao user_id 100074100001100002100003100004100005100006***100074
			--> Usuários com o user_id 100033 ao user_id 100058100033100034100035100035100036100037***100058
			--> Usuários com o user_id 100091 ao user_id 100122100091100092100093100094100095100096***100122