DEV Community: nia

[Git] 5 Git Tips I Learned Today

nia — Sun, 02 Mar 2025 13:36:17 +0000

How to Stop Tracking Unnecessary Files in Git

While working on a team project, I noticed that the .idea/ folder kept being tracked by Git. This folder contains IDE-generated metadata and configuration files, which don't need to be pushed to the remote repository.

Even if .idea/ is listed in .gitignore, Git might still track it if the folder was already added before the .gitignore rule was applied.

To stop tracking the folder without deleting the actual files, run:

git rm --cached -r .idea/

This removes the folder from Git's index without affecting the local files.

After running the command, you should see output like this:

delete '.idea/fileName.xml'
...

Useful Things I Learned

Hidden folders like .idea/ won't show up with ls. Use ls -a to see all hidden files and folders.
The .gitignore file needs to be in the root directory of the project, not inside subfolders.
The /out folder, which stores compiled binaries or build artifacts, should also be excluded from the repository since these files can be regenerated from the source code.

Discard Local Changes and Sync with Another Branch

If you want to discard all local changes and sync your branch with the latest version of another branch, use:

git reset --hard origin/<branch_name>

This command completely resets your branch to match the remote branch, deleting any local changes.

Setting IntelliJ as the Default Merge Conflict Tool

If you're not comfortable resolving merge conflicts from the command line, IntelliJ can be used to visually resolve conflicts.

On Mac:

git config --global merge.tool intellij
git config --global mergetool.intellij.cmd "/Applications/IntelliJ IDEA.app/Contents/MacOS/idea diff $LOCAL $REMOTE $BASE $MERGED"
git config --global mergetool.prompt true

On Windows:

git config --global merge.tool intellij
git config --global mergetool.intellij.cmd "C:/Program Files/JetBrains/IntelliJ IDEA/bin/idea64.exe" diff $LOCAL $REMOTE $BASE $MERGED
git config --global mergetool.prompt true

To verify the configuration:

git config --global --list | grep merge.tool

When a conflict occurs, run:

git mergetool

IntelliJ will display the conflicting files. The left pane shows the local changes (HEAD), and the right pane shows the incoming changes from the remote branch. After resolving conflicts, click Accept and commit the merge result:

git add .
git commit -m "Resolved merge conflicts"

Viewing Git Diff in IntelliJ

To set IntelliJ as the default diff tool:

git config --global diff.tool intellij
git config --global difftool.intellij.cmd "/Applications/IntelliJ\ IDEA.app/Contents/MacOS/idea diff $LOCAL $REMOTE"

Use this command to see differences in IntelliJ:

git difftool

IntelliJ will open each file one by one, showing the changes side-by-side.

Connecting an Existing Local Branch to a Remote Branch

If you create a local branch without pushing it to the remote repository, you'll need to connect it manually.

First, push the branch to the remote repository:

git push origin <branch_name>

Then, set the upstream branch:

git branch --set-upstream-to=origin/<branch_name>

If the remote branch doesn't show up immediately, run:

git fetch

You can verify the remote branches with:

git branch -r

This ensures your local branch is properly tracking the remote branch.

[Git] How to Temporarily Save Changes with `git stash` While Switching Branches

nia — Sat, 01 Mar 2025 13:57:41 +0000

1st March 2025

While working on a team project, there might be times when you're in the middle of writing code on your feat branch but need to switch to the dev branch to check something. In this case, you can use the git stash command to temporarily save your work without committing unfinished changes.

Steps

Save your current changes:

   git stash push -m "Description of your work in progress"

Switch to the dev branch:

   git switch dev

After checking what you need on dev, switch back to your feat branch:

   git switch feat

Restore your stashed changes:

   git stash pop

The git stash pop command applies the latest stash and automatically removes it from the stash list. If you want to apply the stash without deleting it, use:

git stash apply

What If You Have Multiple Stashes?

If you've stashed your work multiple times, you can see the list of saved stashes:

