Forem: marco cabrera

Understanding SQL Queries: A Beginner’s Guide pt1

marco cabrera — Tue, 08 Oct 2024 17:37:04 +0000

Target Audience: New developers who might not be very familiar with SQL.

Introduction

Welcome to this beginner’s guide to SQL! If you’re new to databases and SQL (Structured Query Language), you’re in the right place. We'll break down the anatomy of an SQL query and explore how to craft basic SELECT operations.

The Anatomy of an SQL Query

An SQL query is composed of several key components, each serving a specific purpose in data retrieval or manipulation. Let’s dissect these components:

1. Actions (`SELECT`, `INSERT`, `DELETE`, `UPDATE`)

SELECT: Retrieves data from the database.
INSERT: Adds new records to a table.
DELETE: Removes records from a table.
UPDATE: Modifies existing records in a table.

These actions specify the operation you wish to perform and can target one or many rows.

2. Columns (`[column]`)

Specifies the columns you want to retrieve or manipulate.
Can include data transformations or aggregate functions (e.g., SUM, COUNT).
Allows for calculations or modifications to the data on-the-fly.

3. `FROM` Clause

Marks the end of the column list and precedes the target table.
Indicates which table(s) to retrieve or manipulate data from.

4. Table (`[table]`)

The specific table you wish to query in the database.

5. `WHERE` Clause

Indicates that you’re specifying conditions for data retrieval or manipulation.
Used to filter records based on specific criteria.

6. Search Criteria (`[search criteria]`)

The conditions that follow the WHERE clause.
Filters data so that only records meeting the criteria are affected.
Without this, the query could return or affect all records in the table.

Understanding Data Types

A data type defines the kind of value a column can hold, specifying the data’s format and ensuring integrity. For example, you can’t store 'hello world' (a string) in an INT (integer) column.

Common SQL Data Types:

INT: Whole numbers (e.g., 1, 42, 1000).
VARCHAR(n): Text strings up to n characters (e.g., 'Hello', 'Database').
DECIMAL(p,s): Decimal numbers with precision p and scale s (e.g., 123.45).
DATE: Dates (e.g., '2023-10-15').
DATETIME: Date and time values (e.g., '2023-10-15 14:30:00').

Why Data Types Matter:

Data Integrity: Prevents invalid data entry.
Optimization: Enhances storage efficiency and query performance.
Validation: Automatically checks data compatibility during insertion.

Crafting SQL Queries

Let's see how you'd go about writing SELECT statements. This is an opinionated approach based on what I've experienced in the industry.

Example Database: Dinosaurs and People

We'll use a simple example with a Dinosaur table.

-- Drop the tables if they already exist to avoid errors
DROP TABLE IF EXISTS Dinosaur;

-- Create the Dinosaur table
CREATE TABLE Dinosaur (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    height DECIMAL(5,2), -- Height in meters
    weight DECIMAL(7,2), -- Weight in kilograms
    time_period_alive_in VARCHAR(100)
);

-- Insert fake data into the Dinosaur table
INSERT INTO Dinosaur (id, name, height, weight, time_period_alive_in) VALUES
(1, 'Tyrannosaurus Rex', 12.0, 8000.00, 'Late Cretaceous'),
(2, 'Triceratops', 3.0, 6000.00, 'Late Cretaceous'),
(3, 'Stegosaurus', 4.0, 5500.00, 'Late Jurassic'),
(4, 'Brachiosaurus', 13.0, 62000.00, 'Late Jurassic'),
(5, 'Velociraptor', 0.5, 15.00, 'Late Cretaceous');

Scenario: Retrieving Dinosaurs from the 'Late Cretaceous' Period

Let's walk through a practical example:

"Get all the dinosaurs that lived in the 'Late Cretaceous' period."

Step 1: Exploring the Data

Before writing a complex query, it's helpful to see what's in the table. You can start with:

SELECT * FROM Dinosaur;

This simple query returns all the data from the Dinosaur table.

Note: Be cautious when working with large datasets, as retrieving all rows can be time-consuming. In such cases, you can limit the number of returned rows using clauses like LIMIT or TOP. For example:

In MySQL and PostgreSQL:

  SELECT * FROM Dinosaur LIMIT 500;

In SQL Server:

  SELECT TOP 500 * FROM Dinosaur;

The asterisk (*) tells the SQL engine to return all columns from the target table.

Step 2: Analyzing the Data

Reviewing the output helps you understand the structure of the table, the columns available, and the kind of data stored.

Step 3: Applying a Filter with `WHERE`

Now that we know the table structure, we can write a query to retrieve only the dinosaurs from the 'Late Cretaceous' period:

SELECT * FROM Dinosaur
WHERE time_period_alive_in = 'Late Cretaceous';

Key Points:

