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
1
vote
0answers
9 views

Moving hierarchy with the most revisions with more performance (SQL)

I have hierarchical table for my features with a parent-child relation. There was an unique constraint missing, why I have branches in the data, where multiple children relate to one parent. To add ...
1
vote
1answer
17 views

Count distinct records in a JOINed SQL query

I have two tables with a 1-many relationship, Device and TestResult. My application has a filter builder front-end that allows a user to retrieve TestResults based on whatever filters they specify. ...
1
vote
2answers
62 views

Faster/Better Processing for SQL While Loop

I have a list of about 55k Accounts that I am cleaning up. 95% of them are from US/Canada which is what I'm most concerned with. I am going through record by record and will perform various checks ...
1
vote
1answer
26 views

Calling a function within stored procedure to derive a value

I am trying to write a sql storeprocedure that basically returns a derived value from an outcome of a function call. The derived field returns the difference in days and if the difference is less ...
2
votes
0answers
27 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 ...
1
vote
2answers
34 views

SQL to match string value against multiple columns

I have a table called tblStudent having data as I want to retrieve records having single value compared against multiple columns. For example - find all students having Grade 'C' in any of the ...
0
votes
1answer
35 views

Get average post score of TOP 10 tags on StackOverflow using Stack Exchange Data Explorer

I want to use the Stack exchange data explorer in order to display some basic information about the top tags. The query should print the number of questions and answers, the average number of answers ...
1
vote
0answers
23 views

filter SE posts with specific keywords

I am trying to find all the SE answers which contain "on context" in a single sentence, in the meanwhile the "on" is not a part of common collocations in any form, such as "depending on context", "...
0
votes
1answer
29 views

PHP - Importing columns from uploaded CSV file to SQL database (Laravel 5.7) [closed]

The goal is to upload the columns from the csv into the SQL Pro database. I am able to select the column names that have been uploaded from the csv file, the columns from the csv file are then ...
2
votes
0answers
17 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 ...
1
vote
0answers
23 views

stat the word count for a given stackexchange user

Sometimes I would like use word count to describe someone's contribution, so, I wrote a query to stat the word count for a given stackexchange user, based on SEDE. click to run it online ...
1
vote
0answers
21 views

Database design for Instagram-like post/comments

I'd like a review of my design for an Instagram-like application where users can post and comment on pictures. I just finished a freecodecamp crash course on database design and this is the first ...
1
vote
0answers
16 views

Creating Quantile Cutoffs using SQL Hive

I have a table in Hadoop that looks like this (with up to hundreds of columns and millions of rows): ...
1
vote
1answer
34 views

Database Design for representing category relationships

So I am new to Databases.Recently I was asked this question in an interview where they expected me to come with a schema for storing and retrieving categories for an E-commerce website in an RDBMS. ...
1
vote
1answer
50 views
4
votes
1answer
53 views

Script to push data from one database via api to another

I'm still a beginner so any pointers to make this code more professional/production ready are appreciated. The tokens and database connections have been anonymized. The script is working, its goal is ...
1
vote
1answer
41 views

Dynamic SQL query for count data

I'm working on an application that counts the number of employees in each department. Here's my code: ...
0
votes
0answers
20 views

Django model manager improvments

I'm trying to get a computed field at my Project model. It should calculate all foreign key data specific field. At first, I created a model property with a ...
4
votes
1answer
30 views

Select latest phase of objects not marked “remove”

I have the following table in a PostgreSQL DB (simplified for the question), where I want to select only the rows marked with *: ...
7
votes
4answers
2k views

Simplifying constructing native SQL query with nested if-else's

I've a method which is used to build a native sql query. I've 4 String builder as input and based on different conditions, I need to build my query. Here is the code ...
1
vote
1answer
35 views

capturing counts of open items between dates

DB version is: Oracle 12c. Restricted to the use of inline PL/SQL. The end goal here is to get a count of open items by day, inclusive of created date, exclusive of the end date. The data ...
1
vote
1answer
37 views

SQL String to get data from DB2

I decided to split the SQL Part away from This Question. Before we get started I need to let you know a critical piece of information: Due to permissions within an offsite database I am NOT allowed to ...
3
votes
1answer
83 views

SQL to Excel then to CSV file for Data Upload

Before we get started I need to let you know a critical piece of information: Due to permissions within an offsite database I am NOT allowed to create tables even temporary ones within the database ...
4
votes
1answer
116 views

Slow performing subquery

This query executes very slowly and I wonder if it can be improved? We have an Access database split to front end / back end with around 50 users. BE is in a network folder and FE is on users' hard ...
0
votes
1answer
53 views

Is my code for a forum page vulnerable for sql injections?

