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.
3
votes
1answer
22 views
6
votes
1answer
82 views
SQL code reuse with C#
I have some code that allows for SQL reuse for C#. I'm pretty happy with it, but the fact that I can't find anything similar on the web makes me think I'm going about it the wrong way. I have a GitHub ...
7
votes
0answers
28 views
Find & Delete Duplicate Records on All Tables in current MSSQL Database
The purpose of this code is to search the entire database for duplicate records and produce a script that a user could then run to delete all of the duplicates.
The stored procedure takes the ...
17
votes
3answers
3k views
Oh my goodness: How many selfies have I taken?
The title is a joke on how some people take a lot of selfies pictures
Recently, in The 2nd Monitor, there was a brief discussion on selfie answers, starting with this post.
At one point, a user said ...
2
votes
2answers
34 views
2
votes
1answer
36 views
Rock, Paper, Scissors, Lizard and Spock - Relational Paradigm
I found a few question about the game and IMHO it's a relational problem and better addressed by set logic. Thus I created this solution in SQL to get away from ugly JavaScript, elegant java and ...
5
votes
0answers
14 views
Finding closest points without duplicates and given distance threshold
Given a bunch of latitudes and longitudes stored as Points, I would like to determine the points that are closest to each point given some maximum threshold in meters. This is my first stab at this:
...
1
vote
0answers
20 views
SQL Query Count Records and Pull Distinct
So I need a little help. It is currently working but I might have over complicated it. So maybe someone has a better idea. Now let me try and explain it.
I am trying this query, and it currently ...
1
vote
0answers
18 views
Generating business summary stats in SQL dynamically, based on an inputted date
I have the following Oracle SQL query to generate some system-stats. I am interested in making it a more efficient and readable query.
...
1
vote
0answers
29 views
Archiving an SQL table of sessions and statistics
What my query is doing:
I have two SQL tables and I want to copy datarows from the one table to the other table. I am doing this for statistics. The source table includes all current ...
5
votes
1answer
25 views
Volunteer Signup Report - follow-up
Here is the original question. I've incorporated a lot of the feedback and I'm ready for another round of feedback and suggestions.
Here is the PHP file's output. It creates a volunteer signup report ...
1
vote
1answer
25 views
Selecting mail messages that meet one of four search criteria
This is the current very repetitive and long SQL I have. Unfortunately I'm not that good at SQL to group this and get rid of the repetition.
...
2
votes
0answers
14 views
Delivery date checker for e-commerce website
I’ve made an e-commerce website where administrators can specify the number of deliveries they can make on any one day.
I’ve written a SQL statement that then finds all delivery dates, finds any ...
4
votes
0answers
132 views
Stored Procedure code shortening and optimization
This stored procedure takes user defined table type as parameter. It contains data imported from an Excel file. Values inside that type are the same as in ...
1
vote
2answers
67 views
Float.Parse issue
I have telerik radgrid that has an SQL backend. What this function is doing is conditionally formatting the cells with in the radgrid. It checks each column to see if the min and max values are within ...
1
vote
1answer
37 views
Query performance of this Select Where each Column is a Select
I would like to know how to improve the performance of the following query:
...
0
votes
0answers
28 views
Health insurance claims query involving joins to fetch one column
General feedback is welcome, but I am especially wondering if there is a way to make things more efficient where I am joining an entire large table to gain use of one column (like when I join tables ...
-1
votes
2answers
44 views
-1
votes
1answer
113 views
Performance comparision of SQL queries versus LINQ in Visual Studio 2012
I am new to VS 2012 and need to use it with SQL Server. I need to get data from a table in SQL Server 2012 with 500 rows and 15 columns and process the data in it and update another table. All this ...
1
vote
1answer
26 views
Summarizing income and expense information by month
I made myself a simple budgeting application. Each transaction is stored in the transactions table:
...
1
vote
0answers
49 views
Updating datetime fields from UTC to local time zone
I have created an SQL query to update datetime fields from UTC to local time zone. Can someone review this?
...
2
votes
0answers
19 views
Annualized Turnover Report
What can be made better? How ugly is my code? If it's terrible, where can I improve?
...
8
votes
2answers
70 views
SQL to find table containing all specified columns
I have the below code to list all tables which have columns DataAreaId and CountryRegionId.
This works, but requires me to ...
0
votes
1answer
60 views
1
vote
1answer
29 views
Oracle query to split 1 row into two where conditions are applicable
The scenario question:
"I'm having difficulties with splitting a single row into two individual ones."
My test schema is:
...
5
votes
1answer
44 views
Impyla parse user args into SQL query
My Python is super-rusty and I feel like there must be a better way to do the following. I would really appreciate any feedback/pointers so that I can improve my code and skills (or lack thereof!).
...
3
votes
3answers
83 views
Find a Id that contains all mentioned values
I have a scenario to find the CategoryId which all have the mentioned Mode values.
The schema of the table ...
26
votes
3answers
2k views
Excuse me, how many of you are new here? Mind if I JOIN you?
I had perceived an uptick in new reviewers around here, and I wanted to see if the data backed that observation up. It's been a while since I've written any SQL, so this was a nice little exercise in ...
4
votes
2answers
46 views
SQLite UPDATEs for persons and a subclass
I am using Java with SQLite and I am wondering if I am in the right track onto writing good code i.e. more or less reusable:
SQL scheme:
...
13
votes
2answers
75 views
Sales, Taxes and Totals
I'm working to get some practice with SQL functions which are often used in a financial setting, and I created this simple table with products, prices and tax rates basically.
I then wrote a query to ...
2
votes
1answer
44 views
Simple DB API for inserting a record
I'm looking to make some improvements to a deliberately crude API written in PHP, it's designed to handle simple requests and talk to the DB.
I have the following DB class:
...
2
votes
0answers
37 views
Multi-Table Query Gotchas and Standards [closed]
Does my SQL statement contain any gotcha's or can you see any problems where it could fail?
Below is my first endevour into multi-table querying in PostgreSQL and I have a feeling that I may not be ...
3
votes
0answers
24 views
PL/pgSQL autologin token generation
Here's an excerpt from my migration script for my database for the new "remember me" login option for my web application.
Logging in with a valid username/password combination (authenticate_user) ...
4
votes
0answers
22 views
Tag wikis under a certain length
I wrote a SEDE query to list all tag wikis with a body or an excerpt under a given number of characters. I've used this to find empty wikis or wikis with very little information. The tags can be ...
2
votes
1answer
39 views
Implementing SQL function STR
I am writing a piece of code in c# that mimics the behaviour of following SQL function STR in the following case:
...
1
vote
0answers
19 views
Find nearest location
I am trying to implement a "find nearest location" from a given latitude and longitude using MySql. I have 8000 locations stored in my WordPress website.
I managed to write this:
...
5
votes
1answer
48 views
Searching posts/comments by keywords
I've put together another simple query that allows you to search post/comments with specific keywords. At the moment, only post bodies are checked.
...
4
votes
2answers
58 views
Handling request errors in snap with haskell
I'm currently taking a look at Snap with Haskell. I'm pretty new to Haskell and functional programming in general and am working out the best way to handle errors. Currently I have something like this
...
0
votes
0answers
30 views
Merge inserted and deleted tables in SQL trigger
I have a table with a Balance column of type decimal(18,0).
I want to get updates to its ...
2
votes
2answers
56 views
Custom SQL statement
I am just wondering if my code can still be simplified. I intend to make it reusable in all update statements.
...
9
votes
3answers
382 views
Popular questions by view count
I made this query to create a graph of a user's popular questions and the view count on that question. It allows for a minimum of 500 views, and a score of ...
8
votes
1answer
56 views
Searching text, starring, flagging and joins
I have an interesting performance concern that I would like to address before it becomes a serious issue. I created a SQL Fiddle to demonstrate the query, and the explain statement can be seen on ...
4
votes
0answers
40 views
Finding which users have the most questions with unaccepted answers
I was looking at a few profiles this afternoon and noticed a few users had plenty of open questions. This led me to wonder which users actually had the highest amount of questions with unaccepted ...
1
vote
0answers
35 views
Deciding when to update an image in the database
I have written a method which is used to determine whether or not I need to update an image stored in a database with an image stored on a local machine. The criteria for when an image needs to be ...
4
votes
1answer
75 views
Hotel reservation system with discounts and deposits
I'm making a hotel reservation app that has the following features:
Promo Codes:
The guest can choose to pay half a certain percent of the total_amount or the ...
3
votes
2answers
34 views
Percentage of questions that are unanswered or answered - Part 2
I've refactored my previous query and added better checks when selecting the data. The query link can be found here. The formulas used to calculate the two percentages are the same, and can be seen ...
6
votes
1answer
54 views
Fixing logs that overlap
Here we trying to fix logs that have overlaps with each other. Am I doing it right? Is there any way to improve/refactor it?
...
4
votes
2answers
81 views
Storing data into an SQL table using multiple threads and queues
I have a process running in a separate thread which reads data over Ethernet and raises an event for each read. Then, this data is processed over a tree of classes. At the top of my class tree, I want ...
2
votes
0answers
20 views
Trigger that updates counter
I've got the following schema, which can not be modified since it's a problem taken from an exam:
...
10
votes
2answers
295 views
Percentage of questions that are unanswered or answered
I put together this simple query which calculates the total percentage of answered questions and unanswered questions. The formulas used to calculate these are shown below:
$$
\text{Answered ...
