Mysql – How to delete only the first occurrence of a where condition in sql
If we have a table named "names" | id | name | |----|------| | 1 | ab | | 2 | bc | | 3 | ab | and we give the SQL statement DELETE FROM names WHERE name =…
Mysql – sql show difference in one column based on the sum of another column on another table
I have table like following job_id quantity 2948 0 2947 3 2949 34 2950 4 And I have another table like following job_id quantity 1 10 2949 10 2949 20 If I run SELECT job_id, sum(quantity) FROM arr_database.test_table group by…
Postgresql – SQL: how to rank events within a cohort by time
I'm working with a Postgres table named orders that looks like this: user_id product order_date 1 pants 7/1/2022 2 shirt 6/1/2022 1 socks 3/17/2023 3 pants 2/17/2023 4 shirt 3/13/2023 2 pants 8/15/2022 1 hat 4/15/2022 5 hat 3/14/2023 2…
Return the number of records where condition met within time intervals in MySQL
I am at the beginning of learning SQL, and this problem gives me a headache. I have a table where I want to work with these columns: updated_date upload_date 2023-04-03 09:10:50 2023-04-03 09:08:24 2023-04-03 09:14:55 2023-04-03 09:08:34 2023-04-03 09:23:51 2023-04-03…
PHP script not saving data to MySQL database
I have a PHP script that is supposed to save movie IDs to a MySQL database table called "seen_movies". However, the data is not being saved to the table even though the script runs without errors. I've checked that the…
Postgresql – Are missed/duplicated rows due to row movement possible under READ COMMITTED and/or REPEATABLE READ isolation?
Physical row movement during a scan can cause missed rows and duplicate reads (same row appearing twice in the result) in SELECT queries under certain conditions. For MS SQL Server this is well documented in connection with the NOLOCK hint.…
Postgresql – SQL find timestamp where sum of visitors was greatest
I spend a lot of time solving this problem. So, i have table users: at_timestamp users_count event_type 100000 2 1 100001 4 1 100003 5 0 100005 1 1 100006 3 1 100008 2 0 100008 1 1 etc. So,…
Mysql – I am trying to write SQL that will let me select 2 columns, transform 1 into 2 and count them
I'm working on a project Where I want to use SQL to select the creation date, creation user and count how many unique instances. The problem is I am change the creation date into 2 separate fields of YEAR and…
How can I get postgresql local configuration parameter's current value?
I'm practicing with queries optimization. In particular, tuning work_mem parameter. There's the DB diagram: https://postgrespro.com/docs/postgrespro/12/apks02. I have a sql function get_passengers_and_flights: CREATE FUNCTION get_passengers_and_flights(d timestamptz) RETURNS TABLE(passenger_name text, flight_no text) AS $$ SELECT t.passenger_name, f.flight_no FROM tickets t JOIN ticket_flights…
Not sure whats wrong in the below Query of the problem "Biggest Single Number" from leetcoode – Mysql
A single number is a number that appeared only once in the MyNumbers table. Write an SQL query to report the largest single number. If there is no single number, report null. The query result format is in the following…