5.1 For each of the join examples (Examples 7–10) in Section 5.4.2, replace the join by a subquery, if possible. If not possible, explain why not.
Directions for Exercises 5.2 through 5.24:
Figure 5.3 shows the DDL for creating the Workers and Projects database with the following schema:
![A schema for creating the Workers and Projects database.
Line 1. D e p t, open parentheses, d e p t Name, comma, m g r I d, close parentheses.
The variable d e p t Name is underlined.
Line 2. Worker, open parentheses, e m p I d, comma, e m p Name, comma, date Hired, comma, birth date, comma, salary, comma, d e p t Name, close parentheses.
An arrow points from the variable d e p t Name in Line 2 to the variable of the same name in Line 1.
Line 3. Project, open parentheses, p r o j N o, comma, p r o j Name, comma, start Date, comma, budget, comma, expected Duration, comma, p r o j M g r I d, close parentheses.
The variable p r o j N o is underlined.
Line 4. Assign, open parentheses, e m p I d, comma, p r o j N o, comma, hours, comma, rating, close parentheses.
The variables, e m p I d, and p r o j N o are underlined.
An arrow points from the variable p r o j N o in Line 4 to the variable of the same name in Line 3.
An arrow points from the variable e m p I d in line 4 to the variable of the same name in Line 2. An arrow points from the variable p r o j M g r I d in line 3 to the variable e m p I d in line 2. An arrow points from the variable m g r I d in line 1 to the variable e m p I d in line 2.](https://imgdetail.ebookreading.net/2023/10/9781284231595/9781284231595__9781284231595__files__images__9781284236354_CH05_UNNUNF03.jpg)
The SQL code shown in Figure 5.3 is available at go.jblearning.com/Ricardo4e. Execute the code to create an Oracle database for this example. Write the SQL commands for each of the queries in Exercises 5.2–5.24 and execute them.
If Oracle is not available, you can use another RDBMS, including the community edition of MySQL, which you can download for free. Depending on the product, you may need to make some changes to the DDL.
5.2 Get the names of all workers in the Accounting department.
5.3 Get an alphabetical list of names of all workers assigned to project 1001.
5.4 Get the name of the employee in the Research department who has the lowest salary.
5.5 Get details of the project with the highest budget.
5.6 Get the names and departments of all workers on project 1019.
5.7 Get an alphabetical list of names and corresponding ratings of all workers on any project that is managed by Michael Burns.
5.8 Create a view that has the project number and name of each project, along with the IDs and names of all workers assigned to it.
5.9 Using the view created in Exercise 5.9, find the project number and project name of all projects to which employee 110 is assigned.
5.10 Add a new worker named Jack Smith with ID of 1999 to the Research department.
5.11 Change the hours that employee 110 is assigned to project 1019, from 20 to 10.
5.12 For all projects starting after February 1, 2022, find the project number and the IDs and names of all workers assigned to them.
5.13 For each project, list the project number and how many workers are assigned to it.
5.14 Find the employee names and department manager names of all workers who are not assigned to any project.
5.15 Find the details of any project with the word “urn” anywhere in its name.
5.16 Get a list of project numbers and names and starting dates of all projects that have the same starting date.
5.17 Add a column called status to the Project table. Sample values for this column are active, completed, planned, and cancelled.
5.18 Get the employee ID and project number of all employees who have no ratings on that project.
5.19 Assuming that salary now contains annual salary, find each worker’s ID, name, and monthly salary.
5.20 Add a column called numEmployeesAssigned to the Project table. Use the UPDATE command to insert values into the column to correspond to the current information in the Assign table.
5.21 Write an Oracle data dictionary query to show the names of all the columns in the Worker table.
5.22 Write an Oracle data dictionary query to find all information about all columns named PROJNO.
5.23 Write an Oracle data dictionary query to get a list of names of people who have created tables that you have access to, along with the number of tables each has created.
5.24 Write an Oracle data dictionary query to find the names of tables that have more than two indexes.
5.25
Create a table for the following schema, using a default-only IDENTITY column for the id:
Customer(id, lastName, firstName, phoneNumber, creditLimit)
Insert five records, providing id values for only three of them.
Write a query to show that all the records have id values and execute it.
5.26
Write the DDL commands needed to create a relational database for the schema for the horse racing example shown in Chapter 4, Figure 4.8.
Make up data and enter records for at least five horses (name one of them “Lucky”), six people (including owners, trainers, and jockeys), and at least one race, with at least five entries.
Write SQL queries for each of the following and execute them:
(i) What are the name and telephone number of the person who trained Lucky?
(ii) What was Lucky’s final position in a given race?
(iii) What are the name and address of the jockey who rode the winning horse in a particular race?