For each player whose number is less than 60, get the number of years between the year in which that player jo : Year « Date Time « SQL / MySQL






  1. SQL / MySQL
  2. Date Time
  3. Year

For each player whose number is less than 60, get the number of years between the year in which that player jo

     
ined the club  and that of player 100.
mysql>
mysql> CREATE TABLE PLAYERS
    -> (
    ->     PLAYERNO INTEGER NOT NULL,
    ->     NAME CHAR(15) NOT NULL,
    ->     INITIALS CHAR(3) NOT NULL,
    ->     BIRTH_DATE DATE ,
    ->     SEX CHAR(1) NOT NULL,
    ->     JOINED SMALLINT NOT NULL,
    ->     STREET VARCHAR(30) NOT NULL,
    ->     HOUSENO CHAR(4) ,
    ->     POSTCODE CHAR(6) ,
    ->     TOWN VARCHAR(30) NOT NULL,
    ->     PHONENO CHAR(13) ,
    ->     LEAGUENO CHAR(4) ,
    ->     PRIMARY KEY (PLAYERNO)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO PLAYERS VALUES (2, 'Everett', 'R', '1948-09-01', 'M', 1975, 'Stoney Road','43', '3575NH', 'Stratford'
, '070-237893', '2411');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO PLAYERS VALUES (6, 'Parmenter', 'R', '1964-06-25', 'M', 1977, 'Haseltine Lane','80', '1234KK', 'Strat
ford', '070-476537', '8467');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO PLAYERS VALUES (7, 'Wise', 'GWS', '1963-05-11', 'M', 1981, 'Edgecombe Way','39', '9758VB', 'Stratford
', '070-347689', NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO PLAYERS VALUES (8, 'Newcastle', 'B', '1962-07-08', 'F', 1980, 'Station Road','4', '6584WO', 'Inglewoo
d', '070-458458', '2983');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO PLAYERS VALUES (27, 'Collins', 'DD', '1964-12-28', 'F', 1983, 'Long DRay','804', '8457DK', 'Eltham',
'079-234857', '2513');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO PLAYERS VALUES (28, 'Collins', 'C', '1963-06-22', 'F', 1983, 'Old Main Road','10', '1294QK', 'Midhurs
t', '010-659599', NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO PLAYERS VALUES (39, 'Bishop', 'D', '1956-10-29', 'M', 1980, 'Eaton Square','78', '9629CD', 'Stratford
', '070-393435', NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO PLAYERS VALUES (44, 'Baker', 'E', '1963-01-09', 'M', 1980, 'Lewis Street','23', '4444LJ', 'Inglewood'
, '070-368753', '1124');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO PLAYERS VALUES (57, 'Brown', 'M', '1971-08-17', 'M', 1985, 'Edgecombe Way','16', '4377CB', 'Stratford
', '070-473458', '6409');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO PLAYERS VALUES (83, 'Hope', 'PK', '1956-11-11', 'M', 1982, 'Magdalene Road','16A', '1812UP', 'Stratfo
rd', '070-353548', '1608');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO PLAYERS VALUES (95, 'Miller', 'P', '1963-05-14', 'M', 1972, 'High Street','33A', '5746OP', 'Douglas',
 '070-867564', NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO PLAYERS VALUES (100, 'Parmenter', 'P', '1963-02-28', 'M', 1979, 'Haseltine Lane','80', '6494SG', 'Str
atford', '070-494593', '6524');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO PLAYERS VALUES (104, 'Moorman', 'D', '1970-05-10', 'F', 1984, 'Stout Street','65', '9437AO', 'Eltham'
, '079-987571', '7060');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO PLAYERS VALUES (112, 'Bailey', 'IP', '1963-10-01', 'F', 1984, 'Vixen Road','8', '6392LK', 'Plymouth',
 '010-548745', '1319');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql>
mysql> SELECT PLAYERNO, JOINED -
    -> (SELECT JOINED
    -> FROM PLAYERS
    -> WHERE PLAYERNO = 100)
    -> FROM PLAYERS
    -> WHERE PLAYERNO < 60;
+----------+------------------------------------------------------------+
| PLAYERNO | JOINED -
(SELECT JOINED
FROM PLAYERS
WHERE PLAYERNO = 100) |
+----------+------------------------------------------------------------+
|        2 |                                                         -4 |
|        6 |                                                         -2 |
|        7 |                                                          2 |
|        8 |                                                          1 |
|       27 |                                                          4 |
|       28 |                                                          4 |
|       39 |                                                          1 |
|       44 |                                                          1 |
|       57 |                                                          6 |
+----------+------------------------------------------------------------+
9 rows in set (0.00 sec)

mysql>
mysql>
mysql> drop table players;
Query OK, 0 rows affected (0.00 sec)








Related examples in the same category

1.Date function: YEAR
2.Retrieve year from a date
3.Use YEAR in where clause
4.Determining the Number of Records by Day, Month, and Year
5.To find the earliest birthday within the calendar year, sort by the month and day of the birth values
6.Extract the year part of the reference date and use normal arithmetic to add 10, 20, and 40 to it
7.The following query shows two ways to determine the date for Christmas two years hence.
8.Performing Leap Year Calculations
9.Another way to compute a year's length is to compute the date of the last day of the year and pass it to DAYOF
10.Using Leap Year Tests for Month-Length Calculations
11.February 29 of leap years and March 1 of non-leap years appear to be the same day:
12.Extract the year from a date value by using the YEAR() function: YEAR()
13.Calculate a numerical value for the day, as it falls in the year: DAYOFYEAR()
14.Add a YEAR column type
15.%m returns the month (01-12), %d returns the day (01-31), and %Y returns the year in four digits.
16.Use the YEAR() function
17.For each player, find the player number, the year in which he or she joined the club, and the player's age gro
18.Find the player number, the year in which he or she joined the club, the town where he or she lives, and a cla
19.Get the payment number and the year of each penalty paid after 1980.
20.Get the penalties that were paid between Christmas 1982 (December 25) and New Year's Eve.
21.Get the numbers of the players who were born in the same year as player 27.
22.Get year value from date type and compare
23.Get day name, month name and day of year
24.Get the year of a date and compare
25.Get the Year value from subquery
26.Compare the year value in where clause
27.Year value in
28.Distinct year value
29.Group by year
30.HAVING MAX(YEAR(PAYMENT_DATE)) = 1984
31.Check year value
32.ON SCHEDULE EVERY 1 YEAR
33.WHERE clause uses only part of the date column in the comparisons:
34.Performing Date Calculations
35.Calculate an age as of the beginning 1975 for someone born on 1965-03-01.
36.How old are the Smith children today?
37.Determining Ages in Months