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

learn more… | top users | synonyms (2)

0
votes
1answer
14 views

Extract date and time separately from a time with time zone column [closed]

I have a table in PostgreSQL 9.3 with the following column ...., created_at time with time zone NOT NULL DEFAULT now(), ..., When I do a select created_at from trails; I get this ...
-1
votes
1answer
23 views

Not sure what's wrong with this… HELP! [closed]

SELECT ROUND(months_between(SYSDATE,birthdate)/12,'YEAR') FROM f_staffs WHERE first_name ='Bob' it is giving me an "invalid number" error. Can anyone help me fix this?
3
votes
1answer
47 views

Use large set of data in multiple databases

I have been thinking about this a lot but somehow can't figure out how to deal with it in an efficient matter. Say you have a large set "X" of data that consists of multiple tables. For the sake of ...
-4
votes
1answer
63 views

How to select last row in table? [closed]

I need to select the last row from a table, which consists of two columns, firstname and lastname. I don't need any extra columns in my table, for example an auto-increment column. The row I want: ...
0
votes
2answers
58 views

How to Find SYS Privilege Users

I want to know which users have SYS and SYSDBA privilege before export/import operation. I searched a lot but I could not find.
1
vote
0answers
57 views

Configure Oracle database (first time user)

I am trying to learn how to use Oracle Database. I downloaded the Standard Edition version 12c from here: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html. I ...
3
votes
0answers
80 views

Best design to reference multiple tables from single column?

Proposed schema First and foremost, here is an example of my proposed schema to reference throughout my post: Clothes ---------- ClothesID (PK) INT NOT NULL Name VARCHAR(50) NOT NULL Color ...
3
votes
1answer
89 views

Query to get logs between Start and End Night hour

I am working on Procedure where we pass in the Night Start Hour and the Night End Hour, I need to get the Logs between these hours. So: If the StartHour = 18 and EndHour = 5, the Stored Procedure ...
3
votes
1answer
54 views

Generate linear growth report

My table has the following data: 2016-01-01 : 1 2016-01-02 : 0 2016-01-03 : 4 2016-01-04 : 1 2016-01-05 : 2 I want to generate a report (linear growth) like this: date : linear growth ...
2
votes
2answers
29 views

MySQL Trigger Query Not Deleting As Expected

I have a test database as shown below. If date is updated in tbl1, I would like for a trigger to fire that removes all entries from tbl2 where tbl1.host = tbl2.host2 AND tbl1.date = NEW.date. This is ...
1
vote
1answer
47 views

Best way to create searchable and sortable product database

I'm pretty good with coding but I'm a noob at database design. This is a common problem but I didn't see an exact answer so I just want to confirm before I start coding everything. I have a shopping ...
0
votes
1answer
18 views

Subquery uses ungrouped column “s.student_id” from outer query

I have the following tables: 1 teacher can have 1 or more students 1 student can have 1 or more homeworks I would like to know in a list of teachers, for each teacher if it has any student with ...
3
votes
1answer
28 views

MySQL Query One Table/Column With Value While Another Table/Column Empty

I set up a test database named ‘test’ that contains 2 tables, tbl1 and tbl2. In plain language, I’m trying to return the name of all entries in the package2 column of tbl2 that do not exist in the ...
6
votes
2answers
124 views

Exists subselect vs inner join?

I'm moving up to the next level of my mystery query. It looks like there's a subselect inside of an exists, but on the same table. I think this could probably be simplified with an INNER JOIN higher ...
2
votes
1answer
71 views

Alternate Name of Data Dictionary

What is the alternate name of Data Dictionary? ► Index ► Metadata ► Data ► System Catalog What I know already is, data dictionaries store all the various schema and file specifications and their ...
0
votes
0answers
5 views

Activities to be perform after Table Partitioning

I am applying partitioning on few of the tables from my DB. Need clarification of below things. Unable to find this on net. Do we need to rebuild all indexes on the partitioned table? Suppose, I am ...
0
votes
2answers
36 views

