DB-Fiddle Exptected Result: I have a huge database and need to extract some data from it for a case study. The problem is that I need to extract the full year of the data because I want to be able to conduct a monthly analysis in the case study. Therefore, I can not limit the extract with dates or LIMIT.
Tag: sql
Getting the latest status of user before every calendar Date in SQL
I have 2 tables as below: Table 1: UserID DateEntered Status 1 29/03/2021 a 1 30/03/2021 b 1 31/03/2021 a 2 01/04/2021 a 3 02/04/2021 c 2 03/04/2021 c 3 04/04/2021 a 4 05/04/…
SQL Query to find a group of rows having all of them a certain value in one of its fields
Given table: +—+——-+——+—+ |id | attr | fker |did| +—+——-+——+—+ |1 | attr1 | 5 | 1| |2 | attr1 | 17 | 1| |3 | attr1 | 3 | 2| |4 | attr2 | 31 | 2| |5 | attr2 |…
Failed to add the foreign key constraint 2
CREATE TABLE Program( name VARCHAR(64) NOT NULL, abbreviation VARCHAR(16) NOT NULL, PRIMARY KEY (name) ); CREATE TABLE Student( ssn INTEGER CHECK (ssn > 0) NOT NULL, name …
Select only products that are active via query from two tables
I have a query that provides me with a list of productId:s that I then match with a products table.. But how would I adjust the query to give me only productId:s that are active (active=1)? Answer You can use join:
Populating a recordset between two positions in a table using MYSQL
I’m trying to write a query that will only return a recordset between 2 positions in a table For example, using Excel to demonstrate, populate a recordset between positions 3 and 7 I thought about using BETWEEN with the ID field, but there may be times that a record has been deleted and the ID field aut…
Subquery – define select list. The subquery returns the same value for each result line
do you know what I have done wrong? My subquery is returining the same value for all lines. Thanks a lot in advance for your any advice! đŸ™‚ Answer I think you just want conditional aggregation:
Counter Number of Times in State
I want to add a column which tells me the iteration count of how many times we’ve been in a state, ordered by date. We see we’re in state 1 for the first time, state 2 for the first time, then BACK to state 1 (iteration is now 2), the Iteration stays the same until we change state. I’d like
If I delete a record from a table it is deleted at many other places
I am working on a database built by the previous team. I have to delete certain records from a table (Example shown below). While doing this process, some records from the other tables with the same id is getting deleted. Could someone help how to overcome this problem? Answer In SQL Server, there is statemen…
Is there a way to parse csv string with escapings via HQL/SQL?
I have a problem parsing csv-formatted data that is stored in a Hive table column that is loaded into PostgreSQL DB afterwards. What I need to do is to retrieve some fields from there, however, if a comma is enquoted, it should be treated as a part of data to retrieve; on top of that, quotes can be escaped th…