sql

Finding correlated values from second table without resorting to PL/SQL

I have the following two tables in my database: a) A table containing values acquired at a certain date (you may think of these as, say, temperature readings): sensor_id | acquired | value ----------+---------------------+-------- 1 | 2009-04-01 10:00:00 | 20 1 | 2009-04-01 10:01:00 | 21 1 | 20...

Copying a set of records with constraints, with common ancestor.

I have a set of tables that's actually a stubby tree. At the top, there's a Customer, and below that Invoices, and Invoice Detail records. (In actuality, there's about two dozen of these tables all referring to the Customer but the principle should apply with just three tables.) What I'd like to do is to copy the Customer and all of t...

plsql custom numeric format

I'm trying to format a numeric value always in the format "9 999,99" (notice the space). The problem is that oracle does not provide me a a numeric mask for getting this format. The closest I get is with to_char(value,'99990,99'), But no way of putting that space after the third digit. some examples of what I would like to get 1345....

Database query time complexity

I'm pretty new to databases, so forgive me if this is a silly question. In modern databases, if I use an index to access a row, I believe this will be O(1) complexity. But if I do a query to select another column, will it be O(1) or O(n)? Does the database have to iterate through all the rows, or does it build a sorted list for each col...

Asp.Net binding SQL data to a repeater?

I am trying to bind data from SQL to a repeater control. I have tried what I usually do for a Gridview and it doesn't work. I would like to see an example whether it be using an SQLAdapter or using ExecuteReader from a command. Thank you! string sql = "SELECT [WallPost], [DatePosted] FROM [WallTable] WHERE [UserId] = '" + Request["frie...

select statement to return the column names in a table

Is there any select statement to return the list of columns in the table? ...

Are there multiline comment delimiters in SQL that are vendor agnostic?

While editing, building up, or cherry picking from SQL statements I can find myself interacting with one of four popular database tools. I have been resorting to single line commenting for DB2 and Informix. I have created macros in vim to make this slightly more efficient but I was wondering if I am working too hard. ...

Disable Foreign key constraint on a table?

Can i temporaryly disable a foreign key constraint. How do i do this? ...

Performing Insert OR Update (upsert) on sql server compact edition

I have c# project that is using sqlserver compact edition and entity framework for data access. I have the need to insert or update a large amount of rows, 5000+ or more to the db, so if the key exists update the record if not insert it. I can not find a way to do this with compact edition and EF with out horrible performance, ie takin...

convert a string to int using sql query

i want to convert a string field to integer using sql query? ...

Can you do a select on the results of a stored procedure in T-SQL

select * from (EXEC sp_SomeStoredProc) If you can't do this then what is stopping it from being added to the SQL standard or T-SQL? ...

SQL exclude a column using SELECT * [except columnA] FROM tableA?

We all know that to select all columns from a table, we can use SELECT * FROM tableA Is there a way to exclude column(s) from a table without specifying all the columns? SELECT * [except columnA] FROM tableA The only way that I know is to manually specify all the columns and exclude the unwanted column. This is really time consumin...

Simple SQL to return a count without using sub queries

I am trying to get the number of students enrolled in courses via a single SQL statement, but not using sub-queries. So far I can only figure out how to do it using sub-queries. Is there another way? Consider the following database setup: create table student (id integer not null primary key); create table course_enrolment (student in...

Sql table unresponsive when selecting or counting all rows

We have a table in a database that has 35 rows, according to exec sp_spaceused Department. I am able to run SELECT TOP 1 * FROM Department, and get a result, but when I run SELECT COUNT(*) FROM Department, it runs longer than 2 minutes (I then cancelled it and did not wait for a result, since I expect this to be a simple and fast...

Eliminate partial duplicate rows from result set

Hi, I have a query that returns a result set similar to the one below (in reality it is far bigger, thousands of rows): A | B | C | D -----|----|----|----- 1 NULL | d0 | d0 | NULL 2 NULL | d0 | d1 | NULL 3 NULL | d0 | d2 | a0 4 d0 | d1 | d1 | NULL 5 d0 | d2 | d2 | a0 Two of the rows are consi...

MySQL Multitable Query Problem

I have a problem querying multiple tables in MySQL and am currently at my wits end. I have unique IDs in each table, and am using an INNER JOIN to combine them; I am quite new to SQL and this may be the wrong method, hence me posting here: Query: SELECT res.UserID, res.QuizID, res.QuizResult, u.UserID, u.UserLogin, q.QuizID, q.QuizNam...

SQL: Counting number of occurances over multiple columns in Oracle

Cannot get my head around the SQL that will return the number of times a user has accessed a particular service. Think it might require a nested count and select, but cannot get my head around it. The data looks like this: UserID Service --------------- 1 Map1 1 Map2 1 Map1 2 Map1 2 Map2 3 Map4 3 ...

SQL update with a join?

I have two tables. One is simple string/ID look up: StrTable: str_key String 0 'a' 1 'b' where the strings are unique. The other is more complex, and includes the shared string_id ValTable: str_key other_key val 0 0 1.234 0 1 1.567 1 0 1.890 Now, I want to do an update on Va...

How to filter nvarchar greater than numeric value?

I have a MS SQL table McTable with column BigMacs nvarchar(255). I would like to get rows with BigMacs value greater than 5. What I do is: select * from ( select BigMacs BigMacsS, CAST(BigMacs as Binary) BigMacsB, CAST(BigMacs as int) BigMacsL from McTable where BigMacs Like '%[0-9]%' ...

how can i add select query result into dataset later again select query run...

i want to add select query result into dataset, so i can write new query to run on it to get net dataset but how? Original query: MyDATASET=( select x, y,z from table1 union all select k,l,m from table2 ) i wan to this select * from this.MyDATASET ...