EMP may be involved. All the five tables Employees, Departments, Locations, Dept, Use table Employees for...

   

Transcribed Image Text:

EMP may be involved. All the five tables Employees, Departments, Locations, Dept, Use table Employees for this Q1. List the employee_id, last name, first name, manager (immediate supervisor) ID for those employees that their first name starts with "Alex". Use table LOCATIONS for this Q2. Sometimes, we do not know how the database stores the literals (either all upper, Initial Capital, or all lower cases). Use the function UPPER, we may make our query case insensitive. In this question, you are required to use this technique. Display the Location's location_id, street_address, postal code, city, country_id for those location(s) that city name spells as Seattle-but we do not know how the literal letters (upper case, lower, or...) stored in database. Use table LOCATIONS for this Q3. When we design tables, it is better to use small unit as a column, not same as in our daily language. For the example of ADDRESS, we usually will define the columns of street address, city, state, state, zip_code, country individually. When we output it as a composite address, we will use the concatenation operator ||, to merge some of these columns according to required format. Another similar case is the people's names defined with columns of first, middle, last names. Display the location ID, and its full address as one string. The format is "street, city, state province, postal code, country_id". Just list those locations they located in 'US' (country_id = 'US'). Use table EMP for this Q4. Display the employees' empno, name (ename), salary (sal) and department ID for those whose immediate supervisor's name (ename) is Blake. You may refer to page 13 of Notelb_Advanced SQL.docx to check the output. Use table Employees and Departments for this Q5 List the department ID, department name and the number (how many) of employees that work in that department. The output will be sorted in order of number of employees in ascending order. Exclude those departments that have no employee yet. Use table Locations and Departments for this Q6 List the department ID, department name, Address (as in question 3, you may re-use some of EMP may be involved. All the five tables Employees, Departments, Locations, Dept, Use table Employees for this Q1. List the employee_id, last name, first name, manager (immediate supervisor) ID for those employees that their first name starts with "Alex". Use table LOCATIONS for this Q2. Sometimes, we do not know how the database stores the literals (either all upper, Initial Capital, or all lower cases). Use the function UPPER, we may make our query case insensitive. In this question, you are required to use this technique. Display the Location's location_id, street_address, postal code, city, country_id for those location(s) that city name spells as Seattle-but we do not know how the literal letters (upper case, lower, or...) stored in database. Use table LOCATIONS for this Q3. When we design tables, it is better to use small unit as a column, not same as in our daily language. For the example of ADDRESS, we usually will define the columns of street address, city, state, state, zip_code, country individually. When we output it as a composite address, we will use the concatenation operator ||, to merge some of these columns according to required format. Another similar case is the people's names defined with columns of first, middle, last names. Display the location ID, and its full address as one string. The format is "street, city, state province, postal code, country_id". Just list those locations they located in 'US' (country_id = 'US'). Use table EMP for this Q4. Display the employees' empno, name (ename), salary (sal) and department ID for those whose immediate supervisor's name (ename) is Blake. You may refer to page 13 of Notelb_Advanced SQL.docx to check the output. Use table Employees and Departments for this Q5 List the department ID, department name and the number (how many) of employees that work in that department. The output will be sorted in order of number of employees in ascending order. Exclude those departments that have no employee yet. Use table Locations and Departments for this Q6 List the department ID, department name, Address (as in question 3, you may re-use some of