Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

   

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

Step by Step Solution

3.43 Rating (159 Votes )

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Income Tax Fundamentals 2013

Authors: Gerald E. Whittenburg, Martha Altus Buller, Steven L Gill

31st Edition

1111972516, 978-1285586618, 1285586611, 978-1285613109, 978-1111972516

More Books

Students also viewed these Databases questions