Sheet 5 - Problem 4

--Creating table:
CREATE TABLE nobel_win (YEAR INT NOT NULL, SUBJECT VARCHAR(30) NOT NULL, WINNER VARCHAR(30) NOT NULL, COUNTRY VARCHAR(30) NOT NULL, CATEGORY VARCHAR(30) NOT NULL);

--Populating nobel_win table:
INSERT INTO nobel_win VALUES (1970, 'Physics', 'Hannes Alfven', 'Sweden', 'Scientist');
INSERT INTO nobel_win VALUES (1970, 'Physics', 'Louis Neel', 'France', 'Scientist');
INSERT INTO nobel_win VALUES (1970, 'Chemistry', 'Luis Federico Leloir', 'France', 'Scientist');
INSERT INTO nobel_win VALUES (1970, 'Physiology', 'Ulf von Euler', 'Sweden', 'Scientist');
INSERT INTO nobel_win VALUES (1970, 'Physiology', 'Bernard Katz', 'Germany', 'Scientist');
INSERT INTO nobel_win VALUES (1970, 'Literture', 'Aleksandr Solzhenitsyn', 'Russia', 'Linguist');
INSERT INTO nobel_win VALUES (1970, 'Economics', 'Paul Samuelson', 'USA', 'Economist');
INSERT INTO nobel_win VALUES (1970, 'Physiology', 'Julius Axelrod', 'USA', 'Scientist');
INSERT INTO nobel_win VALUES (1971, 'Physics', 'Dennis Gabor', 'Hungary', 'Scientist');
INSERT INTO nobel_win VALUES (1971, 'Chemistry', 'Gerhard Herzberg', 'Germany', 'Scientist');
INSERT INTO nobel_win VALUES (1971, 'Peace', 'Willy Brandt', 'Germany', 'Chancellor');
INSERT INTO nobel_win VALUES (1971, 'Literature', 'Pablo Neruda', 'Chile', 'Linguist');
INSERT INTO nobel_win VALUES (1971, 'Economics', 'Simon Kuznets', 'Russia', 'Economist');
INSERT INTO nobel_win VALUES (1978, 'Peace', 'Anwar al-Sadat', 'Egypt', 'President');
INSERT INTO nobel_win VALUES (1978, 'Peace', 'Menachem Begin', 'Israel' /*or NULL, they both mean nothing*/, 'Prime Minister');
INSERT INTO nobel_win VALUES (1987, 'Chemistry', 'Donald J. Cram', 'USA', 'Scientist');
INSERT INTO nobel_win VALUES (1987, 'Chemistry', 'Jean-Marie Lehn', 'France', 'Scientist');
INSERT INTO nobel_win VALUES (1987, 'Physiology', 'Susumu Tonegawa', 'Japan', 'Scientist');
INSERT INTO nobel_win VALUES (1994, 'Economics', 'Reinhard Selten', 'Germany', 'Economist');
INSERT INTO nobel_win VALUES (1994, 'Peace', 'Yitzhak Rabin', 'Isreal' /*اللهم دمرهم تدميرًا*/, 'Prime Minister');
INSERT INTO nobel_win VALUES (1987, 'Physics', 'Johannes Georg Bednorz', 'Germany', 'Scientist');
INSERT INTO nobel_win VALUES (1987, 'Literature', 'Joseph Brodsky', 'Russia', 'Linguist');
INSERT INTO nobel_win VALUES (1987, 'Economics', 'Robert Solow', 'USA', 'Economist');
INSERT INTO nobel_win VALUES (1994, 'Literature', 'Kenzaburo Oe', 'Japan', 'Linguist');

--Query a) Write a SQL query to show all the winners of Nobel prize in the year 1970 except the subject Physiology and Economic:
SELECT * FROM nobel_win WHERE (YEAR = 1970) AND NOT (SUBJECT = 'Physiology' OR SUBJECT = 'Economics');
SELECT * FROM nobel_win WHERE (YEAR = 1970) AND SUBJECT NOT IN ('Physiology', 'Economics');