Computer Science: An Overview (12th Edition)
12th Edition
ISBN: 9780133760064
Author: Glenn Brookshear, Dennis Brylow
Publisher: PEARSON
expand_more
expand_more
format_list_bulleted
Concept explainers
Expert Solution & Answer
Chapter 9, Problem 38CRP
Explanation of Solution
Sequence of operation using “JOIN”, “SELECT”, and “PROJECT”:
The sequence of operation for given SQL statement is given below:
LIST1 <- JOIN ASSIGNMENT and EMPLOYEE
where ASSIGNMENT.EmpId = EMPLOYEE.EmpId
LIST2 <- SELECT from LIST1 where EMPLOYEE.Name = 'Joe E.Baker'
ANSWER <- PROJECT StartDate from LIST2
Explanation for above sequence:
The given SQL statement is used to list start date of employee from two relations “Assignment” and “Employee” where the value of “Name” in relation “Employee” is “Joe E.Baker”
- From the given code, the statement “LIST1 <- JOIN ASSIGNMENT and EMPLOYEE where ASSIGNMENT.EmpId = EMPLOYEE.EmpId” performs join operation...
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solutionStudents have asked these similar questions
Table: Team Member
Primary key: Team MemberID
Team MemberID
1
2
3
Team MemberName
Joe Bloggs
Samantha Smith
Pete Ngwenya
Table: Task
Primary key: TaskID
Foreign key: Team MemberID
TaskID
1
2
3
Description
Build login screen
Implement inventory
management
Add logo to splahs 1
screen
TeamMemberEmail
joe@theoffice.com
sam@theoffice.com
pete@theoffice.com
Duration StartDate
Team MemberID
2022-05-06
1
2022-05-15 1
4
10
2022-05-06 2
Write SQL statements for each task:(i) To list the details of equipment (equipment code, name, description, rental rate per day) that cost above $10 to rent per day, with an equipment code that starts with ‘CAM’ and the equipment has been loaned to a customer who is at least 30 years old this year. Give the result in ascending order of the equipment name and rental rate per day. Use subquery.
Write SQL statement that retrieve the department_name that has the maximum number of employees:
Select one:
a.
select d_name
from depart
where depart_id=(
select depart_id
from emp
having count(*)=
(select max(count(*))
from emp
group by depart_id)
group by depart_id);
b.
select depart_name
from depart
where depart_id=(
select depart_id
from emp
where count(*)=
(select max(count(*))
from emp
group by depart_id)
group by depart_id);
c.
select depart_name
from depart
where depart_id=(
select depart_id
from emp
having count(*)=
(select max(count(*))
from empl
group by depart_id));
d.
select depart_name
from depart
where depart_id=(
select depart_id
from emp
having count(*)=
(select max(count(*))
from emp
group by depart_id)
group by depart_id);
Chapter 9 Solutions
Computer Science: An Overview (12th Edition)
Ch. 9.1 - Identify two departments in a manufacturing plant...Ch. 9.1 - Prob. 2QECh. 9.1 - Summarize the roles of the application software...Ch. 9.2 - Prob. 1QECh. 9.2 - Prob. 2QECh. 9.2 - Prob. 4QECh. 9.2 - Prob. 5QECh. 9.2 - Prob. 6QECh. 9.3 - Prob. 1QECh. 9.3 - What is a persistent object?
Ch. 9.3 - Identify some classes as well as some of their...Ch. 9.3 - Prob. 4QECh. 9.4 - Prob. 1QECh. 9.4 - Prob. 2QECh. 9.4 - Prob. 3QECh. 9.4 - Prob. 4QECh. 9.4 - Prob. 5QECh. 9.4 - Prob. 6QECh. 9.5 - Prob. 1QECh. 9.5 - Prob. 2QECh. 9.5 - Prob. 3QECh. 9.5 - Prob. 4QECh. 9.5 - Prob. 5QECh. 9.5 - Prob. 6QECh. 9.5 - Prob. 7QECh. 9.6 - Prob. 1QECh. 9.6 - Give an additional example of a pattern that might...Ch. 9.6 - Prob. 3QECh. 9.6 - How does data mining differ from traditional...Ch. 9.7 - Prob. 1QECh. 9.7 - Prob. 2QECh. 9.7 - Prob. 3QECh. 9.7 - Prob. 4QECh. 9 - Prob. 1CRPCh. 9 - Prob. 2CRPCh. 9 - Prob. 3CRPCh. 9 - Prob. 4CRPCh. 9 - Prob. 5CRPCh. 9 - Prob. 6CRPCh. 9 - Prob. 7CRPCh. 9 - Prob. 8CRPCh. 9 - Prob. 9CRPCh. 9 - Prob. 10CRPCh. 9 - Prob. 11CRPCh. 9 - Prob. 12CRPCh. 9 - Using the commands SELECT, PROJECT, and JOIN,...Ch. 9 - Answer Problem 13 using SQL. PROBLEM 13 13. Using...Ch. 9 - Prob. 15CRPCh. 9 - Prob. 16CRPCh. 9 - Prob. 17CRPCh. 9 - Prob. 18CRPCh. 9 - Prob. 19CRPCh. 9 - Empl Id Name Address SSN Job Id Job Title Skill...Ch. 9 - Empl Id Name Address SSN Job Id Job Title Skill...Ch. 9 - Prob. 22CRPCh. 9 - Prob. 23CRPCh. 9 - Prob. 24CRPCh. 9 - Prob. 25CRPCh. 9 - Write a sequence of instructions (using the...Ch. 9 - Prob. 27CRPCh. 9 - Prob. 28CRPCh. 9 - Prob. 29CRPCh. 9 - Prob. 30CRPCh. 9 - Prob. 31CRPCh. 9 - Prob. 32CRPCh. 9 - Prob. 33CRPCh. 9 - Prob. 34CRPCh. 9 - Prob. 35CRPCh. 9 - Prob. 36CRPCh. 9 - Prob. 37CRPCh. 9 - Prob. 38CRPCh. 9 - Prob. 39CRPCh. 9 - Prob. 40CRPCh. 9 - Prob. 41CRPCh. 9 - Prob. 42CRPCh. 9 - Prob. 43CRPCh. 9 - Prob. 44CRPCh. 9 - Prob. 45CRPCh. 9 - Prob. 46CRPCh. 9 - Prob. 47CRPCh. 9 - Prob. 48CRPCh. 9 - Prob. 49CRPCh. 9 - Prob. 50CRPCh. 9 - Prob. 51CRPCh. 9 - Prob. 52CRPCh. 9 - Prob. 53CRPCh. 9 - Prob. 54CRPCh. 9 - Prob. 55CRPCh. 9 - Prob. 56CRPCh. 9 - Prob. 57CRPCh. 9 - Prob. 58CRPCh. 9 - Prob. 59CRPCh. 9 - Prob. 60CRPCh. 9 - Prob. 61CRPCh. 9 - Prob. 62CRPCh. 9 - Prob. 1SICh. 9 - Prob. 2SICh. 9 - Prob. 3SICh. 9 - Prob. 4SICh. 9 - Prob. 5SICh. 9 - Prob. 6SICh. 9 - Prob. 7SICh. 9 - Prob. 8SICh. 9 - Prob. 9SICh. 9 - Prob. 10SI
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Similar questions
- Access path A database table is composed of records and fields hold data. Data is stored in records. A field is a part of a record and contains a single piece of data for the subject of the record. The access path for finding the list of books is AUTHOR: AuthorID, Lname BOOKAUTHOR: AuthorID, ISBN BOOKS: Pubdate, ISBNarrow_forwardIndicate dependences and their type.arrow_forwardI need help with understanding how to do the SQL code for the following question (attached is the CSV database file): These are the header titles from the picture: ProductModelName, ProductCategory, ProductPrice, RetailerName, RetailerZip, RetailerCity, RetailerState, ProductOnSale, ManufacturerName, ManufacturerRebate, UserID, UserAge, UserGender, UserOccupation, ReviewRating, ReviewDate, Review Text 1. Get the total number of products reviewed and got Rating 5 in Every Cityarrow_forward
- Can you Write the SQL statements to do the following please : 1. Display the artist's name from the Artist table in uppercase.2. Display the person's Key, First Name, Last Name and Area Code (first 3 digits of the phone number) from the Person table and label the output as such. 3. Display the current date and five days from that date.4. Display the difference between the current date and the PurchaseDate of the Inventory.5. Display the day of the week, in words, on which the Purchases were place. (Hint: Use datepart dw.)6. Display the minimum, maximum, sum, average, count fields, count records for the SaleDetailPrice from the SaleDetail table. Appropriately label the output.7. Display the count of phone numbers in the Person table.8. Display the record count of the Person table.arrow_forwardWrite the SQL statements to create all of the necessary tables to represent the above diagram, representing as many constraints as possible. Note: you do not need to worry about specifying types for the fields, but you do need to provide suitable foreign key option(s). When playing a game, the home team name should be stored in attribute htname and the away team name should be stored in atname.arrow_forwardIndicate for each of the following SQL statements, how many tuples would be returned if it was run on the table of Articles given above. 1. SELECT NUMBER FROM ARTICLES WHERE YEAR < 2019;2. SELECT DISTINCT NUMBER FROM ARTICLES WHERE YEAR < 2019;3. SELECT AVG(YEAR) FROM ARTICLES GROUP BY PUBLICATION;4. SELECT NUMBER FROM ARTICLES WHERE TITLE LIKE ‘%D’;arrow_forward
- Write an SQL statement to list ItemNumber and ItemDescription for all items that cost more that $100 and were produced by an artist with a name ending with the letters son. From Database Management: Chapter 3 Structured Query Language Kroenkearrow_forwardBased on SQL informationarrow_forwardTrying to write SQL statements for these shown here and having issues with them working. Explanation on this would be appreciated.arrow_forward
- Write SQL statements to answer the following questions using Assignment 3’s schema (tables below). You can add more data to the tables if you want, just follow the PK and FK rules. Accounts are not Loans and Loans are not Accounts. NOTE I already have Answers for part 1 BELOW PART 1 IS FOR ONLY REFRENCE TO ANSWER QUESTIONS 6-11 6- Find Customer ID, Customer name and the number of loans for each Customer. 7- Find Loan number and Customer Id of the loan with the lowest amount. 8- Create a view called Gary_Branch_V that contains Branch Id, Branch name, and number of loans for each Branch that is in the city of Gary. 9- For each Customer in Hopkins, find the balance in their account(s). 10- Find how many different accounts each customer has at each Branch. The output should be a list of Customer ID and for each Customer ID, the number of accounts for this customer by Branch ID. 11- Find the branch with the highest or largest Average loan amount. List the Branch ID, Branch…arrow_forwardWrite the following subqueries for these statements in SQL using this ERD diagram 1. CustomerNumberOfOrders: Make a complete list of the customer who placed maximumnumber of orders along with order number. 2. Find the information against those order, whose customer and employee city name issame.Your query must display the OrderNo, Customer CompanyName, Employee FirstName and City. 3. Find the top and least selling Product name. 4. OutOfStockProducts: Make a list of products that are out of stock. 5. Find the employee name who tackle minimum number of orders.arrow_forwardAnswer the following questions based on the information in the PATIENT table below. Write SQL queries statement and provide the output of each query. (Screenshot the interface in MySQL that show SQL statement and output for each question). PATIENT PID PNAME AGE ADATE 1001 JAMAL 35 2000-10-28 M 1002 TAN 52 1003 MARIAM 30 2000-11-20 M 2000-07-16 F 2000-12-18 M 1004 JOE 27 1005 SOFEA 19 2000-04-25 F 1) Change the admission date of Sofea to 18-12-2000. GENDERarrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
- Programming Logic & Design ComprehensiveComputer ScienceISBN:9781337669405Author:FARRELLPublisher:CengageA Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr
Programming Logic & Design Comprehensive
Computer Science
ISBN:9781337669405
Author:FARRELL
Publisher:Cengage
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr