#sql

posts tagged #sql

0

A Delightql Language

preview

DelightQL is a query language that transpiles to SQL. Its syntax draws on logic and functional programming to make queries succinct and reusable. It offers the traditional abstractions of a modern programming language while retaining all of SQL’s relational power.

DelightQL is a query language that transpiles to SQL. Its syntax draws on logic and functional programming to make queries succinct and reusable. It offers the traditional abstractions of a modern programming language while retaining all of SQL’s relational power.

0

[DevTool] For Devs who know logic but forget SQL query syntax.

preview

I have started dev tool for myself and now decided to release it. Maybe will be usefull for backend developers or those who are starting to learn sql Link to devtool: isra36.com Link to its documentation: isra36.com/documentation MySQL & PostgreSQL

I have started dev tool for myself and now decided to release it. Maybe will be usefull for backend developers or those who are starting to learn sql Link to devtool: isra36.com Link to its documentation: isra36.com/documentation MySQL & PostgreSQL

0

Are these queries equivalent?

preview

Putting aside any opinions on performance, I’ve been trying to test a notion about whether a couple queries would output the same data (ordering doesn’t matter).

SELECT *
FROM articles
WHERE (
  last_updated >= %s
  OR id IN (1, 2, 3)
  )
  AND created_at IS NOT NULL
SELECT *
FROM articles
WHERE last_updated >= %s
  AND created_at IS NOT NULL
UNION
SELECT *
FROM articles
WHERE id IN (1, 2, 3)
  AND created_at IS NOT NULL

I think they’re equivalent, but I can’t prove it to myself.

Edit: Aye, looking at the replies, I’m becoming aware that I left out a couple key assumptions I’ve made. Assuming:

a) id is a PRIMARY KEY (or otherwise UNIQUE)

b) I mean equivalent insofar as “the rows returned will contain equivalent data same (though maybe ordered differently)”

Putting aside any opinions on performance, I’ve been trying to test a notion about whether a couple queries would output the same data (ordering doesn’t matter).

SELECT *
FROM articles
WHERE (
  last_updated >= %s
  OR id IN (1, 2, 3)
  )
  AND created_at IS NOT NULL
SELECT *
FROM articles
WHERE last_updated >= %s
  AND created_at IS NOT NULL
UNION
SELECT *
FROM articles
WHERE id IN (1, 2, 3)
  AND created_at IS NOT NULL

I think they’re equivalent, but I can’t prove it to myself.

Edit: Aye, looking at the replies, I’m becoming aware that I left out a couple key assumptions I’ve made. Assuming:

a) id is a PRIMARY KEY (or otherwise UNIQUE)

b) I mean equivalent insofar as “the rows returned will contain equivalent data same (though maybe ordered differently)”

0

Optimizing SQLite for servers

Sylvain Kerkour Optimizing SQLite for servers kerkour.com
preview

TL;DR?

PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;
PRAGMA synchronous = NORMAL;
PRAGMA cache_size = 1000000000;
PRAGMA foreign_keys = true;
PRAGMA temp_store = memory ;

TL;DR?

PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;
PRAGMA synchronous = NORMAL;
PRAGMA cache_size = 1000000000;
PRAGMA foreign_keys = true;
PRAGMA temp_store = memory ;

0

Two almost identical tables, should I keep them separate?

preview

Hello! Let me first clarify, this is for a personal project, based on an idea I always use to learn all kinds of things: personal finance tracking.

The DB model I typically use looks something like this:

Initially, I made the decision to separate incomes, expenses and transfers into separate tables, which makes sense to me, according to the way I learned DB normalization.

But I was wondering if there is any benefit in somehow mixing the expense and income tables (since they are almost identical, and any code around these is always almost identical), or even all 3 (expense, income and transfer). Maybe it is more convenient to have the data modeled like this this for an API, but for BI or analytics, a different format would be more convenient? How would such format look like? Or maybe this would be better for BI and analytics, but for an API it’s more convenient to have something different?

A while ago at a previous job, an experienced software architect once suggested, for a transactional system, to separate the transactional DB from a historical DB, and continuously migrate the data differences through ETL’s. I have always thought that idea is pretty interesting, so I wonder if it makes sense to try in my little personal project.

If it was you, how would you model personal finance tracking? Is there something you think I may be missing, or that I should look into for DB modeling?

(Note: I intentionally do not track loans / investments, or at least I have not tried to integrate it for the sake of simplicity, and I have no interest in trying YET.)

Hello! Let me first clarify, this is for a personal project, based on an idea I always use to learn all kinds of things: personal finance tracking.

The DB model I typically use looks something like this:

Initially, I made the decision to separate incomes, expenses and transfers into separate tables, which makes sense to me, according to the way I learned DB normalization.

But I was wondering if there is any benefit in somehow mixing the expense and income tables (since they are almost identical, and any code around these is always almost identical), or even all 3 (expense, income and transfer). Maybe it is more convenient to have the data modeled like this this for an API, but for BI or analytics, a different format would be more convenient? How would such format look like? Or maybe this would be better for BI and analytics, but for an API it’s more convenient to have something different?

A while ago at a previous job, an experienced software architect once suggested, for a transactional system, to separate the transactional DB from a historical DB, and continuously migrate the data differences through ETL’s. I have always thought that idea is pretty interesting, so I wonder if it makes sense to try in my little personal project.

If it was you, how would you model personal finance tracking? Is there something you think I may be missing, or that I should look into for DB modeling?

(Note: I intentionally do not track loans / investments, or at least I have not tried to integrate it for the sake of simplicity, and I have no interest in trying YET.)

0

How to Optimize Slow SQL Queries

preview

cross-posted from: programming.dev/post/10749238

cross-posted from: programming.dev/post/10707322

