Query 1: WITH cte AS ( SELECT *, MAX(score) OVER (PARTITION BY exam_id) AS max_score, MIN(score) OVER (PARTITION BY exam_id) AS min_score FROM student JOIN exam USING (student_id) ORDER BY exam_id, student_id ) SELECT * FROM cte; By Query 1,…
I have a SQL query which works but is slow. I wondered if there is a more efficient way to express it using joins. Scenario: Tables: productions, scripts and a join table, productions_scripts productions id (int) production (text) scripts id…
I have an 'offers' table and a combined- offers table. The offers table has the offers with their details. The combinedofferstbl has two columns: offer_id and combinedwithoffer_id. I want to do something like SELECT * from combined offers where combinedofferstbl.offer_id…
I'm currently working on a PostgreSQL query to extract specific values from a JSONB column. Here's the query I'm using: select a.id, (jsonb_array_elements(a.info->'attribute1')->>'value') as attribute1, (a.info->>'attribute2') as attribute2, (a.info->>'attribute3') as attribute3, (jsonb_array_elements(a.info->'attribute4')->>'value') as attribute4 from a_table a where (cast(a.info->>'attribute3' as…
I am trying 8weeksqlchallenge, and I usually work with MySQL and the error codes are quite cleaner there, but not so much on dbfiddle.com This is my schema CREATE SCHEMA dannys_diner; SET search_path = dannys_diner; CREATE TABLE sales ( "customer_id"…
Probably a very simple solution but I am pretty new to PostgreSQL and I need you help good people. I have a table called countries containing information about different countries and later on I added a column called three_rivers that…
I have a MySQL dev_table with thousands of products identified by their Name, containing changes to price and availability (among other values) over time. The table contains only the dates where a change occurred. I am looking for an efficient…
I am using PostgreSQL 15 and have the following two statements in PL/pgSQL: -- Statement 1 SELECT to_json(T) FROM (SELECT * FROM category WHERE category.id = 43) T) -- Statement 2 SELECT json_build_object('products', JSON_AGG(T)) FROM (SELECT * FROM product WHERE…
this is my student table stud table rollno name 1 A 2 B 3 C 4 B 5 D 6 C this is my marks table marks table rollno mar eng maths phy chem 1 40 45 38 50 50…
I have an old PHP5 MySQL webapp whose code had to be adapted to PHP8/MySQL14.14 There's a view, querying few tables and based on that code: (SELECT `db_demandes`.`bdd`.`id` AS `Id`, `db_demandes`.`bdd`.`ref` AS `Ref`, `db_demandes`.`bdd`.`nodossier` AS `NoDossier`, `db_demandes`.`bdd`.`agentfirme` AS `Agentfirme`, `db_demandes`.`bdd`.`agentanterieur`…