Homework 3

an anonymous user · October 14, 2021 · SQL
-- create a table
CREATE TABLE Author (
  first TEXT,
  last TEXT PRIMARY KEY,
  country TEXT,
  born integer,
  FOREIGN KEY (last) REFERENCES NobleWinner(author)
);

CREATE TABLE Book (
  Title TEXT PRIMARY KEY,
  author TEXT,
  year integer,
  FOREIGN KEY (author) REFERENCES Author(last)
);

CREATE TABLE NobleWinner (
  author TEXT PRIMARY KEY,
  year integer,
  FOREIGN KEY (author) REFERENCES Author(last)
);

-- insert some values
INSERT INTO Author VALUES ('Haruki', 'Murakami', 'Japan', 1949);
INSERT INTO Author VALUES ('Kindzaburo', 'Oe', 'Japan', 1935);
INSERT INTO Author VALUES ('Yasunari', 'Kawabata', 'Japan', 1899);
INSERT INTO Author VALUES ('Yukio', 'Mishima', 'Japan', 1925);
INSERT INTO Author VALUES ('Ivo', 'Andric', 'Yugoslavia', 1892);
INSERT INTO Author VALUES ('Mesa', 'Selimovic', 'Yugoslavia', 1892);
INSERT INTO Author VALUES ('Danilo', 'Kis', 'Yugoslavia', 1910);
INSERT INTO Author VALUES ('Julio', 'Cortazar', 'Argentina', 1935);
INSERT INTO Author VALUES ('Mario', 'Vargas Llosa', 'Peru', 1936);
INSERT INTO Author VALUES ('Jorge', 'Borges', 'Argentina', 1899);
INSERT INTO Author VALUES ('Gabriel Garcia', 'Marquez', 'Colombia', 1927);

INSERT INTO Book VALUES ('Captain Pantoja and the Special Service', 'Vargas Llosa', 1978);
INSERT INTO Book VALUES ('The City and the Dogs', 'Vargas Llosa', 1963);
INSERT INTO Book VALUES ('Confessions of a Mask', 'Mishima', 1949);
INSERT INTO Book VALUES ('Patriotism', 'Mishima', 1961);
INSERT INTO Book VALUES ('Acts of Worship', 'Mishima', 1965);
INSERT INTO Book VALUES ('The Bridge on the Drina', 'Andric', 1945);
INSERT INTO Book VALUES ('The Damned Yard', 'Andric', null);
INSERT INTO Book VALUES ('Death and the Dervish', 'Selimovic', 1966);
INSERT INTO Book VALUES ('Axolotl', 'Cortazar', null);
INSERT INTO Book VALUES ('The Library of Babel', 'Borges', 1941);
INSERT INTO Book VALUES ('Labyrinths', 'Borges', null);

INSERT INTO NobleWinner VALUES ('Andric', 1961);
INSERT INTO NobleWinner VALUES ('Kawabata', 1968);
INSERT INTO NobleWinner VALUES ('Vargas Llosa', 2010);
INSERT INTO NobleWinner VALUES ('Marquez', 1982);
INSERT INTO NobleWinner VALUES ('Oe', 1994);

-- fetch some values
SELECT NobleWinner.author FROM NobleWinner INNER JOIN Author ON Author.country = 'Yugoslavia';
-- SELECT * FROM Author;
-- SELECT * FROM NobleWinner;

Comments

Please sign up or log in to contribute to the discussion.