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

0
votes
2answers
1k views

Large MySQL Batch Inserts From PHP: Run insert from script, or save as SQL file?

We have a large dataset that's current residing in many, many spreadsheets. I've been tasked with getting part of that data into our MySQL DB. When all is said and done, I will probably be inserting ...
1
vote
3answers
199 views

Store history in SQL database column

We have a database that stores the process/history of particular machines. What I mean by that is that we have "started", "updating", "shutdown", and so on for all the computers in the company. The ...
7
votes
4answers
570 views

Direct database manipulation an anti-pattern?

Recently I learned that some teams have moved all their database manipulation to the actual database through the use of stored procedures. I thought that was pretty clever, since the database becomes ...
13
votes
4answers
8k views

Maintaining referential integrity between a mobile client and a server

So I have a relatively simple system. A mobile client creates records in a sqlite database that I would like to have synced to a remote SQL server (that is shared with other mobile clients). So when I ...
1
vote
2answers
247 views

Can I create a query which allows me to do this?

I have a table which stores user information, one column stores their ID. I had implemented four different attributes in the same table which stored a bit indicating which file operations that user ...
2
votes
1answer
694 views

Implementing Syncing between localStorage and SQL database?

I have made a simple web app, which runs completely offline - all data is saved in HTML5's localStorage. Now, I want the data to get synced with the server, so that the user is able to use the app on ...
-1
votes
2answers
709 views

When do you use subquery in SQL? [closed]

When do you use subquery in SQL, and what type of request is subquery useful for? I am practicing SQL and need to know how to spot a subquery problem immediately
0
votes
2answers
257 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
465 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 ...
1
vote
3answers
671 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 ...
90
votes
10answers
25k 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 ...
17
votes
11answers
23k views

Why is prefixing column names considered bad practice?

According to a popular SO post is it considered a bad practice to prefix table names. At my company every column is prefixed by a table name. This is difficult for me to read. I'm not sure the ...
4
votes
6answers
3k views

Confusion about proper use of * wildcard in SQL [duplicate]

Suppose I was writing an address book application where the details of each contact were stored in an SQL database. A user wants to know all the details for a person whose name is "Bob [Someone]". ...
0
votes
4answers
754 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
4k 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 ...
1
vote
0answers
282 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 ...
4
votes
5answers
352 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
433 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. ...
127
votes
14answers
38k views

“Never do in code what you can get the SQL server to do well for you” - Is this a recipe for a bad design?

It's an idea I've heard repeated in a handful of places. Some more or less acknowledging that once trying to solve a problem purely in SQL exceeds a certain level of complexity you should indeed be ...
0
votes
1answer
514 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
261 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
141 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 ...
3
votes
1answer
220 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 ...
1
vote
1answer
405 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 ...
138
votes
13answers
75k views

Why use a database instead of just saving your data to disk?

Instead of a database I just serialize my data to JSON, saving and loading it to disk when necessary. All the data management is made on the program itself, which is faster AND easier than using SQL ...
2
votes
2answers
298 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 ...
4
votes
3answers
526 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
773 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
144 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
821 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 ...
0
votes
4answers
192 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 & ...
-4
votes
1answer
3k views

Why do we still use a programming language instead of using just SQL? [duplicate]

In database managements systems lecture, my professor said that we can use Oracle etc. ui designing tools instead of using Visual C# or etc. He added that SQL is as strong as a programming language. ...
2
votes
0answers
262 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 ...
54
votes
11answers
71k views

SQL: empty string vs NULL value

I know this subject is a bit controversial and there are a lot of various articles/opinions floating around the internet. Unfortunatelly, most of them assume the person doesn't know what the ...
0
votes
1answer
3k views

Size of database a factor for Hibernate vs JDBC?

Do you know if the size of the database (number of tables used) is a factor when choosing between Hibernate and JDBC? Why or why not? In my particular case, I am evaluating Hibernate and JDBC for ...
3
votes
5answers
251 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
538 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 ...
4
votes
4answers
3k views

Can NoSQL databases be used in e-commerce companies for order management?

Is it possible to use a non relational database be in an Order management system for an e-commerce company with a large number of orders everyday, like Amazon? Can the benefits of Non Relational ...
2
votes
4answers
764 views

How to genericize foreign key references in table?

I am trying to design a table to store references to multiple tables as a "target" of the item in each row. An example should clarify what I am trying to do: Imagine you have 4 tables (images, ...
2
votes
2answers
146 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
551 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 ...
5
votes
3answers
491 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 ...
17
votes
2answers
2k views

Does F# offer anything particularly useful for database-driven web development?

My organization finally upgraded to MS Visual Studio 2010 this year. One of the big new features that Visual Studio 2010 offers is the F# programming language. I understand that F# offers a ...
1
vote
1answer
135 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, ...
0
votes
1answer
232 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
805 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
207 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
2k 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
405 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 ...
0
votes
1answer
98 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 ...