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

2
votes
1answer
34 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
25 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
31 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: ...
-4
votes
0answers
23 views

visual basic: get php Query from text box [closed]

How can I get url Query from text box and put it line by line in richtextbox. exemple: I want to get query from textbox1.text : and put it in richtextbox or multilines textbox line by line : ...
1
vote
0answers
50 views

SQL query to count most “popular” value? [migrated]

These are my tables: Table: People id---name 1----John 2----Mike 3----George Table: Visits id---name---visitor 1----Paris--1 2----Paris--1 3----Paris--1 4----London-1 5----London-1 6----Tokyo--1 ...
1
vote
1answer
58 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
49 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: ...
6
votes
7answers
258 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 ...
-1
votes
0answers
9 views

Timestamp and Pagination on same time, for million of records best practices IOS

I have million of records, so i need both timestamp approach as well as pagination. Certain flows that are being used are: Pull to refresh Load more. The best scenerios in my mind are: When i am ...
-1
votes
0answers
26 views

Hotel Booking System in ASP,NET [migrated]

This is the schema I have for my hotel booking system. Customer Table (CustomerID, title, Firstname, Surname, address, town, county,postcode,country, tel, email, password, confirmpassword) Room ...
1
vote
1answer
129 views

Implementing “Trending Items” using linear regression in SQL Server

I'm building an MVC .NET website where people can rate and review movies (similar to IMDb). I'm using SQL Server for the back-end. I'll be implementing a feature where the homepage displays trending ...
1
vote
1answer
38 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 ...
0
votes
1answer
51 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 ...
-1
votes
0answers
33 views

SQL combined SELECT statement (for SQL zoo) [migrated]

I was using SQL zoo for brushing up my SQL knowledge and found the following problem: "Some countries have populations more than three times that of any of their neighbours (in the same ...
0
votes
1answer
158 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 ...
0
votes
0answers
46 views

SQL: GROUP BY where a column is unique [migrated]

So I have this one giant table e.g. PROD_IDGEOG_IDTIME_IDVALUE1 1      MT     JAN    100    ...
1
vote
0answers
12 views

How to use an Address Gazatteer in an application that contains addresses

