Enterprise Data Management
Enterprise Data Management Assignment #2 Enterprise Data Management Name: University ID: Section ID : : : Submitted to: Dr. Yonis Gulzar 1 Enterprise Data Management Branch Registration BRANCHNO STREET CITY POSTCODE B005 22 Deer Rd London SW1 4EH B007 16 Argyll St Aberdeen B003 163 Main St B004 B002 CLIENTNO* BRANCHNO* STAFFNO* DATEJOINED AB2 3SU CR76 B005 SL41 02-JAN-01 Glasgow G11 9QX CR56 B003 SG37 11-APR-00 32 Manse Rd Bristol BS99 1NZ CR74 B003 SG37 16-NOV-99 56 Clover Dr London NW10 6EU CR62 B007 SA9 07-MAR-00 Staff STAFFNO FNAME LNAME POSITION SEX DOB SALARY BRANCHNO* SL21 John White Manager M 01-OCT-45 30000 B005 SG37 Ann Beech Assistant F 10-NOV-60 12000 B003 SG14 David Ford Supervisor M 24-MAR-58 18000 B003 SA9 Mary Howe Assistant F 19-FEB-70 9000 B007 SG5 Susan Brand Manager F 03-JUN-40 24000 B003 SL41 Julie Lee Assistant F 13-JUN-65 9000 B005 CLIENTNO FNAME LNAME TELNO CR76 John Kay CR56 Aline CR74 CR62 Client PREFTYPE MAXRENT 0171-774-5632 Flat 425 Steward 0141-848-1825 Flat 350 Mike Ritchie 01475-392178 House 750 Mary Tregear 01224-196720 Flat 600 PropertyForRent ROOMS RENT OWNERNO STAFFNO* BRANCHNO* House 6 650 CO46 SA9 B007 NW2 Flat 4 400 CO87 SL41 B005 Glasgow G11 9QX Flat 3 350 CO40 SG14 B003 2 Manor Rd Glasgow G32 4QX Flat 3 375 CO93 SG37 B003 PG21 18 Dale Rd Glasgow G12 House 5 600 CO87 SG37 B003 PG16 5 Novar Dr Glasgow G12 9AX Flat 4 450 CO93 SG14 B003 PROPERTYNO STREET CITY POSTCODE PA14 16 Holhead Aberdeen AB7 5SU PL94 6 Argyll St London PG4 6 Lawrence St PG36 TYPE Figure 1: list of tables Note: __________: Primary Key * : Foreign Key 2 Enterprise Data Management i. Write an SQL query to create a table “REGISTRATION’ as shown in Fig. 1, make sure it satisfies all the referential integrity constraints. ii. Write an SQL query to insert a new tuple into a branch table where branch is located in Kuala Lumpur with B017 and in street ‘08 Jalan Gombak’ with postcode JG53100 iii. Write an SQL query to delete all those tuples from BRANCH where city name contains ‘ris’ iv. Write an SQL query to retrieve the Branchno, total number of staff working in each branch from staff table. v. Write an SQL query to update (increase) the salary of those staff by 500 whose position is manger in STAFF table. 3 Enterprise Data Management vi. Write an SQL query to retrieve the details of all the staff from STAFF table who are not ‘male’ and are not ‘managers’ vii. Write an SQL query: from each branch with more than 1 member of staff, find the number of staff in each branch and their sum of their salaries and sort by branch no. Use nested queries/subqueries viii. Write an SQL query to delete tuple in the PROPERTYFORRENT table where property is in city ‘Aberdeen’ staff salary is equal to 9000. ix. Write an SQL to retrieve the fname, lname, position, salary of those staff who are working in city London or Glasgow. x. Write an SQL query to update the DATEJOINED to ‘31=DEC-99’ in Registration Table where registration is handled by the staff name as ‘Ann Beech’ and client PREFTYPE is ‘House’. 4
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.
