Because of using Hibernate, I cannot write a JPA query using exists like this (I translated it to pure SQL query): So I have to write an alternative query to check existance of activated accounts. After reading several suggestions on this website, I see a replacement: My question is: Is this bad for performan…
Tag: sql
How to retrieve the earliest date from columns that have different dimensions?
DATA Explanation I have two data tables, one (PAGE VIEWS) which represents user events (CV 1,2,3 etc) and associated timestamp with member ID. The second table (ORDERS) represents the orders made – event time & order value. Membership ID is available on each table. Table 1 – PAGE VIEWS (1,000 …
How the SQL query with two left join works?
I need help in understanding how left join is working in below query. There are total three tables and two left joins. So my question is, the second left join is between customer and books table or between result of first join and books table? Answer The second join statement specifies to join on s.book_id = …
How to create the correct trigger instead of insert or update? ORACLE. Trigger. pl/sql
Help create a trigger for a non-updatable view. The tables look like this. The view itself looks like this I tried to create a trigger like this. But for some reason it is not created.( Error at line 86: PL/SQL: SQL Statement ignored Error at line 94: PL/SQL: ORA-00917: missing comma) it seems to me that I we…
How to select a row, lock it, update it, then select again?
I have a table with these 3 columns: task (string) status (string) date (datetime) I want to write a query that does the following: Selects the first row WHERE status != “In-Progress” Sorted by Date (oldest first), and Locks it – so other computers running this query concurrently can’t…
crosstab in PostgreSQL
I have the following tables: and How can I query the database so that it returns the table where n ranges over the rows of the table sensor and ‘abbrv $i’ is replaced by the corresponding value in the table sensor? The following query works up to a certain extent: I need to know how many rows ther…
Create query builder that the source table (FROM) is a join table in TypeORM
I’m trying to implement the following SQL in TypeORM using QueryBuilder: The entities are: When you create a query builder you have to use some entity or table but the join table is “hidden” by TypeORM I know I can replace the inner join table order and it will solve the problem but I’…
What’s wrong with this PL/SQL Trigger?
I have this table, and I want to create a trigger on Magazine, that verifies “after insert” if the name of the Magazine inserted is either Vogue or People. If it’s not one of them, it gets deleted. Table: MAGAZINE (ISBN, MAG_NOM, PRIX_Mois); My trigger: But the problem is my teacher told me:…
ID appears twice in query when multiplied with different Prices
I am trying to get the top 5 stations by Sales, but I ran into the problem that one station appears twice if multiplied by a different price. This is my query: Which gives me the following result: b_id START_PRICE 7 75 6 50 4 30 1 16 1 15 What i need though is: b_id START_PRICE 7 75 6
Extract last day of the month from MMM(M)YYYY
How can I extract the last day of the month from dates like DEC2009, APR2013, AUG2008, NOV2017, JUL2014 etc. I’ve tried datepart, convert, cast, month, year and I keep getting in a mess with type conversion errors. Answer Use try_convert and eomonth()