An acronym for "Structured Query Language", 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 access privileges.
-1
votes
0answers
31 views
which database suitable for huge inserts for analytics system [closed]
I want to develop a analytics system for very high traffic web sites, that records many behaviors of users. I want to choose a proper database for doing it well.
This database must suitable for huge ...
0
votes
1answer
26 views
SQL Sever - Remove Duplicates in order
When removing duplicate rows, using the below stated query, how would I go about forcing which of the found duplicates to remove?
I am using this query, from this tutorial:
DELETE FROM ...
9
votes
3answers
243 views
Updating a table with millions of records, its been 4 days
I am currently updating a table with millions of records, its been 4 days and query is still executing.
I checked the activity monitor its shows that query is running.
In event log there is no ...
3
votes
3answers
85 views
Are individual queries faster than joins?
Conceptual question: Are individual queries faster than joins, or: Should I try to squeeze every info I want on the client side into one SELECT statement or just use as many as seems convenient?
...
0
votes
0answers
11 views
Longest prefix search in Oracle
I have a list of phone number prefixes defined for large number of zones (in query defined by gvcode and cgi).
I need to efficiently find a longest prefix that matches given number PHONE_NR.
I use ...
-2
votes
0answers
26 views
SQLYzer experience [closed]
recently found and installed the 30 days free trial version of Precise SQLYzer (just google it and you'll find it easily). I tried it on my UAT env and so far I find it really great. I'm wondering ...
2
votes
1answer
39 views
SQL Windowing function to create a running total
I need some help with windowing functions.
I have been playing around with sql 2012 windowing functions recently. I know that you can calculate the sum within a window and the running total within a ...
2
votes
0answers
32 views
Which database could handle storage of billions/trillions of records? [duplicate]
We are looking at developing a tool to capture and analyze netflow data, of which we gather tremendous amounts of. Each day we capture about ~1.4 billion flow records which would look like this in ...
0
votes
2answers
30 views
Join on different types
In a database I have two tables:
The first has a field named taxonomy_id that is an integer
The latter has a field named ID that is a character varying
The two tables are related: if it exists a ...
1
vote
2answers
52 views
Remove scientific notation from an XML column
I have a XML column as shown below:
<Root>
<Row>
<Rowid>1</Rowid>
<date>2013-05-06</date>
<Balance>1.0002E7</Balance>
</Row>
...
0
votes
1answer
27 views
Upload (someone else's data) to Postgres, SQL Server
I work for a company that does program evaluations for state agencies. Thus, I frequently work with someone else's data. They send me the data via sftp:/, mail, etc. and I load it into either SQL ...
-2
votes
0answers
31 views
How can I group sql rows by hours? [closed]
My table:
tbl_questions =>
qid
question
detail
mcid
cid
uid
answercount
dateposted
status
showname
emailnotify
question_type_id
I would like to get all rows group by hours. {dateposted = Y-m-d ...
1
vote
0answers
48 views
Search for multiple keywords related to separate columns on a single table
Imagine I have a table with the schema shown. Each of the columns represent a searchable collection that I would like to be picked up in the search. I am looking for a method that would derive the ...
2
votes
4answers
71 views
When should I use the shrink option
I have seen a lot of blogs stating that shrinking is not a good habit as it will reduce the performance of the system. I agree with all those things it will lead to side effects like fragmentation, ...
0
votes
2answers
41 views
How to forcibly flush the undo log/tablespace of UNDOTBS1
I'm looking for a way to flush the undo logs so that the UNDOTBS1 doesn't give me an error saying that my tablespace isn't large enough. I tried using a commit, but without success, it still gives me ...
0
votes
0answers
17 views
Where condition execution sequence in MySQL [duplicate]
I am using MySQL 5.0.88. I have a query like
select DISTINCT party.id FROM party LEFT join party_identifier AS pi ON pi.party_id = party.id WHERE (pi.source_note=? AND first_name LIKE ? OR ...
1
vote
1answer
25 views
Postgres 9.2 select multiple specific rows in one query
I have a table with three columns,
Id Serial.
Value real.
timein Timestamp.
I want to select the values based on a range of days ie. from two days ago until now. The table may contain one or two ...
4
votes
1answer
64 views
How to get a row_number to have the behavior of dense_rank
I have a stored procedure will be used to page through data. One of the requirements of the procedure is to have a parameter that will be used to sort two columns of data, the sorting should be used ...
-1
votes
0answers
33 views
How can I delete duplications with one query? [closed]
Is there any way to delete duplications with one, or only two queries? I'm not good with SQL.
Now I'm trying this solution:
$le = mysql_query('SELECT * FROM tbl_answers');
while($i = ...
2
votes
1answer
32 views
How can I include more query in one query?
These are my tables:
tbl_answers =>
aid
qid
answer
uid
dateposted
emailnotify
namedisplay
status
isbestanswer
tbl_questions =>
qid
question
...
0
votes
1answer
20 views
Separate table by often changed / not often changed data?
I have a application which querys different gameservers, and stores for example if the server is online, and how many players are connected. But of course there is also data which is changed rarely.
...
0
votes
0answers
29 views
Simple SQL statement [migrated]
I've been over-thinking this too much. Let's say I have a table TEST(refnum VARCHAR(5))
|refnum|
--------
| 12345|
| 56873|
| 63423|
| 12345|
| 56873|
| 12345|
I ...
2
votes
2answers
25 views
MySQL many to many relation
Sorry is this seems a stupid question, but I have a table of server addresses and a second table with a list of hardware devices which use the addresses, it is possible for multiple devices to use the ...
3
votes
1answer
44 views
Convert SQL Server 2012 SSIS package to 2008
I've created a few SSIS packages in SQL Server 2012 that I need to run on SQL Server 2008. I've been told that it is impossible in their current state.
Is there a way to convert the 2012 packages to ...
3
votes
6answers
107 views
Backup very large table
I have to update certain values of a large table (for the sake of a presumed example, it is called 'Resource' and it is over 5M rows) and thus I have to make a backup before performing the changes. We ...
1
vote
6answers
48 views
Best practices with large amount of data
I'm building a solution and I have a question.
All my data is currently stored in a unique table. Each row of data is associated with a type.
My question is, what's the best practice for defining a ...
6
votes
2answers
72 views
Is there any way to trace optimizer's work in MySQL?
Just like the MEMO structure in SQL Server which is kind of a "paper trail" of steps the optimizer takes in optimizing the query. Is there anything in MySQL through which I can get the information ...
4
votes
2answers
72 views
Should I use a composite or single-column index?
I have the following columns in my database table (Medicines).
ID bigint,
MedicineName nvarchar(50),
BrandName nvarchar(50),
MedicineCode nvarchar(20),
and price,quantity.
I am making a stored ...
-1
votes
1answer
43 views
Adding a column at a specific position using DBMS_REDEFINITION
Can anybody tell me how I can add a column to table at a specific position using the DBMS_REDEFINITION package from Oracle?
0
votes
0answers
30 views
Match two large tables finding similar data [closed]
I have two tables in MySQL. Table 1 contains much data, but Table 2 contains huge data.
Here's the code I implement using Python
import MySQLdb
db = MySQLdb.connect(host = "localhost", user = ...
1
vote
1answer
25 views
Moving postgresql data directory
I moved postgresql's data directory by following the following steps:
Stop postgres
cp -a source_data_directory destination_data_directory
export PGDATA=destination_data_directory
Changing data ...
-5
votes
0answers
45 views
Query for Oracle database [closed]
I have a problem in writing a Database Query
Problem: I have the below fields in my table Fields: Primary_ID(PK) ,E_ID, Bank, REQUEST_STATUS, START_DATE, STATUS
Data: REQUEST_STATUS may contains ...
-4
votes
0answers
42 views
oracle database query [closed]
I have a problem in writing a Database Query
Problem: I have the below fields in my table Fields: Primary_ID(PK) ,E_ID, Bank, REQUEST_STATUS, START_DATE, STATUS
Data: REQUEST_STATUS may contains ...
1
vote
0answers
28 views
How to Change location of postgres cluster and database within the same machine? [duplicate]
I have my present database cluster of postgres at /mnt/my_hard_drive which I want to change to /home/myfolder. I also want to move all my databases present in the present cluster to /home/myfolder. Is ...
1
vote
1answer
25 views
Insert Row With Foreign Key Equal to ID - Circular Reference
In MySQL database I have an events table which has a circular reference to itself with the following columns.
id (PK) | user_id (FK) | event_id (FK) | title
Each new row inserted has a circular ...
0
votes
1answer
32 views
“relation does not exist” trying to import mysql dump into postgres
environment:
ubuntu 10.04
mysql server 5.1.69
postgres 9.2
Here's the sequence of steps:
created a new postgres database, myDatabase
executed this command: mysqldump -u root -p ...
0
votes
2answers
28 views
Query WHERE clause and JOIN
I'm having an issue using a WHERE clause and JOIN.
SELECT * FROM `CallDetailRecord`
WHERE `StartTime` >=1357102799000
AND `StartTime` <=1357880399000
JOIN `CallEvent` ON `EventID` = ...
0
votes
2answers
25 views
copy package from schema B to schema C
I am in the next situation: I am using oracle 11g. I am connected to an user, lets say schema1, where are a lot of permissions. I want to create a script which will copy the Package1 (and its body) ...
4
votes
1answer
442 views
CTE memory space
How do CTE memory space work? When you have complex SQL statements executing with the CTE and you get unusually large result set, can it increase the logical reads of the query?
I have a CTE that is ...
-1
votes
1answer
51 views
Single quote issue
I have to update a column where I am giving query which is
Update Table
Set col = '[189] IS NOT NULL and [189] <> '''
Where colid= 198
But it's giving output:- [189] IS NOT NULL and ...
0
votes
2answers
68 views
Use an Alias in Where Clause Subquery
I need to show some fields from another table in Oracle. Here is my query:
SELECT
ANGGARAN.SIMPEG_PEGAWAI.ID_PEGAWAI AS KODE,
ANGGARAN.SIMPEG_PEGAWAI.NAMA,
ANGGARAN.SIMPEG_PEGAWAI.NIP,
...
-1
votes
1answer
25 views
checking a folder whether required file is present at a regular interval [closed]
i have 4 folder hierarchy in a following format :
Hourly Daily Weekly Monthly.
In every folder i have files which is coming in a frequency based on their folder's name.
Now , i have ...
0
votes
2answers
59 views
Running a query within a scheduled job
I am looking at running the following query within a scheduled job weekly:
Select TOP 10
s.database_name,
m.physical_device_name,
CAST(DATEDIFF(second, ...
0
votes
1answer
33 views
String replace using concatenated strings from various columns
I'd like to remove a substring in a column via update statement. The substring to replace consists of multiple strings from other different columns but in strict order.
The specification says:
...
0
votes
4answers
31 views
Are all matching indexes (indices) searched for a query?
If I have multiple indexes that include a common attribute - will each index be searched upon a query involving this attribute? Or is only one index searched?
-1
votes
1answer
49 views
populate table header and data in another table column [closed]
I have 2 tables: Departments and Employees, where Department_Id is primary key in the departments table and a foreign key in Employees table.
Departments Table:
Department_Id Department_Name
10 ...
0
votes
2answers
20 views
Partitioning MySQL for “expired” transactions to improve performance
I am dealing with an existing application that uses the database as a sort of transaction log in several cases, for example orders or payments. These tables are large (20 - 60 million rows) and poorly ...
1
vote
1answer
37 views
Mysql: Schema/Query Performance Approach for aggregated mailbox folders
I am about to code a messaging system where users can write messages to other users. User can create custom inbox folders for sorting the messages they receive, however, every user has 2 main inboxes: ...
-2
votes
3answers
78 views
Left Join not giving desired result
I have two Datasets
FEES PAYMENTS
CrsCode InstNo FEE Regno CRSCODE Instno Payment
CA1 -2 100 R1 CA1 -2 100
CA1 -1 200 ...
-1
votes
0answers
28 views
Using If statements in stored procedure breaks it [migrated]
This is my stored procedure:
USE [Hires_new]
GO
/****** Object: StoredProcedure [dbo].[ps_selectNewHireWorkPeriodsSQL] Script Date: 05/14/2013 12:59:28 ******/
SET ANSI_NULLS ON
GO
SET ...
