100%(1)1 out of 1 people found this document helpful
This preview shows page 3 - 5 out of 5 pages.
Example Query 0 List the name and phone number of supplier number 1. Part A: Relational Algebra [25 marks] Write Queries one through four listed below in relational algebra. Mark division is as follows: 1, 2, 3 [5 Marks] and 4 [10 Marks]. Part B: SQL [75 marks] Write ALL queries listed below in SQL. Mark Division: 1, 2, 3, 4, 5 [5 Marks] 6, 7, 8, 9, 10 [10 Marks] For SQL part, sort the query results using the “ORDER BY” clause based on the attribute names specified (refer to the above example). You are allowed rename the column names in the data to names you are asked to generate. List of Queries Query 1 A customer wants to know what parts are available between the sizes of 43 through 47 (including 43 and 47) of brand 'Brand#42'. Write a query to retrieve all such parts. The Query 2 The collection team in our Canadian office wants to know the custkeys, names and phone numbers of all local customers that owe us money so that they can harass these customers over the phone. Write a query that will retrieve all custkeys, names, and phone numbers of customers in the nation of ‘CANADA’that have an account balance strictly less than zero. The output should have the column names: c_custkey, c_name, c_phone and is Query 3 Management wants to improve client relations, with both customers and suppliers, who trust us the most. They want a list of all names and phone numbers belonging to either customers that have an account balance strictly greater than 9975 or suppliers that have an account balance strictly greater than 9500. Write a single statement to retrieve these required names and phone numbers. The output should have the column names: name, phone and and is sorted in this order.
Query 4 Management wants to know which countries are well represented by our network of suppliers. Write a statement to list the name of each nation that contains at least two different suppliers. Now let us suppose that instead of at least two different suppliers, management wants to know the names of each nation that has at least seven different suppliers. In your hard copy solutions explain (describe in less than a paragraph of text, no need to give actual queries) what would be the best way change your original answer, in both RA and SQL, to accommodate for this change in specifications. The output should consist of the