### Figure P7.1: The CH07 Construction Database#### Relational DiagramThe relational diagram illustrates the design of the CH07 Construction Company database, showcasing the relationships between four key tables: JOB, EMPLOYEE, ASSIGNMENT, and PROJECT. - **JOB**: Contains job details, including descriptions and hourly charges.- **EMPLOYEE**: Stores employee information such as names, job codes, and employment years.- **ASSIGNMENT**: Links employees to their assigned projects along with specific job roles, dates, hours assigned, and charges.- **PROJECT**: Details different projects, their values, and assigned employees.### Table Details#### Table Name: JOB| JOB_CODE | JOB_DESCRIPTION | JOB_CHG_HOUR | JOB_LAST_UPDATE ||----------|----------------------|--------------|-----------------|| 500 | Programmer | 35.75 | 20-Nov-17 || 501 | Systems Analyst | 96.75 | 20-Nov-17 || 502 | Database Designer | 72.50 | 20-Nov-17 || 503 | Technical Engineer | 54.00 | 20-Nov-17 || 504 | Mechanical Engineer | 67.90 | 20-Nov-17 || 505 | Civil Engineer | 84.25 | 20-Nov-17 || 506 | Clerical Support | 25.90 | 20-Nov-17 || 507 | Analyst | 48.95 | 20-Nov-17 || 508 | Applications Designer| 55.75 | 20-Nov-17 || 509 | Bio Technician | 34.50 | 20-Nov-17 || 510 | General Support | 18.36 | 20-Nov-17 |#### Table Name: EMPLOYEE| EMP_NUM | EMP_LNAME | EMP_FNAME | EMP_INITIAL | EMP_HIREDATE | JOB_CODE | EMP_YEARS ||---------|-------------|-----------|-------------|--------------|----------|-----------|| 101 | News | John | G | 08-Nov-04 | 500 | 17 || 102 | Senior | David **Educational Text for Database Query Example****Figure P7.2: The Query Results for Problem 2**This example demonstrates the results of a SQL query designed to join the EMPLOYEE and PROJECT tables using the common attribute EMP_NUM and to display selected attributes sorted by project value. Here is a breakdown of the table data:1. **Columns**: - **PROJ_NAME**: Name of the project. - **PROJ_VALUE**: Total financial value assigned to the project. - **PROJ_BALANCE**: Remaining balance or funds available for the project. - **EMP_LNAME**: Last name of the employee involved with the project. - **EMP_FNAME**: First name of the employee. - **EMP_INITIAL**: Initial or middle name of the employee. - **JOB_CODE**: Code representing the employee's job function. - **JOB_DESCRIPTION**: Description of the job role of the employee. - **JOB_CHG_HOUR**: Charge per hour for the employee’s job role.2. **Data Entries**: - For the project "Rolling Tide," the project value is 805,000.00 with a balance of 500,345.20. The employee associated is David Senior, a Systems Analyst with a job code of 501, charging 96.75 per hour. - The "Evergreen" project has a value of 1,435,500.00 and a balance of 1,002,350.00. The associated employee is June Arbouugh, a Programmer with a job code of 500, charging 35.75 per hour. - "Starflight" has a project value of 2,650,500.00 and a balance of 2,309,880.00, managed by Maria Alonzo, a Programmer with job code 500, with a rate of 35.75 per hour. - For "Amber Wave," the value is 3,500,650.00 with a balance of 2,110,346.00. Ralph Washington, a Systems Analyst, is the associated employee with a job code of 501, charging 96.75 per hour.This structured table provides a clear view of how employee roles and project financials are interconnected within the ConstructCo database, allowing for efficient data analysis and resource management.

Explanation: Join is a relational operation, which combines the data from two or more tables into…

2. Using the EMPLOYEE, JOB, and PROJECT tables in the Ch07_ConstructCo data- base, write the SQL code that will join the EMPLOYEE and PROJECT tables using EMP_NUM as the common attribute. Display the attributes shown in the results presented in Figure P7.2, sorted by project value.

2. Using the EMPLOYEE, JOB, and PROJECT tables in the Ch07_ConstructCo data- base, write the SQL code that will join the EMPLOYEE and PROJECT tables using EMP_NUM as the common attribute. Display the attributes shown in the results presented in Figure P7.2, sorted by project value.

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: What are the considerations for using the CAST and CONVERT functions for data type conversions in…

A: In the world of SQL (Structured Query Language), data type conversions are a fundamental aspect of…

Q: Employee (empNo, firstName, lastName, address, Department (deptNo, deptName, mgrEmpNo) Project…

A: Solution- We have create an query for each of them in step by step manner.Here, we look for patterns…

Q: EmployeelD Last Name First Name Address City Province PostalCode Phone BirthDate 507 - 20th Ave.…

A: 1- A trigger is created for AFTER insert situation. 2- When a row is inserted, a copy of the row is…

Q: Write the SQL code that counts the number of assignments ordered for each project. Call this…

A: We have four tables namely: JOB PROJECT EMPLOYEE ASSIGNMENT We wish to write a query that counts…

Q: How do I create a query that includes first name, last name, email and state for all customers and…

A: Query:- SELECT first_name, last_name, email, state, CASE WHEN customer_type = 'Customer' THEN 'C'…

Q: My SQL: The Car Maintenance team wants to add new maintenance tasks into the system. The team has…

A: This query selects all cars from the CARS table with a model year of 2021 and a manufacturer of…

Q: Please write the SQL to return the sale revenue (top line & bottom line) by Marketing channel for…

A: Here have to determine about sql query for given problem statements.

Q: How do you optimize user-defined functions that access large data sets in SQL?

A: In SQL, user-defined functions (UDFs) can be very useful for encapsulating logic that is used over…

Q: Using the retail.inventory table, pull the current stock percentage out of the future stock level…

A: The following solution is

Q: Using the output shown in Figure P7.12 as your guide, generate a list of customer purchases,…

A: Here we have to write an SQL query to have the given table/list of data in the fig. P7.12, Key…

Q: What is the primary key for the APARTMENTS table? (It may be a composite key involving 2 or more…

A: The question asks to identify the primary keys and foreign keys for the "APARTMENTS" and "RENTERS"…

Q: please assist with the following questions. need to write a syntax in SQL server. Create an UPDATE…

A: It is defined as SQL (pronounced "ess-que-el") stands for Structured Query Language. SQL is used to…

Q: SELECT BRAND_NAME, BRAND_TYPE, round(AVERAGE,2) AS 'Average Price', UnitsSold FROM (LGBRAND INNER…

A: Actually, SQL stands for structured query language.

Q: Question 6 List classroom ID, building name, class ID and class description. Use a LEFT JOIN to…

A: Here , i create one database and then In table i inserted some value by own.. then writing…

Q: I need help with this SQL query: Write a MySQL statement to create a table named jobs including…

A: Hello student. Warm welcome from my side. Hope you are doing great. I will try my best to answer…

Q: Create a new table, EMP_PROMOTION to store the employees information. You must use a subquery to…

A: Objective: A table "EMP_PROMOTION" would be created using sub-query from an existing relation…

Q: . Please write SQL codes to query data from member tables based on below criteria Please write a…

A: A number is odd number if its remainder when divided by 2 is not equal to 0

Q: Given the table PRODUCT(ProductID, ProductName, ProductClass, ProductWeight, ProductStyle,…

A: In the realm of database management, understanding the characteristics and distribution of data is…

Concept explainers

SQL

SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.

Queries

A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.

Question

100%

### Figure P7.1: The CH07 Construction Database

#### Relational Diagram

The relational diagram illustrates the design of the CH07 Construction Company database, showcasing the relationships between four key tables: JOB, EMPLOYEE, ASSIGNMENT, and PROJECT.

- **JOB**: Contains job details, including descriptions and hourly charges.
- **EMPLOYEE**: Stores employee information such as names, job codes, and employment years.
- **ASSIGNMENT**: Links employees to their assigned projects along with specific job roles, dates, hours assigned, and charges.
- **PROJECT**: Details different projects, their values, and assigned employees.

### Table Details

#### Table Name: JOB

| JOB_CODE | JOB_DESCRIPTION | JOB_CHG_HOUR | JOB_LAST_UPDATE |
|----------|----------------------|--------------|-----------------|
| 500 | Programmer | 35.75 | 20-Nov-17 |
| 501 | Systems Analyst | 96.75 | 20-Nov-17 |
| 502 | Database Designer | 72.50 | 20-Nov-17 |
| 503 | Technical Engineer | 54.00 | 20-Nov-17 |
| 504 | Mechanical Engineer | 67.90 | 20-Nov-17 |
| 505 | Civil Engineer | 84.25 | 20-Nov-17 |
| 506 | Clerical Support | 25.90 | 20-Nov-17 |
| 507 | Analyst | 48.95 | 20-Nov-17 |
| 508 | Applications Designer| 55.75 | 20-Nov-17 |
| 509 | Bio Technician | 34.50 | 20-Nov-17 |
| 510 | General Support | 18.36 | 20-Nov-17 |

#### Table Name: EMPLOYEE

| EMP_NUM | EMP_LNAME | EMP_FNAME | EMP_INITIAL | EMP_HIREDATE | JOB_CODE | EMP_YEARS |
|---------|-------------|-----------|-------------|--------------|----------|-----------|
| 101 | News | John | G | 08-Nov-04 | 500 | 17 |
| 102 | Senior | David

**Educational Text for Database Query Example**

**Figure P7.2: The Query Results for Problem 2**

This example demonstrates the results of a SQL query designed to join the EMPLOYEE and PROJECT tables using the common attribute EMP_NUM and to display selected attributes sorted by project value. Here is a breakdown of the table data:

1. **Columns**:
- **PROJ_NAME**: Name of the project.
- **PROJ_VALUE**: Total financial value assigned to the project.
- **PROJ_BALANCE**: Remaining balance or funds available for the project.
- **EMP_LNAME**: Last name of the employee involved with the project.
- **EMP_FNAME**: First name of the employee.
- **EMP_INITIAL**: Initial or middle name of the employee.
- **JOB_CODE**: Code representing the employee's job function.
- **JOB_DESCRIPTION**: Description of the job role of the employee.
- **JOB_CHG_HOUR**: Charge per hour for the employee’s job role.

2. **Data Entries**:
- For the project "Rolling Tide," the project value is 805,000.00 with a balance of 500,345.20. The employee associated is David Senior, a Systems Analyst with a job code of 501, charging 96.75 per hour.
- The "Evergreen" project has a value of 1,435,500.00 and a balance of 1,002,350.00. The associated employee is June Arbouugh, a Programmer with a job code of 500, charging 35.75 per hour.
- "Starflight" has a project value of 2,650,500.00 and a balance of 2,309,880.00, managed by Maria Alonzo, a Programmer with job code 500, with a rate of 35.75 per hour.
- For "Amber Wave," the value is 3,500,650.00 with a balance of 2,110,346.00. Ralph Washington, a Systems Analyst, is the associated employee with a job code of 501, charging 96.75 per hour.

This structured table provides a clear view of how employee roles and project financials are interconnected within the ConstructCo database, allowing for efficient data analysis and resource management.

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

Step 1

VIEW

Step 2

VIEW

Trending now

This is a popular solution!

Step by step

Solved in 2 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

database
Use your ERD to define the table structures in the database using SQL DDL commands to build your schema.
Write the SQL code that will produce the results shown as below. Write the SQL code to calculate the ASSIGN_CHARGE values in the ASSIGNMENT table. Note that ASSIGN_CHARGE is a derived attribute that is calculated by multiplying ASSIGN_CHG_HR by ASSIGN_HOURS. Write the SQL code that will yield the total number of hours worked for each employee and the total charges stemming from those hours worked. The result is shown as below.
I want to write the SQL to create the tables for the ERD attached, but I also want to add this to the tables: rental date, due date, and return date so that I can support overdue queries. I also want it so each table has primary keys and foreign keys. I want it so the tables have at least 4 rows of data. How can I do this? Thank you for your help as I learn more about SQL and table creation.
You have a table named student having attributes student_name, id, batch, contact. Write a SQL query to display the names of all students in alphabetically increasing order.