Modern Database Management (12th Edition)
12th Edition
ISBN: 9780133544619
Author: Jeffrey A. Hoffer, Ramesh Venkataraman, Heikki Topi
Publisher: PEARSON
expand_more
expand_more
format_list_bulleted
Concept explainers
Videos
Textbook Question
Chapter 5, Problem 5.27PAE
Consider the following two relations for Millennium College:
STUDENT(StudentID, StudentName,
CampusAddress, GPA)
REGISTRATION (StudentID, CourselD, Grade)
Following is a typical query against these relations:
SELECT Student_T.StudentID, StudentName,
CourselD, Grade
FROM Student_T, Registration_T
WHERE Student_T.StudentID =
Registration_T.StudentID
AND GPA >3.0
ORDER BY StudentName;
- On what attributes should indexes be defined to speed up this query? Give the reasons for each attribute selected.
- Write SQL commands to create indexes for each attribute you identified in part a.
Expert Solution & Answer
Learn your wayIncludes step-by-step video
schedule08:38
Students have asked these similar questions
Consider the following relations:
EMP_NO
EMP_NAME
EMP_GRADE
JOB_ID
DEPTNO
START_DATE
EMP
DEPTNO
DEPTNAME
LOC
LOW_SAL
HIGH_SAL
DEPT
Display the name concatenated with the job ID, separated by a comma and space, and name the column Employee and Title.
Display the employee name, employee No and start date of employees hired between February 1998, and May 1998. Order the query in ascending order by start date.
Create a unique listing of all jobs that are in department 80. Include the location of the department in the output.
Write a query to display job ID, total salary and employee name whose job_id contains “REP” and total salary is above 13000. Display the data in ascending order according to total salary.
Create a query that displays the employees’ last names and indicates the amounts of their annual salaries with asterisks. Each asterisk signifies a thousand dollars.
Considering Vaccination Database, write queries to:
Display the Subject CNIC, Name, Contact, Dose1 Center, and Dose2 Center as shown below:
CNIC
Name
Contact
Dose1 Center
Dose2 Center
22401-6645321-1
Nasir
3409991112
Lachi
Kohat 2
14301-6045321-5
Shahab
3409991112
kohat 3
Dara
The subjects who have been vaccinated different types in Dose1 and Dose2. For instance, subjects who were vaccinated ‘Sinovac’ in the first dose, while Pfizer in the second dose.How many Viles of each type have been consumed so far. How many subjects are vaccinated from expired viles. Suggest the name of Incharge for the best performance award based on the highest number of subjects vaccinated on a single day
Consider the following relational schema and answer the following questions.
Employee (Employee_Id, First_Name, Last_Name, Email, Phone_Number, Hire_Date, Job_Id, Salary, Commission_Pct, Manager_Id, Department_Id)
Write a query to get the difference between the highest and lowest salaries.
Write a query to find the manager ID and the salary of the lowest-paid employee for that manager.
Write a query to get the department ID and the total salary payable in each department.
Write a query to get the total salary, maximum, minimum, average salary of employees (job ID wise), for department ID 90 only.
Write a query to get the average salary for all departments employing more than 10 employees.
Chapter 5 Solutions
Modern Database Management (12th Edition)
Ch. 5 - Prob. 5.1RQCh. 5 - Prob. 5.2RQCh. 5 - Prob. 5.3RQCh. 5 - Prob. 5.4RQCh. 5 - Prob. 5.5RQCh. 5 - Prob. 5.6RQCh. 5 - Prob. 5.7RQCh. 5 - Prob. 5.8RQCh. 5 - Explain why you sometimes have to reserve much...Ch. 5 - Why are field values sometimes coded?
Ch. 5 - Prob. 5.11RQCh. 5 - Prob. 5.12RQCh. 5 - Explain why normalized relations may not comprise...Ch. 5 - Prob. 5.14RQCh. 5 - List three common situations that suggest that...Ch. 5 - Explain the reasons why some experts are against...Ch. 5 - Prob. 5.17RQCh. 5 - Prob. 5.18RQCh. 5 - Prob. 5.19RQCh. 5 - Prob. 5.20RQCh. 5 - Prob. 5.21RQCh. 5 - State nine rules of thumb for choosing indexes.Ch. 5 - One of the strongest recommendations regarding...Ch. 5 - Explain why an index is useful only if there is...Ch. 5 - Indexing can clearly be very beneficial. Why...Ch. 5 - Consider the following two relations for...Ch. 5 - Prob. 5.28PAECh. 5 - Prob. 5.29PAECh. 5 - Prob. 5.30PAECh. 5 - Prob. 5.31PAECh. 5 - Suppose you are designing a default value for the...Ch. 5 - When a student has not chosen a major at a...Ch. 5 - Prob. 5.34PAECh. 5 - Prob. 5.35PAECh. 5 - Consider the relations in Problem and Exercise...Ch. 5 - Prob. 5.37PAECh. 5 - Prob. 5.38PAECh. 5 - Prob. 5.39PAECh. 5 - Prob. 5.40PAECh. 5 - Prob. 5.41PAECh. 5 - Consider the relations specified in Problem and...Ch. 5 - Prob. 5.43PAECh. 5 - Prob. 5.44PAECh. 5 - Prob. 5.45PAECh. 5 - Prob. 5.46PAECh. 5 - Prob. 5.47PAECh. 5 - Problems and Exercises 8-65 through 8-68 refer to...Ch. 5 - Refer to the large Pine Valley Furniture Company...Ch. 5 - Problems and Exercises 8-65i5 through 8-68 refer...Ch. 5 - Refer to Figure 4-5 0. For each of the following...
Additional Engineering Textbook Solutions
Find more solutions based on key concepts
Why is the study of database technology important?
Database Concepts (7th Edition)
Each repetition of a loop is known as a(n) _________.
Starting Out with C++: Early Objects (9th Edition)
The programmer intended the following pseudocode to display the numbers 1 through 60, and then display the mess...
Starting Out with Programming Logic and Design (4th Edition)
Word Game Write a program that plays a word game with the user. The program should ask the user to enter the fo...
Starting Out with Java: From Control Structures through Objects (6th Edition)
For each of the following activities, give a PEAS description of the task environment and characterize it in te...
Artificial Intelligence: A Modern Approach
True or False: When a method is declared with the final modifier, it must be overridden in a subclass.
Starting Out with Java: From Control Structures through Data Structures (3rd Edition)
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
- What is the difference between a PRIMARY KEY constraint and a UNIQUE constraint?arrow_forwardConsider the following two relations for Millennium College:STUDENT(StudentID, StudentName, CampusAddress, GPA)REGISTRATION(StudentID, CourseID, Grade) Following is a typical query against these relations:SELECT Student_T.StudentID, StudentName, CourseID, GradeFROM Student_T, Registration_TWHERE Student_T.StudentID = Registration_T.StudentID AND GPA > 3.0ORDER BY StudentName;a. On what attributes should indexes be defined to speed up this query? Give the reasons for each attribute selected.b. Write SQL commands to create indexes for each attribute you identified .arrow_forwardJOIN TABLES One row in the PAT_ENC table represents one patient encounter. One row in the ORDER MED table represents one medication order. One patient encounter can have many medication orders but one medication order can only have one patient encounter. In other words, the cardinality of this PAT_ENC to ORDER_MED relationship is one-to- many. You start a query with PAT_ENC. You then add ORDER_MED using an inner join. What is true about the granularity of the result set before and after adding the ORDER_MED table? SELECT ONE OF THE FOLLOWING A. The granularity stays at one row per patient encounter. B. The granularity stays at one row per medication order. C. The granularity changes from one row per patient encounter to one row per medication order on an encounter. D. The granularity changes from one row per medication order to one row per patient encounter.arrow_forward
- Course: Database Given the following schemas for a student record: STUDENTS (student_id, student_name, region)COURSES (course_id, course_name, subject_type, lecturer_name)RESULTS (student_id, course_id, score, result_status) STUDENTS relation shows the information of the students. The COURSE relation specifies the course details. The RESULTS relation lists the scores that students obtain for each course taken. *Please execute this SQL codes for creating the table schemas mentioned above, Then execute the tasks in the table accordingly using proper SQL queries and Write your answer into the Answer Template CREATE TABLE STUDENTS (student_id VARCHAR(10) PRIMARY KEY, student_name VARCHAR(150), region VARCHAR(50)); CREATE TABLE COURSES (course_id VARCHAR(10) PRIMARY KEY, course_name VARCHAR(150), subject_type VARCHAR(50), lecturer_name VARCHAR(150)); CREATE TABLE RESULTS (student_id VARCHAR(10), course_id VARCHAR(10), score NUMBER, result_status VARCHAR(20), CONSTRAINT pk_results PRIMARY…arrow_forwardWrite a query to find the name of the student(s) who has scored maximum mark in Software Engineering. Sort the result based on name. Hint:Use Correlated Sub query or Inline view. Refer the below schema diagram: College Management System(CMS) Department Student department_id number - PK department_name varchar(30) department_block_number number student_id number --> PK student_name varchar(30) address varchar(40) city varchar(30) department_id number -- FK PK- Primary Key FK- Foreign Key Subject Staff staff_id number -> PK staff_name varchar(30) department_id number-> FK subject_id number ->PK subject_name varchar(30) subject_code varchar(10) staf_id number --> FK Mark value number subject_id number-- PK,FK + student_id number--»PKFKarrow_forwardWrite a query to find the name of the student(s) who has scored maximum mark in Software Engineering. Sort the result based on name. HintUse Correlated Sub query or Inline view. Refer the below schema diagram: College Management System(CMS) Department Student department_id number -- PK department_name varchar(30) department_block_number number student_id number --> PK student_name varchar(30) address varchar(40) city varchar(30) department_id number --> FK PK- Primary Key FK- Foreign Key Subject Staff staff_id number -> PK staff_name varchar(30) department_id number--> FK subject_id number ->PK subject_name varchar(30) subject_code varchar(10) staff_id number -> FK Mark value number subject_id number-- PK,FK + student_id number-->PKFKarrow_forward
- Write a query to display the name of the department that has the maximum staff count order by department name. Refer the below schema diagram: College Management System(CMS) Department Student department id number-- PK department_name varchar(30) department_block_number number O4student_id number --> PK student_name varchar(30) address varchar(40) city varchar(30) department_id number --> FK PK-- Primary Key FK-- Foreign Key Subject Staff subject_id number -PK subject_name varchar(30) subject_code varchar(10) staff_id number -- FK staff_id number --> PK staff_name varchar(30) 王 department_id number--> FK Mark value number subject_id number--> PK,FK + student_id number-->PK,FK 王arrow_forwardGiven the schema: department(department_id, department_name) employees (employee_id, first_name, last_name, salary, department_id) Write a query to get employee first_name along with their department name, only for those who have salary greater than 6000;arrow_forwardClosely examine the following tables and use it to answer the questions that follow: department course student_type student_category Student registration registration_type Suppose that the following are true: - all id column are primary keys in whichever table they are - the dept_id in course table is foreign key that is referencing the id column in the department table - the foreign keys stud_type_id, stud_cat_id in the student table are referencing the id columns in the student_type and student_category tables respectively. - the coursecode and stud_id columns in the registration table are foreign keys in the course and student tables respectively. 1. Design an Entity Relationship Diagram for the system above. 2. Write query that would return the names and contacts of all Local students. 3. Write a query that would return names, contact and locations of all departments that has a location text with Ave 3 as part of it. [hint: use the LIKE clause with “_” or “%”] 4. What would…arrow_forward
- Consider the following two relations for Millennium College:STUDENT(StudentiD StudentName,CampusAddress, GPA)REGISTRATION(Student!D CourseiD Grade)Following is a typical query against these relations:SELECT Student_T.StudentiD, StudentName,CourseiD, GradeFROM Student_T, Registration_TWHERE Student_T.StudentiD =Registration_ T.StudentiDANDGPA>3.0ORDER BY Student Name;a. On what attributes should indexes be defined to speedup this query? Give the reasons for each attributeselected.b. Write SQL commands to create indexes for each attribute you identified in part a.arrow_forwardFor the following Employee database, write a query to find the ID of each employee with no manager. Note that an employee may simply have no manager listed or may have a null manager. Write your query using an outer join and then write it again using no outer join at all. employee (ID, person_name, street, city) works (ID, company_name, salary) соmpany (compaпу лате, сіty) manages (ID, manager_id)arrow_forwardSQL allows a foreign-key dependency to refer to the same relation, as in the following example: create table manager (employee_name char(20), manager_name char(20), primary key employee_name, foreign key (manager name) references manager on delete cascade) Here, employee name is a key to the table manager, meaning that each em- ployee has at most one manager. The foreign-key clause requires that every manager also be an employee. Explain exactly what happens when a tuple in the relation manager is deleted.arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
SQL Basics for Beginners | Learn SQL | SQL Tutorial for Beginners | Edureka; Author: edureka;https://www.youtube.com/watch?v=zbMHLJ0dY4w;License: Standard YouTube License, CC-BY