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.
6
votes
2answers
124 views
Saving and Loading data with SQLite
I'm in the process of learning SQLite in C# and I've come up with some generic methods for finding and updating data. I would appreciate any pointers with the design of my methods and/or my SQL ...
2
votes
0answers
27 views
SQL procedure for Archiving and Deleting
I have a requirement for archiving 5 huge tables from PROD to ARCHIVE server without losing the integrity of the tables.
The query makes use of the Linked Server functionality and current idea is to ...
1
vote
1answer
40 views
INSERTing a student info record based on form inputs
I want to improve my coding skills. How do I write this code with concatenation
...
3
votes
1answer
22 views
Database trigger to monitor insert/update events and update another table
I am working on a MSSQL Database (for a program called Sage 200). There are many tables in the database, however, I want to be notified of changes (new record inserted or existing record updated) via ...
2
votes
0answers
28 views
T-SQL Secure String Comparison
The previous version of my function was scalar-valued and employed a WHILE loop to do the comparison, this is SLOW. This new version is tabled-valued and uses a typical tally table in place of the ...
2
votes
2answers
38 views
PHP Dynamic QueryBuilder function - Security
This function is a working function, which builds a number of queries depending on the values of $_POST.
...
1
vote
2answers
26 views
How to combine these cte's into one SQL Query
I'm wondering if there's a better way to write this SQL Query. I have a table where I'm storing a row for each transaction that records the type of transaction along with the amount of money being ...
0
votes
0answers
17 views
Stored Procedure enhancement for updating columns in a subset
The purpose of this store procedure is to reach into a large database (test), query for a subset, and update a column value, but with a big constraint - I can only update one row at a time.
The ...
1
vote
1answer
18 views
Get top row for muliple registers
I came across a piece of code that generates SELECT queries from a template.
The template:
...
3
votes
1answer
47 views
Displaying a 0-7 ratings graph
I store ratings in a table called ratings.
The table has these columns:
id | user_id | value | ip | showcase_id
value is ...
19
votes
3answers
505 views
Stack Exchange User Activity Score
About
This is a SEDE query which will calculate your activity and participation on a site. It's geared for Programming Puzzles & Code Golf where questions are values more, but the values can be ...
1
vote
0answers
33 views
Potentially complicated query for advertising campaign
I'm looking for some expert advice with my working script below.
What I'm after / how the code works:
A merchant creates an advertising campaign with (let's say) a total budget of £100.
He then ...
3
votes
1answer
63 views
Closing SQL connection for async operations
The following code executes asynchron on the SQL database. Is the closing of the connection implemented in the right way? Are there any missing features regarding connection handling which need to be ...
4
votes
1answer
499 views
Persistent login (“remember me” cookie) using PHP
The code below is a attempt to create a persistent login cookie. I am an amateur and not professional so this is the first attempt to have secure remember me cookie. Now I want you guys to review two ...
-6
votes
1answer
50 views
I my code is vulnerable to sql injection Its hardly coded? [closed]
Is my code is vulnerable to sql Injection.It is hardly coded
...
1
vote
0answers
25 views
3
votes
0answers
22 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
...
0
votes
0answers
19 views
Validating an NPI in T-SQL (Luhn's Algorithm)
The requirements for validating a national provider identifier can be found here. Here's my attempt at a T-SQL implementation:
...
3
votes
1answer
51 views
Eloquent methods to calculate the average rent of apartments
I am working on a Laravel 5.1 website where I am counting the average rent of apartments in a specific area. Besides of that I want to get the average rent by amount of rooms in the apartment. The ...
3
votes
1answer
51 views
Stored procedure to query custom data tables as dynamic SQL
There's a lot going on here, but the background for why this is necessary is that there is a set schema, or 'core' set of tables that are prefixed with 'bu', and any core table can have a custom table ...
2
votes
1answer
15 views
Collating client ratings
The code is fetching records from a table called CSAT_SUMMARY_REPORTS[driver table] but due to the filters conditions applied which needs to be checked in another table called CSAT_REPORT_FIELDS.
In ...
0
votes
1answer
26 views
Finding colors that have not been assigned to a given user
Goal: make this code as efficient as possible.
Given a finite dataset (Colors - this isn't Crayola here :D) how do I know what colors are not assigned to a given user?
Additional requirement - the ...
1
vote
1answer
34 views
Get unique results that have duplicate entries? (intersection?)
I have a table of patients that has a field for which hospitals they visited. I want to retrieve a list of patients that have visited Green Hospital and have also visited other hospitals. Is this ...
3
votes
0answers
98 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 ...
1
vote
1answer
23 views
Parsing data from big json field and geocode services then store to PostgreSQL using psycopg2 instead django-orm
This code works, but my boss told me that it's very bad code, and don't want to deploy it. It is my first time working as a programmer and I have at least 3 months of experience.
...
2
votes
1answer
70 views
Find all the countries I can visit by just crossing direct borders
I was helping on this question. There you have a table border to indicate countryA and countryB have a common border. And want ...
-2
votes
1answer
56 views
Sample SQL Server code using pivot table
I'm using a dynamic pivot example. This SQL code has the purpose of perform transposing data from rows to columns, using a temporary table in SQL Server.
The data is initially presented this way:
...
3
votes
1answer
71 views
MSSQL query to look for duplicate record
This query took 6 seconds to complete. How can I optimize it?
Total records in table is 166803.
...
3
votes
2answers
119 views
Insert or update user data
I inherited an application that uses some stored procedures. Here is a sample of one of the stored procedures used to insert or modify user data.
would like to get the group's opinion on the code. ...
3
votes
1answer
49 views
Stored procedure to classify policies by sales date
Currently this is the store procedure I am working on, it is working according to some testing that was done. I feel that it could be improved though, could use some optimization, feedback or ...
2
votes
1answer
23 views
Looking for meta data which matches certain criteria and was created after a certain date
I've got the below SQL statement which runs in MySQL on a WordPress installation. I'm looking for meta data that matches certain criteria and was created after a certain date.
However, WordPress ...
3
votes
2answers
52 views
What are the top voted comments on closed questions, and who made the comments?
Following this linked question and the feedback it generated, I put together a new query which utilizes a JOIN.
...
3
votes
1answer
57 views
What percentage of down votes are users responsible for?
I put together this simple query to calculate the percentage of downvotes each user is responsible for and displaying them in order of downvote percentage. It's generating the desired results, but I ...
1
vote
1answer
21 views
Database schema for a products and bugs aggregator
As an exercise in learning, I'm creating a bugs/issues tracking and "aggregator" web application. A simple app consisting of products, issues, reviews and links related to it. The links will be urls ...
7
votes
1answer
31 views
SEDE Query to Find… Weird Question/Protect Combinations
I was interested in finding some of the weirdest question/protection combinations, basically out of curiosity.
I heuristically defined this as:
Low view counts
Not a moderator/SE employee
From ...
6
votes
2answers
69 views
0
votes
3answers
42 views
Counting pending bookings using a subselect
Can this query be improved? Is there a way to eliminate the duplicate function call?
...
4
votes
2answers
52 views
Top Python badged users from NYC
I forked this StackExchange Data Explorer (SEDE) query from another one, and made it a bit more complex. I'm looking for feedback on my style, as well as answers to other questions (see below).
...
2
votes
1answer
58 views
Parsing of XML in Python using minidom, and inserting data into a database
I have some XML that I am parsing in Python and I need to insert the data on the database. This is current implementation of my code that is working.
Actual XML:
...
0
votes
1answer
35 views
-2
votes
1answer
58 views
Adding tables and values to a database
I am having problems with my code because I am using a system called styleci. Can you please check it? How can I make it better and more secure?
...
3
votes
1answer
35 views
Query with many UNION ALL
I'm replicating a scenario on a sample set for better understanding.
Database Table: Products
Report required from that table,
the query is working fine, and I'm able to get the required ...
2
votes
1answer
27 views
Reportable Sp_Who2
One of my issues with sp_who2 is that you need to be in the console to run it as it recalls spid twice and most reporting tools throw this as an error.
My question is, would using the code below to ...
1
vote
1answer
74 views
Shredding XML into an SQL table
I have a few "given" things:
An SQL Server 2012 table with certain structure
Data provided as XML with certain structure
My code has to shred that XML into a ...
7
votes
2answers
164 views
Replacement for sp_MSforeachdb
Having been informed that sp_MSforeachdb is undocumented and unsupported, I have been working on creating a replacement stored procedure based off the work done ...
5
votes
1answer
67 views
Inserting products scraped from the web into a database
I am trying to speed up the runtime of my program as the API can make calls. However after adding the inserts to a database 3 mins turns in 7 mins after 180 API calls, which can be one a second. How ...
5
votes
1answer
68 views
Tools for transferring data from an API to a database
I explain everything on my current code but this question is more general "HOW TO" work correctly with this in this case but even next time.
I have 4 methods in my class, each does something but in ...
1
vote
0answers
28 views
Calculation of duration between two timestamps in different rows
In my sample data table below, all datatypes are varchar(255).
...
1
vote
2answers
71 views
Random uniqueID for each ID SQL
How can I improve this code? I wanted to know if there's a possibility to remove the for loop, and UPDATE a different ...
2
votes
1answer
62 views
Build SQL query criteria from form input
Very new to php. Using XAMPP, netbeans.
I am using a <form> to send various input parameters as a GET to retrieve data from a very simple two table MySQL db.
...
