Question

1 Approved Answer

Posted on Aug 19, 2024

a2.sql ----------------------------------------------------------------------------- -- Add below your SQL statements. -- You can create intermediate views (as needed). Remember to drop these views after you have populated

image text in transcribed a2.sql -----------------------------------------------------------------------------

-- Add below your SQL statements. -- You can create intermediate views (as needed). Remember to drop these views after you have populated the result tables. -- You can use the "\i a2.sql" command in psql to execute the SQL commands in this file.

-- Query 1 statements

-- Query 2 statements

-- Query 3 statements

-- Query 4 statements

-- Query 5 statements

-- Query 6 statements

-- Query 7 statements

-- Query 8 statements

-- Query 9 statements

-- Query 10 statements

a2.sql ------------------------------------------------------------------------------------------------

a2.ddl ------------------------------------------------------------------------------------------------

DROP SCHEMA IF EXISTS A2 CASCADE; CREATE SCHEMA A2; SET search_path TO A2;

DROP TABLE IF EXISTS country CASCADE; DROP TABLE IF EXISTS language CASCADE; DROP TABLE IF EXISTS religion CASCADE; DROP TABLE IF EXISTS hdi CASCADE; DROP TABLE IF EXISTS ocean CASCADE; DROP TABLE IF EXISTS neighbour CASCADE; DROP TABLE IF EXISTS oceanAccess CASCADE; DROP TABLE IF EXISTS query1 CASCADE; DROP TABLE IF EXISTS query2 CASCADE; DROP TABLE IF EXISTS query3 CASCADE; DROP TABLE IF EXISTS query4 CASCADE; DROP TABLE IF EXISTS query5 CASCADE; DROP TABLE IF EXISTS query6 CASCADE; DROP TABLE IF EXISTS query7 CASCADE; DROP TABLE IF EXISTS query8 CASCADE; DROP TABLE IF EXISTS query9 CASCADE; DROP TABLE IF EXISTS query10 CASCADE;

-- The country table contains all the countries in the world and their facts. -- 'cid' is the id of the country. -- 'name' is the name of the country. -- 'height' is the highest elevation point of the country. -- 'population' is the population of the country. CREATE TABLE country ( cid INTEGER PRIMARY KEY, cname VARCHAR(20) NOT NULL, height INTEGER NOT NULL, population INTEGER NOT NULL); -- The language table contains information about the languages and the percentage of the speakers of the language for each country. -- 'cid' is the id of the country. -- 'lid' is the id of the language. -- 'lname' is the name of the language. -- 'lpercentage' is the percentage of the population in the country who speak the language. CREATE TABLE language ( cid INTEGER REFERENCES country(cid) ON DELETE RESTRICT, lid INTEGER NOT NULL, lname VARCHAR(20) NOT NULL, lpercentage REAL NOT NULL, PRIMARY KEY(cid, lid));

-- The religion table contains information about the religions and the percentage of the population in each country that follow the religion. -- 'cid' is the id of the country. -- 'rid' is the id of the religion. -- 'rname' is the name of the religion. -- 'rpercentage' is the percentage of the population in the country who follows the religion. CREATE TABLE religion ( cid INTEGER REFERENCES country(cid) ON DELETE RESTRICT, rid INTEGER NOT NULL, rname VARCHAR(20) NOT NULL, rpercentage REAL NOT NULL, PRIMARY KEY(cid, rid));

