Question 21956 in Mysql Question posted in
The official MySQL documentation can be found here.

Write SQL query to output the Maximum number and Minimum number of movies produces by diffrent actors and actresses between year 1991 and 2001 – Mysql

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

Tags:

2

Answers


  1. 0 
    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.
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search