An acronym for "Structured Query Language" (it's not the name of a specific DBMS product), SQL can be used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. It can also be used to manage schemas and data ...

learn more… | top users | synonyms (1)

0
votes
1answer
1k views

How to UPDATE a column to SET previous row value to NULL cells?

I have a table as id value 1 music 2 3 movie 4 5 6 book 7 8 Some values are missing, and I want to assign the value of previous row to produce id value 1 music 2 music 3 movie 4 ...
1
vote
1answer
84 views

How to aggregate datapoints in a table?

Suppose I have following table - CREATE TABLE data_points (t DATETIME PRIMARY KEY, value INTEGER); I want to aggregate the data by calculating average of every 10 points in the table. i.e. If ...
0
votes
1answer
124 views

clustered index - data warehouse

My source data comes from multiple OLTP databases where the primary key is an identitiy column. We anticipate adding data centers and/or splitting the databases by product. Currently i have one system ...
3
votes
1answer
698 views

Profiler TextData output is null

I am doing a trace on a database and dumping the trace realtime into a table. The reason I'm doing a trace is I want to see all the queries that are being executed against the database. ...
2
votes
2answers
793 views

How to GROUP_CONCAT between given rows?

I have a table as id diary 1 breakfast 2 walk 3 start 4 office works 5 office projects 6 end 7 taxi 8 start 9 preparing for meeting 10 doing the meeting 11 end 12 night I want ...
0
votes
1answer
184 views

avoid insert xml duplicate data

how can to prevent duplicate inserts for every Execute? insert into IQ (XMLDT)values('<row>sdsdf</row>')
4
votes
1answer
635 views

ANSI/ISO plans for LIMIT standardization?

Are there currently plans to standardize one best way of limiting the number of results returned by a query? The stack overflow question at Is there an ANSI SQL alternative to the MYSQL LIMIT ...
1
vote
1answer
676 views

Splitting a column to normalized table

I have a de-normalized table as Original De-Normalized Table id text 23 first,second|third,fourth,fifth|sixth I want to normalize the database to create the following tables Table 1: (Split by ...
2
votes
2answers
5k views

How to Drop Database in Single_User Mode

How do I drop a database which shows DatabaseName (Single User) as its name? When I try to delete it, I get the following error: Alter failed for Database 'DatabaseName'. ...
0
votes
2answers
391 views

Merging multiple databases as separate schemas in PostgreSQL

I have to combine data from 4 different databases. Each having its own design. What i want to do is to Create a new database with my desired schema. and one schema per (to be merged) database Then ...
1
vote
3answers
397 views

Which RDBMS has the most advanced “table-valued” functions?

Every Database Management System focuses on specific features and applications. I am using mysql, probably as it is popular and satisfies common needs. However, I came across a problem, which needs ...
2
votes
2answers
405 views

Reordering data in a table

I have a table that stores sensor data (in SQLite) and looks like this; TABLE Timestream: idTimestream PK autoincrementing integer, time int not null, value float not null, idSensor integer not ...
3
votes
2answers
618 views

How to split a string to and get info from another table in mysql

I have a system to assign score for each sentence stored in a column: TABLE 1: word_scores word score this 3 is 4 a 5 test 1 another 0 sentence 8 TABLE 2: ...
0
votes
2answers
180 views

How can I optimize this query and support multiple SKUs?

My current query only can select one SKU at a time. I can leave salesite_id constant. If there is a way to also have varying salesite_ids that would be good too, but not necessary. Also any ...
4
votes
2answers
2k views

How do you set the pagesize, codeset and table space when creating a database in DB2?

I want to create a database in DB2. I found some information on this page I want to use only some options which are explained on the page: PAGESIZE CODESET TABLESPACE Then, I want to ...
0
votes
1answer
194 views

How can I use “select distinct” and have a range for my rows? [closed]

I wrote this code in PL/SQL developer and was wondering how I could have one row per range of t.ttt in the select distinct columns. Here's the SQL: select count (t.aaa), sum (t.bbb) from xxx t where ...
3
votes
4answers
3k views

Field types for columns in a MySQL query

Is it possible to get the field type from a MySQL query, in the same way you can get it from a table with the SHOW COLUMNS command? edit: i should clarify that a bit. I am aware that I can get the ...
2
votes
2answers
2k views

How do I force a JOIN to use a specific index in MySQL?

I have a query that JOINs 2 tables; lineitem and part, select sum(l_extendedprice* (1 - l_discount)) as revenue from lineitem force index for join (l_pk), part where ( ...
5
votes
1answer
792 views

SQL Server 2008 R2 DMV Question

I have a query to retrieve the Top 10 queries based on total logical reads from the dm_exec_query_stats DMV. SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1, ((CASE ...
2
votes
1answer
320 views

Help with SELECT queries

I need help with some queries. I struggled do find out how to do it, but I think I finally caught on or at least got the results I was looking for in the database. These are the tables (given by my ...
1
vote
3answers
386 views

Usefulness of a multi-attribute index

If an index has more than one attribute in it, is there any speed gained in a select statement whose where clause uses one of the attributes in the index? For example, take a table T with an index on ...
1
vote
1answer
205 views

SQL Server - Ignoring object dependencies to copy a DB (despite some column referenced in a sproc no longer existing)

First of all apologies for the novel but there's a lot to explain... We have over 100 databases for our product, 1 per server, each of which has variations on other ones (ideally they should all be ...
3
votes
0answers
120 views

How to Audit for Access with Cross Database Ownership Chaining

I've just joined the data team of a new company and we have a large SQL Server environment (100+ instances) and cross database ownership chaining is on by what I gather is a necessity. I'm looking for ...
12
votes
2answers
10k views

MySQL: Tree-Hierarchical query

SUB-TREE WITHIN A TREE in MySQL In my MYSQL Database COMPANY, I have a Table: Employee with recursive association, an employee can be boss of other employee. A self relationship of kind (SuperVisor ...
1
vote
2answers
515 views

list tables that are missing primary/foreign key in postgresql database

I've seen a ton of queries to list primary and foreign keys but how can I query for tables missing primary and foreign keys? PostgreSQL 8.4 and 9.x versions
4
votes
3answers
1k views

SQL Server - Varbinary Column - Extremely Slow Statistics Update

I have a table in my database that we use as a filestore, the file itself is stored in a varbinary column, which all seemed to work well until recently, we noticed one of our instances of this table ...
2
votes
2answers
113 views

Joining 4 tables and 2 counts

I have four tables. UserMaster ---------- UserKey - PK UserName UserID - UNIQUE UserLogTransaction ------------------ UserID - FK:UserMaster.UserID LoginTime LogoutTime CallCenter ---------- ...
3
votes
1answer
2k views

query to identify all data types used in postgresql database tables

I don't want to know all data types, just all data types used in my database. Can this information be queried? PostgreSQL 8.4 and 9.x versions I currently need to know all data types for over 200 ...
3
votes
1answer
2k views

DB2 Defining remote database alias

I'm using DB2 9.7.0.3 for AIX. I want to select data from a remote database, also running DB2 9.7.0.3 for AIX, and insert it into the local database I'm connected to. I believe the query for this ...
2
votes
1answer
3k views

What are SQL Server Securables?

I migrated two applications to the new server. One of the them works without any issues but the other ran into back end sql permission problems like The EXECUTE permission was denied on the ...
2
votes
2answers
312 views

Execution plan flips Filter and Execute Scalar when using the PK, causes cast to fail

I have some data tables that where imported in from a flat file source so everything is varchar(max) except FileRecordID which is a int that hold the row number the data came from in the flat file ...
3
votes
1answer
3k views

Get week/month data

There is a table (MySQL). It's simplified structure: day DATE val INT UNSIGNED I need the average val for: every week containing a date from the table; every month containing a date from the ...
3
votes
2answers
622 views

How to concatenate a column after x rows?

I have a table with columns object varchar(255), frequency tinyint(3) unsigned, I want to concatenate the objects if the sum of frequencies reach a value. Currently I am doing this in PHP as .. ...
2
votes
2answers
2k views

SQL set allowed values for a column

I want to make an ALTER TABLE expression which adds a new column and sets a default value and additionaly defines the allowed values for that column. It's a text column, and allowed should be only ...
2
votes
2answers
2k views

Oracle 11g: “With Clause” very slow

I have written a query utilizing Oracle's with clause. I have identified one particular with clause in a larger query to be really, really slow. Here are three versions of the same query, they all ...
1
vote
1answer
112 views

Does Microsoft Security Bulletin MS11-049 affect SQL Server 2008 SP3?

The security bulletin does not list SQL Server 2008 SP3 under affected software but it came out prior to that service pack. We are running SP3 but this issue is still showing up on a vulnerability ...
4
votes
2answers
1k views

SQL Server - How to know when a database has been restored

I'm using sql server 2008, and I would like to know if a specific database has been restored from a device and when did it happened. Is it possible?
0
votes
0answers
862 views

Dynamically Converted rows into columns (Pivoting)

Query SELECT Performparam.performparam_Name,studfeedback.studfeedback_Rating, SUM(studfeedback.studfeedback_Rating) AS total, COUNT(studfeedback.studfeedback_Rating) AS totastudents, ...
2
votes
1answer
654 views

SQL Server Maintenance Plan multiple database backups in a single folder

I am using SQL Server 2008 R2. I am trying to create a maintenance plan which backs up multiple databases into one single folder for a day. For ex. I need to backup db1, db2, db3 and dump all these 3 ...
4
votes
2answers
168 views

How to best encapsulate monetary values in a database

I am working on a multi-currency application. For the purpose of simplifying this question, we will not be considering currency conversion (hence I have deliberately left AsAt : datetime out of the ...
4
votes
2answers
3k views

Correct way to design a shopping cart websites database

I am in a web applications course. My instructor really didn't like the layout of my database, saying it was incorrect in pretty much every way. Fast forward 2 months when I'm remaking the website in ...
1
vote
3answers
191 views

Check of SQL Server upgrade success

I have performed SQL Server upgrade from 2005 to SQL Server 2008. Is there any tools/possibilities to check, whether the upgrade from SQL Server 2005 to SQL Server 2008 has been accomplished ...
4
votes
2answers
148 views

We are running database maintenace with indexing the system, indexing fails due to locking?

We have been trying to do re-indexing in our system, there are users still using the database during the indexing. While doing index for certain tables there is failure of indexing this seems to be ...
4
votes
2answers
122 views

SQL Server update and database transfer

In my test network there is a database created with SQL Server 2005. I have to update SQL Server 2005 to SQL Server 2008. The data base should be used with SQL Server 2008. So it is to be moved. What ...
1
vote
1answer
1k views

Sybase SQL Anywhere 12 - Get all indexes which are unique -> ambigious sysindexes error

we are using a Sybase SQL Anywhere 12 db. In the db there are indices, which are unique, but shouldn't be unique. Therefore I search for a quick way to list all tables with unique primary keys. I ...
1
vote
2answers
365 views

SQL queries break our game! (Back-end server is at capacity)

We have a Facebook game that stores all persistent data in a MySQL database that is running on a large Amazon RDS instance. One of our tables is 2GB in size. If I run any queries on that table that ...
1
vote
2answers
200 views

Can this SQL be made more efficient and combined into one select?

I have the 4 SQL queries below that I combine into one result set at the end. I am guessing that since I am operating on the same tables in each of the SQL sections, there is a cool way to get my ...
0
votes
2answers
5k views

Use of CASE statement in a MySQL query

I have 3 tables: TASK (id_task (PRIMARY KEY), name) PROJECT (id_project (PRIMARY KEY), name) PROJECT_HAS_TASK (id_project, task_id) PROJECT_HAS_TASK is the (n,m) relation between PROJECT and TASK. ...
2
votes
1answer
166 views

GRANT privileges to table elements

What happens if I GRANT priveleges (for example SELECT) ON a table TO some group role? What privileges will the table's elements have in this case: indexes, sequences, columns? Should I explicitly ...
2
votes
2answers
818 views

how to get avg() size by day using two decimal places

does this simple query result in grouping the attachments by day and giving me the total number of daily attachments as well as the average size? the only table i am querying is attachmentdetail ...