Structured Query Language (SQL). For questions about the language, not Microsoft SQL Server (tag sql-server).

learn more… | top users | synonyms (2)

1
vote
1answer
45 views

SQL query to check a table against a “rules table”

I have a table PIT that looks like this: PIT_TYPE PIT_STYPE PIT_APID 1 1 1313 1 7 1313 3 25 1316 247 1 1318 247 ...
1
vote
0answers
30 views

Fetching equal records of all options MySQL [closed]

there are 4 columns which I need in conditions. age, city, accommodation, sex I want to export total number of 1000 records in the following criteria: I choose ages between 31-35 years old with both ...
8
votes
3answers
540 views

Compute the total of SUM(column)

I have this code that sums up the qty for a certain item (itemid) and by its product date code (proddte). select sum(qty), itemid, proddte from testtable where .... group by itemid, proddte What ...
1
vote
1answer
33 views

Is it possible to set PostgreSQL roles with privileges this way?

I'm trying to develop a system, and for the first time I'm using PostgreSQL. I'm trying to organize and separate roles properly, but I'm losing my mind with it (about a week on this now). Following ...
1
vote
1answer
33 views

Notify on SQL trace event starting?

I was wondering if there was a method of receiving some sort of notification when a SQL trace begins - be it e-mail or some other method? The circumstances of needing this we currently have a ...
1
vote
1answer
20 views

Changing Key (PK, FK) properties in DROP/Add column statements

