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: Draw flow chart to estimated salary for the proportion of workers 6 people 5 ID per hour where…

A: 1) For start and stop we have to use oval symbol 2)For calculation we have to use rectangle 3)For…

Q: For each order, list the order ID, customer ID, order date, and most recent date among all orders.…

A: Given data For each order, list the order ID, customer ID, order date, and most recent date among…

Q: ng problem statement. Retrieve the customer name, service description, employee name, employee…

A: I have completed the query to fullfil to data to retrive according to the conditions I have used…

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: Update the emp table to set the commission of all employees to SR 1000 who are working as managers

A: Syntax: UPDATE table_nameSET column1 = value1, column2 = value2, ...WHERE condition;

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: Provide a thorough description of layer 4 and its responsibilities, as well as the most significant…

A: What is OSI layers: The OSI (Open Systems Interconnection) model is a conceptual framework that…

Q: Show all name and phone numbers of students and the type of donation they received including any…

A: Show all name and phone numbers of students and the type of donation they received including any…

Q: Find the numbers that has not been used as DEPTNO in the DEPT table.

A: you can use this query

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: List the number of customers living at each state that is included in the Customer_T table.

A: Query:-

Q: 1. Use the following table to answer the questions below. Table name: Products Column Role Data Type…

A: We have to extract year from the Date filed and then the year has to be 2021 Now, we have to count…

Q: Write an sql query thevempld and fullname of all the employees working under managers with ID 986

A: SQL is the Structured Query Language, which is the computer language for storing, manipulating 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: 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: 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: 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: Explain when you would prefer to use embedded SQL over SQL or a general-purpose programming…

A: Embedded SQL is a technique that allows SQL statements to be integrated directly into a…

Q: Display job Title, the difference between minimum and maximum salaries for jobs with max salary in…

A: The table name is JOB and consist of the following attributes, Job_title, minimum_salary,…

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: 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: 2. Determine which orders haven’t yet shipped and the name of the customer who placed the order.…

A: According to the information given:- We have to follow the instruction in order to perform the…

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: Your university runs many hundreds of courses each semester and needs to keep track of key…

A: the code is given below:-

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

A: Solution :

Q: eate the table that results from EMPLOYEE PRODUCT DEPT I am unsure of what to do here. I f the…

A: Description: 1- No, don't need to create a DEPTNAME column in two places. 2- Create a column name…

Q: Use the query box below to answer the questions, saving your query for each question. When you are…

A: The above question is solved in step 2 :-

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: Example of login management. List all possible authentication methods. Are passwords becoming…

A: Login management is an essential aspect of security in any system that requires user access control.…

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

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?
Vacation days are based on status. Using the information in the lower table below, do a lookup in the upper table determine the amount of vacation time earned by each employee. Status Vacation Time Earned Full TIme 10 Part Time 7 Contract 0 Employees Status Vacation TIme Earned John Contract Jane Part Time Al Full TIme
The Colombia City office of StayWell indicated that there is a new tenant by the name of Yigit Yilmaz staying at the property with an ID of 13. You need to include this new resident in the RESIDENTS table.
Prepare an Excel sheet which should contain the data of inpatient. Following parameters should be mentioned:-Hospital registration number of patients.-Name of the patient.-Phone number and mail ID.-Purpose of admission/or Diagnosis.-Admission date and Discharge Data.-Bill amount at the time of discharge
From worksheet Product, in cell 115, display the week number of the date given in cell 19. Answer: From worksheet Accessories, in cell 111, find the median of selling price. A hp
The processor will submit a request that cannot be met in the cache while the write buffer will return a block to the main memory. What are some possible next steps in this scenario?
List the names of all Staff members who hold a faculty position and have a salary of $45,000 or less. Show their names as their first name, followed by a space, and then their last name but sort the list alphabetically by last name and then first name. Hint: Check out the Position column in the Staff table. All staff members who do not hold a faculty position will get a bonus this year equal to 5% of their salary. List the names, current salary, and bonus amount for each Staff member (non-faculty) who is due a bonus. Show your list in order with the person receiving the highest bonus first.
You are given the following schema of a University, where primary keys of each table are underlined and symbol (#) symbolises foreign keys: Student (student id, first_name, last_name, date_of_birth) Course (course_id, #professor_id, course_name, credits, programme) Exam (#student id, #course id, exam_date, grade) Professor (professor id, first_name, last_name, research_field)
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)
DATABASE