Questions tagged [sql]

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.

Filter by
Sorted by
Tagged with
3
votes
0answers
53 views

OOP Python Blackjack game with player accounts in PostgreSQL

This is an OOP version of Lastest version of my Blackjack game. Also, it now uses PostgreSQL as database. ...
0
votes
1answer
130 views

Displaying the value of a stock portfolio

Some people have mentioned to me that my site is quite inefficient and given that it's taking ~17secs to load now, I'd like to change that but I've no idea how. I've tried Gzip to compress it but it ...
8
votes
0answers
150 views

Safe dynamic SQL for generic search

Prompted by discussion about SQL injection, I wanted to put a proof of concept forward to get feedback about whether this is in fact safe and protected against SQL injection or other malicious use. ...
1
vote
0answers
23 views

Stored procs fine tuning for processing bulk data - 5 million records

I have this procedure which is used for bulk upload data from a temporary table (temp_rules_master) into two other related tables. It has 5 million records. The ...
1
vote
1answer
22 views

MySQL GROUP BY id, then show name

I have a users table where some users have the same name: ...
2
votes
1answer
63 views

Join and pivot multiple tables of securities data in MySQL

I'm interested in a way to join three (or more) tables in a way that is more efficient (both in terms of conciseness of code and in terms of generating the results) than what I have currently. ...
3
votes
1answer
47 views

SQL queries in a dataframe

I want to get table names and column names from queries in a dataframe. The dataframe is like this: ...
-2
votes
0answers
23 views

Query is running slow even when the tables are indexed [closed]

The below query takes roughly 2 minutes for almost 2000+ records. The tables are indexed. what might be causing the issue? ...
3
votes
1answer
49 views

Calculating all flight connections

I am working on an API to return all the possible flight route between point A and B. I have a set of data which includes the flight number, airline, departure airport, destination airport, ...
1
vote
1answer
30 views

Updating field in Temporary table

I have a more massive sp, in which I use some temporary tables, below is a part of the sp where I am updating a field in a temporary table two times. ...
4
votes
2answers
111 views
1
vote
1answer
53 views

T-SQL query Choose name of clients, concluded more than one loan agreement in 2010

QUESTION: Choose the name of clients, concluded more than one loan agreement in 2010, as the numbers and dates of issue of these contracts. Sort by name of the client and date of issue of the loan. ...
0
votes
0answers
16 views

Dynamic Statement Evaluator

I've written a stored procedure in order to simplify the process of calling dynamically constructed MySQL statements: Implementation ...
3
votes
1answer
2k views

Displaying comments and replies retrieved from a database

I created an example (for me) of how I must get the comments and replies from the database. Here is the result for example: ...
1
vote
1answer
88 views

SQL Stored Procedure to export specified data

I'm currently using a stored procedure(sproc) to export to Excel. The sproc is being passed ...
3
votes
1answer
125 views

SQL SP creating certificates for shipping

We ship large spools of copper wire and pallets of copper rod that have to meet specific chemical and mechanical requirements, this is what must be put on the certifications. I created an SP which ...
0
votes
1answer
16 views

Querying nearest city with a given shop using latitude and longitude is slow

I am using the following query to search for the nearest city to my location from the cities table using latitude and longitude. The query also checks whether the nearest cities to my location have ...
3
votes
1answer
95 views

Small application for manipulating users and their activities

I made a small application using C#, SQL Database, entity framework , and windows forms to manipulate users (...
3
votes
1answer
23 views

Billing and recurrent payment db structure

I am designing my first payment service and here is my db structure. Each user can have many businesses, each business can have many products. Some are recurring, some are paid once. Each product is ...
0
votes
1answer
91 views

Modify a column in 2 tables based on trigger

This is SQL Server 2008r2. First, here are some tables and data for test: ...
3
votes
2answers
63 views

Shopping cart with Ajax

I build a cart for an ecommerce website, and I want to know if I did well. I want to record the changes in the DB and display the information from the DB to the customer. To be sure that what he sees ...
2
votes
1answer
46 views

Abstracting a SQL Server Table builder

I'm working with SQL Server 2008 and wanted to make table builder, similar to Laravel's migration classes. I would like to be able to expand this down the road but the main purpose of this code will ...
1
vote
2answers
57 views

Combining two queries in one

What I want is getting all names (first, middle, last) of the users along with each of their total hours of work which will be calculated by the SQL statement: ...
1
vote
0answers
16 views

Select splat in a subquery [closed]

Obviously, 'select *' is never a good idea. HOWEVER, I have taken a job with an org that allowed this cancer to spread. They have huge queries, using ...
10
votes
4answers
2k views

Materializing any ADODB Query

Following-up on Creating ADODB Parameters on the fly and pushing the "wrapping" of ADODB a step further, I have written two more classes that allows me to expose methods that don't require a ...
7
votes
3answers
19k views

