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 10th Edition Heikki Topi, Jeffrey A Hoffer, Ramesh Venkataraman - Solutions
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.
Write an SQL query to list each salesperson who has sold computer desks and the number of units sold by each salesperson.
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
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.
Write an SQL command to total the cost of order number 1.
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 display the order number, customer number, order date, and items ordered for order number 1.
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.
List the names and number of employees supervised (label this value HeadCount) for each supervisor who supervises more than two employees.
Write an SQL command that will find any customers who have not placed orders.
Which tutor needs to be reminded to turn in reports? Write the SQL command.
Which tutors, by name, are available to tutor? Write the SQL command.
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.
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 command to find any tutors who have not submitted a report for July.
What do you need to do if a tutor signs up and wants to tutor in both reading and math? Draw the new ERD and write any SQL statements that would be needed to handle this development.
Write the SQL command to add SUBJECT to TUTOR. The only values allowed for SUBJECT will be Reading, Math, and ESL.
Write the SQL command to add MATH SCORE to the STUDENT table.
Determine the relationships among the four entities in Figure 7-15. List primary keys for each entity 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.
Write an SQL query to answer the following question:Which students were not enrolled in any courses during semester I-2008?Problems and Exercises 6 through 14 are based on Figure 7-15.This problem set continues from Chapter 6, Problems and Exercises 10 through 15, which were based on Figure 6-12.
Write SQL queries to answer the following questions:a. How many students were enrolled in section 2714 during semester I-2008?b. How many students were enrolled in ISM 3113 during semester I-2008?
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 an SQL query to answer the following question:Which instructors are qualified to teach ISM 3113?
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
This chapter discusses the data dictionary views for Oracle 11g. Research another RDBMS, such as Microsoft SQL Server, and report on its data dictionary facility and how it compares with Oracle.
Can an outer join be easily implemented when joining more than two tables? Why or why not?
If two queries involved in a UNION operation contained columns that were data type incompatible, how would you recommend fixing this?
What other Oracle object can be used in place of a derived table? Which approach do you think is better?
Describe an example in which you would want to use a derived table.
Explain the use of derived tables.
When do you think that the CASE keyword in SQL would be useful?
When would you consider using embedded SQL? When would you use dynamic SQL?
List four advantages of SQL-invoked routines.
Explain the purpose of SQL/PSM.
Discuss the differences between triggers and stored procedures.
Under what conditions can a UNION clause be used?
Explain the structure of a module of code that defines a trigger.
Care must be exercised when writing triggers for a database. What are some of the problems that could be encountered?
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)?
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 processing order of a correlated subquery.
When is an outer join used instead of a natural join?
Define each of the following terms:a. dynamic SQLb. correlated subqueryc. embedded SQLd. proceduree. joinf. equi-join g. self join h. outer join i. function j. Persistent Stored Modules (SQL/PSM)
Write and execute a variety of queries, based on the introduction to SQL in this chapter to test the functionality of your database. Ensure that your queries are correct and produce the results you expected.
Select a portion of your database and populate it with sample data. Be prepared to defend the sample test data that you insert into your database.
Use the physical data model you created in Chapter 5 to guide you in writing the SQL statements for creating the MVCH database for the relational schema you created in Chapter 4.a. Write the SQL statements for creating the tables, specifying data types and field lengths, establishing primary keys
Using your database from Case Exercise 1, write and test SQL queries thata. Select information from only one of the tables.b. Aggregate information from one attribute in a table.c. Try out the various functions, such as MIN, MAX, and AVG.d. Qualify results by category
In Case Exercise 1 in Chapter 5, you created the physical data model for Dr. Z’s database that keeps track of patients checking in. You may recall that Dr. Z decided to use SQL Server. Instructions for installing SQL Server and SQL Server Management Studio Express are available in the Pine Valley
How do the actual values you are using help you to test the functionality of your database?
Can you suggest an easy way to populate your tables if you want to create a large set of test data?
Which CASE tools are available for completing the case exercises? Can the CASE tool you are using generate the database schema from the physical data model(s) you created?
What version of SQL and what RDBMS will you use to do the case exercises?
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 generate
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
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
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
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.
List ProductID, ProductDescription, ProductFinish, and ProductStandardPrice for oak products with a ProductStandardPrice greater than $400 or cherry products with a StandardPrice less than $300
List MaterialName, Material, and Width for raw materials that are not cherry or oak and whose width is greater than 10 inches.
For each salesperson, list the total number of orders by month for the year 2010. (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
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 NumSalesPersons.
Which product is ordered most frequently?
List all sales territories (TerritoryID) that have more than one salesperson.
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 CustomerID and the total number of orders placed in 2010.
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 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.
Display the product line ID and the average standard price for all products in each product line.
List the MaterialID, MaterialName, Material, MaterialStandardPrice, 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 customers who live in California or Washington.Order them by zip code, from high to low.
Which employees were hired during 1999?
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 QtyOnHand in the Product_T table. Test the modification you made in Problem and Exercise 16 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 not
Modify the Product_T table by adding an attribute QtyOnHand 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?
How long had each student studied in the adult literacy program?
Which student has the highest Read score?
How many students were matched with someone in the first five months of the year?
List the tutors who took the certification class in January.
How many tutors have a status of Temp Stop? Which tutors are active?
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, 2008. Group the students by the
Write SQL queries to answer the following questions:a. Which students are enrolled in Database and Networking?(Hint: Use SectionNo for each class so you can determine the answer from the Registration table by itself.)b. Which instructors cannot teach both Syst Analysis and Syst Design?
Write SQL queries to answer the following questions:a. How many students are enrolled in Section 2714 in the first semester of 2008?b. Which faculty members have qualified to teach a course since 1993? List the faculty ID, course, and date of qualification.
Write SQL queries to answer the following questions:a. Which students have an ID number that is less than 50000?b. What is the name of the faculty member whose ID is 4756?c. What is the smallest section number used in the first semester of 2008?
Write SQL commands for the following:a. Create two different forms of the INSERT command to add a student with a student ID of 65798 and last name Lopez to the Student table.b. Now write a command that will remove Lopez from the Student table.c. Create an SQL command that will modify the name of
Write SQL data definition commands for each of the following queries:a. How would you add an attribute, Class, to the Student table?b. How would you remove the Registration table?c. How would you change the FacultyName field from 25 characters to 40 characters?
Because of referential integrity, before any row can be entered into the SECTION table, the CourseID to be entered must already exist in the COURSE table. Write an SQL assertion that will enforce this constraint.
Use SQL to define the following view: StudentID StudentName 38214 Letersky 54907 Altvater 54907 Altvater 66324 Aiken
Is it possible to use both a WHERE clause and a HAVING clause in the same SQL SELECT statement? If so, what are the different purposes of these two clauses?
What can be changed about a table definition, using the ALTER SQL command? Can you identify anything about a table definition that cannot be changed using the ALTER SQL command?
Showing 2000 - 2100
of 3225
First
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Last
Step by Step Answers
Get 50% OFF
Claim Now