How to observe output from query in Workbench 6.3?

I am just starting to use MySQL Workbench 6.3, and I want to just run simple queries. I've created a table with the appropriate schema and everything, but I can't seem to observe the output from my ...
0
votes
1answer
61 views

Combining 2 SELECT queries and printing the results in PostgreSQL

I have 2 queries which I want to use together to produce one output result to the user. The output describes to the user the journey to take to from A to B. I keep getting a syntax error when I try to ...
0
votes
1answer
36 views

How to total additional results from alias column results in Oracle SQL?

I'm attempting to display the same number of invoices that have the same number of days between two specific dates (e.g. issuance date and payment date). I can ascertain a segment of the ...
7
votes
2answers
372 views

Average of last 4 runs of a product

I need to show the average weight of a product over its last 4 production runs. I'm not sure how best to describe it other than an example: Let's imagine I have the following table that lists a ...
0
votes
1answer
93 views

Oracle connection from Squirrel SQL

I am trying to set up Squirrel SQL for the first time on my Ubuntu Desktop. Here is the SQLPlus command I use to connect to my DB: sqlplus64 "user/pw@pashratgdb01.lex.bb.com:1876/atgpen1.lex.bb.com" ...
0
votes
1answer
47 views

What is the meaning of & in where clause

What does & indicate in below query? What is the meaning of it? select id, name from test where num & 1 = 1;
1
vote
0answers
28 views

Joining and combining MySQL tables from Informix

I previously asked this in StackOverflow, But I couldn't find a solution, so I thought I'd share my problem here too. I have an old system that uses Informix for data storage, and I'm working on ...
0
votes
0answers
64 views

DBeaver launch script from file

I'm trying to find a good alternative to SQL Developer for performing queries and scripts on Oracle instances. I find DBeaver to be an interesting tool, it has a lot of great features, BUT it seems ...
1
vote
1answer
42 views

Find if column exist in certain tables

I want to find out all tables having a column let's say test. How to obtain this? I need DBName, TableName, testcolumnexist(Yes/No), TableCreateDate and ProductName details. The tricky part is ...
2
votes
4answers
218 views

How can I swap two values from particular column in a table in Postgres?

Consider a table test contains an attributes of id and name. The values for that. Id | Name 1 | Raj 2 | Kumar From the above sample table, I know only the id, So through the Id I ...
1
vote
1answer
47 views

Check constraint

check (phone like'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]') This is to check the phone number whether it has exactly 10 digits. But I have no idea about placing number as '[0-9]'. It ...
0
votes
1answer
28 views

How to convert the column sum-function-total to dollars in Oracle SQL?

I am unable to format the below query intending to display the column-alias total re-formatted to display as dollars. Though unsuccessful, I suspect a nested solution combined with TO_CHAR, is ...
0
votes
1answer
47 views

Join a calendar table and a transaction table

The database (DB2) in my company contain two table : Warehouse_transaction contain item_id, transaction_qty (negative or positive value), transaction_date ; Calendar contain all days between 2012 ...
0
votes
0answers
21 views

How to get data using 2 different set of date range for 2 different case conditions

I am trying to get "FCR" from a date range of Jan 24th to Jan 31st and R1 & R2 from Date Range greater than Jan 24th. I tried to use case statement to create a flag called as "First" in the select ...
0
votes
1answer
35 views

How do you get a count for an aggregated row?

I am getting sums for a set of rows, There is an id from each row that relates to 0 to many rows in another table. I am having trouble getting that number into the dataset. So here is what I have ...
0
votes
0answers
11 views

Replicated data from 2 databases into 1 on a server - Don't know how to revert

I am not SQL certified enginner however company doesn't care when it comes to protect the data. I have a site with two databases: Live Test In the testDB there were several entries created in ...
0
votes
1answer
36 views

Get equivalent SQL query for any Postgres meta-command

I hope the title is self-descriptive. I want to be able somehow to translate any Postgres meta-command into its corresponding/underlying SQL query, at least to learn more about Postgres and the way it ...
0
votes
1answer
35 views