git stash list

Example output:

stash@{0}: On branch_name: Work in progress 2
stash@{1}: On branch_name: Work in progress 1

To apply a specific stash, use:

git stash pop stash@{0}

I haven't used multiple stashes much yet, but if I do, I'll come back and explain the process in more detail.

How to Use Vi Editor - Beginner's Guide

nia — Sat, 01 Mar 2025 06:39:43 +0000

Day 11 - 22th Jan 2025

Quick Summary: Create file, edit and save the file

vi <filename.txt > - if the file doesn't exist, this makes file with file name.
i- enter insert mode.
Write your contents in the file.
esc
:wq
enter!

Most Important Concepts for Using `vi`

1. Modes in `vi`

Command Mode: Default mode when you open vi. You can navigate and execute commands here.
Insert Mode: Used for editing text. Enter this mode by pressing i.
Escape Mode: Press Esc to return to Command mode.

2. Basic Navigation

h: Move left
l: Move right
k: Move up
j: Move down
Ctrl + d: Scroll down
Ctrl + u: Scroll up

3. Editing

i: Insert before the cursor.
a: Append after the cursor.
o: Open a new line below the cursor.
dd: Delete the current line.
x: Delete the character under the cursor.
u: Undo the last action.
Ctrl + r: Redo the last undone action.

4. Saving and Exiting

:w: Save changes.
:q: Quit.
:wq: Save and quit.
:q!: Quit without saving.

5. Search

/text: Search forward for "text".
?text: Search backward for "text".
n: Repeat the search in the same direction.
N: Repeat the search in the opposite direction.

6. Copy, Paste, and Cut

yy: Copy (yank) the current line.
p: Paste the copied text after the cursor.
dd: Cut the current line.

7. Replace Text

:s/old/new/: Replace the first occurrence of "old" with "new" on the current line.
:s/old/new/g: Replace all occurrences of "old" with "new" on the current line.
:%s/old/new/g: Replace all occurrences in the entire file.

Quick Tips

