Questions tagged [sql]
Structured Query Language is a language for interacting with relational databases. Read the tag wiki's guidelines for requesting SQL reviews: 1) Provide context, 2) Include the schema, 3) If asking about performance, include indexes and the output of EXPLAIN SELECT.
1,442 questions
6
votes
2
answers
695
views
Weather Observation Station 8 query
Here's my (correct) solution to a HackerRank question:
Weather Observation Station 8
Query the list of CITY names from STATION which have vowels (i.e., a, e, i, o, and u) as both their first and last ...
- 277
5
votes
3
answers
1k
views
Slow SQL query with nested subquery
I have written the below query in order to identify how many events (occur over a week) in each hour.
...
- 403
4
votes
2
answers
81
views
better-SQLite3 and JS/Next.js - updating data using "transaction"- shorter / better code?
I am pretty new to SQL databases and in particular to the better-SQLite3 database. I have managed to insert, update, and delete data in a way I desire. All of it running in the background of a self-...
- 143
9
votes
2
answers
952
views
JavaFX app with User Authentication and SQL Persistence
This JavaFX program is just supposed to allow a user to register a username and password and then have it stored in an SQL database.
There's been some criticism that it's not clean, readable or ...
- 153
3
votes
1
answer
183
views
Getting all column vs. table regardless of alias using JSqlParser
I have been trying to get all column vs. table regardless of alias used, for example, assume this query
...
4
votes
1
answer
63
views
Better Microsoft SQL update
I've got the following SQL that I need to update by adding a new parameter:
...
- 328
0
votes
2
answers
54
views
Enhancing my query for select information implement in oracle(plus/sql) [closed]
I'm working on my query for select information relate to the order table in oracle. I've written the following code. But I'm looking for suggest on how to improve it.
...
- 79
5
votes
2
answers
695
views
A schema for awallet system that allows transfers between users
I am currently working on a wallet system that allows transfers of money between users. I tried creating this as a Stack Overflow question, but it was rejected. I'm not sure if this is the right ...
- 163
3
votes
2
answers
166
views
Clean up verbose code for Oracle SQL
I have the following Oracle SQL code that works, but I would like to try and find a way to clean it up so it's not so repetitive. It is looking at orders for a part ordered each FY in an Oracle ...
- 33
3
votes
1
answer
137
views
Faster Query to Track State changes in a Large Table
I have a table that tracks state changes of an entity with timestamps. The schema of the table is as follows:
entityid
userid
originalvalue
newvalue
changetime
255
101
Draft
Submitted
2023-11-28 12:10:...
- 53
2
votes
1
answer
72
views
Delete duplicates from SQL database with Python and reset ID's
I tried to create a peice of code that helps me remove duplicates from my sql database and reset the ID's as well.
I am not sure that it's error proof.
...
- 137
3
votes
1
answer
74
views
Postgresql generated column from Finnish personal identity code
The Finnish personal identity code is defined as:
Day, month, and year of birth, in ddmmyy form (six digits, zero padded if necessary)
A separator
For those born &...
- 833
2
votes
0
answers
56
views
Case Study: Database design help for a small app used to coach tennis players
I have a small SQL server database and VBA program that is used to automate tasks related to tennis students, programs, practices, performances, and student testing.
I'm trying to learn db design and ...
- 21
2
votes
1
answer
88
views
Feedback for my first project - sqlserver-json-tools
could you review my first project on github please. The JSON_MODIFY function in SQL Server 2016 and 2019 does not allow creating paths dynamically, making it necessary to chain JSON_MODIFY executions. ...
2
votes
1
answer
106
views
Determining the least dangerous tags with over 1K answers to answer
So I thought I'd take another shot at making a SEDE query after being somewhat inactive after a while.
Essentially what this does is that it takes the tags that have at least 1000 answers for that tag ...
- 619
3
votes
1
answer
67
views
PostgreSQL Search Feature
The Problem
I am developing a search feature in PostgreSQL that involves a collection of JSONB documents stored in a table, which have been standardised. The goal is to enable clients to perform ...
- 219
1
vote
1
answer
37
views
Filter filtered bike prices twice without repeating yourself
dbfiddle
Goal
Without repetition in the code, I want to filter BikeValuationList to only give the BikePriceId of the latest ...
- 137
2
votes
0
answers
95
views
To-do app with TypeScript backend (violations of TS best practices or unidiomatic code)
The working code in full is here.
It's a To-Do App with a TypeScript backend, written as practice for an upcoming 4-hour interview for a TypeScript backend role. My colleague and I would like to know, ...
2
votes
1
answer
138
views
Need assistance in Optimizing the Recursive Query in Pyspark
I am trying to convert a SQL Server stored procedure to Pyspark code.
This is my SQL Server query:
...
- 21
3
votes
1
answer
126
views
Creating an array (temporary table) in SQL and iterating through each row within this table to preform a check on the data in each row
The SQL Server version I am using is SQL Server 2008.
I've got an SQL query/script I've written which is to help simplify some things while our API is being rebuilt.
Essentially, this script allows ...
- 131
3
votes
2
answers
120
views
Calculate the count of top performers in SQL
I have a transaction table let's call it Offers Table. Please don't read too much into the business but let's say a customer wants to buy a car and the customer searches variety of websites but at the ...
- 31
6
votes
1
answer
919
views
Show how close a user is to being unsung
So this morning, I decided to create a SEDE query that answers the age-old question "How close am I to being Unsung?" because I recently polished up my first SEDE query and I realized that I ...
- 619
2
votes
1
answer
53
views
Understanding how to properly create a stored procedure that only does INSERT from sourcetable to targettable with dynamic sql
Background and use-case
I need to create a stored procedure where the input parameters will be: @sourceTable, @targetTable, @compositeKey. I'm using available fields for the composite key, since I ...
- 21
2
votes
1
answer
258
views
3
votes
2
answers
165
views
SQLite querybuilder class in python
Motivated by the first commentor's suggestion on my MSAccess question:
Python SQL insert to MSAccess with VBScript
I am moving to use SQLite databases for a python application and I created this query ...
- 123
3
votes
3
answers
120
views
Improving efficiency of database searches under SQLite (SpatiaLite - QGIS)
I'm new to SQL, but I've always heard that it's a very efficient way of searching databases. For the same task, in VBA I get a result in a few seconds for 1000 queries. In SQL it takes several minutes....
- 169
5
votes
1
answer
130
views
Python SQL insert to MSAccess with VBScript
I have a business use case that required running SQL insert queries to a local MSAccess database with a Python script. The actual script does many other things and the SQL function is just one of them,...
- 123
3
votes
1
answer
104
views
SQL query creating tables needed by a simple PHP CMS
I have written recently the SQL query to MariaDB that I want to move into PHP code. Below is my test.sql file. What do you think about my SQL code?
...
- 531
0
votes
1
answer
67
views
Find and conditionally sort all venues matching a filter provided by an API client in PostgreSQL
I have an API where users can retrieve a list of "venues".
Every venue is a profile, but not every profile is a venue
Venues can be "parents" of other venues (think hotel - floor -...
- 103
4
votes
2
answers
246
views
Querying for vendors and categories
I'm looking for pointers on the best way to optimize my code below. The query is designed to pull for a vendor database in a separate SQL server table that I built a connection to in my wordpress site....
- 141
0
votes
1
answer
99
views
3
votes
1
answer
83
views
Find All Recent Winners and Calculate an Encoding of What They Won
Context
I was proud of this code for a little while, but the repetition wounds me. I know that the function name sucks, but the name and interface to this function are not under my control. ...
- 137
2
votes
2
answers
112
views
Find All Recent Winners and Calculate an Encoding
Context
I was proud of this code for a little while, but the repetition wounds me. I know that the function name sucks, but the name and interface to this function are not under my control. ...
- 137
1
vote
2
answers
38
views
Creating single table vs creating multiple table
I have lots of services which I am integrating. (also I am new to sql)
For each service integration I get different response,
For example, for zoom, I would just need to store the access_Token, for ...
- 495
1
vote
1
answer
155
views
What is the percentage of customers who increase their closing balance by more than 5%?
The Question
What is the percentage of customers who increase their closing balance by more than 5%?
Source Code
The database and all details can be found here.
ERD
My Proposed Solution
...
- 113
-1
votes
1
answer
288
views
Optimizing LINQ Query on C# Web API
Can someone help me optimize this code especially the LINQ query. It takes 8-12 seconds just to return a response when I'm calling the endpoint. Its super slow, and my whole endpoint call is taking ...
0
votes
1
answer
123
views
Is there a more concise way to write this Procedure
I have this stored Procedure that is passed in the data below that will building the SQL select that will get EXEC. The data that can be passed in can me null so I have three ...
- 413
1
vote
1
answer
81
views
Is there a more concise way to write this query in SQL Server?
I'm playing with data from a Time Use survey as a beginner SQL user.
Gender is represented as 1's (Male) and 2's (Female), and the age of each participant is listed. I would like to divide them into ...
- 303
1
vote
1
answer
386
views
Employee Attendance structure design
I am designing a solution to store employee day attendance, which can be recorded either as hours or as an interval within a day. The goal is to maintain a monthly attendance list for each employee.
...
- 119
0
votes
1
answer
39
views
Joining with a different (foreign keys) while using the same key on a different table
I'm a bit new to SQL and I'm still learning how to efficiently use joins. I have two tables.
contains Materials and Alternate Materials
contains Real_ID and Materials.
The code below works however I ...
3
votes
0
answers
94
views
Create table of yearly monthly temperatures from monthly data
I've got a series of tables with monthly data on USA regions over about 130 years, divided by either climate divisions (clim_div) or other federal standards (ncdc_fips). They all follow the same ...
-3
votes
1
answer
108
views
3
votes
1
answer
2k
views
DB2 SQL statement to split a string based on delimiter
I have a "flat file" with structure as below:
...
- 41
3
votes
1
answer
86
views
calculating the percentage of distribution of ids per Loc for each country
Which of the two queries is theoretically better and why?
I would like to understand why query 2 takes longer although there is no relevant information in the Execution Plan.
I'm querying a Presto ...
- 71
1
vote
1
answer
65
views
Find offers that can be matched with bids
I am working on a sql query for the following scenario:
Get all available offers for registered demand (offers from profiles
should not be from profile which had registered demand). After that
get ...
- 121
0
votes
1
answer
190
views
Count how many active and inactive users are in a group/unit/department/team
Given the following example data:
id
username
group
unit
department
team
status
1
user1
g1
u1
d1
t1
active
2
user2
g1
u1
d1
t2
active
3
user3
g1
u1
d1
t3
inactive
4
user4
g3
u6
d12
t30
active
5
user5
...
- 461
2
votes
1
answer
69
views
Update table with data from another table under certain criteria
I need to update a lot of data from one table based on criteria from another table.
My tables and records in those tables look like this:
This is Users table where i can have many records for single ...
- 351
-2
votes
1
answer
62
views
Optimizing a TRANSACT SQL statement [closed]
I'm in need to optimize the following TRANSACT SQL statement :
...
3
votes
0
answers
57
views
SQL query to pivot table into dynamically growing set of columns [closed]
I am working on SQL Server I have sample data like this in a table.
CustId
Bank
city
rating
date
rnk
1
Deutsche
Delhi
5
10/10/2022
1
1
BOA
Pune
6
10/10/2022
2
1
UBS
Mumbai
7
10/10/2022
3
1
SBI
...
- 39
1
vote
1
answer
2k
views
15 Days of Learning SQL (HackerRank Challenge) - is having multiple queries a good idea in this case?
The challenge is as follows:
"Julia conducted a 15 days of learning SQL contest. The start date of the contest was March 01, 2016 and the end date was March 15, 2016.
Write a query to print total ...
- 645