SQL query where id=… or id=… or id=… etc [closed]

I haven't had much experience with postgresql (none) and I am wondering/hoping that there is a better way for me to do this query. ...
1
vote
0answers
13 views

Custom WordPress SQL query to get posts and users using term_id

I have a WordPress site with multiple custom post types, that all share a common custom taxonomy. Users can also be tagged with terms from this taxonomy. After giving up on ...
2
votes
1answer
61 views

Generating and storing a unique salt in Oracle database using Java

This is my account table: ...
4
votes
3answers
3k 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 ...
1
vote
1answer
54 views

Displaying a paginated list of forum topics using PHP PDO [closed]

Can someone tell me if this code is safe? Can be sql-injected or something else hacked? Code get some rows from db and show in pages with pagination... if i can improve let me know and show me how, ...
0
votes
0answers
35 views

Parties and Messages in SQL e-Commerce db design

I'm posting a follow-up question based on my first question. My goal is to, as an exercise for myself, create a complex (in my opinion) e-commerce application. The explanation of the application can ...
4
votes
1answer
83 views

SQL e-commerce database design

I want as an exercise to create an e-commerce application from bottom up. My main goal here is to gain a lot of knowledge. I have experience in web development, but never have been there from the ...
2
votes
0answers
20 views

T-SQL Query remove duplicates except for original row from the table

Question: There is an Exam_Merge table into which to import records from a similarly structured table in another database. In this case, the Exam_Merge table contains the values of the primary key ID ...
4
votes
1answer
178 views

SQL query to count loans issued during each week

Calculate the number of loans issued by the Regional offices for each business week in the period from 38 to 40 business week of 2011 inclusive. Sort by name of the regional unit, business week. ...
4
votes
1answer
35 views

JSON from SQL Server: Reduce lists of single dicts to list of values

SQL Server 2016 introduced some JSON features, but they're far from robust. As far as I can tell, there's no way yet to output a simple array; everything must be in ...
3
votes
1answer
7k views

Basic object-oriented PHP login, logout, and registration scripts

I am new to OOP in general and have recently started trying to develop object-oriented PHP code instead of relying on a primarily procedural style, as this is a requirement for my studies. One part of ...
1
vote
1answer
204 views
3
votes
2answers
102 views

SQL Query - records within the last year

Bit of a complicated one for me. I have a database full of hundreds of thousands of records, many of which are duplicated. I need to get all records within the last year but making sure every ...
0
votes
1answer
45 views

Validating region/state/province codes for different countries

I have a large table in which I store region/state/province codes (two letters) from different countries. I use these region codes further downstream for multiple process. One of the steps I do is a ...
1
vote
1answer
62 views

Oracle SQL Code - Multiple Joins used

I'm currently working on some SQL code within Oracle Fusion BIP but it's not very optimised as it's using multiple LEFT,RIGHT and FULL OUTER joins to link tables together. Unfortunately this is ...
1
vote
2answers
44 views

Query for customer quotes by date range

I have a table (Oracle): customer_quote ------ int id int part_key (fk) int condition_code (fk) number unit_price int qty_quoted date entry_date With a query ...
1
vote
1answer
48 views

Query to generate payroll based on attendance for a month

I have written a query to generate payroll based on attendance for a month, for about 4000 employees. It takes quite a long time to execute. Basically, what I am doing is to joining two different ...
4
votes
1answer
85 views

PHP Login Class - Security and Efficiency Check

I'm hoping someone would be able to identify if my code is prone to SQL injections, and just overall see if there is anything that could be done better, faster or more efficiently Im very new to OOP ...
4
votes
0answers
41k views

Building 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 ...
11
votes
1answer
492 views

Twitter clone in Golang

I have built a Twitter clone in Golang, using object orientation principles. I wish to know the design mistakes I have made. ...
2
votes
1answer
80 views

New comments after a user's last comment

Edit: I've added everything I can... I have the following query, which gets the last comment from the user in a given topic, then I check in the while loop if there are newer comments. My problem is,...
1
vote
0answers
23 views

Log table with a summary of claims

I'm writing a log table which contains the summary of the claims. The log table logs the data whenever there is an upload. I have written the following SQL query. Though it serves the purpose, I'm ...
2
votes
1answer
40 views

Extract Students with All Enrolments in the Past

I am trying to extract the students where all of their enrolments are in the Past. Some students can have more than one Enrolment and one of its End Date is in the future while the other one is in ...
4
votes
1answer
32 views

Calculating elapsed time

The code below compares recv.rcpt_dtim (which is a datetime type) against the current date/time. It calculates an elapsed time resulting in hours and minutes formatted like: "04:22". It took me a ...
2
votes
2answers
77 views

Replacing birth year column values with calculated age value

I have a stored procedure that is taking more than 2 hours to complete and I noticed the below SQL is taking close to 25 mins for updating a million+ values. There are multiple steps like this and ...