Category "sql"

DBMS ERD Design

I am planning out an application's database. I have an employee table. I want to store every employee's education degree. Adding an education column to the empl

MySQL How to join 2 tables and get a combined response

I have 2 tables projects and images. My goal is to get all images of a project and get this kind of response. { "data": { "id": "this is project id",

how to display columns in specific order, according to field value in MS Access

I want to display a query but the column order needs to be sorted according to the value in the column, field with greater value as column 1, then second greate

SQL Query to get the last unique value from a column

I have a SQL table with the following columns: id | created_at I want to get a dictionary of all the unique id's and the latest created_at time. My current quer

First time SQL query is taking More time from Spring Boot application

I have to fetch some records and then display in output. In my Spring Boot Application, I am using JPA Specification for creating Criteria and then calling repo

SQL returning all rows and count

I have 3 tables, Property managers, Addresses and Units. I'm trying to get all the rows using the aggregate function COUNT, but it only returns 1 row. I want to

MYSQL query that works out the total amount of days between different time periods for a payroll

I know this question may seem like a simple one and might have been asked before but what makes this tricky is how do I work out the total days for a payroll. N

SQL Server - Regex pattern match only alphanumeric characters

I have an nvarchar(50) column myCol with values like these 16-digit, alphanumeric values, starting with '0': 0b00d60b8d6cfb19, 0b00d60b8d6cfb05, 0b00d60b8d57a2b

Removing leading zeros from a string in SQL Server

i have removed all leading Zeros in my column but what if the same column has a space its replaced by '0'.. Example: batch number has 000123, 0000145 but when t

[Python][SQL Machine Learning Services] Cannot install XGBoost on my SQL instance

I am trying to find a solution to my problem. We are trying to install XGBoost package on our MS SQL 2018 and we are facing this problem: Error Description The

Best way to get list of element combinations from two mysql tables

I have two tables: Table "products" ID Name Price Quantity 1 Product A 10 10 2 Product B 20 10 Table "promotions" ID Product ID Type Price Quantity 1 1 disco

Capitalize the first letter of each word without affecting the consecutive letters

I would like to make the first letter of each word capitalized. However, if the word next to the first letter is already capitalized, then it should not be affe

how to avoid long sql in clickhouse

As ClickHouse can run complex SQL efficiently, we now write hundred-lines-in-one SQL to get the real time analysis results(join lots of source table). And it br

Assign outcome from SQL query to column

I have a dataframe (test_df) that looks like this: dq_code dq_sql Results ID_24 select 'A' as B, 'B' as

Select foreign key matching multiple criteria in a joining table (SQLITE)

Preface: this is all in a fiddle here https://www.db-fiddle.com/f/pMLFrz59aeL7B2jUsmTt9X/1 I have a table that contains items (item #100, item #101...) that eac

Referencing JOIN columns within a subquery from an aggregate function

I'm trying to reference columns in JOIN tables of a subquery from an aggregate function, as follows: WITH my_activities AS (SELECT activities.*, owner.*

How to test a repository with a user that have update rights and the test needs tot add records to the database

I use mysql and spring 5, spring-data-jpa and spring-boot and JUnit5. This must be a common problem but I don't see te sollution. I have to test a repository wi

Vertica SQL: Disregard rows, based on two conditions

I need a where condition that considers to following for an entire table: If a 0 exists for an ID (in column d) then exclude everything that is >0, if 0 does

Best Method to Delete 12M rows from SQL table

I have a table currently with 100M+ rows of telemetry data. The table often receives duplicate records. I can identify duplicate records using a row_number() pa

AWS Athena/Presto SQL: Having trouble getting null values

I am doing a query in aws Athena where I want to get some total values, however I am having issues getting a column where the values are null, this column somet