This assignment is designed to let you have a hands-on experience writing SQL qu

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 assig nment. Consider the following database schema for Greensboro Shop.mI Items(itemID string, name string, description string, qtyInStock 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 supID 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 duplicates, but you should use the SQL keyword distinct only when necessary. 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 query answers must not contain Execute your queries based on these . Find the names and address of suppliers. rlie rows in set (0.08 sec)

Explanation / Answer

1: select name,address from Suppliers; //Select command is used to retrieve the data from the database in sql,here we need only name and address so selecting only those values from table//

2: select empID,firstName,LastName from Employee; //similar to that of 1st //

3: select firstName,lastName from Employee where salary>80,000.00; // where is used in select whenever there is a condition//

4: select firstName,lastName from Employee where salary between 100,000 and 200,000; //between is used where we want values between two given values//

5: alter table "Departments" RENAME COLUMN "name" TO "DeptName"; //Firstly we are renaming the columnname from name to DeptName by using this altertable rename command//

select DeptName,manager from Departments; //similar to 1//

6:  alter table "Departments" RENAME COLUMN "name" TO "DeptName"; //Firstly we are renaming the columnname from name to DeptName by using this altertable rename command//

select DeptName,manager from Departments order by DeptName; //Here order by is used to sort by using the coloumnname specified as in above it is DeptName//

7:  alter table "Departments" RENAME COLUMN "name" TO "Department"; //Firstly we are renaming the columnname from name to Deparmentby using this altertable rename command//

select Department,CONCAT(lastName," ",firstName)AS Manager Name from Employees; //here concat function will join two lastname and first name to a single attribute Manager Name//

8: select a.firstName,a.lastName,b.DeptId from Employees a,WorksIn b where a.empID=b.empID; //these types are called correlated nested queries where we have a common attribute between two tables and it hepls us to retrieve from the tables//

9:   alter table "Departments" RENAME COLUMN "name" TO "DeptName"; //Firstly we are renaming the columnname from name to DeptName by using this altertable rename command//

select firstName,lastName, from Employees where empID IN(select empID from WorksIn where deptID IN(select DeptID from Departments)); // this is called as nested queries or subquery where there is a query inside another query,Here IN is a keyword that is used to include all the values//

10: select firstName,lastName,salary from Employees where empID IN(select empID from WorksIn where deptID IN(select deptID from Departments where name='Information Technology')); //same as above//

11:   alter table "Employees" RENAME COLUMN "salary" TO "AverageSalary"; //Firstly we are renaming the columnname from salary to AverageSalary by using this altertable rename command//

select avg(AverageSalary) from Employees; // we have inbuilt avg() function that gives us the avg of value//

12:  

alter table "Employees" RENAME COLUMN "salary" TO "AverageSalary"; //Firstly we are renaming the columnname from salary to AverageSalary by using this altertable rename command//

alter table "Departments" RENAME COLUMN "name" TO "Department Name";

select avg( AverageSalary) from Employees where empID IN(select empID from WorksIN where deptID IN(select deptID from Departments where group by Department Name));

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

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