1 CMSC424 – Database Design SQL Assignment (Parts A and B) Spring 2008 Prof. Mihai Pop TA: Sharath Srinivas Part A Due Date: Feb 19, before 11:59 PM Part B Due Date: Feb 26, before 11:59 PM • The assignment is to be done by yourself. • To start with, log in to a grace machine, run ”tap oraclient”. The SID is ”dbclass1”. This will initialize the environmental variables. Then run ”sqlplus”, and log in using the user/password emailed to you. You can now start using it. • Data: The dataset contains geographical information about the countries of the world. The information was collected from several web sources, with the CIA World Factbook (https://www.cia.gov/library/publications/the-world-factbook/) being the prominent source. • Schema Information: There are 16 tables in the schema. Most of the table and column names are self-explanatory. The Schema diagram is provided on page 5 and 6 of this document. The ER diagram is provided on page 7. An appendix which lists the meanings of the columns of the tables is on page 8, 9 and 10. • Assumptions and Instructions: 1. The database has several geographical inaccuracies. But, for the questions in this assignment it is sufficient if your answers are consistent with the supplied dataset, even if they are not geographically correct. 2. Answer to the questions 1-16 must be a single query. You can make use of temporary tables and nested sub-queries, but you cannot make use of two different queries to solve one question. For questions 17-20, there is no restriction on the number of queries you can use. 3. The country codes cannot be used for querying any of the tables. E.g., In the “Economy” table the column “Country” is actually the country code and not the name of the country. So, if you have to find the GDP of France, you should make use of the “Country” table to get the code corresponding to France and then use that code to retrieve the GDP of France from the “Economy” table.2 4. The string literals are case sensitive. In order to query on them make use of the SQL “LIKE” condition and the string enclosed within single quotes. The “%” symbol can be used as a wild card. E.g., select * from country where name like 'United States%'; • Instructions to load the data: A compressed file containing the dataset and the instructions for loading it into Oracle will be provided. Go through the README file in dataset.tar.gz for instructions to load the data. • How to hand in your work: You should be making two separate submissions, one for Part A and one for Part B. Please included solutions to all the problems in a file named <lastname>-A.sql for Part A, and a file named <lastname>-B.sql for Part B. Use SQL-style comments at the start of each query to specify what question the query will solve. IMPORTANT: If you do not signify what problem a query addresses, you will receive no points for that query. Aside from comments made inline with your SQL queries, all other comments or notes to the TA should be made in a file named README. Do not include a spool of the output of your queries, we will run your queries to generate the output. You should probably only need to submit at most two files (the README and the file containing your SQL queries). Compress your files using tar, gzip, etc. Call your submission <lastname>-sqlA.tar.gz (or whatever extension matches the compression method you used) for Part A, and <lastname>-sqlB.tar.gz (or whatever) for Part B. Email your submission to [email protected]. Part A (4 pts each = 40 pts) 1. Write a query to change your Oracle password. When submitting the answer, don’t write the new password (write ”****” instead). 2. Figure out how to find out all the attributes and their types for a give table in SQL*Plus and write down the statements and the result on the “Organization” table. This kind of a statement is usually unique to the client, and is not part of standard SQL. 3. Write a query to display the names, capitals and the population of all the countries in the world. 4. Find the countries with the highest and the lowest population. The result should include the country name and its population. 5. For all the cities in the world that are capitals of some country, display their longitude and latitude.3 6. What percentage of Russia lies within Europe? 7. Write a query to find all the countries that border Romania. 8. Find the total number of people living in all the countries that border Romania. 9. Create a View called “Asian_Rivers” that just contains the rivers flowing through any of the countries in Asia and their lengths. 10. Retrieve the list of countries sorted by their continents, and within the continent, arranged by their GDP in ascending order. PART B (6 pts each = 60 pts) 11. Write a query to add a column called “POP_DENSITY” to the “country” table. 12. Initially the POP_DENSITY column of the “country” table is empty. Write a query to update this column with the population density of the country. (Population density = Population/Area) You can use this table in further queries if you want. 13. Find all the countries whose population density differs by a factor of 10,000 or more. 14. Find the 5th largest economy in the world. (Assume that the world economies are ranked on their GDP.) Note: Your result should have only one row: The 5th largest economy in the world. 15. Find all the countries that span across more than one continent. Display the count and names of continents across which the country spans. 16. When a new Organization is established, the country in which the headquarter of the organization is located, by default becomes a member of that Organization. Write a trigger to insert a new record in the “is_member” table whenever a row is added to the “organization” table. Set the “type” column of “is_member” table to the string value: “host”. 17. If all the statistics in the dataset are for the year 2007, write a query to predict the population of Australia in the year 2010. You can assume that
View Full Document
Unlocking...