Mysql – How to COUNT the depth of hierarchy?
I have a table employees as below: id name managerId 1 Annie Null 2 Bob Null 3 Chloe 1 4 Danny 2 5 Eddie 3 6 Felicia 3 7 Georgia 4 8 Harper 3 Top-level management is Annie and Bob…
Postgresql – How to make sure on update that 2 columns belong to each other in a different table?
I have 2 tables, referenced through a FK. users table (id as PK) cars table (id as PK, userId as FK to users, unique relationship id-userId) On a third table, I want to record some data from cars.but in case…
Multi-criteria search query optimization in MySQL
I'm using MySQL 8.0.35 and I've two tables in my database: users and clients. Users can be searched by username, firstname, lastname, email, phone, document or even id. The clients table is used to track which users are clients of…
Converting JSON response into Tables in ORacle database
{ "general": { "product_key": "501088", "group_subtype_id": 1, "group_subtype_name": "Wheel Speed Sensor", "variant_id": 6, "variant_name": "DF22", "rb_customer_id": 287383 }, "partnumbers": [ { "partnumber": "F04FD009BD", "pn_type": "Series OEM", "mat_status": "00 - planned", "properties": [ {"property_id":4,"property_name":"ASIC P/N","value_id":38,"value":"8905502648"}, {"property_id":5,"property_name":"ASIC type","value_id":56,"value":"TLE4942"}, {"property_id":6,"property_name":"Axle","value_id":62,"value":"Front / Rear Right"},…
Translate JSON TO TABLES using JSON TABLE and Nested Path
select JT.* FROM JSON_TABLE ('{ "general": { "product_key": "501088", "group_subtype_id": 1, "group_subtype_name": "Wheel Speed Sensor", "variant_id": 6, "variant_name": "DF22", "rb_customer_id": 287383 }, "partnumbers": [ { "partnumber": "F04FD009BD", "pn_type": "Series OEM", "mat_status": "00 - planned", "properties": [ {"property_id":4,"property_name":"ASIC P/N","value_id":38,"value":"8905502648"}, {"property_id":5,"property_name":"ASIC type","value_id":56,"value":"TLE4942"},…
Mysql – How to Create a SQL VIEW to Indicate User's Favorite Recipes in a Result Set?
I need help writing a SQL VIEW query for the following scenario: Here is the query which I have tried but it only return the matched data, because any someone user_id is matched to any of the recipe: CREATE VIEW…
Mysql – SQL query to get the maximum within a group, then attach another field to it
I have a derived table that looks like this I'd like to get the top sales person in each quarter, so the result should look like The tableit is derived from a larger table that has many other fields, and…
Postgresql – Create CHECK constraint based on complex algorithm
A personal identity codes consist of a string of numbers that indicates the individual’s date of birth, an individualized string, and a control character. Example: 150600A905P • 150600 = Date of birth • A = the character in the middle…
Amazon web services – How to determine 'table type' for Iceberg tables in Athena
I have some Athena databases which contain a number of tables, and I know some of these tables use the Iceberg format. It's important to me to know which are Iceberg tables, because those will allow a greater range of…
PostgreSQL. Getting data from another table
I'm trying to get data from "users" table into 'user_id' and 'profile_fullname' rows from "profiles" table. For example if a row has an id of 1 in the "users" table, then the 'user_id' should also be 1. Same with the…