ASSIGNMENT 3 DATABASE (1)

docx

School

Sheridan College *

*We aren’t endorsed by this school

Course

PROG 32758

Subject

Information Systems

Date

Dec 6, 2023

Type

docx

Pages

Uploaded by CaptainSealMaster657

ASSIGNMENT 3 6% Based on Sub-Queries, Joins, Group By, Having Name: Lovil Jacob Student ID: 991679180 Write SQL statements 1. Return the all the country names in each Region (3 points) [Use Sub-Query only] Answer: USE HR GO SELECT r.region_name, c.country_name FROM countries c JOIN regions r ON c.region_id = r.region_id ORDER BY r.region_name, c.country_name;

2. Return the name of the manager of Finance Department (3 point) [Use Join only] Answer : USE HR GO SELECT e.first_name, e.last_name FROM employees e JOIN departments d ON e.employee_id = d.manager_id WHERE d.department_name = 'Finance' ; 3. Return the name of the employees who manage more than 3 employees (3 Points) [Use either sub- Query or join] Answer: USE HR

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

GO SELECT m.first_name, m.last_name FROM employees m WHERE ( SELECT COUNT ( * ) FROM employees e WHERE e.manager_id = m.employee_id) > 3 ;

4 . Return the employee that have salary that is not in the range between the minimum salary and maximum salary of their job. For example you need to return Adam’s if their salary is 6000 and they work a. job that has Min_salary 7000 and Max_salary 10000 (4 points) [Sub-Query or Join your choice] Answer: USE HR GO SELECT e.first_name, e.last_name FROM employees e JOIN jobs j ON e.job_id = j.job_id WHERE e.salary NOT BETWEEN j.min_salary AND j.max_salary; 5. Return all the locations that are in countries with multiple locations. For example if Canada has 2 locations then I need to return these two locations while if Sweden has only one location then you do not need to return that location (4 Points) [use of join required] Answer : USE HR GO SELECT l.location_id, l.city, c.country_name FROM locations l JOIN countries c ON l.country_id = c.country_id WHERE ( SELECT COUNT ( * ) FROM locations WHERE country_id = l.country_id) > 1 ;

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

6. What is Employee named - Bruce Earnst, Job_title.(3 points) (Use Joins only) Answer: USE HR GO SELECT j.job_title FROM employees e JOIN jobs j ON e.job_id = j.job_id WHERE e.first_name = 'Bruce' AND e.last_name = 'Earnst' ;

ASSIGNMENT 3 DATABASE (1)

Related Documents