Write the following queries (a) List the details of branches in a given city. (b) Identify the total number of branches in each city. (c) List the name, position, and salary of staff at a given branch, ordered by staff name.
Write the following queries
(a) List the details of branches in a given city.
(b) Identify the total number of branches in each city.
(c) List the name, position, and salary of staff at a given branch, ordered by staff name.
(d) Identify the total number of staff and the sum of their salaries.
(e) Identify the total number of staff in each position at branches in Glasgow.
(f) List the names of staff supervised by a named Supervisor.
(g) List the property number, address, type, and rent of all properties in Glasgow, ordered by rental amount.
(h) Create a branch and staff relationship in view of joint perspective.
System Description:
Branches Dream Home has branch offices in cities throughout the United Kingdom. Each branch office is allocated members of staff, including a Manager, who manages the operations of the office. The data describing a branch office includes a unique branch number, address (street, city, and postcode), telephone numbers (up to a maximum of three), and the name of the member of staff who currently manages the office. Additional data is held on each Manager, which includes the date that the Manager assumed his or her position at the current branch office, and a monthly bonus payment based upon his or her performance in the property for rent market. Staff Members of staff with the role of Supervisor are responsible for the day-to-day activities of an allocated group of staff called Assistants (up to a maximum of 10, at any one time). Not all members of staff are assigned to a Supervisor. The data stored regarding each member of staff includes staff number, name, address, position, salary, name of Supervisor (where applicable), and the details of the branch office at which a member of staff is currently working. The staff number is unique across all branches of Dream Home. Properties for rent each branch office offers a range of properties for rent. The data stored for each property includes property number, address (street, city, and postcode), type, and number of rooms, monthly rent, and the details of the property owner. The property number is unique across all branch offices. The management of a property is assigned to a member of staff whenever it is rented out or requires to be rented out. A member of staff may manage a maximum of 100 properties for rent at any one time. When a given property is available for rent, the property details will be displayed on the Dream Home Web site and, when necessary, as advertisements in local and national newspapers. Property owners the details of property owners are also stored. There are two main types of property owner: private owners and business owners. The data stored for private owners includes owner number, name, address, telephone number, email, and password. The data stored on business owners includes name of business, type of business, address, telephone number, email, password, and contact name. The password will allow owners access to parts of the Dream Home
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
(e) Identify the total number of staff in each position at branches in Glasgow.
(f) List the names of staff supervised by a named Supervisor.
(g) List the property number, address, type, and rent of all properties in Glasgow, ordered by rental amount.