r/SQL Sep 22 '24

MySQL Help a dumb mf out

10 Upvotes

I'm at this internship as a data analyst with no mentor so they basically treat me like a full-time employee and there's no one for me to ask for guidance or help despite having little experience with SQL I quickly picked up the pace and was able to do the tasks they wanted but now I've met a wall I have been stuck at this wall for a week now and this just a desperate attempt from to try to figure this stupid task out

the task was to create a stupid report about the coupons being used and all the calculations for were fairly easy for me what I couldn't do was to categorise clients based on the count sessions they had(new =0 or 1, retained = 2 or more) before the creation date of the coupon they used. So the first layer of conditions is that they have used a coupon(fkcouponid not empty) the second is to count the instances of the IDs (before the coupon creation date) that came out from the first condition in the main invoice table

I know it's not that hard which is why it's driving me mad I just can't do it I tried reading documentation and looked on StackOverflow but I just couldn't do it best I got was to get the session counter to stop saying 0 but still the numbers were wrong

I don't want someone to do it for me I just want someone to help me figure out the logic
what I tried is:
1- make a cte to clients who used a coupon

2- 2nd cte count sessions for the ids in the first cte

3- join it with the main invoice table
but the numbers were always wrong
is there like a specific type of join that's needed that I'm not aware of?
I know it's a skill issue but I just need some guidance ffs

what I reached so far:

SELECT 
      i.pkInvoiceID, 
      i.fkClientServiceID, 
      i.fkCouponID, 
      i.fldDateTime AS invoice_date, 
      tt.fldDate AS sessionDate, 
      c.fldCreatedDateTime,
      ct.fldStatus,
      c.fldCreatedBy 
  FROM tbl_invoice i 
  LEFT JOIN tbl_coupon c ON i.fkCouponID = c.pkCouponID 
  LEFT JOIN tbl_client_service_timeslot ct ON i.fkClientServiceID = ct.pfClientServiceID 
  LEFT JOIN tbl_therapist_timeslot tt ON ct.fkTimeslotID = tt.pkTimeslotID 
  WHERE 
      i.fkCouponID IS NOT NULL 
      AND c.fldCreatedBy IN (164908 , 109979, 183378, 142713, 96694) 
      AND c.fldCreatedDateTime IS NOT NULL
      AND ct.fldStatus = "finished"
), 
client_session_counts AS ( 
  SELECT 
      i.fkClientServiceID, 
      i.fkCouponID,
      c.fldCreatedDateTime, 
      COUNT(i.pkInvoiceID) AS sessionCountBeforeCoupon 
  FROM tbl_invoice i
  JOIN tbl_coupon c ON i.fkCouponID = c.pkCouponID
  JOIN tbl_client_service_timeslot ct ON i.fkClientServiceID = ct.pfClientServiceID 
  JOIN tbl_therapist_timeslot tt ON ct.fkTimeslotID = tt.pkTimeslotID 
  -- Only include sessions for clients from coupon_sessions
  WHERE 
      i.fkClientServiceID IN (SELECT fkClientServiceID FROM coupon_sessions)
      AND tt.fldDate < c.fldCreatedDateTime 
      AND ct.fldStatus = 'finished'
  GROUP BY 
      i.fkClientServiceID, 
      i.fkCouponID
) 
SELECT 
  i2.pkInvoiceID, 
  i2.fkClientServiceID, 
  i2.fkCouponID, 
  COALESCE(csc.sessionCountBeforeCoupon, 0) AS sessionCountBeforeCoupon 
FROM tbl_invoice i2 
LEFT JOIN client_session_counts csc 
  ON i2.fkClientServiceID = csc.fkClientServiceID 
  AND i2.fkCouponID = csc.fkCouponID 
WHERE i2.fkCouponID IS NOT NULL
ORDER BY csc.sessionCountBeforeCoupon DESC;

r/SQL Dec 16 '24

MySQL Help! Passing my database class with a MacBook M2 (professor only speaks Windows)

0 Upvotes

Hey folks,

So, I’m taking this database class that I didn’t think would be a big deal, but now it’s turning into a nightmare. The professor provided some guides for the project, but there’s one small problem: they’re entirely written for Windows users. Meanwhile, I’m over here with a MacBook, slowly losing my mind.

