Structured Query Language (SQL). For questions about the language, not Microsoft SQL Server (tag sql-server).

learn more… | top users | synonyms (2)

4
votes
1answer
84 views

Selecting Without Repititions

I have table with observations of objects moving along edges in a graph, this table has the following form: PK | TIMESTAMP | object_id | from_id | to_id where object_id is the id of some object and ...
3
votes
2answers
19k views

Error on SELECT - Primary filegroup is full

A SQL Server 2008 R2 database view unions several transaction table. The query has been slow over the couple of months and the dev team is planning to recreate the query. I need to ETL the results ...
5
votes
1answer
6k views

Best way to increase no of partition in SQL Server Table

I am maintaining a database with a big big table, which portioned for each month. There is already tens of partition there but its till January 2013, So its time to create new partitions. Can anyone ...
3
votes
4answers
5k views

query processor ran out of internal resources and could not produce a query plan

This is showing up in the logs several times a night. How do I find the query causing the issue? SQL Server 2008 R2 Sp1.
4
votes
1answer
238 views

250 billion rows - computing interconnectedness on a really huge scale

I have a table of approximately 700k rows, each identified by a unique itemnumber. Each row/item can be associated with any of the other rows/items in the table by calculating a single numerical ...
3
votes
2answers
476 views

Backup SQL DataBase to C$

Another simple SQL backup question from me.... I've got a SQL Server database running on SSMS 2008 and am trying to configure some backup jobs. I am now seemingly fine with backing up and restoring ...
1
vote
1answer
5k views

Connecting to database using Toad in windows

I am trying to connect to a database using Toad on windows 7. But I am not able to connect because I dont have SQLNET Editor, TNSNames Editor and LDAP Editor installed. Please help me with this. I ...
2
votes
2answers
2k views

SQL Server 2008 R2 DDL Trigger to create DML Triggers on all new user tables

As the title says I would like to create a DDL trigger which in turn creates DML triggers for most new tables. The trigger I have so far will create triggers on ALL tables, which is not what I'm ...
2
votes
2answers
124 views

HELP with SQL WHERE syntax

I have to pull out a range of entries of a database, all based around two pieces of data. In itself thats fine. An example of the data is: Record ID Tags 1 1423 Day, Sun, Warm 2 ...
0
votes
0answers
260 views

Problem with PIVOT [closed]

I am facing a small issue while using PIVOT table. My problem is as following. I have a single table with the procedures id and their steps and i want all the steps needs to be displayed in 5 column ...
1
vote
1answer
2k views

How to model entities in an entity relationship diagram?

I'm a beginner Database designer and wondering if anyone can help me understand this. Let's say we want to design a system that has, among other entities, an entity called Vehicle, and another one ...
2
votes
1answer
98 views

Selecting values that are not associated with one side of a many to many relationship

I have a junction table between categories and values that indicate which categories are associated with which each value. I would like to find the values that are not associated with each category. ...
1
vote
1answer
2k views

SQL query to get the syntax of an existing table

Is there any SQL query to get the syntax of an existing table in my MySql database? Intent: Iam in the early stages of working with databases and I am using the MySql workbench for creating tables ...
2
votes
2answers
1k views

Should I use SQL or NoSQL for this specific design (surveys)?

Building a small web application to create satisfaction surveys with a default list of questions and responses. The application is for a small audience of people (5,000 max) with surveys sent out when ...
0
votes
1answer
272 views

Oracle scheduler max_jobs_Slave_processes is set to null, how many will be allocated?

Executing the following query against my database: select value from dba_scheduler_global_attribute where attribute_name='MAX_JOB_SLAVE_PROCESSES'; yields a value of NULL. My question is what ...
7
votes
3answers
3k views

Query performance degrades with time and use

I have been fighting a problem for several weeks now where the performance of my SQL Server queries degrade over a few days of use. In addition every few days, a query will simply not return from my ...
1
vote
1answer
989 views

How to alter primary key constraint for 2 columns in same table in SQL Server?

In SQL Server, I already created a table called tblDatabase with some columns. I want to add a primary key on the two columns DatabaseName and Servername. I tried to add: ALTER TABLE tblDatabase ...
2
votes
5answers
404 views

How define select, if bridge pair may have 3 players?

Normaly a pair has 2 players, but if there is an odd number of players at the club, one 'pair' has 3 players. Table, members: pair, player_id 1 1 1 2 2 3 2 4 3 5 3 6 4 7 4 8 5 9 5 10 6 11 6 12 6 ...
5
votes
2answers
830 views

MySQL check duplicate with group by using wildcard?

+----+--------------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+--------------+-----+-----------+----------+ | 1 | Ramesh Olive | 32 | ...
5
votes
2answers
1k views

Select the newest data (inner join + group by maybe)

I think I would need some help. To simplify my problem, lets assume that I have two tables. First called "topics" with this structure: idTopic author deleted 1 123 false 2 132 ...
2
votes
2answers
2k views

How to insert into junction table using triggers

Sorry in advance if this is "basic SQL." I wanted to know how to update my junction tables automatically. For example, these are my tables. Artist and Song are base tables and SongArtist is the ...
5
votes
2answers
910 views

Introducing an 'age' category in a patients database

I want to do the following in my DB related to patients: when patient inserts his birthday it shows his age category as: infants, children, adult, elderly... etc. to help me classify my patients how ...
1
vote
1answer
2k views

Query to compare 2 table results and filter extra records found in a table

I have two queries that I want to compare and get the extra rows found in in the first query that are not in the second plus 1. I need the DocEntryCN to perform some work. E.g. Query 1 SELECT ...
0
votes
3answers
3k views

ORA-00905: missing keyword when trying to create a synonym

I have a problem when I try to create a synonym like this: CREATE SYNONYM B2H.BILLING_USER_ACTIVITY@PACOMNET FOR B2H.BILLING_USER_ACTIVITY@PACOMNET.US.ORACLE.COM; Why does this happen and how can ...
1
vote
2answers
71 views

How much work actually happens on SSMS and how much on the server itself?

Suppose I run the following query: declare @i integer set @i=1 while @i < 2000000 BEGIN insert into t1 (ID,DESC,date) values (@i%200,concat(NEWID(),newid()),GETDATE()) set @i = @i +1 END Is the ...
1
vote
1answer
2k views

What are the criteria for a sql staying in the V$SQLAREA?

I needed to pick up details from a SQL result but 5 minutes after running the SQL it was no longer under v$sql and V$sqlarea. What are the criteria for Oracle to keep the query in those views?
5
votes
2answers
2k views

Large Fact table and partitioning key dilemma

I have fairly large fact table (2 billion records, approx 120 GB). This table is not partitioned and the queries are very slow to respond. I am planning to partition the table and indexes. The table ...
3
votes
3answers
8k views

Many-to-one Subselection in single query

I have two tables with a foreign key from T1->T2, in a one-to-many relationship. That is, 1 tuple in table T1 is associated with 0..N tuples in T2. To create a simple example, lets say T1 is Cars, ...
1
vote
2answers
557 views

How to isolate general Oracle 10g issues through SQL

Background: I've been working on a performance analysis on a system with major performance issues on a Oracle 10g server. Much of the SQL is badly written, or at least not written with performance in ...
3
votes
2answers
9k views

GROUP BY two columns

I want to count two columns, but need to get the results of first column in one row. SELECT country, COUNT(*) FROM table1 GROUP BY country, type This query gives me country type COUNT(*) ...
1
vote
0answers
86 views

Mysql: Large Query with subqueries optimization

Any query optimization gurus in the house?? Anyway, I am trying to attack this on two fronts, one being to code a way around it but secondly, I'm thinking some real optimization might help in the ...
1
vote
2answers
547 views

Mysql: Large Query with subqueries optimization

Any query optimization gurus in the house?? Anyway, I trying to attach this on two fronts, one I'm trying to code a way around it but secondly, I'm thinking some real optimization might help in the ...
0
votes
2answers
90 views

what is the best choice from those two tables?

what is the best choice from those tables?? table A : keep DB simple with lower number of tables ... but i will find difficulties if i wanted to extract certain data from column of doses coz it's ...
0
votes
1answer
1k views

Is there a way to list all tables and when they were last accessed?

