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.
0
votes
0answers
18 views
what are the ways to use SYSDATE and timestamp pattern? [on hold]
what are the ways to use SYSDATE and timestamp pattern? I would like to know the importance of sysdate and timestamp in the database query language. Database experts kindly help me also as beginner to ...
2
votes
1answer
54 views
Combine - Select Into, with Insert Into Select
I've read many explainations like this one here that say "Select Into ..." is to create new tables and "Insert Into ..." is to append to existing tables.
I'm automating a coworker's sql scripts. ...
0
votes
1answer
65 views
SQL Server select with regex
Can we use regular expression to select the item from database?
The table item is like below
Table column|name|
10.01.02 | a |
100.2.03 | b |
1021.10.04 | c |
Now my problem is that i ...
1
vote
1answer
20 views
Data modeling membership and profiles
Anyone have an idea on how to solve this issue.
Im modeling my membership and user profile DB and run into one issue I have to solve.
I have this layout of tables.
Membership (where all basic info ...
3
votes
2answers
52 views
How do I deal with items that I need to repeat monthly?
I have a table where users will enter transactions on a weekly basis, these transactions will either be single items or will be recurring i.e. rent would be recurring and should be the same for every ...
0
votes
0answers
9 views
MySQL Replication fails - commands received in wrong order?
The other day, I had this issue with one of our MySQL 5.1.60 master and 5.1.61 slave setups:
Last_Error: Error 'Can't create table 'foobar.#sql-4b87_2' (errno: 150)' on query.
Default database: ...
-1
votes
1answer
27 views
PostgreSQL indeterminate ordering of results [on hold]
When I have this query:
SELECT DISTINCT "spree_products".id FROM "spree_products"
INNER JOIN "spree_variants"
ON "spree_variants"."product_id" = "spree_products"."id"
AND ...
2
votes
1answer
41 views
Getting the most out of SQL Server with a million rows a day
I have a windows service that is inserting 1000 rows at 1 minute intervals to a SQL Server 2008 Standard database table that looks like:
id, datetime, key_id, value
key_id is a foreign key to ...
0
votes
4answers
97 views
Pick one single row each time from either table?
I will run a query and get a list of aID and based on that I will try to match below tables.For each aID I just want one record either from tblGAlert or tblEAlert based on gDateTime and eDateTime ...
0
votes
1answer
79 views
Why would running a SQL query overnight break my database for the following day?
I apologise in advance if this is a duplicate question (which I bet it will be). I had a good search through and found similar questions but nothing that seemed an exact match.
I wrote a report that ...
0
votes
1answer
31 views
SELECT to only see Offsetting Transactions in table?
I have a temp table like this
I have been playing with it but I don't know how to get same PortCode,Cusip combo to give me only offsetting transactions (Buy,Sells) not (Purchase,Purchase) combos.
...
2
votes
0answers
30 views
Did the Change Tracking internals change from SQL Server 2008 to 2012?
In trouble shooting an issue with synchronizing disconnected devices with a central database server, we are experiencing a problem after upgrading to SQL Server 2012 on the server. It appears that the ...
2
votes
2answers
76 views
SQL Server 2008 R2 : A Possible indexing strategy for the given schema?
We have these four tables:
Store (
row bigint,
id uniqueidentifier,
name varchar
)
Products (
row bigint,
id uniqueidentifier,
storeID uniqueidentifier,
productname ...
2
votes
1answer
48 views
Postgres nested hstore select query
I have a carts table with items hstore column. An example entry in this column is:
carts.items row#1 = {
"1614" => {:quantity=>"100", :price_cents=>1655},
...
0
votes
0answers
18 views
MySQL multiple joins, not getting one value, but no error [on hold]
I apologize if this is a noob question. I have already tried google without much success, so I'm trying this.
I have the below query, which is working fine for the most part, but I am not getting a ...
2
votes
1answer
40 views
How to go about modeling the main object of relationship?
I'm using MySQL and MyISAM tables.
I have a bunch of objects that I want to group together
1 object belongs to 1 and only 1 group
1 group can have many objects
1 group has 1 required main object ...
3
votes
2answers
95 views
Efficiently query MAX over multiple ranges
When performing a MIN() or MAX() over a single range covered by an appropriately sorted index, SQL Server does a TOP() and so returns the value after fetching just one row. When the search criteria ...
3
votes
1answer
40 views
Dynamic file name for file import
I'm trying to figure out a way that I can pass a filename into a stored procedure that can import a file. However, after setting up the SQL dynamically, the object can't be found.
DECLARE @fileName ...
0
votes
1answer
65 views
Improve performance with the WHERE NOT IN sub-select clause
In the following query, I have to count transactions for each customer. [EDIT]However, I have to exclude from the result set entirely, customers that have a transaction older than one year.
...
0
votes
0answers
3 views
Reteriving point datatyte in Javaservlet
I have crated a Table With Location as 'Point' data-type and successfully inserted data by using Location = GeomFromText( ' POINT(31.5 42.2) ' )
also
SELECT AsText(Location) FROM consumer_details ...
2
votes
1answer
30 views
Need Help Executing a Remote Task from SQL Server Agent
There is a scheduled task running on a batch server via the Windows Task Scheduler that sometimes overlaps with other scheduled jobs in the SQL Server Agent causing CPU issues. We can solve this if ...
-1
votes
2answers
39 views
Oracle - Clause where in case when
all!
I want to know how wrote this code of SQL, I already trying but i get message error.
I wrote two way different this SQL. See you below
SELECT *
FROM tabela
WHERE coluna IN (CASE WHEN 1 = ...
1
vote
1answer
28 views
Stored procedure to validate xml content against dtd
I have a stored procedure which creates xml content and then is stored to a xml file. Is it possible to validate the generated xml content against a dtd file?
Thank you in advance!
1
vote
2answers
49 views
SQL Server db user mapped to windows login not in SQL Server logins
I am on a SQL Server 2008 where i login with DOMAIN\USER_A
On my database i can see this login is mapped to the user dbo of that database.
What i don't understand is that DOMAIN\USER_A is nowhere to ...
-2
votes
0answers
25 views
What is partition in database/table ? what exact changes it made to table? [closed]
I am a quite new in database related stuffs. I am practicing Voltdb, although I have hands on over MySQL and Neo4j NoSQL. but I never came across partitioning in sql database architecture (other than ...
2
votes
1answer
95 views
What is the difference between COUNT(*) and COUNT(*) OVER()
Take the following code example:
SELECT MaritalStatus,
COUNT(*) AS CountResult
COUNT(*) OVER() AS CountOverResult
FROM (schema).(table)
GROUP BY Marital Status
COUNT(*) ...
-1
votes
1answer
54 views
Need to learn SQL quickly [closed]
I am a 14 year freelance web and software developer. In the middle of a project, a client threw a need for a database to me. I need to learn/be able to use SQL very soon. What resources do you ...
-1
votes
0answers
48 views
Is there a logical way to visualise a join by looking at it quickly [closed]
As the question asks, is there a logical way of visualising a join in your head quickly. I always have trouble visualising the reason for a join from looking at the code, and have to go and visually ...
1
vote
1answer
42 views
how to create an incremental database copy in postgresql?
Virtual machine software like VirtualBox allow one to make incremental VM clones. That is data, once "touched" (opened writable), will be copied and stored in the incremental cache of the new clone.
...
1
vote
1answer
31 views
Get result of joining multiple tables as one row
I have these 2 tables:
table1:
id | name
---------
1 | john
2 | jack
table2:
id | profile_id | institution
-----------------------------
1 | 1 | SFU
2 | 1 | UBC
3 | 2 ...
1
vote
2answers
72 views
Optimize simple query using ORDER BY date and text
I have a query to a table in Postgres with an order based on a date field and a number field, this table has 1000000 records
The data types of the table are:
fcv_id = serial
fcv_fecha_comprobante = ...
2
votes
1answer
57 views
Correct database solution?
So I work for a smaller company and currently we need to do our data processing with a desktop solution. We're running into memory limitations and the overall solution in most people's eyes ...
1
vote
1answer
32 views
What is the difference between TABLESAMPLE(10) and TABLESAMPLE(10 PERCENT) in SQL SERVER 2012
My understanding of the use of TABLESAMPLE is that it returns the top n percent of rows in a result set where TABLESAMPLE(n)
So I wrote the following queries to just check (as I'm learning T-SQL)
...
1
vote
0answers
18 views
Proper Use of COUNT to count number of rows returned in a SELECT statement [migrated]
SELECT FirstName,
MiddleName,
LastName,
COUNT(*) AS 'Count'
FROM Person.Person TABLESAMPLE(10 PERCENT)
Gives me the error
Msg 8120, Level 16, State 1, Line 1 Column ...
0
votes
0answers
42 views
I am looking for a Job in Database field and I need advice from other DBAs [closed]
I am quite new to this website and I am from Australia.
I need advice from other what are the best steps to follow when it comes to people freshers like me who are looking for database/BI related ...
0
votes
1answer
35 views
Two types of data, so two type of databases?
For a social network site, I need to propose a DB. The application is written in Java & will be hosted on VPS(s) initially.
Broadly classified there is two type of data to be stored at backend:
...
-2
votes
0answers
45 views
Oracle to MS SQL Server 2008 Code Conversion Problems [closed]
CREATE OR REPLACE FUNCTION CHI_X2 (a1 in number, b1 in number, a2 in number, b2 in number)
RETURN NUMBER IS
DECLARE @tr1 INT;
DECLARE @tr2 INT;
DECLARE @tc1 INT;
...
0
votes
1answer
25 views
Redundant transpose. Case is not enough to solve my problem
I have db problem in transposing rows to columns. I am half way through the result but getting redundant data. My table:
EMP_ID EMP_NAME SAL_PAID
01 ABC JAN
01 ABC FEB
01 ...
1
vote
3answers
47 views
Which of these two methods is standard when creating a 1 to many database relationship?
If I have a customer that can have many addresses, I can create an Address table with columns Street, Town etc. and CustomerId. Then I can insert multiple records to have multiple addresses per ...
1
vote
2answers
77 views
How to make SSMS upper case keywords
I recently started using Management Studio 2012. When using MySQL Workbench, a handy feature was that I could stay all in lower case and any reserved word (like SELECT, INSERT) would convert to upper ...
1
vote
1answer
59 views
Why does Oracle SQL*Plus consider two queries executed together on the command line as a syntax error?
Consider:
SQL> select sysdate from dual;
SYSDATE
-----------
22-Aug-13 0
SQL> select sysdate from dual; select sysdate from dual;
select sysdate from dual; select sysdate from dual
...
0
votes
0answers
25 views
SET IDENTITY_INSERT equivalent in Access
In sql server i can explicitly insert value to a identity column of a table by using SET IDENTITY_INSERT ON . For an Access database i am using Autonumber column datatype. For this column i am not ...
0
votes
2answers
51 views
Difference between the given queries
I am required to retrieve the list of pno ( product_id ) for the products which were purchased by two customers. For this, I have been given two relations which are ( along with the attributes ) as ...
1
vote
2answers
44 views
Using max for each sub group does not seem to work.
I have a table that looks like this in Oracle 11g:
+----------------------------------------------------------+
| ACCT_NBR | MAIL_TY | ORGA | PERS | RUN_DATE |
...
0
votes
1answer
41 views
Create two columns from one, based on two different WHERE clauses
SELECT TOP 1000 [Value]
FROM [OnlineQnres].[dbo].[tmp_DataSets]
WHERE [VariableID] in ('1')
UNION ALL
SELECT TOP 1000 [Value]
FROM [OnlineQnres].[dbo].[tmp_oDataSets]
WHERE ...
6
votes
2answers
96 views
Database size - MDF too large?
I'm maintaining a SQL Server 2005 database which hosts approximately 2.9Tb of data (2 x 1.45Tb - I have a RAW schema and an ANALYSIS schema so basically two copies of the data ingested). The recovery ...
2
votes
2answers
71 views
SQL Server 2012 Availability Group issue
I need some assistance and some bandages for shooting myself in the foot. This is a DEV environment but I need to bring it up.
I am using SQL Server 2012 Enterprise on Windows 2008 R2. Somehow our ...
1
vote
4answers
68 views
Query to list all tables in a schema
I am using Rational Application Developer to run querys on a database. We are unable to locate anyone who has a list of the tables on the schema and so far the queries we have found to get a list of ...
1
vote
1answer
35 views
Passing column names dynamically to UNPIVOT
I have a table with following data
First Second Third Fourth Fifth Sixth
2013-08-20 2013-08-21 2013-08-22 2013-08-23 2013-08-24 2013-08-25
And ...
-1
votes
1answer
38 views
Problems working with decode function when there is more then one expression
I am using Oracle 11g. I have these two tables:
Table acct:
acctnbr || Name || Mailing address
000001 || Slater ||
000002 || Baker || Alt
000003 || Parke ||
Table addruse: ...
