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.
-4
votes
1answer
29 views
SQL Query Match Two Tables with Comma Separated Value [on hold]
How do I query from this situation. I want to find a match from skills of a job posting to a candidate skill.
Candidate Table
------------------------------------------------------
CandidateID Name ...
0
votes
1answer
29 views
How to do approach doing transactional CRUD operations on Oracle Database while using JavaScript frameworks like Meteor.js?
I work for an enterprise which is believes in using Oracle Database - Hibernate - Spring - JSP stack only. I wish to build a real time mobile & web app using Meteor.js / Node.js + Socks.io, to ...
4
votes
2answers
85 views
Database constraints for a recursive folders structure
In an application, I have a recursive folder structure (like folders in OS X or Windows file system).
Each folder can contain three kind of things:
Other folders (hence the recursive structure)
...
-2
votes
1answer
30 views
Using foreign key as a way to decrease returned values [closed]
If I have a table fruit, with a column for the fruits name and set the primary key as an int (eg index_fruit : 1 = apple, 2 = pear)
If I have another table fruit prices but instead of populating it ...
-4
votes
0answers
37 views
SQL bugs and identification of mismatchers [closed]
Can we have subqueries of selects within selects?
2
votes
3answers
55 views
automatic database fill up in sql script tests
The problem
I am working on an application with ~ 1000 tables in a sql server database. I am having a recurring issue with a sql script.
The script is used by the consulting team to "clean" some ...
-2
votes
0answers
29 views
SQL Problems: Multiple OR in WHERE Clause [migrated]
I have a variable string named "sql" that holds a SQL query - see the below SQL. It has multiple ORs in the WHERE clause.
When I try to open it in a recordset there's an error (see below the code).
...
3
votes
1answer
138 views
How do SQL transactions return immediate results?
I am using transactions to do bulk insert/updates. This is my little test loop:
$now = date('Y-m-d H:i:s');
for ($i=0; $i<60; $i++) {
$db->insert($cfg['ps_manufacturer'], array(
...
0
votes
0answers
111 views
What are good ways to store decorated objects in a relational database without adding a field for every possible property? [migrated]
Suppose I have an object, I use a decorator to add properties to this object, and then I want to store the object and its properties in a relational database, and be able to run queries on these ...
1
vote
0answers
86 views
How Should I Design Models With Deep Relationship Structures?
I am designing a web application in ASP.NET MVC, which uses ADO.NET for the database access.
In the past, I generally have put together a series of classes each of which, maps to one table in a ...
0
votes
3answers
302 views
Why does databases use text? [closed]
This might be a little dumb question but why do we save text in the db instead of something smaller?
Couldn't there be some other way to store data in the db like in a compressed form, and then have ...
0
votes
1answer
89 views
What is the problem will happen if I create auto generate ID without using mysql auto increase of mysql table by my self?
I would like to ask you a question about MySQL AUTO_INCREMENT.
I already created my own function to generate AUTO_INCREMENT when the user inserts any data input a table in MySQL. This function works ...
6
votes
1answer
152 views
Clean Architecture - How to go from “Database Driven” to “Independent of Database” [closed]
I am looking for some clarity and hopefully some advice on writing clean architecture for a large system. My Companies "Web Solution" is +-10 years old, my job is to rewrite it. It is written across a ...
1
vote
1answer
170 views
Why do I need to use recursion on the classic employee manager database relationship?
Take the data from the Oracle scott database. I have modified it to have multiple levels of management from the original,
select * from scott.emp order by mgr desc;
empno ename job ...
-1
votes
1answer
65 views
behaviour of SQL BETWEEN operator [closed]
This is kind of philosophical question...
Today i spent way too much time to debug a method which was generating a SQL command.
I figured out the error was because the code was generating something ...
21
votes
4answers
3k views
Why not just make non-parameterized queries return an error?
SQL injection is a very serious security issue, in large part because it's so easy to get it wrong: the obvious, intuitive way to build a query incorporating user input leaves you vulnerable, and the ...
2
votes
1answer
47 views
Time attendance module design
I am working on time attendance module and I managed to read the data from the timing machine in the following format:
Id CheckIn Type Status
0000142 5/15/2015 6:00 PM 2 OK
...
0
votes
1answer
57 views
Optimizing instant notifications
Scenario
To deliver instant notifications to the client, I'm sending an AJAX request to a PHP page that checks if there are new notifications: if there are, it outputs them, otherwise it sleeps for ...
1
vote
1answer
99 views
Web framework in pure SQL
I heard that SQL is Turing complete language (for example: http://stackoverflow.com/a/7580013/2604170) I am just curious if would be possible to create independent web framework like Ruby on rails or ...
1
vote
0answers
112 views
Why does convention say DB table names should be singular but RESTful resources plural?
It's a pretty established convention that database table names, in SQL at least, should be singular. SELECT * FROM user; See this question and discussion.
It's also a pretty established convention ...
2
votes
1answer
140 views
What is the best way to store a formula in a database for a table driven formula? [closed]
So I am changing my code over to a table driven formula system to make it sustainable in the long term.
So basically I have 5 foreign keys that will get me to a single formula to use. However I'm ...
1
vote
2answers
61 views
Partial Update of Database Table
I have a database table with numerous fields including "category". The source of this table is an xml file which the system receiving occasionally. Each xml file contains data for one category. The ...
1
vote
0answers
42 views
Storing/caching SQL query
If I have a very long-winded SQL query (that has multiple subqueries), but the result of which is unlikely to change from hour to hour (or even day-to-day, or possibly even week-to-week) is there some ...
2
votes
1answer
70 views
Best practices for scope of returned data per stored procedure
I have the following situation:
table1
------------
ID Name Param1 Param2 Param3 RegistrationID
1 Test Value1 Value2 Value3 101
table2
------------
RegistrationID LangID Value
101 ...
1
vote
1answer
58 views
Is it a good practice to write distinct queries in a single stored procedure based on input parameters
I am going through the stored procedures written throughout our project and see that multiple distinct queries are written inside a single stored procedure and are called by passing different values ...
-5
votes
1answer
67 views
confused with ms sql nomenclature [closed]
Question 1. What is the language used in Microsoft SQL Server?
Question 2. I get confused with the name Microsoft SQL Server since its a RDBMS why its name ends as "Server". Because server is used to ...
1
vote
1answer
101 views
What's the quickest and most maintainable way to implement SQL querying of disparate C++ variables?
I've recently joined a team working on radar observations. The team has access to a repository of software (written in C++) used for dealing with these observations, but their work is focused towards ...
2
votes
3answers
138 views
At which point using string-based enums can become a real problem?
This is a direct follower to this question. An answer given to that questions says (my understanding), that nowadays using string-based enums instead of integers isn't a much problem, because hosting ...
1
vote
0answers
32 views
Adding fields a good use case for EAV? [duplicate]
Is being able to add fields quickly to a system a good use case for EAV? What would be the frequency or number that would justify using an EAV model?
2
votes
6answers
1k 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 ...
4
votes
2answers
148 views
Are (basic) SQL queries semantically equivalent to Higher Order Functions?
Is SQL basically a domain specific instance of map + fold + filter?
It seems to me that the following SQL:
SELECT name
FROM fruits
WHERE calories < 100
is just syntactic sugar for the ...
2
votes
0answers
211 views
The best way to convert Dynamic SQL to C#/Linq while joining multiple tables
I am trying to find the best way to convert the following dynamic SQL snippet to C# / Linq. The question is complicated by the use of multiple tables within the dynamic SQL.
I've examined the ...
4
votes
1answer
88 views
Status Bar Timetracking Design
I'm currently developing an application in ASP.NET using SQL as the backend database. In my header, I have a status bar that displays the current status of the user and a timer that shows how long ...
1
vote
1answer
78 views
How to handle rating? [closed]
I want to implement like/dislike system, but I don't know how to achieve best performance. I have to tables: blog posts and comments that need rating.
Should I store all data in one rating table? Or ...
0
votes
0answers
16 views
One Enum vs Multiple Enums [duplicate]
I am developing an application where a user submits a mission and other users accept the mission.
Pretty simple.
I want to keep a track of the mission progress status and store it into a database.
...
2
votes
3answers
147 views
Database view performance - good practice?
Often times at work we opt to create views in the database to expose the data that we want to work with instead of building some monster query in our code. Being somewhat new to this field my ...
2
votes
1answer
73 views
Storing values in SQL whose types are determined in runtime
I have come across a need of storing runtime determined values in a SQL database.
For example, there is a GUI where a user can add new editable fields. So the user adds a field Name, chooses the ...
1
vote
1answer
101 views
When the result set doesn't matter should Left Join or Inner Join be used
When you have a query where you're joining from a non-null foreign key to the foreign table it doesn't matter if you use LEFT JOIN or INNER JOIN from a result standpoint (since there won't be any ...
6
votes
3answers
338 views
Do common relational database systems work 'out of the box'? [closed]
I'm quite new to software development and whenever I come across small projects which involve storing (relational) data I always ask myself if something like a micro-blog (or any other project with ...
11
votes
2answers
689 views
Why store flags/enums in a database as strings instead of integers?
I've been browsing SQL dumps of some famous CMSes, including Drupal 7, Wordpress (some quite very old version), and some custom application based on Python.
All of these dumps contained data with ...
1
vote
1answer
88 views
Knowing when a SQL table is done updating before Querying
I am using a self hosted windows service WEB API as the layer between my Windows 8 App and a SQL Server Database because Windows 8 apps can't directly connect to SQL server like win forms or pretty ...
1
vote
2answers
40 views
Using a masterID in sql to merge different item types
I am designing tables in sql for a small ticketing system. One of the demands is that a user/agent can post a ticket to either a department or an agent.
Now if I have a child parent table where each ...
0
votes
1answer
65 views
Do Temporary tables have any form of index in SQL?
Consider a simple query like this:
SELECT * FROM DATA
JOIN
(
SELECT * FROM DATA
)TEMPORARY_DATA
ON TEMPORARY_DATA.DATA_IDN = DATA.DATA_IDN
What is the performance of this? Is it O(n2) because ...
1
vote
2answers
138 views
Is there a difference between SQL INSERT patterns
SQL inserts are usually documented like so:
INSERT TableName (Column1, Column2) VALUES ('Value1', 'Value2')
However, in TSQL it's also possible to write the (seemingly) same statement like so:
...
1
vote
1answer
71 views
SQL - Designing a Threshold value table
I need to design an SQL table to be used for "threshold" or special values, which in general would have the following properties:
- key
- value
- comparer (less than and greater than as a start, ...
0
votes
1answer
77 views
Is there any reason not to allow negative numbers to be entered into a JSP field labeled for positive numbers?
Our application, a fairly complicated money ledger, has a JSP field for entering a certain type of money due, balanced by payments made towards that amount due. In the application, this field is ...
4
votes
2answers
778 views
Where to put a common database connection for my classes
I have several classes (Repositories) which do the task of saving/retrieving some objects in/from database; all of them need to establish a connection to one database.
I thought in order to avoid ...
1
vote
0answers
57 views
Where should business logic involving SQL go? [duplicate]
I have this question that's been bugging me all day. I have a function like this:
public void voidUsersByUserId(List<Integer> userIds) {
Query query = ...
3
votes
2answers
580 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 ...
23
votes
2answers
3k views
NoSQL within SQL Server
This question is not about the difference between SQL and NoSQL. I am looking for some rationale for something that really does not make sense to me at the moment (maybe because of my lack of ...
