Write SQL query to output the Maximum number and Minimum number of movies produces by diffrent actor-CodePudding

I need to write SQL query to output the Maximum number and Minimum number of movies produces by diffrent actors and actresses between year 1991 and 2001 query written . When I tried this, I got error enter image description here

The expected result is to output the maximum numer each actor or atress produces within that year range The result should look like this

When I tried this, I got error what i tried

The expected result is to output the maximum number each actor and atress produces within that year range The result should look like this

CodePudding user response：

Data type   Description
DATE    A date. Format: YYYY-MM-DD. 
DATETIME(fsp)   A date and time combination. Format: YYYY-MM-DD hh:mm:ss.. Adding DEFAULT and ON UPDATE in the column definition to get automatic initialization and updating to the current date and time
TIMESTAMP(fsp)  A hh:mm:ss. The supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. Automatic initialization and updating to the current date and time can be specified using DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP in the column definition
TIME(fsp)   A time. Format: hh:mm:ss. The supported range is from '-838:59:59' to '838:59:59'
YEAR    A year in four-digit format. Values four-digit format: 1901 to 2155, and 0000.
MySQL 8.0 does not support year in two-digit format.

CodePudding user response：

Oracle dates contain both date and time so if you're looking to test years only you need to extract that part of the date


ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

CREATE TABLE t1 (datestamp) AS
SELECT DATE '2022-10-17' FROM DUAL UNION ALL
SELECT DATE  '2022-12-03' FROM DUAL UNION ALL
SELECT DATE  '2021-04-03' FROM DUAL UNION ALL
SELECT DATE '2021-05-10' FROM DUAL;


select * from t1
where extract(YEAR from datestamp) = 2021

DATESTAMP
03-APR-2021 00:00:00
10-MAY-2021 00:00:00

select * from t1
where extract(MONTH from datestamp) = 04

DATESTAMP
03-APR-2021 00:00:00

select * from t1
where extract(DAY from datestamp) = 10

DATESTAMP
10-MAY-2021 00:00:00

If you don't want to use the extract method you can do the following. Note since dates contain time you need the 1 to include any dates on 2021-04-30 that fall between the times 00:00:00 and 23:59:59

select * from t1
WHERE datestamp >= DATE'2021-04-01' AND
datestamp <DATE'2021-04-30'  1

DATESTAMP
03-APR-2021 00:00:00

Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements for all tables involved, relevant columns only) so the people who want to help you can recreate the problem and test their ideas. Also post the exact results you want from that data, and explain why you want those results from that data. Lastly, don't post any images as they can't be cut and pasted.