Structured Query Language (SQL) is a language for managing data in relational database management systems. This tag is for general SQL programming questions; it is not for Microsoft SQL Server (for this, use the sql-server tag), nor does it refer to specific dialects of SQL on its own.

learn more… | top users | synonyms

6
votes
2answers
92 views

Does it make sense to use a cursor for complex business logic?

I am dealing with a pretty extensive data set. After a few joins, the foundation table has something like 40 columns. The next 40 columns will all be calculated. I am trying to keep my Java clean of ...
1
vote
1answer
62 views

auto generated web CMS for pre-existing SQL db?

What I'm looking for is a way to auto-generate a simple web-based CMS for a simple pre-existing SQL database. To be used by 'app administrators', not the general public. Something that: allows ...
-1
votes
1answer
141 views

Registration form with email verification

Can someone point me in the right direction: I need to make a website with a registration form, to create accounts with e-mail verification, to write the forms content in a database table or something ...
-1
votes
2answers
90 views

Does sql use hastables for indexes? [closed]

Are there other ways of indexing?, Which are the more used? Does sql have an standard for indexes, it uses hash tables?
2
votes
4answers
99 views

Data Model - How to better model this circular reference

I'm designing the data model for a project where users can create documents, save revisions of that document that can be reverted to, and publish documents. The basic data model I have right now is ...
2
votes
1answer
208 views

How is one or more aggregate function implemented in most SQL engines?

In the book Database Fundamentals, Silberschatz. It is explained that aggregate functions can be calculated on the march. This make sense. What it means is that for calculating the maximun, average ...
0
votes
3answers
653 views

What is selection and what is projection

I am working on Android with the query() method of the ContentResolver class and it accepts 6 parameters, (uri,projection,selection,selectionArgs,sortOrder,cancellationSignal) for selection and ...
1
vote
1answer
90 views

Django Migrations: Binds data to code?

If you have custom code for models (custom validators and custom fields are examples), Django Migrations will import them directly into the migration files. An example: file web/models.py def ...
1
vote
1answer
110 views

Getting data from a database with the MVC model

I am creating an application which will have a very large database to draw it's information from (a student registration system to be exact). The database is created with Microsoft SQL Server if that ...
0
votes
1answer
124 views

Designing Pricing table in MYSQL

I'm designing a pricelist table for my database. It will include Customer, Model, Start_date, End_date, Price, Currency, RRP When I update a new pricelist which is sent every now and then (maybe ...
3
votes
3answers
115 views

How to test data based on SQL queries?

I'm trying to set up a test framework for (SQL) datasets that get updated daily. I want to write assertions that involve comparisons between two tables–for example, the sum of column visitors in the ...
7
votes
4answers
209 views

Encapsulation for complex queries

I tried asking this question first on StakOverflow in a more concrete manner, but after being pointed here I realized I should rephrase it in more general terms; however, you can still review the ...
9
votes
4answers
1k views

Should database queries be abstracted out of the page itself?

When writing page generation in PHP, I often find myself writing a set of files littered with database queries. For example, I might have a query to fetch some data about a post directly from the ...
-1
votes
3answers
183 views

What to do with database foreign keys in an entity class?

I know that an attribute defines the state of an object. So, is it correct to keep attributes that don't define the state of an object of a class? For example, I have an Employee class, which has ...
42
votes
8answers
6k views

Is the use of NoSQL Databases impractical for large datasets where you need to search by content?

I've been learning about NoSQL Databases for a week now. I really understand the advantages of NoSQL Databases and the many use cases they are great for. But often people write their articles as if ...
2
votes
0answers
44 views

ODBC 3 Multiple Statements vs Multiple Connections

So right now I have a single thread to handle all the requests for the database. Let's say I have 400 requests per second for logins / logouts / other stuff, and 400 requests per second which are only ...
3
votes
7answers
7k views

What is faster? Using REST API or querying a database directly?

