Code Snippets

SQL

Basic workflow

Create DATABASE

CREATE DATABASE dashboard_db;

Create table

CREATE TABLE stats (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    value INT
);

Put values

INSERT INTO stats (name, value) VALUES
('Users', 120),
('Sales', 75),
('Visitors', 300);

Look and find data

USE dashboard_db;
SHOW TABLES;
DESCRIBE stats; -- Shows stats table summary
EXIT

Nux

Watch protected system directories

sudo nautilus # or dolphin, thunar

Open in the preferred application for file

xdg-open filename

Current wokring directory

pwd

Rename all files sequentially

The example renames .jpg files. Modify as per your need.

ls -v | cat -n | while read n f; do mv -n "$f" "$n.jpg"; done

To start numbering at certain number use the following code

num=0; for i in *; do mv "$i" "$(printf '%04d' $num).${i#*.}"; ((num++)); done

For more on this, look here

Rename file names (“_” to “-”

Modify as per need

rename 's/\_/-/' *

Replace space with hyphen

for file in *' '*
do
  mv -- "$file" "${file// /-}"
done

Rename to lowercase

rename 'y/A-Z/a-z/' *

Count files

find . -type f | wc -l

Replace new line with double new line

`sed -i 's/$/\n/' file.txt`

Convert all mp4 to mp3

#!/bin/bash
local o=$IFS
IFS=$(echo -en "\n\b")

MP4FILE=$(ls . |grep .mp4)
for filename in $MP4FILE
do
name="${filename%.*}"
ffmpeg -i ./$filename -b:a 192K -vn ./$name.mp3
done

IFS=o

Latex

Long Comment

\iffalse
This is a long block of text that serves as a comment. It will be completely ignored by the LaTeX compiler and will not appear in the final output. You can write as many paragraphs as you like here. This is an excellent alternative to using the `comment` package.
\fi

Restart Page-numbering

\newpage
\setcounter{page}{1}

Push contents to bottom/right of page

\vfill contents to push to bottom
\hfill contents to push to right

R

Quit R from terminal in one go

q("no")

or quit(save = "no")

Git

Undoing commit

git reset --soft HEAD~N

Undoes the last N commits, but keeps the changes in the staging area.

git reset --mixed HEAD~N

Undoes the last N commits, and unstages the changes, leaving them in the working directory.

git reset --hard HEAD~N

Undoes the last N commits and discards all changes made in those commits from the working directory. Use with caution as this is a destructive operation.

Rename files with git

git mv old_file new_file

Pull if remote is ahead

git pull --rebase

Revert all tracked files in the working directory

git checkout .

To clean untracked files as well:

git checkout .
git clean -fd

Repository-specific confiuguration

git config --local user.name 'Your Name'
git config --local user.email 'your.work.email@company.com'

Global confiuguration

`git config --global user.name 'Your Name'`
git config --global user.email 'your.work.email@company.com'

Quarto

Render all `qmd` files in the directory

quarto render

Excel / Google Sheet

Mode for ordinal data

=INDEX(UNIQUE(I2:I107), MATCH(MAX(COUNTIF(I2:I107, UNIQUE(I2:I107))), COUNTIF(I2:I107, UNIQUE(I2:I107)), 0))

Median for ordinal data

=INDEX({"Strongly Disagree","Disagree","Neither Agree nor Disagree","Agree","Strongly Agree"}, MEDIAN(FILTER(MATCH(I1:I106, {"Strongly Disagree","Disagree","Neither Agree nor Disagree","Agree","Strongly Agree"}, 0), ISNUMBER(MATCH(I1:I106, {"Strongly Disagree","Disagree","Neither Agree nor Disagree","Agree","Strongly Agree"}, 0)))))

SQL