Modern Database Management
13th Edition
ISBN: 9780134773650
Author: Hoffer
Publisher: PEARSON
expand_more
expand_more
format_list_bulleted
Question
Chapter 1, Problem 1.47PAE
(a)
Program Plan Intro
The way Sales to Date calculated in the given SQL query.
(b)
Program Plan Intro
The change in the given query if Helen Jarvis wanted to see result for all product line and not for only Home office Product Line.
(c)
Program Plan Intro
Explain how the two types of conditions with the WHERE clause is different from each other?
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solutionStudents have asked these similar questions
Consider the star schemas from Figure 11.2. Suppose an analyst finds that monthly total sales (sum of the price values of all sales tuples) have decreased, instead of growing, from April 2018 to May 2018. The analyst wishes to check if there are specific item categories, stores, or customer countries that are responsible for the decrease.
What are the aggregates that the analyst would start with, and what are the relevant drill-down operations that the analyst would need to execute?
Write an SQL query that shows the item categories that were responsible for the decrease in sales, ordered by the impact of the category on the sales decrease, with categories that had the highest impact sorted first.
Using SQL data definition language, write the SQL statements to create each of the following relations. i. customer(cust id, lname, fname, initial, area code, phone, balance)ii. product(prod id, vendor id, description, expiry date, quantity, reorder level, price, discount)iii. vendor(vendor id, name, contact, area code, phone, state)iv. invoice(inv number, cust id, date, subtotal, tax, grand total)Note: Use primary key, foreign key and not null constraints where necessary.
Using SQL data definition language, write the SQL statements to create each of the following relations. i. student(student id, name, dept id, total credits)ii. department(dept id, building, budget)iii. lecture_hall(hall id, campus, capacity)iv. course(course id, title, dept id, credits)
v. takes(student id, course id, semester, year, grade)
Chapter 1 Solutions
Modern Database Management
Ch. 1 - Prob. 1.1RQCh. 1 - Prob. 1.2RQCh. 1 - Prob. 1.3RQCh. 1 - Prob. 1.4RQCh. 1 - Prob. 1.5RQCh. 1 - Prob. 1.6RQCh. 1 - Prob. 1.7RQCh. 1 - How are relationships between tables expressed in...Ch. 1 - What does the term data independence mean, and why...Ch. 1 - Prob. 1.10RQ
Ch. 1 - Prob. 1.11RQCh. 1 - Prob. 1.12RQCh. 1 - Prob. 1.13RQCh. 1 - Prob. 1.14RQCh. 1 - Prob. 1.15RQCh. 1 - Prob. 1.16RQCh. 1 - Prob. 1.17RQCh. 1 - Prob. 1.18RQCh. 1 - Prob. 1.19RQCh. 1 - Prob. 1.20RQCh. 1 - Prob. 1.21RQCh. 1 - Prob. 1.22RQCh. 1 - Prob. 1.23RQCh. 1 - Prob. 1.24RQCh. 1 - Prob. 1.25RQCh. 1 - Prob. 1.26RQCh. 1 - Prob. 1.27PAECh. 1 - Prob. 1.28PAECh. 1 - Prob. 1.29PAECh. 1 - Prob. 1.30PAECh. 1 - Prob. 1.31PAECh. 1 - Prob. 1.32PAECh. 1 - Great Lakes Insurance would like to implement a...Ch. 1 - Figure 1-22 shows an enterprise data model for a...Ch. 1 - Prob. 1.35PAECh. 1 - Prob. 1.36PAECh. 1 - Prob. 1.37PAECh. 1 - Prob. 1.38PAECh. 1 - Prob. 1.39PAECh. 1 - Prob. 1.40PAECh. 1 - Prob. 1.41PAECh. 1 - Prob. 1.42PAECh. 1 - Prob. 1.43PAECh. 1 - Prob. 1.44PAECh. 1 - Consider the project data model shown in Figure...Ch. 1 - Answer the following questions concerning Figures...Ch. 1 - Prob. 1.47PAECh. 1 - Helen Jarvis wants to determine the most important...Ch. 1 - In this chapter, we described four important data...
Knowledge Booster
Similar questions
- Toys Galore currently has a credit limit of 7,500. Because Toys Galore has an excellent credit rating, TAL Distributors is increasing the companys credit limit to 10,000. If yourun the SQL query in Exercise 1 after the credit limit has been increased, would ToysGalore still be included in the view? Why or why not?arrow_forwardAnswer the following questions according to the figure which shows the ERD(Entity Relationship Diagram) of the Hospital system: What is the command of SQL language used to delete the whole table?arrow_forwardConsider a scenario in which the SQL statement is of the form SELECT DISTINCT c1, c2, c3 from T. Why might it make sense to create an index to optimize this query?arrow_forward
- Using SQL data definition language, write the SQL statements to change each of the following relations according to the suggestions given below.i. customer (customer id, customer name, street, city)Suggestion: Remove customer name and add lname, fname and initial.ii. account (account number, customer id, branch id, balance)Suggestion: Add a foreign key constraints for customer id and branch id, referencing customer and branch tables.iii. branch(branch id, branch name, branch city, assets)Suggestion: Add the not null constraint to branch name and branch city.arrow_forwardWhat does a given primary key mean within the big-picture context of a problem? What are primary, foreign, external, surrogate, and substitute keys? Be prepared to write SELECT, INSERT, UPDATE, or DELETE queries by hand. including SELECT statements with JOIN, WHERE, and/or ORDER BY What are the Sigma and Pi operators in relational algebra? Can you use them to express the equivalent of a given SQL query?arrow_forwardthank you for the tips! but for demonstration can you break down this sql query to help me understand better what u mean in a practical sense? Person (SSN, Name, Surname, DateOfBirth, Gender) SerologicalTest (CodT, CommercialName, Brand, Reliability) Building(CodB, City, Province Region, MaxCapacity) UndergoTest (SSN, CodT, Date, CodB, Outcome) Considering only the buildings located in the Piedmont region (Region = “Piedmont”), find the dates of June 2020 at which the overall number of tests made in that building is maximal. please keep in mind that im a beginner but also please explain everything rigorously i really have to understand the thought process before 4 days so i can at least solve questions of this level. its just relational tables nothing more for the exam for now.arrow_forward
- Question One From the Salesman and Customer tables below, write SQL statements to do the following: List the salesman id, rating and names of those salesmen who get rating from 3 to 5, sorted DESCENDING by rating. List all the customers’ information whose name starts with ‘F’ and the fourth character is ‘I’, rests may be any character. List the name and country of those customers and salesmen who belongs to the same country.arrow_forwardConsider following schema and write SQL for given queries. LIST OF TABLES: Visitors (visitor id, name, age, gender, address, contact_no, email, entry_time, exit_time, date_of_visit, mode_of_transport) Events (event_id, event_name, capacity, timings, category) Employee (emp_id, emp_name, address, contact_no, gender, department, shift, salary, dob, join_date, event_id) Maintenance_problems (complaint_id, complaint_name, date_of_complaint, status, date_of_completion, employee_id, event_id) Bookings (ticket_no, payment_mode, card_type, price_before_discount, price_after_discount, no_of_visitors, visitor_id, event_id) Give the name of the visitors whose mode of transport to amusement park is bus. Give the name and address of the visitors who visited the amusement park on ‘02-10-2018’. Give the Name of the events which has the accommodation for more than 300 visitors. Give the time of event E150. List the details of male employees who work during night shift. Give the contact number of the…arrow_forward
arrow_back_ios
arrow_forward_ios
Recommended textbooks for you
- A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr