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
1answer
61 views

2 Date Ranges - How many days from one range excluding weekends overlap

First - Thanks for your help. I'm looking to create a query in SQL that provides the me the # of days from one date range that overlap another date range and excludes weekends. Example Data: ...
0
votes
1answer
40 views

MySQL: Rank by Department in an employees table

I need Mysql query to rank the hours used by each department in an employees table, but it just ranks all without taking into consideration the departments.
1
vote
1answer
38 views

How to query a list of all the different errors?

I have a DB which is very big and stores a lot of eventlogs, around 15 million rows. When I try this query, it is totally impossible to get some results. Any idea how I could do this? Maybe indexed ...
1
vote
3answers
32 views

Study material for Advanced Databases module inc SQL, Relational Model, ERM & TM [closed]

For my next module in my masters, I am planning on studying Advanced Databases. (Long story short, the masters is designed for people with NON-CS degrees, so I have never studied databases, therefore ...
-2
votes
1answer
35 views

Oracle Concurrent Insertion

We have simple case like Create table t1 (c1 number primary); session1: insert into t1 values (1); //session1 do not commit transaction session2: insert into t1 values (2); Will Session2 ...
2
votes
2answers
43 views

SQL Insert Statement Persists Two Records When I Need Just One

I have altered it to include hardcoded variables when I pulled this chunk of code from the stored procedure. I ran it independent of the application with the same result. I need one insert statement ...
0
votes
1answer
27 views

SQL Insert Statement Persists Two Records When I Need Just One [duplicate]

I have altered it to include hardcoded variables when I pulled this chunk of code from the stored procedure. I ran it independent of the application with the same result. I need one insert statement ...
2
votes
2answers
62 views

Using collate: When to use and when not to use

In one of my applications we had tsql like shown below SELECT DISTINCT [Dblist].[DbName] AS [DbName] FROM [Common].dbo.[Dblist] WHERE dbname not in ( SELECT [name] FROM ...
2
votes
0answers
25 views

Best way to get time zone key name from SQL Server

Below is what I pieced together but I wanted to see what other ways are available. SET NOCOUNT ON; GO DECLARE @tz VARCHAR(50) EXEC [master].[dbo].[xp_regread] 'HKEY_LOCAL_MACHINE' ...
0
votes
1answer
38 views

to pivot or not to pivot - schema design question [closed]

I am implementing something similar to this schema http://www.databaseanswers.org/data_models/event_reservations/index.htm It occurs to me that the pivot table for Customer_Bookings would just keep ...
2
votes
3answers
323 views

Run multiple stored procedures in sequence

I have a stored procedure written in T-SQL (SQL Server 2008 R2). It is a master procedure that essentially call multiple other subprocedures in sequence. The call and error handling is identical for ...
0
votes
1answer
69 views

Merging tables, but ignoring duplicate columns

I have two tables I want to merge: city1(id, city, state, zip, lat, lon, county) city2(id, city, state) The tables contain overlapping city and state, but not primary keys. I can't figure out how ...
0
votes
3answers
41 views

Converting huge ASCII file to flat and convert

I am not a db administrator but I thought to ask this question here. I have several very large ASCII files (3-5GB each) that I need to extract the data and then sort it. The instructions say to ...
0
votes
4answers
66 views

SQL Server combine Update and Delete

Is it possible to combine both update and delete in a single SQL statement? I would like to execute something like this: update a set a.value = a.value + b.value from Table1 a join Table2 b on a.ID = ...
1
vote
2answers
57 views

MS SQL Server 2012 Log Shipping Questions

I have a few questions regarding Log Shipping in SQL Server 2012. 1) If the primary database server dies, is it possible to allow users to connect to the secondary database so as to continue working ...
1
vote
0answers
17 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
2answers
45 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
27 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
1answer
38 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
76 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
75 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
1answer
91 views

SQL Server stuck in single_user with auto_update_statistics_async on [closed]

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
42 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
21 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
49 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
35 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
35 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
23 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
135 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
31 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
38 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
28 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 ...
2
votes
1answer
78 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
49 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
97 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
272 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
428 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
51 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
58 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
102 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
57 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
68 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
38 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
36 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
4answers
198 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
19 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
44 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
41 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
54 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. ...