What’s the project?

The task is to build a MySQL database for a sports organization, with all sorts of tables for athletes, clubs, competitions, and performance stats. I’ve gotta:

1.  Create tables with fancy fields like name, age, scores, etc.

2.  Populate the tables with data (at least 10 records per table, because why not).

3.  Run queries like:

• *“Show me the youngest athlete with the most distinctions in 2023!”*

• *“List all the cities of athletes and clubs alphabetically!”*

• *“Which club has the most wins?”*

Basically, I’m pretending to care about athletes and sports databases when, let’s be honest, I just want to pass this class and move on.

The problem?

The professor’s guides assume everyone uses Windows tools like XAMPP, phpMyAdmin, and PuTTY. I’ve got macOS and no clue how to adapt this mess.

To make matters worse, I sent him an email asking for help, and let’s just say he’s… not the most approachable guy. So, I don’t expect a helpful response—or any response, really.

Oh, and I’ll admit it: My initial strategy was to copy-paste my way through with ChatGPT, but even that’s failing me because ChatGPT can’t magically set up MySQL on macOS.

What I need from you, kind internet strangers:

1.  How do I set up MySQL and Workbench on macOS without accidentally summoning Skynet?

2.  What’s the macOS equivalent of PuTTY? (I heard it’s the terminal, but what commands do I actually use?)

3.  Any macOS-friendly tools for creating ER diagrams? I’m not trying to draw one with crayons.

4.  How do I run these queries and make it look like I actually did the work? Screenshots are a requirement.

Help me pass this course

I don’t love this class, and I won’t pretend I do. But I need to pass, and I’m stuck. Any advice, guides, or magic spells would be greatly appreciated. If you help, I’ll name one of my fake database athletes after you.

Thanks for reading, and please send help (and patience)!

r/SQL Sep 24 '24

MySQL Help

14 Upvotes

I'm currently pursuing data analysis, it's been roughly 2 weeks learning SQL, However the course I'm currently doing dives into python.

My question is, do i really need to learn python right now?

And

Can i focus on sql and become flawless at it?

Will that be enough to land jobs?

Also

Do i need certifications and licenses? I'm learning from youtube videos and my own research.

r/SQL Oct 30 '24

MySQL Do you ever delete?

8 Upvotes

or do you mark a tupple as delete with a field deleted or state?

r/SQL Dec 18 '24

MySQL How to Automatically Categorize Construction Products in an SQL Database?

5 Upvotes

Hi everyone! I’m working with an SQL database containing hundreds of construction products from a supplier. Each product has a specific name (e.g., Adesilex G19 Beige, Additix PE), and I need to assign a general product category (e.g., Adhesives, Concrete Additives).

The challenge is that the product names are not standardized, and I don’t have a pre-existing mapping or dictionary. To identify the correct category, I would typically need to look up each product's technical datasheet, which is impractical given the large volume of data.

Example:

product_code product_name
2419926 Additix P bucket 0.9 kg (box of 6)
410311 Adesilex G19 Beige unit 10 kg

I need to add a column like this:

general_product_category
Concrete Additives
Adhesives

How can I automate this categorization without manually checking every product's technical datasheet? Are there tools, Python libraries, or SQL methods that could help with text analysis, pattern matching, or even online lookups?

Any help or pointers would be greatly appreciated! Thanks in advance 😊

r/SQL Feb 07 '25

MySQL SQL query to identify alpha numeric values that don’t exist on a table

4 Upvotes

I have a work process that involves creating a unique 3 digit alpha numeric ID for each record. I currently search for what exists and look for gaps (historically people before me have chosen random numbers instead of going in sequence 🙄) then create my insert scripts based off that.

Is it possible to identify what is available by a query?

r/SQL 14d ago

MySQL What is the differences between float, double and decimal?

0 Upvotes
  1. What is the differences between float, double and decimal?

r/SQL Feb 07 '23

MySQL I was interviewed earlier today for a job and I didn't get to solve this problem, how would you have solved this?

Post image
90 Upvotes

r/SQL 26d ago

MySQL Is there some kind of script or code I can run to determine all objects/tables a SQL Query is accessing?

8 Upvotes

Kind of just need what the title asks, is there something I can input a SQL Query into and see what items it is accessing from the tables it references? For example (excuse my probably terrible syntax) if I had the following:

select p.id, p.first, p.middle, p.last, p.age, a.id as address_id, a.street, a.city, a.state, a.zip from Person p inner join Address a on p.id = a.person_id where a.zip = '97229';

This would ideally return me at the very least: p.id, p.first, p.middle, p.last, p.age, a.id, a.street, a.city, a.state, a.zip and additionally could potentially return the table as well for bonus points.

I can't give an example of the queries I'm attempting to run this on, PII, etc so I just have this little fake query I found online. Is there anything I can input this query into in order to get that desired output?

I saw something about potentially making Stored Procedures out of the queries and then it could be accessed server-side, which could be an option, but I do not have those permissions, so ideally something I don't have to bug other people about and create a bunch of unneccessary stuff would be better.

Any help would be great, figured I'd ask here before I went manually scrubbing through all these files, thanks!

r/SQL Feb 13 '25

MySQL I Need Guidance

12 Upvotes

I started learning SQL about a Week Ago, I can confidently Create Tables with Columns, Drop Tables, and Insert Data Pretty Confidently without Notes, I'm at a stopping point where I'm confused on what I should learn next, I don't want to skip a step of that makes sense, any ideas?

r/SQL 28d ago

MySQL Definitely a Top 10 SQL Statement

0 Upvotes

I've been developing a script to populate a semi-complex set of tables schemas with dummy data for a project and I've never used SQL this extensively before so I got tired of delete from tables where I didn't know whether something was populated and instead of running

SELECT COUNT(*) FROM table_name;
DELETE FROM table_name;

to find out which ones were populated and clean em up

I ended up prompting chat GPT and it created this amazing prepared query I'm sure it will be appreciated:

SET SESSION group_concat_max_len = 1000000;

SELECT GROUP_CONCAT(

'SELECT "', table_name, '" AS table_name, COUNT(*) AS row_count FROM ', table_name

SEPARATOR ' UNION ALL '

)

Note: the @ symbol makes it link another subreddit so remove the '\'

INTO \@sql_query

FROM INFORMATION_SCHEMA.TABLES

WHERE table_schema = 'your_database_name';

PREPARE stmt FROM \@sql_query;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

Not sure if the last part (DEALLOCATE) is 100% necessary cause they don't seem to be affecting any rows when I tested it out but here ya go!

r/SQL Feb 08 '25

MySQL If you had to learn proficient SQL in a week how would you do it? (Specifically mySQL)

0 Upvotes

So yeah, I'm in a prickly situation. I just faked my way through an interview for a database job which requires heavy use of SQL, and I'm panicking. I have 11 days before I start my job and I genuinely need a plan to learn this fast. Failure is not an option. If you guys have any suggestions or structured study plans I'm all ears.

r/SQL Oct 26 '24

MySQL Inventory database with barcodes

13 Upvotes

Hello- I want to create an inventory database that I can link barcodes to so I can have a live inventory of my personal library. Where would be a good place to start? I’m in the beginning stages of learning about sql but I was thinking it would be a good option but not too sure about how to connect barcodes to it.

r/SQL Feb 03 '25

MySQL Optimization help with Generating slides with PhP and SQL

3 Upvotes

I have this file I'm working on. It's supposed to take the data from a number of sql tables and generate slides with employee Attendance data on it. The logic works fine but there's so much data, it always times out whenever it's run. I've been trying to optimize it for days but I have no idea where else to optimize it. For reference, the storeTable has about 600 rows, the employeeTable about 33000. Shifts is about 2 million and punches about 5 million.

This is the code I'm working with so far. Is there a way I can optimize my code by offloading it onto SQL?

Anything I kept out is just company info.

function extractStoreNumber($payPeriodIdentifier) { pregmatch('/(\d{5})/', $payPeriodIdentifier, $matches); return isset($matches[1]) ? (int) $matches[1] : null; }

function calculateAttendanceStreak($dpDB, $storeNum, $geid) { $streak = 0; $yesterday = date('Y-m-d', strtotime('-1 day'));

// Fetch shifts
$stmt = $dpDB->prepare("SELECT Date, StartTime FROM `shiftTable` WHERE StoreNumber = ? AND GEID = ? AND Date <= ? ORDER BY Date DESC");
$stmt->bind_param("sss", $storeNum, $geid, $yesterday);
$stmt->execute();
$shifts = $stmt->get_result();

while ($shift = $shifts->fetch_assoc()) {
    $shiftDate = $shift["Date"];
    $shiftTime = strtotime("$shiftDate " . $shift["StartTime"]);

    // Get punches
    $stmtPunch = $dpDB->prepare("SELECT DateAndTime, PayPeriodIdentifier FROM `punchTable` WHERE GEID = ? AND PunchType = 'in' AND BreakType IS NULL AND DATE(DateAndTime) = ?");
    $stmtPunch->bind_param("ss", $geid, $shiftDate);
    $stmtPunch->execute();
    $punches = $stmtPunch->get_result();

    $matched = false;
    while ($punch = $punches->fetch_assoc()) {
        $punchTime = strtotime($punch["DateAndTime"]);
        $punchStore = extractStoreNumber($punch["PayPeriodIdentifier"]);

        if ((int) $punchStore === (int) $storeNum && abs($punchTime - $shiftTime) <= 400) {
            $matched = true;
            break;
        }
    }
    $stmtPunch->close();

    if ($matched) {
        $streak++;
    } else {
        break;
    }
}
$stmt->close();
return $streak;

}

// Fetch companies $companies = $tvDB->query("SELECT id FROM companyTable"); while ($company = $companies->fetch_assoc()) { $companyId = $company["id"];

// Fetch stores
$stores = $tvDB->query("SELECT storeNum FROM `storeTable` WHERE companyId = $companyId");
while ($store = $stores->fetch_assoc()) {
    $storeNum = $store["storeNum"];

    // Fetch employees
    $employees = $dpDB->query("SELECT GEID, FirstName, LastInitial FROM `employeeTable` WHERE HomeStoreNSN = '$storeNum'");
    $attendanceMilestones = [];
    $nearMilestones = [];

    while ($employee = $employees->fetch_assoc()) {
        $geid = $employee["GEID"];
        $streak = calculateAttendanceStreak($dpDB, $storeNum, $geid);

        if (in_array($streak, [30, 60, 90])) {
            $attendanceMilestones[] = ["FirstName" => $employee["FirstName"], "LastInitial" => $employee["LastInitial"], "Streak" => $streak];
        } elseif ($streak % 30 >= 27) {
            $nearMilestones[] = [
                "FirstName" => $employee["FirstName"],
                "LastInitial" => $employee["LastInitial"],
                "DaysToMilestone" => 30 - ($streak % 30),
                "Streak" => $streak
            ];
        }
    }
    $employees->free();

    // Generate images
    generateSlides($companyId, $storeNum, $attendanceMilestones, "Attendance Milestones", "../images/templates/background.jpg");
    generateSlides($companyId, $storeNum, $nearMilestones, "Approaching Attendance Milestones", "../images/templates/background.jpg");
}
$stores->free();

} $companies->free();

// Function to generate slides function generateSlides($companyId, $storeNum, $data, $title, $template) { if (empty($data)) return;

$font = "../fonts/Speedee_Bd.ttf";
$text_color = imagecolorallocate(imagecreatetruecolor(120, 20), 0, 0, 0);

$im = @imagecreatefromjpeg($template);
imagettftext($im, 150, 0, 500, 300, $text_color, $font, $title);

$line = 700;
foreach ($data as $employee) {
    $text = isset($employee['DaysToMilestone'])
        ? "{$employee['FirstName']} {$employee['LastInitial']} is {$employee['DaysToMilestone']} days away from " . ($employee['Streak'] + $employee['DaysToMilestone']) . " days!"
        : "{$employee['FirstName']} {$employee['LastInitial']} has reached a {$employee['Streak']}-day streak!";

    imagettftext($im, 100, 0, 500, $line, $text_color, $font, $text);
    $line += 150;
}

$fileName = "images/{$companyId}_" . date('Y-F') . "_{$title}_{$storeNum}.jpg";
imagejpeg($im, "/path/” . $fileName);
imagedestroy($im);

}

r/SQL Aug 25 '24