How to select recently updated row? [closed]

I have problem in selecting a row in child sp which just got updated in parent SP. MySP updates MyTestTable's MonkeyColumn, and right after it want to return newly updated row to the user by calling ...
0
votes
1answer
31 views

Determining CPU Usage of last week

We got a production issue last week (high CPU spike) on 25-Feb due to which server reboot was performed but ticket landed our DBA queue just today for analysis on CPU spike. Could you pls help me in ...
2
votes
2answers
50 views

Customer table with last order id - violate normalization?

So I'm looking at a customers table that has a relationship with an orders table. There is a trigger in place to set the last_order_id field of the customers table whenever a order it placed. Does ...
5
votes
1answer
46 views

Database stored procedure with a “preview mode”

A fairly common pattern in the database application I work with is a need to create a stored procedure for a report or utility that has a "preview mode". When such a procedure does updates, this ...
1
vote
1answer
58 views

How to mount a query to join by searching by last date

I have the following tables: TB_PRODUCTS ID COD_PROD Name =============================================== 1 100 Video monitor 2 101 Keyboard 3 102 Mouse ...
1
vote
1answer
55 views

Store values in select as a temp variable and reuse them

I am trying to clean up a query that is ugly but does the job. I am wondering if it is possible to create a temp variable inside of a SELECT statement. I have Googled and not found any help. Basically ...
2
votes
1answer
32 views

Handling External DB without PK

How can I handle a table given to me from an outside DB that does not have a PK? Or even an ID field that marks the record specifically. I am using SQL Server. I have a table that has come to me that ...
0
votes
1answer
23 views

Deleting aged rows from postgresql table which don't have related entries in another table

I maintain a website where users post interesting links, and other users leave replies under the said links. I want to delete aged link entries under which no one replied. The postgresql database is ...
0
votes
3answers
88 views

Inserts through Stored Procedure 6 time slower than direct insert

We have recently created a StoredProcedure in order to test the insert speed between a direct query into SQL Server without parametrization (New plan for each different insert) and insert through ...
3
votes
1answer
73 views

In a WordPress/WooCommerce table the prices are hosed. How to realign them? (a key=value structure)

We had in our WordPress/Woocommerce table (hence mySQL) ab_postmeta the sale (discount) prices for some products. We thought that reseting the sale prices via SQL query (and not via WordPress ...
1
vote
1answer
32 views

Is it possible to write a select * view that includes new columns added to table?

I would like to write a view that is essentially something simple select a + b, * from table_name and will always truly include all columns in table_name, even when I add or remove them. If I do the ...
-2
votes
1answer
100 views

Display top 5 sales and bottom 5 sales in the same query

I want to display the top 5 sales and the bottom 5 sales in the same query in Oracle, not in PL/SQL.
1
vote
1answer
25 views

Deleting aged postgresql rows with foreignkey constraints

I have a postgresql table where I want to delete rows beyond a certain age. But the catch is that some foreign key constraints would be violated For instance, if I run DELETE FROM links_reply WHERE ...
0
votes
1answer
79 views

Compare two databases and get the differences

I want to between two databases and get the differences: columns: in the first table the column name is varchar2(12), in the second table the column name is varchar2(20). Or I have an additional ...
1
vote
2answers
47 views

An aggregate may not appear in the WHERE clause

I'm attempting to execute the following query to return a data set where the Updated Date is the most recent. I'm thinking my joins are causing the issue, but I'm not sure, and have not made any ...
-1
votes
1answer
26 views

How can i return the total of products of 3 months in MySQL? [closed]

How can i return the total of products of 3 months (months now - two months before ) eg : month now is February i have to return total since December until February. This's my query : SELECT ...
0
votes
1answer
36 views

how to access a table containing . in its name

I have created a database schema as "mytest.us" and inside this schema, I have created a table "a.b.c". When I do select * from select * from "mytest.us"."a.b.c"; year | mydata ------+-------- ...