$24
Reading Tables
In a file named buildIndustry.sql, put the following commands.
Create a database named Industry if it does not exist. For each of the tables on the following page, drop them if they already exist, then create tables by those names with appropriate data types and cardinality restrictions. In particular, this means salary should be a number with two decimal places.
In a file named populateIndustry.sql, put the SQL necessary to populate the tables created in buildIndustry.sql with the data from the following page. This data has been included in codio in the file Industry Data.csv for your convenience, so that you can copy and paste instead of retyping the data. Instructions on how to load sql files into mysql have been included in Populate Database.txt, with the data from the University database used by the textbook given as an example. Note that the largeRelationsInsertFile.sql file takes a long time to load and should be preloaded in the database University, you may test the commands with the smallRelationsInsertFile.sql file.
Page 1 of 4
Employee Table
PERSON_NAME
STREET
CITY
Sarah
Clinton
Miami
Gregory
Broadway
Chicago
Amy
State
New York
Matthew
Roosevelt
Chicago
Theresa
Main
Detroit
Omar
102nd
Louisville
Employment Table
PERSON_NAME
COMPANY_NAME
SALARY
Theresa
Facebook
15,000
Amy
Amazon
12,000
Matthew
Starbucks
9,000
Gregory
American Airline
9,000
Sarah
Amazon
14,000
Company Table
COMPANY_NAME
CITY
First Bank
Miami
Amazon
Chicago
Facebook
Detroit
Starbucks
Chicago
American Airline
Seattle
McDonalds
Milwaukee
Page 2 of 4
SQL
For each of the remaining questions, put the answer in a file named Q#.sql, where # is the number of the question. For example, the answer to question 1 should be placed in a file named Q1.sql. If order is not
specified, order results in alphabetical order (A-Z) for the first string attribute.
1. GiveΠ SQL queries_ that are(equivalent to the following) relational algebra expressions.
Π_ 10000()
3. Π _ , ( ⋈ )
4. Π _ , _ ( =" ℎ "( ⋈ ))
5. Π _ , _ ( =" ℎ "( ⋈ ))
Page 3 of 4
Write a SQL query which returns the employee name and company name for every currently employed person in the database.
Write a SQL query which returns the name and salary of each person recorded in the database, but only for people whose salaries are less than 10,000, ordered from highest to lowest salary
Write a SQL query which returns the names of both the employees and the companies they work at, but only for employees who work at in the same city they live in. Employees live in the city indicated in the Employee table. Employees work at the city indicated in the Company table.
Write a SQL query which returns the names of both the employees and the companies they work at, but only for employees who work in a different city from the one they live in.
Write a SQL query which returns a table listing all known cities, both locations where people live as well as the cities where companies are headquartered. This should be a table with a single column.
Write a SQL query which lists the names of all companies along with the average salary that company pays. If a company has no known employees, the result should be NULL. Title the column containing the total salary as TOTAL_SALARY in the table your query generates.
Page 4 of 4