New Semester
Started
Get
50% OFF
Study Help!
--h --m --s
Claim Now
Question Answers
Textbooks
Find textbooks, questions and answers
Oops, something went wrong!
Change your search query and then try again
S
Books
FREE
Study Help
Expert Questions
Accounting
General Management
Mathematics
Finance
Organizational Behaviour
Law
Physics
Operating System
Management Leadership
Sociology
Programming
Marketing
Database
Computer Network
Economics
Textbooks Solutions
Accounting
Managerial Accounting
Management Leadership
Cost Accounting
Statistics
Business Law
Corporate Finance
Finance
Economics
Auditing
Tutors
Online Tutors
Find a Tutor
Hire a Tutor
Become a Tutor
AI Tutor
AI Study Planner
NEW
Sell Books
Search
Search
Sign In
Register
study help
business
database management systems
Modern Database Management 13th Global Edition Jeff Hoffer, Ramesh Venkataraman, Heikki Topi - Solutions
. Write an SQL query to total the cost of order number 1.
. Write an SQL query that lists those work centers that employ at least one person who has the skill ‘QC1’.
. Write an SQL query to display the total number of employees working at each work center (include ID and location for each work center).
. Write an SQL query to display each item ordered for order number 1, its standard price, and the total price for each item ordered.
. Write an SQL query to display the order number, customer number, order date, and items ordered for some particular customer.
. List the name of each employee, his or her birth date, the name of his or her manager, and the manager’s birth date for those employees who were born before their manager was born; label the manager’s data Manager and ManagerBirth. Show how you constructed this query using a Venn or other
. List the names and number of employees supervised(label this value HeadCount) for each supervisor who supervises more than two employees.
. Modify P&E 6-46 to include only those product lines the average price of which is higher than $200.
. Write an SQL query to list all product line names and, for each product line, the number of products and the average product price. Make sure to include all product lines separately.
. Write an SQL query that will find any customers who have not placed orders.
. Which tutor needs to be reminded to turn in reports?Write the SQL query. Show how you constructed this query using a Venn or other type of diagram.Problems and Exercises 6-45 through 6-85 are based on the entire (“big” version)Pine Valley Furniture Company database.Note: Depending on what
. Which tutors, by name, are available to tutor? Write the SQL query.
. List the total number of lessons taught in 2018 by tutors in each of the three Status categories (Active, Temp Stop, and Dropped).
. For each student group, list the number of tutors who have been matched with that group.
. List all active students in June by name. (Make up names and other data if you are actually building a prototype database.) Include the number of hours students received tutoring and how many lessons they completed.
. Write an SQL query to list the Read scores of students who were ever taught by tutors whose status is Dropped.
. Write an SQL query to determine the total number of hours and the total number of lessons Tutor 106 taught in June and July 2018.
. Where do you think student and tutor information such as name, address, phone, and e-mail should be kept?Write the necessary SQL commands to capture this information.
. Write the SQL query to find any tutors who have not submitted a report for July.
. Write a SQL query to identify all students who have been matched in 2018 with a tutor whose status is Temp Stop.
. What do you need to do if a tutor signs up and wants to tutor in both reading and math? Draw the new ERD, create new relations, and write any SQL statements that would be needed to handle this development.
. Write the SQL command to add column SUBJECT to TUTOR. The only values allowed for SUBJECT will be Reading, Math, and ESL.
. Write the SQL command to add column MATH SCORE to the STUDENT table.
. Determine the relationships among the four relations in Figure 6-16. List primary keys for each relation and any foreign keys necessary to establish the relationships and maintain referential integrity. Pay particular attention to the data contained in TUTOR REPORT when you set up its primary key.
. Write an SQL query to answer the following questions:a. Which students were not enrolled in any courses during semester I-2018?b. Which faculty members are not qualified to teach any courses?Problems and Exercises 6-31 through 6-44 are based on Figure 6-16.This problem set continues from Chapter
. Write SQL queries to answer the following questions:a. What are the names of the course(s) that student Altvater took during the semester I-2018?b. List the names of the students who have taken at least one course that Professor Collins is qualified to teach.c. List the names of the students who
. Write SQL queries to answer the following questions:a. How many students were enrolled in section 2714 during semester I-2018?b. How many students were enrolled in ISM 3113 during semester I-2018?
. Write an SQL query to answer the following question: Is any instructor qualified to teach ISM 3113 and not qualified to teach ISM 4930? If yes, list the faculty ID and name of the instructor.
. Write SQL retrieval commands for each of the following queries:a. Display the course ID and course name for all courses with an ISM prefix.b. Display the numbers and names of all courses for which Professor Berndt has been qualified.c. Display the class roster, including student name, for all
. Write an SQL query to answer the following question:Which instructors are qualified to teach ISM 3113?
. Research a NoSQL database such as MondoDB or Firebase.Why is it important to consider a metadata-based schema when working with NoSQL databases?
. Discuss the new commands that have been incorporated into SQL2007 and SQL2016, and identify the commands they have replaced in previous versions of SQL.
. What are the key new features of SQL introduced in SQL:2016?
. What is the purpose of the temporal extensions to SQL that were introduced in SQL:2011?
. What are the potential security implications of using embedded or dynamic SQL?
. Provide three reasons for embedding SQL in a 3GL.
. Discuss the differences between triggers and stored procedures.
. Explain three procedures to enforce data integrity.
. What can Persistent Stored Modules be used for?
. Why is it not possible to update a base table via update commands against a view?
. Describe an example in which you would want to use a derived table.
. Explain the use of derived tables.
. Explain the purpose of the WITH CHECK OPTION in a CREATE VIEW SQL command.
. When should we use joining and sub-query techniques?
. Explain why it is necessary to limit the kinds of updates performed on data when referencing data through a view.
. Explain some possible purposes of creating a view using SQL. In particular, explain how a view can be used to reinforce data security.
. Explain how to combine queries using the UNION clause.
. Explain the following statement regarding SQL: Any query that can be written using the subquery approach can also be written using the joining approach but not vice versa.
. Explain the relationship between EXISTS and correlated subqueries.
. What are some of the purposes for which you would use correlated subqueries?
. When is it better to use a subquery instead of a join?
. Discuss the differences between an equi-join, natural join, and outer join.
. Match the following terms to the appropriate definition:equi-join derived table natural join correlated subquery outer join triggera. returns all records of designated tableb. keeps redundant columnsc. utilizes values from main query in the subqueryd. outcome of a query embedded in the FROM
. Define each of the following terms:a. dynamic viewb. correlated subqueryc. materialized viewd. base tablee. joinf. equi-join g. self join h. outer join i. virtualized table
. Write and execute a variety of queries to test the functionality of your database based on what you learned in this chapter. Don’t panic if you can’t write all the queries;many of the queries will require knowledge from Chapter 6. Your instructor may specify for which reports or displays you
. Based on your findings from 5-98 above, populate the tables in your database with sample data that can potentially allow you to test/demonstrate that your database can generate these reports.
. Reread the case descriptions in Chapters 1 through 3 with an eye toward identifying the typical types of reports and displays the various stakeholders might want to retrieve from your database. Create a document that summarizes these findings.
. Write the SQL statements for creating the tables, specifying data types and field lengths, establishing primary keys and foreign keys, and implementing any other constraints you may have identified. Use the examples shown in this chapter to specify indexes, if appropriate.
. Arrange an interview with a database administrator in your area who has at least seven years of experience as a database administrator. Focus the interview on understanding how DBA responsibilities and the way they are completed have changed during the DBA’s tenure. Does the DBA have to
. Arrange an interview with a database administrator in your area. Focus the interview on understanding the environment within which SQL is used in the organization.Inquire about the version of SQL that is used and determine whether the same version is used at all locations.If different versions
. Locate three database administrator vacancies advertised online and assess the requirements for the position. What is the key knowledge and skill employers require of a database administrator? If you were interested in a career in administration, how would you go about acquiring this essential
. Tyler Richardson set up a house alarm system when he moved to his new home in Seattle. For security purposes, he has all of his mail, including his alarm system bill, mailed to his local UPS store. Although the alarm system is activated and the company is aware of its physical address, Richardson
. Not all versions of this database include referential integrity constraints for all foreign keys. Use whatever commands are available for the RDBMS you are using, investigate if any referential integrity constraints are missing.Write any missing constraints and, if possible, add them to the
. List the average number of customers per state (including only the states that are included in the Customer_T table).Hint: A query can be used as a table specification in the FROM clause.
. For products 1, 2, and 7, list in one row and three respective columns that product’s total unit sales; label the three columns Prod1, Prod2, and Prod7.
. For each customer, list the customer ID, the number of orders from that customer, and the ratio of the number of orders from that customer to the total number of orders from all customers combined. (This ratio, of course, is the percentage of all orders placed by each customer.)
. For each order, list the order ID, customer ID, order date, and most recent date among all orders. Show how you constructed this query using a Venn diagram.
. List ProductID, ProductDescription, ProductFinish, and ProductStandardPrice for oak products with a Product-StandardPrice greater than $400 or cherry products with a StandardPrice less than $300. Show how you constructed this query using a Venn diagram.
. List MaterialName, Material, and Width for raw materials that are not cherry or oak and whose width is greater than 10 inches. Show how you constructed this query using a Venn diagram.
. For each salesperson, list the total number of orders by month for the year 2018. (Hint: If you are using Access, use the Month function. If you are using Oracle, convert the date to a string, using the TO_CHAR function, with the format string ‘Mon’ [i.e., TO_CHAR(order_date,’MON’)].If
. Display the SalesPersonID and a count of the number of orders for that salesperson for all salespersons except salespersons 3, 5, and 9. Write this query with as few clauses or components as possible, using the capabilities of SQL as much as possible.
. Display the territory ID and the number of salespersons in the territory for all territories that have more than one salesperson. Label the number of salespersons NumSales-Persons.
. Measured by average standard price, what is the least expensive product finish?
. For employees who live in TN or FL, list the age at which they were hired.
. Which product is ordered most frequently?
. List all sales territories (TerritoryID) that have more than one salesperson.
. Assume that for those materials the ID of which starts with a numeric character, the last three letters of the ID represent a wood type. Further, assume that the numeric part of MaterialID(everything except the last three characters) is called material type. For each material type, list the
. For each customer who had more than two orders, list the CustomerID and the total number of orders placed.
. For each salesperson, list the total number of orders.
. For each customer, list the customer ID and the total number of orders placed in 2018.
. For each payment made on or after March 10, 2018, list PaymentID, OrderID, PaymentAmount, and the first 10 characters of PaymentComment.
. Display the product ID and the number of orders placed for each product. Show the results in decreasing order by the number of times the product has been ordered and label this result column NumOrders.
. For each salesperson, display a list of CustomerIDs.
. For each customer, list the CustomerID and total number of orders placed.
. For each order, display the order ID, the number of separate products included in the order, and the total number of product units (for all products) ordered.
. For every product that has been ordered, display the product ID and the total quantity ordered (label this result TotalOrdered). List the most popular product first and the least popular last.
. Modify query in P&E 5-67 by considering only those products the standard price of which is greater than $200.Include in the answer set only those product lines that have an average standard price of at least $500.
. Display the product line ID and the average standard price for all products in each product line.
. List the MaterialID, MaterialName, Material, Material-StandardPrice, and Thickness for all raw materials made of cherry, pine, or walnut. Order the listing by Material, StandardPrice, and Thickness.
. List all raw materials that are made of cherry and that have dimensions (thickness and width) of 12 by 12.
. List the number of customers living at each state that is included in the Customer_T table.
. List the customers who live in California or Washington.Order them by zip code, from high to low.
. Which employees were hired during 2005?
. List the employees whose last names begin with an L.
. Use the Pine Valley database to answer the following questions:a. How many work centers does Pine Valley have?b. Where are they located?
. Add an order to the Order_T table and include a sample value for every attribute.a. First, look at the data in the Customer_T table and enter an order from any one of those customers.b. Enter an order from a new customer. Unless you have also inserted information about the new customer in the
. Enter sample data of your own choosing into QtyOn-Hand in the Product_T table. Test the modification you made in Problem and Exercise 5-57 by attempting to update a product by changing the inventory to 10,000 units. Test it again by changing the inventory for the product to −10 units. If you do
. Modify the Product_T table by adding an attribute Qty-OnHand that can be used to track the finished goods inventory. The field should be an integer field of five characters and should accept only positive numbers.
. What is the average length of time a student stayed (or has stayed) in the program?Problems and Exercises 5-57 through 5-93 are based on the entire (“big” version) Pine Valley Furniture Company database.Note: Depending on what DBMS you are using, some field names may have changed to avoid
. How many tutors have an Active status in the database?
Showing 1200 - 1300
of 3225
First
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Last
Step by Step Answers
Get 50% OFF
Claim Now
