Data Base Design Question and Answer 2022

Running head: DATABASE DESIGN
DATABASE DESIGN
Name of the Student
Name of the University
Author Note

DATABASE DESIGN
1
Table of Contents
Section B –Queries..........................................................................................................................2
Section C – Data Modelling............................................................................................................4
Question 1....................................................................................................................................4
Question 2....................................................................................................................................4
Question 3....................................................................................................................................5
Question 4....................................................................................................................................6
Bibliography....................................................................................................................................7

DATABASE DESIGN
2
Section B –Queries
1. Write an SQL query that displays total number of customers (rename the field ‘Total
Customers’), number of customers without referrals (rename the field ‘Direct Customers’), and
number of referred customers (rename the field ‘Referred Customers’).
SELECT COUNT(customer#) AS "Total Customers" , SUM(CASE
WHEN referred IS NULL THEN 1 ELSE 0 END) AS "Direct
Customers", SUM(CASE WHEN referred IS NOT NULL THEN 1 ELSE
0 END) AS "Referred Customers" FROM customers;
2. Write a SQL query that displays customer number, last name, first name, and city for all
customers who reside in Florida and who have not been referred by another customer.
SELECT Customer#, Lastname, Firstname, City FROM customers
WHERE city='FLORIDA' AND referred IS NULL;
3. Display the full name of all the customers in a single column (by concatenating first name
followed by a comma then a space then last name), rename the filed ‘Customers’, region and
referred for all customers who live in any western region (that is in any region in the western
sector ‘W’, ‘SW’, ‘NW’) and who have been referred by another customer.
SELECT lastname||', '||firstname AS CUSTOMERS, Region,
referred FROM customers
WHERE region LIKE '%W%' AND referred IS NOT NULL;