select * from uscensus where id=9000;

/* Exercise 2.1: Some examples of queries that can be made */

SELECT * FROM Uscensus WHERE id <= 500;
SELECT * FROM Uscensus WHERE id >= 500;
SELECT * FROM Uscensus WHERE id between 1000 and 2000;
SELECT * FROM Uscensus WHERE id <= 500 OR id >= 30000;

SELECT * FROM uscensus WHERE id=9000;
SELECT COUNT(*) FROM uscensus;
SELECT COUNT(age) FROM uscensus;

select count(*) FROM uscensus WHERE age between 86 and 88;
select age, count(*) from uscensus group by age order by age desc

SELECT * FROM Uscensus WHERE id <= 500
UNION
SELECT * FROM Uscensus WHERE id >= 30000;

SELECT * FROM Uscensus WHERE id <= 500
UNION ALL
SELECT * FROM Uscensus WHERE id >= 30000;

/* Exercise 2.2 */

alter table uscensus add constraint pkuscensus primary key(id);

/* Now, you need to check the execution plan provided by Oracle. You can do this through the interface provided by 
the sql developer or through the explain plan command */


/* Exercise 3.1 */

ALTER TABLE uscensus DROP CONSTRAINT PKUSCENSUS;
ALTER TABLE USCENSUS ADD CONSTRAINT pk_uscensus PRIMARY KEY (id);


CREATE INDEX age_idx ON uscensus(age);  
ANALYZE INDEX age_idx COMPUTE STATISTICS;

SELECT avg(age) FROM uscensus WHERE age >= 20;

SELECT avg(age) FROM uscensus WHERE age >= 20;
SELECT avg(age) FROM uscensus WHERE age >= 80;

CREATE  INDEX id_age_idx ON uscensus(age,id);
ANALYZE INDEX id_age_idx COMPUTE STATISTICS;

select id from uscensus where age = 20

/* Exercise 3.2 */

select avg(age) from uscensus where education='Preschool';

create index btree_educ on uscensus(education);
ANALYZE INDEX btree_educ COMPUTE STATISTICS;
ANALYZE table uscensus COMPUTE STATISTICS;

select avg(age) from uscensus where education='Preschool';
select /*+index(uscensus,btree_educ)*/ avg(age) from uscensus where education='Preschool';

 /* Evaluate the clustering factor of indices (Note that, this statistics is not reliable regarding bitmaps indexes*/
select clustering_factor from user_indexes where index_name='BTREE_EDUC'
drop index btree_educ;

drop cluster uscensus_cluster;
CREATE CLUSTER uscensus_cluster (education VARCHAR2(15) ) SINGLE TABLE HASHKEYS 20;

CREATE TABLE uscensus_hash (
                id NUMBER(7),
                age NUMBER(3),
                workclass VARCHAR2(20),
                fnlwgt NUMBER(10),
                education VARCHAR2(15),
                educationnum    NUMBER(3),
                maritalstatus VARCHAR2(25),
                occupation VARCHAR2(20),
                relationship VARCHAR2(20),
                race VARCHAR2(20),
                sex VARCHAR2(10),
                capitalgain NUMBER(8),
                capitalloss NUMBER(8),
                workhours NUMBER(2),
                country VARCHAR2(30),
                income VARCHAR2(10))
    CLUSTER uscensus_cluster(education);


drop cluster uscensus_cluster;
INSERT INTO uscensus_hash SELECT * FROM Uscensus;

select avg(age) from uscensus_cluster where education='Preschool';
select avg(age) from uscensus where education='Preschool';

/* Exercise 4.1 */

select avg(age) from uscensus where education = 'Preschool'

CREATE BITMAP INDEX BM_ea ON uscensus(education, age);
ANALYZE INDEX BM_ea COMPUTE STATISTICS;
ANALYZE table uscensus COMPUTE STATISTICS;

/* Exercise 4.2 */

select count(*) from uscensus where education = 'Preschool'

/* Exercise 4.3 */

select * from uscensus where country='Scotland' and sex='Female' and income='>50K';

create index arv_csi on uscensus(sex, country, income);
drop index arv_csi;
select * from uscensus where country='Scotland' and sex='Female' and income='>50K';

CREATE BITMAP INDEX BM_csi ON uscensus(sex,country, income);
select * from uscensus where country='Scotland' and sex='Female' and income='>50K';

/* Exercice 4.4 */

select count(*) 
from uscensus A, uscensus B
where abs(A.workhours - B.workhours) <= 1 and
      A.educationnum = B.educationnum and
           (A.workhours+B.workhours)/2 >
                ( select avg(C.Workhours) from uscensus C
                   where C.educationnum <>A.educationnum and A.occupation = C.occupation);
				   
				   
create index i_weo on uscensus(workhours, educationnum, occupation);
drop index i_weo;

create bitmap index i_bm_weo on uscensus(workhours, educationnum, occupation);
drop index i_bm_weo ;


/* Other exercises */
	
/* Clustered index */	
	  
  CREATE TABLE uscensus_age_clustered (
			id NUMBER(7),
			age NUMBER(3),
			workclass VARCHAR2(20),
			fnlwgt NUMBER(10),
			education VARCHAR2(15),
			educationnum    NUMBER(3),
			maritalstatus VARCHAR2(25),
			occupation VARCHAR2(20),
			relationship VARCHAR2(20),
			race VARCHAR2(20),
			sex VARCHAR2(10),
			capitalgain NUMBER(8),
			capitalloss NUMBER(8),
			workhours NUMBER(2),
			country VARCHAR2(30),
			income VARCHAR2(10),
			CONSTRAINT Pk_docindex2 PRIMARY KEY (age, id))
	ORGANIZATION INDEX;

analyze table uscensus_age_clustered compute statistics;
INSERT INTO uscensus_age_clustered SELECT * FROM Uscensus;

select * from uscensus where age between 20 and 60
select * from uscensus_age_clustered where age between 20 and 60;

/* Bitmap for querying for tuples with NULL values */

CREATE BITMAP INDEX education_bitmap ON uscensus(education);

UPDATE uscensus SET education = NULL WHERE id = 12345;
SELECT * FROM uscensus WHERE education IS NULL;


/* Index skip scan */

drop index sexage_idx;
CREATE INDEX sexfnlwgt_idx ON uscensus(sex, fnlwgt);

analyze index sexfnlwgt_idx compute statistics;
analyze table uscensus compute statistics;

select * from uscensus where fnlwgt fnlwgt;