This assignment is designed to let you have a hands-on experience writing SQL qu
ID: 3729322 • Letter: T
Question
This assignment is designed to let you have a hands-on experience writing SQL query statements. You have to use MySQL to complete this assignment. Chapter 6.3 and Lecture Notes provide a good introduction to the basic features of SQL. After following lecture notes you should be able to complete this assignment. Consider the following database schema for Greensboro Shop Items(itemlD string, name string, description string qtylnStock integer) Departments deptID string, name string, location string, telNumber string, budget real, manager string) Suppliers(supID string, name string, address string) Orders(ordID string,ordDate date, shipAddress string Employees(empID string, firstName string, lastName string, ssn string, telNumber string, salary real) Carries deptID string, itemID string) Supplie(itemID string, suplD string, price real) Includes(itemID string, ordID string, qty integer) WorksIn deptID string, empID string) For your convenience, the SQL statements for creating these tables and populating data is available in the file of HW4DB2018.sql. Download it your computer and run it to build the database GShop on MySQL database server. In this assignment, your task is to write the following queries in SQL query statements and run them on the MySQL Database Server. The query answers must not contain duplicates, but you should use the SQL keyword distinct only when necessary. Execute your queries based on these tables. Please put your SQL query statements and the results of these query statements into a SQL script file and submit it to the Assignment Link Homework 4 . Find the names and address of suppliers. 11 rows in set te.08 sec) w*4444*Explanation / Answer
1.
Select distinct name,address from Suppliers;
2.
Select empID,firstName,lastName from Employees;
3.
Select firstName,lastName ,salary from Employees where salary > 80000;
4.
Select firstName,lastName ,salary from Employees where salary between 100000 and 200000;
5.
Select name as DeptName,manager from Departments;
6.
Select name as DeptName,manager from Departments order by name;
7.
Select distinct name as Department, concat(firstName,lastName) as 'Manager Name' from Departments inner join Employees on Departments.manager = Employees.empID;
8.
Select distinct firstName,lastName ,deptID from Employees inner join WorksIn on Employees.empID = WorksIN.empID;
9.
Select firstName,lastName ,name as DeptName from Employees inner join WorksIn on Employees.empID = WorksIN.empID inner join Departments on WorksIn.deptID = Departments.deptID;
10.
Select firstName,lastName ,salary from Employees inner join WorksIn on Employees.empID = WorksIN.empID inner join Departments on WorksIn.deptID = Departments.deptID where Departments.name = 'Information Technology;
11.
Select avg(salary) as 'Average Salary' from Employees;
12.
Select name as 'Department Name',avg(salary) as 'Average Salary' from Employees inner join WorksIn on Employees.empID = WorksIN.empID inner join Departments on WorksIn.deptID = Departments.deptID group by Departments.deptID;
Do ask if there are any queries. Please upvote if it is helpful.