Question

A major multi-national corporation as employed you to research and implement SQL statements to support their data management

Please refer to the following business scenarios:


(i) Using SELECT statements of SQL, find the employee id, first name, last name, job title and email of all employees working in Asia (i.e. all countries coming from the region
    ‘Asia’). This query must be implemented as a nested query!


(ii) Using SELECT statements of SQL, find the employee id, first name, last name, job title and supervisor id of employees who had worked for more than 3 years and completed their jobs in the information technology department.


(iii) Using SELECT statements of SQL, find the employee id, first name, last name, hire-date, salary of supervisors, and the total number of employees directly supervised by each one
     of them. Display only those records of supervisors who supervised 8 or more employees.

0 0
Add a comment Improve this question Transcribed image text
Answer #1

1)SELECT employee_id,first_name,last_name,job_title,email,

(SELECT job_title FROM job

WHERE job.id=employee.job_id as job_title) FROM employee

WHERE(employee e

JOIN department d

ON e.department_id=d.department_id

JOIN location 1

ON d.location_id=1.location_id)

AND

WHERE country_name IN

(SELECT region_name

FROM region TABLE

WHERE region_name='ASIA');

2) SELECT employee_id,first_name,last_name,job_title,supervisor_id

FROM employees

WHERE EXTRACT((YEAR

FROM age(CURRENT_DATE,HIRE_DATE))>3 and department_id=IT);

3) SELECT employee_id,first_name,last_name,hire_date,

count(employee.supervisor_id)employee_count

FROM employee

WHERE(SELECT supervisor.salary,count(*) FROM supervisor WHERE having count>7);

