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.

learn more… | top users | synonyms

0
votes
0answers
28 views

Pulling specific random values from a PHP array [on hold]

On the page I'm creating there is graphic representation of pigeon-holes with five compartments for new comments. If there is new unread comment it should show up as graphic in one random compartment. ...
-1
votes
0answers
22 views

Storing attributes against products [closed]

I'm created a database - part of which is below. I'm not an database or SQL guy so am looking for advice and potential to improve the schema. The function of the schema is to store ...
1
vote
1answer
64 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 ...
3
votes
1answer
54 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
160 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
221 views

Sonar remarks make SQL unreadable?

I ran the following code through Sonar, a static analysis tool. Original file: ...
5
votes
1answer
64 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 ...
2
votes
1answer
76 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
47 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
39 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
99 views

Two tables for storing comments

I have two tables for storing comments comments ...
7
votes
2answers
673 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
59 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
38 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
59 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
24 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
94 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
68 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
42 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
36 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
30 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
36 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
135 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
34 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
154 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
28 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
59 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
107 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
83 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
55 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
45 views

Replace CASE WHEN statement with something more sensible

While checking my SPs today I found that I have, ...
10
votes
2answers
88 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
74 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 ...
14
votes
2answers
119 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
53 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
39 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
33 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
34 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
116 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
63 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
138 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
71 views

Improvement of delete method [closed]

I was wondering if there is a better way to code this: ...
17
votes
6answers
629 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 ...