Concept explainers
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.
Learn your wayIncludes step-by-step video
Chapter 5 Solutions
Modern Database Management (12th Edition)
Additional Engineering Textbook Solutions
Database Concepts (7th Edition)
Starting Out with C++: Early Objects (9th Edition)
Starting Out with Programming Logic and Design (4th Edition)
Starting Out with Java: From Control Structures through Objects (6th Edition)
Artificial Intelligence: A Modern Approach
Starting Out with Java: From Control Structures through Data Structures (3rd Edition)
- What is the difference between a PRIMARY KEY constraint and a UNIQUE constraint?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_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_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_forwardConsider 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.arrow_forwardCourse: 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_forward
- Write 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_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_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_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_forward