1. Display employees (by name and address) from "Chicago" and with an annual salary between 60000 (included) and 90000 (excluded) then rename the output as "ChicagoEmployees" For each employee, who works in the "Design" department since January 2009, display the employee's name and the employee's annual salary 3. Find the name and rank of the manager who manages the "Research" department, also display the budget of that department. 4. Find the name of employees who earn more than $100,000 per year, along with the names of the departments they work in 5. Assume employees can work in more than one department, find employees (by name and salary) who work in the "Design" department but not the "Research" department.

1. Display employees (by name and address) from "Chicago" and with an annual salary between 60000 (included) and 90000 (excluded) then rename the output as "ChicagoEmployees" For each employee, who works in the "Design" department since January 2009, display the employee's name and the employee's annual salary 3. Find the name and rank of the manager who manages the "Research" department, also display the budget of that department. 4. Find the name of employees who earn more than $100,000 per year, along with the names of the departments they work in 5. Assume employees can work in more than one department, find employees (by name and salary) who work in the "Design" department but not the "Research" department.

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Q: 3,#4,#5 Will provide my re

Q: In cell D5, construct a formula to calculate the % of Total Projected Investments for Pooled…

A: According to the information given:-We haev to define above scenario in excel.

Q: Based on the ERD below, display all columns in the JOB table for those job descriptions that have…

A: 2) Query to display all columns in the JOB table for those job descriptions that have both analysts…

Q: Considering the un-normalized relational table PROJECT below: PROJECT (Project Title, EmpeName,…

A: Functional dependencies are... ProjectTitle, EmpeName -> HoursWork ManagerName -> ManagerPhone…

Q: Show all female’s first names and their salary. Show the branchno that has a supervisor whose…

A: Relational database is a type of database which stores and provides the access to data which are…

Q: repare the loan amortization table/schedule from scratch (not the Excel template) for a $300,000,…

A: Please refer the table we have calculated the loan value as 520000 = 650000*(1-0.80) as 20% is down…

Q: Find the staff name who has the maximum salary for each branch Find the staff name who has the…

A: SQL is the Structured Query Language, which is the computer language for storing, manipulating and…

Q: J. K. Does this relation contain a transitive dependency? If so, what is it? Redesign the relation…

A: SOLUTION -From the given table we have given a table PROJECT with ProductId, EmployeeName,…

Q: View 3 Seneca Courses Inc. Employee Forms All employee information for SCI is maintained on file…

A: Actually, the answer has given below:

Q: Question 2: For the following table find the relevant Functional Dependencies and resolve them till…

A: Third normal form: The Third Normal Form (3NF) is a normal form used in the normalization process of…

Q: Course evaluation must have a student, the students relation contains a unique student id, full name…

A: An Entity–relationship model (ER model) describes the structure of a database with the help of a…

Q: Your university runs many hundreds of courses each semester and needs to keep track of key…

A: the code is given below:-

Q: Create a list that shows each department name, # of employees, maximum salary, minimum salary, total…

A: First EMP and DEPT tables have to join basing deptno in both. Then group them by using dname. Then…

Question

Employee:
(EID, Name, City, Address)
Department:
(DID, Department_Name, City, budget)
Works_IN:
(EID, DID, Annual_Salary, Start_Date)
Manages:
(ManagerID, DepartmentID, rank)
DID and EID in Works_IN relation are foreign keys referencing the primary keys of the Department and Employee
relations respectively. Manager_ID and Department_ID in the Manages relation are foreign keys referencing the
primary keys of the Employee and Department respectively.
From the relational schemas illustrated above, use the different relational algebra operators and operations (p, o, n,
N, U, -, X) discussed in class to build the following queries:
Display employees (by name and address) from "Chicago" and with an annual salary between 60000
(included) and 90000 (excluded) then rename the output as "ChicagoEmployees"
1.
2.
For each employee, who works in the “Design" department since January 2009, display the employee's
name and the employee's annual salary
3. Find the name and rank of the manager who manages the “Research" department, also display the budget
of that department.
4. Find the name of employees who earn more than $100,000 per year, along with the names of the
departments they work in
5. Assume employees can work in more than one department, find employees (by name and salary) who work
in the "Design" department but not the "Research" department.

Expert Solution

This question has been solved!

Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.

This is a popular solution!

SEE SOLUTION Check out a sample Q&A here

Introduction

LOGIC

Solution: 1)

Solution: 2)

Solution: 3)

Solution: 4)

Solution: 5)

Trending now

This is a popular solution!

Step by step

Solved in 7 steps with 5 images

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions

Check the following statement: select sysdate Today,(select count (*) from departments) Dept_count,(select count (*) from employees) Emp_countfrom dual; And choose the correct answer. Select one: a. Determines the current date and the current number of unique departments and employees.b. Determines the current date and the current number of departments and employees.c. Determines the current date of hire and the current number of departments and employees.d. The statement generates an error because no requirements can be placed in the SELECT
1. For each sale made on June 1, 2015, show the following information: CustomerID, Customer name (lastname + comma + space + firstname) as 'CustomerName', SaleDate, Sum of Tax and Shipping as 'tax_shipping'. Sort it by customer last name. 2. For each sale made on June 1, 2015, show the following information: CustomerID, Customer name (lastname + comma + space + firstname) as 'CustomerName', SaleDate, ProductName, ItemSize, Quantity and the total amount of each sale item (quantity times SalePrice as 'SaleAmount'). Sort it by customer last name. Hint: The answer result will have a record for each SaleItem. 3. The color field in the Product table has a mix of single colors or multiple colors separated by commas. Provide a list of Products and replace every comma with a semicolon plus one space. Name the column 'OpenColor'. Show ProductID, ProductName, Composition, Category, and 'OpenColor'. For simplicity include only ProductIDs for products with a list price of less than $20 and…
View 4 Seneca Courses Inc. Customer Forms All customer information for SCI is maintained on file forms. The following are sample customer information forms: CUST_ID 78 CUST _ID 54 NAME YORK UNIVERSITY NAME SENECA COLLEGE LOCATION NORTH YORK LOCATION NORTH YORK SALES_REP ANDRE MARTIN SALES_REP PARVEZ RAI CUST _ID 74 CUST _ID 43 NAME RYERSON NAME INDIGO BOOKS LOCATION TORONTO LOCATION TORONTO SALES_REP PARVEZ RAI SALES_REP ANDRE MARTIN Business Rules: NAME must always be different for each customer. SALES_REP must always have a value. SALES_REP must be an existing employee. A customer only has one sales rep, but the sales_rep may have more than one customer. Normalize the above user view. Document all steps including UNF, 1NF, Dependencies, 2NF, and 3NF and explain or list the…
C) Which employees earn less salary than the average salary for the consultants who perform the same job? Show the employees first and last names, job id, salary, department name, and manager's last name. Sort the result by the employees’ employee id. D) For each department, find the manager who pays out the most in salaries. Show the department number, the manager's last name, and the total she/he pays each month. Sort by the department number. Here are the tables and the columns within the table Here are the tables and the columns within the table CONSULTANTS - CONSULTANT_ID- FIRST_NAME- LAST_NAME- EMAIL- PHONE_NUMBER- HIRE_DATE- JOB_ID- SALARY- COMMISSION_PCT- MANAGER_ID- DEPARTMENT_ID . COUNTRIES - COUNTRY_ID- COUNTRY_NAME-REGION_ID . CUSTOMERS - CUST_IDCUST_EMAILCUST_FNAMECUST_LNAMECUST_ADDRESSCUST_CITYCUST_STATE_PROVINCECUST_POSTAL_CODECUST_COUNTRYCUST_PHONECUST_CREDIT_LIMIT . DEPARTMENTS - DEPARTMENT_IDDEPARTMENT_NAMEMANAGER_IDLOCATION_ID . EMPLOYEES -…
Tables: department, dept_emp, dept_manager, employees, salaries, titles department (columns): dept_no, dept_name dept_emp (columns): emp_no, dept_no, from_date, to_date dept_manager (columns): emp_no, dept_no, from_date, to_date employees (columns): emp_no, birth_date, first_name, Last_name, gender, hire_date salaries (columns): emp_no, salary, from_date, to_date titles (columns): emp_no, title, from_date, to_date a) Your task is to write a SQL query to find count of employee who does not have a manager in the above given table schema?
a) List the names (Fame and Lame) and SSN of all male employees (Sex='M). b) List the last name and SSN of employees who work on project 'City Tower', sorted by alphabetical order of last na c) Retrieve the average salary of all female employees (Sex='F'). d) List department names and the number of employees who work for each department. Sort by department name. e) Retrieve the names of employees who make at least $10,000 more than thé employee who is pald the least in
You have the following table: MANAGERS (MANAGER_ID, LAST_NAME, FIRST_NAME, DEPARTMENT) Write a Stored Procedure that returns the last name and first name for all managers in a department with the DEPARTMENT passed in as a parameter. Answer: To write a stored procedure
Camille needs to identify the number of projects that have a final cost of more than $100,000 and those that were completed in 2019.Create formulas that provide this information as follows: In cell K11, create a formula using the DCOUNT function to count the number of projects with final costs of more than $100,000, using the data in the entire Projects table (Projects[#All]) and counting the values in the column of final costs ("Final Cost") that are equal to the values in the range J9:J10. In cell K15, create a formula using the DCOUNTA function to count the number of projects completed in 2019, using the data in the entire Projects table (Projects[#All]) and counting the values in the column of completion dates ("Completed") that are equal to the values in the range J13:J14.
in a shop ,there are 10 employee and 20 kinds of goods，goods id between 1-20 EMPLOYEE id first name last name gender 10001, 'Tom', 'Brown', 'F'10002, 'Elizabeth', 'Tremblay', 'F'10003, 'Gladys', 'Julie', 'F10004, 'John', 'Taylor', 'M10005, 'Amelia', 'Smith'10006, 'Logan', 'Katherine'10007, 'Leo', 'Brown'10007, 'Lem', 'Thompson'10009, 'Tom' 'Smith'10010, 'Emma', 'Campbell' ------------ and I want to add a name library in it ,like this how could i create a HTML file ,with will randomly create customers with these employee . there is a start button on the page . press "start "bottom ,and it will It will randomly match 10 items, customers, and goods, display goods id ,customername and gender ,employer name , id and gender . how to do such a page ?
Create a report of employees within the Research department showing First Name, Last Name, sorted alphabetically A-Z.
SQL DML/DDL There are five tables describing employees of a company, departments, buildings, which department(s) an employee works in (and a percentage of the time for each), and in which building an employee works (an employee may have more than one office). The primary key of each table is the attribute(s) in capitals and underlined. The foreign keys are in italics. Other attributes are not necessarily unique.Employee (EID, Ename, Salary, Start_Date, End_Date)Building (BID, Bname, Address)Department (DID, Dname, Annual_Budget)In_Department (EID, DID, Percentage_Time)In_Building (EID, BID)Write the SQL statements for the following.1. Find the names of Departments where NO employees work. 2. For employees who are still working in the company, end date has no value (empty). For those who have left the company and have end date values, show their names and the duration of their employment in years. 3. Find the names of buildings where more than 50 employees work. 4. The tables have…
Based on the ERD below, for each project calculate the difference between the project value and the project balance. Display the result in a new column name “Remaining Balance”. Include the project number and project name in the result output.