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,459
questions
0
votes
0
answers
13
views
SQL; Inserting Rows within a CTE - Is this correct?
I am working with Netezza SQL.
I have the following table (called "table_a") about elephants and the year that they escaped from the circus:
...
- 69
4
votes
2
answers
215
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
59
views
2
votes
1
answer
66
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. ...
- 117
2
votes
2
answers
82
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. ...
- 117
0
votes
0
answers
15
views
specify a policy for allowing access to a table through a foreign key
Consider the following two tables in a PostgreSQL database:
...
- 101
1
vote
2
answers
24
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
0
votes
0
answers
12
views
Order Model for a Market Place Ruby on Rails Application
I've created a Marketplace application on ruby on rails and would like to request a review of my code (Order Model) to check if I am in the right track or not...please kindly understand that I am a ...
1
vote
1
answer
106
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
0
votes
1
answer
140
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
108
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 ...
- 359
1
vote
1
answer
61
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
232
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
0
answers
63
views
SQL: updating a variable number of columns in a row
Data coming from HTML form: several inputs that need to be entered into the database, any of which may be empty. How do you solve this task?
My approach: check params, null if empty, than execute one ...
- 53
0
votes
1
answer
27
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
82
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 ...
-2
votes
1
answer
77
views
1
vote
0
answers
632
views
DB2 SQL statement to split a string based on delimiter
I have a "flat file" with structure as below:
...
- 21
1
vote
1
answer
70
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 ...
- 59
1
vote
1
answer
46
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
105
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
...
- 361
1
vote
0
answers
34
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 ...
- 341
-2
votes
1
answer
51
views
Optimizing a TRANSACT SQL statement [closed]
I'm in need to optimize the following TRANSACT SQL statement :
...
3
votes
0
answers
46
views
SQL query to pivot table into dynamically growing set of columns
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
...
- 31
1
vote
1
answer
1k
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 ...
- 633
1
vote
0
answers
36
views
Can this SQL Query for Grouping Multiple Columns and Date be Improved in any way?
I'm trying to refresh my very rusty SQL skills.
I have a table with device logs, collected every 5 minutes. I'm interested in counting the number of times a device is either 'connected' or '...
- 143
0
votes
0
answers
46
views
Can this database design be optimized? Two joins on large dataset
Given a table of video IDs ordered by review score, I'd like to return videos the user hasn't seen yet and aren't from a blocked author.
Considerations
Writes can be slow but reads should be fast to ...
- 101
2
votes
1
answer
123
views
Extracting authors and books from XML and inserting them into PostgreSQL
There is a tree of start folder, it's subfolders, their subfolders, etc. In each folder, subfolder, etc. there are the same structured XML files stored.
books.xml
...
- 23
1
vote
0
answers
51
views
-1
votes
1
answer
50
views
Nested table in query
I have transaction tables called MaterialRequest and MaterialRequestDetails.
What I want to check my query and review that it's ...
5
votes
1
answer
60
views
Sum values in different categories by each financial asset
My SQLite database:
CREATE TABLE Movimentacao
( mydate DATE
, dividend_type VARCHAR(200)
, dividends DECIMAL(19, 2)
, ticker VARCHAR(300)
);
Inserting some data:
<...
- 183
4
votes
3
answers
371
views
Queries to get products selected in multiple stores
Project description
I'm making a website listing products. The listing goes as following (See here):
Product name
Shop name
Price in that shop
If the product appears multiple times in the table for ...
- 43
0
votes
1
answer
59
views
Query for sequential column permutations in SQLite
For dummy's sake, let's say that I have a database with columns text, ind and sentid. You ...
- 495
2
votes
1
answer
339
views
Login authentication using MVP pattern
How can I improve my login script and is there a cleaner way to get the same result? I had this project and redesigned it using MVP patten . Working with this patten is a new challenge for me. It's ...
- 21
3
votes
1
answer
371
views
Why are my read speeds so bad using sqlite?
I know very little about databases and even less about how to optimize them, but I have a problem which calls for a database so here I am...
I created a sqlite3 database using the following script:
<...
- 916
0
votes
1
answer
51
views
Find dogs who stopped being good boys without ever being good boys - Was HAVING the wrong approach?
The problem I have solved is as follows:
Consider a table of dogs, DOGGIES, that records on each row the ID of a doggy, one ...
- 117
0
votes
0
answers
32
views
How to create a good database model in my task (I have a few tables for now)
I create a smaller app that has to be used for money transactions where it focuses on favor and expense from bank accounts (later I will expand other tables for admin, workers costs, credit ...
- 1
-1
votes
2
answers
177
views
Product list from database to web tables/boxes
I am a self-taught front-end developer, trying to land a job, the PHP and MySQL are quite new to me, since I only watched and did some code through Traversy Media courses.
This is a test project, ...
- 11
1
vote
0
answers
92
views
Postgresql: reduce the number of joins needed to get the information about followers and following [closed]
This query is part of a mobile application backend I am working on. It is hosted on Supabase (open source Firebase alternative): It's purpose is to retrieve the profile 'preview' - the most important ...
- 119
-3
votes
2
answers
154
views
Insert employee data into a database
I've constructed code using prepared statements to try and avoid chances of SQL injections. This code is meant to insert the employees information. It does what I need it to do but I want to know if ...
3
votes
1
answer
78
views
Newspaper Bill Calculator CLI with Python (3 of 3, Database)
Code is posted after explanation.
Due to the size of the project, this is being posted in three separate posts. This also ensures each post is more focused.
Post 1 of 3, Core: Newspaper Bill ...
- 400
1
vote
3
answers
1k
views
Saving Multiple Lists to Database - C# Forms
I am trying to store multiple lists to a database but I'm struggling on finding a better way of doing it, as the way I am doing it now, seems to be incorrect.
On my form, I have 6 different legs. Each ...
- 21
0
votes
2
answers
183
views
Version 2: Display visitor's number on your web page (changed code after getting answer for first version)
Version 2: Display visitor's number on your web page (changed code after getting answer for first version).
The first version is here: Display visitor's number on your web page
The "visitors&...
user245050
0
votes
1
answer
231
views
Display visitor's number on your web page
Display visitor's number on your web page.
The "visitors" table has only one column (visitor_count) and only one row. The column's initial value is 0.
I know goto should not be used but then ...
user245050
3
votes
2
answers
100
views
Change query strings with a Bash script
The following script changes start and end dates of several queries saved as files in the folder /app/queries. It is used by executing the script and passing two ...
- 319
1
vote
1
answer
39
views
PostgreSQL query which return metrics joining different tables
I have a query that can be seen on this fiddle and I'm relatively new to PSQL, having a few months of experience.
DB Fiddle
As mentioned in the comment I should put the query here and the code is ...
- 271
0
votes
0
answers
41
views
Extract the divisors between dividing and divider
It turns out that I have to do a procedure called THEDIVIDERS which receives two numbers for parameters, in my case the parameter 1 is by dividing and the second divisor parameter.
Statement :
Make a ...
- 123
2
votes
1
answer
599
views
SQL query to find expenditures of employees who have exceeded their limit
I've 2 tables emp and expenditure.
Emp:
ID, NAME
Expenditure:
ID, EMP_ID, AMOUNT
Each emp has a limit of 100 that he/she can ...
- 137
1
vote
1
answer
73
views
Updating multiple database tables when products are shipped back by customer
So, in this example I have to make these changes in my tables:
Select Product Data => SKU, QTY
Insert into shipping_back table
Update in shipping_back to current date
Update in customers to ...
- 75
2
votes
0
answers
22
views
Plpgsql function that redacts substrings wrapped with double pipes `||`
I'm using Postgres 14 and I've written a function (for use with Hasura GraphQL Computed Fields) that transforms a TEXT field, replacing all instances of spoiler-annotated text (e.g. ...
- 121