DO NOT USE this for questions targeting Microsoft SQL Server, use the tag sql-server instead. "SQL" is an acronym for "Structured Query Language", it is used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. ...

learn more… | top users | synonyms (1)

0
votes
0answers
2 views

how can I count by week, month, and YTD with postgres?

under PGSQL 9.3 I am trying to run a query to summarize some data from table a: | Entry date-Date | Customer ID-charvar | received-Boolean | Processed-Boolean | finalized-Boolean | billed-Date | ...
0
votes
0answers
5 views

Long full scan hasn't returned but session is gone from V$SESSION

I have a query on a big table purposefully hinted with /*+ FULL() */ and uses partition pruning, and for a while the scans for each partition can be seen in V$SESSION_LONGOPS. After a while no more ...
7
votes
2answers
14k views

How do I find an object that doesn't seem to exist in the objects table?

I have an object called cot_ntn_pi_v. I was told this was a synonym. It doesn't appear in the all_synonyms table. It looks like a view or table but I can't find it in the all objects table. I can ...
1
vote
2answers
66 views

Minimum value on most recent date per account MS ACCESS SQL

I have a MS ACCESS DB table Riskscore with the three fields: CHACCTBS, CHRQDATE, CHSCORE and want to find the lowest CHSCORE on the most recent CHRQDATE per CHACCTBS where it is possible to have ...
1
vote
1answer
44 views

Update Table Query: Automatically handle “Enter Parameter Value” prompts?

EDIT: Here are two database Files that demonstrate an applied example of the problem: Example_DBs.zip (Zipped due to 2 link limitation.) Note: The missing Field in "Not Working.mdb" is ...
1
vote
2answers
46 views

Compatibility level 100 on SQL Server 2008 R2

I'm upgrading my databases to compatibility level 100 on SQL Server 2008 R2. My next step is to check if my stored procedures, functions, views, triggers are still working properly. Running each of ...
0
votes
2answers
259 views

Idea for manking tables for a pharmacy

OK I'm not into Database management, but I got this problem, also not sure if I'm posting it at the right place or not. So, my Dad works in a pharmacy and all the work there is on paper and is done ...
0
votes
0answers
22 views

How to represent multiple unique constraints across hierarchical tables?

