Question

In: Computer Science

SQL ONLY- Given Tables- Employee (EmployeeID, FirstName, LastName, Address) Department (DeptNo, DepartmentName, City) DepartmentEmployee(DeptNo, EmployeeID, Position)...

SQL ONLY-

Given Tables-

Employee (EmployeeID, FirstName, LastName, Address)

Department (DeptNo, DepartmentName, City)

DepartmentEmployee(DeptNo, EmployeeID, Position)

Project (ProjectNo, ProjectName, DeptNo)

Project Team ( ProjectNo, EmployeeID, Role)

MAKE THE QUERY FOR-

1. List the name and position of all the employee who works in the (DeptNo = ‘22C’).

2. List the total number of employees in each department for those departments with more than 5 employees.

3. List the project number, project name and the number of employees who worked on each project role.

Solutions

Expert Solution

Answer:

1)Select e.FirstName,e.LastName,de.Position from Employee e
join DepartmentEmployee de on de.EmployeeID = e.EmploeeID
where de.DeptNo = '22C'
order by e.FirstName;
/*This query will list down all the name and position of the employees who works in the (DeptNo = ‘22C’)*/

2)Select d.DeptNo,d.DepartmentName,count(de.DeptNo) from Department d
join DepartmentEmployee de on de.DeptNo = d.DeptNo
Group by d.DepartmentName having count(de.Deptno)>5
order by d.DepartmentName;
/*This query will list down the total number of employees in each department with more than 5 employees*/

3)select p.ProjectNo,p.ProjectName,pt.Role,count(pt.EmployeeID) from Project p
join ProjectTeam pt on pt.ProjectNo=p.ProjectNo
order by p.ProjectName;
/*This query will list down the project number, project name and the number of employees who worked on each project role*/

Let me know in case if you have any questions. Thanks and all the best.


Related Solutions

