I have 3 TABLES: movies which has title and id columns stars which has person_id and movie_id columns people which has id and name columns I want to write a SQL query to list the titles of all …
Tag: database
PostgreSQL – Get count of items in a table grouped by a datetime column for N intervals
I have a User table, where there are the following fields. | id | created_at | username | I want to filter this table so that I can get the number of users who have been created in a datetime range, …
How to create a leaderboard as a database
I need to create a database which is a top 5 leaderboard, however, what I tried didn’t work – it brought up many different errors (code below): Problems: The 4th line beginning “db = ” comes up with an error on the brackets. I initially thought it had to do with the fact that I am tryi…
Alternate approach to WITH CTE and large UNION query
I’d like to rework a script I’ve been given. The way it currently works is via a WITH CTE using a large number of UNIONs. Current setup We’re taking one record from a source table, inserting it into a destination table once with [Name] A then inserting it again with [Name] B. Essentially cre…
Unable to join from 3 different tables – MySql
I am struggling to understand if this query is possible. I have 3 tables, User, Account, Loan. 2 users are linked by an account, and any loans facilitated between the 2 of them gets linked to the …
How to change max_connections_per_hour in XAMPP?
In my production environment I’m getting this error: I don’t have this problem in my development environment so I need to set the max_connections_per_hour to 500 in order to replicate it. How do I do this? My database is run from the XAMPP control panel. I don’t see this variable name in my.…
User with multiple companies (different type)
I built an application in Laravel that is a simple CRUD app. Now im struggling with migrations a bit. User belongs to Company. Company can have many users. Company type can be Supplier, Customer or …
Do any databases get NULL and “NULL” confused, or is it always an application design failure?
I’ve read a few articles like this one about a license plate value of NULL (about Droogie from DEF CON 27), including part of chapter three Little Data in the book Humble Pi by Matt Parker (talking about Steve Null), where storing a string value of “NULL” in a database matches NULL values. W…
SQL DB structure for N items with M parameters varying over time
There’re the items: item_1 … item_N N is about a few hundreds. New items can be added sometimes. Each item has a set of parameters: parameter_1 … parameter_M M is about 20-30. These parameters …
sql: long to wide format without using PIVOT
I have a table like the following: I want to convert the data to wide format like the following: the SQL I used does not have a pivot choice so I am wondering how to convert the long format to wide without using PIVOT. Answer If I understand your question correctly, you can do conditional aggregation: Maybe y…