Consider the following schema and answer the following questions: Project(projld: integer, proj_name: varchar) Employee(empld: integer, jobTitle: varchar, salary: number, projld: integer, deptld: integer) Department(deptid: integer, dept_name: varchar) a. Write the following queries in relational algebra. i. Find the jobTitle of employees who worked in project with id= "2654". ii. Find the IDs of employees who are earn salaries more than 1000. i. Find IDs of projects who accomplished by employee with id = "7452". b. Write SQL queries for the following statements. i. Display all the employee's details in the decreasing order of their salary. ii. Count and display the number of employees worked in each project. i. Add the given new department's data into the Department table. deptld Dept_name Human Resources 123

Consider the following schema and answer the following questions: Project(projld: integer, proj_name: varchar) Employee(empld: integer, jobTitle: varchar, salary: number, projld: integer, deptld: integer) Department(deptid: integer, dept_name: varchar) a. Write the following queries in relational algebra. i. Find the jobTitle of employees who worked in project with id= "2654". ii. Find the IDs of employees who are earn salaries more than 1000. i. Find IDs of projects who accomplished by employee with id = "7452". b. Write SQL queries for the following statements. i. Display all the employee's details in the decreasing order of their salary. ii. Count and display the number of employees worked in each project. i. Add the given new department's data into the Department table. deptld Dept_name Human Resources 123

Database Systems: Design, Implementation, & Management

12th Edition

ISBN:9781305627482

Author:Carlos Coronel, Steven Morris

Publisher:Carlos Coronel, Steven Morris

Chapter11: Database Performance Tuning And Query Optimization

Section: Chapter Questions

Problem 21P: Problems 1721 are based on the following query: SELECT V_CODE, V_NAME, V_CONTACT, V_STATE FROM...

See similar textbooks

Similar questions

