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

4
votes
6answers
10k views

Using a relational database vs JSON objects for event/activity data

I am working on a project where I am trying to decide between using a standard SQL relational database or JSON objects to store data about an event or activity. The project will store data on ...
0
votes
2answers
172 views

Return multiple columns with SQL where GroupBy is on one column [closed]

I have noticed that all the columns in a SQL Select Query should be in a aggregate function, because otherwise the output value for group would be arbitrary. This makes sense. But when the other ...
1
vote
1answer
265 views

Is there a purely SQL alternate to looping?

Historically, I probably would have written a script (php or something) to loop through all spreadsheet rows and perform some calculations. I'm looking for a pure SQL solution on the presumption ...
0
votes
3answers
329 views

Maintaining indices for location in a sorted list in database rows

I have a fairly simple data structure like this: create table project (id int auto increment primary key, name text); create table item (id int auto increment primary key, name text, project_id ...
88
votes
10answers
17k views

Why is “Select * from table” considered bad practice

Yesterday I was discussing with a "hobby" programmer (I myself am a professional programmer). We came across some of his work, and he said he always queries all columns in his database (even on/in ...
0
votes
4answers
512 views

How to handle or prevent all Admins and users being removed

I have a website that stores all users in a database. A user can log on to the website and if they have sufficient privileges can add/delete users, as well as change their privileges. How do I ensure ...
0
votes
1answer
2k views

Why doesn't MySQL have an explicit “No Limit” option for queries?

I use SQLyog Community edition and like many other SQL applications out there it puts a 1000 result limit on queries that do not have a limit provided. I am wondering why MySQL doesn't have an ...
4
votes
5answers
271 views

Is it bad software design to mix JDBC SQL calls and use an ORM in an application? [closed]

Is it bad software design to have JDBC/raw SQL and also use an ORM? I don't mind using an ORM for boilerplate CRUD, but some queries are better in SQL (for me). I'm not arguing for or against ORMs. ...
6
votes
2answers
376 views

Slight extension for SQL prepared statements syntax. Need advice

In my database abstraction library I am extending SQL prepared statements syntax to hint a parser with expected literal type. I take it as a very essential improvement, my reasoning you can read here. ...
4
votes
2answers
815 views

How deeply programmer should know about internals of database engine? [closed]

I am learning SQL Server 2012 along with C#. In the past, I took database class in college, and have good grasp on SQL language and can write queries. However, looking at SQL Server documentation (or ...
4
votes
1answer
971 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 ...
0
votes
3answers
119 views

SQL string field lengths in a db having about 6 different types of addresses, with about 20 markets and environments

I have been advocating lately that all string fields should be ntext / nvarchar(max) - we're using MS SQL Server. The objections seem to be either "it's not a good idea" (without any reason) or "that ...
0
votes
1answer
322 views

What happens if computer crashes after giving commit command

If we are updating a field in SQL and ALTER the row also. After giving the COMMIT command, the system is crashed. What will happen to the commands given, whether it will UPDATE and ALTER the table ...
1
vote
3answers
178 views

Select custom output formats from database with SQL

I am planning to make a simple rest service application, and I am currently deciding the architecture. I have decided that I want to write the middle layer in multiple languages, so that it is easy to ...
0
votes
0answers
59 views

Using a single table for identity and metadata

I'm in the early design phase of a project to provide an e-commerce platform that will require several entities to be modelled, products, customers, orders, CMS pages, etc. They will all have a few ...
1
vote
1answer
386 views

Do software developers write SQL? [closed]

Do software developers write SQL or database developers provide the SQL and the software developers use it? I am a novice to software development. I am just trying to understand the software ...
2
votes
2answers
209 views

Use Of Exists clause in SQL

Just finished Stanford lecture on SQL (by Prof. Jennifer Widom). However I have developed a confusion regarding the use of EXISTS clause. I thought it is just like a condition and an expression so ...
3
votes
3answers
460 views

Is it better to use a switch statement or database to look through 5,000 to 10,000 instances?

I have some JSON data in this format (5,000 instances for now): {"id":"123456","icon":"icon.png","caseName":"name of case"} I am allowing the user to search for the name of the case and then return ...
4
votes
4answers
525 views

Choosing value in SQL query vs (Java) code

I need to choose one of three values of an integer using the value of a column on a nullable column of a table. There are at least two approaches: 1) use SQL to do all the work: test null values, and ...
0
votes
3answers
141 views

High-Level SQL Interoperability

In a server -> client scenario, wouldn't it be simpler and faster to grant a public user access to a Stored Procedure rather than using web services (XML, REST, SOAP, etc) and other interoperable ...
0
votes
1answer
430 views

How to combine Google Analytics with Relational Database

I have a website with quite a lot of registered users, they are stored in a PostgreSQL database. I also running Google Analytics on the website to track the users. Is there a way to individually ...
1
vote
1answer
348 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, ...
0
votes
4answers
152 views

When to store Date/DateTime?

I'm working on a social network website and I'm not quite sure when I should store the date/datetime. The service includes some of the following events: Register/Login Follow "Like" (images & ...
2
votes
1answer
201 views

Mental schema for SQL queries [closed]

While preparing SQL introductory material, I've ended asking myself about the line of thought followed by a developer when writing a query. I believe it could be too valuable from a beginner's point ...
-3
votes
1answer
147 views

Finding next free IP in a network

I have an IP network and want to automatically find a free IP address to provision a new server. Due to frequent server additions/removals the used IPs might be quite fragmented over the whole address ...
2
votes
0answers
227 views

Match buyers and sellers based on date ranges and quantity of shares

NOTE: This is in reference to the question I asked on Stack Overflow but was told to post this here I'm building a trading application that matches buyers/sellers that own subscription. Subscriptions ...
3
votes
5answers
246 views

Violating SQL principles

I have to write a decent size database, 1GB more or less. I have only taken an introductory semester regarding SQL databases. During the course we learned that the relational model under SQL has no ...
-1
votes
2answers
508 views

Lead developer unable to do INNER JOIN [closed]

I had worked in the past for a small company developing web applications. Back then the current lead developer, who is also one of the bosses, told me that he is incapable of making a JOIN inside a ...
1
vote
2answers
863 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 ...
1
vote
0answers
193 views

What database structure is suitable for tracking File audits?

I need to track permissions and access requests to a file server in a database. I'm given the full path of the folder and am considering parsing the path (splitting on the "/" character) and creating ...
2
votes
2answers
137 views

SQL Query syntax - formatting?

I'm looking at a very large (5-6 digit LOC), very complex, code-base, full of interacting bulky, interdependent, views and stored procedures (multiple 4-5 digit count silos). The source SQL has been ...
4
votes
3answers
402 views

How to avoid unmaintainable code when using SQL in an MVC setting?

I want to present a list of products to the user. This list is paginated and can be sorted by the user by product ID or product name in ascending or descending order. I am using an MVC setting, where ...
0
votes
1answer
565 views

How exactly can I check for new rows in sql with ajax? [closed]

How do certain services, just like google plus and facebook, check for new content without reloading the page? Whenever you are on any of those websites a new notification of a post related content ...
0
votes
1answer
147 views

How to store Status for Reservation table

I have a reservation table (a user fills out a form to request a reservation). It has two parts that need to be confirmed. isReservationAccepted (decline,accept,waiting) and hasReservationBeenSent ...
0
votes
1answer
167 views

Queries in three tier architecture

I am wondering where should the DB queries need to be in the three tier architecture. The first association that think is in the DAL. But then if the BL want to search for an object, the DAL need to ...
22
votes
2answers
1k views

Internal-use websites: Is there a compelling case against SQLite?

Many web frameworks, such as Flask or Django use SQLite as their default database. SQLite is compelling because it's included in python, and administrative overhead is pretty low. However, most high ...
0
votes
1answer
219 views

Search for object in loop or query? Who has better performance?

I have a DB that stores some complex objects of my app. When my app starts I load the objects into list and store them on the RAM. But sometimes I would like to search for an objects that fulfills ...
5
votes
3answers
476 views

Consequences of “naïve” vs “naive”?

While using IE autocorrect "naive" got transformed to "naïve"! My regional settings are Au English, from a Unicode search point of view the two are nothing alike. I am not even sure whether there are ...
0
votes
1answer
91 views

Multiple schemas vs aditional column per customer

If you have different database per customer as there is no need to share any data between them does the same apply if a customer has multiple stores ? I believe that it is easier to do queries for ...
0
votes
1answer
741 views

Notify client about changes in database in java

I've been searching around for the answer to this question, but haven't been able to find a solution. I am working on a distributed client-server system that uses TCP. The program's functions is to ...
0
votes
1answer
113 views

SQL-99 specification, is it specified to first order the result and limit it afterwards?

If I wrote this in MySQL SELECT * FROM foo ORDER BY bar LIMIT 1 it would first order the result and limit it afterwards. I always get the lowest bar. I wonder if this is specified like this in ...
8
votes
1answer
279 views

Can relational algebra/calculus proofs be used to test/verify SQL?

Is it feasible or even possible to use relational algebra and/or relational calculus in the form of proofs to test/verify the correctness of SQL statements, functions, and stored procedures? It seems ...
7
votes
5answers
323 views

In retrospect, has it been a good idea to use three-valued logic for SQL NULL comparisons?

In SQL, NULL means "unknown value". Thus, every comparison with NULL yields NULL (unknown) rather than TRUE or FALSE. From a conceptional point of view, this three-valued logic makes sense. From a ...
3
votes
1answer
194 views

Best way to rename existing unique field names in database?

I have a database table that contains id, filename, userId id is unique identifier filename should also be unique table may contain >10000 records When a user uploads a file it should be ...
4
votes
2answers
2k views

Database model for keeping track of likes/shares/comments on blog posts over time

My goal is to keep track of the popular posts on different blog sites based on social network activity at any given time. The goal is not to simply get the most popular now, but instead find posts ...
1
vote
1answer
254 views

Store data for multiple User Type

I am developing an app where four types of user share same module. So is it a good idea to create: one table and user_type and user_id (something like that) table prefixes basically 4 diff tables of ...
1
vote
2answers
139 views

Configurable tables in sql database

I have the following tables in my database: Config Table: ====================================== Start_Range | End Range | Config_id 10 | 15 | 1 ...
9
votes
1answer
2k 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 ...
0
votes
2answers
3k views

Using lucene and sql server togheter. Newbie needs directions [closed]

Basically the whole thing can be explained simply: I need to index one or more SQL Server 2005 databases with lucene so I can search the various records. I found a lot of examples and documentation ...
1
vote
1answer
129 views

structure problem in Relational DBMS creation

For learning and understanding purpose, I currently want to try to make a small relational DBMS with simple features like (for now) only sequential reading/writing and CREATE TABLE, INSERT, SELECT, ...