This is the backend code for the page where you upload a post. I have already tried to do a sql attack on this page, but it didn't work. ...
1
vote
1answer
25 views

Clubbing three queries that return mutually exclusive records

There is a table in Oracle database with the following data ...
5
votes
1answer
75 views

Excel to SQL server

I am self-taught when it comes to coding. I want to improve, so please give me advice if I have some bad habits or if there is in general a better way of doing this. The idea of this code is to look ...
2
votes
1answer
30 views

Assigning book ids to certain students on registration of a new student

I have a function where I am trying to assign book ids to certain students on registration of a new student. ...
1
vote
1answer
32 views

Scenario creator database design

I am working on moving a save system from loose files to a (spatial sqlite) database. The user places objects from a library in a 3d simulation. Scenarios are "what-ifs", what if we place 5 wind ...
2
votes
0answers
35 views

Python Script to look for DNS expiration [closed]

I just wrote a Python script that will take a list of urls and find which ones are about to expire. The program queries a database, gets the websites, sanitizes the list, then loops through the list ...
0
votes
0answers
48 views

Metro Train Route and TimeTable Database Schema

I am still learning MySQL and Database design. I need experts' advice to design the database in a well-optimized way. I want to store the following data Trains, Cateogies (e.g fast, slow, special ...
1
vote
0answers
26 views

Finding total values for each day - Postgresql

I wrote an SQL query to find how many leases were "active" for each day of 2018. So if a contract began on 27/12/18 and ended on 27/12/18 and another one began on 27/12/18 and ended on 28/12/18, the ...
1
vote
0answers
22 views

Postgres Schema Design for School Classes with Cancellations and Substitute Instructors

I have modelled a series of school classes as follows. The part I am not sure about is having a separate table for cancellations and substitute instructors. With this setup every time you looked up ...
2
votes
2answers
142 views

Calculate the midpoint of a polyline

I have polylines in a GIS database. The polylines are stored using a user-defined type called ST_GEOMETRY. ST_GEOMETRY has lots of spatial functions. However, it does not have a polyline midpoint ...
2
votes
1answer
93 views

Database Model for Feedback Module

I wish to generate an online feedback system. Requirements are Admin can enter feedback questions Admin will select these questions and create a feedback session One Question can be used in many ...
2
votes
1answer
21 views

Querying all records from one table, add column from another with foreign key constraint

Apologies for the cryptic title, I don't quite know how to title this question. I'm asking here rather than StackOverflow since I've got a working query, I'm just wondering if it can be improved. ...
5
votes
2answers
64 views

Add as newest ID to SQLite Database

At our company we use an SQLite Database to manage our Drawing IDs (CAD Drawings). The code is written in Python and I need to update it. I think the code I have has some problems, but I am quite ...
0
votes
0answers
33 views

Redundant query?

I have the following query: ...
1
vote
0answers
23 views

MySQL select statement for retrieving user role permissions

I have a java project that I'm working on and I'm trying to implement in my application role-based authentication. So for example when a user is logging and he is an admin he will have access to some ...
1
vote
1answer
30 views

query optimization and indexing

database relations can be found here http://www.tpc.org/tpc_documents_current_versions/pdf/tpc-h_v2.18.0.pdf ...
1
vote
1answer
114 views

Returning search results based off checkboxes

I use the following code to filter data from SQL and it works fine. However, below code is vulnerable to SQL injection, but all the ...
1
vote
0answers
23 views

Storing multiple translations for retrieval

I've designed a database schema to store multiple translations with the objective of it being easy to retrieve data and add more translations if ever needed in the future without needing to make any ...
1
vote
0answers
51 views

Blob data from Oracle to .zip file using python [closed]

I have been trying to get the blob data from oracle into a zip file using Python. I couldn't find the answer on any of the other links. Below is the code:- ...
3
votes
1answer
108 views

VBA and SQL to return SQL results to Excel

Now that I finally have time to look back at this code I wrote over a year ago. I need help with streamlining the code I have written. I had to write a significant amount of ...
2
votes
0answers
31 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 ...
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. ...
1
vote
0answers
27 views

Searching for Duplicates in SQL Table with Confidence Score

Sometimes I'm not sure if my questions should be here or StackOverflow, so I appologize if this is the wrong place. I have a SQL (MS SQL 2016) stored procedure that is evaluating data looking for ...
3
votes
1answer
66 views

SQL query on relational schema

I'm trying to find the best solutions for operations on the following database: ...
0
votes
0answers
25 views

SQL Server - Flagging overlapping time entries and calculating time without overlaps

Here's my example set up in rextester. https://rextester.com/LROA87361 I have the following table in gray (the rightmost 2 columns are for explanation). As you can see an employee's time entries ...

1
2 3 4 5
27