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)

2
votes
3answers
168 views

Why MAX() of SUM() giving wrong output?

I have a table employee id name salary city 1 ram 50000 c1 2 sham 20000 c2 3 jadu 80000 c1 4 madhu 90000 c4 5 hari 10000 c2 6 gopal 34000 c3 7 ...
1
vote
1answer
22 views

Index on integer column not helping. Help? (EXPLAIN ANALYZE output inside)

The table market_trades has an integer column market_id which takes values from 1 to 5. This seems like a pretty straightforward query to get the number of trades on each market: WITH markets AS ( ...
1
vote
1answer
42 views

Optimizing a query on a large table (EXPLAIN ANALYZE result inside)

I have a table market_trades with a timestamp column: api_production=# \d market_trades; Table "public.market_trades" Column | Type ...
1
vote
2answers
43 views

sp_publication_validation procedure doesn't seem to work properly

I am trying to use sp_publication_validation stored procedure to validate that my replication setup is working correctly. This procedure is pretty straightforward and is described here. I am running ...
0
votes
0answers
21 views

Combining 2queries: One filtered Users on Role, One Select users with only one specific role

First query: Selects All users with specific rid ( 3 or 4 or 19 or ...). SELECT u.uid AS uid, u.name AS name, fn.field_full_name_value AS field_full_name_value FROM users u LEFT OUTER ...
1
vote
0answers
45 views

How to add SQL Server in networks list?

After changing my PC name and workgroup, I am no longer able to connect to SQL Server. Currently in my servers list I have only Home & localhost. Anybody know how to add server to networks list? ...
2
votes
4answers
101 views

Make IN clause behave like AND clause

I've asked the same question on stackoverlow, but got no answer, so that's why I'm asking it here (I've read that simple sql questions should be asked at SO, but I have no choice). Suppose I have 3 ...
1
vote
0answers
64 views

How to handle many columns / variable schema?

I apologize in advance in case this question has been asked already. I'm working on revamping a reporting application used within my company. The requirements are: Support addition of new fields ...
0
votes
1answer
45 views

Max from 2 columns with priority date col then the seq col Oracle

I have a column xdate, column seqnumber, personID I want 1 record per personID, where it is the most recent xdate max(xdate) if there is multiple same max xdates i want it to look then for the max ...
0
votes
0answers
9 views

SQL Select a rolling count of total transactions over time [duplicate]

I need to get a set of total transactions over time on a weekly interval. Essentially I need a total-transactions-to-date column. When I group by WEEK(Date) I get the amount of transactions for that ...
2
votes
3answers
66 views

Rolling count of total transactions over time

I need to get a set of total transactions over time on a weekly interval. Essentially I need a total-transactions-to-date column. When I group by WEEK(Date) I get the amount of transactions for that ...
0
votes
0answers
13 views

How to back out of mass update in SQL Replication [duplicate]

We have a database which is set up in Merge Replication. We accidentally updated hundreds of thousands of records in a large published table on the publisher database. We did is something like ...
2
votes
0answers
8 views

Active record relation AND filtering [duplicate]

(Edit: since reposted to dba.stackexchange.com and answered there). Suppose I have 3 models: Post, PostCategory and Category. I'm implementing some kind of page with filters where I can filter posts ...
0
votes
3answers
87 views

Delete all the data from all tables

I have a little problem. I have to delete all the data from our database. I don't want to drop tables, only delete the data sets. These are 47 tables in total. I tried the following: Delete ...
1
vote
4answers
42 views

where clause: if this value from col x make sure col y is this value

I feel like this is an easy one for you Experts! I haven't done any kind of Advanced SQL in years. Fundamental question: select * from myTable where ((if col1 = 'this', make sure col2 = ...
0
votes
0answers
10 views

Multischema view for postgres , querying multiple databases. [duplicate]

Im managing SaaS app where each customer has a separate database (Postgres 9.3) . I need to figure out the best way to aggregate and query this data ( READ-only ). I know postgres has problems with ...
-1
votes
2answers
39 views

My database is missing and disappeared after detaching it

I have detached my database from SQL Server 2008 and unfortunately I do not know its path and by my connection string I extract its exact name and searched it but it is disappeared from my computer. ...
1
vote
0answers
24 views

How to append records from one complete table into a partly populated differently formatted table?