-- The hdi table contains the human development index of each country per year. (http://en.wikipedia.org/wiki/Human_Development_Index) -- 'cid' is the id of the country. -- 'year' is the year when the hdi score has been estimated. -- 'hdi_score' is the Human Development Index score of the country that year. It takes values [0, 1] with a larger number representing a higher HDI. CREATE TABLE hdi ( cid INTEGER REFERENCES country(cid) ON DELETE RESTRICT, year INTEGER NOT NULL, hdi_score REAL NOT NULL, PRIMARY KEY(cid, year));

-- The ocean table contains information about oceans on the earth. -- 'oid' is the id of the ocean. -- 'oname' is the name of the ocean. -- 'depth' is the depth of the deepest part of the ocean CREATE TABLE ocean ( oid INTEGER PRIMARY KEY, oname VARCHAR(20) NOT NULL, depth INTEGER NOT NULL);

-- The neighbour table provides information about the countries and their neighbours. -- 'country' refers to the cid of the first country. -- 'neighbor' refers to the cid of a country that is neighbouring the first country. -- 'length' is the length of the border between the two neighbouring countries. CREATE TABLE neighbour ( country INTEGER REFERENCES country(cid) ON DELETE RESTRICT, neighbor INTEGER REFERENCES country(cid) ON DELETE RESTRICT, length INTEGER NOT NULL, PRIMARY KEY(country, neighbor));

-- The oceanAccess table provides information about the countries which have a border with an ocean. -- 'cid' refers to the cid of the country. -- 'oid' refers to the oid of the ocean. CREATE TABLE oceanAccess ( cid INTEGER REFERENCES country(cid) ON DELETE RESTRICT, oid INTEGER REFERENCES ocean(oid) ON DELETE RESTRICT, PRIMARY KEY(cid, oid));

-- The following tables will be used to store the results of your queries. -- Each of them should be populated by your last SQL statement that looks like: -- INSERT INTO queryX (SELECT )

CREATE TABLE query1( c1id INTEGER, c1name VARCHAR(20), c2id INTEGER, c2name VARCHAR(20) );

CREATE TABLE query2( cid INTEGER, cname VARCHAR(20) );

CREATE TABLE query3( c1id INTEGER, c1name VARCHAR(20), c2id INTEGER, c2name VARCHAR(20) );

CREATE TABLE query4( cname VARCHAR(20), oname VARCHAR(20) );

CREATE TABLE query5( cid INTEGER, cname VARCHAR(20), avghdi REAL );

CREATE TABLE query6( cid INTEGER, cname VARCHAR(20) );

CREATE TABLE query7( rid INTEGER, rname VARCHAR(20), followers INTEGER );

CREATE TABLE query8( c1name VARCHAR(20), c2name VARCHAR(20), lname VARCHAR(20) );

CREATE TABLE query9( cname VARCHAR(20), totalspan INTEGER );

CREATE TABLE query10( cname VARCHAR(20), borderslength INTEGER );

a2.ddl -------------------------------------------------------------------------------

Instructions 1. Read this assignment thoroughly before you proceed. Failure to follow instructions can affect your grade. 2. Download the database schema a2.ddl from the course website. 3. Download the file a2.sql from the course website. 4. Download the java skeleton file Assignment2.java from the course website. 5. Submit your work electronically using UNIX submit. Your submission must include the following files: a) a2.sql your queries for the interactive SQL part of the assignment (can include any view creation statement). If you define any views for a question, you must drop them after you have populated the answer table for that question. your java code for the embedded SQL part of the assignment. Be careful to submit the .java file (not the .class file.). To get you started, we provide a skeleton of this file that you must download from the assignment webpage. b) Assignment2.java Interactive SQL Queries [50 marks] In this section, you must edit the file a2.sql and add SQL statements that can be run in psql on Prism machines. Your SQL statements can create views and queries that will populate the result tables query1 query2, ..., query10 with tuples that satisfy the questions below. In order to ensure that everything is run in the correct order (by the markers or the automarker), you should add all your SQL statements in the file a2.sql that we have provided. This file can be read and executed using the psql command \i You can assume that the a2.ddl file has been read and executed in psql, before your a2.sql file is executed. Follow these rules: The output of each query must be stored in a result table. We provide the definitions of these tables in the a2.ddl file (query1, query2, ..., query10) For each of the queries below, your final statement should populate the respective answer table (queryX) with the correct tuples. It should look something like INSERT INTO queryX (SELECT ...) where X is the correct index [1,...,10] . In order to answer each of the questions, you are encouraged to create virtual views that can keep intermediate results and can be used to build your final INSERT INTO queryX statement. Do not create actual tables. Remember that you have to drop the views you have created, after each INSERT INTO queryX statement (i.e., after you have populated the result table) . Your tables must match the output tables specified for each query. The attribute names must be identical to those specified in italics, and they must be in the specified order. Also, make sure to sort the results according to the attributes and ordering we specify in each question .We are not providing a sample database to test your answers, but you are encouraged to create one We will test the validity of your queries against our own test database All of your statements must run on PostgreSQL on the Prism machines, so be sure to populate your tables with test data and run all your statements on Prism prior to submission e NOTE: Failure to do follow the instructions may cause your queries to fail when (automatically) tested, and you will lose marks Express the following queries in SQL. 1. [5 marks] For each country, find its neighbor country with the highest elevation point. Report the id and name of the country and the id and name of its neighboring country Output Table: query1 Attributes: clid c1name country name) c2id c2name (neighbor country name) VARCHAR(20)] c1name (country id) (neighbor country ic) ASC INTEGER] [VARCHAR(20)] [INTEGER] Order by 2. [5 marks] Find the landlocked countries. A landlocked country is a country entirely enclosed by land (e.g., Switzerland). Report the id(s) and name(s) of the landlocked countries Output Table: query2 Attributes cid (landlocked country id[INTEGER] (landlocked country name) VARCHAR(20)] ASC cname order by cname 3. [5 marks] Find the landlocked countries which are surrounded by exactly one country. Report the id and name of the landlocked country, followed by the id and name of the country that surrounds it Output Table: query3 Attributes clid (landlocked country id) INTEGER] c1name (landlocked country name) VARCHAR(20)] c2id c2name (surrounding country name) [VARCHAR(20)] c1name (surrounding country id) INTEGER] Order by ASC 4. [5 marks] Find the accessible ocean(s) of each country. An ocean is accessible by a country if either the country has an ocean coastline itself (direct access to the ocean) or the country is neighboring another country that has an ocean coastline (indirect access). Report the name of the country and the name of the accessible ocean(s) Output Table: query4 Attributes cname country name) VARCHAR(20)] (ocean name) ASC DESC [VARCHAR(20)] oname cname oname Order by 5. [5 marks] Find the top-10 countries with the highest average Human Development Index (HDI) over the 5-year period of 2009-2013 (inclusive) Output Table: query5 Attributes cid cname avghdi ghdli (country id) (country name) (country's average HDI) DESC INTEGER] [VARCHAR(20)] [REAL] Order by 6. 5 marks] Find the countries that their Human Development Index (HDI) is constantly increasing over the 5-year period of 2009-2013 (inclusive). Constantly increasing means that from year to year there is a positive change (increase) in the country's HDI Output Table: query6 Attributes cid (country id) (country name) ASC INTEGER] [VARCHAR(20)] cname Order by:cname 7. [5 marks] Find the total number of people in the world that follow each religion. Report the id of the religion, the name of the religion and the respective number of people that follow it. Output Table: query7 Attributes rid name followers (religion id) (religion name) (number of followers) INTEGER] [VARCHAR(20)] INTEGER Order by: followers DESC 8. [5 marks] Find all the pairs of neighboring countries that have the same most popular language. For example, is one example tuple because in both countries, English is the most popular language; can be another tuple, and so on. Report the names of the countries and the name of the language Output Table: query8 Attributes c1name(country name) [VARCHAR(20)] c2name Iname Iname c1name (neighboring country name) [VARCHAR(20)] (language name) ASC DESC [VARCHAR(20)] Order by 9. [5 marks] Find the country with the larger span between the country's highest elevation point and the depth of its deepest ocean. If a country has no direct access to an ocean, you should assume that its ocean's depth is 0. Report the name of the country and the total span. Output Table: query9 Attributes cname (country name)[VARCHAR(20)] totalspan (total span) INTEGER] 10. [5 marks] Find the country with the longest borders (with all its neighboring countries). Report the country and the total length of its borders Output Table: query10 Attributes cname (country name)[VARCHAR(20)] borderslength (length of borders) INTEGER] Instructions 1. Read this assignment thoroughly before you proceed. Failure to follow instructions can affect your grade. 2. Download the database schema a2.ddl from the course website. 3. Download the file a2.sql from the course website. 4. Download the java skeleton file Assignment2.java from the course website. 5. Submit your work electronically using UNIX submit. Your submission must include the following files: a) a2.sql your queries for the interactive SQL part of the assignment (can include any view creation statement). If you define any views for a question, you must drop them after you have populated the answer table for that question. your java code for the embedded SQL part of the assignment. Be careful to submit the .java file (not the .class file.). To get you started, we provide a skeleton of this file that you must download from the assignment webpage. b) Assignment2.java Interactive SQL Queries [50 marks] In this section, you must edit the file a2.sql and add SQL statements that can be run in psql on Prism machines. Your SQL statements can create views and queries that will populate the result tables query1 query2, ..., query10 with tuples that satisfy the questions below. In order to ensure that everything is run in the correct order (by the markers or the automarker), you should add all your SQL statements in the file a2.sql that we have provided. This file can be read and executed using the psql command \i You can assume that the a2.ddl file has been read and executed in psql, before your a2.sql file is executed. Follow these rules: The output of each query must be stored in a result table. We provide the definitions of these tables in the a2.ddl file (query1, query2, ..., query10) For each of the queries below, your final statement should populate the respective answer table (queryX) with the correct tuples. It should look something like INSERT INTO queryX (SELECT ...) where X is the correct index [1,...,10] . In order to answer each of the questions, you are encouraged to create virtual views that can keep intermediate results and can be used to build your final INSERT INTO queryX statement. Do not create actual tables. Remember that you have to drop the views you have created, after each INSERT INTO queryX statement (i.e., after you have populated the result table) . Your tables must match the output tables specified for each query. The attribute names must be identical to those specified in italics, and they must be in the specified order. Also, make sure to sort the results according to the attributes and ordering we specify in each question .We are not providing a sample database to test your answers, but you are encouraged to create one We will test the validity of your queries against our own test database All of your statements must run on PostgreSQL on the Prism machines, so be sure to populate your tables with test data and run all your statements on Prism prior to submission e NOTE: Failure to do follow the instructions may cause your queries to fail when (automatically) tested, and you will lose marks Express the following queries in SQL. 1. [5 marks] For each country, find its neighbor country with the highest elevation point. Report the id and name of the country and the id and name of its neighboring country Output Table: query1 Attributes: clid c1name country name) c2id c2name (neighbor country name) VARCHAR(20)] c1name (country id) (neighbor country ic) ASC INTEGER] [VARCHAR(20)] [INTEGER] Order by 2. [5 marks] Find the landlocked countries. A landlocked country is a country entirely enclosed by land (e.g., Switzerland). Report the id(s) and name(s) of the landlocked countries Output Table: query2 Attributes cid (landlocked country id[INTEGER] (landlocked country name) VARCHAR(20)] ASC cname order by cname 3. [5 marks] Find the landlocked countries which are surrounded by exactly one country. Report the id and name of the landlocked country, followed by the id and name of the country that surrounds it Output Table: query3 Attributes clid (landlocked country id) INTEGER] c1name (landlocked country name) VARCHAR(20)] c2id c2name (surrounding country name) [VARCHAR(20)] c1name (surrounding country id) INTEGER] Order by ASC 4. [5 marks] Find the accessible ocean(s) of each country. An ocean is accessible by a country if either the country has an ocean coastline itself (direct access to the ocean) or the country is neighboring another country that has an ocean coastline (indirect access). Report the name of the country and the name of the accessible ocean(s) Output Table: query4 Attributes cname country name) VARCHAR(20)] (ocean name) ASC DESC [VARCHAR(20)] oname cname oname Order by 5. [5 marks] Find the top-10 countries with the highest average Human Development Index (HDI) over the 5-year period of 2009-2013 (inclusive) Output Table: query5 Attributes cid cname avghdi ghdli (country id) (country name) (country's average HDI) DESC INTEGER] [VARCHAR(20)] [REAL] Order by 6. 5 marks] Find the countries that their Human Development Index (HDI) is constantly increasing over the 5-year period of 2009-2013 (inclusive). Constantly increasing means that from year to year there is a positive change (increase) in the country's HDI Output Table: query6 Attributes cid (country id) (country name) ASC INTEGER] [VARCHAR(20)] cname Order by:cname 7. [5 marks] Find the total number of people in the world that follow each religion. Report the id of the religion, the name of the religion and the respective number of people that follow it. Output Table: query7 Attributes rid name followers (religion id) (religion name) (number of followers) INTEGER] [VARCHAR(20)] INTEGER Order by: followers DESC 8. [5 marks] Find all the pairs of neighboring countries that have the same most popular language. For example, is one example tuple because in both countries, English is the most popular language; can be another tuple, and so on. Report the names of the countries and the name of the language Output Table: query8 Attributes c1name(country name) [VARCHAR(20)] c2name Iname Iname c1name (neighboring country name) [VARCHAR(20)] (language name) ASC DESC [VARCHAR(20)] Order by 9. [5 marks] Find the country with the larger span between the country's highest elevation point and the depth of its deepest ocean. If a country has no direct access to an ocean, you should assume that its ocean's depth is 0. Report the name of the country and the total span. Output Table: query9 Attributes cname (country name)[VARCHAR(20)] totalspan (total span) INTEGER] 10. [5 marks] Find the country with the longest borders (with all its neighboring countries). Report the country and the total length of its borders Output Table: query10 Attributes cname (country name)[VARCHAR(20)] borderslength (length of borders) INTEGER]