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
0
votes
2answers
146 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 ~17 seconds to load now, I'd like to change that but I've no idea how. I've tried Gzip to compress it but ...
-3
votes
1answer
52 views

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

I have a Sql query ...
3
votes
1answer
774 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
172 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
913 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
628 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
42 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
828 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
109 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
255 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 ...
2
votes
1answer
279 views

Single MySQL Query one-to-many efficiency

I am fairly inexperienced with the ramifications of doing a single select where joining several tables and there are several one-to-many relationships while also running some built in MySQL functions ...
1
vote
0answers
31 views

Analyze the image of the database relation and create the original database

I've sorted out an image relational database, and created its original database in phpmyadmin (I use XAMPP). Here is a picture of the database relation: This is a database image I created myself in ...
0
votes
1answer
406 views

Excel VBA Filtering: AODB vs Autofilter vs SQLite

I have an Excel sheet with data in about 100,000 rows, 16 columns. I have to extract data (one value from the 1 row I find) from this table a few thousand times. I have been using Autofilter to ...
1
vote
1answer
47 views

Update Trigger (Comparing Descriptive string), as well as checking for valid updates

I have an audit table EpisodesHistory. One of the fields that gets copied is eDescription. I expect ...
1
vote
1answer
179 views

Ontario Health Card mod 10 validation

An example of the business rules are here. Does this code implement those rules correctly? Note that you will have to derive the rules from the example. ...
1
vote
1answer
23 views

Classifying counts of repeat visits per user for a website

I am trying to get number of times (or sessions) a user re-visited the website. What I'm trying to find out- Get the total number of users, number of sessions in the last 30days. I need to a pie ...
0
votes
1answer
39 views

Selecting data from a table based on value in another table (SQL server)

I am using the northwind example database to brush up my SQL server skills. I want to select all the products ordered by the customer with the customer id 'Folko'. My idea is to join the Orders ...
2
votes
1answer
398 views

Populate SQL tables with test data

I have some unit-tests that must be executed against a real database. This requires populating the database with test-data. In order to simplify this process I created the ...
1
vote
2answers
68 views

Select Records Constituting Percentage of Total

The Setup I have a table (call it myTable) in MS Access with two fields: Item (Text) and ...
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
2answers
669 views

Conditionally inserting MySQL entries in a Flask app

I am a newbie in back-end developing. I know that the biggest part of back-end development is interaction with database. I established a connection and the code is 100% working, but I am wondering if ...
0
votes
2answers
56 views

SQL Server Multi Term Wildcard Search on Multiple Fields with Ranking

I've built up this search query for searching airport locations on multiple fields, but I don't particularly like the way it works in practice. Even though it is fairly cool. I'll be reverting back to ...
3
votes
1answer
42 views

Comparing columns values and change their places by condition

There are three simple tables. Actor, Film, Film_actor. ...
1
vote
0answers
52 views

Creating SQL Indexes on Large Tables

I currently have a table with about 29 million records. The table currently has no indexes. However, I would like to build a composite index built on three columns. When I attempt to add the NON ...
1
vote
2answers
237 views

PHP PDO statements

I wrote a piece of code. and to be honest it seems a bit messy so I would like some opinions on it, and if I can make it cleaner. Or if there are better ways to do it: ...
1
vote
1answer
44 views

MySQL query to select number of fields occurring more times than x

I want to find out the 'rank' of a word in any given language. A word with a rank of 1 means it's the most commonly used word in the language. There are two MySQL ...
6
votes
1answer
432 views

Univariate analysis with SQL stored procedure

I wrote a SQL univariate analysis stored procedure taking as input a schemaname and a tablename on PostgreSQL. It is exactly like a PROC SAS UNIVARIATE. As output, you get less than 20 usable ...
8
votes
3answers
583 views

SQL trigger to log when employee records are updated

I am learning TSQL (SQL in general for that matter), and I wrote this trigger that inserts logs about updated employees into the tblEmployeeAudit table. When I was ...
1
vote
0answers
53 views

Sorting BIGINT column with ascending positive values followed by descending nonpositive values

I have a column flowing in with the full range of integers. Whilst generating keys on the INT column, I need to push the 0's and negatives down (ordered DESC) and ...
-3
votes
1answer
53 views

Optimize mySQL query [closed]

I have the following query: ...
0
votes
1answer
162 views

PHP & MySQLi Login Script - Is it safe from Injection?

I've been geeking up on MySQLi prepared statements to avoid potential SQL injection and I have managed to modify my legacy code (previously just using mysqli_real_escape_string). I am looking for ...
6
votes
3answers
218 views

Get values from db where column is '1'

I'm doing a project for my OOP Class, using C# with SQL Server, and I think I'm not coding the best way... I don't want code, just some guidance on how to improve my code. For example: I have the ...
1
vote
2answers
55 views

Stored procedure for processed orders in SQL Server

I am struggling with creating a Stored Procedure in SQL Server for getting sales statistics. I have managed to get the information I need out, but the query is a mess and I have probably messed up the ...
1
vote
0answers
106 views

Variadic templates to generate SQL string

We have this old MFC code here which uses CString and format specifiers to generate an SQL statement and then execute that SQL. Something like this: ...
1
vote
0answers
33 views

Find record with minimum value in column(s)

I've a table with plant names where I need to select one English synonym for each code: ...
0
votes
1answer
74 views

Looping in SQL Server to insert row into table at top

Here I have a stored procedure that seems to be taking a little longer to run than I had hoped for. What made this algorithm so lengthy was the fact when adding an element to a new table in SQL it ...
1
vote
0answers
65 views

CRUD in JSP AND SQL

I would like to simplify my code when I add an entry. All is OK for 1 table (table = "ecole"). I can add an entry without problem. My problem is that I will wish to add a second table named "nom" in ...
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 ...
2
votes
1answer
51 views

Trigger Updating another table - Baseball Database

This is my first time writing T-SQL so I'd like to get some criticism on how I wrote this trigger. I'm building a Pitch-Tracking App that will use a relational database to track Pitches, At-Bats, ...
1
vote
0answers
396 views

Selecting coins with alert count by using a subquery

I have a table called alerts and is related to a table called coin (coin can have many alerts). I would like to return a list of alerts but also return a subquery count of how many alerts that coin ...
1
vote
0answers
76 views

Insert complex object into multiple sql server tables

I am inserting data into three tables Properties, Rooms and RoomImages, using a stored ...
2
votes
1answer
325 views

Duplicate Payments using SQL

Working on identifying duplicate vendor payments using SQL in MS Access. The business logic uses four column; Vendor #, Invoice #, Invoice Date and Invoice Amount. There are 5 core reviews, if all are ...
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 ...
6
votes
3answers
18k views

Shortest and longest city name and alphabetical order

Problem Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one ...
5
votes
2answers
1k views

Code which pulls from SQL and dumps in Excel

I have a working code that pulls data from SQL and dumps in a column. Unfortunately it is extremely slow as it takes around half a second to pull each data point and it needs to be much faster. Are ...
0
votes
1answer
561 views

EF Core Complex Query Method

The code is returning reviews based on users like status order. It seems it executes a lot of SQL queries, so do you think it can be simplified and can it be a headache to server performance? ...
-1
votes
3answers
70 views

Insert to table from a temporary table [closed]

I created a procedure involving dynamically collecting from various projects (databases) some records into a temporary table and from that temporary table I am inserting into a table. With a ...
6
votes
1answer
801 views

SQL lexer in JavaScript

Context Some time ago I wrote a very simple SQL interpreter for Javascript for fun one weekend and have since started using it in production in a few projects for work, and as requirements change for ...