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
computer sciences
databases
Modern Database Management 12th edition Jeff Hoffer, Ramesh Venkataraman, Heikki Topi - Solutions
Write SQL queries to answer the following questions: a. What are the courses included in the Section table? List each course only once. b. List all students in alphabetical order by StudentName. c. List the students who are enrolled in each course in Semester I, 2015. Group the students by the
Write SQL queries to answer the following questions: a. List the numbers of all sections of course ISM 3113 that are offered during the semester "1-2015." b. List the course IDs and names of all courses that start with the letters "Data." c. List the IDs of all faculty members who are qualified to
How many tutors have a status of Temp Stop? Which tutors are active?Based on the relations shown in Figure 6-12. The database tracks an adult literacy program. Tutors complete a certification class offered by the agency. Students complete an assessment interview that results in a report for the
What is the average Read score for all students? What are the minimum and maximum Read scores?Based on the relations shown in Figure 6-12. The database tracks an adult literacy program. Tutors complete a certification class offered by the agency. Students complete an assessment interview that
List the IDs of the tutors who are currently tutoring more than one student.Based on the relations shown in Figure 6-12. The database tracks an adult literacy program. Tutors complete a certification class offered by the agency. Students complete an assessment interview that results in a report for
What are the TutorlDs for tutors who have not yet tutored anyone?Based on the relations shown in Figure 6-12. The database tracks an adult literacy program. Tutors complete a certification class offered by the agency. Students complete an assessment interview that results in a report for the tutor
Explain what capabilities the new temporal features added to the SQL standard in SQL:2011.
How long had each student studied in the adult literacy program? Based on the relations shown in Figure 6-12. The database tracks an adult literacy program. Tutors complete a certification class offered by the agency. Students complete an assessment interview that results in a report for the tutor
What is the average length of time a student stayed (or has stayed) in the program?
Use the Pine Valley database to answer the following questions: a. How many work centers does Pine Valley have? b. Where are they located?
Describe a relational DBMS (RDBMS), its underlying data model, its data storage structures, and how data relationships are established.
List the customers who live in California or Washington. Order them by zip code, from high to low.
List the MateriallD, MaterialName, Material, MaterialStandardPrice, and Thickness for all raw materials made of cherry, pine, or walnut. Order the listing by Material, StandardPrice, and Thickness.
For each customer, list the CustomerlD and the total number of orders placed in 2015.
List six potential benefits of achieving an SQL standard that is widely accepted.
Which product is ordered most frequently?
For each salesperson, list the total number of orders by month for the year 2015. (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 you are
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.
List ProductID, ProductDescription, ProductFinish, and ProductStandardPrice for oak products with a ProductStandardPrice greater than $400 or cherry products with a StandardPrice less than $300. Show how you constructed this query using a Venn diagram.
Describe the components and structure of a typical SQL environment.
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.
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 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.
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
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
Arrange an interview with a database administrator in an organization in your area. When you interview the database administrator, familiarize yourself with one application that is actively used in the organization. Focus your interview questions on determining end users' involvement with the
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 Chapter 5 to specify indexes, if appropriate.
Distinguish among data definition commands, data manipulation commands, and data control commands.
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 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 7.
Define each of the following terms:a. Dynamic SQLb. Correlated subqueryc. Embedded SQLd. Proceduree. Joinf. Equijoing. Self joinh. Outer joini. Functionj. Persistent Stored Modules (SQL/PSM)
Explain the structure of a module of code that defines a trigger.
Discuss the differences between triggers and stored procedures.
List four advantages of SQL-invoked routines.
When would you consider using embedded SQL? When would you use dynamic SQL?
When do you think that the CASE keyword in SQL would be useful?
Describe an example in which you would want to use a derived table.
What is PL/SQL, and what does it include in addition to SQL?
Match the following terms to the appropriate definition: _________ Equijoin……………………………a. Undoes changes to a table _________ Natural join………………………..b. user-define data type _________ Outer join………………………….c. SQL.: 1999 extension _________
If two queries involved in a UNION operation contained columns that were data type incompatible, how would you recommend fixing this?
What is the purpose of the temporal extensions to SQL that were introduced in SQL:2011?
Discuss some of the mechanisms that were used for the same purposes as the temporal extensions before they were introduced.
This chapter discusses the data dictionary views for Oracle 12c. Research another RDBMS, such as Microsoft SQL Server, and report on its data dictionary facility and how it compares with Oracle.
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 all courses for which Professor Berndt has been qualified.c. Display the class roster, including student name, for all students enrolled in section
Write an SQL query to answer the following question: Which instructors are qualified to teach ISM 3113?
Write an SQL query to answer the following question: Is any instructor qualified to teach ISM 3113 and not qualified to teach ISM 4930?
Write SQL queries to answer the following questions: a. What are the names of the course(s) that student Altvater took during the semester 1-2015? b. List names of the students who have taken at least one course that Professor Collins is qualified to teach. c. How many students did Professor
Write SQL queries to answer the following questions: a. How many students were enrolled in section 2714 during semester 1-2015? b. How many students were enrolled in ISM 3113 during semester 1-2015?
When is an outer join used instead of a natural join?
Write an SQL query to answer the following question: Which students were not enrolled in any courses during semester 1-2015?
Determine the relationships among the four relations in Figure 7-17. 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 REPORTS when you set up its primary key.
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 find any tutors who have not submitted a report for July.
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 an SQL query to determine the total number of hours and the total number of lessons Tutor 106 taught in June and July 2015.
Write an SQL query to list the Read scores of students who were ever taught by tutors whose status is Dropped.
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.
Which tutors, by name, are available to tutor? Write the SQL command.
Which tutor needs to be reminded to turn in reports? Write the SQL command. Show how you constructed this query using a Venn or other type of diagram.
Write an SQL command that will find any customers who have not placed orders.
List the names and number of employees supervised (label this value HeadCount) for each supervisor who supervises more than two employees.
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 type of
Write an SQL command to display the order number, customer number, order date, and items ordered for some particular customer.
Write an SQL command to display each item ordered for order number 1, its standard price, and the total price for each item ordered.
Write an SQL command to total the cost of order number 1. Calculate the total raw material cost (label TotCost) for each product compared to its standard product price. Display product ID, product description, standard price, and the total cost in the result.
For every order that has been received, display the order ID, the total dollar amount owed on that order (you'll have to calculate this total from attributes in one or more tables; label this result TotalDue), and the amount received in payments on that order (assume that there is only one payment
What are some of the purpose for which you would use correlated subqueries?
Write an SQL query to list each customer who has bought computer desks and the number of units sold to each customer. Show how you constructed this query using a Venn or other type of diagram.
Write an SQL query to list each customer who bought at least one product that belongs to product line Basic in March 2015. List each customer only once.
Modify Problem and Exercise 7-51 so that you include the number of products in product line Basic that the customer ordered in March 2015.
Modify Problem and Exercise 7-52 so that the list includes the number of products each customer bought in each product line in March 2015.
List, in alphabetical order, the names of all employees (managers) who are now managing people with skill ID BS12; list each manager's name only once, even if that manager manages several people with this skill.
Display the salesperson name, product finish, and total quantity sold (label as TotSales) for each finish by each salesperson.
Write a query to list the number of products produced in each work center (label as TotalProducts). If a work center does not produce any products, display the result with a total of 0.
The production manager at PVFC is concerned about support for purchased parts in products owned by customers. A simple analysis he wants done is to determine for each customer how many vendors are in the same state as that customer. Develop a list of all the PVFC customers by name with the number
Display the names of the states in which customers reside but for which there is no salesperson residing in that state. There are several ways to write this query. Try to write it without any WHERE clause. Write this query two ways, using the set command UNION, INTERSECT, or MINUS and not using any
Explain the relationship between EXISTS and correlated subqueries.
Write an SQL query to produce a list of all the products (i.e., product description) and the number of times each product has been ordered. Show how you constructed this query using a Venn or other type of diagram.
Display the customer ID, name, and order ID for all customer orders. For those customers who do not have any orders, include them in the display once. Display the EmployeelD and EmployeeName for those employees who do not possess the skill Router.
Display the results in order by EmployeeName. Show how you constructed this query using a Venn or other type of diagram.
Display the name of customer 16 and the names of all the customers that are in the same zip code as customer 16. (Be sure this query will work for any customer.)
Rewrite your answer to Problem and Exercise 7-63 for each customer, not just customer 16.Display the name of customer 16 and the names of all the customers that are in the same zip code as customer 16. (Be sure this query will work for any customer.)
Display the customer ID, name, and order ID for all customer orders. For those customers who do not have any orders, include them in the display once by showing order ID 0.
Show the customer ID and name for all the customers who have ordered both products with IDs 3 and 4 on the same order.
Display the customer names of all customers who have ordered (on the same or different orders) both products with IDs 3 and 4.
Review the first query in the "Correlated Subqueries" section. Can you identify a special set of standard prices for which this query will not yield the desired result? How might you rewrite the query to handle this situation?
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.
List the IDs and names of all products that cost less than the average product price in their product line. List the IDs and names of those sales territories that have at least 50 percent more customers as the average number of customers per territory.
Write an SQL query to list the order number and order quantity for all customer orders for which the order quantity is greater than the average order quantity of that product.
Write an SQL query to list the salesperson who has sold the most computer desks.
Display in product ID order the product ID and total amount ordered of that product by the customer who has bought the most of that product; use a derived table in a FROM clause to answer this query.
Display employee information for all the employees in each state who were hired before the most recently hired person in that state.
The head of marketing is interested in some opportunities for cross-selling of products. She thinks that the way to identify cross-selling opportunities is to know for each product how many other products are sold to the same customer on the same order (e.g., a product that is bought by a customer
For each product, display in ascending order, by product ID, the product ID and description, along with the customer ID and name for the customer who has bought the most of that product; also show the total quantity ordered by that customer (who has bought the most of that product). Use a
Identify opportunities for using triggers in your database and write the appropriate DDL queries for them.
Create a strategy for reviewing your database implementation with the appropriate stakeholders. Which stakeholders should you meet with? What information would you bring to this meeting? Who do you think should sign off on your database implementation before you move to the next phase of the
What is the purpose of the COMMIT command in SQL? How does commit relate to the notion of a business transaction (e.g., entering a customer order or issuing a customer invoice)?
Define each of the following terms: a. Application partitioning b. Application program interface (API) c. Client/server system d. Middleware e. Stored procedure f. Three-tier architecture g. Open Database Connectivity (ODBC) h. XML Schema i. Web services j. XSLT k. SOAP
If you were charged with developing a client/server application, how would you ensure success?
What are the three common types of cloud computing services?
Showing 1800 - 1900
of 3228
First
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Last
Step by Step Answers
Get 50% OFF
Claim Now
