Mysql – OUT parameter needs INPUT
I am doing a course on SQL on MYSQL workbench. In the course I had an exercise in which I had to take an IN parameter value and save as an OUT parameter value for further use. I came up…
Postgresql – Why is Postgres execution plan changing vastly based on where condition
I am trying to execute the same SQL but with different values for the where clause. One query is taking significantly longer time to process than the other. I have also observed that the execution plan for the two queries…
How to uniquely order a MySQL table with a multi column primary key
Good day all I have a strange query. Let's say I have a table with a composite primary key (2 columns). CREATE TABLE `testtable` ( `ifk1` INT(10) NOT NULL, `ifk2` INT(10) NOT NULL, `data1` VARCHAR(10) DEFAULT NULL, PRIMARY KEY (`ifk1`,`ifk2`),…
Postgresql – SQL – How to generate boolean based from condition of two columns
I have two tables named property and equipment_type. CREATE TABLE IF NOT EXISTS equipment_type ( class_code class_code NOT NULL, major_code character(2) NOT NULL, minor_code character(2) NOT NULL, estimated_useful_life integer NOT NULL, -- in years PRIMARY KEY (class_code, major_code, minor_code) );…
Create separate columns after grouping by a particular column in MySQL
Currently, I have a DB table called mytable which looks like this id startTime result 100 2022-11-17 06:19:00 pass 101 2022-11-17 07:19:00 fail 102 2022-11-17 08:44:00 pass 103 2022-11-17 16:19:00 fail 104 2022-11-16 06:11:00 pass 105 2022-11-16 06:11:00 fail 106…
How to fix 'must be an aggregate expression or appear in GROUP BY clause' with 'as' clause in Presto?
This question is similar to another one, but I'm providing a simpler example. The other query was too advanced to make sense to me. Sample (fake) data id gender kg 4f5a07ca-02e0-8981-3c30-4d9924a169a3 male 103 4f5a07ca-02e0-8981-3c30-4d9924a169a3 male 85 4f5a07ca-02e0-8981-3c30-4d9924a169a3 male 469 e05d54e9-8292-b26c-5618-8a3712b4fc44…
Postgresql – Is there a way to make a constraint that checks if, between a range, we have all the values of that range?
I was wondering if there was any way to check if, in a column, there were all values between a range. Example: i have an INTEGER column with values 0 1 2 3 5 6 i want to check if…
Postgresql – SQL: filter "after" aggregation
I have a set of normalized tables with one-to-many and many-to-many relationships. For most queries I am really interested in a highly denormalized view which shows: Entity-per-row for one specific table Aggregated information about related entities from other tables Here…
PostgreSQL – Error "42701" when try to create view
I try to create a view which contains calculated data from others table. However, I have this error message: [42701] ERROR: column "id" specified more than once. My SQL script: CREATE VIEW dashboard_view AS SELECT c.libelle_commune AS "commune", count(bv.id) AS…
How to pass a rows from select to a function as args in Postgresql?
I have a function: my_function(arg1 integer, arg2 text, arg3, text); I have a view my_view that returns as a row: (arg1, arg2, arg3); I can call my_function() with the row from the view as I would do explicitely: select my_function(arg1…