2552265AG2(NAWAL_HAMDAN)
docx
keyboard_arrow_up
School
Cleveland State University *
*We aren’t endorsed by this school
Course
331
Subject
Industrial Engineering
Date
Jan 9, 2024
Type
docx
Pages
5
Uploaded by ColonelHornet2591
ID: 2552265 NAME: NAWAL HAMDAN CLASS: IST 331
AG2: Tables used (Project table and assignment table) A1 to A5 (project table): A1) Queries using the "PROJECT" table:
Query 1 - List projects with a specific name:
Retrieve all details of projects with the name 'Evergreen.'
A2) List projects with a project value greater than $2,000,000:
Retrieve projects with a project value greater than $2,000,000.
A3) List projects led by a specific employee (e.g., Employee Number 102):
Retrieve projects where Employee Number 102 is the project leader.
A4) List projects with a value between $500,000 and $1,000,000:
Retrieve projects with a project value between $500,000 and $1,000,000.
A5) List projects with a "PROJ_BALANCE" greater than 2,300,000.00:
Retrieve projects with a "PROJ_BALANCE" greater than 2,300,000.00.
B1 to B5 (ASSIGNMENT table) B1) List assignments with specific ASSIGN_NUM values (e.g., ASSIGN_NUM 1001 and 1002):
Retrieve assignments with ASSIGN_NUM values 1001 and 1002.
B2) Calculate the total hours worked by all employees:
Calculate the total hours worked across all assignments.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
B3) List assignments with more than 6 hours of work:
Retrieve assignments where the number of worked hours is greater than 6.
B4) List assignments with specific ASSIGN_NUM and ASSIGN_HOURS conditions (e.g., ASSIGN_NUM 1001 and hours less than 4):
Retrieve assignments with ASSIGN_NUM 1001 and worked hours less than 4.
B5) Calculate the average number of hours worked per assignment:
Calculate the average number of hours worked for all assignments.
Queries for the "Project" Table:
Listing Projects with a Specific Name: We retrieved all details of projects with a specific name (e.g., 'Evergreen') using a SELECT query with a WHERE clause on the project name column.
Filtering by Project Value: We filtered projects with a value greater than $2,000,000.00, showcasing the use of numerical conditions in SQL queries.
Filtering by Project Leader: We selected projects led by a specific employee (e.g., Employee Number 102) by filtering based on the project leader's employee number.
Range Query for Project Value: We retrieved projects with a project value between $500,000 and $1,000,000, illustrating the use of the BETWEEN operator.
Filtering by Project Balance: We queried projects with a "PROJ_BALANCE" greater than a specified amount (e.g., 2,300,000.00).
Queries for the "Assignment" Table:
Selecting Assignments by ASSIGN_NUM: We retrieved assignments with specific ASSIGN_NUM values (e.g., 1001 and 1002) using the IN operator.
Aggregating Total Hours Worked: We calculated the total hours worked by all employees across assignments using the SUM function.
Filtering by Worked Hours: We filtered assignments where the number of worked hours exceeded 6, demonstrating the use of numerical conditions.
Combining ASSIGN_NUM and Worked Hours: We selected assignments with specific ASSIGN_NUM (e.g., 1001) and hours worked less than 4, combining multiple conditions.
Calculating Average Hours Worked: We calculated the average number of hours worked per assignment using the AVG function.