I'm wondering if there's a way in SQL Server to make a list of all tables and when they were last accessed (select / update / insert or delete) either by a user, view or stored procedure?
4
votes
1answer
728 views

Query to check where database growth was?

I have a database that grew very significantly overnight and I'd like to find out why. I've restored yesterdays backup to another location. Is there a query I can run to query the size of tables or ...
9
votes
2answers
22k views

SQL Server backup failing. Error: 3041, Severity: 16, State: 1

First of all, I'm very new at stackexchange, so please bear with me. I'm running a SQL Server 9.0.4060. My problem is: my SQL Server backup keeps failing on a lot of the databases. The Maintenance ...
3
votes
2answers
2k views

MySQL - Create a function from another function

I want to create a function same like my old functions. When I create a table, I can use `AS to other table as follows: CREATE TABLE dummy2 AS SELECT * FROM dummy1; Can I do the same thing with ...
1
vote
1answer
152 views

a question in tables relations

Table A is linked to Table B , and Table B is linked to Table C using primary and foreign keys My questions: Should i link Table A to Table C too?? Or Table B can act as intermediate link between ...
3
votes
1answer
132 views

Help to create indexes to improve query performance and reduce report generation time

The database in SQL Server 2008 has these tables below - the main table being the Master: Master A_ID (PK) SYSSerialNumber Bootlog SequenceNumber FileName Cfg Unique(serialnumber,sequencenumber) ...
1
vote
2answers
353 views

Optimizing SSIS Packages

I have a SSIS package that loads data in an Access DB to tables on a SQL Server DB. Currently I double click the package and execute it. There are times when there is a lot of data to load. My ...
-2
votes
1answer
3k views

Which is the shortest way to find out if a float IS NULL or equals 0 in mysql [closed]

I want to type the least possible characters, to find out all columns in a table that are 0 or NULL In this example I explain it further: lets assume the table test CREATE TABLE IF NOT EXISTS `...
13
votes
2answers
7k views

Performance Tuning for Huge Table (SQL Server 2008 R2)

Background: I have a fact table in UAT Phase. Objective to load 5 yrs of data in Prod (expected size 400 Mn records). Currently it has only 2 years of data in Test. Table Features: No of ...
0
votes
1answer
150 views

MySQL query optimization

I'm quite new to big MySQL queries (I used to extract raw data from tables then manipulate them with PHP) so I'd like to know if my query below is too "heavy" or slow for some reason (it seems to be ...
0
votes
1answer
804 views

Average, between specific date/time, of the difference between current field and previous field

Im trying to calc the average, between a specific date/time, of the difference between the current field and the previous field. At present ive got a query which displays this but im unsure how i can ...
0
votes
3answers
646 views

Pivoting row contents to produce one dimensional result

I have a table with a varchar column containing pipe-delimited values For example: Row 1 Column 1 = a|b|e|gg|foo Row 2 Column 1 = oV|foo|do Row 3 Column 1 = boop How can I query this to return ...
0
votes
1answer
1k views

Massive update to Clob fields oracle 10g

I need to remove a string of characters from 2000+ clobs fields in oracle 10g. How would I do this in PL/SQL? For example, in table t1 there is a clob field called VBase that needs to have the ...
1
vote
1answer
1k views

How to create sql query results in columns from rows?

From the following tables: store(store_id, ...) {1 ..., 2 ...} two stores film(film_id, ...) inventory(inventory_id, film_id, store_id) rental(rental_id, rental_date, inventory_id, return_date, ...) ...
4
votes
3answers
36k views

Single sql query to select a record from parent table only if all it's records in child table has values

I've Table1 and Table2, Table1's PK is the FK of Table2. Now i need to perform a select from Table1 if and only if all the records in Table2 corresponding to a Table1 record has values (if any field ...
3
votes
1answer
12k views

Joining 3 tables using SQL Query?

I'm having problems joining three tables. It works well when I use the same query for joining two tables (after removing LEFT OUTER JOIN C ON B.No = C.No). However, it doesn't work when I use three ...
5
votes
1answer
553 views

SQL Server 2008 upgrade advisor tool

We upgraded to SQL Server 2008 from SQL Server 2000. But due to some legacy scripts/queries in our health care application, that run only in 2000, we had to run 2008 version on backwards compatibility ...