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

learn more… | top users | synonyms (2)

1
vote
1answer
10k views

To drop a table, do I have to drop all foreign key constraints or can I just NOCHECK them?

I know you cannot drop a table that has active foreign key constraints. So I was wondering is it safe to: ALTER TABLE myTable NOCHECK CONSTRAINT all DROP TABLE myTable Rather than dropping the ...
2
votes
2answers
120 views

Index included columns

After running a fairly hefty query, the execution plan gave me a missing index suggestion which was of the form: (Timestamp) INCLUDE (CustomerID, EventID, ID, EmployeeID) Which seems to be a ...
4
votes
3answers
985 views

Full Text Search With PostgreSQL

i have a table with this rows: Stickers ------------------------------------------------------ ID | Title |Keywords (ts_vector) ------------------------------------------------------ ...
5
votes
2answers
80k views

Using SELECT in the WHERE clause of another SELECT

I have made a draft remote application on top of libpq for PostrgreSQL. It behaves well, but I have profiled the general functioning of the application. For each final business result that I produce, ...
1
vote
1answer
544 views

Set based solution to group multiple rows within a sequence

Using SQL Server 2008 and a set based solution, how could I manipulate the following data: GroupID Code StartSeq EndSeq StartDayNo EndDayNo 1755549 3506 0 0 59442 ...
3
votes
1answer
5k views

sql error 1241 operand should contain 1 column