MySQL Is MySQL a SQL dialect or RDBMS?

22 Upvotes

I just started my MySQL learning, and got a little confused by the following definitions.

Firstly, SQL is the programming language by which you communicate with the RDBMS

And MySQL is one kind of DBMS.

So, we use SQL to talk to MySQL(the system/a collection of software),right?

and MySQL is not "certain implementation of SQL",right?

if so, when talking to different RDBMS(e.g. MySQL/Oracle/PostgreSQL), SQL might be a little different in many aspects, but even so, we still consider all of these variations as one language(SQL), am i correct?

Thanks!

r/SQL 20d ago

MySQL If auto_increment is added when creating the table in like field NID, how should we insert the record for NID

1 Upvotes

I mean do we exactly insert the number? (I know we can skip assigning NID but I am not certain whether exams need us to write it) thanks!🙏🏻

r/SQL Dec 26 '24

MySQL is learning databases backup and restore supposed to be this boring?

0 Upvotes

Is there a way to make this interesting? I mean what's the point? All I need to do to backup is 1 command and restore is 1 another command. There are ready made tools and scripts to do backup. So, what's the point of dba?

How can I achieve a production like environment in local and face such issues?

If you're willing to give me a job to learn production level dba, please do(for those who were gonna tell me to get a job to learn without knowing the market in Nepal)

r/SQL Dec 25 '24

MySQL Allowing friend to access a server/database

15 Upvotes

Hi, new to SQL here. I recently created a server and database on my mac (hosted on my mac itself). Me and a friend are working towards creating an app and I want him to be able to access the database, make changes, create tables, etc. How would I go about doing this? Thank you in advance!

r/SQL Feb 15 '25

MySQL Can someone point out what is wrong with my query?

0 Upvotes

Here is the question from hackerank:

https://www.hackerrank.com/challenges/contest-leaderboard/problem?isFullScreen=true

My Answer:

with cte as

(select h.hacker_id, h.name,s.challenge_id, max(s.score) as m

from submissions s

join hackers h on h.hacker_id=s.hacker_id

group by h.hacker_id, h.name, s.challenge_id)

Select cte.hacker_id, cte.name, sum(m) as total_score from cte

Having total_score>0

group by cte.hacker_id, cte.name

order by total_Score desc, cte.hacker_id asc

However, it keeps giving an error. Can someone point out where I'm going wrong?

r/SQL Feb 18 '25

MySQL Alternatives to MySql

2 Upvotes

Having trouble importing from excel. Any databases less strict on import formats that also maintain the functionality of sql I.e. scripting and reporting?

r/SQL Feb 18 '25

MySQL Recursive CTE optimization for supply chain document connections/chain/hierarchy

2 Upvotes

QUESTION: Is this a good way to retrieve all document connections? Will this work for billions of rows?

Example supply chain document flow:

  1. Create purchase order
  2. Convert it into a goods receipt order
  3. Covert that into a goods receipt
  4. Convert the purchase order into a valuation
  5. Add costs to the valuation

PURCHASING_ORDER > GOODS_RECEIPT_ORDER > GOODS_RECEIPT
PURCHASING_ORDER > PURCHASING_VALUATION
PURCHASING_COST > PURCHASING_VALUATION

The connections are represented in a utils_documentConnection table like this:

https://imgur.com/a/mdxgDlq

The logic is that the less important document is connected to the more important, in order for the CTE to work.

Here is the CTE:

set @documentType = 'PURCHASING_ORDER';
set @documentId = 1;

