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)

3
votes
1answer
125 views

How to enforce a constraint over multiple tables?

I have the following five tables: PRODUCT FEATURE PROVIDER PROVIDER_PRODUCT PROVIDER_FEATURE The logic is as follows: A generic PRODUCT can have one or more FEATUREs. There are different ...
1
vote
1answer
13 views

Feature of database-level audit in SQL Server 2012 Express edition

Like in SQL Server 2012 Enterprise edition, can we have database-level audit in SQL Server 2012 Express edition? Can we have Audit Folder in Security Tab?
1
vote
1answer
38 views

PostgreSQL “size of temporary files”

I've imported data into a new database (about 600m rows of timestamp, integer, double). I then created some indexes and tried to alter some columns (got some out of space issues), the database is ...
3
votes
2answers
70 views

How does TOPN influence a query with group by?

I have a query that looks like the following: Select t1.field1, t2.field2, sum(t1.field3) From t1 inner join t2 on t1.id = t2.id Group by t1.field1, t2.field2 It's simplified, normally there are a ...
3
votes
1answer
25 views

Inventory management in Rails

I'm creating a inventory management app in rails. I have an User Table and a Recipe table, there is also some tables with types of material (pens, sharpies, paper, and so on). Because there is a lot ...
2
votes
1answer
102 views

How to join all combinations from two tables with a common column

I have a list of suppliers who supply a list of countries. For example: Supplier A supplies to: USA France China and another list of suppliers with their suppliers' factories For example: ...
2
votes
1answer
46 views

How to insert values in junction table for many to many relationships (MySQL)?

