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.

Filter by
Sorted by
Tagged with
3 votes
1 answer
45 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 ...
user avatar
1 vote
3 answers
69 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 ...
user avatar
0 votes
2 answers
153 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&...
user avatar
0 votes
1 answer
97 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 ...
user avatar
1 vote
1 answer
28 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 ...
user avatar
1 vote
1 answer
36 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 ...
user avatar
  • 217
0 votes
0 answers
28 views

Optimizing performance of T-SQL code

This is sql code is about showing business locations that are within a certain proximity to other of interest businesses. It will eventually become a stored procedure once it works properly. My basic ...
user avatar
0 votes
0 answers
31 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 ...
user avatar
2 votes
1 answer
508 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 ...
user avatar
  • 137
1 vote
1 answer
59 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 ...
user avatar
2 votes
0 answers
14 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. ...
user avatar
3 votes
2 answers
108 views

RawSqlQuery minimizing risk of field name confusion

Is there a way to make the following code more maintainable? ...
user avatar
  • 423
-1 votes
1 answer
41 views

Which one is faster and preferable when doing aggregate function to the data in sql? CASE WHEN or WHERE? [closed]

I have following simple sql code to select the number of rows that meet a specific criteria: SELECT COUNT(CASE WHEN /*condition*/ THEN 1 END) FROM table_name; ...
user avatar
  • 121
0 votes
1 answer
42 views

Search controller logic to search through models and manufacturers

I have the following Laravel controller that searches through two models - Manufacturer and SearchTerm (which contains models of manufacturers, synonyms of models as well as common misspellings) using ...
user avatar
1 vote
1 answer
39 views

Creating SQL query for relative timeframes and multiple tables

What I want to achieve: I have several tables of the following schema: +------+-------+ | date | val | +------+-------+ | DATE | INT64 | +------+-------+ I want ...
user avatar
2 votes
2 answers
134 views

Is this a good way to process an order? (SQL+PHP)

I'm making a simple webshop, it's not the full code, there are more security procedures. I'm just want to know your opinions. Thank you. These SQL tables I have: (I have more column in this table this ...
user avatar
1 vote
0 answers
37 views

Calculate time interval excluding breaks

I have a solution for a question I asked on SO: I am trying to create a database where I also need to find the time difference inside a record. However, timediff() ...
user avatar
0 votes
1 answer
48 views

Creating database and two tables linked by foreign key

These commands do what I want, but I'm trying to find out the best approach for this. My main areas of concern are: error handling and disposal of resources. In regards to error handling, I'm catching ...
user avatar
0 votes
0 answers
17 views

Using TVP and MERGE to OUTPUT Inserted Variables and TVP Variables

I have an application where a list of objects must be passed to an SQL stored procedure. The application needs to be aware of the unique ID that is has been given once inserted into the database. My ...
user avatar
  • 1
1 vote
1 answer
92 views

Try-catch-finally with 'using' in ADO.NET

I want to check if I'm correctly disposing of resources using Ado.NET. I'm not sure when the 'using' statement makes .Dispose() irrelevant. Please bear in mind I'm aware I shouldn't hardcode my SQL ...
user avatar
0 votes
0 answers
21 views

PowerShell to query SQL database with a string from a filename

I am trying to write a PowerShell script that will check a folder for any files and then for every file it will grab part of the filename and check it against the SQL database. If it found a match, it ...
user avatar
  • 1
0 votes
0 answers
58 views

Creating employee attendance report for some time period

I have written a logic which will get 2 months and get all months between them then I am looping all months and inside every month I am finding all days in that month then looping through these days. ...
user avatar
0 votes
1 answer
20 views

Comparing two columns across tables for a remaining subset

How can I find the unique phone numbers (and collapse them into a single column) from table_1 (while keeping the IDs and date fields), and remove phone numbers that appear in table_2? table_1 id ...
user avatar
  • 1
1 vote
3 answers
170 views

SQL query joining applications to categories

I do have large database and my query takes about 2s to complete on production environment which is kinda slow and would like to ask is there a space for improvement? My initial inputs are ...
user avatar
  • 119
0 votes
0 answers
45 views

SQLite loses performance versus SQL server and powershell regarding LEFT JOIN (INTERSECT)

I have the following files in a directory: 1256590_hashed.txt 1273301_hashed.txt 1177301_hashed.txt ... etc... these represent cut and hashed segments of documents ...
user avatar
  • 1
0 votes
0 answers
36 views

Create trigger that updates Table3 from an insert into Table1 that multiplied by a value in Table 2 in MySQL

I know this is probably more complicated than it needs to be, however, I would really appreciate some help. I'm trying to create a trigger in MySQL that will update the value in the Costs table when a ...
user avatar
4 votes
0 answers
436 views

Student report card management Python program

The program is designed to take data from a MySQL database, using mysql-connector-python, and print it as a table in Python using the texttable module. The program also plots charts based on the data ...
user avatar
2 votes
1 answer
32 views

Find coherent datetime ranges in postgresql

My Data consists of rows containing a Length/Duration in Minutes, a Begin & EndTime. The precision is 1min (or larger). the table looks something like that: ...
user avatar
  • 121