Say I had access to the post office address file (http://en.wikipedia.org/wiki/Postcode_Address_File) and wanted to cleanse about one million addresses. I am trying to think of an "algorithm" to do ...
1
vote
1answer
345 views

Creating a online database creator

I've been thinking of creating an online database creator (think DabbleDB, Zoho Creator, Intuit Quickbase, etc.). I've been thinking of a couple solutions: Have 4 database tables: Tables, Rows, ...
5
votes
3answers
229 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
51 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
2answers
804 views

Database Context and Singleton injection with IoC

All of the below relates to a ASP.NET c# app. I have a Singleton Settings MemoryCache that reads values from database on first access and caches these, then invalidates them using SQL Service Broker ...
3
votes
2answers
82 views

Use of NOLOCK on Microsoft SQL Server

The (NOLOCK) optimizer hint is well known by most SQL developers for setting the isolation level so that tables with rows or escalated page locks can be read. I've always known about the bad and ugly; ...
3
votes
2answers
65 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 ...
1
vote
0answers
143 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
1answer
211 views

Is a series of OR or a single IN SQL statement better?

I'm using MS SQL 2008 R2. I have been using a series of OR and have an issue where my app periodically give the following error: Exception message: Timeout expired. The timeout period elapsed prior ...
1
vote
3answers
286 views

Best OOP pattern to use for creating a commandline SQL API for a proprietary json based company datastore

We need to create a SQL commandline tool for querying a proprietary json based datastore. I'll be using java. Any suggestions on the primary OOP design pattern I should use for building this API? ...
1
vote
2answers
158 views

How should I compare two database tables - with SQL or using Java? [closed]

I have two tables with different structure. I should compare ID from 1st table with ID of the intermediate table and then comapre TXT field of the intermediate table with TXT field of the 2nd table. ...
2
votes
1answer
197 views

Should I use foreign keys in my database if I use laravel?

I'm creating a website with Laravel for the first time. I checked relationships documentation today and it seems that Laravel just uses simple SQL queries. class User extends Eloquent { public ...
3
votes
2answers
189 views

Software development - The industry & general trends / Bad practices [duplicate]

I am a Web Developer and part of a small team working on an abundance of projects. This is my first "actual" real company after graduating with a degree in computer Science and I have about 2 years ...
33
votes
10answers
3k views

Is there any material difference between queries joined by WHERE clauses, and queries using an actual JOIN?

In Learn SQL the Hard Way (exercise six), the author presents the following query: SELECT pet.id, pet.name, pet.age, pet.dead FROM pet, person_pet, person WHERE pet.id = person_pet.pet_id ...
0
votes
1answer
32 views

Merging user activities into one stream with a common representation

My social network website currently has forum threads and posts gallery pictures albums on the users' profiles group discussions classifieds I would like to create one "stream" of activity. It ...
1
vote
2answers
779 views

SQL injection attacks, how do I test and secure coldfusion queries?

I'm running Coldfusion 8 and SQL server 2008. I've been building serveral forms that insert data into the database from external users, we have a custom built security module built by the guy who ...
3
votes
2answers
1k views

One of my team members committed SQL injection-vulnerable code; should I report it to the manager? [closed]

One of my team members committed a huge mistake; a clear SQL injection-vulnerability. It obviously didn't pass my peer review and I made very clear that this is unacceptable. I never saw this ...
0
votes
3answers
262 views

Using SQL queries in loops

Is it a bad idea? I need to compare each entry.Key value in a Dictionary of strings to a SQL table. If there is a match, then I pull the data from that row. I was originally going to use a foreach ...
17
votes
6answers
35k 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 ...
-1
votes
2answers
119 views

Storing Dates & Times in SQL [closed]

I have a tool that I am working on which allows people to create meetings. They can select the Date, Time & Timezone that this meeting is occurring in. I need to determine the best way to store ...
3
votes
1answer
178 views

What is the best way to format complex SQL queries in Node?

I am using node-mysql to query a MySQL database via Express and Node. My queries are getting fairly complex and I want to format them across multiple lines. However, the only way I could accomplish ...
0
votes
2answers
577 views

Clients connect to WCF or SQL Server

I was wondering what is the prefered way of accessing data from a server in a client application. The data will be stored on the server in an SQL database and I have previously used the following 2 ...
3
votes
3answers
373 views

What kind of user info is ok to be stored as plain text in SQL Database?

I'm practicing around building e-commerce asp.net applications that allows for users to register to the site and their user credentials are stored in a MySQL database. In my sample project the ...
3
votes
1answer
761 views

How to translate SQL query into REST API requests?

Let's say I have a machine-readable description (such as in WADL, Swagger or RAML) of a REST API that provides interface to a database. My users submit queries about underlying database in form of ...
3
votes
3answers
371 views

ASP.NET MVC should I reference the DAL from the UI?

I am developing an ASP.NET MVC application, and I have three projects: UI (with the system.web.mvc reference, BL and DAL references) BL (business facade and business objects) DAL (contains my ...
0
votes
2answers
80 views

Sending a notification to each participant 3 hours before the meeting starts

Let's assume an application that deals with meetings where some users will participate. What is an efficient way to achieve this use case: 3 hours before the meeting should start, one reminder mail ...
10
votes
4answers
833 views

Historical precedent for why Prolog is less popular than SQL in Imperative Programming? [closed]

It seems that writing Declarative SQL is very popular in Imperative Programming. However, it also seems that writing Declarative Prolog could save a lot of complexity but this is not very common. ...
0
votes
1answer
42 views

Django Project Logic Solution

I'm starting to develop my first webapp, and I'm using django. Before anything I'm working on the software logic, how it should work, the links and objects it needs. The problem is: I need the app ...
4
votes
4answers
426 views

Organise C++ classes around SQL database

My question is about how best to organize C++ classes around a database model, and I understand this may appear very elementary. The software I propose to create will do the following. It is intended ...
2
votes
5answers
203 views

Stored Procedures, ORMs and other application layers

I'm just starting out on a project to develop a new, fairly substantial web application which has an underlying MSSQL database. We're hiring a team of developers to write the application (in .NET) and ...
2
votes
2answers
207 views

An algorithm that spreads similar items across a list

I need an algorithm that distributes same items across a list, so maximizing the distance between occurrences. E.g. I have a list of 15 items: {a,b,c,c,c,d,e,f,f,f,g,h,h,i,j} The algorithm should ...
0
votes
2answers
58 views

Efficient database access for related data

As an example for this question, consider a content management system that has a content database table which contains things like the page title and location as well as the content itself which could ...
2
votes
2answers
132 views

Combine union with distinct

Situation: I need distinct results from two tables, that both have duplicates. I would like to know if I should combine UNION and DISTINCT. Example 1 - Only use distinct SELECT DistinctValue FROM ...
1
vote
1answer
277 views

Should ORM data access methods be wrapped or used directly?

We're using an internally-built ORM. Each table is represented by a Model class, which inherits from a base model class that has a handful of methods like GetAll, GetWhere (to get rows with specified ...