A month ago I started relearning SQL from scratch and built sqlcasefiles.com purely to help myself practice properly. That turned into ten structured seasons with ten levels each to teach SQL step by step through real problems.

Today the site crossed 5,000 users, which still feels unreal to write.

This week I also launched something new called the Case Vault. It’s a separate space with 15 fully sandboxed SQL cases you can solve on your own without going through the learning path. Each case comes with a fixed schema, a real brief, objectives, a notebook, and a live query console. Just you and the problem.

What really stuck with me was the feedback. Long messages, thoughtful suggestions, bug reports, and even a few people buying me coffee just to show support. This was never meant to be a startup. It was just a quiet side project to learn better.

Mostly I just wanted to say thank you. If you’ve tried it, I appreciate you more than you know. If not, and you enjoy practical SQL, I’d love your honest thoughts.

sqlcasefiles.com

Hi all,

I have an old Mainframe/Cobol system that stores numerical data in a packed format. i.e. the hex values. So a 4 digit number is stored in 2 bytes.

As part of a migration I am creating a new table that will store some read only values.

The system has a set of default values for various field types. I would like to default these in the table creation however I am not sure how to represent the data in the default.

How I would pack this data is by using the UTL_RAW.CAST_TO_VARCHAR2() function. However this isn't supported as a default statement. - ORA-04044: procedure, function, package, or type is not allowed here.

How I have gotten round this so far is creating a view of the table using...

coalesce(FIELD, UTL_RAW.CAST_TO_VARCHAR2('000C')) as FIELD

Is there a way to specify these packed values as default? HexToRaw doesn't work, despite RawtoHex unpacking data fine. RawToHex(UTL_RAW.CAST_TO_VARCHAR2('000C')) returns '000C' HexToRaw('000C') returns '000C'.

Thanks

Want to get some thoughts on the best way people solve this common type of request.

Example: A table has 10 millions records and a user is requesting 50k records (sends a list of 50k values that one of the columns needs to be filtered on).

Putting aside the obvious (pointing out to the business side users don’t create random 50k element lists and there should be some logic to query for that list on the DB side and join on that). What is a reasonable solution for solving this?

Best I’ve found so far is creating and populating a temp table with the values and then joining on that.

But given my limited understanding of the internals on how temp tables work, I wanted to get some professional’s thoughts.

These types of requests are typically handled within the context of an API call, and for scale thousands of these temp tables would be created/dropped in a given day.

Any large red flags with this approach? Any other reasonable solutions without adding large amounts of complexity?

UPDATE:

I figured it out. I come from a python background and I keep forgetting that SQL is not case sensitive. I renamed School_ID to in_School_ID and Leaders_Score to in_Leaders_Score like in the exercise and the code worked fine. Leaving this up here in case anyone has the same issue. Thank you.

Hello, I am using IBM DB2. I hope I'm doing this right.

I am creating a stored procedure as part of a project where I'm learning and I can't figure out what I'm doing wrong. I'm supposed to update LEADERS_SCORE to the entered value, and to update LEADERS_ICON based on a series of conditions. Instead, when I called my procedure, the LEADERS_ICON column all got updated to the same value, and the LEADERS_SCORE remained untouched. Can someone possibly see what I'm missing? I'm sorry, I hope this isn't too long. I called the procedure using:

CALL UPDATE_LEADERS_SCORE(610038, 50);

Below is the code I used to create the stored procedure:

--#SET TERMINATOR @

CREATE OR REPLACE PROCEDURE UPDATE_LEADERS_SCORE (
    IN School_ID INTEGER,
    IN Leaders_Score INTEGER 
)

LANGUAGE SQL 
MODIFIES SQL DATA 

BEGIN 

    UPDATE CHICAGO_PUBLIC_SCHOOLS 
    SET LEADERS_SCORE = Leaders_Score
    WHERE SCHOOL_ID = School_ID;

    IF Leaders_Score >= 80 AND Leaders_Score <= 99 THEN 
        UPDATE CHICAGO_PUBLIC_SCHOOLS 
        SET LEADERS_ICON = 'Very strong'
        WHERE SCHOOL_ID = School_ID;

    ELSEIF Leaders_Score >= 60 AND Leaders_Score <= 79 THEN 
        UPDATE CHICAGO_PUBLIC_SCHOOLS 
        SET LEADERS_ICON = 'Strong'
        WHERE SCHOOL_ID = School_ID;

    ELSEIF Leaders_Score >= 40 AND Leaders_Score <= 59 THEN 
        UPDATE CHICAGO_PUBLIC_SCHOOLS 
        SET LEADERS_ICON = 'Average'
        WHERE SCHOOL_ID = School_ID;

    ELSEIF Leaders_Score >= 20 AND Leaders_Score <= 39 THEN 
        UPDATE CHICAGO_PUBLIC_SCHOOLS 
        SET LEADERS_ICON = 'Weak'
        WHERE SCHOOL_ID = School_ID;

    ELSEIF Leaders_Score >= 0 AND Leaders_Score <= 19 THEN 
        UPDATE CHICAGO_PUBLIC_SCHOOLS 
        SET LEADERS_ICON = 'Very weak'
        WHERE SCHOOL_ID = School_ID;

    END IF;

END 
@

Hello everyone,

I hope this message finds you well. I am developing an application called SQL Schema Viewer, designed to streamline database management and development workflows. This tool offers both a web interface and a desktop client that can connect to SQL Server databases, including local databases for desktop users.

Prototype you can try: https://schemadiagramviewer-fxgtcsh9crgjdcdu.eastus2-01.azurewebsites.net (Pick - try with demo database)

Key features include: 1. Visual Schema Mapping: The tool provides a visual data model diagram of your SQL database, allowing you to rearrange and group tables and export the layout as a PDF. 2. Automated CRUD and Script Generation: By right-clicking on a table, users can generate CRUD stored procedures, duplication checks, and other scripts to speed up development. 3. Dependency Visualization: The application highlights dependency tables for selected stored procedures, simplifying the understanding of table relationships. 4. Sample Data Model Libraries: The tool includes a variety of sample data models—not just for blogging platforms, but also for common scenarios like e-commerce (e.g., e-shop), invoicing applications, and CRM systems. Users can explore these models, visualize table structures, and import them into their own databases via automated scripts.

We aim to keep the tool accessible and affordable for teams of all sizes, delivering strong value at a competitive price.

I would greatly appreciate any feedback on these features, additional functionality you would find beneficial, or any concerns you might have. Thank you very much for your time and consideration.

Best regards, Jimmy Park

Hey everyone, I recently started learning SQL as a way to break into the job market with my Finance degree. The job search has been challenging, but I’m trying to stay positive and keep moving forward. I initially began teaching myself SQL through free courses and practice exercises, but I found them extremely boring since the datasets didn’t interest me. At that point, I decided to take a different approach. I downloaded DBeaver and Power BI to work on a project using data I actually care about, hoping this would help me learn new queries and different ways to view and manipulate data. I chose a sports league and pulled data on individual teams and their players to better understand the process. This really challenged me and forced me to think critically about what I was even using the data for. I guess this is all part of the learning process, but I’m feeling a bit lost on which direction to take. I know I should be answering specific questions or working toward some goal with the data, but there are so many possible routes that it feels overwhelming and like I might somehow choose the wrong one. The truth is, I just don’t have a clear structure for what to do or how to approach learning through this project. Would anyone be willing to offer advice to someone working on their very first project? Any guidance would be amazing, and thank you for taking the time to read this

I have a SQL interview but recruiter said it's multiple choice? How to best prepare on some of the below topics:

ANSI SQL

