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

2
votes
1answer
29 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
112 views
+100

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
62 views

Improvement of delete method [closed]

I was wondering if there is a better way to code this: ...
16
votes
6answers
606 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
268 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
67 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 ...
0
votes
3answers
53 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
61 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
35 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
58 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
71 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
167 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
55 views

Autocompleting locations

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

Database design for an exam system [migrated]

I am working on creating a simple exam system, in which you are going to be basically able to store students, classes, exams, questions and scores(and some other stuff that those depend on). I am also ...
10
votes
3answers
127 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
78 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
63 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
42 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
39 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
45 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
35 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
51 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
27 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
70 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
119 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
401 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
69 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
47 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
56 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
56 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
106 views

Schema design for user profile

I have the below schema for user profile management: ...
0
votes
2answers
48 views

Django ListView with MySQL query for the queryset

(Note: Even if you don't know Django, it's probably the query that needs work anyway, so you probably don't need to know Django/Python) I have a ListView that ...
4
votes
3answers
306 views

For a query over multiple databases, is it faster to do GROUP BY first or UNION first?

Having several databases with the same table, I need to group the union of the records. The xTables are stored in separate databases named by the year (this is by ...
3
votes
1answer
45 views

MySQL case statement with two tables

How can I improve the following working SQL statement for better performance and consistency? ...
3
votes
1answer
68 views

Getting a value from a database table dynamically

I am trying to create a forum using php in a MVC architecture. This is my database setup: Table: forum_categories ...
2
votes
1answer
21 views

Improving sub select query on two tables without relationship

I am trying to return the count of employees by salary band and can return data using the following sql but wondered if this was the most efficient way of achieving it? I know creating a join ...
1
vote
0answers
443 views

Bulding a “LIKE” query in Yii2

This code is making an SQL query. The last andWhere() method adds a LIKE condition with one percentage sign at the right. I'm ...
5
votes
2answers
40 views

Out of one Oracle into another

A project that I have been working on required data to be transferred from an Oracle database into MySQL. The process I devised for that transfer involved a query (included below) on the Oracle ...
1
vote
1answer
41 views

Building query for search engine

I'm writing a basic search engine for my website. It works but I'm really unhappy with the code. I know it can't be improved but I'm lacking some experiences here. In my params from my search I get: ...
10
votes
3answers
269 views

Trading Card Game database schema for statistics

This database schema will be used in conjunction with various systems as related to an online Trading Card Game, mostly for permanent storage and statistics. Some example cases would include: JDBC ...
3
votes
1answer
37 views

Better way to divide by the total count SQL

I have this query where I am working out the percentage of peoples occupation, I have a sub query that works out the total count of people codes and that result is what I used to divide by, I could ...
4
votes
1answer
28 views

Grouping counts from tables by account ID

I'm working with this query to get counts from three different tables, and to group the results by account ID: ...
21
votes
2answers
1k views

Calculate amount of mugs StackExchange should give away so I'll receive one

Description Calculate how many mugs StackExchange should give away(for graduation) to top reputed users on codereview.stackexchange.com so I'll also receive one. (output is divisible by 50). Code ...
3
votes
1answer
41 views

Change Data Capture using HIVE

Could you please review my code and let me know if there is something wrong? I'm getting the expected results. I'm trying to do change data capture using hive. We already have an existing CDC in ...
2
votes
1answer
22 views

Sorting of nested categories inside a Common Table Expression

I have a PostgreSQL database which contains (among other things) the following table definition: ...