How to manipulate dates in SQL (demo)

This is a reminder on how to manipulate dates in SQL (Oracle).

Setup:

I'll create a simple table a populate it with a few data:
create table date_test(id number, date1 date, ts1 timestamp, date2 date, ts2 timestamp);

insert into date_test values (1, 
to_date('2014-06-27', 'RRRR-MM-DD'), 
to_date('2014-06-27', 'RRRR-MM-DD'), 
to_date('2014-06-27', 'RRRR-MM-DD'), 
to_date('2014-06-27', 'RRRR-MM-DD'));

Data selection examples:

SELECT CURRENT_TIMESTAMP FROM DUAL;

SELECT CURRENT_TIMESTAMP, CURRENT_TIMESTAMP + INTERVAL '1' SECOND(1) 
FROM DUAL;

SELECT  TO_CHAR (TS1, 'RRRR-MM-DD, HH24:MI:SS') AS ORI, 
TO_CHAR (TS1-INTERVAL '1' SECOND(1),'RRRR-MM-DD, HH24:MI:SS') AS FORMATTED 
FROM DATE_TEST;

See also this page.

How to use the CASE clause in a SQL statement (a demo)

This is a little demo to show how to use the CASE clause in a SQL statement:

drop table two_fields;

drop table two_fields2;

create table two_fields (id number, text varchar2(200), field1 varchar2(10), field2 varchar2(10));

create table two_fields2 (id number, text varchar2(200), field12 varchar2(10));

insert into two_fields values (1, 'text1', 'f1-1', null);
insert into two_fields values (2, 'text2', 'f1-2', null);
insert into two_fields values (3, 'text3', 'f1-3', null);
insert into two_fields values (4, 'text4', null, 'f2-1');
insert into two_fields values (5, 'text5', null, 'f2-2');
insert into two_fields values (6, 'text6', null, 'f2-3');

commit;

select * from two_fields;

select * from two_fields2;

select id, text, field1, field2, case field1 when null then 'field2' else 'field1' end as f1f2 from two_fields;

select id, text, field1, field2, decode (field1 , null, field2, field1) f1t from two_fields;

INSERT INTO TWO_FIELDS2 (SELECT ID, TEXT,  decode (field1 , null, field2, field1) from two_fields);


ALTER TABLE two_fields ADD CONSTRAINT c1 check (field1 is not null or field2 is not null);

ALTER TABLE two_fields ADD CONSTRAINT c2 check ((field1 is not null and field2 is null) OR (field1 is null and field2 is not null))

Find out the columns used in a referencial integrity constraints

Using the data dictionary views USER_CONSTRAINTS and USER_CONS_COLUMNS, it is possible to find out what columns are used in a referencial integrity constraint:


SELECT 
  UCC1.TABLE_NAME, 
  UCC1.COLUMN_NAME, 
  UCC1.CONSTRAINT_NAME, 
  UC.R_CONSTRAINT_NAME, 
  UCC2.TABLE_NAME
FROM USER_CONSTRAINTS UC , 
  USER_CONS_COLUMNS UCC1, 
  USER_CONS_COLUMNS UCC2
WHERE UC.CONSTRAINT_TYPE = 'R'
  AND UCC1.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
  AND UCC2.CONSTRAINT_NAME = UC.R_CONSTRAINT_NAME

How to find out which roles does a user have

I often have to find out whether a particular user has a certain role.

This is the command to read the AD - Roles of a user:

U:\>net user z100079 /domain
The request will be processed at a domain controller for domain mobi.mobicorp.ch.

User name                    Z100079
Full Name                    Z100079
Comment                      Service Account für "Vertragsverlauf", Gysling Eva
User's comment
Country/region code          000 (System Default)
Account active               Yes
Account expires              30.12.2099 23:00:00

Password last set            27.01.2011 13:30:38
Password expires             Never
Password changeable          27.01.2011 13:30:38
Password required            Yes
User may change password     Yes

Workstations allowed         All
Logon script
User profile
Home directory
Last logon                   Never

Logon hours allowed          All

Local Group Memberships
Global Group memberships     *AMPS_WS              *Domain Users
                             *ASES-INTERN          *AISDV_CONSUMER
                             *ACONTRACTREADCLIENT  *ASES-EXTERN
                             *ALOSSRATIOREADCLIENT *ACLAIMREADCLIENT