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.

161 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
8
votes
0answers
150 views

Safe dynamic SQL for generic search

Prompted by discussion about SQL injection, I wanted to put a proof of concept forward to get feedback about whether this is in fact safe and protected against SQL injection or other malicious use. ...
7
votes
0answers
99 views

Science Fiction Wall of Fame (Shame?)

I have a few of hobbies, one is reading science fiction and fantasy and another is coding solutions. I thought I might have a chance to combine both hobbies on this project. I have well over 1500 ...
7
votes
0answers
220 views

Executing dynamic SQL programmatically

Usage (all parameters are optional): ...
6
votes
0answers
110 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 ...
6
votes
0answers
536 views

T-SQL Hmac (Rfc2104 SHA2_256) Implementation

I'm trying to implement an Hmac in T-SQL using this question as an guide. Please critique this: ...
5
votes
0answers
931 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
40 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
0answers
1k views

Fastest way to insert rows in mysql vs sqlserver (large dataset)

I'm looking for any possible improvements that could be made to run the mysql piece of code faster. I created a simple test winform application that creates two docker databases : Sqlserver on linux ...
4
votes
0answers
78 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
112 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 ...
4
votes
0answers
112 views

T-SQL Pbdfk2 (Rfc2898 SHA2_256) Implementation

Using my previous function as a base I've come up the following: ...
4
votes
0answers
41k 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
24 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
41 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
0answers
32 views

SEDE query to calculate net migrations quarterly from ELU to ELL

Over on EL&U Meta, someone raised the question of how many net migrations there are to/from our sister site, ELL. The following SEDE query calculates the net migrations by quarter (...
3
votes
1answer
117 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
84 views

Reading Database, scraping Barchart and uploading prices to DB

This is my first real excursion into Python, coming from C#. I wanted to see if my code is well-formatted and efficient. The following will read the needed codes from a Database Table, then submit ...
3
votes
1answer
749 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 ...
3
votes
0answers
248 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 ...
3
votes
0answers
39 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
81 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
36 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
56 views

Stored procedure to describe the reason that a particular employee is unsuitable for a particular task

I used the following Stack Overflow questions as references for writing this code: Select columns from result set of stored procedure What is the equivalent of String.Join on TSQL? The idea behind ...
3
votes
0answers
33 views

SQL Trigger - Audting Config Table - Send Email with update details

Keep in mind i'm not an expert or developer, working in IT operations and improving my coding skills. Goal: Trigger to send email when a specific configuration table is updated or deleted. Should ...
3
votes
0answers
75 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
1answer
246 views

MySQL statements to update overlapping database entries and insert new data into various tables

As I've mentioned in previous questions, I am writing MySQL statements to update overlapping database entries and insert new data into various tables. My database design looks something like this, ...
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
57 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 ...
3
votes
1answer
48 views

Acquiring earnings information

The first part of the query grabs Policy's Premiums, Effective and ...
2
votes
0answers
15 views

OOP Python Blackjack game with player accounts in PostgreSQL

This is an OOP version of Lastest version of my Blackjack game. Also, it now uses PostgreSQL as database. ...
2
votes
0answers
20 views

T-SQL Query remove duplicates except for original row from the table

Question: There is an Exam_Merge table into which to import records from a similarly structured table in another database. In this case, the Exam_Merge table contains the values of the primary key ID ...
2
votes
0answers
43 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 (...
2
votes
0answers
27 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
54 views

SQL Database for Visitor Registration System

This is intended for a web-based application that allows someone to register visitors, assign their hosts, and indicate the dates in which they will be visiting. Imagine this scenario: I want to ...
2
votes
0answers
32 views

Get local datetime from UTC time

One of the app is inserting date in UTC format (column CreateDate). When reporting from this table, I used a difference in hours between the inbuilt ...
2
votes
0answers
921 views

MySQL database schema for e-commerce website

I'm making an e-commerce site, using MySQL for storing data. ...
2
votes
1answer
80 views

New comments after a user's last comment

Edit: I've added everything I can... I have the following query, which gets the last comment from the user in a given topic, then I check in the while loop if there are newer comments. My problem is,...
2
votes
0answers
34 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
279 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
80 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
33 views

SQL query for selecting count from grouped columns of latest records

I have this table on SQL: ...
2
votes
0answers
66 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
76 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
52 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
359 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
39 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: ...