What is wrong with the following INSERT commands: INSERT IN EMPLOYEE (EmployeeNumber, FirstName, LastName, Department, Position,...
What is wrong with the following INSERT commands: INSERT IN EMPLOYEE (EmployeeNumber, FirstName, LastName, Department, Position, Supervisor, OfficePhone, EmailAddress) VALUES (5,'Alan','Adams','Human','H R 1',4,'360-285-8320','AlanAdams@WP.com'); INSERT INTO (EmployeeNumber, FirstName, LastName, Department, Position, Supervisor, OfficePhone, EmailAddress) VALUES (5,'Alan','Adams','Human','H R 1',4,'360-2858320','AlanAdams@WP.com'); INSERT INTO EMPLOYEE (EmployeeNumber, FirstName, LastName, Department, Position, Supervisor, OfficePhone, EmailAddress) VALUES ('Alan','Adams','Human','H R 1',4,'360285-8320','AlanAdams@WP.com'); INSERT INTO EMPLOYEE (EmployeeNumber, FirstName, LastName, Department, Position, Supervisor, OfficePhone, EmailAddress) VALUES ('Alan','Adams','Human','H R 1',4,'360285-8320','AlanAdams@WP.com')
Using C++ Design a class named PersonData with the following member variables: lastName firstName address city...
Using C++ Design a class named PersonData with the following member variables: lastName firstName address city state zip phone and at a minimum the following member functions: A class constructor - which initializes all the member variables above a display() function - which ONLY displays all the person's data to the console Also write the appropriate accessor/mutator functions for the member variables Write a NewPersonData function. This is a stand-alone function that you can use in your main to generates...
Tables CUSTOMER CustomerID FirstName LastName Address Phone LicenseNo 1 David Hacker 101 Yammba road, Rockhampton 0749008877...
Tables CUSTOMER CustomerID FirstName LastName Address Phone LicenseNo 1 David Hacker 101 Yammba road, Rockhampton 0749008877 089 777 123 2 Tony Morrison 98 South street, Melbourne 0490787772 088777555 3 Issac Newton 90 Heaven road, Sydney 0299001122 099 776 123 4 James Farrell 101 St Lucia Garden, Brisbane 0733099000 090 566 777 5 David Land 345 Illinois road, Brisbane 0739000554 456 000 999 6 Peter Garry 201 South port road, Gold Coast 0745676766 234 090 767 RENTAL RentalID CustomerID RegoNo StartDate...
Design a database/mysql that has 3 tables: TABLE 1 student: StudID (int), LastName (varchar), FirstName (varchar),...
Design a database/mysql that has 3 tables: TABLE 1 student: StudID (int), LastName (varchar), FirstName (varchar), MiddleName(varchar) TABLE 2 Course: StudID, CourseID TABLE 3 study course: CourseID(int), CourseTitle(varchar), Units(int), PreqCourse(varchar) ** Insert at least 5 records. ** Use the "select" command to view the content of the tables. ** POST THE SCREENSHOT
Database Normalize the relations to BCNF: Member- (MemID, dateJoined, firstName, lastName, street, city, state, zip, areaCode,...
Database Normalize the relations to BCNF: Member- (MemID, dateJoined, firstName, lastName, street, city, state, zip, areaCode, phoneNumber, currentOfficeHeld) Play- (title, author, numberOfActs, setChanges) Sponsor- (sponID, name, street, city, state, zip, areaCode, phoneNumber) Subscriber- (subID, firstName, lastName, street, city, state, zip, areaCode, phoneNumber) Production- (prodyear, seasonStartDate, seasonEndDate, title) Performance – (perfyear,perfdate, time, seasonStartDate) TicketSale- (saleID, saleDate, totalAmount, perfyear, perfdate,subID) DuesPayment- (MemID, duesYear, amount, datePaid) Donation – (sponID, donationDate, donationType, donationValue, prodYear, seasonStartDate) Ticket- (saleID, seatLocation, price, type) Member - Production-(MemID, prodYear,...
SQL statmen: List the first name, the last name, the address, the city, the state, the...
SQL statmen: List the first name, the last name, the address, the city, the state, the branchNo, and the email of agents working in the branch B005 and having email addresses ending with extensions different from .com.
1. write SQL statements to create the following two tables: a) Department table that contains the...
1. write SQL statements to create the following two tables: a) Department table that contains the following columns(dept_no,deptname,location) set the deptno to be the primary key. b) Employee table contains the following columns(emp_no,empname,deptno,salary)set the emp_no to be the primary key and dept_no to be the foreign key. 2. Write SQL statements to Insert the following 3 rows in Employee table:        (101,’Sami’,’D-101’,5000)        (102,’Yousef’,’D-101’,4000)        (103,’Sami’,’D-102’,7000) 3. Write SQL statements to Insert the following 3 rows in Department table:       ...
SQL 9. Link the following two tables: HumanResources.Employee and Sales.SalesPerson then display Employee PK, job title,...
SQL 9. Link the following two tables: HumanResources.Employee and Sales.SalesPerson then display Employee PK, job title, Date of birth, Gender, Sales quotas, Commission percent and bonus. Use an outer join to display all the employee whether they are in sales or not. Make sure to sort by bonus desc. Explain why some of the field from the Sales.SalesPerson table are null.
Employee ID First Name Last Name email Title Address Extension Department Department ID Hiring Date Department...
Employee ID First Name Last Name email Title Address Extension Department Department ID Hiring Date Department Phone # 0001 John Smith jsmith Accountant 1300 West st 5775 Accounting 2100 8/1998 407-366-5700 0002 Brian Miller badams Admin Assistant 1552 Palm dr 5367 Human resource 2300 4/1995 407-366-5300 0003 James Miller miller Inventory Manager 2713 Buck rd 5432 Production 2520 8/1998 407-366-5400 0004 John Jackson jackson_sam Sales Person 433 tree dr 5568 Sales 2102 6/1997 407-366-5500 0005 Robert Davis Davis Manager 713...
Given the tables: SKU_DATA (SKU, SKU_Description, Department, Buyer) WAREHOUSE (WarehouseID, WarehouseCity, WarehouseState, Manager, Squarefeet) INVENTORY (WarehouseID,...
Given the tables: SKU_DATA (SKU, SKU_Description, Department, Buyer) WAREHOUSE (WarehouseID, WarehouseCity, WarehouseState, Manager, Squarefeet) INVENTORY (WarehouseID, SKU, SKU_Description, QuantityOnHand, QuantityOnOrder) Write an SQL statement to display SKU and SKU_Description. Write an SQL statement to display the SKU, SKU_Description, and WarehouseID for products having QuantityOnHand greater than 0. Sort the results in descending order by WarehouseID and ascending order by SKU. Write an SQL statement to display the SKU, SKU_Description, WarehouseID, WarehouseCity, and WarehouseState for all items stored in the Atlanta,...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT