todas las querys de prueba.txt

#creamos conexión
USE stg_visa;
CREATE USER uservs@'localhost' IDENTIFIED BY 'uservs123';
GRANT ALL ON stg_visa.* TO uservs@'localhost';
;

#probamos algunas tablas test
DROP	TABLE stg_visa.tabla_test
DROP	TABLE stg_visa.tabla_demostracion4

#testeamos errores de conexión por tiempo
SHOW SESSION VARIABLES LIKE 'wait_timeout';
set global max_allowed_packet=67108864;

#vemos las conexiones que kick
SHOW GLOBAL STATUS LIKE  'Aborted_connects';
SET GLOBAL connect_timeout = 10;

#consultas test
SELECT COUNT('CASE_NUMBER') FROM tabla_demostracion40
SELECT COUNT('CASE_NUMBER') FROM tabla_test

DROP TABLE stg_visa.tabla_test

SELECT * FROM tabla_test3 WHERE SOC_CODE IS NULL
SELECT * FROM stg_visa.stging_workplace
SELECT COUNT('CASE_NUMBER') FROM stg_visa.stging_workplace 
SELECT COUNT('id') FROM stg_visa.tabla_test5
SELECT COUNT('id') FROM stg_visa.stging_workplace WHERE 'CASE_NUMBER' IS NOT No
DROP TABLE stg_visa.tabla_us
#SELECT * FROM stg_visa.stging_workplace CROSS APPLY STRING_SPLIT(WORKSITE, ','); 
#DROP table tabla_test5
#pruebas fallidas
SELECT DISTINCT `WORKSITE` SUBSTRING(`WORKSITE`, 1, CHARINDEX(',', `WORKSITE`) - 1) as `WORKSITEC` FROM stg_visa.tabla_test ,
                RTRIM(LTRIM(REPLACE(REPLACE('WORKSITE',SUBSTRING('WORKSITE' , 1, CHARINDEX(',', 'WORKSITE') - 1),''),
                REVERSE( LEFT( REVERSE('WORKSITE'), CHARINDEX(',', REVERSE('WORKSITE'))-1 ) ),'')))as 'WORKSITE'
                FROM stg_visa.tabla_test
#pruebas
SELECT SUBSTRING(WORKSITE, 1, 8) as WORKSITEC FROM stg_visa.tabla_test
SELECT WORKSITE, SUBSTRING(WORKSITE, 1, SUBSTRING_INDEX(WORKSITE, ',', -1) as WORKSITEC FROM stg_visa.tabla_test 
SELECT WORKSITE, SUBSTRING_INDEX(WORKSITE, ',', +1) AS CITY, SUBSTRING_INDEX(WORKSITE, ',', -1) AS STATE FROM stg_visa.tabla_test WHERE WORKSITE LIKE '%,%,%'
SELECT WORKSITE_CITY, WORKSITE_STATE_ABB, WORKSITE_STATE_FULL, WORKSITE FROM stg_visa.tabla_test WHERE WORKSITE LIKE '%,%,%'}}}}
DROP TABLE stg_visa.tabla_test

#Creamos tablas de ciudades de eeuu
CREATE TABLE tabla_US(c
#pruebas
SELECT * FROM stg_visa.tabla_us WHERE AK = 'NY'

#creamos database de dw visa
CREATE DATABASE dw_visa;
USE dw_visa;
GRANT ALL ON dw_visa.* TO uservs@'localhost';

#+pruebas
SELECT FULL_TIME_POSITION, JOB_TITLE FROM stg_visa.tabla_test WHERE JOB_TITLE = 'QA ANALYST'

#siguen las consultas...
SELECT * FROM dw_visa.dw_rel1
SELECT * FROM dw_visa.dw_soc
DROP TABLE stg_visa.stging_tabla_test
SELECT YEAR, COUNT('YEAR') FROM stg_visa.stging_tabla_test
DROP TABLE dw_visa.dw_worksite
DROP TABLE dw_visa.dw

#me comi los sueldos en varchar
SELECT MAX(PREVAILING_WAGE) FROM stg_visa.tabla_test
SELECT MAX(sueldo) FROM dw_visa.dw_proc
SELECT MAX(PREVAILING_WAGE) FROM stg_visa.stging_tabla_test
SELECT MAX(PREVAILING_WAGE) FROM stg_visa.stg_baseid

#consulta prueba final
SELECT dw_proc.case_id, dw_proc.estado, dw_proc.sueldo, COUNT(*)
FROM dw_visa.dw_proc 
LEFT JOIN dw_rel1 ON dw_proc.case_id = dw_rel1.c_id
GROUP BY dw_proc.case_id, dw_proc.estado, dw_proc.sueldo
ORDER BY sueldo DESC;