How to use two aggregate functions (for example double string_agg or just sum), but ensure that the results do not contain duplicates caused by the other aggregate function (caused by second join)? I use PostgreSQL. Example I have three tables:…
I have a movie table with over 100 different movies, shown below on PostgreSQL: I want the name of each most-recent released movie, according to EACH GENRE. How do I get the following output below on PostgreSQL:
I have the following database relationship and query for PostgreSQL below: create table gender( gender_id int primary key, gender char(10)) create table student( student_id int primary key, first_name char(50) not null, last_name char(50) not null, gender_id int not null, constraint…
I have the following movie database relationship diagram and used the queries below to create the tables and its keys as shown below: The following are the codes that I used to create these 4 tables and inserted the data:…
Context I have the table "user_numbers" with the following records: id user_id number 1 3 123 2 3 666 3 4 123 4 5 666 5 6 555 I want to build query that find all user numbers and all…
Following is the given JSON data: DECLARE @Jdata NVARCHAR(MAX) = '{ "EmployeeDetails": { "BusinessEntityID": 3, "NationalIDNumber": 509647174, "JobTitle": "Engineering Manager", "BirthDate": "1974-11-12", "MaritalStatus": "M", "Gender": "M", "StoreDetail": { "Store": [ { "AnnualSales": 800000, "AnnualRevenue": 80000, "BankName": "Guardian Bank", "BusinessType": "BM",…
I need to write the following query. Suppose we have a table structure like this: main_table: id PRIMARY KEY column1, column2, column3 table_1: id PRIMARY KEY main_table_id REFERENCES main_table NOT NULL, column1, column2, column3 table_2: id PRIMARY KEY table_1_id REFERENCES…
I have the following function which I want to pass anyelement and it should return whether it is a valid URL by matching the regex: CREATE OR REPLACE FUNCTION isURL(data anyelement) RETURNS BOOLEAN AS $$ BEGIN RETURN data::text ~ '^https?://[-a-zA-Z0-9@:%._+~#=]{2,255}.[a-z]{2,6}(/[-a-zA-Z0-9@:%._+~#=]*)*(?[-a-zA-Z0-9@:%_+.~#()?&//=]*)?$';…
im a beginner and using mysql workbench8.0 ,and it seems like i cant get this to work. ive got three tables. these are my tables: 1.books(id=pk) id title author 1 my first book sara 2 my second book maman 3…
Here is schema of table:- INSERT INTO cars(id,model,brand,color,make) VALUES (1, 'Model S', 'Tesla', 'Blue', 2018), (2, 'EQS', 'Mercedes-Benz', 'Black', 2022), (3, 'iX', 'BMW', 'Red', 2022), (4, 'Ioniq 5', 'Hyundai', 'White', 2021), (5, 'Model S', 'Tesla', 'Silver', 2018), (6, 'Ioniq 5',…