I'm new to relational database design and I've hit a stumbling block. I have three tables... PROJECTS project_id, name, ... PROJECTS_UNDERGROUND projects_FK, underground_FK (I have both FKs setup ...
1
vote
0answers
15 views

MaxDB - Strange behaviour when selecting from a CLOB-Row

I am using "sqlcli" for Linux to write the output of the following query into a file: select clob_field from table (...) in SAPGui (original user input) the contents are displayed as: First ...
3
votes
0answers
100 views

How to manage hundreds of SQL jobs? [closed]

I work in Microsoft SQL Server database and I have to monitor and manage hundreds of SQL Jobs to see which job will execute on what day and what time. I also have to troubleshoot the failure of the ...
0
votes
1answer
21 views

Is there a query to see if all of my logins and password are moved during migration from SQL Server 2005 to 2008R2

I am trying to migrate my database from SQL Server 2005 to 2008 R2 test, I did follow back up and restore method to migrate the database, and I ran the QUERY provided by Microsoft to transfer logins ...
2
votes
2answers
64 views

MySQL: memory requirements for django app with 1 big table (460M rows)

I have created a django app that is running on virtual machine. All db tables are rather small (up to 1M rows) but recently I was asked to add a big table (460M rows). CREATE TABLE `genescorrelation` ...
5
votes
2answers
92 views

Will I need to create a pivot or can I alter this query

The following Query that I wrote is working to a point. The problem that I found is that in each pump, cable, etc can be listed in each of the part columns. Example: What I would like to get is ...
1
vote
1answer
21 views

mysql - select random rows with max 3 rows per parent id

I currently have a query that returns a maximum of 3 rows per parent_id. I want to enhance the query to return a maximum of 3 random rows per parent_id. This does not work for me at the moment as it ...
2
votes
1answer
125 views

Order by values with most incidence

I have a table as this example: CREATE TABLE customerEvent( customerID int NOT NULL, eventID int NOT NULL ) customerID may appear in many records. I use the following query to identify ...
0
votes
1answer
85 views

Find columns with different values compared with a reference row

My table, MARC, has more than 200 columns with WERKS (plant) as the key. Most of the entries in MARC have values that come from a reference plant. So, usually, all the 200 columns will contain the ...
3
votes
0answers
101 views

Which is more efficient b+ tree or search (binary or linear)?

SELECT A FROM T WHERE M > 1000 AND M < 5000; I am unable to judge which of the below is best suited for the above query: Linear search Binary search on M B+-tree with index on M B+-tree with ...
2
votes
2answers
29 views

Oracle Database 12c: TNS:permission Denied (CentOS)

(This post has a similar title to another post but the underlying cause and solution are totally different.) Problem: I can't login to sqlplus as sysdba but the listener seems to start ...
1
vote
1answer
27 views

Identify records with one identical field, but different timestamp

I am trying to process some data, stored in an Oracle DB, for a machine learning model I am working on. My current query uses this line in the SELECT statement to identify records that have a field ...
3
votes
2answers
41 views

Combine the effects of separate delete and insert rows

How to I combine the two rows below: Date Id For_Id Type New_Value Old_Value ------------------------------------------------------------------ 2015-08-13 6 327 ...
0
votes
1answer
31 views

Automate Backup of SQL Server Database Structure

I have a data warehouse which isn't huge only about 16Gb in a Microsoft SQL Server Standard Edition 2014 database. However, now that it forms part of our production system I don't like developing on ...
1
vote
0answers
29 views

Update row value with a sub-select join

I am trying to update 1 column in all rows in a table to have an appropriate count value. Sadly, the SELECT query I am using won't let me join on a field in update table. The query looks like this ...
2
votes
1answer
30 views

How to fix DB2 Unsupported functionality: GENERATED BY DEFAULT

On IBM DB2 I try the following direct SQL: CREATE TABLE users ( user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1), user_name VARCHAR(16) NOT NULL, email_address ...
2
votes
2answers
253 views

Numeric vs Integer for a column - size and performance

I have an application which uses a PostgreSQL table. The table is very big (billions of rows) and has a column which is an integer. The integer can be up to 6 digits, i.e. 0-999,999 , no negatives. ...
3
votes
1answer
69 views

Display non-null and null count using case statement

In total there are 10 values in my table, 5 of which are null. I need to display the count using case statement in Oracle, like output below: Sample Data: +------+ | Eid | +------+ | 1 | | 2 ...
2
votes
2answers
32 views

Differentiate between a row modification and add?

Any recommendations into how one can figure out which rows were solely modified from an existing row (edit) and which one were solely inserted? I know minus can extract any rows that were modified ...
2
votes
0answers
55 views

Finding the most recent event older than 60 days [closed]

I would like to filter rows in a table for the most recent event for particular individuals that did not occur within the last 60 days: Select pid, Max(date) as date FROM table WHERE date ...
4
votes
2answers
98 views

Count rows with inner joined tables

I have 3 tables: Players: mysql> SELECT * FROM players; ...
1
vote
0answers
82 views

SQL Server 2012 memory issue [closed]

I am having performance issues on a Windows 2012 Server running SQL 2012. When I run the DBCC MEMORYSTATUS command I receive the following. Process/System Counts Value Available Physical Memory ...
3
votes
1answer
38 views

Scripting Microsoft Data Warehouse Wizard

I am trying to automate the MDW setup process in my environment. Currently I have everything complete except for enabling the data collectors (usually uses a wizard as seen ). Has anyone seen or ...
2
votes
0answers
50 views

How can I organize complex queries?

I am analysing a relatively large (25GB compressed MySQL dump) static data set. Some of the queries I'm writing are several tens of lines long, which makes them difficult to understand, modify, and ...
2
votes
1answer
43 views

How to add DATETIME value for session user

I am maintaining two tables: Employee table consists of username and password(for logging in) log table consists of login and logout time for the session user With the click of a button, I should ...
2
votes
2answers
104 views

INSERT… ON DUPLICATE KEY UPDATE not working as I expect

I have a table called "Example" CREATE TABLE IF NOT EXISTS `example` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) NOT NULL, `b` int(11) NOT NULL, `c` int(11) NOT NULL, PRIMARY KEY ...
0
votes
1answer
22 views

json_array_elemnts() in FROM in postgresql 9.3 acting as where clause

I am trying to extract array out of json in the FROM statement But issue occurs when certain json doesn't have that array in it and then query just ignores that row and doesn't get counted in the ...
1
vote
2answers
51 views

Use array expression from subquery in ANY() condition

