Based on the picture please help me write an SQL querey for each (don't answer the actual question just please write an SQL querey)Write an SQL Query to list the name(s) and address(es) of employees who work at the department named- 'Human Resources’? (Hint: Use Table Joining)Write an SQL Query to list the names of all projects run by the department – ‘IT Transformation’? (Hint: Use Table Joining)Write an SQL Query to list the department number and the count of projects in each of these departments and only display those values that has more than 3 count. Rename the count column as ‘Project Count’.(Hint1: Use GROUP BY and HAVING. You need not use table joining) (Hint2: It is OK to see an empty outcome as long as your query is correct)Write an SQL Query to count unique ‘Female’ employees who have been designated as ‘Staff’ and also have worked for more than 20 hours on a single project? Rename the count value as ‘Employee Count’ (Hint: Use Table Joining). The image presents a database schema with multiple tables and their respective attributes. Each table represents a different entity related to an organizational structure and its dependencies. Here's a detailed transcription:1. **Employee Table:** - **Primary Key:** `empNo` (Employee Number) - Attributes: - `firstName` - `lastName` - `address` - `DOB` (Date of Birth) - `sex` - `position` - `deptNo` (Department Number - Foreign Key)2. **Department Table:** - **Primary Key:** `deptNo` (Department Number) - Attributes: - `deptName` (Department Name) - `mgrEmpNo` (Manager Employee Number - Foreign Key)3. **Project Table:** - **Primary Key:** `projNo` (Project Number) - Attributes: - `projName` (Project Name) - `deptNo` (Department Number - Foreign Key)4. **WorksOn Table:** - **Composite Key:** `empNo`, `projNo` (Employee Number and Project Number) - Attribute: - `hoursWorked`5. **Dependent Table:** - **Composite Key:** `empNo`, `Dependent_name` (Employee Number and Dependent's Name) - Attributes: - `Sex` - `Bdate` (Birth Date) - `Relationship`### Explanation:- **Primary Keys** are underlined and specify unique identifiers for each table.- **Foreign Keys** are highlighted in red and connect tables, establishing relationships between different entities.- Each table is designed to normalize data, minimize redundancy, and maintain data integrity within the relational database structure.

The solution is written in SQL. Please check the solution in the next step. Note: As per the…

Answered: Write an SQL Query to list the name(s)…

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

Concept explainers

Question

Based on the picture please help me write an SQL querey for each (don't answer the actual question just please write an SQL querey)

Write an SQL Query to list the name(s) and address(es) of employees who work at the department named- 'Human Resources’? (Hint: Use Table Joining)
Write an SQL Query to list the names of all projects run by the department – ‘IT Transformation’? (Hint: Use Table Joining)
1. Write an SQL Query to list the department number and the count of projects in each of these departments and only display those values that has more than 3 count. Rename the count column as ‘Project Count’.
(Hint1: Use GROUP BY and HAVING. You need not use table joining)

(Hint2: It is OK to see an empty outcome as long as your query is correct)
Write an SQL Query to count unique ‘Female’ employees who have been designated as ‘Staff’ and also have worked for more than 20 hours on a single project? Rename the count value as ‘Employee Count’ (Hint: Use Table Joining).

The image presents a database schema with multiple tables and their respective attributes. Each table represents a different entity related to an organizational structure and its dependencies. Here's a detailed transcription:

1. **Employee Table:**
- **Primary Key:** `empNo` (Employee Number)
- Attributes:
- `firstName`
- `lastName`
- `address`
- `DOB` (Date of Birth)
- `sex`
- `position`
- `deptNo` (Department Number - Foreign Key)

2. **Department Table:**
- **Primary Key:** `deptNo` (Department Number)
- Attributes:
- `deptName` (Department Name)
- `mgrEmpNo` (Manager Employee Number - Foreign Key)

3. **Project Table:**
- **Primary Key:** `projNo` (Project Number)
- Attributes:
- `projName` (Project Name)
- `deptNo` (Department Number - Foreign Key)

4. **WorksOn Table:**
- **Composite Key:** `empNo`, `projNo` (Employee Number and Project Number)
- Attribute:
- `hoursWorked`

5. **Dependent Table:**
- **Composite Key:** `empNo`, `Dependent_name` (Employee Number and Dependent's Name)
- Attributes:
- `Sex`
- `Bdate` (Birth Date)
- `Relationship`

### Explanation:
- **Primary Keys** are underlined and specify unique identifiers for each table.
- **Foreign Keys** are highlighted in red and connect tables, establishing relationships between different entities.
- Each table is designed to normalize data, minimize redundancy, and maintain data integrity within the relational database structure.

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 3 steps with 2 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