2. The following tables form part of a database held in a Relational Database ManagementSystem:Employee(empID, fName, IName, address, DOB, sex, position,deptNo)Department(deptNo, deptName, mgrEmpID)Project(projNo, projName, deptNo)WorksOn(empID, projNo, hoursWorked)where Employeecontains employee details and empID is the key.Departmentcontains department details and deptNo is the key. mgrEmpIDidentifies the employee who is the manager of the department. Thereis only one manager for each department.Projectcontains details of the projects in each department and the key isprojNo (no two departments can run the same project).andWorksOncontains details of the hours worked by employees on each project,and empID/projNo form the key.a) List all employees in alphabetical order of surname and within surname, first name.b) List all the details of employees who are female.c) List the names and addresses of all employees who are Managers.d) Produce a list of the names and addresses of all employees who work for thedepartment.e) Produce a complete list of all managers who are due to retire this year, in alphabeticalorder of surname.f) Find out how many employees are managed by 'James Adams’.Answer:

As per our company guidelines, we are not supposed to answer more than three subparts of question…

a) List all employees in alphabetical order of surname and within surname, first name. b) List all the details of employees who are female. c) List the names and addresses of all employees who are Managers.

a) List all employees in alphabetical order of surname and within surname, first name. b) List all the details of employees who are female. c) List the names and addresses of all employees who are Managers.

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: List the invoice number and invoice date for each invoice created for the customer James Gonzalez

A: SQL queries are a set of instructions given to the database to retrieve specific data. SQL was…

Q: Please see the attached and assist with below question. Using the “Aggregates” worksheet, complete…

A: The given problem is to complete the given excel for an income tax calculation using MS EXCEL and…

Q: List the job title and maximum salary which the salaries are between $10,000 and $20,000. Use…

A: The objective of the question is to retrieve the job title and maximum salary from a database where…

Q: Q24) ?What product(s) have a price equal to the maximum product price Q25) List all employees with…

A: A relational database contains tables. Tables are also called relations and they contain rows and…

Q: From the employee table, calculate the average compensation for each job category where the employee…

A: here is the the required SQL query as shown below :- SELECT -- columns to be retrieved JOB…

Q: Based on the ERD below, write the SQL statement to display the average hourly job charge for a job.

A: Query to display the average hourly job charge for a job. To get average hourly job charge for all…

Q: 1. List the address, square footage, owner number, services ID, number of estimated hours, and…

A: List the address, square footage, owner number, service ID, number of estimated hours, and number of…

Q: - Copy the name, salary, and commission of each employee hired before 1980 into TEMP table - Copy…

A: INSERT INTO query is used to copy the sql data from one table to another table.WHERE is SQL clause…

Q: CREATE A SQL QUERY FROM QUESTION BELOW: List the name of the division that has more than one…

A: A query is to retrieve the data and execute it using commands. The subquery is defined as a query…

Q: 1. a) Create a table Staff Column Name Datatype Constraint Sid Number(3) Primary Key Varchar2(30)…

A: To write query statement to create a table for staff details.

Q: . Which manufacturers are located in the city of New York? Show the manufacturer's name, state,…

A: First, three parts will be answered. SQL query: 1) In this query, the SELECT statement along with…

Q: . Determine how many books are in the Cooking category. SQL commands

A: SQL stands for Structured Query Language.

Q: ? List each order number and the date it shipped. SQL comm

A: Solution - In the given question, we have to write the SQL command for the given query.

Q: Create the Student table with following details: Data type Number Varchar(25) Field Constraint Sid…

A: We are assuming that the field book_id references a table by name of Book which has book_id as its…

Q: a Write in SQL a command that creates the table Products according to the above description a. Write…

A: Given :

Q: Given the employees of medium-sized semi-conductor firm, find the employee that joined November 8,…

A: The Excel INDEX function returns a value in an array based on the row and column numbers you…

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: Show students’ first name and last name, the course numbers the student took (don’t include…

A: Data is coming from 3 tables . So apply inner join on 3 tables with joining condition. student…

Q: Q16) List the V_CODE and V_NAME of vendors that provide products Q8) List the product description,…

A: Structured Query Language is abbreviated as SQL or also pronounced as See-Qwell. It is developed for…

Q: Use SQL to write the following queries: a) Find the last names of students born after January 1,…

A: In this question we have to understand about the given SQL queries and suggest if these queries are…

Q: List the department name and its instructor’s average salary in which the instructors teach a course…

A: SQL implements WHERE clause with SELECT command in order to retrieve stored information from the…

Q: For each different job_id group in the employee table, show the average salary and job_id for the…

A: SELECT Average_salary, Job_id FROM employees WHERE EXISTS (SELECT *…

Q: Task 17: List the invoice number and invoice date for each invoice that was created for James…

A: We need to provide a query that lists the invoice number and invoice date for each invoice that was…

Q: HBase Shell Queries 1) Create an employee table 2) Make sure that the table has been created 3) Add…

A: 1. create 'employee', 'personal_info' 2. list 3. put 'employee','1','personal_info:name','Ali' put…

Q: List the department name, manager id and location id of all the departments that location id is 2500…

A: SELECT department_name, manager_id, location_id FROM departments where manager_id=124 ||…

Q: Display CustomerName and Status as 'Q2 for every customer who was born during the 2nd quarter of…

A: The given problem is related to database SQL query. The problem has provided the msCustomer table…

Q: Task 6: Find the ID, first name, and last name of each customer for which an invoice was not created…

A: Queries are given below :

Q: Check the following statement: select sysdate Today, (select count (*) from departments)…

A: Solution :

Q: C) Which employees earn less salary than the average salary for the consultants who perform the same…

A: According to the information given:- We have to consider the instruction to write queries on the…

Q: Item Number (Item Name) In this case, the date of supply is 04 VENDOR Vendor supply chain Authors'…

A: Attribute (Simple): Simple qualities are ones that cannot be further subdivided. For example, in the…

Q: Display the product ID and the number of orders placed for each product. Show the results in…

A: SQL (Structured Query Language) is a database computer language primarily designed for the…

Q: Find the name and the age of the youngest graduate student (use subquery).

A: Subquery is a query written inside another query and here we use this concept to find the minimum…

Q: a) Your task is to write a SQL query to find count of employee who does not have a manager in the…

A: SQL query output is given in step2.

Q: 5. In the OrderItems table, display a count of the number of products where item_price is greater…

A: Algorithm:From the OrderItems tableFrom the OrderItems tableWhere item_price is greater than or…

Q: a) List the names (Fame and Lame) and SSN of all male employees (Sex='M). b) List the last name and…

A: As per our guidelines, only 3 sub parts will be answered. So, please repost the remaining questions…

Q: Vacation days are based on status. Using the information in the lower table below, do a lookup in…

A: A lookup function is used to match a lookup value in a one-column or one-row range, and returns the…

Q: You have the following table: MANAGERS (MANAGER_ID, LAST_NAME, FIRST_NAME, DEPARTMENT) Write…

A: Stored procedures in a relational database management system (RDBMS) are powerful tools for…

Q: The Colombia City office of StayWell indicated that there is a new tenant by the name of Yigit…

A: Here, the new record has to be added to a table and this process is called as Insertion and the…

Q: From worksheet Product, in cell 15, display the week number of the date given in cell 19. Answer:

A: Given: Calculate week number for excel.

Q: List the names of all Staff members who hold a faculty position and have a salary of $45,000 or…

A: SQL Space: To leave the space while displaying the data ,a function called space is used. Syntax:…

Q: Use SQL to write the following queries: 1. a) Find the last names of students born after January 1,…

A: a) Find the last names of students born after January 1, 2000:1SELECT last_name 2FROM students…

Concept explainers

Basic SQL Queries

Structured Query Language is the full form of SQL. It is a high-level language used to communicate with a relational database. It is used for creating, storing and accessing contents of a relational database. SQL is used to write the queries which are executed using database engines like Oracle, SQL Server and so forth.

Question

2. The following tables form part of a database held in a Relational Database Management
System:
Employee
(empID, fName, IName, address, DOB, sex, position,
deptNo)
Department
(deptNo, deptName, mgrEmpID)
Project
(projNo, projName, deptNo)
WorksOn
(empID, projNo, hoursWorked)
where Employee
contains employee details and empID is the key.
Department
contains department details and deptNo is the key. mgrEmpID
identifies the employee who is the manager of the department. There
is only one manager for each department.
Project
contains details of the projects in each department and the key is
projNo (no two departments can run the same project).
and
WorksOn
contains details of the hours worked by employees on each project,
and empID/projNo form the key.
a) List all employees in alphabetical order of surname and within surname, first name.
b) List all the details of employees who are female.
c) List the names and addresses of all employees who are Managers.
d) Produce a list of the names and addresses of all employees who work for the
department.
e) Produce a complete list of all managers who are due to retire this year, in alphabetical
order of surname.
f) Find out how many employees are managed by 'James Adams’.
Answer:

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

Note:

Part a

Part b

Part c

Trending now

This is a popular solution!

Step by step

Solved in 4 steps

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

Determine the results of the below query. Explain how the result was calculated. SELECT TeamMember ID, SUM (Duration) FROM Task WHERE Duration > 1 GROUP BY TeamMember ID;
Table Employee (eid,ename, manager_id,edptid) Show employee with his/her manager name.
Show the employees who were in the engineering department as of Jan. 1, 2005 (using SQL)
Input table into sql workbench
Create a report of employees within the Research department showing First Name, Last Name, sorted alphabetically A-Z.
write the following queries (n) Identify flats with at least three rooms and with a monthly rent no higher than £500 in Aberdeen. (0) List the details of leases due to expire next month at a given branch. (p) List the total number of leases with rental periods that are less than one year at branches in London. (q) List the total possible daily rental for property at each branch, ordered by branch number. System Description: Branches Dream Home has branch offices in cities throughout the United Kingdom. Each branch office is allocated members of staff, including a Manager, who manages the operations of the office. The data describing a branch office includes a unique branch number, address (street, city, and postcode), telephone numbers (up to a maximum of three), and the name of the member of staff who currently manages the office. Additional data is held on each Manager, which includes the date that the Manager assumed his or her position at the current branch office, and a monthly…
Data normalisation
e) Display the sum of salaries of employees from each department ordered in ascending order.
Normalize the below given Student Course Plan to First Normal Form, Second NormalForm and Third Normal Form.
List the name of each department, and its total number of employees, and the total amount of salaries
A retailer of computers maintains a record containing the following information for every individual computer sold. • Manufacturer name • Model number • Physical memory size • Processor name • Date of sale • Price sold for Using only the database, which of the following CANNOT be determined? The most popular model of computer sold by the retailer Which models of computer stocked by the retailer have not had a single sale The average price of a computer was sold by the retailer In which months less than 100 computers were sold
List the number of albums for each artist. Only include artists with more than 5 albums. table of database Artist (ArtistId, Name) Album (AlbumId, Title, ArtistId, Year) Track (TrackId, Name, AlbumId, ArtistId, GenreId, Year) Genre (GenreId, Name)