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)

5
votes
1answer
1k views

Mysql sub query returns more than 1 row

SELECT * FROM wp_posts WHERE ID IN ( (SELECT courses FROM wp_category WHERE CatID =401) OR (SELECT meta_value FROM wp_postmeta WHERE post_id IN (SELECT courses FROM wp_category WHERE CatID =401) ...
1
vote
2answers
729 views

SQL Partioning on date, how does it work for future records?

I am setting up a new system and I would like to partition the data for speed. Basically what I would like to do is have one database with all the latest information (e.g. last 3 months). then have ...
4
votes
1answer
130 views

According to the SQL92 spec, can I store the value 1 in a field where the precision = scale?

Is it SQL92 compliant to store the number 1 in a field that is defined as NUMERIC 3,3? What about DECIMAL 3,3? Does this mean that the precision is a floating decimal place or static that must ...
2
votes
1answer
770 views

MySQL: AVG of defined variable per month

I need help with the following code: SELECT DISTINCT userid , count( userid ) as login_count FROM ( SELECT DISTINCT userid , ...
3
votes
1answer
493 views

Retrieving queries when the hour of starting timestamp is less than the hour of ending timestamp

I have a CTE-based query in which I retrieve hourly intervals between two given timestamps. My query works as following: Getting start and end datetimes (let's say 07-13-2011 10:21:09 and 07-31-2011 ...
7
votes
3answers
5k views

How to Best Implement nearest neighbour search in mysql?

So, in short, What should be the data type of latitude and longitude? What SQL command I should call to get the first 100 nearest restaurants for example? Detail: I have 100k biz record each with ...
2
votes
1answer
91 views

Count total where owner_user_id = some_value and deleted_by_owner = 0 or to_user_id = some_value and deleted_by_to = 0

I didn't know if this is possible using just a query or if I need some procedural logic on this one. I have this table: CREATE TABLE `default_messages_users` ( `id` int(11) NOT NULL ...
2
votes
1answer
104 views

Question about new behavior of current connection combobox in SQL Server 2012

I have a question about the new behavior of the database selection combobox in SQL Server 2012 (Press Ctrl + U to move your focus to it). It seems that, unlike in SQL Server 2008 R2, clicking in the ...
1
vote
1answer
686 views

T-SQL, show a set of rows (specific column) as a field, for a better desc, please see the table relationship

I have been having a problem for quite a while now, that I can show with a sample of 5 tables (I'm actually dealing with more than 5!): -------------------------------- shipments ...
1
vote
2answers
386 views

Managing features for a real estate agency [duplicate]

I have a database table real_estate where the basic information is held (id, status, sku, address, building_type, sales_status) and now I want to attach features to that real_estate table. The data ...
0
votes
1answer
520 views

MySQL table with multiple values in one field

I'm building a database for a real estate company. The real estate properties are managed by sales people using a CodeIgniter website. How should I proceed with the database design for this table? ...
-1
votes
1answer
488 views

Very slow MySQL query. Index problem?

I have a query like this: SELECT BE.NAME as NAME, BE.PREVIEW_TEXT as PREVIEW_TEXT, BE.ID as ID, FPV1.VALUE as PROPERTY_COUNTRY_VALUE, FPV1.ID as PROPERTY_COUNTRY_VALUE_ID, ...
1
vote
2answers
249 views

Join on clustered tables shows greater cost than non-clustered tables?

I have created a cluster and have two tables emp and sep on the cluster. When I do the simple query... explain plan select * from emp_cluster join dep_cluster using (dno) the cost is 26. I ...
2
votes
2answers
3k views

Remove the last character from Variable String

I have a few variable data in the column "email". For example: roy2087@gmail.com123 sg123@yahoo.compori How do I get rid of the characters after .com? I used Replace(email,'.com%',gmail.com), ...
4
votes
2answers
1k views

WHERE clause is not filtering on LESS THAN and GREATER THAN or BETWEEN

I am trying to select MTR values that are between the values of 0 and 4. What am I doing wrong? SELECT DATEDIFF(M, TheDate, TheOtherDate) AS MTR FROM MyTable WHERE MyID = 2074163 AND MTR >= 0 ...
7
votes
1answer
636 views

Friends relations in MySQL

I'm developing a friendship relation in MySQL where friend relation is mutual. If A is friend of B, then B is friend of A. If one of the users end friendship then relation drops. I want to learn which ...
2
votes
3answers
95 views

transaction log after restore

Can you let me know why a transaction log would be a lot smaller in size after restoring from a full backup. Currently we have a massive log file on the live server, but after a restore to a test / ...
1
vote
1answer
1k views

Unable to add records to query in Access 2007

I've found this excellent resource that covers why a query might be read-only. I have a query that is not read only, yet I'm unable to add new records to it. There are three tables. Let's call them ...
2
votes
2answers
1k views

EXECUTE sp_executesql

i have a problem with EXECUTE sp_executesql and stuck with it. my query is as follows DECLARE @TABLE_NAME VARCHAR(5)='#T' DECLARE @TABLE_ID INTEGER=0 DECLARE @QRY NVARCHAR(MAX) ='' ...
2
votes
2answers
106 views

Retrieve job Details in SQL Server 2005

How do I retrieve information about a SQL Server Agent job scheduled against databases on SQL server 2005?
2
votes
1answer
499 views

Should I use left join to do my job in this scenario?

What I want to do is to find out the items which appeared more than once in this table: mysql> desc tables; +-----------------+---------------------+------+-----+---------+-------+ | Field ...
1
vote
1answer
1k views

Decode query truncates a date

I am not sure if I am looking at this problem the right way: I have a query like this on an Oracle database SELECT DECODE( TO_CHAR(MIN(DATE), 'YYYYMMDD') , '' , NULL ...
2
votes
1answer
503 views

Renaming the Data Sets in SSRS

What is the easier way to rename the Data Sets in completed SSRS report? I have created a report in BIDS 2008 and now for some reasons I have to rename the Data Set names, but it seems that renaming ...
4
votes
4answers
886 views

Shrink database is not shrinking as much as expected on a 1.5 TB database.

I'm working in an Enterprise system with a 1.5 terabyte database that is on a single filegroup, with the log in Simple Recovery Mode. Since we have many teams working on different projects in ...
1
vote
2answers
351 views

How to DELETE duplicate entries on a field

I have a huge table that has 1,990,546 users with numerous data. Sometimes I get email lists and dump them into this table also. I want to delete users with duplicate email addresses. However, I ...
1
vote
2answers
62 views

stack data into a single column

SELECT * would result in: course_id + group ----------------- 1 + SE123 1 + BA134 2 + SE213 3 + SE223 3 + BA344 4 + SE222 I would like it be like ...
1
vote
2answers
251 views

PostgreSQL Database Migration

How can I migrate data from on PostgreSQL database to another PostgreSQL database? Is there any technique to migrate data with SQL scripting?
2
votes
1answer
817 views

Oracle SQL Loader - load file where record is composed of “n” delimiters

How would you write a control file for Oracle SQL Loader that treats each record as being composed of 3 commas? I have read the documentation, however I cannot find how to handle this scenario, ...
1
vote
1answer
7k views

“Operand type clash” with DATE column

I enter the following command into the SQL Server Management Studio insert into testtable ([product_name], [price], [expire_date], [expire_time]) values ('Teapot', 10.00, 23/12/2012, '12:35:00') ...
0
votes
1answer
76 views

Redundant / partially duplicated data in a DB - how to structure?

I'm building a website for which a large amount of user generated data will be stored in a back end db and will be discoverable by all users. By its nature, a portion of the user entries will be ...
1
vote
1answer
1k views

Simple Car Rental database

I am trying to learn SQL development using SQL Server 2008 R2 Database Management Studio. I was trying to model a car rental company, where clients can rent cars, and I have to know which client has ...
3
votes
2answers
4k views

How to GROUP_CONCAT DISTINCT values in a MySQL query that gets number of records and min/max values?

I'm running MySQL 5.0.88 (Coldfusion8) I have a product search which I'm querying number-of-results as well as min/max prices/rebates across the product table. I also want to include a string of ...
1
vote
1answer
2k views

How to design a table for storing news items?

I have a script that can get result from news websites and it will run every hour. The php script will get the title and content. The things I want to store in MySQL are: title content ...
3
votes
2answers
792 views

Restarting One Database Not Entire Server

Is there anyway to restart one database in SQL Server instead of restarting the entire server? I have a task in perpetual rollback on one of my databases. I'd like to restart it without turning off ...
3
votes
1answer
312 views

How can I conditionally summarize multiple rows in Microsoft Access?

I have a table of data, in Microsoft Access 2003, listing multiple rows for each person. I want to conditionally summarize a subset of these rows for each person, for instance, if entry for columnA = ...
17
votes
5answers
5k views

SQL: What is slowing down INSERTs if not CPU or IO?

We have a database for a product that is write-heavy. We just bought a new server machine with a SSD to help. To our surprise, the insertions were not faster than on our old machine with much slower ...
1
vote
0answers
527 views

SELECT value from database over ODBC wont reach the software [closed]

SQL Server 2008 R2 latest updates. SQL server ODBC driver version 6.01.7601.17514 PS!!! native drivers are incompatible with the software so i cannot use them. I cannot edit or change the server ...
1
vote
0answers
256 views

unable to run multiple create index scripts in pgadmin query tool [closed]

I have created CREATE INDEX scripts using the indexdef column of pg_indexes in one database and tried to run the script on another database. When each line of the script is executed separately it ...
1
vote
1answer
141 views

Use of '@' '.' and 'public operator/keywords in Oracle database?

I got into a weird SQL query-patch which looks for @ . and public keywords. I was wondering what is the purpose of these operators/keywords? I suspect that it might be looking for SQL injection or ...
6
votes
2answers
2k views

Is DB2 more reliable and stable compared to PostgreSQL or MySQL?

Always i was using MySQL or PostgreSQL for my projects such as Web/Desktop apps. I had data loss few times and thanks to old backup i was always up and running. But for a recent project for Airport, ...
2
votes
1answer
91 views

Change-set SQL optimization

I'm trying to clean up a change-set query that returns rows from a table that have changed since a given time and also do not exist in a known list of already previously returned results. select ID, ...
3
votes
2answers
247 views

SQL Server Transaction Log Initial Data Load

What is the standard process when initially populating a database with 5GB+ to avoid a large transaction log? From my understanding the log file would jump to a large size after the initial load and ...
3
votes
1answer
268 views

SQL Server 2008 SP1 to SQL Server 2008 R2 upgrade

One of our project is planning to upgrade from SQL Server 2008 SP1 to SQL Server 2008 R2. I would like to know from this community the suggestions on: Is there any configuration or any change ...
-1
votes
1answer
220 views

Selecting just one table from Oracle view [closed]

I am creating a view to ease my manual selecting because I only want to fetch data from DB where it is meeting certain criteria which are present in several tables, so it is containing a lot of joins. ...
2
votes
4answers
4k views

PostgreSQL import CSV File causes Syntax Error

I'm attempting to import a CSV file into a database via the "COPY" command; however, I get the (what seems common) error that I need to be a superuser and that I should use "\copy" instead. However, ...
3
votes
1answer
58 views

INDEX on a table where I'm only filtering on a row_number()

I've got the following table (scaled down to a MWE): Fiddle here. CREATE TABLE weekly ( yearnum int, weeknum int, location varchar2(5), weekly_count int, weekly_sum int); INSERT INTO weekly ...
2
votes
3answers
289 views

selecting multiple instances of a record based on record lifespan over years

I have a table of items that have a origin date and a life span. I want to create a procedure that selects all of the items that will need to be replaced in a specific duration. I feel like there is a ...
1
vote
0answers
138 views

hStmt/cursor error caused by faulty data?

Could a busy hStmt and invalid cursor errors be caused by faulty data? ODBC and the software that connects to the database is correct and working - i have ran a test with a clean database (that has ...
3
votes
1answer
524 views

Connecting to an SQL server instance without using square brackets to avoid backslash

In our company we have an old piece of software which was written by our previous I.T manager but was never finished. The program connects to SQL server but we want it to point to a newer SQL server ...
10
votes
2answers
450 views

Postgres query plan of a UDF invocation written in pgpsql

It's possible when using the pgadmin or plsql to get a hold of a query plan for a sql statement executed via a UDF (using EXPLAIN). So, how do I get a hold of the query plan for a particular ...