MIS 385 / MBA 664 Fall 2022 Assignment 3
1
Write SQL queries to answer the following questions. Failing to follow the instructions will result in a significant
deduction of credit for this assignment.
Submit a single .SQL file and identify each query using comments.
Write your name as a comment at the top of the file.
DO NOT repeat the questions in the SQL file, only specify the question number.
You will need to do some research over the internet to answer some of the questions.
This assignment will be closely monitored for any similarities in submissions. Do not share your solutions
with others.
Each question is worth 0.5 point. Expected output for some of the questions is given at the end of the document.
Dream Home
1. Order staff members by the descending number of properties managed by each of them.
2. Write a query that shows all information on branches, but in addition to the branch number, it spells out the number of the branch as well. For instance, for branch B005, the query must have an output column with the value ‘Five’. The query must work not only for the current branches but also for potential branches to be opened in future.
3. Write a query that shows tel No in the Private Owner as follows:
Add ‘+44’ to the beginning of tel No, followed by a ‘-‘ . The leading 0 must be dropped. For example, ‘0141- 943-1728’ should be changed to ‘+44-141-943-1728′.
Hospital Database
4. Write a query that shows the patient ID and age of each patient. Note that age must be a whole number. For each patient, this information must be shown only once.
5. Write a query that shows the Encounter ID, Patient ID, DOB, admission and discharge date of the patients together with two numbers that rank the patients by the ascending order of their ages at the time of admission and descending order of length of stay in the hospital. Order the output by the ascending order of Encounter ID.
North wind Database
6. Which employees’ job title ends with an ‘r’ or includes three words?
7. Which employees have a postal code that is longer than 5 characters?
MIS 385 / MBA 664 Fall 2022 Assignment 3
2
8. Print a list of managers in the North wind database together with the number of employees that report to
them.
9. The ‘notes’ column in the EMPLOYEE table of the North wind database points out to foreign languages
that employees have some familiarity with. Write a series of SQL statements to do the following:
Create new attributes in the employee table to store a 1 (with the bit data type) if an employee knows, in any capacity, French, German, Italian, Spanish, Portuguese, or Japanese. If an employee is not familiar with any of these languages, s/he will have a 0 for that attribute.
10. Write a query that shows the employee ID, first name, and last name, and the total number of languages that each employee has some familiarity with. Sort the output such that those who know more languages are at the top .
PVFC Database
11. Create a list of customers based on the amount they have purchased from pvfc.
12. What is the third most expensive product?
13. Which product line has the most number of orders?
World Database
14. Write a query to show the country code, name, and number of languages spoken in all countries in which English is spoken.
15. Create a report that ranks the cities in each country by their population. That is, the most populous city in each country gets a rank of 1 and so on. The rankings must reset for each country. Sort the results
by country Code and then by the ranking you created.
MIS 385 / MBA 664 Fall 2022 Assignment 3
3
Sample output (top rows)
1.
2.
3.
4.
MIS 385 / MBA 664 Fall 2022 Assignment 3
4
5.
6.
7.
8.
9. Sample output not given
10. Abridged result:
MIS 385 / MBA 664 Fall 2022 Assignment 3
5
11. abridged results
12. Sample output not given
13. Sample output not given
14. abridged results
15. abridged results
