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.
1
vote
1answer
54 views
Populating an HTML combo box by looped concatenation
This code takes the results of a database query and uses them to populate the options in a combobox on a form.
Would it be better to start with an empty htmlCode ...
-2
votes
0answers
12 views
How to yearly upgrade students in new grades/classes in school management system [closed]
I'm sorry if this question is inappropriate. if it doesn't fit here kindly give me specific link where i can get a solution or please answer here!
I've been through many solutions on this site but i'm ...
3
votes
1answer
44 views
Matchmaking system - sorting musicians by skills, genres, and location
I'm making a matchmaking system using PHP and MySQL which allows users to search through a list of musicians so that they can find people with the right skills and interests who are near them. It ...
1
vote
2answers
154 views
Multi-Table Unions [closed]
I've been using joins for years but in this instance I want to be able to do different queries on different tables which may have different structures due to clients. Long term we are aiming to make ...
8
votes
4answers
213 views
Sonar remarks make SQL unreadable?
I ran the following code through Sonar, a static analysis tool.
Original file:
...
5
votes
1answer
59 views
PostgreSQL multiple processes and queries or nested query
I have a series of tables and queries I run a process against and at times this process can take hours - if not multiple days - depending on the date range I select. I am running PostgreSQL 9.4.
In ...
1
vote
1answer
53 views
SQL Dependency with Broker
I have written a windows service for one of our local servers. This service works like a gem on my local machine, does what it's supposed to (ACCP to exchange db data), but I'm not overly familiar ...
3
votes
2answers
45 views
Eager load additional data about some instructors
Below is a snippet of code of a little practice project. I am still pretty new to EF and I was wondering if there is a way to write the below statement so that all required data is eager loaded since ...
2
votes
2answers
36 views
Query using cross join or inner join
Please consider the following three tables that I am playing with in the image below:
I want to come up with a SQL Query which shows first_name, last_name, and the total amount of all orders for ...
1
vote
1answer
75 views
7
votes
2answers
653 views
Preventing SQL injection by converting all characters to their ASCII values
Scenario
In order to prevent SQL injection, I'm converting every character of a string to be inserted in the database into its ASCII value before performing the query; in order to read the value of ...
1
vote
1answer
55 views
Performance shifting for SQL Database and Linq memory grouping
I am messing around with shifting between stored procedures and business layer EF getting data rapidly then processing in memory.
This works and on a static data range of 3 months I get execution ...
4
votes
1answer
36 views
Luhn algorithm MS-SQL
This a solution Luhn algorithm for credit validation.
This algorithm is a basic one inspired by wikipedia. I mean no check for length, type, and so on. You can enhance the base code as you want it to ...
1
vote
2answers
58 views
SQL Data Fetcher for CMS
I am creating a CMS for a website, so I've added most of the functions that will fetch data from the database to a separate class. The code doesn't seem to have any bugs(Since I've tested it) but I ...
1
vote
0answers
18 views
prestashop: get final product category
In general, in prestashop there are no funcions to really have the final (the one with highest level_depth) category of a product based on id_product, so I extended the Product class and added this ...
5
votes
3answers
85 views
SQL database design for ecommerce
I need to create a database which stores details products, Manufactures,Suppliers. So initially I divided Products into categories and subcategories.
These are the tables I created.
...
2
votes
0answers
61 views
Optimizing a Query in Entity Framework
I'm wondering what can be done to optimize the following. I've tried to note the points at which most of the overhead is occurring. The main two points I see are
The call to DiffDays from within ...
3
votes
1answer
41 views
Join - order by vote-sum and check if user has voted
I have a vote and a comment table. Now I want to display all comments and order them by votes-sum. Sum because some votes can have a higher value than others.
Therefore I want to display if the user ...
0
votes
1answer
56 views
API POST to save a new item in a Database via PHP & MYSQLI
Is this correct MYSQLI implementation?
IS it safe from injection?
IS the object cleanup done properly?
...
7
votes
1answer
33 views
Stack Exchange Data Explorer query to find upvoted questions with only downvoted answers
I have written a SQL query against the Stack Exchange Data Explorer that identifies answered questions with a positive score for which all the answers have a negative score (this was an answer to this ...
2
votes
2answers
23 views
CROSS JOIN solution for records “missing” from a link table
Use case: We have a simple two tables plus a link table setup using some modern RDBMS.
For example:
Hospital table (id, name, ...
1
vote
1answer
26 views
Select Queries From Parent-Child Tables Model
I have two tables (parent-child model): Loans & Transactions.
Each Loan row has multiple Transaction rows.
I want to select all rows from Loans together with most recent row from Transactions.
...
6
votes
1answer
35 views
Implementing EXCEPT functionality for social networking exercise
I am taking Stanford's Introduction to Databases Self-Paced online course. I have gone through the videos in the SQL mini-course, and I am having trouble completing the exercises.
The following is ...
8
votes
0answers
128 views
Optimizing performance of slow complicated article-sorting query
In short I have a bunch of articles (~100k) which shall be ranked ("mixed", hence "newsmix") based on their newsValue (how important they are, between 1 and 10) and newsLifetime (how long they stay ...
2
votes
1answer
29 views
Simplify SQL query that utilizes GROUP BY and HAVING BY and uses aggregation
I am taking Stanford's Introduction to Databases Self-Paced online course. I have gone through the videos in the SQL mini-course, and I am having trouble completing the exercises.
The following is ...
3
votes
1answer
39 views
Query about advertiser calls times out
I'm a bit of a rookie MySQL coder and I've been tasked with building a query that will join and filter several very large tables (one with ~210 million rows, one with 10 million, one with 2.5 million, ...
4
votes
3answers
152 views
Stack Exchange Data Explorer T-SQL to Find Users by Most Badges
I very infrequently use SQL. I need more experience, so I have created a data explorer query to find those users with most of any particular badge (have to edit the query itself right now to alter ...
3
votes
1answer
27 views
Return most recent non null field values for multiple unknown fields
I am attempting to create a stored procedure that looks at one table and imports changes in that table to another.
This stored procedure is going to be preformed on multiple source tables and will ...
6
votes
1answer
46 views
Generate random records for table in SQL
I'm doing a project in ms sql and I have to fill table with random records.
Here is a definition of table:
...
3
votes
2answers
99 views
Finding max rating for a movie
I am taking Stanford's Introduction to Databases Self-Paced online course. I have gone through the videos in the SQL mini-course, and I am having trouble completing the exercises.
The following is ...
5
votes
1answer
39 views
Finding bad Narutos
While looking for Naruto posts
(= overlooked accepted answers well worthy of upvotes),
I noticed some odd false positives,
selfie answers,
where the user who asked the question accepted his own answer ...
7
votes
1answer
96 views
Finding answers where happy customers commented “thanks”
To find answers where users expressed their gratitude (by mentioning "thank" in comments),
I put together this SEDE query (latest):
...
1
vote
1answer
82 views
Optimizing my program by accessing data locally instead of a remote database
I have a database with 4 tables filled with millions of rows. I have my program run on several computers computing data and then returning it to the database. The huge bottleneck in my program design ...
0
votes
1answer
52 views
EF Code First Seed Method for States/Countries Part I
I am working to complete this even further, but here's what I've got thus far...
(See my other post for the definition.)
...
2
votes
2answers
44 views
Replace CASE WHEN statement with something more sensible
While checking my SPs today I found that I have,
...
10
votes
2answers
86 views
Forgotten zombie killers
Zombies are questions with no upvoted answers.
I came up with this SEDE query to find answers with zero votes on questions that have no accepted answer and no answers with greater than zero score.
In ...
5
votes
2answers
73 views
Optimise web panel users statistics query
Details
Can anyone instruct me on a more optimised way of querying this statistics of registered users? I currently lack the knowledge to create a better query. Right now it works fine, but I want ...
12
votes
2answers
104 views
Naruto, where are you?
During Winter Bash 2014, the Naruto hat was awarded to users who gave an answer that got accepted but hasn't received votes for 12 hours.
It was a weird thing, really:
why would someone ever accept a ...
2
votes
2answers
48 views
Haversine formula in SQL
This is an implementation of the Haversine formula in Microsoft Transact SQL.
How can I simplify the function?
...
2
votes
1answer
38 views
SQL query to order by the difference between FIRST and BEST scores
Can anyone help me optimise this query? I have the following table:
...
4
votes
1answer
31 views
Selecting internationalization preferences
I have a query below using CTE. I am wondering if there are some approach other than this. So I am collecting some advise to refactor it. Or does the query look good even though I am using this ...
1
vote
0answers
33 views
Selecting all posts with a certain tag
The task is to select all posts tagged with a certain tag, along with other tags the post may have, given as text rather than ID.
Table posts
...
2
votes
1answer
111 views
Securing a form with image uploads
I am using PDO prepared statements to store user entered input and store that input to the database. The code will store a text input and 5 uploaded images.
Will this code protect from data SQL ...
1
vote
1answer
19 views
Inserting into Group_roles table for all rows in Group table
I have five groups in Group table as Group1, Group2, ...
2
votes
4answers
67 views
Selecting three stylesheets in a specific order
I'm somewhat new to SQL (only had to use it in Database classes). I have a simple table of stylesheets, from which I want to select specific ones in a specific order (e.g. reset, then layout, then ...
3
votes
1answer
62 views
Is the usage of SQL Cursor appropriate here?
I'm looking for following kind of feedback:
Firstly, is the use of a cursor an overkill here?
Is there a simpler way to do what I'm doing in SQL?
Could the script go terribly wrong?
I have limited ...
7
votes
2answers
137 views
What are my highest activity streaks?
I have written the following query to figure out activity streaks on a per-user basis. I find it... Ugly... And would love to improve it!
Limitations
Those are explained as commented text at the ...
3
votes
1answer
69 views
17
votes
6answers
628 views
Winterbash 2014 “Red Shirt” Estimation
Some fellow code reviewers (hi @Janos!) have been inquiring about a SEDE query to allow to check progress of the Red Shirt "hat" progression.
Try it here!
Background
Red Shirt
cast 5 ...
10
votes
6answers
3k views
Select first value if exists, otherwise select default value
Given an instance of SQL Server, imagine there's a table named Configuration, which has three columns: ID, ...
