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.

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.

Oracle 12c: SQL

3rd Edition

ISBN:9781305251038

Author:Joan Casteel

Publisher:Joan Casteel

Chapter9: Joining Data From Multiple Tables

Section: Chapter Questions

Problem 9RQ

See similar textbooks

Similar questions

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
Given 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;
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.
Consider 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 .
Computer Science At UFL, each class is a specific offering of a course (subject). Each row in the Courses table contains information on a single course or subject taught a UFL and each row in the Classes table contains information on a single offering of a course during a specific term. Investigate the two tables, and then write a query that returns the CourseTitle, ClassID, Term, SectionID and FacultyID for classes taught by FacultyID 5000009. SOLUTION:
Consider the following schema STUDENTS(student_code, first_name, last_name, email, phone_no, date_of_birth, MS_subject, percentage_of_marks); Which of the following query would display names of all the students whose MS_subject is English and percentage_of_marks more than 70, or MS_subject is French and percentage_of_marks more than 70? select first_name, last name from students where MS_subject = “English” or MS_subject = “French” and percentage_of_marks > 70; select first_name, last name from students where (MS_subject = “English” or MS_subject = “French” ) and percentage_of_marks > 70; select first_name, last name from students where (MS_subject = “English” or MS_subject = “French” and percentage_of_marks > 70); select first_name, last name from students where (MS_subject = “English”) or MS_subject = “French” and percentage_of_marks > 70;
Consider the query:with dept_total (dept_name, value) as(select dept_name, sum(salary)from instructorgroup by dept_name),dept_total_avg(value) as(select avg(value)from dept_total)select dept_namefrom dept_total, dept_total_avgwhere dept_total.value >= dept_total_avg.value;Rewrite this query without using the with construct.
JOIN 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.
create a query for cursor.. cursor cur1 asSELECT e.first_name, e.last_name, e.salary, e.job_id, j.min_salary, j.max_salaryFrom employees e JOIN jobs jto get every employee information and their job_id and for each job_id’s min and max_salary range
FROM customer WHERE city IN (SELECT city FROM employee WHERE reports_to= 2); 2. SELECT customer.* FROM customer INNER JOIN employee ON customer.city = employee.city WHERE reports_to = 2; Query #1 would be more efficient as it is based on primary and foreign keys. O Query #1 would be more efficient as it is not using indexed columns. Both would be the same as both use the same indices for the join and filter. Query #2 would be more efficient as it is based on primary and foreign keys. 70°F Cloudy QL
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.
Using the Henry Books database,update the paperback field for the book titled Black Houseto Y. You will insert 2 snips for this question. Insert a snip of the construct used to update the table: Insert your screen shot of the Books table here (be certain Black Housedisplays): Using the Henry Books database,add a new row to the AUTHORS table. The new author’s name is John Grisham. You will insert 2 snip s for this question. Insert a snip of the construct used to update the table: Insert your snip of the Authors table here showing the update: Using the Henry Books database,create a view named Paperback. It consists of the book code, book title, publisher name, and book price for every book that is available in paperback. You will insert 2 snips for this question. Insert a snip of the construct used to create this view: Run a query using this view and insert a snip of the resultset: Using the Henry Books database,write a stored procedure that…