1 Consider the following table and answer the question that

1) Consider the following table and answer the question that follows: (0.5 point) Table Name: PEOPLE ID Firs Age (in years) Tim Anderson Robert Dickson Beth Roberts Nancy Hicken John Smith Mary Show the output of the following SQL query: SELECT First Name, Last Name, CASE WHEN AGE 13 THEN CHILD WHEN AGE 19 THEN TEENAGER WHEN AGE 19 THEN ADULT ELSE \'UNKNOWN\' END AS LIFE STAGE FROM PEOPLE Write your answer in this box.

Solution

Given Query:

___________

SQL> SELECT FirstName,LastName,

2 CASE

3 WHEN AGE < 13 THEN \'CHILD\'

4 WHEN AGE < 19 THEN \'TEENAGER\'

5 WHEN AGE > 19 THEN \'ADULT\'

6 ELSE \'UNKNOWN\'

7 END AS LIFE_STAGE

8 FROM PEOPLE;

Results:

______

FIRSTNAME LASTNAME LIFE_STA

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

Tim Anderson CHILD

Robert Hickson TEENAGER

Beth Roberts CHILD

Nancy Hicken CHILD

John Good ADULT

Mary Smith CHILD

6 rows selected.

Table Creation:

_____________

SQL> create table people(id number(3),FirstName varchar2(15),LastName varchar2(15),Age number(3));

Table created.

SQL> desc people;

Name Null? Type

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

ID NUMBER(3)

FIRSTNAME VARCHAR2(15)

LASTNAME VARCHAR2(15)

AGE NUMBER(3)

Inserting Data:

_____________

SQL> insert into people values(1,\'Tim\',\'Anderson\',3);

1 row created.

SQL> insert into people values(2,\'Robert\',\'Hickson\',13);

1 row created.

SQL> insert into people values(3,\'Beth\',\'Roberts\',7);

1 row created.

SQL> insert into people values(4,\'Nancy\',\'Hicken\',11);

1 row created.

SQL> insert into people values(5,\'John\',\'Good\',22);

1 row created.

SQL> insert into people values(6,\'Mary\',\'Smith\',11);

1 row created.

SQL> select * from people;

ID FIRSTNAME LASTNAME AGE_IN_YEARS

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

1 Tim Anderson 3

2 Robert Hickson 13

3 Beth Roberts 7

4 Nancy Hicken 11

5 John Good 22

6 Mary Smith 11

6 rows selected.