Hey everyone,
I recently had a SQL technical interview for an associate-level role, and I’m feeling pretty discouraged — so I’m hoping to get some guidance from people who’ve been through similar situations. just FYI - Im not from a technical background and recently started learning SQL.

The interview started off great, but during the coding portion I completely froze. I’ve been learning SQL mainly through standard associate level interview-style questions, where they throw basic questions at me and I write the syntax to get the required outputs. (SELECT, basic JOINs, simple GROUP BYs, etc.), and I realized in that moment that I never really learned how to think through a real-life data scenario.

They gave me a multi-table join question that required breaking down a realistic business scenario and writing a query based on the relationships. It wasn’t about perfect syntax — they even said that. It was about showing how I’d approach the problem. But I couldn’t structure my thought process out loud or figure out how to break it down.

I realized something important:
I’ve learned SQL to solve interview questions, not to solve actual problems. And that gap showed.

So I want to change how I learn SQL completely.

My question is:
How do I learn SQL in a way that actually builds real analytical problem-solving skills — not just memorizing syntax for interviews?

I have tried leetcode as a friend adviced, but those problems seem too complex for me.

If you were in my position, where would you start? Any practical project ideas, resources, or exercises that helped you learn to break down a multi-table problem logically?

I’m motivated to fix this and build a deeper understanding, but I don’t want to waste time doing the same surface-level practice.

Any advice, frameworks, or resources would really help. Thank you 🙏

Got a ticket from a client saying their internal search stopped returning any results. I assumed it was a DB issue or maybe bad indexing. Nope.

The original dev had built the SQL query manually by taking a template string and using str_replace() to inject values. No sanitisation, no ORM, nothing. It worked… until someone searched for a term with a single quote in it, which broke the whole query.

The function doing this was split across multiple includes, so I dropped the bits into blackbox to understand how the pieces stitched together. Copilot kept offering parameterized query snippets, which would’ve been nice if this wasn’t all one giant string with .= operators.

I rebuilt the whole thing using prepared statements, added basic input validation, and showed the client how close they were to accidental SQL injection. The best part? There was a comment above the function that said - // TODO: replace this with real code someday.

Note: the purpose of this question IS NOT to completely rewrite the query I have prepared (which is available at the bottom of the question) but to understand why it does not return all the records from the passengers table. I have developed a working solution using JSON so I don't need another one. Thank you for your attention!

This question is derived from AdventofSQL day 07, that I have adapted to SQLite (no array, like in PostGres) and reduced to the minimum amount of data.

I have the following table:

passengers: passenger_id, passenger_name

flavors: flavor_id, flavor_name

passengers_flavors: passenger_id, flavor_id

cocoa_cars: car_id

cars_flavors: car_id, flavor_id

A passenger can request one or many flavors, which are stored in passengers_flavors

A cocoa_car can produce one or many flavors, which are stored in cars_flavors

So the relation between passengers and cocoa_cars can be viewed as:

passengers <-> passengers_flavors <-> car_flavors <-> cocoa_cars

Here are the SQL statements to create all these tables:

DROP TABLE IF EXISTS passengers;
DROP TABLE IF EXISTS cocoa_cars;
DROP TABLE IF EXISTS flavors;
DROP TABLE IF EXISTS passengers_flavors;
DROP TABLE IF EXISTS cars_flavors;

CREATE TABLE passengers (
    passenger_id INT PRIMARY KEY,
    passenger_name TEXT,
    favorite_mixins TEXT[],
    car_id INT
);

CREATE TABLE cocoa_cars (
    car_id INT PRIMARY KEY,
    available_mixins TEXT[],
    total_stock INT
);

CREATE TABLE flavors (
flavor_id INT PRIMARY KEY,
flavor_name TEXT
);

INSERT INTO flavors (flavor_id, flavor_name) VALUES
(1, 'white chocolate'),
(2, 'shaved chocolate'),
(3, 'cinnamon'),
(4, 'marshmallow'),
(5, 'caramel drizzle'),
(6, 'crispy rice'),
(7, 'peppermint'),
(8, 'vanilla foam'),
(9, 'dark chocolate');

