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)

1
vote
0answers
11 views

Is there a faster way to get UNION ALL behavior in Postgres? [duplicate]

I have a table (t0), in my Postgres DB, with data that looks something like this: t1_id t2_id 1 1 2 1 2 1 4 null 4 null 5 null And I have a query to return my ...
1
vote
0answers
19 views

Multidimensional aggregates

Suppose one wanted to cache simple aggregate data over a table or grouped parts of a table, one could simply make a table with the aggregated results. If the original table is only appended to, it is ...
0
votes
2answers
18 views

Create a test enviroment in Oracle 10g

I have a production enviroment in a Oracle 10g database which I would like to authomatically duplicate in a test enviroment. My production enviroment is just a number of tables in which I have usually ...
0
votes
0answers
26 views

Query output conditional upon values in dataset

If the number column contains values 5 AND 6, AND the value X exists in the chr column, I would like to exclude rows with 10 in the test column. But, if the number column contains values 4 AND 5, AND ...
2
votes
1answer
70 views

PostgreSQL return second 'Group' as an array

I have some tables that include websites and visitors and I am trying to write a query that produces a 'top 10' for each site, the tables look something like this... websites ------- website_id (pk) ...
5
votes
2answers
70 views

Is the runtime of checking for duplicate primary keys in SQL Server O(n)?

I was curious and I couldn't find too much info on this. Is it also O(n) for checking for duplicates among primary keys? Is it the same for most other SQL databases? Is there a place that lists this ...
5
votes
0answers
72 views

SQL Server stuck in single_user with auto_update_statistics_async on

Books online states Before you set the database to SINGLE_USER, verify the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. When set to ON, the background thread used to update statistics ...
1
vote
1answer
35 views

Linking first and second level composite primary keys

I am trying to create a new database with a hierarchical structure of two many-to-many tables using composite keys. Unfortunately the creation of tables fails stating that the foreign keys do not ...
0
votes
1answer
20 views

Retrieve index column

I have this index schema: CREATE index comments_idx ON comments (books) How can I retrieve column name ("books") through SQL query?
1
vote
2answers
48 views

Get player count per match on a matchmaking server [closed]

This is my current structure of my matches table: id, title, description, max_players, host_username, host_ip and there's another table called matches_players: match_id, player_username ...
0
votes
1answer
31 views

How to order by Levenshtein distance?

I want to order a table according to a char field using Levenshtein distance or similar because some entries may contain underscores or grammar errors. It is a table with all the books I plan to read ...
0
votes
1answer
32 views

Complex query on big table

I have a big user table (3M), of structure: age (int) country (FK) city (text) gender (text) active (bool) My objective is to run a search finding 15 users of given age area, gender, city and ...
0
votes
1answer
16 views

SQL - Add column if match else, on aggregate data

I have an order table that roughly looks like this |Customer id |Date | revGBP |orderID |GiftOrder | --------------------------------------------------------------------- |1 ...
2
votes
1answer
68 views

Can you put a SQL DB on a NAS? [closed]

Can you put a SQL DB on a NAS drive? For example can I put the PostgreSQL DB "files" on a Sinology NAS or am I better to leave everything on a "real" PC? (like, say, a regular PC server running Linux) ...
1
vote
1answer
126 views

What's the term that describes filtering query that makes use of indexes

