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.
-1
votes
1answer
44 views
2
votes
0answers
11 views
Complex views are a little slow
I have this view vw_RFQPartVendor that's based on these two views vw_requestVendor and ...
2
votes
0answers
15 views
Replacing names in text fields with aliases to help de-identify data
I have a table with over 1,000,000 records. I need to replace any names in the text fields with aliases to help de-identify the data. For this example, let's assume the table is ...
1
vote
0answers
24 views
SQL Procedure to populate or create tables from views - performance issues
I have written the below to populate/create tables (t_) from all of the corresponding views (v_) in my database. I converted a previous script from using a cursor to improve running speed. Is there ...
0
votes
0answers
38 views
Connecting to a database with ADO.NET [on hold]
We are using ADO.NET to connect to a database in all the web applications we are developing. Normally there are few people using the systems and in a new web application there are more users than ...
1
vote
0answers
30 views
Optimize subquery that searches xml
The following query lists all domains from multiple companies in which a value occurs in an XML set. This is filtered to the most recent occurrence within a provided date range.
On large databases ...
0
votes
0answers
25 views
Conditional join of three tables [closed]
Table ABC has keys ID, INST, STR, CRR and has 4 million rows.
Table DEF has keys INST, STR, CRR, AGRP and FGLVL and 500 rows. DEF does not have the field ID.
Table GHI has keys INST, STR, CRR, AGRP, ...
1
vote
1answer
32 views
Acquiring earnings information
The first part of the query grabs Policy's Premiums, Effective and ...
9
votes
2answers
349 views
Who are these lurkers?
Last night in The 2nd Monitor there was a discussion about postless users - I wanted to see how many there are, whether they're one-timers that showed up once, registered and never came back, and ...
1
vote
1answer
27 views
Read uncommited rows and lock row on update
I have 5 (or more) instances of an application running on same or different machines. Each of these application will pass a comma separated string of UserIDs and the app should pick up a unique unused ...
17
votes
1answer
2k views
Where are my downvotes?
I've been thinking my posts aren't that great, and so I've been wanting to improve them.
One sure fire way to do this is to look at all of my downvoted posts.
Not knowing if Stack Exchange allows you ...
-1
votes
1answer
89 views
SQL QUERY: GET THE NULL FATHER [closed]
As a picture often says more than words, here is a basic sketch of my DB:
...
3
votes
0answers
19 views
Find unused stored procedures/functions in SQL 2012
I was given the task of finding those unused procs/functions in our SQL 2012 database.
Initially my plan was to use the table sys.dm_exec_procedure_stats. I could set up a job to check the table ...
0
votes
0answers
13 views
Rescheduling long production tasks to the next day
The code below is used for a table with production tasks. It moves tasks to the next day where the sum of hours required for actions belonging to the same production order is more than 8.
The code ...
0
votes
0answers
11 views
Stored procedure for product search with dynamic filters
Below stored procedure implemented for filtering and sorting operation.But number of table column increases below query becomes complex to read/maintain. This is good for small tables.
I want to ...
2
votes
0answers
36 views
Generating email with BCP
This code iterates through the table @Table1 to get the Purchaser, PurchaserID, ...
7
votes
3answers
399 views
Update 12 records in a database table in less than 13 queries
I've got a database table in my application called "periods". The structure of the table is:
Year (Number)
Month (Number)
Period (Text)
The Year column stores years (Say, 2016), Month stores months (...
3
votes
1answer
20 views
Selecting attributes corresponding to the most frequent entries in a junction table
I currently am writing a query which retrieves the max amount of rows for a junction table. I'll use the following example in SQL Fiddle:
MyTable
...
0
votes
1answer
23 views
Check user changed his password recently or not in specific period
I am working on Oracle SQL. What I am trying to do is:
Query a record with ID
If the record does not exists use user input value
If the record exists, compare ...
1
vote
2answers
236 views
SQL Prepared Statement Factory
I have written a simple prepared statement factory in Java and would like some feedback on how "secure" it is. By "secure" I mean that I can pass any old garbage in as a string and it should not allow ...
0
votes
1answer
39 views
Performant DB2 queries for aggregation context
I need to improve the following DB2 queries, I understand DB2 is slow by nature and that the infrastructure is not the most ideal but here's my scenario.
I have to go and grab data from ...
0
votes
1answer
27 views
Security check using an SQL call
In our latest Veracode scan for an application, I have come across the issue of Improper Resource Shutdown or Release. It is pointing at a function. Here's what the code looks like:
...
3
votes
3answers
189 views
Parameterized search query in C#
In the UI I have a single search text box into which users can type a query. The query will split the string and compare each word to each configured column in the database and return the matches.
...
3
votes
1answer
89 views
Passing multiple parameters to an SQL query
I am trying to use this VBA code to pass a SQL stored procedure multiple values from an excel sheet.
In order to have the procedure run multiple times and insert multiple sets of information into the ...
3
votes
1answer
39 views
3
votes
1answer
101 views
Complex report generator using huge SQL Query
As the title say, I have a huge SQL Query which was built over the time (as new requests came, fast workarounds were demanded).More, there were three DB Admins who contributed to this query, and I'm ...
2
votes
2answers
50 views
Rolling totals in a view
I have a view that performs some rolling totals. In this instance, it must be in a view. The issue is that it takes about 10 minutes to run, returning 2700 records. There should be a faster way, but I ...
1
vote
0answers
22 views
Sorting by pivot table results
I had to create a report for a customer by most sold products in the last month and sorted by parameters they have assigned.
We have three parameters on products: ...
0
votes
1answer
56 views
Get all recipes from database with the cheapest ingredients
I have the following query which tries to get all the cheapest products linked to my recipes and it takes 10 seconds to return from the database:
...
3
votes
1answer
49 views
List users, ordered by accuracy of soccer match predictions
I have a solution to make a ranked list of users that are predicting soccer scores. I have 3 tables:
matches contains soccer matches information.
...
1
vote
1answer
27 views
SQL query to select all teachers not in different table
For homework I had to do the following:
Give a list of all the teachers that don't have a classroom assigned to them.
It involves the following 2 tables:
Code ...
0
votes
1answer
43 views
Optimizing a query with multiple cases [closed]
I need some help optimizing this mess of a query below. The main idea of the query is that I need to run a group of machines( a.group = '3008000204') to find out which ones in the select group do not ...
4
votes
2answers
57 views
Generate SQL UPDATE from Excel CSV file
I must write updates in a database from a flat file (CSV). I want to do that in the shell, with tools such as AWK.
...
3
votes
0answers
24 views
SQL Trigger - Audting Config Table - Send Email with update details
Keep in mind i'm not an expert or developer, working in IT operations and improving my coding skills.
Goal: Trigger to send email when a specific configuration table is updated or deleted. Should ...
1
vote
0answers
35 views
First unique record from update queue
The goal is to select the first change in the UpdateQueue on change of Unit or UnitsRelative....
-1
votes
4answers
108 views
How can I choose the right string with fewer lines and less repeating
First of all I know my code is highly vulnerable to SQL injections, but I wanted to code the base first. I have a feeling this can be done with fewer lines and less repeating. I'm sorry if it is ...
2
votes
2answers
56 views
SQL Query to display minimum prices per country
I have developed a Tender management System sort of code for one of my client. This is the piece of code :
...
0
votes
0answers
35 views
Alternative session token implementation in flask
Here is my implementation of an alternative session token. The idea is to reset session_token every time get_id is called, so (I ...
7
votes
2answers
180 views
SQL query to compare two XML columns
I need to compare two XML columns. I currently have about 1000 rows of data that this is running against and it takes about 20min. Is there anyway I can improve this query to run faster.
...
2
votes
3answers
166 views
Booking appointments with MySQLi and PHP
The following code books one appointment for one or more slots. I have never used database before, but have seen so many warnings regarding SQL injections. Since it has never affected me I have not ...
1
vote
2answers
40 views
MS Access SQL query for active assigned cases
I've inherited an Access database and am looking to start using it to do reporting. I've written a query that gets me the information I'm looking for, I'm just hoping someone can have a look over it ...
3
votes
1answer
51 views
Printing the hierarchical representation, and finding all ascendants for a node, in a self-referential table in Python with recursion
I have a self referential database table:
CREATE TABLE category (
id INT PRIMARY KEY,
name VARCHAR,
parent_id INT REFERENCES category (id)
);
And ...
1
vote
1answer
84 views
Reading SQL file using injection and utility reader
I've come up with a way to externalize and read in my .sql files instead of having them in-code as strings. Note that I'm keeping the .sql file in the same package as the DAO (NOT separated into a ...
5
votes
1answer
38 views
Totalise the data of an insurance branch
I've inherited a piece of legacy code that's pretty huge, and I've no idea how to go about breaking it into smaller pieces, and how to make the DB queries more efficient.
The project is built on a ...
1
vote
1answer
28 views
Correlated subquery slow
I have a table that looks like this
Directory nvarchar(max)
Extension nvarchar(10)
Length bigint
I also have another table that provides the type of file (lookup table)
extension nvarchar(10)
...
7
votes
1answer
50 views
Load and Parse Stack Exchange data dump XML into DB table
I have put together a stored procedure to load and parse the Stack Exchange Data Dump into a relational database (akin to Stack Exchange Data Explorer). Each site has 8 XML files like these:
The ...
1
vote
1answer
25 views
Attendance tracker design MySQL [closed]
The task is to create a website which allows the admin to create classes, students and teachers. He/She should be able to assign students to classes and one teacher. The admin should also be able to ...
2
votes
2answers
52 views
Find items in a table that match a set of conditions in another table
I have two tables. Table 1 has sets of EXPORT conditions: e.g., SET 1 has FOOD, GARMENT, and TECHNOLOGY, and SET 2 contains GARMENT and TECHNOLOGY.
...
1
vote
0answers
40 views
3
votes
1answer
80 views
