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
vote
0answers
157 views
Is there a Postgres admin GUI that can execute a master file containing multiple sql files
I'm developing a really long script. I'd like to break the script into smaller, more manageable scripts and include each sql file in a master file, then just execute the master file.
example: ...
3
votes
3answers
74 views
Sum Up values in a query based on other information
I am trying to grab the sum of 2 columns if 1 column is the same. I currently have a record-set that looks like this:
I get these results by running this statement:
select distinct a.eventnum, ...
0
votes
1answer
131 views
SQL update set column = @ parameter
doeas anyone knows how to update a table where column = @parameter WHERE SELECT
Basically I have a table with the following values. Table name is UserAttribute
ID|Name |Properties|
...
1
vote
1answer
79 views
Query to determine a count by rolling interval
I'm looking at orders and trying to simulate some messageing that we are doing in the query so that Marketing can get a base line for how many responses they might expect if we changed the interval of ...
1
vote
1answer
45 views
Script to get duration
I am new to PostgreSQL. I am trying to write a query which can give me duration of the time. The fields are in the format yyyymmddhhmmss. In between we will get empty fields for Start_Time or ...
1
vote
1answer
83 views
How to Reorganize Microsoft Access Table
I have just taken over a retiring DBA's position and is responsible for migrating the current access database to SQL Server or PostgreSQL. Right now, the table structure is a mess. Instead of ...
2
votes
2answers
85 views
Representing SQL constraints on a table
I have this table:
CREATE TABLE `sold` (
`part_ID` INT(11) NOT NULL ,
`date` DATE NOT NULL ,
PRIMARY KEY (`part_ID`, `date`) ,
FOREIGN KEY (`part_ID` )
REFERENCES `part` (`part_ID` );
This ...
0
votes
1answer
40 views
What index to add in case of join by two optional fields
I have query similar to the one below, it joins two tables by field which can have NULL value. The result matchs the data if both tables have the same data or consider the condition as optional.
...
7
votes
1answer
236 views
Is there an execution difference between a JOIN condition and a WHERE condition?
Is there a performance difference between these two example queries?
Query 1:
select count(*)
from table1 a
join table2 b
on b.key_col=a.key_col
where b.tag = 'Y'
Query 2;
select ...
1
vote
1answer
75 views
Database Designing for a Ecomerce website
I am new to database designing. I am designing a database for an eCommerce website, there is a lot of products to be updated, but while designing for product specification table I do not understand ...
0
votes
3answers
18 views
Mysql - How to optimize retrival time in a table
I have query like this! which has 200 million Records in a single table.. I am using BTree Indexes in my table...
mysql> select COUNT(DISTINCT id) from [tablename] where [columname] >=3;
...
1
vote
3answers
137 views
How to model inheritance of two tables mysql
I have some tables where I store data and depending on the type of person (worker,civil) that did a job I want to store it a table 'event', now these guys rescue an animal (thereis a table animal).
...
1
vote
2answers
96 views
Search every column in every table in Sybase Database
I'm been taxed with the task of creating an application that pulls data from our Micros Point of Sales system. The POS is using a Sybase database running on one of our servers. The database schema is ...
5
votes
1answer
113 views
Does large table variables fill up logs in tempdb?
I'm running into an issue with a DBA who claims that table variables reside in ldf of the tempdb and when large amount of data is loaded into the table variable, the tempdb's logs fill up.
The DBA's ...
1
vote
0answers
38 views
Will Startup Procs Run Again After Database Recovery?
We have a number of stored procedures that run on startup via the sp_procoption startup:
exec sp_procoption 'ProcName', 'startup, 'true'
A customer's server started up with a very large database in ...
4
votes
1answer
64 views
Covering Index Question
I have the following query
Select Pt.PRODID, PT.INVENTREFID, Inv.ItemName, PT.ItemID, Configid, Pt.QTYSCHED,
PT.DLVDATE, Pt.CREATEDDATETIME, pt.SCHEDEND,
CASE Left(PT.INVENTREFID, 3)
WHEN ...
2
votes
2answers
115 views
Is this relation in 3NF?
I created a relation called Customer and defined as:
I just wanted to know if it is in 3NF?
My concern is regarding address, since customer may have the same address.
2
votes
2answers
77 views
SQL Max() revision number
This SQL returns a table with everything unique, which is what I want. However, if there are two (or more) event numbers, I need it to return the line with the highest rev_num for that eventnum.
I ...
1
vote
2answers
98 views
Does this query make sense?
I recently migrated a database from Sybase ASE to Microsoft SQL 2008r2.
One category of queries that was broken in transit is:
SELECT person_id
FROM leaveperiods
WHERE group_id=146
GROUP BY ...
2
votes
0answers
38 views
What is the equivalent of a “Certificate of Destruction” when using a cloud-hosted database?
We have a Rails application hosted by Heroku and using an Heroku Postgres database. Some of the information we store is considered to be sensitive by our users, and we've been asked if we can provide ...
4
votes
3answers
145 views
Backup strategies for zero data loss
I am working on one project and it has around 100K records. We can afford a downtime of 1 day but we need the data to be always available to us in latest state.
I've gone through two options:
1) ...
0
votes
2answers
153 views
Large time difference between two almost identical simple queries…why?
I'm trying to understand why is there so much difference in execution time and CPU usage between two simple queries that only differ in a computation. The queries are the following:
SELECT ...
1
vote
2answers
126 views
How to find DELETE and UPDATE statements that have been executed on a database?
I am trying to find out what delete and update statements have been executed on a certain database in a certain period of time.
I came across this following query, but it generates an error message:
...
0
votes
1answer
68 views
What is the maximum number of rows in a clustered index on datetime column?
I would like know what the max no of rows is in a clustered index (non-unique) on a datetime column table in SQL Server 2008R2.
0
votes
3answers
48 views
Using MySQL InnoDB as an Archive
My site has a main MySQL InnoDB table that it does most of its work on. New rows get inserted at a rate of 1 million per week, and rows older than a week gets moved over to an archive table on a daily ...
2
votes
2answers
321 views
Check if row exists
I need to write a query that will insert a row only once, even if the query is run multiple times. Being new to SQL (well new-ish), I did an if not exists(...) but a friend said he preferred deleting ...
0
votes
3answers
126 views
Select MaxDate where hh:mm:ss makes the difference
How to select the maximum date from a table where hh:mm:ss is selected?
My query is the following, and returns the latest date, but if there are different times recorded, the date will be returned ...
5
votes
2answers
358 views
SQL Server alter table to change TEXT to NVARCHAR(MAX) greatly increasing database size
I need to update a SQL Server database thats about 18GB in size to change a significant number of TEXT columns to NVARCHAR(MAX).
The problem I'm having is after executing all the alter table ...
0
votes
0answers
45 views
Database too slow after CHARSET Conversion
I have an Oracle database which runs on version 9.0.1 with WE8859P1 charset and i'm trying to migrate it to the version 9.2i which has the WE8MSWIN1252 charset.
The conversion is made without any ...
3
votes
1answer
36 views
Creative workaround for granting edit rights to individual jobs for a group?
We have two developers that say they can not function without full rights to a few jobs on our database server. They both want rights to the same jobs.
Given that it seems that MS doesn't seem to be ...
0
votes
0answers
28 views
How can i configure sql server 2012 to use multiple nic? [duplicate]
I've a server with 4 nics.
I want to use 2 nics to query db and 2 nics to retrieve results from db
I want they work in load balancing and still continue to work if one nick is down.
It is possible?
...
4
votes
1answer
140 views
Join To a @Table Variable is running ineficiently
Alright so I have a report sproc that was running incredibly slow. Customers were complaining that the report would not run so I started investigating exactly where in the sproc the problem was and I ...
0
votes
2answers
67 views
Need a way to query a table, and JOIN it with the TOP 1 related record from an other table
As a follow up to this question, I'm wondering if there is a better and/or more efficient way to gather the data in question.
As stated, this query does return me 95% of the data I need --
SELECT ...
0
votes
3answers
100 views
Fixing a query that returns failed job information
I' using a SQL Server 2008 Sp3 environment.
I have a query that returns information about failed jobs from the last 10 minutes.
If a job has two schedulers (for example - a job that runs every 10 ...
1
vote
2answers
113 views
MySQL Replication without stopping master
I am currently setting up a replication for my database one of the ways i am thinking of synchronizing the master and slave without write locking the master, is by first taking a log position then ...
5
votes
1answer
120 views
How to improve performance on PostgreSQL when using multiple concurrent processes?
Seeing query performance which is not satisfactory on our Python application, which runs several processes that use SQLAlchemy core to access a PostgreSQL 9.2 database. We may have around 100 - 200 ...
1
vote
1answer
60 views
Question about Oracle's interpretation of “host cpu utilization” in v$sysmetric view
I am studying the v$sysmetric view from Oracle and trying to understand what is Oracle's interpretation of "Host CPU Utilization"
It says in the metric_unit as % busy/(idle + busy), but what is busy? ...
0
votes
1answer
143 views
FETCH API_CURSOR causing open transaction in tempDB
A select statement run from Oracle 11gR1 to SQL Server 2005 using Gateway leaves an open transaction in tempdb. The user is a datareader in Database XYZ. Not sure why the open tran is in tempDB when ...
1
vote
0answers
163 views
Efficiently retrieve big amount of data based on date range
Table EVENTS
TimeStamp |O.Name |Location |Source |
2008-11-12 |Pretorian|Európe |England |
2009-12-24 |Lex Rosa |Európe |Italy |
2010-01-01 |Nasdaq |USA |Whasington |
...
1
vote
2answers
122 views
Only one NULL for one part of primary key
I have two date columns : "start date" and "finish date".
I want to make sure that there can be only one null value in the "finish date" column (which should be current state if it's NULL) for one ...
0
votes
1answer
58 views
Implementation of stack in MySQL
I had an abstract idea of implementing a stack in MySQL. Tried a lot of Google work but couldn't reach to any logical conclusion.
The Stack must be a collection of tables with the stack top pointing ...
3
votes
1answer
420 views
What queries is Oracle executing, how frequently, and time-taken?
I have a (Java) web-application, that of course executes many queries (selects, inserts, updates, deletes) to the Oracle Database.
I want to find out the following for last 7 days:
SQLQuery ...
1
vote
2answers
43 views
Need logic to collate data
I have a table in the below structure.
phone bigint
answer_time datetime
I should group all phone numbers, and its total number of attempts and mention its no:of time answered and no:of time not ...
1
vote
1answer
47 views
Scheduling a backup in SQL Server 2005
I am trying to schedule back up in SQL Server 2005 with management option. I am unable to find database name while scheduling a backup with full option. Database name is not over there in selected ...
0
votes
0answers
98 views
error in creating trigger in phpmyadmin
I have 4 tables:
event:
event_id(p.k) | uid | circle_id(f.k)
activity:
uid | performed_activity_id(f.k->event_id) | activity_type_id
follow:
follower_id | circle_id(f.k)
noiticication:
...
1
vote
1answer
36 views
Hard Parse on Non-Identical Statements?
Let's say I have this query :
SELECT X FROM Y
If I run it for the first time then it would do a hard parse.
After that, I run this query :
select x from y
With just a difference in alphabetical ...
0
votes
0answers
136 views
Postgres error ( XX000: invalid memory alloc request size 4294967290 )
I'm getting the following error in my postgres 8.4 database:
XX000: invalid memory alloc request size 4294967290
Operating System : Windows 7 64 bit
Memory : 8GB
Database Version: Postgres 8.4 ...
1
vote
0answers
382 views
The Cluster service failed to bring clustered service or application 'SQL Server (MSSQLSERVER)' completely online or offline
SQL Server service restarted/stopped, which cause the production database down n find this errors in System log.
ERROR5:The Cluster service failed to bring clustered service or
application 'SQL ...
1
vote
1answer
156 views
mysql query - how to delete duplicates based on highest int?
I'm running a reverse auction website and I need to delete the duplicate bids and keep only the highest amount . lastPriceBid_rounded is the "amount". The duplicate is defined by the userId.
...
7
votes
2answers
177 views
Different results rebuilding an index online and offline
I have a non-clustered, non-unique index on a foreign key column of type bigint. When I rebuild the index online, the average fragmentation drops to 3%, with 2 fragments, and 30 pages.
When I run ...
