Give an SQL schema definition for the following employee database: employee (employee name, street, city) works (employee
Question:
Give an SQL schema definition for the following employee database: employee (employee name, street, city) works (employee name, company_name, salary) company (company name, city) manages(employee name, manager_name) Choose an appropriate domain for each attribute and an appropriate primary key for each relation schema. Include foreign key constraints where needed. The table manages contains two foreign keys where both employee_name and manager_name are foreign keys referencing the relation employee. Be careful about the order you create the tables.
[20 points] Populate the tables you created for the previous question with data (i.e, write SQL insert statements to insert data in the tables). You need to insert at least three rows in each table. Be careful about the order you populate the tables with data.
[50 points; each part is worth 10 points] For this question, you are not allowed to use any SQL command that was not covered in module 5. Answers that use SQL commands covered in modules after module 5 will be considered wrong. Write SQL queries to answer the following questions: Find the names of all employees who work for “First Bank Corporation”. Find the company_name and city for every company. Order the output in alphabetical order by company_name. Find the names of the employees who make a salary of at least $65,000. Notice: in the query the value should be specified as 65000. Find all employees in the database who live in the same cities as the companies for which they work. Find all employees in the database who live in the same cities and on the same streets as do their managers.
olve the following questions using PostgreSQL. Name your file hw5.sql. All non executable statements in your file must be commented out. A single line comment starts with two hyphens -- in PostgreSQL.
Database System Concepts
ISBN: 9780078022159
7th Edition
Authors: Abraham Silberschatz, Henry F. Korth, S. Sudarshan