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
4
votes
1answer
49 views

Stack Overflow Tag Usage

I am trying to efficiently use the SEDE tool to get a list of tags a user has answered questions on, and their stats on each Tag. My code works for my account in 3185 ms, but if I try to run it on @...
5
votes
2answers
125 views

Combining SQL Tables into One Table in New DB with Similar Fields

I have a SQL Server Database filled with Reports all having the same schema. What I've done is created a .NET application to automate the process of adding all reports to a new table. How can the ...
4
votes
1answer
196 views

SQL QueryBuilder

I've made a simple querybuilder for my job, but I'm kind of insecure about starting to use it because I'm afraid of errors happening when I start using it. I've done some tests with ...
1
vote
2answers
216 views

Merge table stored procedure

I wrote a stored procedure that will do a merge of two tables: ...
2
votes
1answer
77 views

Database model for a hotel

I have to create a Hotel database according to these requirements: information about guests should be found, information about rooms divided into categories, reservations (ordered by a type of a room ...
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 ...
5
votes
2answers
452 views

Searching for a word in a list of tuple extracted from a db via SQL

Python 3.6 and Sqlite3. I have a database with something like 300K entries, each entry has several fields (date, username, etc) and a "text" one. I have a list of 200 words and for each word i have ...
3
votes
2answers
431 views

Secure PHP login with SQL injection prevention

I am working on a project where users have to log in then if it is correct it creates a cookie to process requests. I would like to find a way to auto-renew sessions every day without affecting the ...
3
votes
2answers
83 views

SQL Server - Iterate, aggregate, and insert

I'm trying to find an efficient way to aggregate data for reporting. Let's say I need to aggregate the following data in 5-second intervals: ...
2
votes
1answer
46 views

Comparing data in SQL using foreign keys

The following is given: ...
4
votes
1answer
31 views

Parse numbers from text based on relative position to certain substrings

The input is a product code. I want to pull out the "percentage" portion of that code. For example, if the code contains the substring BIO, I want all the numbers that follow it until I hit another ...
2
votes
1answer
59 views

SQL Query to check if 100+ (or Dynamic) columns in table is null

Let assume a table has more than 100 of columns & I want to display all the rows when all of its columns (Col1, Col2, ..., ColN) are nulls except for the column(s) I mentioned (PK). This is just ...
1
vote
0answers
78 views

KNN search by distinct category in postgis/postgres

I have a postgres table, etablissements with millions of rows, and a geom (st_point) column , spatially indexed (using gist). I'...
0
votes
1answer
61 views

Query to show each sites stock transfer history between all other sites

The below query is used to return a dataset for a SSRS report. It works fine and is quick enough but I am looking for any improvements that could be made. The client has asked to see all transfers ...
0
votes
1answer
35 views

Is using beforeFind() event in background to dynamic update SQL queries a good practice? [closed]

I have a Projects table, which I need to ORDER BY code column. But problem is ...
1
vote
2answers
55 views

Displaying a single record based on DocID

I have a table with ~900,000 records. I need to display a single record based on DocID using this query. The record is the latest record that got modified by another program. This piece of query run ~...
1
vote
0answers
54 views

Login/Register/Welcomepage/Updateuserprofile security improvements

I have created a Login, Register ,welcomepage(userprofile with updatefunction). I have done that without thinking about security. Now I would like to improve the security on these pages. Note: This ...
0
votes
3answers
125 views

SQL query to classify and count logged messages

I have an SQL query that fetches some stats, and those stats are displayed on a dashboard. The dashboard runs this query every 15 seconds to fetch the most up-to-date data. The query is very CPU-...
4
votes
2answers
57 views

Inserting two rows that are almost identical to each other

I'm trying to make test data for an application I'm writing, and some of my test data is very repetitive, involving inserting a row and then inserting another row that is identical except for one ...
-1
votes
2answers
720 views

Insertion of big TXT file into Sql Server

The code posted below reads 500000 lines from a txt file and imports the lines read in a Sql Server 2014 database, the code works correctly but it takes too much time (even 40 minutes), especially ...
1
vote
0answers
51 views

Determining how many tabs to create and how many lines per each tab

I wrote this PL/SQL code for Oracle Apex, works perfectly. At the beginning I had more than 200 lines for the same code. I’m new and I’m still learning. After searching on multiple websites I found ...
2
votes
1answer
92 views

Too slow JSON data processing using SQL

I have a case study where I need to take data from a REST API do some analysis on the data using an aggregate function, joins etc and use the response data in JSON format to plot some retail graphs. ...
2
votes
0answers
227 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 ...
0
votes
1answer
55 views

SQL query to return a set of calculated values per site from multiple tables

I have written the below and am seeking any way that I could improve its performance. ...
3
votes
3answers
280 views

PHP Signup & Login

I want everything to be well and secure with my authentication code. I've done many signup and login systems before but just for testing purposes, and didn't take into consideration all the security ...
9
votes
1answer
118 views

The seasoned Zombie sniper: a query to find unanswered questions with specific tags

The query (on SEDE) This query builds upon the previous and includes the suggestions by rofl as well as the now mentioned activity index. ...
-1
votes
2answers
77 views

Improving predominance for updating 40000 records using entity framework [closed]

I have Two tables Circuit and Tag, Circuit ------------------ Id Name Path Tag ----------- Id Name Circuit_Id Now I have to loop through all the tags and ...
11
votes
1answer
91 views

The sniper: a Zombie searcher for specific tags

Sometimes, it's not enough to know what the easy prey is. You also need to know whether you will be able to hunt it down. What good is an almost turned haskell zombie if you cannot read the functional ...
13
votes
1answer
1k views

A wounded Zombie finder, or: how to find the almost dead undead?

The Zombies are on their way to eat our statistics. Fortunately, we have a way to get rid of them: our votes. However, we need to find them first. The /unanswered section is too large and contains ...
1
vote
1answer
70 views

Decrease execution time in updating rows in PHP

Any tips on how the below code can be optimised for faster execution? I have a table in postgres called staging with the below columns; ...
1
vote
1answer
53 views

Insert and update SQL entries for posts to be accessed by up to 3 other users

At first, I have this HTML I use here: body { text-align: center; } ...
2
votes
1answer
53 views

Gathering articles from an articles database

I'm new to PHP and OOP and am building methods in order to gather articles from an article database. My method will receive three parameters: $limit, ...
0
votes
2answers
54 views

CTE Query and DateTime Matching

I have two tables (they are defined below and you can use the SQL below to build them) ...
2
votes
1answer
117 views

SELECT/INSERT between DB2 and MySQL

I've spent a week getting a very old script that runs between DB2 and MySQL condensed and functioning but I've been told the last few days on SO that my code is horrible and confusing and shouldn't ...
5
votes
2answers
1k views

Flagging employees whose spending is close to the budgeted amount

I am working with an SQLite database. Is this code written the best way it can be and correct? The code: Selects from the db adding a column for the percentage difference between two columns Loops ...
2
votes
3answers
74 views

search for text across multiple tables

I've got the following query. ...
1
vote
1answer
79 views

Display items from DB using MVC and Domain Driven Design

I am starting a project to put into practice the lessons learned in "Clean Code" by Robert "Uncle Bob" Martin. The project is a website that displays the names of items in a DB table. It uses ASP.NET ...
0
votes
1answer
210 views

T-SQL query outer apply with nested subqueries

I have the following 3 tables, and I don't have the option to modify them. [Logs] table with [log_type], ...
0
votes
1answer
130 views

Displaying the value of a stock portfolio

Some people have mentioned to me that my site is quite inefficient and given that it's taking ~17secs to load now, I'd like to change that but I've no idea how. I've tried Gzip to compress it but it ...
-3
votes
1answer
52 views

Difference between using WHERE and AND with ON in SQL [closed]

I have a Sql query ...
3
votes
1answer
751 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 ...
2
votes
0answers
166 views

Matching entities in SQL Server to replace iterative Python script

I have a SQL (MS SQL Server) database of ~22 million companies (called target_db in my code). For example: ...
4
votes
1answer
866 views

Runs a MySQL query, returns Dictionary<string, string> or false;

I've been practicing C# and ASP.NET, and have been working on a somewhat larger-scale project than I am currently used to. I am sure there are horrible things wrong with this code that I will find out ...
1
vote
1answer
604 views

Query Select and Insert if not exists

Are there any good ways to make this createIndexBlock() method shorter? It looks wastefully long. I have many tables. I don't like to make these kinds of methods. ...
2
votes
1answer
41 views

Inner Join only if satisfies If condition

I'm looking for code review, and optimizations. I have two tables ItemCategory and ShopItemCategory ItemCategory ...
2
votes
1answer
781 views

Navigation properties with sql/dapper

I want to have the same experiance with slq/dapper for my navigation properties just like in Entity Framework. But I'm not sure which sql query i should use. As you see, every single Resource entity ...
0
votes
1answer
73 views

Counting first letter of title and printing letters from a to z

I am trying to count the titles according to the first letter and filter it by the tag. I tried for ages and I am not sure if I can write this query in a better way. Can you please advise me. Here ...
2
votes
2answers
108 views

T-SQL simplify repeated query unions for each day of the week

I've created the T-SQL code below to get the next nearest datetime value during the next 7 days. The ScheduleDetail table schema is: ...
3
votes
0answers
249 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 ...
1
vote
4answers
67 views

Select multiple count queries in single query

I currently have 6 very similar queries that I'm trying to increase performance on by making them all subqueries in 1 query. I'm not sure what is better performance wise, keeping them 6 separate ...