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...
-
The less time that a manager devotes to staffing a team and preparing its members to complete the project to which they are assigned, the more time gets used up in the forming and storming phases of...
-
How many units must be sold to achieve a target profit of $5,000? Oslo Company prepared the following contribution format income statement based on a sales volume of 1,000 units (the relevant range...
-
M_Limited has an authorised share capital of 1,500,000 divided into 1,500,000 ordinary shares of 1 each. The issued share capital at 31 March 2007 was 500,000 which was fully paid, and had been...
-
Leno Company manufactures toasters. For the first 8 months of 2014, the company reported the following operating results while operating at 75% of plant capacity: Sales (350,000 units) .......
-
15. Please refer to Oscar's financial statements. All of Oscar's costs and net working capital vary directly with sales. Sales are projected to increase by 10 percent. What is the pro forma accounts...
-
The file P02_18.xlsx contains daily values of the Standard & Poors 500 Index from 1970 to early 2012. It also contains percentage changes in the index from each day to the next. a. Create a histogram...
-
A very important note : Please check equal Veriances and normallity of data and show me this It is the most important thing in solving the question Question (3): (8 Points) A student organization...
-
5. (5 marks) An island has two types of inhabitants: Knights who always tell the truth and knaves who always lie. On the island, you encounter A, B, and C who are inhabitants of the island. A says...
-
Suncity Pty Ltd operates a hotel in the New South Wales ski fields, providing accommodation, bar and restaurant facilities for tourists. Casual and part-time wages are a major expense item,...
-
What are the human behaviors economists should observe when creating economic models? Example: people tend to find solutions that are good enough, but not the best solutions. 2) two examples pf how...
-
What is your prescription ( refractive power ) if you measure your lenses to have a focal length of - 1 4 . 2 cm ?
-
Two objects with equal amounts and types of charge are separated by a distance of 34.2 cm. They repel each other with an electric force of 5.36 N. Determine the amount of charge on one of the objects.
-
6. Indicate what the changes listedwould do to the Aggregate Demand Curve byusing an (I) for increase AD (shift) (D) for decrease AD (shift) or N for neither _a. The price level increases b....
-
Explain the Hawthorne effect.
-
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...
-
Why is the accounting different for nonconvertible bonds with detachable stock purchase warrants and nonconvertible bonds with nondetachable stock purchase warrants?
-
Deflation Ltd, which had experienced trading difficulties, decided to reorganise its finances. On 31 December 2005 a final trial balance extracted from the books showed the following position:...
-
On 31 March 2002, the following was the balance sheet of Planting Trees Ltd. The following scheme of capital reduction was sanctioned by the Court and agreed by the shareholders: (a) Preference...
Study smarter with the SolutionInn App