I have a relatively complicated query with a subquery fetching an array like so: ... ARRAY(SELECT category_id FROM category_schedule_con con WHERE s.id = con.schedule_id ORDER BY category_id) ...
1
vote
1answer
28 views

Query to get yesterdays data only for a specific time interval

Here is my where condition which I use for daily data but I've got to change the date everyday. WHERE X.stage = 'Approval' AND X.T_ExitTime between '2015-08-06 06:00:00:000' and '2015-08-06 ...
1
vote
2answers
28 views

Hows to make a DB2 table read-only?

We need to test how an application will fail when the DB2 database cannot be written to. What is the easiest way to make a DB2 table read-only? Is this considered locking? Thanks in advance, Bert
5
votes
3answers
420 views

How can I force MySQL to IGNORE ALL indexes?

I have read articles about FORCE index, but how can I force MySQL to IGNORE ALL indexes? I tried SELECT * FROM tbl IGNORE INDEX(*), but I was not successful. As for why I (and others) need to do ...
-1
votes
2answers
34 views

error18470 when i try to connect to Database Engine

I am new in SQL Server. Wrongly I disabled my instance when I'd worked with Logins. Now I can not connect to Database Engine. Could you please help me?
1
vote
3answers
63 views

Sql Script takes much longer than estimated

In order to clean up a very large table holding binary Files in IMAGE Cols, I created the following sequence (based on this: Article) to get rid of a part of those image Bloby quickly: Select * into ...
1
vote
1answer
80 views

SQL get row based on condition [closed]

Hi all please see attached data below. I'm trying to get a row based on various conditions. scenario 1 - get highest row if no hours exist against it that has (setup + processtime > 0). scenario 2 ...
5
votes
2answers
70 views

Create smaller row from alias and preserve column names

Using Postgres: SELECT users."name" AS "name" , array_to_json(array_agg(sites)) as sites FROM remodel.users AS users JOIN remodel.user_sites AS user_sites ON users.id=user_sites.user JOIN ...
3
votes
1answer
82 views

Is it possible to validate more than one parameter simultaneously into a Stored Procedure in sql?

On Microsoft SQL Server 2008 I realized a stored procedure which contains more than 20 parameters, in this SP I update or insert data into table which does not accept null values. I need to evaluate ...
1
vote
1answer
29 views

SQL 2012 insert data into a table from another table and custom columns

I'm trying to insert data from one table into another table and I also have 4 columns that are not in the source table but need to be populated in the target table. Here is the Insert statement SET ...
1
vote
2answers
55 views

Retrieve additional columns in recursive CTE

This works as far as getting the amount of children a "thread" has but now I can't seem to get it to pull parent row columns. Parent rows have parent_id is null, trees can be any level deep. I manage ...
1
vote
1answer
23 views

Parsing out date_entered field to capture TIME 'hh:mm:ss' based on time range

Based on the date_entered column, which is a data_type of 'DATE' how would I parse out the data to pull the time. Values in the DATE_ENTERED field (DATA_TYPE is DATE) looks like this 5/15/2015 ...
0
votes
0answers
39 views

SQL Server Profiler performance

I was wondering if I set a filter for just a particular user does it limit the amount of resource consumption used by sql server and does using a filter(depending on the filter) limit the performance ...
2
votes
1answer
109 views

Which DBMS are more standard compliant? [closed]

I find it frustrating that the same queries don't work in different DBMS. Example, some DBMS (such as Microsoft SQL Server and MySQL) seems to support the information_schema table, while others (such ...
0
votes
1answer
53 views

copy tables from one database to another

I have two Databases on mysql. They are used for completely different things and contain different tables. The problem is I need them both on the same server that can only host 1 db (and I can't ...
-1
votes
2answers
55 views

Optimize query or split in two queries and process result separately?

I have this DB schema: As you may notice already there is no relationship between tables and won't be on the future. If I run the following queries I will get a lot of rows per table: SELECT ...
2
votes
2answers
41 views

Extracting two particular values from a multi valued column

I have a table SUBSCRIBER and in it the data is stored in a way that bars is a multi-valued column . Different values of bars are stored in different rows with the ID as following : ID BARS 1 , ...