Answer to Question #250465 in Databases | SQL | Oracle | MS Access for BossyGurl

Question

PRACTICAL: Question 6 (Marks: 10)

Create a PL/SQL query to display the customer name and total amount spent by each customer. In your solution determine the customer rating. If the total amount spent is greater than or equal to R 1000, the customer receives a star rating, otherwise no star rating applies.

Sample Output

FIRST NAME: Patrick

SURNAME: Smith

AMOUNT: R 700

----------------------------------------------------

FIRST NAME: Steven

SURNAME: Hewson

AMOUNT: R 570

----------------------------------------------------

FIRST NAME: Barry

SURNAME: Goodwin

AMOUNT: R 2500 (***)

----------------------------------------------------

Answer 1

create table customer(firstName varchar(40) not NULL, SIRNAME varchar(50) not null,AMOUNT int not null );
insert into customer values ('Patrick','Smith',700);
insert into customer values ('Steven','Hewson',570);
insert into customer values ('Barry','Goodwin',2500);


select firstname,amount from customer where amount>1000;

Output: