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
33 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
21 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:
...
1
vote
1answer
37 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
51 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
21 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
votes
4answers
105 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
27 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 ...
6
votes
2answers
166 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
131 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
34 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
40 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
61 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
36 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
25 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
45 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
23 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
1answer
43 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
38 views
3
votes
1answer
68 views
3
votes
3answers
668 views
Fetching busy students and their latest product
I use SQL Server 2014 and I have three tables:
Student (ID, Name, Age)
Product (ID, Name)
StudentProduct (StudentID, ProductID, Date)
...
0
votes
0answers
40 views
MySQL query with multiple union and group by
My database schema is something like this:
desc SUB:
...
1
vote
1answer
62 views
Obtain values from SQL
I have a simple app which is checking occurrences of values. If there is already occurrences which I am looking for, it display that. If not, it adds.
...
0
votes
1answer
53 views
SQL message logger run loop
I am learning Java by reading source code of other authors. This Java method handles communication with SQL database. While the code does work several things concern me. For instance the ...
2
votes
0answers
15 views
Validate User By Comparing against SQL Data
I'm currently building a Windows Form Application using Visual Studio 2015 and the Visual Basic.NET programming language.
On the main form there is a Tab Control and an ActiveX PDf viewer control. ...
0
votes
0answers
57 views
Script Task to read complex Excel file structure with multiple sheets and export into database tables
Currently working in SSIS 2012 - Script Task to read complex Excel file structure with multiple sheets and export into database tables. Below is my sample code which does below process.
Get the ...
7
votes
2answers
85 views
1
vote
1answer
31 views
Getting the last post of all threads
My MySQL query is loading very slowly (over 30 secs) and I was wandering what tweaks I can make to optimize it.
The query should return the last post with the string "?" of all threads.
...
3
votes
1answer
54 views
Finding the Nth occurrence of character in string
I wrote an SQL Server function which returns the substring before the Nth occurrence of a character.
For example:
...
1
vote
1answer
44 views
Oracle trigger to keep a copy of deleted records in another table
I am using this trigger to keep a copy of the deleted record from table Orders_Details in table Orders_Details_Deleted.
I am ...
1
vote
0answers
60 views
Pagination from resultset for JTable
I am trying to create simple app, where I need a pagination for my JTable. I want have there buttons with number for all "pages" in my JTable. I have code here:
Class Customer:
...
3
votes
1answer
52 views
1
vote
1answer
55 views
Generate SQL for translated resources from Excel Spreadsheet
I do some work that involves translating resources for a webpage. These are all stored in a SQL Server table that looks like this (plus a few irrelevant constraints).
...
3
votes
1answer
88 views
Entity Framework query optimization
A user can track a show, and mark episodes and seasons of that show as watched. To support this I have the models below:
Models
...
3
votes
0answers
59 views
Query for a ticket system involving cases and casts on many attributes
I have this SELECT statement that inserts into a temp table and it is slow! Right now it takes ~1500 ms to run and only deals with ~7000 rows. As the data grows ...
6
votes
1answer
48 views
Who likes that post?
Following on from my last question, I've been continuing to look at what information is available from the SE Data Explorer, I thought it would be interesting to know who else had favorited a question....
7
votes
2answers
49 views
Retrieve high views per day questions with few answers
I've been messing around with the SE Data Explorer and I've come up with a query that basically returns information about questions that have a high number of views per day, that have few or no ...
-1
votes
2answers
109 views
Calculating price with a sub query
So I have this sub query:
*(select p.value1 From Params as p where p.PAR_Field = 'Taxe' and p.PAR_KEY = 'TVA')*
And I was using it 3 times to get the same value.
...
2
votes
1answer
28 views
Stored procedure to swap account holders
I'm doing an exercise, and would like to know if what I'm doing is correct and follows the correct structure for rollback in case a transaction fails. I would like to know as well if my ...
1
vote
0answers
19 views
auto_increment composite primary key with MySQL InnoDB
The intent is to have an auto_increment composite primary key on a InnoDB table which mimics the behavior as on a MyISAM table. The following assumes (3) InnoDB tables all with an auto_increment ...
3
votes
2answers
97 views
Checking a user's emails using an ID
I'm trying to do this:
Getting user's ID based on his email name.
Using that user_id to check all his emails that have been sent for him so far.
...
3
votes
1answer
52 views
Grabbing SQL records: Nullable bool to bool
So I have a method that does a lookup in an existing List<T>, populated elsewhere by grabbing the records from a SQL table holding configuration data for each ...
2
votes
1answer
81 views
MySQL statements to update overlapping database entries and insert new data into various tables
As I've mentioned in previous questions, I am writing MySQL statements to update overlapping database entries and insert new data into various tables. My database design looks something like this, ...
1
vote
0answers
27 views
Export database values as csv with a helper class
Should I be using getter and setter functions in my class nodeStruct?
I am currently using them where there is need to manipulate the input value (e.g for ...
3
votes
1answer
49 views
Database design for users' postcards
I would like to a review of my database design.
There are 3 tables + ASP.NET Identity tables.
The Users could have many postcards.
Here's my tables without Identity.
I attached also my scripts ...
1
vote
0answers
22 views
Database schema for factory application
I am creating an application to manage the assembly of shoes through a web application.
Could you tell me that you think that is more or you would be missing to be as solid and complete as possible, ...
2
votes
1answer
84 views
Simple login page
Could anyone tell me if this code is well written and if it has bugs / vulnerabilities?
class.user.php:
...
0
votes
1answer
37 views
Repair orders query in SQL Server with subselects
I was wondering if you guys know how to reduce the amount of subselects in this SQL Server Query? I think I'm using bad practices in this:
...
0
votes
0answers
31 views
Spark Code for doing SQL Operations on a/ multiple JSON files
Input is one or many json files to run a query and print the result.
I have tried both Collect and saveAsTextFile but both are slow and would appreciate suggestions to help speed things up.
Here is ...
6
votes
4answers
711 views
SharePoint CRUD class
I am writing an application where I have some objects like customer, supplier, product, etc.
I have written a class for the object 'supplier' and wanted to ask if this is a good design. I have put in ...