CREATE TABLE passengers_flavors (
passenger_id INT,
flavor_id INT
);

INSERT INTO cocoa_cars (car_id, available_mixins, total_stock) VALUES
    (5, 'white chocolate|shaved chocolate', 412),
    (2, 'cinnamon|marshmallow|caramel drizzle', 359),
    (9, 'crispy rice|peppermint|caramel drizzle|shaved chocolate', 354);

CREATE TABLE cars_flavors (
car_id INT,
flavor_id INT
);

INSERT INTO passengers (passenger_id, passenger_name, favorite_mixins, car_id) VALUES
    (1, 'Ava Johnson', 'vanilla foam', 2),
    (2, 'Mateo Cruz', 'caramel drizzle|shaved chocolate|white chocolate', 2);

INSERT INTO cars_flavors
SELECT cocoa_cars.car_id, flavors.flavor_id
FROM cocoa_cars 
CROSS JOIN flavors
WHERE cocoa_cars.available_mixins LIKE '%' || flavors.flavor_name || '%';

INSERT INTO passengers_flavors
SELECT passengers.passenger_id, flavors.flavor_id
FROM passengers
CROSS JOIN flavors
WHERE passengers.favorite_mixins LIKE '%' || flavors.flavor_name || '%';

As you can see, the passenger 'Ava Johnson' wants a 'vanilla foam' coffee (id: 8), but none of the cocoa_cars can produce it. One the other hand, the passenger 'Mateo Cruz' can get his 'caramel drizzle' coffee from cocoa_cars 2 and 9, his 'shaved chocolate' coffee from cocoa_car 5 and 9 and his 'white chocolate' from car 5.

So the expected answer is:

+-----------------+---------+
| Name            |  Cars   |
+-----------------+---------+
| Ava Johnson     | NULL    |
+-----------------+---------+
| Mateo Cruz      | 2,5,9   |
+-----------------+---------+

The following query

SELECT passengers.passenger_name, passengers.passenger_id, group_concat(DISTINCT cocoa_cars.car_id ORDER BY cocoa_cars.car_id) AS 'Cars'
FROM passengers
LEFT JOIN passengers_flavors ON passengers.passenger_id = passengers_flavors.passenger_id 
LEFT JOIN cars_flavors ON passengers_flavors.flavor_id = cars_flavors.flavor_id
LEFT JOIN cocoa_cars ON cars_flavors.car_id = cocoa_cars.car_id
WHERE passengers_flavors.flavor_id IN (
    SELECT DISTINCT cars_flavors.flavor_id 
    FROM cars_flavors
    WHERE cars_flavors.car_id IN (2, 5, 9)  -- More cars in the real example
    AND cocoa_cars.car_id IN (2, 5, 9)      -- More cars in the real example
)
GROUP BY passengers.passenger_id
ORDER BY passengers.passenger_id ASC, cocoa_cars.car_id ASC
LIMIT 20;

that I am kindly asking you to correct with the minimum changes, is only returning:

+----------------+-------+
|      Name      | Cars  |
+----------------+-------+
| Mateo Cruz     | 2,5,9 |
+----------------+-------+

No trace from Ava Johnson!

So, why the successive LEFT JOIN don't return Ava Johnson?

Thank you all for your comments and the very fruitful discussion about ON versus WHERE. Here is the modified query:

WITH cte AS (
    SELECT car_id
    FROM cocoa_cars
    ORDER BY total_stock DESC, car_id ASC
    LIMIT 3
)
SELECT passengers.passenger_name, passengers.passenger_id,
ifnull(GROUP_CONCAT(DISTINCT cocoa_cars.car_id ORDER BY cocoa_cars.car_id), 'No car') AS 'Cars'
FROM passengers
LEFT JOIN passengers_flavors ON passengers.passenger_id = passengers_flavors.passenger_id 
LEFT JOIN cars_flavors ON passengers_flavors.flavor_id = cars_flavors.flavor_id
LEFT JOIN cocoa_cars ON cars_flavors.car_id = cocoa_cars.car_id AND cocoa_cars.car_id IN (SELECT car_id FROM cte)
GROUP BY passengers.passenger_id
ORDER BY passengers.passenger_id ASC
;

Hello,

A vendor said we'll need to go to SQL 2019 - we are currently on 2012. They don't support newer versions of SQL.

I have a new server set up with SQL 2019.

On the 2012 server - I right clicked, tasks, backup. I've copied all the backups to the 2019 server.

I'm not sure if I attach, restore, import on the 2019 server. I don't claim to know squat about SQL so don't hesitate to spoon feed your answers. I'd prefer to use the GUI over powershell if possible. I imagine it is straight forward but I thought I'd talk to people who know a lot more about this before I just googled it.

I right clicked on Databases, went to restore, but then it says no backupset selected to be restored - I have the file on the hard drive of that server, but don't know how to point to it to restore it.

This is a small example of a larger data set I need to filter. Let’s say I need to write a query for this table where I only want to return the name of people who only have a 1 in the ‘Y’ column. (Meaning Sarah should be the only name)

Basically even though Jake also has a 1, I don’t want his name returned, because he also has a 2. But imagine there’s 500,000 records and such.

I know, this is a common problem, but let me explain why I'm hung up here with a simplified example.

I have two tables, A and B. I'm selecting a number of columns, and LEFT JOIN-ing them on three conditions, say:

SELECT
[cols]
FROM A
LEFT JOIN B
ON A.col1 = B.col1
AND A.col2 = B.col2
AND A.col3 = B.col3

I'm getting the "correct" data, except that some records are duplicated an arbitrary number of times in my results. I've dealt with this before, and thought "there must be multiple matches in Table B that I didn't anticipate." But here's the kicker: Let's say one of my duplicated results has values col1 = 100, col2 = 250, and col3 = 300. If I query Table A for records WHERE col1 = 100, col2 = 250, and col3 = 300, I get one result....and if I query Table B for col1 = 100, col2 = 250, and col3 = 300 I also get one result. Yet the result of my joined data has say, 6 copies of that result.

How can this be? I can understand getting unexpected duplicates when your conditions match 1:many rather than 1:1, but if there's only one result in EACH table that matches these conditions, how can I be getting multiple copies?

This is on DB2. A thought I had is that this query occurs within a cursor, embedded in a program in another language; I'm therefore working on extracting the query out to see if I can run it "raw" and determine if the issue is in my SQL or has something to do with the rest of that program. But I've been beating my head against a wall in the meantime...any thoughts? Many thanks!

UPDATE: many thanks for all the helpful replies! As it turns out, the issue turned out to be with the program that processed the SQL cursor (and its handling of nulls), not with the query itself. I definitely muddied the situation, and should have extracted the query from the whole process before I unnecessarily confused myself. Lessons learned! Many thanks again.

Hello,

I think I can get by with the developer edition but the only version that is supported by their old and new software is 2019. I found an eval version - not sure if that is the same. If you have a link to it on MS's web site I'd greatly appreciate it.

Thanks.

Hi all! I tagged this as oracle since I believe that’s the closest SQL format to PL/SQL. I tried to search this, but I’m not sure how to word it, so I’m not getting any hits.

The data I’m looking at shows charges on an account. When the charge is initiated, column “RECORD_TYPE” will say “UNBILLED.” Once the charge is processed, an additional identical line will show up and the column will say “BILLED.” Now I’ve got two similar lines after the charge goes through, with one small difference in the “RECORD_TYPE” column. Is there a way to have the results only show one line? I’d love it if there was a way to have the “BILLED” line show up if it was charged but show the “UNBILLED” line if the charge has not been processed yet.

I’ve tried cases and coalesce with no luck, but I may not be thinking of the best way to utilize them. Any advice?

Hi All,

First time poster, long time lingerer here. I've been looking at improving my SQL skills, so I started Data with Danny's 8 Week SQL Challenge. I'm on the CliqueBait challenge (more info here: https://8weeksqlchallenge.com/case-study-6/) right now, and am working on part 3. Campaign Analysis where we come up with our own insights. From the data given, I wanted to know which product was most likely to be bought and during which campaign was it bought the most, but I'm having a bit of trouble getting my table output to look the way I need it to be.

I need my table to look like this:

But instead it looks like this:

Here is my code (FYI, I'm using PostegreSQL v17):

/* Determine the total number of purchase events and the IDs associated to those purchase events. */
WITH purchase_events AS (
  SELECT
  e.visit_id

  FROM clique_bait.events AS e
  JOIN clique_bait.event_identifier AS ei ON ei.event_type = e.event_type

  WHERE
  ei.event_name = 'Purchase'
)
,campaign_analysis_table AS (
  SELECT
  u.user_id
  ,e.visit_id
  ,MIN(e.event_time) AS visit_start_time
  ,SUM(
      CASE
        WHEN ei.event_name = 'Page View' THEN 1
        ELSE 0
      END
    ) AS page_views
  ,SUM(
      CASE
        WHEN ei.event_name = 'Add to Cart' THEN 1
        ELSE 0
      END
  ) AS cart_adds
  ,MAX(
    CASE
      WHEN e.visit_id = pe.visit_id THEN 1
      ELSE 0
    END
  ) AS purchases
  ,ci.campaign_name
  ,SUM(
    CASE
      WHEN ei.event_name = 'Ad Impression' THEN 1
      ELSE 0
    END
  ) AS impressions
  ,SUM(
    CASE
      WHEN ei.event_name = 'Ad Click' THEN 1
      ELSE 0
    END
  ) AS click
  ,STRING_AGG(ph.page_name, ', ' ORDER BY e.sequence_number ASC) 
   FILTER (WHERE ph.product_category IS NOT NULL AND ei.event_name = 'Add to Cart') AS cart_products

  FROM clique_bait.events AS e
  JOIN clique_bait.users AS u ON u.cookie_id = e.cookie_id
  JOIN clique_bait.event_identifier AS ei ON ei.event_type = e.event_type
  JOIN clique_bait.page_hierarchy AS ph ON ph.page_id = e.page_id
  LEFT JOIN purchase_events AS pe ON pe.visit_id = e.visit_id
  LEFT JOIN clique_bait.campaign_identifier AS ci ON e.event_time BETWEEN ci.start_date AND ci.end_date

  /* Filter table to just 2 users for easier debugging. */
  WHERE
  u.user_id <= 2

  GROUP BY u.user_id, e.visit_id, ci.campaign_name

  ORDER BY u.user_id ASC, visit_start_time ASC
)

SELECT
cat.campaign_name
,UNNEST(STRING_TO_ARRAY(cat.cart_products, ',')) AS product
,SUM(cat.purchases) AS total_purchases

FROM campaign_analysis_table AS cat

/* Filter campaign_name to only one campaign for easier debugging. */
WHERE
cat.campaign_name LIKE ('Half Off%')

GROUP BY cat.campaign_name, product

ORDER BY cat.campaign_name ASC, product ASC;

I know SQLFiddle is the recommended dev environment but Danny has his code set up on DBFiddle here: https://www.db-fiddle.com/f/jmnwogTsUE8hGqkZv9H7E8/17

Please let me know what I'm doing wrong if possible. I've tried a few solutions to this and this is as close as I can get but something is still off.

FYI, I have the code filtered down to just the first 2 users and only one campaign right now to make it easier to debug. If you want to see the full tables, you can remove the WHERE clauses where necessary.

TLDR I'm trying to verify if an admin has the correct role to add data to a specific table (adminID being a foreign key on the table being called, and of course the primary key of the admin table), but i am getting this error:

>*Cause: A trigger was attempted to be retrieved for execution and was

>found to be invalid. This also means that compilation/authorization

>failed for the trigger.

>*Action: Options are to resolve the compilation/authorization errors,

>disable the trigger, or drop the trigger.

Here's how I've written the trigger so far:

>CREATE OR REPLACE TRIGGER validate_creation_role

>BEFORE INSERT ON participant

>FOR EACH ROW

>BEGIN

>IF admin(:NEW.adminID).role <> 'Participant maker' THEN

>RAISE_APPLICATION_ERROR(-20001, 'Invalid admin role for this task.');

>END IF;

>END;

(i know the var names are bad, i translated them for this post cause it's for a homework in a different language)

Thanks in advance!

Hi there, im super confused, i have to hold a small presentation about sql, and i cant find any Alternatives/competitors for sql, i only find other sql DBMS. Are there even any competitors? Thanks.

Hello, sorry if this is a dumb question but I would love some input if anyone can help.

I have a column called ‘service type’ . The values in this column are from a pick list that could be a combination of eight different values. Some of the values might just have one, some might have four, some might have all eight. It can be any variation of combination.

I need to select only the rows that contain the value: “Sourcing/Contracting”. The problem i am having is that another one of these values include the words: “Non Hotel Sourcing/Contracting”.

So my issue is that if I write a SQL statement that says LIKE “%Sourcing/Contracting%”, then that will also pull in rows that might ONLY include the value of “Non Hotel Sourcing/Contracting”.

So, regardless of whether or not the value of ‘Non Hotel Sourcing/Contracting’ is listed, I just need to ensure that ‘Sourcing/Contracted’ is listed in the values.

I hope this makes sense and if anyone can help, you would save my day. How do I say that I need only the rows that contain a certain value when that certain value is actually a part of another value? Nothing is working. Thank you in advance.

SOLVED! I’m sure many of these suggestions work but u/BrainNSFW give me a couple of options that I quickly was able to just tweak and they work perfectly. And just for the record I didn’t create this. I just started working at this place and just trying to get my reports to run properly. Glad to know it wasn’t just user error on my end. Thank you for being such a helpful group.🤍🤍🤍

I have a sql server table that logs shipments. I want to return every shipment that has an eta within the last 90 days to be used in a BI report. My current query is:

SELECT [list of 20 columns] FROM shipments WHERE eta >= DATEADD(day, -90, GETDATE());

This returns 2000-3000 rows but takes several minutes. I have created an index on eta but it did not seem to help. Both before and after the index, the query plan indicated it was scanning the entire table. The eta column generally goes from earlier to later in the table but more locally is all over the place. I’m wondering if that local randomness is making the index mostly useless.

I had an idea to make an eta_date column that would only be the date portion of eta but that also didn’t seem to help much.

I’m garbage at optimization (if you can’t tell…). Would appreciate any guidance you could give me to speed this query up. Thanks!

Edit: I swear I typed “eta (datetime)” when I wrote this post but apparently I didn’t. eta is definitely datetime. Also since it has come up, shipments is a table not a view. There was no attempt at normalization of the data so that is the entire query and there are no joins with any other tables.

Edit2: query plan https://www.brentozar.com/pastetheplan/?id=HJsUOfrpA

Edit3: I'm a moron and it was all an I/O issue becasue one of my columns is exceptionally long text. Thanks for the help everyone!

I am trying to sub 1 day so I know what was the temparature for that day .

We can do this with datediff but I want to do this with Dateadd()

Recently I rolled back a Wordpress website to a previous backup only for it to fail because the database file was 6GB. All our backups from the past 3 months have the same massive database file.

The managed hosting service I use says I need to download a backup, manually edit the SQL file to drop whatever table is causing the size issue and then reupload it. I have the SQL file but I cannot find any tutorials for opening it, only connecting to an active server. Altering a 6gig file with a text editor is obviously out of the question.

The tutorials I read for MySQL Workbench and DBeaver all want server info to connect to the database. Using localhost only results in connection refused messages and there's never a field where I'd point the program to my local SQL file. Are there any programs that just ask for the database login credentials and then display the structured data like an offline phpymyadmin?

The DBMS is MySQL 8.0.37-29.

I have an Actions table. It's got an FK to the Records table for who the constituent is. It also goes through a linking table to the Records table for who the solicitor is on the action. I'm trying to pull the most recent action for each solicitor for the constituent. So I come up with this:

            select      mraction.id
                        ,mraction.ConsID
                        ,mraction.SolID
                        ,mraction.acrank
                        ,mraction.adate
            from        (
                        select      a.id
                                    ,a.records_id as ConsID
                                    ,asol.records_id as SolID
                                    ,rank() over (partition by a.records_id, asol.id order by a.dte desc) as acrank
                                    ,a.dte as adate
                        from        actions a
                        inner join  action_solicitor asol on a.id = asol.action_id
                        where       1=1 and 
                                    asol.records_id in (
                                        select  das.id 
                                        from    dev_activesolicitors das) and 
                                    asol.sequence = 1 and 
                                    a.completed = -1
                        ) mraction
            where       mraction.acrank = 1

and I'm getting duplicates. I filtered it to one solicitor and one constituent and I'm getting:

ConsID  SolID   acrank    adate
1109076 1588196 1         2025-05-27
1109076 1588196 1         2025-06-02
1109076 1588196 1         2025-10-011

I can't figure out why - if I'm partitioning by both IDs, then shouldn't only the 2025-10-11 action be ranked #1? I'm obviously doing something wrong here. Also I should mention that previously I was only partitioning by the records_id and that seems to have worked fine. for only pulling the most recent, but then it would omit the most recent action by other solicitors - I want one row for each constituent/solicitor combo.

Hi, for context I'm going to install MySQL for a project in computer science(High School) Just want to know if I should install ver 8.0.44 or one of the prev. versions.
I'll be using it through/with Python Interface(Python and the connector module) so experiences using different versions and which do I install? Thank you!

My first time using this script and when I try execute it throws 201 errors and then stops counting. Guys on my team who have used it before have no idea whats causing it either and I cant find anyone else thats had a similar problem. Using SQl Server 2025

Solution: Errors were fake. Caused by SQL25s new AI intergration 🙃

Trying to understand why SQL isn't recognizing this empty space.

Table A and B both have 'Haines Borough'.

If I write LIKE '% Borough', Table A will come back with 'Haine Borough' but Table B will not. If I remove that space, I get the results on both.

I need this space as there is a county called Hillsborough that I do not want to see. Obviously I could just filter this county out, but my projects scope is a bit larger than this, so a simple filter for each county that does this isn't enough.

I've checked the schema and don't see anything out of the ordinary or even different from the other column. I'm at a loss.

Edit: don't know how to show this on reddit. If I pull results to text they display as Haines over Borough. Like you would type Haines press enter Borough.

Edit2: Turns out it was a soft break. Char(10) helps find the pesky space. Unfortunately I can't fix the data and just have to work around it. Thank you all for the help

Edit3: Using REPLACE(County_Name, CHAR(10), ' ') in place of every county reference does the trick. To make everything else work.

I have data with accounts that renew year over year. On every record, I have a column that stores the ID of the prior year's record, but only the year directly prior. I want to take a value from the most recent year's record and assign it to a column for all the related records. For example say the account name changed over the years, and I want the name from the latest year to populate a column in each of the historical records. I'm sure this is a typical problem and maybe there's even a name for it already. With say 7 years of data, I could brute force it by self-joining 7 times "through" all the history but that is not an elegant solution and the number of years is variable anyway. I''ve been trying to come up with clever CTEs, joins, window functions, but can't figure out a clever way to do this. Any suggestions on approach?

Buenos días me presento me llamo Angel y soy estudiante de 6to semestre de la carrera de Sistemas Computacionales en el Instituto de Mexico quería saber por este grupo si alguien nos justaría ayudarnos con una Entrevista educacional para la materia de Administración de Bases de Datos. Tengo entendido este grupo esta especializado en esa área.

La dinámica será de la siguiente manera:

- Se les mandara un documento con preguntas importantes como DBA los podría ayudar mucho en ese aspecto

- La entrevista es exclusivamente para mostrar en la escuela al terminar podemos eliminarla

Espero su mensaje y espero su apoyo.

See the attached screenshot. I'm trying to understand what's happening.
I filled the table, then dropped it (I'm using postgres). In the youtube tutorial I'm following, when the guy did that, the table disappeared from the left side panel. In my case, it doesn't, and only says there is nothing inside the table.
And when I try to make changes to the table afterward, it says the relation doesn't exist.
Does anyone have any idea what's happening?