This is my query select * from players where sport='football' and position='DEF' and pname!='Binoy Dalal' and pname not in (select ...
15
votes
2answers
12k views

How to keep history of SQL Server stored procedure revisions

Note: I am not asking about full version control. Is there any way automatically to keep a history of stored procedures on SQL Server. Similar to how Google Docs automatically keeps a history of ...
5
votes
1answer
3k views

Oracle extract slow function call from a WHERE clause

I have an oracle table in which dates are stored as the number of minutes since a specific date. It looks something like this: CREATE TABLE MY_TABLE ( SOMETHING NUMBER(15,1) NOT NULL, ...
0
votes
1answer
977 views

Database design of Ledger Account and Balance sheet generation

I am building an application which has deposit and loan accounts management, for which i have to develop ledger accounts and then generate balance sheet. Being new to database design, i am having a ...
3
votes
2answers
5k views

Query: All employee with sum of salary > 500

In my MYSQL Database COMPANY, I have a Table: Worker : mysql> DESC `Worker`; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
1
vote
2answers
13k views

Connect Error (2054) - mysql_old_password issue still not solved

i use php 5.4.3 and i want to connect to a MySQL DB remotely (php script resides on a different server than the db) with normal oop way: $mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME); ...
1
vote
1answer
269 views

Query for localization app

Here are my tables: mysql> SELECT * FROM locale; +-----------+---------+ | locale_id | value | +-----------+---------+ | 1 | English | | 2 | Deutch | | 3 | French | ...
4
votes
1answer
117 views

How to get the information of the optimizer of sql server?

The info like total optimization time, the time spent on each stage of optimization, the number of alternative plans and the cost of each alternative plan. Is there any way to get these info using sql ...
0
votes
0answers
791 views

RAND in T-sql function

Can any one tell why t-sql not allowing using RAND function in side a function SELECT CAST (RAND() * 10000000 as varchar(20)) the error is Invalid use of a side-effecting operator 'rand' ...
1
vote
1answer
5k views

SQL Query Killed, remains in Kill/Rollback with estimated time increasing

I killed a transaction and now it remains in Killed/Rollback status and whenever I run Kill 64 with statusonly I get this output: SPID 64: transaction rollback in progress. Estimated rollback ...
3
votes
3answers
1k views

How do I go about creating this SQL query?

I'm attending the free DB course at Stanford Online, and frankly one of the exercises gave me some trouble. I have a feeling that this should be horribly simple, so for a DBA, I'm obviously not very ...
1
vote
1answer
93 views

What is a proper way to handle a “sequence number” like field?

The fields of a trimmed-down version of a database I'm working with looks like this: Model Number, Model Name, Customer, Date, SeqNum The customer can purchase the exact same item on the same date ...
2
votes
2answers
446 views

INSERT succeeds but all inserted values become NULL

I tired to execute an insert directly from phpMyAdmin as follows: INSERT INTO oracle.PLAYLIST_MUSIC ( TID, ID, STATUS, CREATED_BY, ...
4
votes
1answer
501 views

Is there a standard way to replace codes with values from a lookup table for reporting or analytics?

Suppose I have a "main" table that was probably heavily normalized and consists largely of columns that merely contain codes that are lookups into other tables (they are probably foreign keys but feel ...
1
vote
2answers
2k views

Factoring out subselect (with clause) ends in ORA-00904

I am trying fo factor a repetitive subquery with with but getting ORA-00904 over and over again. This is my base query which works quite well: select tka.id, tka.name, txt.text as langname, ...
0
votes
0answers
4k views

0x84BB0001 Error when installing SQL Server 2012. Could not create a handshake [closed]

I have hopelessly attempted to install SQL server 2012 onto my 64 bit Windows 8 machine. The database engine always fails to install with error 0x84BB0001. Native reporting also fails, I reckon from ...
4
votes
2answers
9k views

query_cache_type = 0 vs query_cache_type = 1

Recently, I moved from standard MySQL to Percona, and used the Percona Wizard to generate my.cnf. However, I can see that, by default, the generated settings for my.cnf use query_cache_type = 0. ...
4
votes
2answers
717 views

Insert with 0 Writes?

I've been looking at some SQL Profiler traces recently, and I see something I don't understand. I'm running simple INSERT commands: INSERT INTO Foo (ForeignID, FileExtension, MimeType) VALUES (1, ...
0
votes
2answers
2k views

Indexing a longtext field

I would like to run an index on a longtext field using: CREATE INDEX post_meta ON wp_postmeta (meta_value(8)); There are currently ~1 million records. Questions: Will creating this index affect ...
1
vote
1answer
1k views

MySqli - select from db based on time difference with CURTIME() or SUBTIME()

I have a db with some customers data. There I store their last login time as datetime. I want to run a cron php script every min which will query mysqli for their last login datetime and if that was ...
0
votes
1answer
265 views

MySQL: select a row if that row is equal for every user

In my database I have 5 fix users, lets say user1, ..., user5. Furthermore I have a table month(name, finished, month, year) where name is VARCHAR (the name of one of the five users) and the ...
1
vote
3answers
295 views

How can I best combine 3 queries into 1?

I need the following queries combined into one query that returns the following from all results from the first query you see below (Right now I load the first query into an array in php then run two ...
5
votes
2answers
124 views

Algorithmic order of table indexes for table operations

I have not been able to find a clear indication of the impact table indexes have on the algorithmic order of table operations. I am not interested in the nuts & bolts of specific implementations; ...
0
votes
0answers
26 views

SQL services wont start due to incorrect drive letter [duplicate]

Possible Duplicate: SQL services wont start due to incorrect drive letter after windows restore SQL services wont start due to incorrect drive letter after windows restore Had a server ...
1
vote
1answer
534 views

How to delete and create user remotely on SQL Server 2000

My boss wants to make sure that no one runs a job accidentally so we are setting up lots of safeguards to ensure that no one sleepwalks, log into the system and run a job. We are updating the ...
1
vote
3answers
4k views

SQL services wont start due to incorrect drive letter after windows restore

SQL services wont start due to incorrect drive letter after windows restore Had a server crash, was successfully able to reinstall windows and restore the server SQL and all the necessary services ...
2
votes
1answer
457 views

Storing Different Products and Joining on table Names

I am setting up a database using mysql to store different products with different product types. For example I could have Books, Software and clothes etc. What I am think of is creating a parent ...
0
votes
1answer
6k views

Save output from multiple SQL SELECT commands to a file

Within a Postgres function I would like to run a number of SQL SELECT commands and append the output to the same variable. After all the SELECT commands have run this output should be written to a ...
0
votes
1answer
86 views

Is there any way to get recently inserted value in a table without using aggregate functions?

I tried to get the users list who are all accessed our application in past 6 months . In our table , we are storing login_time , user_id ..etc . It's a huge table approximately it will contain over 3 ...
4
votes
2answers
1k views

Backup Only Database Schema Everynight

I have an instance with 15 databases on SQL Server 2012. I will set up to backup system which will work every night. I want to backup only the schema because several databases are very big. I haven't ...
2
votes
3answers
3k views

SQL Subquery , One to Multiple rows

Following query I have written returns the result as expected, example 121350 rows. SELECT dbo.Articles_in_Consignment.Consignment_id, dbo.ORDER_D.Orders_boxsize, ...
0
votes
2answers
3k views

How to select row and column name where field is null?

I wanted to show row and column where field is null. i already see this example here but i don't understand xml code. my code is like this if exists (select * from Header where [name] is Null ...
1
vote
1answer
304 views

Delete word, its meanings, its meaning's example sentences from DB

I have three tables as below (simplified for demonstration): words ===== integer id text word meanings ======== integer id integer word_id text meaning examples ======== integer id integer ...
0
votes
2answers
2k views

ERROR: could not find array type for datatype information_schema.sql_identifier

I am trying to run the below sql command: SELECT ARRAY( SELECT column_name FROM information_schema.columns WHERE table_name ='gis_field_configuration_stage' ); and I get the below ...
1
vote
1answer
178 views

cloud computer to speed up sql big data queries?

i have been doing a lot of data mining at my new job. we collect TONS of data, and it's my job to visualize that sea of information in real time with statistic/heuristic analysis. we're currently ...
2
votes
1answer
215 views

Finding similar values in columns

For example I have a column id and column email. I would like to find all values from id and column that share similar pattern. let's say: in id 'column' I have Alexander and in 'email' column - ...
1
vote
1answer
421 views

MAX function and group by usage - postgresql

I am building a ranking algorithm using IMDB's formula: http://stackoverflow.com/questions/1411199/what-is-a-better-way-to-sort-by-a-5-star-rating/1411268#1411268 The formula for calculating the Top ...
4
votes
2answers
229 views

Performance on SQL Join

I have a query which takes ages to run mainly due to a join between 2 tables on a PK and FK. The Key is prefixed with 2 letters (which is always the same, RN) followed by 7 digits So RN1234567 for ...
1
vote
1answer
6k views

Retrieving data from inner join using LIMIT and OFFSET

These are my two tables: table1 qid[PK] |gid[PK] |abcd | xyz | date ---------------+---------+---------+------+------------ 00001 | qwe | 54 | a | 1994-11-29 00002 ...
5
votes
1answer
1k views

sql_slovak_cp1250_ci_as vs Slovak_CI_AS

Can someone explain to me what is the difference between collation sql_slovak_cp1250_ci_as vs Slovak_CI_AS? I have a db set to sql_slovak_cp1250_ci_as and shared hosting supports only Slovak_CI_AS. ...
1
vote
1answer
165 views

Setting up simple relational database structure

I'd like to set up simple MySQL database but I'm a bit confused about everything, so let me try to explain... I have 4 relevant tables, let's call them POS, POS_STRUCT, POS_STRUCT_DIV and MEMBERS. ...
2
votes
1answer
397 views

sorting groups of related rows by average values while keeping the groups together

I'm using PostgreSQL 8.4, but would like a standard SQL solution if possible. Consider the following table. corrmodel=# SELECT * from data limit 1; id | datasubgroup_id | datafile_id | ...
5
votes
3answers
3k views

SQL Server 2012 database backup successfully report but no backup file

I have created maintenance plan in SQL Server 2012. And every day, the maintenance plan should backup the database. There is no database backup file when I look in the folder where the backups must ...
-2
votes
2answers
26k views

Dynamic pivot: sum of sales per month

My table is like this: Sitecode Month Amount -------- ----- ------ XX Jan 1000 XX Jan 3000 XX Apr 3000 XX Apr 1000 What I want is, to ...
4
votes
1answer
1k views

Error while restoring database in SQL Server 2008 R2

This error occurs while I restore database from .bak file A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software ...