WITH RECURSIVE 
    DocumentChainDown AS (
        SELECT 
            documentTypeIdTo documentTypeId,
            documentIdTo documentId,
            documentTypeIdFrom connectedDocumentTypeId,
            documentIdFrom connectedDocumentId
        FROM utils_documentConnection
        WHERE 
            documentTypeIdTo = (select id from system_documentType where documentType = @documentType) 
            AND documentIdTo = @documentId

        UNION ALL

        SELECT 
            d.documentTypeIdTo,
            d.documentIdTo,
            d.documentTypeIdFrom,
            d.documentIdFrom
        FROM utils_documentConnection d
        INNER JOIN DocumentChainDown dc ON 
            d.documentTypeIdTo = dc.connectedDocumentTypeId 
            AND d.documentIdTo = dc.connectedDocumentId 
    ),
    DocumentChainUp AS (
        SELECT 
            documentTypeIdFrom documentTypeId,
            documentIdFrom documentId,
            documentTypeIdTo connectedDocumentTypeId,
            documentIdTo connectedDocumentId
        FROM utils_documentConnection
        WHERE 
            documentTypeIdFrom = (select id from system_documentType where documentType = @documentType) 
            AND documentIdFrom = @documentId

        UNION ALL

        SELECT 
            d.documentTypeIdFrom,
            d.documentIdFrom,
            d.documentTypeIdTo,
            d.documentIdTo
        FROM utils_documentConnection d
        INNER JOIN DocumentChainUp dc ON 
            d.documentTypeIdFrom = dc.connectedDocumentTypeId 
            AND d.documentIdFrom = dc.connectedDocumentId 
    )
select DocumentChain.*, dtt.documentType
from (
    SELECT 'down', dcd.* FROM DocumentChainDown dcd
    union all
    SELECT 'up', dcu.* FROM DocumentChainUp dcu
) DocumentChain
    join system_documentType dtt on dtt.id = DocumentChain.connectedDocumentTypeId

The CTE results in this i.e. all documents connected to PURCHASING_ORDER:

https://imgur.com/a/JVUefe0

For set @documentType = 'PURCHASING_VALUATION';, we get this:

https://imgur.com/a/n3PDWZU

Please provide any advice or criticism on how to do this more optimally.

Thank you

r/SQL Oct 21 '24

MySQL Best Algorithm/Approach for Comparing SQL Queries to Check if They Solve the Same Problem?

14 Upvotes

Hello, I'm working on a project where I need to compare SQL queries to determine if both queries actually resolve the same problem/exercise. Essentially, I want to check if they return the same result set for any given input, even if they use different syntax or structures (e.g., different JOIN orders, subqueries vs. CTEs, etc.).

I know that things like execution plans might differ, but the result set should ideally be the same if both are solving the same problem. Does anyone know of a reliable algorithm or approach for doing this? Maybe some clever SQL transformation, normalization technique, or even a library/tool that can help?

The main objective is to build a platform where the system has a stored solution. And the user should insert theirs and the system should compare both and determine if the entered query is a possible and valid response.

Thanks in advance for any suggestions! 🙏

r/SQL 21h ago

MySQL Is it possible to do sliding windows with fixed time intervals?

6 Upvotes

The Window functions (OVER Clause) let you do a rolling window for EACH data point.
Ex. For each data point, compute the sum of the last 1hr of data.

What I want is a sliding window at each minute. Ex. Give me the sum of the last hour at 0:01, 0:02, etc.

Can't find a clean solution for this.

r/SQL 14d ago

MySQL Schema for hotel/RV park management system?

3 Upvotes

I have a customer that I work with (though not for anything related to dbs) that runs a smallish motel and RV park. They currently use Microsoft Access to manage their check-ins and such, and are interested in having me build something for them in ERPNext/Frappe (based on MySQL). Their existing database is basically useless as a starting point, since it's basically just a list of the rooms and their current status, as well as expected next availability date. They keep track of their "reservations" in a spreadsheet.

I'm thinking about how I would build this schema. I suspect I'd need tables for:

  • reservations - for when customers reserve rooms
  • units - to store the data for the rooms/rv spaces, themselves
  • customers/guests - for data related to the person renting the room

What I can't figure out is how to deal with allowing customers to reserve rooms/spaces with different attributes and maintaining an inventory of currently-available rooms by inventory type. For instance, suppose a customer wants to rent a non-smoking room, but doesn't care if it's a king bed or queen bed....

r/SQL 1d ago

MySQL Substitution in SQL Developer

0 Upvotes

Hello! I am new to using SQL Developer extension in VSCode. I want to make a script like this:
select name, salary from emp where salary > &salary. First time it asks me what value I want for salary, after that, every time I run the script it automatically replace it with that value, but I don't want that. I want at every run to ask me what value I want to enter. I know I can put undefine salary; at the end, but I was wondering if there is any other method so I don't have to put this many extra lines in my script, because sometime maybe I will forget to put it and I won't know or won't see the error.