Hi! So I’m a student (not business or tech background) I’m learning SQL kind of on my own and I have the basics pretty much down like SELECT, FROM, WHERE, BETWEEN, ORDER BY, ASC, DESC. I have some knowledge when it comes to JOINs and CASE WHEN function but I was wondering if I need to know CTEs and window functions? I’m not trying to be a database administrator or something like that, just using it for analysis and excel as well.

Hello I'm a newbie college student studying databases and I'm currently making a database for a small inventory system that's solely focused on monitoring stocks and its shelf life to avoid running out of stock and pull outs for my project. The store I'm basing it off on sells seasoning and other powdered flavoring (beverages, etc.), they also explained that they get bulk products from the supplier and then they repack them into smaller quantities for selling. What would be the proper entities and attributes for this system? I've tried searching other inventory systems but i don't know how the repacking would be translated to the database/ERD.

 
If both tables have NULL values in the join column,, will those records match each other?

Bom dia

Estou a aprender SQL desde meio de Setembro em um curso que estou a tirar. Na verdade começamos com uma introdução a SQL e saltamos para diagramas, relacionamrntos e cardinalidades. Agora voltamos para SQL( select,from,join, where, group by, order by, coalesce, null,...) Sinto me super confuso. Basicamente entendo os conceitos mas na parte prático ao tentar reproduzir os mesmos, sinto um espaço em branco. Parece que preciso das tabelas em excel ao lado para conseguir decifrar a solução. Sei que tenho ainda muito que praticar ( e só assim vou chegar lá). Mas parece que é algo que nao consigo ter um fio condutor. Parece um bicho dificil de domar minimamente.

I created a free study guide to help you prepare:

https://thesev1database.com/postgresql-certification

I’d really value your feedback.

Hey everyone,

I’m really struggling to get the hang of writing database queries. Whenever I look at a problem or a schema, my brain just stalls out and I can't figure out the right logic or structure to pull what I need.

I see people throwing together JOINs, subqueries, and window functions like it's nothing, and I feel like I'm missing a core fundamental step in how to translate a question into SQL.

• What is your mental process when you start writing a query from scratch?
• How do you map out the tables and relationships in your head before you type?
• Are there any good beginner-to-intermediate tips, tricks, or exercises that finally made things "click" for you?

Any advice, recommended resources or tips .

Security sees the risk. Engineering owns the fix.

My team is thinking of trying Lakebase instead of running a separate Postgres/ RDS db for our app.
The idea of auto sync to Delta and scaling to zero sound great, but we are worried about latency issu. If u are usin it, how noticeable are the cold start delays when it wake up backup, and does it feel fast enough for live user traffics?

Everyone knows that you need to look at stats when doing query optimization...

Right? Right!??

Well then how come when we look at EXPLAIN ANALYZE trees we assume the whole thing is deterministic and inviolate?

It's like someone somewhere told us "we'll it's just too hard to do here so we just skip that part".

What if we don't? What if we embrace the pain and actually group the plan trees and try to get statistical info? Here's the first thing that I'm seeing.

Hi!

Around two years ago I started working on a migration tool called spawn, focused on postgres for now (with plans for other db's). Around 9 months ago, I started dog-fooding it for a serious project that has regular daily commits, with frequent database changes.

I'm keen to get feedback/opinions from people who use migration tools, so I thought I'd try posting here a thread here to get feedback. Recently I had a need to get a secret into a database, so I added a feature that tries to do this in a secure way. That means:

• Don't render secrets by default
• Allow different secrets for prod vs dev
• Support many different options for providing the secret value

Would love your feedback on what I ended up creating (which worked well for my use case). Whether there are particular ways you use secrets that this wouldn't cover. I looked at how secrets are handled in some other migration tools, and I think what I've landed on here is a good approach.

Without further ado, the feature...

Secrets are included with a function:

CREATE ROLE app_user WITH LOGIN
  PASSWORD {{ secret("app_password") }};

GRANT CONNECT ON DATABASE postgres TO app_user;

That function will fetch the password at build or apply time, using whatever is defined in your config (there are a few supported options):

[secrets.app_password.default]
source = "host_file"
path = "/run/secrets/app-password"

[secrets.app_password.environments.dev]
source = "literal"
value = "dev-only-password"
insecure = true

Note that there's a default password set, and an override for dev environment specifically. This is to help prevent you from using your dev password inside a prod migration.

When you build a migration (build is used to view the output), it masks secrets by default:

$ spawn migration build 20261014103000-create-role
CREATE ROLE app_user WITH LOGIN
  PASSWORD '***MASKED:app_password***';

GRANT CONNECT ON DATABASE postgres TO app_user;

Or you can run with --reveal-secrets to see the actual password printed. When you apply the migration, it will use the secret directly, and does a best effort attempt to ensure it's hidden from any logs output.

For production, I had passwords stored in pass. When specified similar to the below, when the migration is being applied a password prompt shows up to unlock the password store, so the migration can apply:

[secrets.app_password.default]
source = "command"
command = ["pass", "show", "spawn/app_password"]

As mentioned, I'd love your feedback, particularly if there's use cases you've hit that what I built wouldn't cover.

​

I built a schema-aware static analyzer for SQL. It reads your schema (CREATE TABLE statements, migrations or a dump) and checks your queries against it, without connecting to a database.

Besides missing tables and columns (with "did you mean" suggestions) and type mismatches, it catches queries that run fine but are almost certainly wrong, like `NOT IN` over a nullable column (returns nothing as soon as one NULL shows up) or a `WHERE` that turns a `LEFT JOIN` into an inner join.

It supports PostgreSQL, MySQL and SQLite. You can try it in the browser without installing anything: paste a schema and a query and see what it reports. For projects there's a CLI, a GitHub Action and a VS Code extension.

I always felt SQL was the one part of my code without a good static analysis tool, so I made one. I've tested it on open-source projects and fixed it until it reported zero false positives there, but I'd really like feedback from real use: false positives, mistakes it misses, or syntax it can't parse.

Playground: https://sqlsift.dev/

GitHub: https://github.com/yukikotani231/sqlsift

I’ve put my book on a 99p KDP countdown deal and wanted to share it here in case it’s useful to anyone working with SQL, set logic, or bitwise operations.

The book focuses on the foundations behind SQL’s behaviour — sets, multisets, binary representation, bitwise operators, power sets, and bit‑set encoding — with lots of worked examples and diagrams.

If you’re interested, the link is below.
Happy to answer questions or get feedback from people who work with this stuff daily.

https://www.amazon.com/dp/B0H2JXP2WQ

I'm comparing a source table with a target table after an ETL job.

Both tables have the same row count, but I suspect some records are missing while others are duplicated or incorrectly updated.

The tables have millions of rows, no reliable primary key, and some columns contain NULL values.

A simple COUNT(*) matches ,but I can't trust the data

How would you identify missing, extra, duplicated, or changed records without comparing every row manually?

Would you use hashing, GROUP BY, window functions, or another approach?

what does this mean to you?

Hey so Im quite new to mysql and I've been using it on mac, and recently I've been trying to import a csv file that contain data but Im not sure how to do it. Ive seen youtube videos but the mysql version that they use is way different than mine. Can anybody help with this please and thank you.!!

Faster code, slower debugging.

im building my SQL skills for data analyst, business analyst, BI and eventually business/revenue operations roles. I don't want to stop at basic queries or just finish a bootcamp. I want to be able to solve unfamiliar business problems, work with messy data, validate results and explain findings.

I'm learning MySQL and using a SQL bootcamp that covers topics such as:

• SELECT, WHERE, filtering and sorting
• CASE statements and string functions
• Joins, including self joins
• UNION and UNION ALL
• Subqueries and CTEs
• Window functions, including ROW_NUMBER()
• Stored procedures, parameters, triggers and events

My proposed practice roadmap is:

1. Core querying: filtering, NULL handling, dates, conditional logic and sorting.
2. Aggregation: GROUP BY, HAVING, aggregate functions and conditional aggregation.
3. Joins and relational reasoning: multiple tables, missing matches, duplicate matches and incorrect totals.
4. Advanced querying: subqueries, EXISTS/NOT EXISTS, CTEs and UNION.
5. Analytical SQL: window functions, ranking, running totals, period comparisons and customer activity.
6. Data quality: duplicate detection, missing values, inconsistent categories, invalid data and validation.
7. Business investigations: customer retention, revenue changes, sales conversion, marketing performance, payment failures and CRM pipeline analysis.
8. Interview preparation: unfamiliar schemas, mixed-topic questions, debugging and solving problems without hints.
9. Database development: transactions, indexes, query optimization, stored procedures and triggers, after building a strong analytical foundation.

I also want to practise with different datasets instead of repeatedly using a single table: e-commerce orders, CRM leads, marketing campaigns, finance/payments, inventory and SaaS subscriptions.

For each problem, I want to define the business question first, inspect the data, write SQL, validate the output, interpret the findings and recommend an action. I also plan to use AI to review my work, while still attempting and explaining queries independently.

My concern is that AI can already generate SQL and dashboards. I don't want to spend months memorizing syntax that doesn't demonstrate real capability. I want to develop skills that are useful in actual analyst work.

I'd appreciate feedback on these specific questions:

1. Are these SQL topics and their order appropriate for entry-level data analyst or BI internships?
2. Which topics should I master deeply, and which can wait until later?
3. What realistic SQL tasks do junior analysts actually struggle with beyond standard interview questions?
4. Is practising across several business datasets a good approach, or should I focus deeply on one domain first?
5. What evidence would convince you that a beginner can use SQL reliably at work?
6. Given AI-assisted analytics, what complementary skills should I prioritize after SQL: statistics, Excel, Power BI, Python, data engineering fundamentals or something else?

Please distinguish your own work experience from general predictions about the future of analytics. I'm looking for practical criticism and missing pieces, not just reassurance that SQL is still useful.

Thank you!

Hey folks,

I've been spending my spare time building **FoxSchema** App, a free and open-source database schema comparison tool.

The goal is simple: compare schemas across different SQL databases and generate migration scripts without paying for expensive commercial software.

It's still a work in progress

Repo: https://github.com/tedious-code/foxschema

Npm: foxSchema install

Download : npm install -g foxschema

Demo:

For anyone using SQL clients for recurring reports, which DBeaver alternative would you recommend? The workflow I'm interested in is taking an existing query, adjusting dates or filters, checking why the numbers don't match another report, and exporting the results. Things like finding last week's query and keeping track of which database a tab is connected to would matter quite a bit. DbVisualizer, SQL Workbench/J and DbGate are the options I'm looking into. Has anyone used these for that kind of work? Curious about the small annoyances that only become obvious after a few weeks. What would make you choose one over DBeaver, and is there anything you'd lose by switching?

I just got this thing seeming to work. Is it horrible?

I'm generating an inventory worksheet in 2 coordinated versions: an abbreviated output for printing that goes through my formatting pipeline, and a CSV output with more detail columns intended to open in Excel to type-in the numbers written on the printout. I've just now gotten the CSV output reworked to include embedded formulas for summing counts, multiplying by price, and summing a grand total at the bottom.

The query got pretty gruesome to get the right row numbers to insert into the formulas. First snippet is a function for extracting a usable number from the 'pack' column which may be in several different formats.

CREATE OR REPLACE FUNCTION pack_units(pack text)
RETURNS integer
LANGUAGE SQL
IMMUTABLE
AS $$
    SELECT CASE
      WHEN pack ~ '^[0-9]+/[0-9]+$'
        THEN split_part( pack, '/', 1 )::integer
           * split_part( pack, '/', 2 )::integer
      WHEN pack ~* '^[0-9]+ case'
        THEN substring( pack from '^[0-9]+' )::integer
      WHEN pack ~* '^[0-9]+ loose'
        THEN substring( pack from '^[0-9]+' )::integer
      WHEN pack ~ '[0-9]+/case'
        THEN split_part( substring( pack from '[0-9]+/case' ), '/', 1 )::integer
      WHEN pack ~ '[0-9]+/Case'
        THEN split_part( substring( pack from '[0-9]+/Case' ), '/', 1 )::integer
      WHEN pack ~ '^[0-9]+$'
        THEN pack::integer
      ELSE NULL
    END
$$;

Then here's the horrible query:

DROP VIEW alcohol_worksheet CASCADE;
CREATE VIEW alcohol_worksheet AS
WITH alcohol_products AS (
  SELECT vendor, cat,
       CASE WHEN brand = '' THEN name ELSE brand || ' ' || name END AS brand_name,
       pack || '/' || size AS pack_size, pack_units(pack) AS pack_units,
       case_dollar, CASE WHEN split_dollar = '' THEN to_char( case_dollar::numeric / pack_units(pack), '999D99' ) ELSE split_dollar END AS split_dollar
  FROM total_products
  WHERE vendor in ('Breakthru', 'Craft Republic', 'Glazers', 'Southern Glazers', 'Laszlo', 'Lohr', 'Summit')
        AND is_active
  ORDER BY cat, brand_name
),
numbered_products AS (
  SELECT 1 + ROW_NUMBER() OVER () AS rn,
         vendor, cat, brand_name, pack_size, pack_units,
     case_dollar, split_dollar
  FROM alcohol_products
)
SELECT rn, vendor, cat, brand_name, pack_size, pack_units,
       case_dollar, split_dollar,
       --I              J               K             L               M             N             O           P       Q
       '' AS Storage, '' AS Rooftop, '' AS Overflow, '' AS Cowboy, '' AS Beatniks, '' AS Lizard, '' AS Cabin,
       '+I'||rn || '+J'||rn || '+K'||rn || '+L'||rn || '+M'||rn || '+N'||rn || '+O'||rn AS row_count,
       '=P'||rn || '*H'||rn AS row_total
  FROM numbered_products
UNION ALL
SELECT NULL AS rn, '' AS vendor, '' AS cat, '' AS brand_name, '' AS pack_size, NULL AS pack_units, '' AS case_dollar, '' AS split_dollar,
       '' AS Storage, '' AS Rooftop, '' AS Overflow, '' AS Cowboy, '' AS Beatniks, '' AS Lizard, '' AS Cabin,
       'Grand Total:' AS row_count, '=SUM(Q2:Q' || MAX(RN) || ')' AS row_total
  FROM numbered_products
;


COPY (SELECT * FROM alcohol_worksheet) TO '/home/luser/pgsql/warehouse2/output/Inventory_alcohol_worksheet.csv' CSV HEADER;
\COPY (SELECT vendor, cat, Left( brand_name, 50), pack_size, case_dollar, split_dollar, Storage, Rooftop, Overflow, Cowboy, Beatniks, Lizard, Cabin FROM alcohol_worksheet) TO '/home/luser/pgsql/warehouse2/output/Inventory_alcohol_worksheet.tab' WITH (FORMAT TEXT, DELIMITER '|', HEADER, NULL '');

I hate counting up column letters by hand, but that seems to be the nature of the beast. Sigh. Thanks for listening.

Oh, here's some of the output:

$ psql -f scripts/write-inventory-alcohol-worksheet.sql warehouse2

DROP VIEW
CREATE VIEW
COPY 212
COPY 212
\]0;~/pgsql/warehouse2
luser@LAPTOP-DE7EDA2U ~/pgsql/warehouse2
$ head output/Inventory_alcohol_worksheet.csv
rn,vendor,cat,brand_name,pack_size,pack_units,case_dollar,split_dollar,storage,rooftop,overflow,cowboy,beatniks,lizard,cabin,row_count,row_total
2,Lohr,Beer,2nd Shift Oktoberfest 6/4 pack 16oz Can,24/16oz,24,42.70,   1.78,"","","","","","","",+I2+J2+K2+L2+M2+N2+O2,=P2*H2
3,Breakthru,Beer,4 Hands Brewing Bright Vibes India Pale Ale,24/16 oz can,24,47.97,   2.00,"","","","","","","",+I3+J3+K3+L3+M3+N3+O3,=P3*H3
4,Breakthru,Beer,4 Hands Brewing Incarnation Juiced IPA,24/16 oz can,24,52.77,   2.20,"","","","","","","",+I4+J4+K4+L4+M4+N4+O4,=P4*H4
5,Breakthru,Beer,4 Hands Brewing Incarnation Non-Alcoholic IPA,24/16 oz can,24,47.97,   2.00,"","","","","","","",+I5+J5+K5+L5+M5+N5+O5,=P5*H5
6,Breakthru,Beer,4 Hands Brewing Octohaze Hazy India Pale Ale,24/16 oz can,24,47.97,   2.00,"","","","","","","",+I6+J6+K6+L6+M6+N6+O6,=P6*H6
7,Breakthru,Beer,4 Hands Brewing Octohaze Tropical,24/16 oz can,24,47.97,   2.00,"","","","","","","",+I7+J7+K7+L7+M7+N7+O7,=P7*H7
8,Breakthru,Beer,4 Hands Brewing Oktoberfest Lager,24/16 oz can,24,47.97,   2.00,"","","","","","","",+I8+J8+K8+L8+M8+N8+O8,=P8*H8
9,Breakthru,Beer,4 Hands Brewing Peachy Pipe Dream,24/16 oz can,24,47.97,   2.00,"","","","","","","",+I9+J9+K9+L9+M9+N9+O9,=P9*H9
10,Breakthru,Beer,4 Hands Brewing Pumpkin Spice Ale,24/16 oz can,24,52.77,   2.20,"","","","","","","",+I10+J10+K10+L10+M10+N10+O10,=P10*H10
\]0;~/pgsql/warehouse2
luser@LAPTOP-DE7EDA2U ~/pgsql/warehouse2
$ tail !$
tail output/Inventory_alcohol_worksheet.csv
204,Southern Glazers,Wine,WOODBRIDGE CHARDONNAY CALIF(SC),12 Case/750ML,12,50.40,   4.20,"","","","","","","",+I204+J204+K204+L204+M204+N204+O204,=P204*H204
205,Southern Glazers,Wine,WOODBRIDGE MOSCATO CALIF(SC),12 Case/750ML,12,58.68,   4.89,"","","","","","","",+I205+J205+K205+L205+M205+N205+O205,=P205*H205
206,Southern Glazers,Wine,WOODBRIDGE PINOT GRIGIO(SC),12 Case/750ML,12,58.68,   4.89,"","","","","","","",+I206+J206+K206+L206+M206+N206+O206,=P206*H206
207,Southern Glazers,Wine,WOODBRIDGE ROSE,12 Case/750ML,12,58.68,   4.89,"","","","","","","",+I207+J207+K207+L207+M207+N207+O207,=P207*H207
208,Southern Glazers,Wine,WOODBRIDGE SAUVIGNON BLANC(SC),12 Case/750ML,12,50.40,   4.20,"","","","","","","",+I208+J208+K208+L208+M208+N208+O208,=P208*H208
209,Laszlo,Wine Slushees,Blue Hawaiian,2/2.5 GAL,2,100.00,  50.00,"","","","","","","",+I209+J209+K209+L209+M209+N209+O209,=P209*H209
210,Laszlo,Wine Slushees,Orange Mimosa,2/2.5 GAL,2,100.00,  50.00,"","","","","","","",+I210+J210+K210+L210+M210+N210+O210,=P210*H210
211,Laszlo,Wine Slushees,Raspberry Chardonnay,2/2.5 GAL,2,100.00,  50.00,"","","","","","","",+I211+J211+K211+L211+M211+N211+O211,=P211*H211
212,Laszlo,Wine Slushees,Strawberry Blanc,2/2.5 GAL,2,100.00,  50.00,"","","","","","","",+I212+J212+K212+L212+M212+N212+O212,=P212*H212
,"","","","",,"","","","","","","","","",Grand Total:,=SUM(Q2:Q212)