I have two tables of interest linked in a MS Access 2007 database. The master table called EMPL_MASTER (from which I only have read access, I cannot make changes) updates every time a system ...
1
vote
1answer
59 views

Beginner questions about SQL and relational algebra

I'm doing some exercices waiting for the exam. I have some doubt. RELATIONAL ALGEBRA These are the table: Newspaper (Cod_n, Name_n, Publisher) Article (Cod_a, Title, Topic, ...
0
votes
1answer
27 views

Need some help with this query or some ideas for change it to better model

I've a dilemma in my application and I don't get how to solve it at DB level. The idea is that a user can be subscribed to one or more services but services are show depending on user type. This can ...
2
votes
1answer
53 views

Foreign key, key split in two tables

I have to record a list of events made of action, reason (optional, may be multiple), user. I have a master table with all the valid action, and another with all the valid action-reason combination. ...
3
votes
5answers
107 views

Find all values not present for other values of other column

Let's suppose I have a table with integer columns a and b (each pair (a,b) is unique) and following data: a | b ------- 1 | 2 1 | 3 1 | 4 1 | 5 2 | 1 2 | 3 2 | 4 3 | 1 3 | 3 Now I need to ...
0
votes
1answer
37 views

PL/SQL group orders by date and sum the total [closed]

I need to group orders by date and sum the total, My table looks like this : TEST_DATE TEST_ID - NUMBER SUCC_ID - NUMBER I need to group by date and sum the total, this is my query: SELECT ...
0
votes
1answer
24 views

Count(*) as rolecount, filter by rolecount = 1

How can i filter out the rows who only got rolecount = 1? WHERE rolecount = 1 gives an error. Can't really figure out how to do it. SELECT u.uid AS uid, u.name AS name, u.picture AS picture, ...
0
votes
1answer
29 views

Is there any performance difference between a group by statement and nested select statements in sql?

I am curious to know about the differences between using group by statement and nested selects to achieve the same result. for example consider I have two tables such as the following: tblPurchase ...
5
votes
1answer
374 views

Bad Design or Complicated Query?

Problem: For fun I am building a Database for lottery results to get some statistics off of. As of now I am only interested in a Pick 3 style lotto and might introduce more later. How would you ...
1
vote
1answer
31 views

Joining timestamped records with the most-recent-prior records from another timestamped table

Say I have 2 tables: Table: sales timestamp timestamp without time zone amount numeric Table: prices timestamp timestamp without time zone price numeric How can I get a ...
2
votes
1answer
528 views

Simple SQL Query

I have a db that in one column have 5 possible values: "Fechado", "Aberto", "Em Despiste", "Cancelado", "Resolvido". And im trying to do a query that counts the values to a json file var data = ...
3
votes
1answer
95 views

Copy database and clear down data (including linked tables) older than N days

I have a sizable production database (140GB) on MS SQL Server 2012, which I'm trying to automatically download to our development environments on a periodical basis (every week or on demand). I'm not ...
0
votes
1answer
30 views

how to optimize grouped select from an already grouped select query

I already had a specific query to get a number of shops, but I couldn't sum the cost field, I kept on getting an error on the sum function, but it worked when I did this; select tab.shop_id, ...
0
votes
0answers
65 views

Nesting multiple INNER JOIN within LEFT OUTER JOIN in sql on deifferent side of keyword

I am having some problems with one of the question's answered in the book "SQL FOR MERE MORTALS". Here is the problem statement Problem Statement Here is the Database Structure The Database ...
2
votes
3answers
461 views

How do I ORDER BY typical software release versions like X.Y.Z?

Given a "SoftwareReleases" table: | id | version | | 1 | 0.9 | | 2 | 1.0 | | 3 | 0.9.1 | | 4 | 1.1 | | 5 | 0.9.9 | | 6 | 0.9.10 | How do I produce this output? | id | ...
3
votes
1answer
58 views

Determining what query was called

I want to reverse engineer what exactly happens to the MySQL database when a product is added in Magento. I will have to reproduce the query, because I would like to manually enter new records using a ...
0
votes
1answer
44 views

procedure to select the first name of the passed id

I have a table person_name DESC PERSON_NAME Name Null Type ------------- -------- ------------ ID NOT NULL NUMBER(9) FIRSTNAME VARCHAR2(20) ...
0
votes
1answer
22 views