My (shortened) table definition is: CREATE TABLE bus ( id integer NOT NULL PRIMARY KEY, name character varying NOT NULL CHECK (name<>'') ); CREATE TABLE network_node ( id ...
0
votes
2answers
49 views

Dynamic Filters in SQL server [on hold]

I have problems with my dinamic filter in 'sql server'. My problem is that the filters aren't working. When I try to execute the store procedure I just get all the data without filtering. I'm new ...
0
votes
0answers
46 views

How to tune a stored procedure that is fast on test data but slow in production

If you have a SQL Server 2012 database with test data in development and preproduction phase and the real from the company's server take place only in the production phase. In other words, the real ...
0
votes
0answers
32 views

The target principal name is incorrect. Cannot generate SSPI context - SQL Server 2012 error

I don't understand why this error is popped up suddenly. It was working till yesterday. It is a stand alone env. Problem is when me or any user wants to connect from their machine locally it is ...
0
votes
1answer
103 views

MySQL select data over time that matches pattern over several rows

We currently log data from our plants running remotely for diagnostic purposes on MySQL. I have been asked to come up with a typical running profile for several variables. For this, I would require ...
5
votes
2answers
66 views

Find rows where integer sequence contains a given subsequence

Problem Note: I refer to the mathematical sequences, not the sequences mechanism of PostgreSQL. I have a table representing sequences of integers. The definition are: CREATE TABLE sequences ( id ...
0
votes
1answer
561 views

Multiple table rows in one row Informix

I have problem just like this one but in Informix. I have table with 2 attributes ID, email like this one: ID email 1 my_mail1@mail.com 2 my_mail2@mail.com 2 my_mail3@mail.com 2 ...
4
votes
1answer
160 views

Is this a sane model for a network provisioning system?

I'm sketching out a model for a network setup, where I have the following conditions: There are multiple nodes which contains a number of switches each. Each switch has multiple services, which may ...
2
votes
1answer
3k views

Extract MySQL information from a compressed tar.gz file

I have a back up-of a MySQL database which is contained in a .tar.gz file - I need to restore it. I've never had to do this before. The only way I can access the database is through phpmyadmin. There ...
1
vote
1answer
149 views

how to group data against more than one conditions

I have a table named Employees with below two columns. ================== Employees 1- EmpId (auto incremental id) 2- EmpName (varchar) There is another table named 'LateDays' with below five ...
1
vote
2answers
27 views

MYSQL find if a default value has been set for a table (DateTime Specifically)

I am trying to internationalize a database, but there are places where the timestamp that gets set is using NOW() instead of UTC_TIMESTAMP(), and it's using Amazon's RDS with multiple MySQL servers. ...
0
votes
2answers
56 views

Select statements during long update

I've a table MESSAGES id (long) | attach_data (image) | ins_date | flag this table contains all messages of my application. During night, my job cleans old attach_data with query update messages ...
1
vote
1answer
11 views

Replication with fail over cluster in SQL Server 2014

Presently I am Using Replication Feature in SQL Server 2012 .I want to migrate to SQL server 2014 .I want to use fail over cluster feature .Does it effect the replication feature which we are using ...
10
votes
3answers
2k views

Why should a key be made explicit?

I am very new to the subject of databases so this may sound ignorant, but I am curious why a key should be made explicit within a table. Is this primarily to tell the user that the given column value ...
1
vote
0answers
41 views

SQL Server Profiler

I had run a SQL trace on SQL server 2008 r2 and had originally saved it to the table format. This table now resides in the database and I was wondering could I export the contents of the table to a ...
0
votes
1answer
252 views

Data Analysis with MongoDB or MySQL

I have a need for processing some data in order to come up with some statistic. This is the scenario: Each user can answer several questions from the question list. They pick their answers from a ...
11
votes
3answers
4k views

Choosing a database for storing large amount of data and allowing for thousands of writes [closed]

I am working on a project that has some serious requirements regarding its database and having hard time choosing the best solution. The details are: Database will easily reach tens of terabytes of ...
0
votes
0answers
36 views

How to fetch two column different row values in sql [on hold]

I need emp table column name is ename 'Martin' and 'jones' details and in job column who every working as 'clerk' they both want to fetch Martin and jones are working in different jobs
3
votes
1answer
351 views

System.Data.Entity.Infrastructure.CommitFailedException: C# Multithreading & SQL Server 2012

We have a C# multi threaded (100 threads) program which reads the records from the DB and each thread picks up one record (one Entity Framework connection per thread) and update the a single DB table. ...
0
votes
0answers
10 views

i have a roll_no,semister_id and marks of semister i want to show result like roll_no semister_1 and semister_2 [on hold]

I have a roll_no,semister_id and marks of semester, and want to show result like roll_no , semester_1 and semester_2 [As shown below]
0
votes
1answer
25 views

Deleting near duplicates based on one varchar field

I'm getting around to cleaning a database but I need some help with deleting near duplicates in MySQL database but is only off by one character in a column. I've searched and found ways to remove ...
9
votes
3answers
889 views

How to implement business logic permissions in PostgreSQL (or SQL in general)?

Let's assume I have a table of items: CREATE TABLE items ( item serial PRIMARY KEY, ... ); Now, I want to introduce the concept of "permissions" for each item (please note, I'm not talking ...
11
votes
2answers
58k views

Oracle: Quick way to list all database links

The title says it all, is there a way to quickly list all the current database links in oracle? Something along the same lines as this, which lists the current user's tables: select * from ...
2
votes
2answers
956 views

Designing database with Category and Tags for Posts - Seeking for guidance/advice

I'm not really a database novice but I'm not an expert either. Problem: I'm having this difficulty on designing my Post, Category and Tags relationship. I've read Normalization many times but could ...
0
votes
0answers
17 views

Compare values between columns in different tables

I have two tables that looks exactly the same: CREATE TABLE `_sync_history` ( `id` int(11) NOT NULL AUTO_INCREMENT, `object_name` varchar(45) DEFAULT NULL, `sync_time` datetime DEFAULT NULL, ...
0
votes
2answers
349 views

How to implement a dynamic string into a (prepared) sql statement?

I have some spatial datasets which are available in a open source metadata catalog software GeoNetwork and in a PostGIS database. The problem is that the student who created the database used ...
3
votes
4answers
3k 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 ...
2
votes
1answer
73 views

select n values per group

Lets say I have the following table ----------------------------- | user_id | comment | ----------------------------- | 2 | thats cool | | 2 | awesome | | 3 ...
3
votes
2answers
4k 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 | ...
2
votes
1answer
554 views

Designing a High Score/Leaderboard table

I'm looking for input on how to design a highscore table for a game I'm creating. The database I'm using is postgres. That's not really something I can or want to change (unless there's very good ...
0
votes
2answers
46 views

How many passes of a table are done with UNION ALL? [on hold]

I am trying to run a single query that will make a single pass through a table, and was wondering if by using UNION ALL to make counts of total row unioned with counts of unique rows so I would be ...
0
votes
2answers
3k views

Optimize IN clause in where query with order by - MySQL

I am trying to optimize a query that using IN clause in WHERE to avoid file sorting. To make it easy , I created the following sample which shows the problem. Here is my query: SELECT * FROM `test` ...
0
votes
1answer
139 views

How to merge two databases, both with primary keys

I have two databases, both with the same tables, but the data is out of sync. I need to add data from database A to database B, but not overwrite any data in B that exists in A, instead add it with a ...
2
votes
1answer
127 views

Should database be denormalized if I need an index across multiple tables

I have a catalog of album and song names. The song has a foreign key to the album table. I expect queries like: SELECT * FROM song s JOIN album a ON a.id = s.album_id WHERE LOWER(CONCAT_WS(' ', ...
2
votes
1answer
24 views

How to write a query that counts rows by date?

I have a database named emp1. There is a table named tx_feeder. I want to get the count of rows from that table. I have used the following query to get the data of single day, select count (*) ...
0
votes
0answers
23 views

Creating a trigger to update field in 1 table based on values in another table using data in multiple rows

I'm new at SQL and need to create 2 triggers between 2 tables. Table 1 has the following columns: tbl1.ContractID char(6) tbl1.ContractFulfilled char(1) Table 2 has the following columns: ...
3
votes
5answers
5k views

How to get the total days per month between two dates?

Given two dates like 20120302 and 20120605 I need to get a list of Months and the total days in those months that fall between those two dates, like so: March 28 April 30 May 31 June 03
-1
votes
1answer
17 views

T-SQL Trying to create a trigger that uses joins across 2 server instances [on hold]

I am trying to complete a trigger on 2 instances of SQL Server. I am running MS Dynamics ERP set CRM & GP. I need to remove data from one server once an update happens to another table. I have ...
-1
votes
0answers
44 views

Design for fast database query over millions of rows [on hold]

I want to query in a database like : Let us consider a student table with the following attributes: (Id, Age, CGPA, Address, Gender). Our table contains record for all the student in the world (so my ...
0
votes
1answer
169 views

MySQL-How to find that value in a column is subset with all other values in same column multiple time

I have a table named as Team where table contain 3 column (ID,Team,Task). Its datatypes are (INT,VARCHAR(255),INT). Below you will find a sample table where I would like to perform the query on column ...
1
vote
0answers
124 views

Generally unsolvable problem? How to ORDER BY aggregated columns GROUPED BY many possible columns very fast?

Is there general solution or some best practises or at least some vague hint, how to solve following problem (general, but concretised in following example)? I think this must have been solved many ...
0
votes
3answers
245 views

What's faster: two UPDATEs with WHERE (in / not in) or one UPDATE with CASE?

What's best performance wise, two separate UPDATEs: UPDATE table1 SET enabled = 0 WHERE code NOT IN ( SELECT id FROM table2 ); UPDATE table1 SET enabled = 1 WHERE ...
2
votes
4answers
106 views

Finding all date ranges that overlap a target date range

Say I wish to store when staff are on holiday (FromDate, ToDate) and then I wish to find all staff that are on holiday between two given dates (QFromDate, QToDate). Now assume that I have many such ...