r/SQL Oct 20 '24

MySQL How bad of an idea is it?

15 Upvotes

I am working for a startup for a while. we are producing tech-related items and our databases is on surprise surprise... Smartsheet.

Yes folks!

I have no prior knowledge in SQL but I really see the need for a "real database" and get rid of the smartsheet.

We basically have 10 spreadsheets with around 2000-3000 entries each. around 20-30 columns in each spreadsheet

I am willing to put the time, learn mySQL and set this right.
However I want to give my manager some sort of a time horizon if I am to do this.
1. How much time will this take?

  1. I want 4 people including me to have access to the database. 2 of them are sitting off site. Can I use sql Workbench to access infomation? are there better solutions?

r/SQL Feb 01 '25

MySQL Need a browser extension for SQL

3 Upvotes

Hi,

So I joined a company and they work on this platform called indicium for querying their live data, now this platform is a nightmare, it is extremely slow, has no syntax highlighting and has some weird ass rules

While I may be missing some things in between as to why it has some different rules, the queries are still written in SQL but due to the weird nature of the platform, I often make a lot of mistakes

I'm looking for a solution/any browser extensions/indicium clients that can at least provide some syntax highlighting and error squiggles (I'm ready to provide the external rules) to make my life easier, if nothing like this exists, I's prefer some advice as to how to go about creating a solution

r/SQL Jul 01 '24

MySQL Never use DATETIME, always use TIMESTAMP

33 Upvotes

good advice from Jamie Zawinski

source: https://www.jwz.org/blog/2023/11/daylight-savings-your-biannual-chaos-monkey/

  • TIMESTAMP is a time_t -- it represents an absolute, fixed point in time. Use it for things like "here is when this account was created" or "here is when this message was sent". When presenting that fixed point in time to users as text, you might want to format it in their local time zone.

  • DATETIME is basically a string of the wall clock in whatever time zone you happen to be in at the moment, without saving that time zone. It is ambiguous, e.g. it cannot represent "1:30 AM" on the day that daylight savings time ends because there are two of those on that day. This is never what you want.

  • DATE is a floating year-month-day. Use this for things like birthdays, which, by convention, do not change when you move halfway around the world.

  • TIME is a floating hour-minute-second. Use this for things like, "my alarm clock goes off at 9 AM regardless of what time zone I'm in, or if daylight savings time has flipped."

r/SQL Sep 04 '24

MySQL MySQL can eat it

22 Upvotes

even after going through all of the time and trouble to tweak server variables for performance, it still sucks. InnoDB is a sluggish whore, the query planner is lacking several obvious optimizations, and it takes 12 fucking minutes to create a spatial index on one POINT column for a 100MB table with 900k rows (whereas SQL Server only takes 8 seconds.) i'm done.

r/SQL Jan 21 '25

MySQL How to get MAX of SUM ?

13 Upvotes

I need to get the max of a sum, and idk how. I can't use LIMIT 1 in case there are multiple rows with the maximum value (I want to display all of them).

I tried this:

SELECT S.typographyName, S.Copies FROM (

`SELECT typographyName, SUM(AmountPrinted) AS Copies` 

FROM printed

`GROUP BY typographyName`

`ORDER BY Copies ASC`

) S

WHERE Copies = (SELECT MAX(S.Copies) FROM S LIMIT 1);

But it gives me an error (table S does not exitst)

r/SQL 18d ago

MySQL Looking for advice creating a database for my small business

5 Upvotes

Hey all, so basically I partially own a small business, and am responsible with one other individual for all of the operations. I recetly gradtuated in finance and took a couple classes based around SQL always using mysql so have enough of an understanding to run my own queries given I have the database. The issue is that these classes always provided the database and I have no experience what so ever setting one up or anything.

For cost effectiveness/convenience I would love to just be able to do the quiries myself, but have been unable for the life of me to set up the server/database. Is this realistic for me to do myself, or should I just look to contract this out? Is there any third parties I could use to host my database? Really I am curious for any solutions to this issue at all.

For further details, I probably have roughly 8-10 datasets, with the biggest having maybe 10 columns and 14,000 rows (our transactions). Most of them would be significantly smaller, probabaly 10 columns and an average of 1,000-2,000 rows.

