Currently all our tables have a created_at and updated_at timestamp, e.g. The updated_at field is modified with every INSERT. This allows us to query all Cart’s which have been updated since a particular point in time with something like this: However this would not capture updates to FK relationships s…
Tag: database
One large DB request plus many Casts vs. many single DB requests – which is faster for a lot of Data [closed]
Closed. This question is opinion-based. It is not currently accepting answers. Want to improve this question? Update the question so it can be answered with facts and citations by editing this post. Closed 2 years ago. Improve this question let’s say you have 5 concrete classes implementing the interfac…
Top 3 Values in the Same Row
I am working on Steam data and need to find the top 3 tags people used for each game but the problem is all the tag values are in the same row for each game like the image below (appid column then 370 column for each tag and values). Result can be anything like 200,300,400 or column name plus tag
N:M Relationship between users and guests
I’m building an app that can assign guests to an existing user. Those guests can see some information about the users they are associated with. It’s N:M: Users can have multiple guests associated Guests can be associated to multiple users Under the hood they are stored in the same table, the only …
Oracle SQL Roll up function or alternatives
First, I am finding an average of some data, and then formatting that average into to decimal points. And in the end, I want to use rollup to generate a total row for all columns. The problem is: I want the rollup to sum the data as they appear, however, when adding up the average it is not adding them
Array variable inside DB::raw query laravel
I want to predifine $processingStatus array and use it inside Laravel DB::raw query but this gives me the following error- Syntax error or access violation: 1064 You have an error in your SQL syntax My query is a large one so I am only pasting the fragment which I guess is causing the error- DB::raw(“CO…
SQL Sub-Query Multiple Columns [closed]
I have a dataset containing a list of recorded coronavirus cases in countries across the world through a time period of December 2019 to April 2020. I am trying to write an SQL query to give a a …
Maximum number that can be returned by 2 Natural Joins
Given relations A(a,b,c), B(e,f), C(d,g,h), where A has 800 tuples, B 200 and c 500. In worst case gives the expression A * B * C ( with * natural join) : a) 800 tuples b) 200 tuples c) 500 tuples …
Database table design for ‘availability’ within time ranges per day
I am working on a meeting scheduling web-app, where a person can share their availability for specific time ranges within the day. E.g. A user is available 3 time-ranges within the day, from 09:10 to 10:00, 13:00 to 14:00 and 16:30 to 17:15. This can go on for 6 days per week, from Monday to Saturday. What I …
Why is SQLITE taking forever on a simple count query?
I have a set of SQLITE tables that adds up to 7 GB on disk. The particular table I am querying (FQ) has approximately 50 columns, and 3 million rows. I am querying from the sqlite3 interactive shell (…