sql

How do I UPDATE a row in a table or INSERT it if it doesn't exist?

I have the following table of counters: CREATE TABLE cache ( key text PRIMARY KEY, generation int ); I would like to increment one of the counters, or set it to zero if the corresponding row doesn't exist yet. Is there a way to do this without concurrency issues in standard SQL? The operation is sometimes part of a transaction...

MS Access query: why does LIKE behave differently when being called from VB6 app?

I don't do a lot of coding with VB6, but I'm updating an existing app now and just encountered a snag. I figured out the problem. In VB6, queries must use the % wild card when using LIKE, but in MS Access, you have to use the * wild card. I'm querying the same database - (it's in MS Access). When querying from within MS Access, the f...

Informix SQL count() comparisons

I'm trying to build a SQL query that will count both the total number of rows for each id, and the number of 'FN%' and 'W%' grades grouped by id. If those numbers are equal, then the student only has either all 'FN%' or all 'W%' or a combination of both. I need a list of all the id's who only have stats of 'FN%' or 'W%' example id # 6...

What SQL query or LINQ code would get the following data?

First, here is a picture of the relevant part of my database schema: I have several conceptual queries of increasing complexity. Currently, I am doing these with a series of SQL queries (i.e. many back-and-forth trips from the C# code to the SQL Server), and I'd like to find a way to get these each in a single query, but doing so is ...

SQL JOIN: ON vs Equals

Is there any significant difference between the following? SELECT a.name, b.name FROM a, b WHERE a.id = b.id AND a.id = 1 AND SELECT a.name, b.name FROM a INNER JOIN b ON a.id = b.id WHERE a.id = 1 Do SO users have a preference of one over the other? ...

How to determine the number of days in a month in SQL Server?

I need to determine the number of days in a month for a given date in SQL Server. Is there a built-in function? If not, what should I use as the user-defined function? ...

How to handle duplication between java enum and database table?

It is a quite common situation in our applications that some entity have to be represented by an enum: for example types, categories, status, and things like that. Often, there are conditions or flows in the code that use the values to decide between one action or another, so the values have to be "known" in some way for the applicatio...

Does SELECT DISTINCT imply a sort of the results

Does including DISTINCT in a SELECT query imply that the resulting set should be sorted? I don't think it does, but I'm looking for a an authoritative answer (web link). I've got a query like this: Select Distinct foo From Bar In oracle, the results are distinct but are not in sorted order. In Jet/MS-Access there seems to be some ...

Query will not run with variables, will work when variable's definitions are pasted in.

This is a Query in VBA (Access 2007) I have 3 strings defined: str_a = "db.col1 = 5" str_b = " and db.col2 = 123" str_c = " and db.col3 = 42" Then I use these in the WHERE part of my Query: "WHERE '" & str_a & "' '" & str_b & "' '" & str_c & "' ;" This fails, but If I paste in the strings like this: "WHERE db.col1 = 5 and db.col2 ...

Saving to a SQL Database?

I have made a small SQL database to hold some information about students. I want to save the data to the DB. I've been using the Datasource Wizard to dispaly/manage/save my data. It all works until I need to save. I double-clicked the save button and this is the code that is in there: this.Validate(); this.studentsBindingSource.EndEdit(...

Quick SQL question: Correct syntax for creating a table with a primary key in H2?

I'm currently starting a new Java application using the H2 database, but I have some confusion about basic SQL use for creating tables. How do I make a table of entries (strings) each with unique, auto-incrementing, non-null, integer primary keys? One of the most basic things to do, but I'm not sure offhand what the correct way to do i...

Ensuring Atomicity sql

Hi, I was just reading about RDBMS, and one property of an RDBMS is atomicity. So, if money is withdrawn from an account and transferred to another, either the transaction will happen completely or not at all. There are no partial transactions. But how is actually ensured? Sql queries for the above scenario might look like (i) UPDATE ac...

SQL query with join, sum, group by, etc

Hi, I'm trying to build a report that will look like this: jan feb mar apr may jun jul ago sep oct nov dec food 0 1 1 2 0 0 3 1 0 0 1 1 car 1 0 0 0 1 2 1 0 1 2 3 4 home 0 0 1 2 2 2 5 1 2 4 0 0 other 0 0 0 0 0 0 0 0 0 0 0 0 I have two...

Where can I find useful item lists (SQL, or plain text) like sports, hobbies, and others?

GeoNames public geographic locations index is a very useful website that I have used to import a list of countries and cities into my web app. Now, are there any other public databases that would provide various useful lists? (Other than Wikipedia) I am looking for a list of sports, hobbies and similar items. ...

SQL INSERT/SELECT where not in insert table

INSERT INTO `tableA` SELECT `Col1`, `Col2`, NOW() FROM `tableB` WHERE tableA.Col1 is not already in tableB.Col1 I can't get the WHERE clause right to ensure that the record copied from tableA only appears in table B once... ...

SQL: how to join multiple tables?

I have the following tables (and example values): user: user_id (1, 2, 3) username (john33, reddiamond...) password (pass1, pass2...) session: session_id (4,5, 6) user_id (1, 2, 3) activity activity_id (1, 2) name (running, walking...) user_activity user_activity_id (1, 2, 3, 4, 5) session_id (4, 5) activity_id (1, 2) All columns wi...

Why does my website constantly freeze?

This is a pretty vague question and getting it answered seems like a long shot, but I don't know what else to do. Ever since I made my website live every now and then it will just freeze. You click on a link and the browser will just site there looking like its trying to connect. It seems the freezing can last up to 2 minutes or so, th...

SQL Join help

<?php $query = mysql_query("SELECT * FROM threads INNER JOIN accounts ON threads.author = accounts.id WHERE id = ".intval($_GET['threadID'])); $row = mysql_fetch_assoc($query); $title = $row['title']; ?> What if I have a column named the same in both tab...

Average, Count, etc of entire column of table in SQL Query or as Function in VBA to Display result in Form?

I need to calculate Count, Average and a couple other things of a column as a result of a Query and then put the result in a text box in a form. I was thinking about either implementing it in the Query and putting the results in a column or something like that, OR use a VBA function to calculate it; however I don't know how to calcula...

best way to get good with sql queries

As a programmer I would like to get a strong hold on writing queries. In my college years I've read a few sql books and rest I've just learned working as a programmer for last couple of years. But as those queries were work related...they weren't that 'hard' or complex. what would you guys suggest ? Is there a good advanced sql book...