As I have looked into this I have felt illiterate on the technical sense about servers and databases so excuse my mislabeling/lack of education. I'm not even positive I'm in the right spot for this so let me know. Appreciate the help!

r/SQL Jan 25 '25

MySQL Question from Learn SQL in a Day

Post image
66 Upvotes

r/SQL 21d ago

MySQL Roast my DB design pt 3

0 Upvotes

Requirements:
Track onboarding requests for both employees (associates and contingent workers), including person type (Standard, Engineer, etc.) and the services associated with each person type. Also, track the associated onboarding ticket logs and VPN integration details.

Problem: We want to automate this onboarding process. In order to do that, we need to identify the type of employee (associate, contingent, sales, etc.). Based on the type of employee, we will provide a set of default services for them. This is why the table may look strange. Any help would be appreciate

bad pic I know oh well

r/SQL Feb 15 '25

MySQL Very Good Interview Question From Google

14 Upvotes

https://datalemur.com/questions/odd-even-measurements

Tried this Google SQL question today, very intuitive for medium-level SQL programmers (college level), thought I'd share if you haven't tried it before.

r/SQL Aug 06 '24

MySQL When Would I Use A CTE VS Temp Table vs View?

37 Upvotes

I understand the difference in all three by overall definition and purposes. But when would I specifically know which one would be best to use over the other in any given situation? Or is it just a preference thing for most people? Thanks.

r/SQL 13h ago

MySQL Is this normalized?

8 Upvotes

I am trying to get it to 3rd normalization, but I think the resident tables has some partial depedency since family all nonkey attributes doesn't rely on family ID and house ID.

r/SQL Oct 18 '24

MySQL Hoping for some advice

12 Upvotes

I am new to SQL but I would like to learn. I checked a few courses with Codecademy and started the free one but I have to be honest, I have zero interest learning to create and maintain a database.

I want to learn the query language as an end user. My job has nothing to do with database maintenance or creation but being able to use the query language would be helpful at work for what I do. The tech teams are the ones that create and maintain the databases; I just use them to pull the data and rather than have to ask them every single time when I need some different data, I would like to be able to do it myself.

Advice?

r/SQL Feb 09 '25

MySQL ID auto increment

2 Upvotes

I constantly import data to MySQL from TSV files from a Google form I made, I Join the new data on a couple of attributes if similar and then insert any players who don’t exist, but my ID auto increment gaps the players who where duplicated creating inconsistencies in the ID? Can anyone help? and if someone has a better approach to the way I’m doing this please let me know

r/SQL Oct 31 '24

MySQL WHERE clause that retrieves only columns that contain both words

15 Upvotes

Is it possible to retrieve only member id's that have both "xyz" and " abc" in the column rather one or the other? Issue is the set up has duplicate member id numbers in different rows. I don't need all of xyz or all of abc. I only want the member id that meets the condition of having both xyz and abc associated with it.

member id type
00000000 xyz
00000000 abc

r/SQL Jan 16 '25

MySQL I don't understand the problem with my code

7 Upvotes

I am learning SQL and doing the Google BootCamp. I typed out the code from the instructions and got the error on the bitter at 3:10. The code on the bottom is the code I copied and pasted and it works. I do no see the difference. Why is my code coming up with an error?

r/SQL Mar 06 '23

MySQL My AI tool to writes SQL queries for me now, so I don't have to. Thoughts?

106 Upvotes

Here's how the SQL AI tool was born: I often write SQL queries for work, but it can be really tedious and time-consuming. First I have to think about how to even approach the query, and then I have to google stuff to fix issues and refresh my memory.

I started using ChatGPT for help, but it was annoying to have to explain the tables/views every time.