PostgreSQL SQL syntax - JOIN and substrings

I have a single table of hostnames and NFS exports, some but not all of which are related. Example entries: HOST VOLUME ----- ---------- host1 vol1 host1 vol2 host1 ...
1
vote
1answer
52 views

optimize the query with big and low cardinality

I have big enough dataset and few joins to get them all with help next query: SELECT DISTINCT c.c_id FROM c_active z1 INNER JOIN cs c ON (z1.cv_id=c.cv_id) INNER JOIN indi i ON (c.m_id=i.m_id) INNER ...
0
votes
0answers
73 views

Combine two mySQL sum queries into one which use different where clauses

I have two sum quires referencing different tables in mySQL. How can I combine the two in order to calculate the difference between the two values for each user in the database? Query 1: SELECT ...
1
vote
1answer
78 views

Restoring a database, changing recovery model

I regularly make a backup of my production database, then restore the same onto a test database, so developers can always have recent data for testing. The production database uses full recovery ...
0
votes
1answer
55 views

Update table from different table

UPDATE RBI.RBI_OV_F_DETAIL A SET (BRANCH_ID, BRANCH_NUMBER) = ( SELECT PRICE_BRANCH_ID, PRICE_BRANCH FROM RSE.RSE_SO_F_ORDERS B WHERE A.SOURCE_ID = ...
0
votes
1answer
273 views

Why straight SQL query run faster than stored procedure?

I am facing-out a very surprising problem in SQL Server. The problem is when the query run in SSMS window it will return result in ~1 sec and when the query run through a stored procedure it will ...
0
votes
1answer
52 views

Is this statement syntactically correct? [closed]

$q = "SELECT var1, var2, var3 FROM tablename WHERE var_a = '$var_a' AND var_b = '$var_b' AND ( ( var1 = '$var1' AND var2 = '$var2' ) OR ( var1 = '$var2' AND var2 = '$var1' ) )"; This ...
0
votes
1answer
15 views

Update query for Minimum absolute difference in Access [SQL]

I have 3 tables in an Access db. Table_1 and Table_2 are where the source data comes from and I am filling Table_3. Table_1 has the following fields: Acct_Name Amt Table_2 has the following ...
2
votes
2answers
77 views

Logical problem with a database model

Beforehand the table names and columns are in Spanish, my native language is Spanish. (table names have been now edited to English) I've got a database to manage the tickets sales of a trip that is ...
0
votes
1answer
53 views

MySQL add few column exception for TRIGGER UPDATE FOR EACH ROW

How to effectively add column id, name exception for trigger: CREATE TRIGGER updated BEFORE UPDATE ON table1 FOR EACH ROW SET NEW.date=NOW() so that date=NOW() will be affected on updating any ...
2
votes
1answer
212 views

SQL Parameter Sniffing Possible Workaround

Our system has roughly 500 "Customers" that have extreme differences in record counts. Here is a (greatly simplfied) sample query that could return 0 - 100000 rows depending on the parameter passed. ...
0
votes
0answers
41 views

Pasting into PHPMyAdmin

This is probably really simple but I have had a look on the web but can't seem to find an answer. I am experimenting with MySQL + PHPMyAdmin on a WAMPserver stack and was saving queries in Excel When ...
1
vote
1answer
82 views

Reducing execution time for this gnarly SQL

I'm creating an order system of some kind. There is a function that I use to get orders (in PHP) and retrieve the required information about the order. However, it seems that I am pulling a lot of ...
1
vote
1answer
50 views

Update different columns on different clauses

I have a database table in Postgres 9.3 with the following layout: id SERIAL, col1 INT, col2 INT Whenever a new row is inserted, I will have to update ALL rows col1 and/or col2 in different cases ...
0
votes
0answers
16 views

How does database connection actually works? [duplicate]

I am wondering, on a practical level : what is a database connection? What does it represent for PHP/Java/Ruby/etc to have an opened connection for instance? What about the MySQL deamon? Does it ...
0
votes
1answer
33 views

How to sum the buy order and sell order in one row per each ticker

I have a table containing some trading information of several tickers and I would like to divide the data into sum(Amount) for buy and sell separately, grouping by each ticker. I have one table ...