I want to change a table by adding a column. So far nothing to see here, BUT I would like to make this column part of a composite key, i.e., I now have a layout table_name( Field_1 datatype PK, ...
11
votes
1answer
216 views

Modeling a database for Video Game related information with multiple many-to-many relationships

Scenario I am relatively new to database design, and I decided to make my own hypothetical database for practice. However I am having trouble modeling and normalizing it, as I esteem that there are ...
1
vote
1answer
106 views

Execute a stored procedure in a SELECT or FROM statement or a SQL function

Are there any possibilities to execute a stored procedure in a SELECT or FROM statement or a SQL function? Like select sp_name_and_parameters from table_name_or_sp_name_and_parameters or select ...
1
vote
0answers
40 views

How to export views and import it in other DB?

I am trying to export the database and everything is fine but I am not able to export the VIEWS. Is it possible to export the views from sql queries and import it?
1
vote
1answer
35 views

Select rows and add their previous rows

I have a table structure that holds historical data for another table. Something like id | name | date ---------------------------------------- 1 | John | 2016-01-01 14:38....
0
votes
1answer
38 views

SQL Developer auto trace does not show cascading deletes

I'm trying to understand the performance characteristics of SQL statement that does DELETE cascade on 4 linked tables. When I do the auto tracing of the statement it only shows me the DELETE ...
2
votes
3answers
77 views

2 fast SQL queries get slow when bunched as subquery on a very large table

I'm trying to use a subquery to speed up a query on a very large table right now 30 million row it most likely going to end up close to 1 billion rows. My table lnmntr is as follow Lnmntr_ndx: ...
0
votes
1answer
17 views

Oracle SQL: Why are column headers missing?

I want to understand why my columns are missing their headers. Consider this table: SQL> DESC customer; Name Null? Type ------------ -------- ------------ CUST_ID NOT ...
1
vote
2answers
55 views

How to find out why this query is in waiting state?

I have a query that has been several minutes in waiting and is not running at all. In the SQL Developer monitor session view it shows that query has been waiting for 667 seconds? How can I find ...
0
votes
0answers
35 views

Writing query for sorting based on foreign attribute

I am creating a voting system. My database looks like . Vote is a 'wrapper' for subitems. A vote has many subitems. When an action on that wrapper is performed (create/update/edit),a row is added in ...
1
vote
1answer
46 views

Consolidate multiple rows into single row

In PostgreSQL 9.5, I have a table named reports: CREATE TABLE public.reports ( id BIGSERIAL PRIMARY KEY, id_station character(11) NOT NULL, date date NOT NULL, element character(4) NOT NULL, ...
1
vote
1answer
27 views

Flat similar rows on few columns - Oracle

I wonder is there any way to flat data like this using sql query maybe you can help me The data looks like this: id name customer_code column1 column2 ----------------------------------------- 1 ...
0
votes
0answers
58 views

Get rank of users based on dynamic points

I have this db structure for my ranking system. users table id username 1 alex 2 john points table id name description value 1 foo bar 100 2 bar foo ...
1
vote
1answer
21 views

Can I grant privileges to a Postgres user on all schemas belonging to a database with one command or script?

Let's say I have 20 different schemas on a Postgres database. I create a new user theUser and now I want to grant only the SELECT and INSERT privileges for this user on all schemas. Is there an easy ...
5
votes
3answers
159 views

Calculate 40 day moving average w.r.t to a field

I have a table that stores the information about user calls in a call center. The table has a call_id, date when the call was made, actual date and time of the call, call type and a score associated ...
1
vote
1answer
75 views

Create Materialized View from foreign table

I'd like to create a simple materialized view from a table which lies in a different database. The two databases are on the same server. What do I have to add to make the query access the foreign ...
1
vote
0answers
55 views

Querying pg_buffercache: relation “pg_buffercache” does not exist

I have PostgreSQL 9.4.7 installed in LXC container on Ubuntu 14.04. For monitoring purposes, i try to execute this from my host machine: psql -qAtX -h *** -p *** -U postgres -c "select count(*) from ...
0
votes
2answers
52 views

Why can I execute this query but not see its execution plan?

Consider the following query: SELECT table_name, to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from ' ||owner ||'.' ||table_name)),'/ROWSET/ROW/C')) AS COUNT FROM ...
3
votes
3answers
52 views

SQL, category tree with multiple parents

I need to make a category tree where some leafs can have multiple parents, for exaple g \--b \--a c \--d \--a above category 'a' has two parents(g and c) My first attempt was to create the ...
0
votes
1answer
58 views

Create pivot table from SQL query results

I need to convert the results from my SCCM 2012 query about user device affinity to show multiple columns per computer name. Example of query results :- Desired results format :- Is it possible ...
1
vote
1answer
59 views

Querying dates with different timezones at db and application level

There is a created_at column of datetime type in the order table. In my application I'm storing dates to the db as datetime in UTC. When I bring them back from the db I'm presenting/formating them to ...
1
vote
1answer
56 views

Copying Partitioned table data to another table on different SQL Server

We have a partition table on Production environment with 2338117620 rows and 206 partitions. Now we have to copy only last 2 year data in Test environment. But the problem is that , Import Export ...
0
votes
2answers
42 views

SQL Server stop/exit query on error

I have the below 2 commands (insert and drop) in a query. When I run the query I get an error saying: String or binary data would be truncated. When I go to check the source table I find out that ...
-2
votes
1answer
64 views

select from where row x column A = row x+1 column B

Query select from where row x column A = row x+1 column B Data Car ID | Time 1 | Time 2 1 | 5:15 | 4:12 2 | 3:13 | 5:15 3 | 6:12 | 5:15 4 | 4:12 | 2:15 Result It ...
3
votes
1answer
74 views

Importing and exporting data from a database to another using SyBase is taking a long time

In order to update a database(Sybase Adaptive Server Anywhere 8) I'm taking all the data from one database on clients computer and using SELECT * FROM tabelName OUTPUT TO 'C:\path' FORMAT ASCII ...
0
votes
0answers
20 views

How to fetch Records matched with Comma separated value stored in table column

I have data stored in this format. topic_id tags 1 `tag1,tag2,tag3` 2 `tag1,tag4,tag5` 3 `tag2,tag4,tag5` 4 `tag6,tag7,tag2` I want Mysql Query to ...
0
votes
1answer
117 views

Speed up query that have joined tables and function calls

I have a Bootstrap Table that use server side pagination to display results from a query like this: SELECT Item.a, Item.b, Item.c, Info1.Name, Info1.Name, Info2.Name, ...
0
votes
1answer
23 views

Insert data into oracle table with loop?

How to use sql to insert line detail data into header with the output like below. line detail MO_ID SEQ_NO QTY LEVEL 121M 2 3 121M 3 9 125M 2 6 125M 3 7 125M ...
0
votes
2answers
77 views

Oracle EM 11g - query to find out CPU utilization

in OEM 11g, if you see performance, there are graph about cpu utilization, memory utilization, and disk I/O utilization. my question is: what is the script to see CPU-Utilization history ? Thank ...
0
votes
0answers
23 views

MS SQL, MySQL linked server distributed transactions

I have a linked mysql server object that I am accessing through MS SQL. I'm unable to execute any 'OpenQuery' queries to the MYSQL inside a trigger because I get the following error message: Msg ...
0
votes
1answer
61 views

Oracle equivalent of sp_rename in SQL Server

I am trying to rename a column of multiple tables in Oracle using a cursor over a table that has all the information of other table. In SQL Server I was able to do it correctly using sp_rename. I am ...
-1
votes
1answer
30 views

Can update fail if old data are invalid? [closed]

I am just starting to manage the backend for a project and still getting used to the code and database. I had this case when for one user, the value displayed in the user module was UTC+10:00, however ...
0
votes
1answer
27 views

Event to update a column won't run

I have successfully created an event in MySQL to update a column called dob in a table called auth. My problem is that the event won't run. Here is my code: DELIMITER $$ CREATE EVENT ...
1
vote
1answer
18 views

append 0 returned by DATENAME function Sql Server management studio

I want to append 0 to my minute(mi) result returned by DATENAME function of sql server management studio. select datename(mi,GETDATE()) If the current time where 12:06:33.12(hh:mi:ss) then '6' ...
0
votes
1answer
42 views

MySql - Get all children in JOIN if one child matches a condition

I have a one-to-many relationship between two tables Parent and Child. I want to get all children if one child matches a condition. Something like below: SELECT * FROM Parent p INNER JOIN Child c ON ...
3
votes
1answer
195 views

Building new version of a table and replacing the old one by rename, is it a good idea?

We have a slightly heavy table which is semi-frequently (every ~5 minute) being recreated by a procedure. I wondered if there was something that could be done in order to minimize the downtime during ...
1
vote
1answer
50 views

SQL log file growing exponentially [duplicate]

We have a SQL database on FULL back up with a client and they have NOT implemented a Log file maintenance and noticed that the log file grows exponentially, filling the 20GB they have allocated to it ...
4
votes
4answers
77 views

Select rows for which at least one row per set meets a condition

I have following table: create table test ( company_id integer not null, client_id integer not null, client_status text, unique (company_id, client_id) ); insert into test values (1, 1, '...
6
votes
1answer
186 views

Create a function to generate a column value in a specific format

I am new to databases or, in fact, programming. I want to create a function or maybe a stored procedure (if it works) to insert/create default values for a column of the users table. In this table, ...
0
votes
1answer
25 views

Get the highest value [duplicate]

I have the following data: account_id note_id size id ---------- ------- ------ -------- 48 1554 196036 28229509 48 1554 18777 28229588 48 1554 4861 28229566 ...
0
votes
1answer
59 views

Insert into multiple tables with one query

I would like to add data to at least three tables with one query. I thought of something like this: WITH ins AS ( INSERT INTO core.adr (street, "number", postal_code, city) VALUES ('test ...
1
vote
0answers
35 views

pub/sub database schema

I am having a hard time creating a pub/sub database schema with stateful metrics. Saying it aloud it sounds easy enough: "There are many organizations that will subscribe to checklists that are ...
0
votes
2answers
41 views

sp_configure: Preserve values of 'xp_cmdshell' and 'Show Advanced Options'

I dont want to disturb the server values of xp_CMDShell & Show Advanced options. The criteria is to use SQL to run a batch file. This is the script i am trying to solve it with: DECLARE @...
0
votes
1answer
35 views

Inserting two tables together with no id or unique anything

Let's assume we have two tables: table1 Name Height Age Mary 1.60 18 Mary 1.62 19 Mary 1.68 20 John 1.80 17 John 1.85 18 John 1.86 19 table2 Name Height Age Mary 1.60 18 Mary 1.62 19 Mary 1.68 20 ...
0
votes
2answers
52 views

Sorting by month by chronological occurrence when the year doesn't matter?

Given a table with a date column, and given a query that selects dates in a given range of the year (like "November 20th through February 20th"), and where the year portion of the date doesn't matter (...