I can write basic queries in MySQL, PostgreSQL, and MSSQL, but that's about it (as you can see from some of my questions here). But as soon as you get anything more advanced than a basic query, I fall apart. So what are some good resources to read to improve my SQL skills? I'm looking for books, websites, PDFs...anything. Free would be b...
Hi, I'm looking for an UPDATE statement where it will update a single duplicate row only and remain the rest (duplicate rows) intact as is, using ROWID or something else or other elements to utilize in Oracle SQL or PL/SQL? Here is an example duptest table to work with: CREATE TABLE duptest (ID VARCHAR2(5), NONID VARCHAR2(5)); run ...
I'd like to create a table which has an integer primary key limited between 000 and 999. Is there any way to enforce this 3 digit limit within the sql? I'm using sqlite3. Thanks. ...
We have a common problem of moving our development SQL 2005 database onto shared web servers at website hosting companies. Ideally we would like a system that transfers the database structure and data as an exact replica. This would be commonly achieved by restoring a backup. But because they are shared SQL servers, we cannot restore b...
In DB2, you can name a column ORDER and write SQL like SELECT ORDER FROM tblWHATEVER ORDER BY ORDER without even needing to put any special characters around the column name. This is causing me pain that I won't get into, but my question is: why do databases allow the use of SQL keywords for object names? Surely it would make more ...
For example, using the answer for this question: http://stackoverflow.com/questions/152024/how-to-select-all-users-who-made-more-than-10-submissions "How to select all users who made more than 10 submissions." select userId from submission group by userId having count(submissionGuid) > 10 Let's say now I want to know many rows th...
I have recently had the need to write a fluent interface for C# that will essentially mirror SQL. Yes, I am aware of LINQ to SQL, but I'm interesting in getting "closer to the metal"--having something that essentially provides nothing more than an Intellisensified SQL shim within C#. E.g., var fq = new FluentQuery(); Expression<Action...
In the recent project, we had an issue with the performance of few queries that relied heavily on ordering the results by datetime field (MSSQL 2008 database). When we executed the queries with ORDER BY RecordDate DESC (or ASC) the queries executed 10x slower than without that. Ordering by any other field didn't produce such slow result...
Is there any way to export data (not necessarily schema) to an access database via asp.net? The server has no office components installed and the process must occur via a webpage (like an excel export). ...
I have an XML column in a table; I want to "promote" a certain value in that XML as a computed column and index it for faster searching. I have a function that takes in the XML information and outputs the element of interest, like this: CREATE FUNCTION [dbo].[fComputeValue] (@data XML) RETURNS datetime WITH SCHEMABINDING AS BEGIN RETU...
We may tag a question with multiple tags in StackOverflow website, I'm wondering how to find out the most related questions with common tags. Assume we have 100 questions in a database, each question has several tags. Let's say user is browsing a specific question, and we want to make the system to display the related questions on the p...
Each of my clients can have many todo items and every todo item has a due date. What would be the query for discovering the next undone todo item by due date for each file? In the event that a client has more than one todo, the one with the lowest id is the correct one. Assuming the following minimal schema: clients (id, name) todos...
Does anyone have any idea what is wrong with this create statement for mysql? EDIT: now it states the error is near: revised VARCHAR(20), paypal_accept TINYINT, pre_terminat' at line 4 Thanks for the help everyone Still errors after using sql beautifier though CREATE TABLE AUCTIONS ( ARTICLE_NO VARCHAR(20), ARTICLE_NAME ...
What's the most efficient way of getting the value of the SERIAL column after the INSERT statement? I.e. I am looking for a way to replicate @@IDENTITY or SCOPE_IDENTITY functionality of MS SQL ...
I'm familiar with the issue behind ORA-01775: looping chain of synonyms, but is there any trick to debugging it, or do I just have to "create or replace" my way out of it? Is there a way to query the schema or whatever to find out what the current definition of a public synonym is? Even more awesome would be a graphical tool, but a...
How can I run a stored procedure from a CL program? RUNSQLSTM requires a source member, but I just want to build a command so users can pass in variables easily, so this won't work. ...
It seems like there is a lot of overhead involved in rapidly opening and closing sqlconnections. Should I persist a connection (one, per client, per database), or continue declaring a new sqlconnection object whenever I need one, and making sure I clean up after myself? What have you done? What worked well and what worked poorly? ...
One of the "best practice" is accessing data via stored procedures. I understand why is this scenario good. My motivation is split database and application logic ( the tables can me changed, if the behaviour of stored procedures are same ), defence for SQL injection ( users can not execute "select * from some_tables", they can only call ...
In T-SQL, you can do this: SELECT ProductId, COALESCE(Price, 0) FROM Products How do you do the same thing in Access SQL? I see examples for doing it with Nz in VBA, but I'm looking for the SQL equivalent. Thanks. ...
I'm checking for existing of a row in in_fmd, and the ISBN I look up can be the ISBN parameter, or another ISBN in a cross-number table that may or may not have a row. select count(*) from in_fmd i where (description='GN') and ( i.isbn in ( select bwi_isbn from bw_isbn where orig_isbn = ? union all select...