Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Find the name of instructor who works in the physics department and his salary is between 100000 and 90000 (1 Mark) 2. Find the name

Find the name of instructor who works in the physics department and his salary is between 100000 and 90000 (1 Mark)

2. Find the name of students who study in the electrical engineering department and takes more than the average total credits (1 Mark)

3. find the prerequisite course for Robotics (1 Mark)

4.Create triggers for credits change, the trigger should be activated before the database is updated and should prevent any change of course credits. The trigger should display an appropriate massage. (1 Mark)

5. Write a Procedure-SQL that given the name of course, it will give you the room number at the times for the course. Please give your Procedure an appropriate name. (hint: look up MySQL Stored Procedures That Return Multiple Values) (3 Marks)

6. the university has decided to increase the salary of instructors based on the following criteria.

If the instructor is in the computer since department and his/her salary is less than 70000 then his/her salary will be increased by 15%

If the instructor is in the electrical engineering department and his/her salary is less than 90000 then his/her salary will be increased by 8%

Instructors in other departments and have a salary of less than 70000 then his/her salary will be increased by 3.5%

Please write a query that will show the instructor name, department name, salary, and salary after increase (2 Mark)

7. Define an SQL-procedure that will give name of courses that are taught in Watson. Please give your Procedure an appropriate name . (2 Mark)

8. fix the errors in the following query to find the names of students who are being taught by 'Srinivasan' (2 Mark)

select ID,name,ID,name

from instructor , teaches, takes. student

when ID = ID

and ID > name

and corse_id in a.course_id

or aID = u.ID

and i.name = Srinivasan

9. CREATE FUNCTION that given the instructor name will calculate the number of students who are being taught by the instructor. Please give your function an appropriate name (2 Mark)

(hint: use the following query before building the function:

SET GLOBAL log_bin_trust_function_creators = 1

)

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Joe Celkos Data And Databases Concepts In Practice

Authors: Joe Celko

1st Edition

1558604324, 978-1558604322

More Books

Students also viewed these Databases questions

Question

What are financial assets and financial liabilities?

Answered: 1 week ago

Question

Describe the factors influencing of performance appraisal.

Answered: 1 week ago

Question

1. What are your creative strengths?

Answered: 1 week ago

Question

What metaphors might describe how we work together?

Answered: 1 week ago