Tagged Questions
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.
2
votes
0answers
6 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
22 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:
...
3
votes
2answers
16 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 ...
0
votes
0answers
12 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
58 views
+50
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
62 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
56 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
129 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
66 views
17
votes
6answers
615 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
313 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
75 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
65 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
66 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
37 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
61 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
74 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
58 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
128 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
2
votes
3answers
66 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
57 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
41 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
48 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
28 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
73 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
129 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
71 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
49 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
58 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
63 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
108 views
0
votes
2answers
56 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
308 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
584 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 ...
