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.

145 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
7
votes
0answers
139 views

Modeling tabular structure in MSSQL to store data like Excel sheet applying Column inheritance

I've created this database structure to store tabular information (it's a simplification of my real structure but enough to show the point). In it I have Sheets and ...
5
votes
0answers
142 views

Find unused stored procedures/functions in SQL 2012

I was given the task of finding those unused procs/functions in our SQL 2012 database. Initially my plan was to use the table sys.dm_exec_procedure_stats. I could set up a job to check the table ...
5
votes
0answers
953 views

A Groovier way of inserting and updating database records

I've written a simple little Groovy class handling my database interactions. All it has to do is check if a row exists in a target table with primary key values which match those of an incoming map. ...
4
votes
0answers
50 views

Postgres Shopping Database using Python

So I have just started to work with SQL and I have tested my script and is working very well when it comes to my database. The database is used for shopping. What I mean by that is that I scrape a ...
4
votes
0answers
45 views

Insert a page definition in the right order

I have a table SlideList that defines all available slides that can be put into a PowerPoint presentation. I have a table ...
4
votes
1answer
993 views

Multi-query PHP Parser for SQL files

I have an external project, which needs to run an SQL file containing multiple SQL queries (a bit like mysqldump, if you like, but is user-created, with any kind of SQL statement/definition). PHP ...
4
votes
0answers
94 views

SQL: How to only look up unique values

I have a piece of code that does what I need. But it looks complicated and messy for what it does. Is there a simpler way to look up the member CIN unqiuely without having a create a sub-query? Get ...
4
votes
0answers
43k views

Building a “LIKE” query in Yii2

This code is making an SQL query. The last andWhere() method adds a LIKE condition with one percentage sign at the right. I'm ...
3
votes
0answers
84 views

cleaning up c# code which connects to sql database using windows form

All I would like is for help with putting this code into separate classes as I cant seem to figure it out. I would just like to understand how this would be possible with this set of code. ...
3
votes
0answers
28 views

spark takes long time for checking an array of items present in another array

I am new to spark. I have two dataframes df1 and df2. df1 has three rows. df2 has more than few million rows. I want to check whether all items in df2 are in transaction of df1, if so sum up the costs....
3
votes
0answers
51 views

Generic library for querying various kinds of databases

Since C# 8 and .Net Core 3 are going to be released sometime this year, I wanted to try and convert the library I made to make querying databases easier, implementing the already accepted features (...
3
votes
1answer
209 views

MySQL JSON diff procedure

I'm building a procedure which compares two JSON objects and return two JSON objects with differences between each (compared keys and values). But I have relatively superficial SQL knowledge, so I ...
3
votes
1answer
151 views

ToDoApp - adding users, managing tasks, getting tasks

I've implemented a console ToDo application. I would like to hear some opinions about it, what should I change etc. About project: It is intended to manage everyday tasks. Application contains: ...
3
votes
0answers
42 views

Elixir SQL Query Returning Structs or Counts

I have a few raw queries which are similar to each other in that it is mostly duplicate code with the difference being that one returns structs and the other returns counts of how many there are. How ...
3
votes
0answers
56 views

Class to build and execute dynamic SQL statements

I'm uncertain about proper OOP design since my teachers are explaining OOP very vaguely. Do I need to use the constructor and the getters and setters like this or am I better off removing the ...
3
votes
0answers
90 views

PHP Laravel function to search for ads in MySQL

I am new to the Laravel framework. I have recently written a function which gets called through a route. The function is created for two purposes: Get ads from a particular city based on provided ...
3
votes
0answers
37 views

Correctness of a multi-language book collection SQL design

I'm currently designing my book collection software, because I couldn't find any that suits my particular needs, especially when it comes to translations. Here are my particular needs: Books, ...
3
votes
0answers
328 views

MySQL Schema for Tag System Works, But Can It Be Better?

I'm working on system that tags images. I'm going to use the xxHash (lz4) for hashing images. With hashes of size 64 bits, it looks like I won't have a 50% chance of a collision until ~4 billion ...
3
votes
0answers
80 views

Query for a ticket system involving cases and casts on many attributes

I have this SELECT statement that inserts into a temp table and it is slow! Right now it takes ~1500 ms to run and only deals with ~7000 rows. As the data grows ...
3
votes
0answers
58 views

SqlDataConnection update method

I'm using Microsoft's SqlDataConnection type provider. However, the only way I've seen to update a row in the database is to use something similar to this where it updates mutable values: ...
3
votes
0answers
34 views

Updating ActiveRecord with text from a joined table

I have an Active Record update with the following data model: TvMenuProfile has one rich_text_special ...
3
votes
0answers
114 views

Custom MySQL duplicate_key update queries

The goal is to create an activity or update its counter if it's already present. And to be super accurate, the code avoids race conditions with a custom ...
3
votes
0answers
59 views

PL/pgSQL autologin token generation

Here's an excerpt from my migration script for my database for the new "remember me" login option for my web application. Logging in with a valid username/password combination (authenticate_user) ...
3
votes
0answers
44 views

Updating results when an opinion poll is answered

Let's say I am trying to build an opinion poll app, such that I can create a template of an opinion poll, give it multiple sections/questions, assign multiple people to different copies of a given ...
2
votes
0answers
30 views

Python class for helping template SQL queries when working with psycopg2

While working on a project to do some spatial data processing in PostgreSQL, I found it cumbersome to use the psycopg2.sql objects to safely compose queries. I ...
2
votes
0answers
19 views

Entity - Model (EAV) system with delta updates

I am trying to create my first eav system that can handle updates as well. The user can create models with list and objects inside and with references other models. the user can then create instances ...
2
votes
0answers
36 views

Python Wrapper for Dynamic SQL

...
2
votes
0answers
47 views

SQL Code That Looks for Dupes with Confidence Score, 3hr / 1.2 BILLION Rows Returned. Optimize?

OK, I have a customer list that has approx 80k records. I need to search and compare looking for duplicates, but duplicates could be an exact match, or a similar match (only some data matches) so it ...
2
votes
0answers
35 views

C++ Sql-like base operating on csv files

I implemented very constrained trivial implementation of sql querries operating on user files (in csv format). Where possible i tried to use modern C++ features. The fancy goal is to serve MySql ...
2
votes
0answers
31 views

Insert data from one table to another (million+ rows)

I have a procedure that collects data from one table and inserts it into another table with the same columns, but after correlating with a few other tables. Since the existing table (from which data ...
2
votes
0answers
6k views

MySQL database schema for e-commerce website

I'm making an e-commerce site, using MySQL for storing data. ...
2
votes
0answers
36 views

SQL query that extracts a topic keyword from article titles

I have a query like this to select the only row with title contains specific programming topics. ...
2
votes
0answers
496 views

Simple web API that access sqlite database

This is a very simple web API for a school assignment, as a part of a bigger system. The web API returns a list of courses or a single course as json. I doubt that I will get any feedback on the code ...
2
votes
0answers
206 views

Upsert query using CTE in PostgreSQL 9.4

I have a generic pipeline in SnapLogic that logs the start time of the executions of a group of incremental jobs. Suppose, the log table is as follows. ...
2
votes
0answers
606 views

Kotlin DAO with Exposed: How to reduce redundancy in classes?

I'm writing a Kotlin app that uses Exposed (https://github.com/JetBrains/Exposed) as a SQL wrapper to write and read objects from databases. The problem I'm facing is that I end up with 3 classes ...
2
votes
0answers
35 views

SQL query for selecting count from grouped columns of latest records

I have this table on SQL: ...
2
votes
0answers
71 views

One class for MySQL and MSSQL Queries

EDIT BELOW I am trying to create resusable classes for MSSQL and MySQL queries (later also Update and Delete queries). I tried it doing the OOP-way. Here is my result: Einstellungen.vb: ...
2
votes
0answers
165 views

SQL/PHP Laravel - Looping through records to find relationships

I have two tables in my database, which contains a big list of organisations. They are called orgs_main and orgs_sub. This is ...
2
votes
0answers
55 views

Calculating effective shop inventories (SQL)

This is an SQL version of that question. Shops and groups The problem domain is a collection of shops (identified by an int shop_id number) each of which is ...
2
votes
0answers
662 views

Design database for OHLC

Requirements: I have to design a database to store tick data of 350 stocks. Inside this database queries will be executed to transform tick data and retrieve OHLC candles of 1 minute, 1 hour, 1 day. ...
2
votes
0answers
40 views

Counting distinct number of items in multiple group by query efficiently

I have the following query that counts the number of vessels in each zone for each week: ...
2
votes
0answers
110 views

PostgreSQL: maintaining non-overlapping temporal ranges

I have to store some temporal meta data used primarily in bulk processing of a time-series (sensor measurements). Each value of this meta data is valid for a potentially half-open range of time. ...
2
votes
0answers
44 views

Breaking down retention metrics

Does this code calculate the retention rate per acquisition channel correctly? $$ R = \frac{E - N}{S} * 100 $$ Where \$E\$ is the number of customer at the end of a period, \$N\$ is the number of ...
2
votes
0answers
389 views

Create and email an Excel report of data retrieved from SQL

This is my first Python (3.6) script which I would appreciate some critique of. I am generating a daily alarm report. The data is gathered from an SQL Server database, presented in an xlsx file ...
2
votes
0answers
351 views

Query your MySQL server for table size by row count and physical size by database

After writing a similar SQL procedure to check my table sizes several times, I wrote this stored procedure to check my MySQL database's table size. It will show you the table sizes and row count ...
2
votes
0answers
55 views

Joins on same table with MAX()/MIN() and corresponding fileds

I made a query for showing max and min value and dates that are corresponding to those MAXs and MINs. The idea was to show max/min value and the date when the those values occurred in one row for ...
2
votes
0answers
26 views

Filter table with drop down values given multiple constraints

I have a table that stores drop down list values. The table looks like this: ...
2
votes
0answers
47 views

Creating many aspx pages

I just want to ask if its okay to create many aspx pages. What i did was, i made a guitar store website that has 6 brands and each of them have their own webpage. The code is like this: The aspx.cs ...
2
votes
0answers
35 views

Using a Closure to generate express route handler function

I'm using a lambda function to generate the callback handler for each list page in a group of routes, because the sql is very repetitive for each of these routes. The lambda function uses a callout to ...
2
votes
0answers
54 views

Api occasionally slows down at runtime, possibly due SQL/connections

I'm currently working on an API that behaves incredibly unpredictable at runtime. Context: We run the same call 100 times without delay in between. A call can take anywhere from ~200 ms to ~5 seconds. ...