a. Write a SQL query that would enable the user 'ww10_StaffMember' to view all of the...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
a. Write a SQL query that would enable the user 'ww10_StaffMember' to view all of the data in the ww10_student table. 1b. (1 query) Assuming the SQL query from #5 has executed, write a SQL query that would remove the granted permission for 'ww10 StaffMember' to view all of the data in the ww10_student table. 1c. (1 trigger) Using the database tables provided on the next page, create a trigger that will fire when an update to the ww10_tutor table takes place and the status of a tutor is changed to 'Dropped' or 'Temp Stop'. The trigger will update the ww10_match_history table by setting the end date of all records for the associated tutor to be the current date. a. Hint: To set the end date in a query, use the GETDATE() function, such as: SET EndDate = GETDATE() ww10 tutor (tutor id, tutor_cert_date, tutor_status) Data Type Column Name Allow Nulls 8 tutor_id int tutor_cert_date date tutor_status varchar(50) tutor_id tutor_cert_date tutor_status 1 100 2020-01-05 Active 2 101 2020-01-05 Temp Stop 3 102 2020-01-05 Dropped 103 2020-05-22 Active 104 2020-05-22 Active 6 105 2020-05-22 Temp Stop 7 106 2020-05-22 Active ww8_student (student_id, student_group, student_read_score) Column Name Data Type Allow Nulls 8 student_id int student_group int student_read_score numeric(2, 1) student_id student_group student_read_score 1 3000 3 2.3 2 3001 2 5.6 3002 3 1.3 3003 1 3.3 3004 2.7 6 3005 4 4.8 7 3006 3 7.8 8 3007 4 1.5 a. Write a SQL query that would enable the user 'ww10_StaffMember' to view all of the data in the ww10_student table. 1b. (1 query) Assuming the SQL query from #5 has executed, write a SQL query that would remove the granted permission for 'ww10 StaffMember' to view all of the data in the ww10_student table. 1c. (1 trigger) Using the database tables provided on the next page, create a trigger that will fire when an update to the ww10_tutor table takes place and the status of a tutor is changed to 'Dropped' or 'Temp Stop'. The trigger will update the ww10_match_history table by setting the end date of all records for the associated tutor to be the current date. a. Hint: To set the end date in a query, use the GETDATE() function, such as: SET EndDate = GETDATE() ww10 tutor (tutor id, tutor_cert_date, tutor_status) Data Type Column Name Allow Nulls 8 tutor_id int tutor_cert_date date tutor_status varchar(50) tutor_id tutor_cert_date tutor_status 1 100 2020-01-05 Active 2 101 2020-01-05 Temp Stop 3 102 2020-01-05 Dropped 103 2020-05-22 Active 104 2020-05-22 Active 6 105 2020-05-22 Temp Stop 7 106 2020-05-22 Active ww8_student (student_id, student_group, student_read_score) Column Name Data Type Allow Nulls 8 student_id int student_group int student_read_score numeric(2, 1) student_id student_group student_read_score 1 3000 3 2.3 2 3001 2 5.6 3002 3 1.3 3003 1 3.3 3004 2.7 6 3005 4 4.8 7 3006 3 7.8 8 3007 4 1.5
Expert Answer:
Answer rating: 100% (QA)
1 You cant provide schema for WW10StaffMember table so you can ... View the full answer
Related Book For
Database Systems A Practical Approach to Design Implementation and Management
ISBN: 978-0132943260
6th Edition Global
Authors: Thomas Connolly, Carolyn Begg
Posted Date:
Students also viewed these finance questions
-
Complete the audit log provided on the next page for each of the seven remaining confirmations. Consider whether each confirmation provides sufficient, appropriate audit evidence, whether sufficient...
-
Create a trigger named trg_char_hours that will automatically update the AIRCRAFT table when a new CHARTER row is added. Use the CHARTER tables CHAR_HOURS_FLOWN to update the AIRCRAFT tables AC_TTAF,...
-
Create a trigger named trg_pic_hours that will automatically update the PILOT table when a new CREW row is added and the CREW table uses a pilot CREW_JOB entry. Use the CHARTER tables...
-
Two billiard balls of equal mass undergo a perfectly elastic head-on collision. If one balls initial speed was 2.00m/s, and the others was 3.00m/s in the opposite direction, what will be their speeds...
-
Use a graph to estimate the x-coordinates of the points of intersection of the given curves. Then use this information and your calculator to estimate the volume of the solid obtained by rotating...
-
Male birds of many species have brighter feathers than females. Bright colors on males are often adaptations for winning mates, as in the case of birds of paradise and peacocks, discussed in this...
-
A \(45^{\circ}\) off-axis rail shear test specimen of an orthotropic lamina is shown in Figure 10.38. Attached to the specimen are three strain gages that measure the normal strains...
-
For fiscal year 2012, Mango Computer, Inc., reported net sales of $19,323 million, net income of $1,997 million, and no significant discontinued operations, extraordinary items, or accounting...
-
A basketball player balances a ball on his finger. The ball is rotating with an initial angular speed of 4 rad/s. He then uses his hand to push along the edge of the ball to make it rotate faster....
-
bring this packet with you to every class as this is an ongoing problem. Chart of Accounts 11. Cash 31. Jolene Upton, Capital 12. Accounts receivable 14. Supplies 15. Prepaid rent 16 Prepaid...
-
Provide practical tips and strategies to improve communication with individuals with hearing disabilities. This could include using visual aids, gestures, and facial expressions, using assistive...
-
Olive ceases trading on 31 May 2021. Her recent adjusted trading profits/(losses) are: Overlap relief of 7,140 is available. Calculate the terminal loss and show how this would be relieved, assuming...
-
What are the requirements for entering a valid premarital agreement in your state?
-
Closely examine each of the following situations. Determine which restraints on marriage, if any, are enforceable. Give specific reasons you think the restraint is or is not enforceable. a. John is...
-
Jim is the managing director of a company and earns a basic salary in 2021-22 of 225,000. He receives benefits from the company during the year as follows: (a) He is provided with the use of a...
-
In 2021-22, Ahmed has capital gains of 130,000 and allowable losses of 24,000. He also has capital losses brought forward of 3,700. Ahmed's taxable income for 2021-22 (after deduction of the personal...
-
What should be the price of a bond which has a par value of Rs . 1 0 0 0 carrying a coupon rate of 8 % and having a maturity period of 9 years? The required rate of return of the investor is 1 2 % .
-
Assume you are the accountant for Catalina Industries. John Catalina, the owner of the company, is in a hurry to receive the financial statements for the year ended December 31, 20X1, and asks you...
-
Analyze the RDBMSs that you are currently using. What types of relational languages does the system provide? For each of the languages provided, what are the equivalent operations for the eight...
-
Discuss the general characteristics of advanced database applications.
-
Using the Hotel schema given at the start of the Exercises at the end of Chapter 4, determine whether the following queries are semantically correct: (a) SELECT r.type, r.price FROM Room r, Hotel h...
-
Explain the building blocks of accounting: ethics, principles, and assumptions.
-
On May 1, 2025, Park Flying School Ltd., a company that provides flying lessons, was started with an investment of 45,000 cash in the business. Following are the assets and liabilities of the company...
-
The historical cost basis results in: a. initially recording assets at cost and adjusting when the current value changes. b. keeping activities of an entity separate and distinct from its owner. c....
Study smarter with the SolutionInn App