Add a comment
Know the answer?
Add Answer to:
Please refer to the following business scenarios: (i) Using SELECT statements of SQL, find the employee...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions
  • SQL query: Write an SQL query that will output the last name, employee id, hire date and department from the employee table. Pick only those employees whose department ID is specified in the employee...

    SQL query: Write an SQL query that will output the last name, employee id, hire date and department from the employee table. Pick only those employees whose department ID is specified in the employee table. If the employee id is 777, name is ABC and hire date is 01-JAN-2016, the output should look as follows - 'Stephen (Employee ID - 777) was hired on the 1st of January, 2016 – Department: 90'. Note - The date should not have preceding...

  • I need this written in SQL Employee EMP ID EMP FNAME EMP LNAME EMP STREET EMP...

    I need this written in SQL Employee EMP ID EMP FNAME EMP LNAME EMP STREET EMP CITY EMP STATE EMP ZIP EMP START DATE Table TABLE ID AREA IDTABLE SEATS Area AREA ID AREA NAME AREA SUPERVISOR EMPLOYEE ID Customer CUST ID CUST LAST NAME CUST NUMBER OF GUESTS Assignment EMPID TABLE ID Seating CUST IDTABLE ID SEATING COST SEATING DATE SEATING TIP Question 29 (10 points) Write an SQL query to list the employee ID and first and last...

  • Use the HR database for all the questions in this lab. For each item in this lab, paste the SQL y...

    Use the HR database for all the questions in this lab. For each item in this lab, paste the SQL you used to complete the task and the number of rows retrieved. Ensure you follow the coding standards listed in the ETSU SQL standards guide found on D2L. Save this document as a PDF and name it your_name_lab7.pdf Display the employees’ first name, last name and phone number of employees with salaries greater than 10,000 dollars. SELECT first_name, last_name, phone_number,...

  • Problem 33, chapter 7 from Database Systems 13th edition by Coronel Write a query to display...

    Problem 33, chapter 7 from Database Systems 13th edition by Coronel Write a query to display the employee number, last name, email address, title, and department name of each employee whose job title ends in the word “ASSOCIATE.” Sort the output by department name and employee title Problem 36, chapter 7 from Database Systems 13th edition by Coronel Write a query to display the number of products within each base and type combination, sorted by base and then by type...

  • database and sql problme THE QUESTIONS 3, 4,5 and 6 REFER TO THE RELATIONAL TABLES LISTED...

    database and sql problme THE QUESTIONS 3, 4,5 and 6 REFER TO THE RELATIONAL TABLES LISTED BELOW CREATE TABLE Department ( DECIMAL(5) VARCHAR(30) CHAR(5) DATE NOT NULL NOT NULL NOT NULL * Department number /*Department name * Department manager number */ /Manager start date DNumber DName Manager MSDate CONSTRAINT Department_PK PRIMARY KEY(DNumber) CONSTRAINT Department_CK UNIQUE(DName) CREATE TABLE DeptLocation DECIMAL(5) VARCHAR(50) NOT NULL NOT NULL DNumber * Department number */ * Department location */ Address CONSTRAINT DeptLocation_PK PRIMARY KEY(DNumber, Address) CONSTRAINT...

  • can someone solve these quick please and thank you! sql chapter 4 Query #1: List the...

    can someone solve these quick please and thank you! sql chapter 4 Query #1: List the company name, contact name, contact title and the phone number for customers who HAVE NOT put in an order. Use an outer join. Query #2: Create a listing displaying the employee first name, last name and the full name (First name space Last Name) of the person they report to (Supervisor). This is a self-join. If an employee does not report to anyone then...

  • Quis -2 EMPLOYEES EMPLOYEE ID NUMBER(6.0 FIRST NAME VARCHAR NTE) CONSTRAINT EMPLASTNAME NN NOT NULL ENA...

    Quis -2 EMPLOYEES EMPLOYEE ID NUMBER(6.0 FIRST NAME VARCHAR NTE) CONSTRAINT EMPLASTNAME NN NOT NULL ENA LAST NAME VARCHAI CONSTRAINT EMPEMAIL NN NOT NULL ENABLE "EMAIL VARCHAR CHARZI29 BYTEL "PHONE NUMBE CONSTRAINT EMP HIRE DATE NN NOT NULL ENABLE "HERE DAT Aaue BYTE) CONS TRAINT EMPJOB NN NOT NULL ENABLE 3o8 MBER3 BYTE) SA OMMISSION PCT NUMBER2.23 "MANAGER 1D NUMBER(6.0), "DEPARTMENT ID NUMBER(4,0) 1. Return the names of employees who have the 2nd highest salary Sεutct 2. For every employee,...

  • 2. SQL queries (40 points total, 5 points each query) Write SQL query statements to query...

    2. SQL queries (40 points total, 5 points each query) Write SQL query statements to query the following Hotel Room Booking Database Tables. "cid" is Customer ID. "rid" is Room ID. "bid" is Booking ID. "dob" means Date of Vwwww Birth; "beds" means the number of beds in the room which is normally 1 or 2 beds. "DueDate" and "DueAmount" are the payment due dates and due amounts of this booking. Customers fname name phone email cid dob zipcode Rooms...

  • I have already turned in my HW, I need some understanding of the below SQL queries....

    I have already turned in my HW, I need some understanding of the below SQL queries. 1. Find the number of employees in each department. 2. List the names of departments that have more than 5 employees working there. 3. Retrieve the lowest and highest salary in each department. Output the department name in alphabetical order. Consider the following relational schema. An employee can work in more than on department; also, the percentTime field of the Works relations shows the...

  • SQL queries and procedures TABLE: Employees Business Rules: EmployeeID is defined as the primary key. Address...

    SQL queries and procedures TABLE: Employees Business Rules: EmployeeID is defined as the primary key. Address has been denormalized to include City and State for performance reasons. DeptNbr is a foreign key that references DeptNbr in the Department table Manager is a foreign key that references EmployeeID in the Employees table Data Structure: (EmployeeId, LastName, FirstName, Street, City, State, Zip, DateOfHire, DateOfBirth, JobTitle, Salary, DeptNbr(fk), Manager(fk)) TABLE: Department Business Rules: DeptNbr is defined as the primary key. Data Structure: (DeptNbr,...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT