I have a store procedure that will Update maximum 500 rows status from 0 to 1 Return those rows to program via cursor Here is my store procedure code PROCEDURE process_data_out ( o_rt_cursor …
Mysql subquery in where clause that returns comma separated value
I’m not really good at subqueries, here’s the sample tables that I have. table customers ===================== id | name | order_ids 1 | John | 1,2 table orders ===================== id | name 1 …
Using Over() with aggregate functions while calculating percentage
I am writing a query to print Total Students in each department in a college, I also want to print the percentage of those students in each department out of Total students in the college. Having issues with calculating percentage, the above query throwing an error dbo.Student.Id is invalid in the select list because it is not contained in either
Handling of generate_series() in queries with date or timestamp with / without time zone
I have a query to generate a report based on a date series that is grouped by date and employee_id. The date should be based on a particular time zone, in this case ‘Asia/Kuala_Lumpur’. But this can change depending on where the user’s time zone is. Definition and sample data for table attendance: Here is a fiddle to test. The
Check for overlapped dates on any row of a table Oracle SQL
I have the following schema: CREATE TABLE EPOCA ( ID INT CONSTRAINT PK_EPOCA PRIMARY KEY, NOME VARCHAR(250), DATA_INI DATE CONSTRAINT NN_EPOCA_DATA_INI NOT NULL, …
Calculate age from date of birth with formatting
I would like to calculate the age from the date of birth stored as a string using the format in the comments in the code. Here’s my code: The result I get is the following: I guess there’s something wrong with the year but I can’t figure it out! Answer The error is in this line: You should not call
Can you use ORDER BY (column) LIMIT with a secondary column?
I want to find out the Top 10 rented movies in the sakila db. It must be sorted DESC after total_rentals and secondary after film title. My solution looks like this and works so far without the film …
Show sales per employee (staff) and year in sakila db?
I’m kind of stuck on an excercise concerning the sakila db. I want to show sales per employee (staff) and year in mySQL and I’m failing at using the staff_id’s correctly. This is all I have so far: …
Create temporary table to display missing months from query
I am trying to create a simple MySQL query to get the sales data for each month of the year, grouped by monthname and SalesName. CREATE TEMPORARY TABLE Cal (monthid INT, monthname VARCHAR(50)); INSERT …
Date Format Convert
I am dealing with sql DB where the date is recorded in this format ‘2020-348T08:55’. I need to convert this to datetime. Please guide if there is possible way to do that? Answer With string and date/time functions extract the year and days to create a DATETIME without the time and finally add the time part: If the days part