Hallo,
könnte mir jemand bei der Aufgabenstellung helfen. ich komm nicht auf die richtige Lösung.
Aufgabenstellung:
How many states brought forth a president born before 1900?
Meine Lösung:
SELECT (name(state)) FROM state INNER JOIN president ON birth_year <= 1900;
-- Table: state
-- DROP TABLE state;
CREATE TABLE state
(
id serial NOT NULL,
name character varying(15) NOT NULL,
admin_id integer,
year_entered integer,
CONSTRAINT prim_key_state PRIMARY KEY (id),
CONSTRAINT state_fk1 FOREIGN KEY (admin_id)
REFERENCES administration (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT check_year_entered CHECK (year_entered >= 1600 AND year_entered <= 2100)
)
WITH (
OIDS=FALSE
);
ALTER TABLE state
OWNER TO postgres;
COMMENT ON TABLE state
IS 'States, added in year and under which president.';
-- Table: president
-- DROP TABLE president;
CREATE TABLE president
(
id serial NOT NULL,
name character varying(20) NOT NULL,
birth_year integer,
years_served smallint,
death_age smallint,
party character varying(10),
state_id_born integer,
CONSTRAINT prim_key_pres PRIMARY KEY (id),
CONSTRAINT president_fk1 FOREIGN KEY (state_id_born)
REFERENCES state (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT check_birth_year CHECK (birth_year >= 1600 AND birth_year <= 2100),
CONSTRAINT check_death_age CHECK (death_age > 20 OR death_age IS NULL)
)
WITH (
OIDS=FALSE
);
ALTER TABLE president
OWNER TO postgres;
COMMENT ON TABLE president
IS 'President name, birth year etc.';
-- Index: ipres
-- DROP INDEX ipres;
CREATE INDEX ipres
ON president
USING btree
(party COLLATE pg_catalog."default");
Eine Andere Frage: darf ich hier überhaupt Fragen zum Thema SQL Abfragen stellen oder bin ich im falschem Film?