What is faster performance wise? Creating a REST API and having your web app use the REST API to do all interactions with your database OR querying your database directly (i.e. using whatever typical ...
0
votes
1answer
66 views

Why storing a payroll in XML type field would be good or bad

We have a PAYROLL issues by the client to make it dynamic i.e. to make the whole process computerized. It includes: Employees Allowances (Scale wise, which could change yearly or monthly or ...
0
votes
3answers
112 views

Getting RowCount without wasting resources

I have a large SQL query that relates 6-7 different tables and returns fourteen different rows as distinct sets, in a union with an equally-large query, that at times can return over a thousand ...
1
vote
0answers
69 views

SQL Query and Java Constant Abuse?

I am currently charged with taking over a lot of code that is written with fields names placed into Java constant at the top of the file, and then the SQL queries constructed using string ...
2
votes
3answers
212 views

Tracking time users spend on my website

We are going to track user engagement (i.e. time spent on website, most viewed part/page of the system, etc). I don't see Google Analytics / MixPanel being able to do this, since we have to analyze ...
2
votes
2answers
261 views

SQL query or C# .net code for csv files import?

My aim here is to find out the best possible and feasible solution for my dilemma. I want to import some csv file (may contain around 50~60K records) into database after some manipulation into the ...
1
vote
0answers
43 views

More scalable alternative to a transaction-per.request in a SQL+REST stack?

I'm new in web programming with SQL database. So please forgive my ignorance. I'm using some modern framework to make a REST server with a SQL database. I'm using transaction only when I think I need ...
0
votes
0answers
15 views

Conceptually, how should a unidirectional multivalued relationship be implemented?

Ok, I'm trying not to upset the programmers list, but not so successful so far. My apologies. I'm trying to understand, conceptually and based on experience, which is the correct way for a JPA ...
4
votes
2answers
101 views

Why are triggers seen as a last resort to solve multiple paths in cascading deletes?

There is a problem using ON DELETE CASCADE on foreign keys in a SQL database if there are multiple paths from the root foreign key to the leaf. The way around this seems to be to replace the ON DELETE ...
2
votes
2answers
127 views

Why use JSON dataset rather than SQL Query?

A colleague was asking me to explain a system information flow, as they are having problems with the wrong information being presented. It seems that what they have created is a custom built website, ...
1
vote
1answer
1k views

Notify client about changes in database in Java

I'm working on a distributed client-server system that uses TCP. The program's functions is to transfer money to an from the server, but also between the clients, a sort of MobilePay. When one ...
0
votes
1answer
122 views

Relationship between REST APIs and Databases [duplicate]

I've been studying databases and rest APIs lately and I have a question about the relationship between the two. Imagine I have a database with three tables, STUDENTS, ENROLLED, and CLASSES. STUDENTS ...
0
votes
0answers
67 views

Best practices for dashboard of near real-time analytics

I’m currently building a dashboard to view some analytics about the data generated by my company's product. We use MySQL as our database. The SQL queries to generate the analytics from the raw live ...
2
votes
4answers
96 views

Keeping a ratio column that's the division between two other columns

I'm about to build a SQL table where I want to store currency orders. That means that I need to store how much I paid for a certain quantity, and the ratio between both quantities. So for example: ...
9
votes
7answers
4k views

What are the advantages to using SQL query builders?

Are there any advantages to using a query builder, rather than using raw SQL? E.g. $q->select('*') ->from('posts') ->innerJoin('terms', 'post_id') ->where(...) vs: SELECT * FROM ...
0
votes
0answers
17 views

SQL data model for nested milestones/timframes

I'm currently weighing different approaches to a data model and I'm curious what others would do. The main nugget of info is ACTIVITIES, which belong to a chain of milestone time releases. Stages ...
0
votes
0answers
76 views

Options for implementing database and programming logic for statistics application?

I am implementing a statistics driven web application. The statistics relate to sporting events. Right now I have a postgresql database with a table that holds a row for each match. This is a ...
-1
votes
1answer
50 views

Querying Results from End of the table

Normal behavior of the SQL Table is to add new rows in end of the table. I want to query results of single row only. So mysql command is SELECT id FROM users WHERE country='india' LIMIT 1 this ...
0
votes
0answers
14 views

When would I choose a Lucene/MSFT Search of SQL to store Spatial data?

I need to store the locations of stores and allow customers to query nearby businesses, with a varying amount of filters. The filters in the Search world seem to be named "facets" What reasoning ...
4
votes
0answers
70 views

Using mysql 5.7 Json Columns for EAV

I am developing an e-commerce product and I have been able to implement all functionality and am left with allowing users to create additional attributes for a product. Right Now I have two options. ...
0
votes
0answers
102 views

Advice on program performance after changes

So, the situation is like this: For the past two years, I have implemented a class in c# which is used to filter different fields from different tables in database (SQL), for reporting purposes. Now ...
0
votes
0answers
44 views

Use vb.net sql reader as a Delegate to gather Data

Is there a way to use a sub to create a loop, but change the content of the inside of the loop? I don't know what I don't know. I keep looking at this code and I have a feeling that it's possible. ...
4
votes
2answers
1k views

How do you unit test \ use TDD methods for ETL's and reporting projects?

ETL projects are projects created using an ETL (Extract - Transform - Load) tool such as SSIS, PowerCenter,etc These typically involve reading data from an external source, loading it to a staging ...
6
votes
5answers
8k views

Why SQL numeric precision is specified as the number of digits?

I understand that this might be a loaded question. For example, in Sql Server 2008, doing DECLARE @someNum NUMERIC(10); means that the range of the variable will be limited by 10 digits. In other ...
9
votes
5answers
7k views

Foreign key restrictions -> yes or no? [closed]

I would like to hear some "real life experience" suggestions if foreign key restrictions are good or bad thing to enforce in DB. I would kindly ask students/beginners to refrain from jumping and ...
8
votes
2answers
3k views

In what order are rows fetched absent ORDER BY clause?

One programmer is testing and comparing the same application which uses the same database structure, and the same data, only in two separate databases, one with Oracle 8 and one with Oracle 9. The ...
26
votes
6answers
54k views

SQL Triggers and when or when not to use them.

When I was originally learning about SQL I was always told, only use triggers if you really need to and opt to use stored procedures instead if possible. Now unfortunately at the time (a good few ...
4
votes
2answers
90 views

How to represent a collection with capacity in a database?

Concerning normalization of a one to many relationship, I am not sure how to represent a collection with a specified capacity in a database. I can think of two ways to represent an entity A with a ...
3
votes
3answers
108 views

Relational table design for data ownership

Setup (schema simplified for clarity) Suppose I have a table called resource: +---------------+--------------+------+-----+ | Field | Type | Null | Key | ...
2
votes
1answer
185 views

Serialized values or separate table, which is more efficient?

I have a Rails model email_condition_string with a word column in it. Now I have another model called request_creation_email_config with the following columns admin_filter_group:references ...
1
vote
1answer
82 views

How to handle multiple “valid after {date}” in a performant way

We work on an Application which displays a duty roster and also some other DateTime related things. So my Database is full of Relations like in this small Example: This results in big ...
12
votes
2answers
4k views

Is inline SQL still classed as bad practice now that we have Micro ORMs?

This is a bit of an open ended question but I wanted some opinions, as I grew up in a world where inline SQL scripts were the norm, then we were all made very aware of SQL injection based issues, and ...
10
votes
3answers
2k views

Storing n-gram data

I was hoping to brainstorm a little bit on the subject of storing n-gram data. In my project, I am trying to solve linguistic problems where I know all (n-1) data items and want to statistically guess ...
40
votes
11answers
3k views

Why don't relational databases support returning information in a nested format?

Suppose I'm building a blog that I want to have posts and comments. So I create two tables, a 'posts' table with an autoincrementing integer 'id' column, and a 'comments' table that has a foreign key ...