cross-posted from: programming.dev/post/10707319

In this article, we want to share our experience with fellow developers and offer insights using real-life examples on how to identify and optimize slow SQL queries, especially when working with relational database management systems like PostgreSQL, MySQL, MSSQL, Oracle, etc.

cross-posted from: programming.dev/post/10749238

cross-posted from: programming.dev/post/10707322

cross-posted from: programming.dev/post/10707319

In this article, we want to share our experience with fellow developers and offer insights using real-life examples on how to identify and optimize slow SQL queries, especially when working with relational database management systems like PostgreSQL, MySQL, MSSQL, Oracle, etc.

0

How to Optimize Slow SQL Queries

preview

cross-posted from: programming.dev/post/10707319

In this article, we want to share our experience with fellow developers and offer insights using real-life examples on how to identify and optimize slow SQL queries, especially when working with relational database management systems like PostgreSQL, MySQL, MSSQL, Oracle, etc.

cross-posted from: programming.dev/post/10707319

In this article, we want to share our experience with fellow developers and offer insights using real-life examples on how to identify and optimize slow SQL queries, especially when working with relational database management systems like PostgreSQL, MySQL, MSSQL, Oracle, etc.

0

SQL for the Weary

preview

intended audience

  • Rachel has a master’s degree in cell biology and now works in a research hospital doing cell assays.
  • She learned a bit of R in an undergrad biostatistics course and has been through the Carpentries lesson on the Unix shell.
  • Rachel is thinking about becoming a data scientist and would like to understand how data is stored and managed.
  • Her work schedule is unpredictable and highly variable, so she needs to be able to learn a bit at a time.

prerequisites

  • basic Unix command line: cd, ls, * wildcard
  • basic tabular data analysis: filtering rows, aggregating within groups

learning outcomes

  • Explain the difference between a database and a database manager.
  • Write SQL to select, filter, sort, group, and aggregate data.
  • Define tables and insert, update, and delete records.
  • Describe different types of join and write queries that use them to combine data.
  • Use windowing functions to operate on adjacent rows.
  • Explain what transactions are and write queries that roll back when constraints are violated.
  • Explain what triggers are and write SQL to create them.
  • Manipulate JSON data using SQL.
  • Interact with a database using Python directly, from a Jupyter notebook, and via an ORM.

intended audience

  • Rachel has a master’s degree in cell biology and now works in a research hospital doing cell assays.
  • She learned a bit of R in an undergrad biostatistics course and has been through the Carpentries lesson on the Unix shell.
  • Rachel is thinking about becoming a data scientist and would like to understand how data is stored and managed.
  • Her work schedule is unpredictable and highly variable, so she needs to be able to learn a bit at a time.

prerequisites

  • basic Unix command line: cd, ls, * wildcard
  • basic tabular data analysis: filtering rows, aggregating within groups

learning outcomes

  • Explain the difference between a database and a database manager.
  • Write SQL to select, filter, sort, group, and aggregate data.
  • Define tables and insert, update, and delete records.
  • Describe different types of join and write queries that use them to combine data.
  • Use windowing functions to operate on adjacent rows.
  • Explain what transactions are and write queries that roll back when constraints are violated.
  • Explain what triggers are and write SQL to create them.
  • Manipulate JSON data using SQL.
  • Interact with a database using Python directly, from a Jupyter notebook, and via an ORM.
0

Learning SQL

preview

I’ve started to take an intro SQL class but I want to find more places to practice. Do any of you have recommendations for sites that I can use to practice creating queries based on pieces of information someone is looking for?

Any other advice to give to a brand new SQL learner?

Thanks in advance for any guidance.

Edit: Thanks a ton for the responses, I really appreciate it. I’ve bookmarked these pages and started to go through these sites.

I’ve started to take an intro SQL class but I want to find more places to practice. Do any of you have recommendations for sites that I can use to practice creating queries based on pieces of information someone is looking for?

Any other advice to give to a brand new SQL learner?

Thanks in advance for any guidance.

Edit: Thanks a ton for the responses, I really appreciate it. I’ve bookmarked these pages and started to go through these sites.

0

Fix Posts List Performance + cursor-based pagination by phiresky · Pull Request #3872 · LemmyNet/lemmy

GitHub Fix Posts List Performance + cursor-based pagination by phiresky · Pull Request #3872 · LemmyNet/lemmy github.com
preview

Looks like @phiresky@lemmy.world is looking for reviews on their latest optimizations to the Lemmy backend. Figured folks here might be interested in taking a look.

Looks like @phiresky@lemmy.world is looking for reviews on their latest optimizations to the Lemmy backend. Figured folks here might be interested in taking a look.

0

How This SQL Command Blew Up a Billion Dollar Company - Kevin Fang

play · youtube.com
YouTube How This SQL Command Blew Up a Billion Dollar Company youtu.be
preview

cross-posted from: programming.dev/post/700753

I tried looking the original Joint USSS/FBI Advisory shown in the end of the video, and found these:

cross-posted from: programming.dev/post/700753

I tried looking the original Joint USSS/FBI Advisory shown in the end of the video, and found these:

0

saulpw/visidata: A terminal spreadsheet multitool for discovering and arranging data

GitHub GitHub - saulpw/visidata: A terminal spreadsheet multitool for discovering and arranging data github.com
preview

cross-posted from: programming.dev/post/252619

Works with Postgresql, MySql, and SQLite natively.

Extends confirmed support for DuckDB, ClickHouse, BigQuery, and Snowflake via the vdsql plugin.

cross-posted from: programming.dev/post/252619

Works with Postgresql, MySql, and SQLite natively.

Extends confirmed support for DuckDB, ClickHouse, BigQuery, and Snowflake via the vdsql plugin.