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
0answers
26 views
is it possible to search a msql database for a word? [on hold]
If I have a database(blog) and a table(posts) can I search the column(content) for the word 'code' in ' I would enjoy being taught to code'? I tried to use whoosh but got lost
2
votes
1answer
29 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
...
-1
votes
1answer
35 views
How do you code communication between a windows app and storing data in SQL server? [closed]
For context I am fairly new to programming so keep that in mind. For one of my first decent sized projects I am trying to develop a small windows app that will have an interface that will allow me to ...
0
votes
1answer
50 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
89 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
72 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
87 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
0answers
34 views
Do I want an index if all I care to query is field not null [migrated]
In SQL, do all the common considerations about indexes apply if everything I'll ever want to query (in the where condition) is when a field is not null?
Is there a special configuration of the index ...
1
vote
2answers
46 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
40 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
59 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
44 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
52 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
85 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
121 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
492 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
131 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
99 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
85 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 ...
0
votes
1answer
62 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
120 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
65 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
97 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
332 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
520 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
77 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
39 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
64 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
128 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
63 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
62 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
511 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
346 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 ...
22
votes
2answers
2k 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 ...
3
votes
2answers
121 views
How to infer the result of a query?
To understand what a query does, I have always thought the following is the procedure on evaluating a query:
Form the table as specified in the FROM clause.
Pick the rows from that table as ...
0
votes
2answers
40 views
SQL - for some attributes specific to different clients' users, how to handle schema?
I have clients, each of whom have an app with a bunch of users.
Their user data could be pretty different, but there is also a lot of overlap. Ex: all their users have "gender" and "age" and plenty ...
1
vote
1answer
55 views
Object Storage where Object Type is variable
From time to time, we come across a problem where we must store an object, but we don't know in advance what kind of object we're going to store. I'm going to give a very simple example.
Requirement: ...
1
vote
1answer
84 views
Sanity check SQL [closed]
Is there any tools/processes to check SQL does not contain any possible issues.
For example we had an SQL ran that forgot to put a where clause on an update and mistakenly updated the entire table.
...
0
votes
0answers
57 views
How to deal with requests that don't conform to the existing data structure?
The system I'm working on has a list of assets that belong to different customers of the company. This data is saved on an SQL database.
Removing irrelevant data, let's say the table is like this:
...
2
votes
2answers
100 views
Is using triggers best solution for this scenario
A large SQL transactional database has more than 100 tables (and it will grow). One of them is called Order. Then, there is another table WorkLoad which derives from Order and many other joined table ...
6
votes
7answers
487 views
How would you design a user database with custom fields
This question is around how should I design a database, it can be relational / nosql databases, depending on what will be the better solution
Given a requirement where you'll need to create a ...
0
votes
1answer
112 views
Always set form input max length?
Background
I'm looking to put together a single page web application with many form inputs. I've run into issues with other web pages where there is an ugly SQL error sent to the user if the ...
0
votes
1answer
159 views
Injectable, Poppable Stack
Background
A project involves converting a relational expression map (rxm) to an XML/SQL statement. The map resembles:
root > people, # "root" keyword starts the document
...
5
votes
3answers
361 views
SQL - Algorithm for finding availability of a resource
I'm having trouble creating a mysql compatible algorithm for this.
Background
App with mysql, perl and JS. It's a booking system where each booking is comprised of a start, end and qty. Start and ...
1
vote
0answers
215 views
Preserving Pre-formatted Multi-Line Strings in Node.js Scripts
There is a lot I don't like about PHP, but one thing I love is multi-line strings:
$query = <<<EOT
select
field1
,field2
,field3
from tableName
where
field1 = 123
EOT;
...
1
vote
0answers
230 views
What is the “correct” way to store functions in a database?
Note: Yes, I know that storing functions in databases should be punishable by law.
We are developing a financial web application using PostgreSQL, Sinatra and AngularJS. As you may have guessed, a ...
3
votes
2answers
89 views
Integration tests of SQL logic
In my daily work, I use a lot of SQL logic (oracle stored procedures and SQL functions).
To limit the code regression on the implemented code I make what I call "integration scripts" : C# scripts that ...