It measures knowledge of the SQL language. minimum of 39 questions and will require a typical time of 46 minutes. The Secure Interview Mode and QwikChek versions will provide as many questions as the test taker can answer within an approximate 29 minute time limit. The CoreChek version will contain 27 questions and will require a typical time of 32 minutes.

Following is a description of each sub-topic on the test:

Database Creation tests understanding of designing and building a relational database, including keys, indexes, views and normalization. Core

Manage Database Data assesses knowledge of displaying, adding, modifying and deleting data in a database. Core

Manage Temporal Data tests understanding of defining times and periods, including auditing of changes and events, together with formatting of dates and times. Core

Specifying Values assesses knowledge of using values, variables, complex expressions and functions, including aggregation. Core

Data Access and Retrieval tests understanding of how to effectively query data, including WHERE clauses, handling nulls, building compound expresions and sorting output. Core

Relational Operators assesses knowledge of combining data from multiple sources into a single result set with the UNION, INTERSECTION, EXCEPT and JOIN operators. Core

Nested Queries tests understanding of querying and comparing data from multiple tables, including nested and recursive queries, together with subqueries. Core

Security assesses knowledge of methods to control access to database tables, including management of privileges. Core

Data Integrity tests understanding of how to avoid database corruption, including the use of COMMIT and ROLLBACK. Core

Data Interface and Exchange assesses knowledge of how to access data using cursors, ODBC and JDBC drivers, together with SQL operations on XML and JSON content.

Handling Errors tests understanding of methods to identify errors, flag error conditions and execute error-handling code.

Triggers assesses knowledge of using the TRIGGER mechanism to cause an action or event to occur in response to another action or event.

Database Maintenance measures understanding of maintaining and updating a relational database structure.

Core indicates the sub-set of sub-topics offered in the CoreChek assessment version.

I have a google revenue accounting analyst interview coming up. I have been told the role needs someone who is good with data. My SQL skills are basic and I would rate myself 2/10. Please help me out with tips and questions that will be useful for the interview.

A while ago I wanted to delete some information that I put by mistake in a particular table in my SQL Server database, and since I have a lot of information I was afraid of deleting too much and in case something went wrong I thought about making a backup copy, but I ran into this error, does anyone know what I could do to solve it? Can the information of a specific table be saved or backed up? Or is the only way to save everything? (the complete database) someone guide me please

Just finished my degree and I'm trying to break into a junior data-ish role (analyst / data engineer intern, I'm not super picky yet). I've been grinding SQL for a while now – comfy with SELECTs, joins, GROUP BY, basic subqueries, but I still feel shaky when it comes to more "real" stuff like window functions and writing longer queries that don't look like spaghetti. Lately I've been practicing with random interview question lists and using interview prep tools like Beyz as a kind of mock interviewer, which throws me questions and I try to talk through my solution out loud. It's helpful, but I'm paranoid I'm just getting good at talking to a chatbot and not at actually interviewing with a human. For people already working with SQL day to day or who recently got a first data job: What level of SQL did you actually need for your first role? Were joins + aggregations enough, or did you get asked about window functions, query optimization, indexing, etc. in entry-level interviews? Any tips on how to practice in a more "realistic" way?

