Difference between revisions of "Select the oldest person"

From SQLZOO
Jump to: navigation, search
(Created page with "How can I select the oldest person in the table PERSON by birthday? <div class='ht'> <div class=params>schema:scott</div> <div> I have a table <tt>PERSON(<u>personID</u>, name...")
 
 
(2 intermediate revisions by one user not shown)
Line 1: Line 1:
 
How can I select the oldest person in the table PERSON by birthday?
 
How can I select the oldest person in the table PERSON by birthday?
 
<div class='ht'>
 
<div class='ht'>
<div class=params>schema:scott</div>
+
<div class=params>schema:gisq</div>
 
<div>
 
<div>
I have a table <tt>PERSON(<u>personID</u>,
+
I have a table  
 +
 
 +
<tt>PERSON(<u>personID</u>,
 
name,
 
name,
 
sex,
 
sex,

Latest revision as of 16:23, 17 July 2012

How can I select the oldest person in the table PERSON by birthday?

schema:gisq

I have a table

PERSON(personID, name, sex, birthday, placeOfBirth)

Now I would like to SELECT the oldest person in the table by birthday.

Answer: Use a nested query to find the earliest birthday.

 
 
-- Set up the problem
DROP TABLE people;
CREATE TABLE people(personId INTEGER PRIMARY KEY,
                    name VARCHAR(20),
                    sex CHAR(1),
                    birthday DATE,
                    placOfBirth VARCHAR(20));
INSERT INTO people VALUES
  (1,'Oliver','M','25 May 1985','Bedford');
INSERT INTO people VALUES
  (2,'Andrew','M','20 May 1962','Hong Kong');
 
-- Here is the answer
SELECT * FROM people
WHERE birthday = (SELECT MIN(birthday) FROM people)
-- Set up the problem
DROP TABLE people;
CREATE TABLE people(personId INTEGER PRIMARY KEY,
                    name VARCHAR(20),
                    sex CHAR(1),
                    birthday DATE,
                    placOfBirth VARCHAR(20));
INSERT INTO people VALUES
  (1,'Oliver','M','25 May 1985','Bedford');
INSERT INTO people VALUES
  (2,'Andrew','M','20 May 1962','Hong Kong');
 
-- Here is the answer
SELECT * FROM people
WHERE birthday = (SELECT MIN(birthday) FROM people)
-- Set up the problem
DROP TABLE people;
CREATE TABLE people(personId INTEGER PRIMARY KEY,
                    name VARCHAR(20),
                    sex CHAR(1),
                    birthday DATE,
                    placOfBirth VARCHAR(20));
INSERT INTO people VALUES
  (1,'Oliver','M','25 May 1985','Bedford');
INSERT INTO people VALUES
  (2,'Andrew','M','20 May 1962','Hong Kong');
 
-- Here is the answer
SELECT * FROM people
WHERE birthday = (SELECT MIN(birthday) FROM people)
-- Set up the problem
DROP TABLE people;
CREATE TABLE people(personId INTEGER PRIMARY KEY,
                    name VARCHAR(20),
                    sex CHAR(1),
                    birthday DATE,
                    placOfBirth VARCHAR(20));
INSERT INTO people VALUES
  (1,'Oliver','M','25 May 1985','Bedford');
INSERT INTO people VALUES
  (2,'Andrew','M','20 May 1962','Hong Kong');
 
-- Here is the answer
SELECT * FROM people
WHERE birthday = (SELECT MIN(birthday) FROM people)
-- Set up the table
DROP TABLE people;
CREATE TABLE people(personId INTEGER PRIMARY KEY,
                    name VARCHAR(20),
                    sex CHAR(1),
                    birthday DATE,
                    placOfBirth VARCHAR(20));
INSERT INTO people VALUES
  (1,'Oliver','M','1985-05-25','Bedford');
INSERT INTO people VALUES
  (2,'Andrew','M','1962-05-20','Hong Kong');
 
--Here is the answer
SELECT * FROM people
 ORDER BY birthday ASC
 LIMIT 1
-- Set up the problem
DROP TABLE people;
CREATE TABLE people(personId INTEGER PRIMARY KEY,
                    name VARCHAR(20),
                    sex CHAR(1),
                    birthday DATE,
                    placOfBirth VARCHAR(20));
INSERT INTO people VALUES
  (1,'Oliver','M','25 May 1985','Bedford');
INSERT INTO people VALUES
  (2,'Andrew','M','20 May 1962','Hong Kong');
 
-- Here is the answer
SELECT * FROM people
WHERE birthday = (SELECT MIN(birthday) FROM people)
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense