Format date value and column renaming : Columns « Query Select « Oracle PL/SQL Tutorial






SQL> create table gift(
  2           gift_id                integer         primary key
  3          ,emp_id                integer
  4          ,register_date              date not null
  5          ,total_price        number(7,2)
  6          ,deliver_date           date
  7          ,deliver_time           varchar2(7)
  8          ,payment        varchar2(2)
  9          ,emp_no                 number(3,0)
 10          ,deliver_name           varchar2(35)
 11          ,message        varchar2(100)
 12  );

Table created.

SQL>
SQL> insert into gift(gift_id,emp_id,register_date,total_price,deliver_date,deliver_time,payment,emp_no,deliver_name,message)values
  2                 (1,1,'14-Feb-1999', 123.12, '14-Feb-1999', '12 noon', 'CA',1, null, 'Happy Birthday to you');

1 row created.

SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time ,payment ,emp_no,deliver_name ,message)values
  2                 (2,1,'14-Feb-1999', 50.98, '14-feb-1999', '1 pm', 'CA',7, 'name1', 'Happy Birthday');

1 row created.

SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
  2                 (3, 2,'14-Feb-1999', 35.99, '14-feb-1999', '1 pm', 'VS',2, 'Tom', 'Happy Birthday');

1 row created.

SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
  2                 (4, 2,'14-Feb-1999', 19.95, '14-feb-1999', '5 pm', 'CA',2, 'Mary', 'Happy Birthday');

1 row created.

SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values
  2                 (5, 6,'4-mar-1999', 10.95, '5-mar-1999', '4:30 pm', 'VS', 2, 'Jack', 'Happy Birthday');

1 row created.

SQL>
SQL>
SQL> select gift_id, to_char(register_date, 'hh24:mi:ss')
  2         as "Order Time"
  3    from gift;

   GIFT_ID Order Ti
---------- --------
         1 00:00:00
         2 00:00:00
         3 00:00:00
         4 00:00:00
         5 00:00:00

5 rows selected.

SQL>
SQL>
SQL> drop table gift;

Table dropped.








2.8.Columns
2.8.1.Selecting All Columns from a Table with asterisk character (*)
2.8.2.Count(*) and column renaming
2.8.3.Expression and column renaming
2.8.4.DISTINCT tableName.*
2.8.5.Combined columns and renaming
2.8.6.Format date value and column renaming
2.8.7.Reference one column more than once
2.8.8.Reference renamed column in order by
2.8.9.Rename case when column
2.8.10.Returning Multiple Columns
2.8.11.Table renaming in from clause
2.8.12.Use table name to reference column name