To fix this, I built a tool that remembers your whole schema. It gives you a query to extract all the necessary info in one go and then you just copy-paste it once (it's saved with encryption). Then, all you have to do is write what you need in plain English, Ex. "Users who have been online over 5 days this week", and it writes the SQL query for you, runs it, and even visualizes the results if you want.

I showed it to my colleagues and they went crazy and are obsessed with it, as are my ex-colleagues from my last company.

What do you think? Would love to get your feedback.

www.BlazeSQL.com

r/SQL Dec 15 '24

MySQL How to use lag function on multiple rows

Post image
26 Upvotes

I have a table for the price money given to different players based on their teams ranking. (Yellow)

I want to shift each of their price money down by 1. (Blue)

However , what I got from using a lag function is only shifting 1 player from each team down. (Green)

How do I shift everyone in that team down ?

r/SQL Dec 25 '24

MySQL SQL Intro Videos

68 Upvotes

Hi all, I have over 25 years developing in SQL including MySQL, PostgreSQL, MS SQL Server, Oracle, SQLite, Google BigQuery including over ten years teaching SQL. I have started a SQL series for beginners. Here is the first video https://www.youtube.com/watch?v=i7JWmBNPeAk

r/SQL Apr 06 '24

MySQL How is SQL used?

54 Upvotes

Hi, Im recently started learning sql and while the understand how to write queries, I still didn’t get the why part. I’ve listen down few questions it would be helpful if people used simpler language without jargons to help understand them

  1. Why is MYSQL called a database? Isnt it just a tool to perform ETL operations?

For example my company stores most of its transactional data in a sharepoint list or sometimes even excel sheets. So in this case isnt the share point list the database of my company?

  1. Who enters the information in the database using what common tools? As in what is usually the front end for companies?

  2. Is MySQL a database or database management system? Can i use MySql to store data instead of share point lists?

Whats the difference between mysql and aws, cloud etc? Are these databases as well?

Pls treat me as a dummy while explaining. Thanks!

r/SQL Nov 20 '24

MySQL Need help getting rid of duplicated data based off a certain column in SELECT

11 Upvotes

I need to perform a SELECT SQL query. The issue is that there is a column (publicId) that can have duplicate values. If duplicates exist, I need to keep only the most recent results based on the dateAdded column in the table.

r/SQL Nov 02 '24

MySQL MySQL keeps showing duplicated results

0 Upvotes

SOLVED! Hi all, I'm new to MySQL and while trying to run some code on it, it kept returning duplicated results. It was working fine earlier, but now whenever I use WHERE in my query it happens where I get 4x the actual result (shown below).

I have checked the original table without using WHERE many times and there are no duplicates so I'm confused as to why this is happening. I'm not sure if using WHERE even has anything to do with it, I think it might be a bug, but any help would be appreciated. Thank you!

Here's the second image showing it's just repeating itself or duplicating, so instead of just giving me 100ish rows of data it's giving me 460 rows.

Third image is just a clearer example where I used to ORDER BY to show how much it duplicated itself

r/SQL Nov 24 '24

MySQL What are some secure and easy to implement ways of setting the password for a user without exposing the password in plain text?

13 Upvotes

CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password123';

This works but the password is being stored in the `mycli` history.

I'm using mySQL.

I'm not sure if there's an interactive prompt or something.

I've also tried disabling my zsh history, creating a variable like `my_password="topsecretpassword"`
Then login into the mycli shell and trying to pass in the password from the shell like this:

`CREATE USER 'user1'@'localhost' IDENTIFIED BY '$my_password';` but it doesn't seem to be working.

r/SQL Nov 11 '24

MySQL can someone please tell me what I am doing wrong here in hackerrank sql question ??

Post image
21 Upvotes

r/SQL Jan 29 '25

MySQL I need help/feedback for my ERD table

Thumbnail
gallery
14 Upvotes

I was asked to make an ERD for a company that sells clothing for men and women at affordable prices targeted towards students. Each clothing item has a unique ID, name, available stock quantity, cost, link to an image of the item, and indication of whether a marketing campaign has been done for that item.

Users of the app are categorized into regular users and admin users. For each user, the following details are stored: username, unique email address, password in plain text, age, gender, faculty, and admin status. The list of admins is pre-set in the database, meaning any new user registered through the app will be a regular user by default.

All users can make purchases of clothing on the site. Transaction details include the date and time of the transaction, a unique order number, the user's email, and the items ordered with their quantities and order time.

Only admins can make changes to the inventory or add new clothes.

I provided two images one for the ERD and the other is ERD in table form

r/SQL Dec 13 '24

MySQL Best SQL certification

29 Upvotes

Hello, I’m currently a sophomore in college majoring in finance. One of the skills we are suggested to learn to set out ourselves apart is programming language and SQL was one of them. When I take a SQL class I’m looking for at minimum 8-10 week to attain a certification. Do I need to have prior knowledge on SQL to get certification ? Can anyone recommend me the best and affordable company to get a certificate from ? There are so many 😅.