MYSQL join tables with dynamic names
I have the following table: id | name 1 | Group1 2 | Group2 .. | ... I want to join it with another table this way: id | group_1 | group_2 | .. 1 | 10 | 8 |…
How to count values from a column with JSON content?
I have a table with singers and each singer can be linked to many musical styles. Musical Styles are in a TEXT column, but the content of the column has JSON format. I want to count the number of occurences…
Postgresql – SQL query to get hourly data for highest revision number per day
I have two related tables as below. Master table: ID Date Rev 1 2022-01-01 1 2 2022-01-02 1 3 2022-01-02 2 4 2022-01-03 1 Detail table: ID hour Capacity 1 1 1 1 2 2 1 3 3 1 4…
Postgresql – Get lowest value in each category
I have a very flat table that is a collection of swimmer names, distances, events (strokes), and times(float). What I would like is to get the only fastest time for each combination of distance and stroke. To be clear, there…
Postgresql – SQL not allowing deletion of related data
In SQL I have: CREATE TABLE Customer( cust_id INT NOT NULL PRIMARY KEY CHECK (cust_id > 0), full_name TEXT NOT NULL, phone TEXT NOT NULL, address TEXT NOT NULL CHECK (LENGTH(address) >= 3) ); CREATE TABLE "Order"( order_id INT NOT…
Mysql – Why I am getting Missing From Keyword error in the following code?
The cancellation rate is computed by dividing the number of canceled (by client or driver) requests with unbanned users by the total number of requests with unbanned users on that day. Write a solution to find the cancellation rate of…
Postgresql – Using arrays in `WHERE IN` statements
There are the following table structures and data: CREATE TABLE t_source_category ( id INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, label varchar(255) NOT NULL, path varchar(255) NOT NULL, pid INT NOT NULL); INSERT INTO t_source_category (id, label, path, pid)…
Convert multiple MYSQL rows to only one array column
I have a simple database with the user and the country tables. Which user belongs to only one country. Which country has a list of users. Right now Im using group_concat (code below) and its working in a "manual" way…
Postgresql – Doing a bulk update in Vercel Postgres by dynamically building query
I have a simple table in Vercel Postgres: export const sets = createTable("card_table", { id: varchar("id").primaryKey(), info: jsonb("info"), }); I have populated the table with data and would like to do a bulk update. I figured out how to update…
Postgresql – How to trim a string by the nth occurrence of a special character?
I have string values (filed name: filename) that follow a certain template: filename_this_is_called_Plan_A file_this_is_Plan_A filename2024_this_is_known_as_Plan_A While the strings are all a different length, they all end with _Plan_A, also known as the segment. How can I trim this in PostgreSQL…