Always press Esc to ensure you're in Command mode before executing commands.
Practice basic navigation and saving before diving into advanced features.
To exit quickly without saving (if you're stuck), press Esc and type :q!.

[SQL] Using Session Variables in MySQL

nia — Fri, 28 Feb 2025 12:13:05 +0000

26th Feb 2025 Wednesday

What is a Session Variable?

Session variables, also known as user-defined variables, are temporary variables that persist for the duration of a database session — the period during which a user remains connected to the database. These variables are only accessible within the current session and are automatically discarded once the session ends. Unlike table data, session variables are not permanently stored in the database.

Declaring and Assigning Variables

You can declare and assign values to session variables using the SET statement:

SET @my_var = 100;
SELECT @my_var;  -- Retrieves the value of the variable

Alternatively, the SELECT ... INTO syntax allows you to assign the result of a query directly to a variable:

SELECT COUNT(*) INTO @total_employees FROM employees;
SELECT @total_employees;  -- Displays the total number of employees

Using Session Variables in Queries

Session variables can be applied in queries to perform dynamic calculations. For example, to calculate salary increases:

SET @increase_rate = 1.1;

SELECT name, salary, salary * @increase_rate AS new_salary
FROM employees;

Here, the @increase_rate variable stores the salary increase rate, which is applied to each employee’s salary in the query result.

Updating Data with Session Variables

Session variables can also be used in UPDATE statements to set dynamic conditions. For instance, to increase the salaries of employees earning below the average salary:

SET @avg_salary = (SELECT AVG(salary) FROM employees);

UPDATE employees
SET salary = salary * 1.05
WHERE salary < @avg_salary;

This query first calculates the average salary and stores it in @avg_salary, then applies a 5% salary increase to employees earning below that amount.

Inserting Data with Session Variables

You can insert data into another table using session variables combined with INSERT INTO ... SELECT statements. For example, to store information about the highest-paid employees in a separate table:

SET @max_salary = (SELECT MAX(salary) FROM employees);

INSERT INTO high_paid_employees (name, salary)
SELECT name, salary
FROM employees
WHERE salary = @max_salary;

This method inserts data into the target table by selecting matching rows from the source table, without needing explicit VALUES clauses.

Session variables provide a flexible way to store temporary data and simplify query logic, especially in scenarios involving calculations or conditional updates.

[SQL] Essential Terms in the Relational Database Model and Characteristics of a relation

nia — Mon, 24 Feb 2025 21:51:31 +0000

Feb 24rd 2025 - part 1

Before starting the post...

Today I learned about terms used when dealing with relational database models and the key characteristics of a relation. I’m the type of person who thinks using a precise word is important, so I’m glad that this is a trait that’s considered desirable for a developer. 😄 It’s 19:33 currently and I’m off to the gym after writing this post!

Why understanding the correct terms is important

Every field has its vocabulary that defines key concepts, and so does the RDBMS. It is crucial to understand the basic terms to get a deeper understanding of RDBMS. They are the building blocks for grasping complex concepts and applying them correctly.

Defining essential terms used in relational database model

A relational database model is a way of organizing and managing data in a database using a structure based on relations and their relationships.

A relationship refers to an association between two or more tables based on common attributes. Relationships are established using foreign keys, which link a column in one table to the primary key of another or the same table.

A relation schema is the structure of the relation. It defines the relation's design, including the names of the columns, the data types for each column, and constraints. But it does not include the actual data.

A Relation instance refers to the specific data currently in that relation at a given time. It represents the actual, concrete data that exists in the relation.

A relation is what we commonly know as a table. Relation includes its structure (schema) and the current data (tuples) stored at a given time. A relation combines the schema (the structure) and the instance (the current set of data).

Intension and Schema are essentially the same. It's a structure of the relation, but not the data itself.
The Extension, however, includes the actual tuples in the table. A set of tuples is the extension of a relation.

Attributes are columns of a relation.
Constraints of a relation are rules enforced to ensure the data's accuracy, consistency, and integrity. They define what is allowed or not allowed in the relation.

Domain refers to the set of valid values an attribute in a relation can hold. It defines the type, range, and constraints for the data that can be stored in that column.

Data typeis a specific category of data, like INT or VARCHAR. But domain is a broader concept that includes data type plus any additional constraints (NOT NULL, UNIQUE), and allowed range of values (ENUM sets).

Degree refers to the number of attributes in a relation. It measures how many fields each tuple contains.

A tuple is a single row in a relation. It's a collection of values that describes a single entity in the relation.

Cardinality refers to the number of tuples in a relation at a given time. If the relation has 50 tuples, the cardinality of that relation is 50. If a relation is empty, its cardinality is 0.

Why understanding the characteristics of a relation is important

To ensure consistency, integrity, and efficiency, a relation must follow specific characteristics that define its structure and behavior. These characteristics establish rules for how data is stored, retrieved, and maintained within the database.

Characteristics of a relation

Each attribute(column) must have a unique name. No two attributes have the same name.
Each tuple is unique. A relation must not have duplicate tuples. The primary key ensures its uniqueness.
Entries in a column are from the same domain. This means that all values in a particular column must follow a consistent set of rules, like data type, constraints and format (an email must contain @).
The order of rows and columns does not matter.
The rows and columns in a relation have no inherent order. You can rearrange them without changing the meaning of the data. A relation is a set, not a list, so the focus is on the data itself, not its position.
Values in each column must be atomic. This means a single cell should only contain one value.
Each attribute must have a defined domain (data type and constraints).
NULL values are allowed unless restricted. NULL means missing or unknown data.

[SQL] - Inner Join, Left Join, Non-Equi Join, Self Join

nia — Sun, 23 Feb 2025 12:43:03 +0000

[Day 26] Reviewing Incorrect SQL problems.

Before diving in, I’ll start by explaining the purpose of using JOINs.

Why Use the JOIN in SQL

The JOIN combines information from different tables into a single dataset to retrieve the desired data.

For example, imagine one table contains "customer information" (e.g., name, customer ID), and another table holds "order information" (e.g., order ID, customer ID, order date). If I want to know "which customers placed orders and when," how would I approach this?

I can connect the customer table and the order table using the common key, customer_id, through a JOIN, allowing me to view the customer’s name and order date together in one result. If the customer table is called the customer and the order table is order, use customer.customer_id = order.customer_id retrieves only the rows where customer_id matches in both tables. This effectively returns the intersection of the two tables—only the overlapping data.

Problem 01

You need to generate a report of each employee’s salary grade, which is stored in the Job_Grades table. The report should include the employee’s job title, department name, hire date, salary, and salary grade.

The employees table does not contain job_title.
- employees e JOIN jobs j ON e.job_id = j.job_id: I use the employee’s job_id to fetch the job_title from the jobs table.
The employees table does not include department_name.

- `employees e JOIN departments d ON e.department_id = d.department_id`: I use the employee’s `department_id` to retrieve the `department_name` from the `departments` table.

I needed to consider how to use the lowest_sal and highest_sal fields in the job_grades table to assign grades based on an employee’s salary. I couldn’t rely on the typical equi join (joining with =), which I’ve been accustomed to using.

- I realized that I need to fetch only the rows where the `salary` falls between `lowest_sal` and `highest_sal`. If I want to include employees whose salaries fall outside any defined range, I should use a `LEFT JOIN`. Using a plain `JOIN` (which defaults to `INNER JOIN`) would exclude employees whose salaries don’t fit any range, as those rows with NULL values would be omitted from the output.

```sql
SELECT e.last_name, j.job_title, d.department_name, e.hire_date, e.salary, jg.grade_level
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN jobs j ON e.job_id = j.job_id
LEFT JOIN job_grades jg ON e.salary BETWEEN jg.lowest_sal AND jg.highest_sal;

```

This problem initially confused me because I had primarily used equi joins to solve problems. It turned out to be a valuable opportunity to learn more about non-equi joins.

Problem 02

Some employees report to other employees as their managers (supervisors). Query the employees’ manager IDs, manager names, employee names, and employee IDs. If an employee does not report to a manager, you must still include that information in the output, and the manager’s name should be displayed in uppercase.

SELECT
    e.employee_id AS employee_id,
    e.first_name AS employee_name,
    e.manager_id AS manager_id,
    UPPER(m.first_name) AS manager_name
FROM
    employees e
LEFT JOIN
    employees m ON e.manager_id = m.employee_id;

Since the problem specifies, “If there are employees without a reporting manager, include that information as well,” I need to include employees with manager_id as NULL in the results. Using a plain JOIN (which defaults to INNER JOIN) would exclude employees without managers, as it only returns matching rows. Therefore, I must use LEFT JOIN to ensure all employees are included.

Here’s another valid version:

SELECT e1.employee_id AS employee_id,
       e1.last_name AS employee_name,
       e2.employee_id AS manager_id,
       e2.last_name AS manager_name
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;

Problem 02

Output the department name and employee count for departments with five or more employees, sorted by employee count in descending order.

SELECT d.department_name, COUNT(e.employee_id)
FROM departments d, employees e
WHERE e.department_id = d.department_id
GROUP BY d.department_name
HAVING COUNT(e.employee_id) >= 5;

- ANSI Standard Version:

SELECT d.department_name, COUNT(e.employee_id)
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name
HAVING COUNT(e.employee_id) >= 5;

I initially got this wrong because I performed the grouping before joining the tables. I need to join the tables first to establish the connection, then group and filter the results.

Problem 03

Query the total number of employees working as managers.

A Quick review of primary key and foreign key

Databases store data across multiple tables. To connect data between tables, I use a common value called a “key,” which can be thought of as a column in a table. A primary key (Primary Key) uniquely identifies each row (record) in a table. A foreign key (Foreign Key) is a column in one table that references the primary key in another table (or the same table). “Referencing a key” means that a foreign key in one table points to or connects to a primary key in another (or the same) table.

[EER Diagram of the database]

I can use MySQL Workbench’s Reverse Engineer feature to create an EER diagram and examine the relationships between tables. By observing the four lines connected to the employees table in the diagram, I can identify which columns in the employees table are linked to columns in other tables.

Here, the manager_id I need isn’t connected to any other table’s key. Instead, manager_id references the employee_id within the employees table itself. Therefore, I need to use a self-join.

Every employee has an employee_id, and managers do too. To query employees working as managers, I can write:

SELECT COUNT(DISTINCT e1.employee_id) AS manager_count
FROM employees e1
JOIN employees e2 ON e1.employee_id = e2.manager_id;

e1 is the alias for the manager table, and e2 is the alias for the employee table. When joining the manager table (e1) with the employee table (e2), e1.employee_id = e2.manager_id means it retrieves only the rows where the manager table’s employee_id matches the employee table’s manager_id.

Here’s another query that produces the same result:

SELECT COUNT(DISTINCT e1.employee_id) AS manager_count
FROM employees e2
JOIN employees e1 ON e1.employee_id = e2.manager_id;

The difference lies in the FROM clause and the JOIN employees e1 part. The logical relationship remains the same, so the results are identical. However, conventionally, placing the “subject (manager)” in the FROM clause and the “dependent (employee)” in the JOIN clause is more readable and aligns with standard practices.

Conversely, if the logical relationship changes, the query results will differ. For example:

SELECT COUNT(DISTINCT e1.employee_id) AS num_of_manager
FROM employees e2
JOIN employees e1 ON e2.employee_id = e1.employee_id;

This query implies that e2 (employee) references e1 (manager). “Reference” means a column in one table points to or connects to a column in another (or the same) table. Here, manager_id (foreign key) references employee_id (primary key) in the employees table, forming a “self-referencing relationship.”

A primary key uniquely identifies each row in a table and does not reference data in other tables—it is only referenced by foreign keys. A primary key does not reference a foreign key; rather, a foreign key references a primary key. In the EER diagram, the self-referencing relationship between employee_id and manager_id confirms this connection.

I used to interpret the JOIN as simply “fetching rows where they match,” but this review has helped me gain a clearer understanding of 'referencing' in joining tables.

DEV Community: nia

[Git] 5 Git Tips I Learned Today

How to Stop Tracking Unnecessary Files in Git

Useful Things I Learned

Discard Local Changes and Sync with Another Branch

Setting IntelliJ as the Default Merge Conflict Tool

Viewing Git Diff in IntelliJ

Connecting an Existing Local Branch to a Remote Branch

[Git] How to Temporarily Save Changes with `git stash` While Switching Branches

Steps

What If You Have Multiple Stashes?

How to Use Vi Editor - Beginner's Guide

Quick Summary: Create file, edit and save the file

Most Important Concepts for Using vi

1. Modes in vi

2. Basic Navigation

3. Editing

4. Saving and Exiting

5. Search

6. Copy, Paste, and Cut

7. Replace Text

Quick Tips

[SQL] Using Session Variables in MySQL

What is a Session Variable?

Declaring and Assigning Variables

Using Session Variables in Queries

Updating Data with Session Variables

Inserting Data with Session Variables

[SQL] Essential Terms in the Relational Database Model and Characteristics of a relation

Before starting the post...

Why understanding the correct terms is important

Defining essential terms used in relational database model

Why understanding the characteristics of a relation is important

Characteristics of a relation

[SQL] - Inner Join, Left Join, Non-Equi Join, Self Join

Why Use the JOIN in SQL

Problem 01

Problem 02

Problem 02

Problem 03

A Quick review of primary key and foreign key

Most Important Concepts for Using `vi`

1. Modes in `vi`