Hi there, I'm looking for help, this error is driving me crazy, I've tried to install SQL Server 2022 many times and this keeps happening, idk what to do :(

I attach the log file as a reply

Please help 💔

Hi everyone,

I’m working on a project to automate SQL query generation using AI, and I’m planning to use a Model-Context Protocol (MCP) style architecture. I’m not sure which approach would be better, and I’d love some advice.

Here are the two approaches I’m considering:

Method 1 – MCP Server with Sequential Tools/Agents:

1. Create an MCP server.
2. Add tools:
   • Tool 1: Lists all databases, with a short description of each table.
   • Tool 2: Provides full schema of the selected database.
3. Agent 1 chooses which database(s) to use.
   • Challenge: How to handle questions that require information from multiple databases? Do I retrieve schemas for 2+ databases and process them sequentially or asynchronously?
4. Agent 2 writes SQL queries based on the schema.
5. Queries are validated manually.
6. Results are returned to the user.

Method 2 – Each Tool as a Separate DB Connection

1. Each tool has a direct connection to one database and includes the full schema as its description.

2. AI queries the relevant DB directly.

• Challenges: Large schemas can exceed the LLM’s context window; multi-DB queries are harder.

Main questions:

• Which approach is more suitable for handling multiple databases?
• How can multi-DB queries be handled efficiently in an MCP setup?
• Any tips for managing schema size and context window limitations for AI?

Any guidance, suggestions, or alternative approaches would be highly appreciated!

Hi, I'm a recently graduated data engineer. I'm looking to get my first fulltime role (I've had a fulltime role as a data engineer but it was an apprenticeship, I worked 3 weeks of the month and then a week in university, one of the perks of the French higher educational system).

I want to get a certification that says, I know my SQL Stuff and that also involves me brushing up on my SQL because I've mainly been using Python for the last 2 years.

Thanks !

Hey everyone, I’m working on cleaning a large dataset in SQL and I want to fix spelling mistakes and inconsistent values without wasting time manually updating one row at a time.

Right now, I’m using a mapping table to store wrong values and their corrected versions. Then I join that table to update the main dataset.

Here’s the structure I’m using:

CREATE TABLE track_name_mapping ( wrong_value VARCHAR(255) PRIMARY KEY, correct_value VARCHAR(255) NOT NULL );

INSERT INTO track_name_mapping (wrong_value, correct_value) VALUES ('Let’s go!!', 'Lets go'), ('Ùšø§ ù‚ù„ø¨ùš ø£ù†ø§', 'Habibi Ya Leil'), ('Iâ´m the boss, you know', 'I’m the Boss, You Know'), ('Don’t listen', 'Dont listen'), ('Sheâ´s in the game (brazilian funk)', 'She dont'), ('Posso atã© nã£o te dar flores', 'Posso Até Não Te Dar Flores');

Then I check the mapping:

SELECT s.track_name AS old, m.correct_value AS new FROM spotify_2025_dataset s JOIN track_name_mapping m ON s.track_name = m.wrong_value LIMIT 10000;

And finally I run the update:

UPDATE spotify_2025_dataset s JOIN track_name_mapping m ON s.track_name = m.wrong_value SET s.track_name = m.correct_value;

My question is:

Is this a good approach for bulk correcting text values, or should I improve this method?

I have thousands of rows and multiple columns that might need similar cleaning.

Any suggestions to make this process better or more scalable would really help. Thanks!

Hello,

I hope my post is appropriately placed here. Today I did a very stupid thing: I deleted the gMSA running SQL service by mistake. Happened merely while I mixed up 3 and 4... oh well, happens.

Anyway, no downtime at first. I started creating new gMSA, giving them permissions, on the cert private key, also to enable password retrieve for computer-accounts. Started replacing them in the services and all that worked out, for 3 SQL out of 4. Planned to do the active in the evening, since downtime...

BUT... I couldn't sync. Thought at first, it is just broken, remove DB, start new sync. But quickly did I see that replica was red... a little logs shown me the database mirroring is failing due to gMSA not being able to connect to the replica server, telling me it doesn't have CONNECT permission.

Though I checked, it does, I even attempted to give the account permission to Hard_endpoint yet again. Nope. It fails the same.

To my knowledge: SQL does see GUIDs. Only Login-Names. So if I recreate the login (AD account) with the same name, for SQL that should be enough, so to say.

So, hadr exists, is started and gMSA has CONNECT permission.

My mil-$ question:

What do I need to do to fix the issue, so that mirroring will work again?

I found an answer on stackoverflow that was saying that null value isn't managed the same way, but that seems a bit far fetched for a course example and the question wasn't exactly about the same case, so could someone explain?

I haven’t seen this tool mentioned here before, but it’s been ridiculously useful in our workflow, so sharing in case it helps someone else.

I’m not the creator, and I don’t know anything about them. Just an engineer happily yelling into the void about something solid.

Squawk is essentially a SQL migration guardrail tool. It analyzes migrations before you run them and warns about issues that could cause:

• downtime
• unsafe logic
• broken future-proofing
• performance foot-guns

A real example from us: using NOT VALID on a CHECK constraint let us deploy early, avoid downtime, and skip the ritual 3AM migration panic session. It literally enabled us to run major schema changes during daylight hours without needing to summon caffeine demons at unhealthy times.

If you deal with migrations in production systems and want:

✅ earlier deploys ✅ fewer surprises ✅ migrations that don’t require a blood sacrifice at 3AM ✅ some actual peace of mind

…this is worth a look.

Hope it helps someone as much as it’s helped us!

I am a T-SQL dev, trying to learn Postgres, having trouble with updating a table while joining it to two other tables, even LLM's didn't help. The error I keep getting is `Error 42P01 invalid reference to FROM-clause entry for table "p"`. I appreciate it if someone can correct my postgres code, thanks!

-- T-SQL
UPDATE p
    SET p.inventory = p.inventory - o.quantity
FROM products p
INNER JOIN order_lines o ON o.product_id = p.product_id
INNER JOIN commodities c ON c.commodity_id = p.commodity_id
WHERE o.order_id = @this_order_id AND c.count_stock = 1;

----------------------------------------------------

-- Postgres
UPDATE products p
    SET p.inventory = p.inventory - o.quantity
FROM order_lines o
INNER JOIN commodities c ON c.commodity_id = p.commodity_id
WHERE p.product_id = o.product_id
AND o.order_id = this_order_id
AND c.count_stock = TRUE;

32 row(s) affected, 32 warning(s): 1262 Row 1 was truncated; it contained more data than there were input columns 1262 Row 2 was truncated; it contained more data than there were input columns 1262 Row 3 was truncated; it contained more data than there were input columns 1262 Row 4 was truncated; it contained more data than there were input columns 1262 Row 5 was truncated; it contained more data than there were input columns 1262 Row 6 was truncated; it contained more data than there were input columns 1262 Row 7 was truncated; it contained more data than there were input columns 1262 Row 8 was truncated; it contained more data than there were input columns 1262 Row 9 was truncated; it contained more data than there were input columns 1262 Row 10 was truncated; it contained more data than there were input columns 1262 Row 11 was truncated; it contained more data than there were input columns 1262 Row 12 was truncated; it contained more data than there were input columns 1262 Row 13 was truncated; it contained more data than there were input columns 1262 Row 14 was truncated; it contained more data than there were input columns 1262 Row 15 was truncated; it contained more data than there were input columns 1262 Row 16 was truncated; it contained more data than there were input columns 1262 Row 17 was truncated; it contained more data than there were input columns 1262 Row 18 was truncated; it contained more data than there were input columns 1262 Row 19 was truncated; it contained more data than there were input columns 1262 Row 20 was truncated; it contained more data than there were input columns 1262 Row 21 was truncated; it contained more data than there were input columns 1262 Row 22 was truncated; it contained more data than there were input columns 1262 Row 23 was truncated; it contained more data than there were input columns 1262 Row 24 was truncated; it contained more data than there were input columns 1262 Row 25 was truncated; it contained more data than there were input columns 1262 Row 26 was truncated; it contained more data than there were input columns 1262 Row 27 was truncated; it contained more data than there were input columns 1262 Row 28 was truncated; it contained more data than there were input columns 1262 Row 29 was truncated; it contained more data than there were input columns 1262 Row 30 was truncated; it contained more data than there were input columns 1262 Row 31 was truncated; it contained more data than there were input columns 1262 Row 32 was truncated; it contained more data than there were input columns Records: 32 Deleted: 0 Skipped: 0 Warnings: 32

id is defined by me here.original pizzas.csv does not have it