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.
0
votes
0answers
17 views
A better\shorter way to write this SQL query
Is there a better or shorter way to write the SQL query below?
...
2
votes
0answers
16 views
Generating days in a specific range
I have a query in MSSQL, where I get a summary by days from a range in a table, and this is in union with other queries. This query is used to generate days in a specific range, then add the registry ...
1
vote
2answers
29 views
Displaying a greeting to one user selected from a database
I execute the following SQL statement to get back an data object into a variable.
...
2
votes
1answer
36 views
Method to act on parameter or from iterable if not parameters are passed
The scenario is as follows. The API of my program will allow the user to perform some method on a particular element (let's say a tuple from a database). If this parameter is not passed to the ...
4
votes
2answers
87 views
DAO to create, update, and delete a project
I'm trying to write my first "complex" program in Java. So far, I created a local H2 database but I'm not sure about the quality of my code (I'm newbie in Java) because I don't know how I can deal ...
2
votes
2answers
207 views
Update user country based on IPv4 decimal address
I have UserSignatures, a table with clientIPs, some of which have CountryIds that are null ...
0
votes
1answer
21 views
Get parent folder ids by folder id(s)
My Scenario:
#TmpDistinctFolderIds table contains one or many FolderId.
...
2
votes
2answers
60 views
TSQL query to gather amounts for an employees, spouses, and children in one row
I have a query that works as intended, kind of pulling from multiple records in a table, and flattening the data into one row. I am wondering if there is a faster/more semantically correct way to do ...
4
votes
2answers
97 views
Check existence of a row then update a column
not too experienced with SQL, wondering if I can make this better... I am trying to check if a row exists and then add one to the frequency column if it does, if it does not exist it should return ...
1
vote
1answer
52 views
Disposing of an SQL dataset
I am getting a compiler warning when running my code analysis. It is CA2202. It is saying that I have not properly disposed my dataset through all exception paths. Here is the code:
...
1
vote
1answer
40 views
Fetch data from multiple tables
I have three primary tables:
Customer
Product
Order
Relations:
Many to many between product and order.
...
3
votes
2answers
77 views
Displaying recent buyers' feedback on games
I have the following code made to output the following:
Code:
...
3
votes
1answer
51 views
Simple shop database site
I'm learning PHP, HTML and SQL - and wondered if anyone could take a look at this code and give me some feedback?
...
3
votes
0answers
34 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
1answer
14 views
SQL report of subscribers and their associated revenue
A user can have many subscriptions, and a subscription belongs to one plan. The plans table tells us a subscription's cost. Is the query below the correct one for ...
6
votes
2answers
163 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 ...
1
vote
2answers
57 views
Pivot script for sales numbers
I've recently been tasked with migrating some code stacks for an applicatin that is moving from using Access to using SQL-Server.
I don't usually use PIVOT all ...
4
votes
1answer
62 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
43 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
26 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
35 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
41 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
28 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
20 views
Database stored procedure with a “preview mode”
A fairly common pattern in the database application I work with is a need to create a stored procedure for a report or utility that has a "preview mode". When such a procedure does updates, this ...
0
votes
0answers
18 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
52 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 ...
18
votes
3answers
515 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
34 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
70 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
522 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
54 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
22 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
54 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
54 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
20 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 ...
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
36 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
99 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
27 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
74 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
57 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
73 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
124 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
51 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 ...
3
votes
1answer
28 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
58 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 ...
