Mysql – Finding the right ticket cost
I'm trying to create a query where if customers took 1 trip that day, then they would have to pay the price of a single ticket (200). If they took 2 trips on the day, then they would have to…
Mysql – Make an INSERT statement out of result set
I have an SQL script, which returns the data from a Clickhouse table in amount of ~23k rows. I want to insert that exact data into a table in MySQL. Since they're two different DBs and there's no proxy to…
Mysql – Replace COUNT comparison in SQL
I have an exercise where I have to rewrite a query without using the NOT EXISTS. For example, I can have this query that is for getting all the employees older than 30 that are not managers: SELECT * FROM…
Postgresql – How to query JSON Column in postgres
My table name is participant_preference and the column name is 'value' and the JSON is in the column as below, { "DiaryUsers": [ { "UserId": "HUGWFJJ6", "IsDiaryVisible": false, "ParticipantName": "Dr. Mckenzie, Alistair G [SP000024]", "Status": "A" }, { "UserId": "HUV65PV1",…
Mysql – Query to calculate number of active jobs
There is a jobs table, jobID state timestamp 1 inProgress 2023-11-02 10:39:37 1 some random log 2023-11-02 10:40:37 2 inProgress 2023-11-02 10:43:37 1 inProgress 2023-11-02 10:44:37 1 inProgress 2023-11-02 10:45:37 1 inProgress 2023-11-02 10:50:00 1 end 2023-11-02 11:40:37 1 inProgress…
Mysql – query value in range of two fields
I have a table ` table VALUES from UNSIGNED INT to UNSIGNED INT value VARCHAR(2) ` example of filling 0,100000,good 100001,200000,better 200001,300000,best --- etc. I need to query a value by number, e.g. 100 (is between 0 and 100000), should…
Postgresql – Postgres select based on true condition from another select
I'm a bit new to postgres and quite rusty with SQL. I have a users table with password_hash column which stores a hashed password through pgcrypto. id | name | email | password_hash | ----+------------+-----------------+------------------------------------+ 1 | john doe |…
Mysql – Get only blog posts where I'm not the last commenter
I have a list of blog posts, people can leave comments and I reply to them This is how the structure looks like table structure Is there a way, with a single query, to get all posts where I'm not…
SUM generated_sequence in PostgreSQL 13.0
I have something like that, i need to get "triangular" number - created by summing all natural ones from 0 to n. I have such code, what should i write so i could SUM name_of_column FROM generate_series? SELECT n, CASE…
Sum values of JSON array in oracle sql
I have a Table which contains data like id, country, city,capacity. Capacity is a JSON array field which has values like [{'totalCapacity':100.0},{'totalCapacity':90.0},{'totalCapacity':80.0},{'totalCapacity':80.0}] against a row in table, so row in table looks like id country city capacity 1 usa new-york…