Question

1. How do I write a query that displays the name (concatenate the first name, middle initial, and last name), date of birth, and age for all students? Show the age with no decimal places, and only include those students who are 21 or older. Order by age, as shown below: (Hint: Use the TRUNC function. The ages may be different, depending on the date that the query is run.)

SELECT S_FIRST || ' ' || S_MI || ' ' || S_LAST AS "student name"
from STUDENT;

WHERE .....

Here is the query 1 result bellow

Full Name Date of birth Age

1. Brian D. Umato 1 9-AUG-98 21

2. Amanda J. Mobley 24-SEP- 96 22

3. Daniel . Black 10-OCT-94 24

Query 2. Employees table

Using the EMPLOYEES table, display the email, phone number, and then one other column (the name of the column is entered by the user). This same column is then also used in the ORDER BY clause to order the resulting rows. Restrict the rows to those that are equal to a certain department id (this number is also entered in by the user.) Use the double ampersand for the column substitution variable to insure that the user is prompted to enter the column name ONLY ONCE, and that it is “cleared out” after the run (UNDEFINE).

I just need a little solution thanks.

Answer 1

Question 1

Table Definition (DDL): create table STUDENT ( FIRST_NAME VARCHAR2(50), MIDDLE_INITIAL VARCHAR2(1), LAST_NAME VARCHAR2(50), DATE_OF_BIRTH DATE )

Data: Tom   K   Tommy   2015-09-18 00:00:00 Jerry   M   Jarr   1986-02-23 00:00:00 Chip   A   Che   2001-03-02 00:00:00 Donald   H   Duck   2002-11-11 00:00:00 Zorba   D   Greek   2000-10-01 00:00:00 Po   D   Panda   1995-08-30 00:00:00 Mulan   Z   Chan   1992-12-10 00:00:00

Query: select first_name || ' ' || middle_initial || ' ' || last_name as full_name, date_of_birth, floor(months_between(trunc(sysdate), date_of_birth) / 12) as age from student order by age;

Result: Tom K Tommy   2015-09-18 00:00:00   3 Donald H Duck   2002-11-11 00:00:00   16 Chip A Che   2001-03-02 00:00:00   18 Zorba D Greek   2000-10-01 00:00:00   18 Po D Panda   1995-08-30 00:00:00   24 Mulan Z Chan   1992-12-10 00:00:00   26 Jerry M Jarr   1986-02-23 00:00:00   33

Question 2

Table definition (DDL): create table EMPLOYEES ( DEPT_ID NUMBER, NAME VARCHAR2(50), EMAIL VARCHAR2(50), PHONE VARCHAR2(20) )

Data: 12   Jerome K Jerome   [email protected]   11235813 13   James Bond   [email protected]   12345678 9   Peter Pan   [email protected]   13579111 5   Harry Potter   [email protected]   24681012 11   Simba   [email protected]   12357111 15   Timon   [email protected]   24689101 8   Goofy   [email protected]   98765432 3   Aldous Huxley   [email protected]   87654321

Query: select email, phone, &&dyna_col from employees order by &&dyna_col; undefine dynacol; undefine deptid;

Output: SQL> select email, phone, &&dynacol from employees where dept_id = &&deptid order by &&dynacol; Enter value for dynacol: name Enter value for deptid: 15 old 1: select email, phone, &&dynacol from employees where dept_id = &&deptid order by &&dynacol new 1: select email, phone, name from employees where dept_id = 15 order by name EMAIL PHONE Name ------------------------------------------------------------------------------ [email protected]               24689101 Timon

Please provide feedback for the answer;