1) Find names of those departments, each of which has at least two same-aged employees. 2)List names of those departments each of which has an employee named ‘Joe’. 3)List the names of employees, each of whom has salary more than the budget of the department he/she works in. Answer the above questions in relational algebra with reference to the following relational schema. Key fields are bold. Emp (eid:integer, ename:string, age:integer, salary:real)Works (eid:integer, did:integer, pct_time:integer)Dept (did:integer, dname:string, budget:real)
You are working with a database that stores information about suppliers, parts and projects. The Supply relation records instances of a Supplier supplying a Part for a Project.The schema for the database used in this question is as follows: (note that primary keys are shown underlined, foreign keys in bold).SUPPLIER (SNo, SupplierName, City)PART (PNo, PartName, Weight)PROJECT (JobNo, JobName, StartYear, Country)SUPPLY (SNo, PNo, JobNo, Quantity)Provide relational algebra (NOT SQL) queries to find the following information. Each question is worth 2 marks.NOTE:• You can use the symbols etc or the words ‘PROJECT’, ‘RESTRICT’ etc as you prefer. • You do not need to try to make efficient queries – just correct ones. • Where you use a join, always show the join condition.a. List the quantity of parts supplied on JobNo 4745.b. List the weight of the Part named “Left-handed screwdriver”.c. List the Project name and Part name of any Parts where fewer than 500 of the part has been supplied to a…
You are working with a database that stores information about suppliers, parts and projects. The Supply relation records instances of a Supplier supplying a Part for a Project.The schema for the database used in this question is as follows: (note that primary keys are shown underlined, foreign keys in bold). SUPPLIER (SNo, SupplierName, City)PART (PNo, PartName, Weight)PROJECT (JobNo, JobName, StartYear, Country)SUPPLY (SNo, PNo, JobNo, Quantity) Provide relational algebra (NOT SQL) queries to find the following information. NOTE:• You can use the symbols σ, π, etc or the words ‘PROJECT’, ‘RESTRICT’ etc as you prefer. • You do not need to try to make efficient queries – just correct ones. • Where you use a join, always show the join condition.a. List the quantity of parts supplied on JobNo 4745.b. List the weight of the Part named “Left-handed screwdriver”.c. List the Project name and Part name of any Parts where fewer than 500 of the part has been supplied to a particular project.d.…
You are working with a database that stores information about suppliers, parts and projects. The Supply relation records instances of a Supplier supplying a Part for a Project.The schema for the database used in this question is as follows: (note that primary keys are shown underlined, foreign keys in bold). SUPPLIER (SNo, SupplierName, City)PART (PNo, PartName, Weight)PROJECT (JobNo, JobName, StartYear, Country)SUPPLY (SNo, PNo, JobNo, Quantity) Provide relational algebra (NOT SQL) queries to find the following information.NOTE:• You can use the symbols σ, π, etc or the words ‘PROJECT’, ‘RESTRICT’ etc as you prefer.• You do not need to try to make efficient queries – just correct ones.• Where you use a join, always show the join condition.a. List the Project name and Part name of any Parts where fewer than 500 of the part has been supplied to a particular project.b. List the name of any Suppliers who have supplied Parts to the Project called “New Academic Building” or to the Project…
You are working with a database that stores information about suppliers, parts and projects. The Supply relation records instances of a Supplier supplying a Part for a Project. The schema for the database used in this question is as follows: ( primary keys are shown underlined, foreign keys in bold). SUPPLIER (SNo, SupplierName, City) PART (PNo, PartName, Weight) PROJECT (JobNo, JobName, StartYear, Country) SUPPLY (SNo, PNo, JobNo, Quantity) Provide relational algebra (NOT SQL) queries to find the following information. NOTE: You can use the symbols s, P, etc or the words ‘PROJECT’, ‘RESTRICT’ etc . do not need to try to make efficient queries – just correct ones. Where you use a join, always show the join condition. List the quantity of parts supplied on JobNo 4745. List the weight of the Part named “Left-handed screwdriver”. List the Project name and Part name of any Parts where fewer than 500 of the part has been supplied to a particular project. List the name of any Suppliers…
Kindly use the hospital relational schema provided below to solve these questions. 1. List EID, Dnumber and Lname of each doctor attending a patient in hospital H02 at the ward W18.a) We want RESULT(DoctorEID, Dnumber, DoctorLname)although column headers can be different.b) Need to join DOCTOR, EMPLOYEE to get all the required attributes.c) Need a selection for "H02" and "W18" 2. List Registration numbers of all patients in H02, ward W18 who have been diagnosed with "Flu" but for whom "Chest X-ray" test has not been ordered.a) First get a table with only one column RegistrationNumber, which lists all patients from W18 of H02 who have been diagnosed with flu.b) Second, get a table with only one column RegistrationNumber, which lists all patients from W18 of H02 who have had a test with Tname="Chest X-ray".c) Finally, take the set difference.
Consider the following relational schema:Student (sid, sname, dept)Enroll (cid, sid, grade, semester)Course (cid, cname, dept, credit)Prereq (cid, pid) Write the following queries in Relational Algebra expression: a) For students who have received an A in a course, return name of students and titleof the course. b) Find name and id of students taken at least one of the prerequisite of CS595course and got an A grade in this prerequisite course.
Consider the following schema for STUDENT- Student (RollNo, Name, City, Father-Name, Branch, Sem, Per) Write the following queries in Relational Algebra: (i) List the records of students. (ii) List the records such students whose city is Mbd. (iii) List the records of those students whose Percentage marks is more than 75%. (iv) List the records of those students whose Percentage marks is less than 40%. (v) List the records of those students whose Percentage marks between 80 to 90%. (vi) Show student’s name and City and branch who live in Delhi. (vii) Show student’s RollNo, Name and Sem whose Percentage Mark is more than 80%. Plz try to give solution of this question within 1hrs as it is very urgent plzz....
SQL: Consider the following relational schema: Staff (staffNo, name, dept, skillCode) Skill (skillCode, description, chargeOutRate) Project (projectNo, startDate, endDate, budget, projectManagerStaffNo) Booking (staffNo, projectNo, dateWorkedOn, timeWorkedOn) where: Staff contains staff details and staffNo is the key. Skill contains descriptions of skill codes (e.g. Programmer, Analyst, Manager, etc.) and the charge out rate per hour for that skill; the key is skillCode. Project contains project details and projectNo is the key. Booking contains details of the date and the number of hours that a member of…
Consider the following relational schema, and answer the following questions by writing relational algebra queries: Employee (EmpID, City, Zipcode) Works (EmpID, CorpID, Salary) Corporation (CorpID, City) Manages (EmpID, MngrID) a. Find the ID of all employees who work for Amawig Corporation. b. Find the ID and City of all employees who work for Amawig. c. Find the ID and City of all employees who work for Amawig corporation and earn more than $100,000. d. Find the IDs of all employees who live in the same city as the corporation for which they work. e. Find the ID of all employees who live in the same area (zipcode) as do their managers. f. Find the IDs of all employees who earn more than every employee in the Amawig Corporation. g. Suppose that the corporations may have more than a branch in different cities. Find all corporations located in every city in which Amawig Corporation is located.
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) a. Write a query to get the average salary and number of employees working the department 90. b. Write a query to get the highest, lowest, sum, and average salary of all employees. c. Write a query to get the number of employees with the same job. d. Write a query to get the difference between the highest and lowest salaries.
Consider the following relational schema, and answer the following questions by writing relational algebra queries: Employee (EmpID, City, Zipcode) Works (EmpID, CorpID, Salary) Corporation (CorpID, City) Manages (EmpID, MngrID) a. Find the ID and City of all employees who work for Amawig. b. Find the ID and City of all employees who work for Amawig corporation and earn more than $100,000. c. Find the IDs of all employees who live in the same city as the corporation for which they work. d. Find the ID of all employees who live in the same area (zipcode) as do their managers. e. Find the IDs of all employees who earn more than every employee in the Amawig Corporation. f. Suppose that the corporations may have more than a branch in different cities. Find all corporations located in every city in which Amawig Corporation is located.

SEE MORE QUESTIONS

Recommended textbooks for you

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

A Guide to SQL

Computer Science

ISBN:

9781111527273

Author:

Philip J. Pratt

Publisher:

Course Technology Ptr

Oracle 12c: SQL

Computer Science

ISBN:

9781305251038

Author:

Joan Casteel

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

A Guide to SQL

Computer Science

ISBN:

9781111527273

Author:

Philip J. Pratt

Publisher:

Course Technology Ptr

Oracle 12c: SQL

Computer Science