Review View Tell me what you want to do Share This assignment is designed to let

Question

Review View Tell me what you want to do Share This assignment is designed to let you have a hands-on experience writing advanced SQl. query statements subqueries and complex joins. Subqueries: one way to nest or a cascade query is to stick a query in the where clause, find the names of employees worked at the department Research. This is a powerful way to take advantage of the fact that any SQL query returns a table, which can then be the starting point of another SQL query Complex Joins : the 'where clause can become·luite con lex with many ponasandralled 'and'and ior cond tns . Consider the following database schema for Greensboro Shop Items(itemD string. name string, description string, gtylnStock integer) Departments(deptlD string, name string, location string, IelNumber string, budget real, manager strume) Suppliers(supID string, name string, address string) Orders(ordID string.ordDate date, shipAddress string Employees(emplD string. firstName string, lastName strimg, ssn string, telNumber string, slary real) Carries deplD string, itemlD string) SuppliecitemID string, suplD srring, price real) Includes(itemiD string, oxdllD string, qty integer) Worksln deptID string, emplD string) In this assignment, your task is to write SQL queries that answer the following questions based on the database GShop from Homework 4 in the ANSI-style JOIN. Each question should be answered by one SQL statement only. The result of each statement must not contain duplicates, but you should use the SQL keyword distinet only when necessary. Please put your SQL query statements and their results into a SOL script file. Run your SQL statements on the MySOL Database Scrver. Make sure that your SOL script file does not contain any syntax errors before submitting to the Assignment Link -IHomework 5 Retrieve every départment's name along with its manager's name. Reference MySoL cONCAT) to dsplay the mianagefist name and last mame in one colm ins the tables Employes and Depurtmasmts sbold be included in the query statement Outpu

Explanation / Answer

1. Select d.name as Department, CONCAT(e.firstName,' ',e.lastName) as "Manager Name" from Departments d join Employees e on d.manager=e.empID

2. Select e.firstName as "firstName, e.lastName as "lastName" , d.deptID as "DeptID" from Employees e join WorksIn w on e.empID=w.empID join Departments d on d.deptID=w.deptID

3. Select e.firstName as "firstName, e.lastName as "lastName" , e.salary as "salary" from Employees e join WorksIn w on e.empID=w.empID join Departments d on d.deptID=w.deptID where d.name='Information Technology'

4. Select e.firstName as "firstName, e.lastName as "lastName" , e.salary as "salary" from Employees e join WorksIn w on e.empID=w.empID join Departments d on d.deptID=w.deptID where e.salary>(select avg(salary) from Employees)

5. Select e.firstName as "firstName, e.lastName as "lastName" , e.salary as "salary" from Employees e join WorksIn w on e.empID=w.empID join Departments d on d.deptID=w.deptID where e.salary=(Select max(salary) from Employees e join WorksIn w on e.empID=w.empID join Departments d on d.deptID=w.deptID where d.name ='Information Technology')

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.