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
1answer
31 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.) ...
0
votes
0answers
7 views

Using a Drupal function to return ranked results [migrated]

I need some help with my syntax. I've posted some code below but I'm unsure about the SQL and the PHP code that follows it. Note that this is for a custom Drupal form module. This is just the ...
2
votes
2answers
38 views

Replace CASE WHEN statement with something more sensible

While checking my SPs today I found that I have, ...
10
votes
2answers
71 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 ...
0
votes
0answers
6 views

Return a default value if no rows are found in UNION ALL query [migrated]

Scenario I have a query developed from this question, were part of the optimisation was to create a MySql view which is used for generating statistics for users and sales, the problem is that when ...
5
votes
2answers
67 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
75 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
42 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
0answers
7 views

Create View having trouble with JOINS [migrated]

I have 5 regions that each contain 1 store and 1 warehouse. I have a table that contains the regions and corresponding stores and warehouses. I also have a table that lists the inventories of both ...
2
votes
1answer
35 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
2answers
24 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
31 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
79 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
17 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
65 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
57 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
130 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
67 views

Improvement of delete method [closed]

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

SQL Server: 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, ...
2
votes
2answers
86 views

Divide certain distinct row values into separate columns

This might be difficult for me to describe clearly but I will try anyway. Also note that this is entirely speculative; it might not even be able to run any faster (that is why I am asking). This is ...
1
vote
3answers
76 views

Looping to update article codes on one server based on queries on another server

I've read somewhere on Stack Overflow that doing queries in a loop is very inefficient. It will hammer your SQL server and make your script very slow. Sample code: ...
3
votes
1answer
70 views

Inserting data into database

I started learning PHP and MySQL, was using MySQL_ functions. Then I learned they are deprecated, had to choose between PDO and MySQL. I chose MySQL and started using it. As I learn OOP PHP, I ...
5
votes
1answer
39 views

Slow query joining orders and products

The end result I am trying to achieve is an array containing the total ordered qty for each product. I have been successful but the query takes about 20 minutes to run and I'm wondering if there is ...
7
votes
1answer
63 views

Fascinating, ma'am

Finding a famous question with high score I haven't voted on yet was proving to be a bit difficult for me. So I came up with this simple SEDE query to find good candidates: ...
13
votes
2answers
75 views

Bling Accounting - Badge summaries

A question was raised in chat (hi @malachi) about how you can count the number of badges of a given metal (bronze, silver, gold). This can be hard to do because the medal details are not stored in the ...
8
votes
2answers
168 views

Checking for Duplicates, Except

I have a query that returns records that have duplicate names within a given DBSTATUS. I used to do this with a direct connection to the remote oracle database ...
10
votes
1answer
60 views

Autocompleting locations

Can this function be cleaner? It looks to me that I have too much nested stuffs(using+if+while) ...
10
votes
3answers
132 views

Tag badges per user SEDE query

I wanted to see where I lined up with other users on tag badges (I wanted to count just bronze but figured I would start with all of them to start out with). Here is the query that I ended up with: ...
7
votes
1answer
87 views

Summing the prices from Transaction files

I need to cut down the run time of this query. Currently it's taking 45 minutes. Is there something I can change in the table or the query to allow this to run faster? ...
12
votes
2answers
82 views

Archaeologist Progression

One of the badges available on Stack Exchange is the Archaeologist badge. This is awarded for making 100 edits on posts that had been inactive (at the time of the edit) for 6 months. There is no ...
1
vote
1answer
31 views

Most efficient way to get columns where one is minimum

I have a table with 3 columns in the format below: WebcastChannelId, WebcastEventId, SortSequence 1 , 2 , 1 1 , 5 , 2 1 , 3 , 3 2 , 7 , 2 2 , 8 , 1 I would like to get the values for the ...
5
votes
3answers
90 views

Selecting Employee details from a complicated schema

I have a "litte" problem with my stored procedure because I need some values selected at a point in time. I need to do many select and group by's in my Left outer join which looks and feels like I am ...
2
votes
1answer
21 views

Selecting Timesheets

I'm trying to improve my SQL syntax, take the following for the example ...
2
votes
3answers
73 views

A class which represents an SQL table

I'm representing DB tables as classes (in this case PHP classes). This has the goal of modeling every section of the web page, where you will get inputs from the user. My actual base design is: One ...
6
votes
1answer
114 views

Run arbitrary INSERT INTO SQL Query using parameters

While DoCmd.RunSQL is all well and good for simple code, the number of times I've run into problems with unescaped apostrophes and the like was starting to nark a ...
3
votes
1answer
42 views

SQL stored procedure for fruit checks with several joins

I developed a SQL stored procedure which works lovely, but when under stress, it takes more than 2 minute to get back to the user. I am providing the stored procedure but would also provide the table ...
2
votes
0answers
49 views

Refactor (DRY, KISS) QueryStatus event for VS Addin

I have complex logic in QueryStatus event (CommandTarget) for a VS Addin (VS 2010). I'm a newbie using C#. I would like elegant, easily maintainable code that follows DRY and KISS principles. Maybe ...
-1
votes
1answer
36 views

SQL - Dynamic, avoiding SQL Injection [closed]

Aim: Dynamic SQL based on user input Use: User search boxes to get a result Query/Review Question: Does the below leave the code exposed to SQL Injection attacks? ...
2
votes
2answers
58 views

Social network message board website security

I have a script that contains functions from login in to posting status to friend request send and receive. Pretty much a social network script. Anyways i think my security is not up to date so if ...
2
votes
1answer
30 views

Simple SQL query to get phrase pairs from a translations table

This is probably the noobiest SQL question ever, and I'm almost positive there's a JOIN or something in PostgreSQL for this, but I can't find it so I'm using a ...
3
votes
2answers
79 views

SQL cursors and dynamic SQL

The idea is to have a heading for each date returned by the query. This could be any number of dates. I dynamically altered the #table which is ugly but there is no other way. Ultimately I would ...
12
votes
3answers
147 views

Creating an object oriented model in VBA using COM and ADODB from 2 depended SQL tables

The story... A bit of background info and how is the database designed... Please notice you don't really have to rebuild the tables in SQL but I shared an SQL Fiddle just in case and screenshots1 of ...
4
votes
3answers
404 views

Shapes that can be saved to a database

I have been asked to refactor the below code using SOLID principles and I was just wondering how you would go about it. I am not really sure if I am winning or not. Could you have a stab at it and ...
4
votes
2answers
80 views

Find minutes with no data in database

I'm trying to show a table, for each ID, to see if there is data available. In this case, its measurements. If there's no data, that means the logger isn't doing its job properly. I currently have ...
0
votes
1answer
79 views

Data Access Layer and Business Objects

This is my first cut at separating the data access layer out of my very old spaghetti codebase. I have tried to keep this as simple as possible, it is for a small project, so I am not really ...
1
vote
1answer
56 views

Counting business days in a month, ignoring holidays

In the past, I have worked mostly in MySQL and SQL Server. Almost everything at my current job is Oracle, though. I could have written this instantly for MySQL but I could barely google what I ...
0
votes
1answer
62 views

Reduce/Optimize repetitive code

I have 3 repetitive sections in my code, how do I refactor them? The differences are only in SQL query and in the foreach loops. ...
3
votes
2answers
91 views

Calculating Standard Deviation using SQL

I have been reading a bit about statistics to improve the way I understand data. This is my attempt at calculating Standard Variance using data tables. I solved this simple question: This is the ...
5
votes
3answers
116 views

Schema design for user profile

I have the below schema for user profile management: ...