WHERE Clause: Filters the results to include only records where the condition is true.
Search Criteria: time_period_alive_in = 'Late Cretaceous' is our condition.

Tip on Quotes:

Be mindful of using single quotes (') around string literals in SQL.
Using incorrect quotes (e.g., double quotes " or backticks `) can cause errors.
Stick to single quotes for strings to ensure your query runs correctly.

Conclusion

Understanding the basic components of an SQL query is crucial for any developer working with databases. By breaking down queries into their fundamental parts, you can:

Retrieve Data: Use SELECT with specified columns and conditions.
Insert Data: Add new records using INSERT.
Update Data: Modify existing records with UPDATE.
Delete Data: Remove records using DELETE.

Always start by understanding your data — explore the tables and get familiar with the columns and types of data they hold. This foundation will make writing and debugging queries much more straightforward. In this first part of our series, we've focused on SELECT statements. Stay tuned for upcoming posts where we'll dive into INSERT, UPDATE, and DELETE operations.

Happy querying!

So you think you need _Lodash?

marco cabrera — Sun, 11 Feb 2024 04:44:51 +0000

Intro

Recently, I got caught up in a discussion about Lodash and whether we really need to lean on third-party libraries as much as we do in the JavaScript world. It's clear we love our shortcuts and helpers, but it's also incredible to see how JavaScript itself has grown, turning from a basic scripting language into something much more powerful, complete with its own advanced features like array and map methods. This article is about putting Lodash side by side with vanilla JavaScript to see how they compare today. Feel free to follow along on a browser console for the Vanilla JS potion of the blog.

Deep Copy

Let's start with the concept of deep copying in JavaScript. It's a common necessity across programming tasks.

Lodash

Lodash provides a _.cloneDeep method to perform a deep copy:

const _ = require('lodash');

let obj = { a: 1, b: { c: 2 } };
let deepCopiedObj = _.cloneDeep(obj);

console.log(deepCopiedObj); // => { a: 1, b: { c: 2 } }

Vanilla Javascript

Vanilla JavaScript offers a couple of ways to achieve deep copying, each with its own considerations. Here's a look at two prevalent methods.

With modern JavaScript, you can achieve a deep copy using the structuredClone method.

let obj = { a: 1, b: { c: 2 } };
let deepCopiedObj = structuredClone(obj);

console.log(deepCopiedObj); // => { a: 1, b: { c: 2 } }

Vanilla JavaScript

Another way is to use the JSON.parse and JSON.stringify methods.

let obj = { a: 1, b: { c: 2 } };
let deepCopiedObj = JSON.parse(JSON.stringify(obj));

console.log(deepCopiedObj); // => { a: 1, b: { c: 2 } }

Note: This method works well for objects containing only primitive values, arrays, and plain objects but might not be suitable for objects with methods, circular references, or special types like Date, Set, Map, etc. I honestly hate seeing this, but I know it's prevalent in many code bases i and can attest to it working, so I have included it in this article.

Difference

Finding the difference between two arrays is a common task, where we identify elements present in one array but not in another.

Lodash


_.difference([2, 1], [2, 3]); // => [1]

Vanilla JavaScript

let firstArr = [1,2,3];
let secondArr = [2,4];
let difference = firstArr.filter(x => !secondArr.includes(x));
console.log(difference); // => [1, 3]

DifferenceBy

DifferenceBy introduces a twist to finding array differences by allowing comparisons based on a transformation function.

Lodash


_.differenceBy([2.1, 1.2], [2.3, 3.4], Math.floor); // => [1.2]

Vanilla JavaScript

let firstArr = [1,2,3, 4.2];
let secondArr = [2,4];

// Various approaches but they get more funtional
let difference = firstArr.filter(x => !secondArr.includes(Math.floor(x)));
console.log("difference:" + difference); // => [1, 3]

const diffBy = element => Math.floor(element);
let differenceWithFunc = firstArr.filter(element => !secondArr.includes(diffBy(element)));
console.log("differenceWithFunc:" + differenceWithFunc); // => [1, 3]

const transform = element => Math.floor(element);
const notIncluded = (element, transformFunc, secondArray) => !secondArray.includes(transformFunc(element));
let differenceEncapsulationVersion = firstArr.filter(element => notIncluded(element, transform, secondArr));

console.log("differenceEncapsulationVersion:" + differenceEncapsulationVersion); // => [1, 3]

Union

Union operations merge multiple arrays into a single array with unique elements. This common functionality showcases another aspect of array manipulation.

Lodash

_.union([2], [1, 2]); // => [2, 1]

Vanilla JavaScript

Using ES6's Set and spread operator:

let firstArr = [2];
let secondArr = [1,2];
let thirdArr = [2,3,4];
let union = new Set([...firstArr, ...secondArr, ...thirdArr]);
console.log([...union]); // => [2, 1, 3, 4]

Zip

Zipping arrays is a powerful method to combine arrays element-wise.

Lodash

_.zip(['a', 'b'], [1, 2], [true, false]); // => [['a', 1, true], ['b', 2, false]]

Vanilla JavaScript

Using Array.from, we can zip arrays of varying lengths:

function zip(...arrays) {
    const maxLength = Math.max(...arrays.map(arr => arr.length));
    return Array.from({ length: maxLength }, (_, index) => arrays.map(array => array[index]));
}

let abc = ['a', 'b', 'c'];
let num = [1, 2, 3];
let bool = [true, false, true];

let zipped = zip(abc, num, bool);
console.log(zipped);

A more functional approach:

const zip = (...arrays) => arrays[0].map((_, i) => arrays.map(array => array[i]));

let abc = ['a', 'b', 'c'];
let num = [1, 2, 3];
let bool = [true, false, true];

let zipped = zip(abc, num, bool);
console.log(zipped); // => [['a', 1, true], ['b', 2, false], ['c', 3, true]]

Currying

Currying transforms a function, so it can be invoked piecewise, with each call returning a new function that accepts the next argument.

Lodash

var abc = function(a, b, c) {
    return [a, b, c];
};

var curried = _.curry(abc);

curried(1)(2)(3); // => [1, 2, 3]
curried(1, 2)(3); // => [1, 2, 3]
curried(1, 2, 3); // => [1, 2, 3]
curried(1)(_, 3)(2); // => [1, 3, 2]

Vanilla JavaScript

implementing currying with support for placeholders:

const _ = Symbol('_');

function curry(fn) {
    return function currier(...args) {
        const isComplete = args.length >= fn.length && !args.slice(0, fn.length).includes(_);
        if (isComplete) {
            return fn.apply(this, args);
        } else {
            return function(...newArgs) {
                const combinedArgs = args.reduce((acc, arg) => {
                    if (arg === _ && newArgs.length) acc.push(newArgs.shift());
                    else acc.push(arg);
                    return acc;
                }, []);
                return currier(...combinedArgs, ...newArgs);
            };
        }
    };
}

const abc = (a, b, c) => [a, b, c];
const curried = curry(abc);

console.log(curried(1)(2)(3)); // => [1, 2, 3]
let unfinishedCurry = curried(1,2);
console.log(unfinishedCurry(3)); // => [1, 2, 3]
console.log(curried(1, 2)(3)); // => [1, 2, 3]
console.log(curried(1)(_, 3)(2)); // => [1, 3, 2]
console.log(curried(1, _, _)(2)(3)); // => [1, 2, 3]

Conclusion

There's an argument to be made for using only specific parts of Lodash to minimize the footprint, yet the essence of my argument remains: As developers, our aim should be to deeply understand the functions we employ. Relying heavily on abstractions can, if not careful, erode our foundational skills. This isn't to undermine Lodash's value; on the contrary, I hope this article highlights its excellence. Lodash has played a pivotal role by providing robust functionalities during a time when such features were lacking in native browser environments.

Currently, I find myself not relying on Lodash as much, though I remain open to the possibility that it might offer indispensable solutions to complex problems in the future. My journey through this exploration was not just to question commonly held beliefs but to deepen my own grasp of JavaScript and to hone my skills. This reflection on JavaScript's growth celebrates Lodash's contribution to easing software development and acknowledges the language's continuous, albeit occasionally contentious, evolution. The path JavaScript is on is filled with promise and excitement for what's yet to come.

Forem: marco cabrera

Understanding SQL Queries: A Beginner’s Guide pt1

Introduction

The Anatomy of an SQL Query

1. Actions (SELECT, INSERT, DELETE, UPDATE)

2. Columns ([column])

3. FROM Clause

4. Table ([table])

5. WHERE Clause

6. Search Criteria ([search criteria])

Understanding Data Types

Crafting SQL Queries

Example Database: Dinosaurs and People

Scenario: Retrieving Dinosaurs from the 'Late Cretaceous' Period

Step 1: Exploring the Data

Step 2: Analyzing the Data

Step 3: Applying a Filter with WHERE

Conclusion

So you think you need _Lodash?

Intro

Deep Copy

Lodash

Vanilla Javascript

Vanilla JavaScript

Difference

Lodash

Vanilla JavaScript

DifferenceBy

Lodash

Vanilla JavaScript

Union

Lodash

Vanilla JavaScript

Zip

Lodash

Vanilla JavaScript

Currying

Lodash

Vanilla JavaScript

Conclusion

1. Actions (`SELECT`, `INSERT`, `DELETE`, `UPDATE`)

2. Columns (`[column]`)

3. `FROM` Clause

4. Table (`[table]`)

5. `WHERE` Clause

6. Search Criteria (`[search criteria]`)

Step 3: Applying a Filter with `WHERE`