1 vote
2 answers
101 views

Moving files according to paths in database

I'm trying to move a large number of files. I get the path of source and destination from database. It takes hours to move images, and I was wondering if there is a faster way for moving large ...
user avatar
5 votes
1 answer
72 views

PostgreSQL: Grouping and Aggregating on multiple columns

Problem Statement: I am working on this simple dataset from Kaggle. I have provided a snippet of data with only required columns in below table. Dataset is quite simple, it has all IPL (cricket) ...
user avatar
  • 53
1 vote
1 answer
239 views

Beginner CRUD Console Application

I wrote this basic CRUD app + simple report so I could practice talking to a db directly. I purposefully avoided repository pattern and Entity Framework and only used a library to show the coming data ...
user avatar
1 vote
1 answer
74 views

Prevent Column Name Collision

I have been working on a series of posts about a library to connect to databases but most of the scenarios I have to resort to some "JOIN" the problem arose when two or more tables had some ...
user avatar
7 votes
3 answers
443 views

Schema design for user profile and transaction

I'm creating a schema design for a user profile and transactions. I am very new in data modeling and currently this is my design. The schema has multiple deposit ...
user avatar
  • 71
-2 votes
1 answer
45 views

T-SQL Query Performance [closed]

The query below already has indexes based its execution plan but it is still under performant with larger datasets. Are the additional null checks needed and why? Do you see anything that can be done ...
user avatar
  • 35
1 vote
1 answer
161 views

Database schema for discussion forum scraping

I'm scraping posts from various discussion forums and storing those posts, along with some metadata, into a relational database (SQL). My actual use case is a bit more complex, but I think I can give ...
user avatar
0 votes
2 answers
32 views

Table-Value Function with row-level-locking

I'm implementing row-level-locking on a MS SQL Server. The function I created below works, but even with a very small data set, it takes a significant amount of time to return the results. If I were ...
user avatar
  • 181
1 vote
1 answer
59 views

Simple website sql database

I'm getting into backend with postgresql and I would like to know how much of my example would fit for a real website database, just for storing and then displaying it on website. ...
user avatar
  • 163
1 vote
1 answer
192 views

Get result of a scalar-valued SQL function from C#

I do not receive files on holidays; I only receive files on regular days—i.e., non-holidays. I want to skip 2021-09-06 since that's a holiday (Labor Day) and I know I will not receive any files. ...
user avatar
3 votes
1 answer
91 views

SE Data Explorer: Users by City

I view users by city using this SQL query: ...
user avatar
1 vote
0 answers
74 views

ContactEditor - Excel VBA db app: Storage Library

ContactEditor uses Excel VBA as a prototyping platform for a personal information manager combining the "Model, View, Presenter" (MVP) pattern and a persistent storage manager. Fig. 1. Data ...
user avatar
  • 375
3 votes
1 answer
131 views

VBA introspection library for SQLite

SQLiteDB VBA Library is a set of VBA helper functions for the SQLite engine. The primary motivation behind this project is to provide convenient access to extended introspection features. Because of ...
user avatar
  • 375
0 votes
2 answers
78 views

Efficient way to build table within database or update it using C#

I have a headless application that does either of two things. If table does not exist within database create it and copy all the data over OR If table exists update it Now I usually use access ...
user avatar
  • 135
2 votes
3 answers
214 views

Queries for storing and get information from postgreSQL

I have been working with postgresql where I am trying to get information from database, update, exists and much more. I have created a context manager for commit and to close whenever it is in ...
user avatar
4 votes
3 answers
1k views

Optimizing the insertion of 400 000 records in sqlite

I have the python script that creates a sqlite database and fills it with data (originally coming from a json file). At the time I execute the code below, my word ...
user avatar
  • 143
0 votes
1 answer
25 views

Appending script from within function to header [closed]

I'm attempting to import an external script into an HTML file. The name of the js file that I want to import is hosted in a SQL database. Therefore, I am using a XMLHttpRequest to access the SQL ...
user avatar
  • 1
3 votes
3 answers
76 views

Order a table by column and get row number

In my database, I have table wp_postmeta, example: ...
user avatar
0 votes
4 answers
112 views

Appointment database - design II

I want to create a database which gives an admin user ability to create schedule for other users to book from. This is second edition of this database schema. I would like to know if there are any ...
user avatar
  • 177
1 vote
1 answer
67 views

Appointment database design - Schema

How would you go about designing a database for service appointment? Here is what i came up with so far. Let me know if there is a better way. Basically the admin should be able to build services list,...
user avatar
  • 177
-1 votes
1 answer
59 views

IF SELECT statement will break SQL ACID Compliance? [closed]

My QA told me that I should not use IF SELECT before doing an UPDATE statement because it will breaks the atomicity of a ...
user avatar
  • 141
1 vote
1 answer
343 views

SQL for precision and recall?

I have a table containing two versions of results for some predictors, one by human labeling and the other by machine predicting, and I want to calculate some metrics. ...
user avatar

1
2 3 4 5
30