I am looking for a technical term that describes the type of query in SQL that makes use of indexes. I completely forgot the name and can not look it up anymore (there's even a Wikipedia article about ...
0
votes
2answers
30 views

How to use relationships to extract data via select

Disclaimer: Novice in SQL I have 3 tables. There is one "major" table (labeled used_for_lookup below) used to do look-ups on and the other two (labeled fid_table and did_table below) are foreign keys ...
1
vote
1answer
31 views

Question about the workflow/design process/tools used by professional database designers

I hope this in the right place, as it's really more a question about database design software than the philosophy and practise of database administration. I'm a masters student doing an "introduction ...
1
vote
1answer
22 views

SQL : get rows from two tables relatively

I've two tables named categories and products. Simple structure is given below. categories cat_id cat_name 1 c1 2 c2 3 c3 products prod_id ...
1
vote
0answers
39 views

SELECT query with DISTINCT on a table-structure for graphs is very slow

I have 2 tables where nodes contains about 60m rows and edges about 500m rows (both growing fast). These two tables represent a directed graph. CREATE TABLE edges ( node_from bigint NOT NULL, ...
3
votes
1answer
46 views

How to join by most recent preceding date?

Imagine I have a database with historic price data and a second table with dates Item | Price | Date Customer | Item | date ------------------------- --------------------------- ...
1
vote
3answers
63 views

How to run sql agent jobs in a sequence order.with T-sql

My requirement is to run the SQL Agent jobs (EX: job1 to job10) with T-sql in a sequence order. That means job2 starts automatically when job1 completes. This process should continue until job10 ...
2
votes
2answers
265 views

Updating every cell of of a table?

I have a table with m columns and n rows. The ETL tools I was using wrote an empty ('') to every cell without a value and I would like them to be NULL instead. This got me thinking if it would be ...
4
votes
2answers
410 views

Restored Database is different from the original

I have recently performed a restore of our data-warehouse staging database onto a separate instance in our dev environment. After the restore I did a comparison and it looks like the database sizes ...
0
votes
1answer
39 views

how to select top ID from join operation and staging tables

I have this inner join from staging and operations table jobsCode I want to select the top surrogate key ID and get the Start_Date from this join my problem is my Query rerunning 2 records original ...
0
votes
1answer
46 views

Use if exists in postgresql [closed]

I know about the exists feature, but that does not help me with the given situation. What I want is: Check if some row exists, and if exists then check for an another row if row exists. Here's ...
0
votes
1answer
78 views

Generate All days in moth report using PIVOT of SQL server

My table, tblEmployeeScan(EMPLOYEE_ID varchar(7), ScannedTime) Everyday each employee scans 2 times, 1 time or 0 time Now I want to create store procedure Create proc spMonthScanReport @MONTH ...
0
votes
0answers
52 views

Accidentally deleted records in MS SQL Server 2014

I have a very big problem with SQL Server data lost. Recently, i have performed SQL replication. and i found out that all exising records/rows in the subscriber was totally erased and replaced by the ...
0
votes
0answers
37 views

Querying system tables from Linked Server

I am trying to query the syspublications table on Linked Server A from Server B. Both servers are running SQL Server 2012. Using this simple query on Server B: SELECT a.dest_table FROM ...
0
votes
1answer
34 views

Getting the entire records from Two tables for Count purposes SQL

I'm just wondering if anyone could help on how to bring two columns from two tables together in one table. These table have id's but they are not linked although they have other columns that I can ...
0
votes
1answer
34 views

Using count() and sum() in a calculation in a select

I am trying to do the following in a single select. We have a SAAS that has Company records and User records. A User is a member of a Company (Company.CompanyId = User.CompanyId). In the User record ...
1
vote
3answers
92 views

Execute permission denied on object sp_start_job

I need to allow a user to kick of a specific agent job without having any ability to start other ones. To accomplish this, I've created the following procedure (simplified): ALTER PROCEDURE ...
0
votes
0answers
18 views

PostgreSQL SELECT and JOIN question [duplicate]

Trying to fix the previous post text formatting issue there was and I did not recognize: I'm using PostgreSQL under ubuntu 14.04. I have two tables as below: create table material_base_name ( ...
0
votes
1answer
42 views

Help with optimising a sql query on two partitioned tables

I have a SQL query that I am running on two very large tables in SQL Server 2012 Enterprise The two tables are ACKs and Logs. Both are partitioned on their time by day column with the appropriate ...
1
vote
1answer
39 views

joins with “could be blank” conditions

Question is most probably stupid but I am stuck on this and cant think anymore(Pissed Off). I have these tables-> employee(has a column foreign to location),location and location_flag. And I need ...
0
votes
1answer
42 views

Unable to copy a DB from SQL Server 2000 to SQL Server 2012 using copy wizard

I am trying to copy a database from SQL Server 2000 to SQL Server 2012 using copy wizard in my local machine where SQL Server 2012 is installed. But, the wizard is not detecting destination server. ...
0
votes
2answers
72 views

SQL Server 2012: Full Text Catalog taking up all the disk space

We have created full text catalog and index for a pretty large table. The column on which the index is being created has a datatype of varchar(max). The problem started as soon as we started ...
8
votes
1answer
107 views

Clustered Columnstore indexes in SQL Serer 2014 for data warehousing

I am performance tuning a DWH using indexes. I Am fairly new to SQL Server 2014. MS describes the following: "We view the clustered columnstore index as the standard for storing large data ...
1
vote
1answer
23 views

how to included concatenated values in Dynamic Pivot Column Value

I am extending a dynamic pivot query. The original query works fine. In the second query Instead of display the date as a value for the pivot column I would like to display a concatenated column ...
0
votes
0answers
5 views

ODBC/MYSQL Insert a Query Result from ODBC to a databse in MYSQL [migrated]

I'm connecting to a cloud database through a ODBC connection: $conn=odbc_connect('MYDATABASE','',''); if (!$conn) { exit("Connection Failed: " . $conn); } $sql="SELECT DATETIME_ID, NAME, Sum(CNDROP) ...
0
votes
1answer
33 views

How to add a default null row if no results from the dynamic pivot query [closed]

I execute this procedure and display results in my application. If my query does not return any results, I still need to print out the column headers and since its a dynamic query I cannot hard code. ...
0
votes
1answer
50 views

sql or hql query to find all parents of a particular record

I need a sql or hql query to find all parents of a particular record, that should work in oracle as well as mysql, for eg if I have the id: 201, the parents would be: 188, 187, 1. any help would ...
0
votes
2answers
33 views

Error with making backup in sql Server

We have a win server where installed sql server 2008. When I try to backup database, I have an error. Is anybody know what is the error? TITLE: Microsoft SQL Server Management Studio ...
0
votes
0answers
27 views

mysql advanced query

I'm sorry for my English. I have a problem with db mysql. I have a table like this id|rat|time 1 |2 |12:00 2 |0 |12:55 3 |0 |13:05 4 |6 |13:45 5 |0 |14:00 6 |8 |15:00 7 |0 |16:30 8 |9 |17:00 ...
0
votes
2answers
37 views

How to configure Standby database in oracle for read/write

I have configured a physical standby database in Oracle 11gR2, and the standby database it's working fine. All the changes in the principal database are replicating to the standby database, the ...
0
votes
1answer
46 views

Procedure with Insert Data From One Table to Another Table

I have two tables Person with PersonId, Name,Address,Mobile and PersonLog with Id,Name,Address,Mobile,FK_PersonId. I am trying store the old data in PersonLog and Person to be updated. This is my ...
0
votes
0answers
22 views

How to retrive output depending on last alphabet from entry in table [migrated]

I have table User with Id, Name. So depending on last alphabet from column Name, i need to output some text. If last char is 'o' should return YES, if is 'i' return NO, else null Ex. Monika ...
1
vote
3answers
62 views

Longest matching suffix while using aggregate functions

Background Looking to find the longest matching string suffix. Setup Consider the following fiddle: CREATE TABLE noun ("label" varchar(10)) ; INSERT INTO noun ("label") VALUES ...
1
vote
1answer
39 views

Tables names in a supertype/subtype schema

I have 3 types of users in my app. The users share some common attributes, but they also have quite a bit of stuff that pertains uniquely to each user type. I have a person table that contains limited ...
0
votes
1answer
25 views

Why does my MySql Savel replicate changes that are neither in binlog_do_db nor replicate_do_db?

I have a standard simple master slave replication that usually runs fine. But sometimes, all of a sudden, I get replication errors on the "phpmyadmin" table. I have explicitly only specified one ...
0
votes
0answers
41 views

SQL Mirroring synchronize logins and agent jobs

We have just started using SQL Server mirroring for several clients on SQL Server 2008 R2 Standard. We've got the SQL Server mirroring working fine however now we run into the issue of replicating ...