Rafael Tedesco rafaeltedesco

🏠

Working from home

Software & Data Specialist @actdigital, Helping Global Companies Build Scalable Software and Data Solutions

rafaeltedesco / fix_tz.sql

Last active August 16, 2023 20:10

Fix MySQL timezone

	SELECT * FROM mysql.time_zone_name;

	SET GLOBAL time_zone = 'America/Sao_Paulo';

	SELECT @@global.time_zone;

rafaeltedesco / revert.sh

Created March 3, 2023 12:45

Revert Last commit and clear history

	git rebase -i <hash do commit>~
	git push -f

rafaeltedesco / showAllPro.js

Created March 3, 2023 12:44

Show all methods from a Prototype

	// Where String.prototype could be replaced
	Object.getOwnPropertyNames(String.prototype)

rafaeltedesco / over_partition.sql

Created November 24, 2022 12:44

'Over partition' example using 'row_number' to present a cumulative view of movies that each actor have been participated

	USE sakila;

	SELECT
	CONCAT(tbl_actor.last_name,
	' ',
	tbl_actor.first_name) AS actor_name,
	tbl_film.title AS movie,
	ROW_NUMBER() OVER(PARTITION BY CONCAT(tbl_actor.last_name,
	' ',
	tbl_actor.first_name)) AS total_movies

rafaeltedesco / remove_manual_id_trigger.sql

Created November 22, 2022 12:25

Guarantee that user cannot insert a manual id on a auto_increment field

	DROP TRIGGER IF EXISTS sakila.bf_insert_actor;

	DELIMITER $$

	CREATE TRIGGER bf_insert_actor
	BEFORE INSERT ON sakila.actor
	FOR EACH ROW
	BEGIN
	IF NEW.actor_id IS NOT NULL THEN
	SET NEW.actor_id = null;

rafaeltedesco / 01_select_case.sql

Created November 21, 2022 16:42

SELECT CASE EXAMPLE 01

	SELECT * FROM sakila.customer;

	SELECT
	(CASE
	WHEN active = 1 THEN 'ATIVO'
	ELSE 'INATIVO'
	END) AS user_active,
	CONCAT(first_name, ' ', last_name) AS full_name,
	YEAR(create_date) AS joined_at
	FROM

rafaeltedesco / change_value_using_case.sql

Created November 17, 2022 16:45

Change row value When match a specific condition using CASE in mySQL

	SELECT
	new_name,
	last_name,
	CONCAT(new_name, ' ', last_name) AS full_name
	FROM
	(SELECT
	(CASE
	WHEN first_name = 'NICK' THEN 'LINK'
	ELSE first_name
	END) AS new_name,

rafaeltedesco / create_table_from_another.sql

Last active April 28, 2023 20:47

Create TABLE based on Another TABLE filtered

	-- CREATE A TABLE BASED ON ANOTHER

	CREATE DATABASE people_by_name;

	CREATE TABLE people_by_name.people_with_p
	AS SELECT * FROM main_db.people WHERE first_name LIKE 'p%';