Oracle SQL Functions - Lab Exercise
Oracle SQL Functions - Lab Exercise
Practice questions |
1.print the sysdate in '24/07/2007' format;
|
2.print the sysdate in '24-07-2010:23:00:00' format. Also make sure which ever date you access should show the time.
eg. 'select sysdate from dual' should print with time.
|
3.create table TESDB_TAB (tdate date);
insert into TESDB_TAB values(sysdate);
insert into TESDB_TAB values(sysdate);
insert into TESDB_TAB values(sysdate);
insert into TESDB_TAB values(sysdate);
commit;
Explain why we get 0 records for "select * from TESDB_TAB where tdate=sysdate;"
|
4. Print sysdate twice using one sql statment.
ex.
04-MAY-2021
04-MAY-2021
|
5. SQL> create table students(COURSE_ID varchar2(20), COURSE_NAME varchar2(20),
TRAINING_TYPE varchar2(20), LOCATION varchar2(20), FEES number(10), DURATION int);
select * from students;
COURSE_ID COURSE_NAME TRAINING_TYPE LOCATION FEES DURATION
-------------------- -------------------- -------------------- -------------------- ---------- ----------
c001 sql_plsql online chennai 10000 20
c002 oracle_dba online chennai 30000 180
c003 linux online chennai 5000 10
c004 rac online mumbai 15000 20
c005 dataguard online banglore 7000 20
c006 golden_gate online bangalore 15000 90
c007 aws online chennai 15000 30
c008 postgresql_dba online chennai 25000 30
c009 oracle_developer online bangalore 15000 20
c010 sql classroom chennai 5000 20
c011 plsql classroom chennai 10000 30
c012 oracle_dba classroom bangalore 30000 200
c013 linux classroom mumbai 5000 10
c014 rac classroom mumbai 15000 20
c015 dataguard classroom bangalore 7000 20
c016 goldengate classroom bangalore 15000 20
c017 aws classroom chennai 15000 30
c018 postgresql_dba classroom chennai 25000 30
c019 oracle_developer classroom mumbai 15000 20
19 rows selected.
|
6.1. Which course has maximum fees.
|
6.2. List out the courses that is taken in Chennai.
|
6.3. List out the courses that is taken online training from Bangalore.
|
6.4. What is the fees for online oracle dba training in chennai.
|
6.5. List out the course name from c001 to c006
|
6.6. Print the total fees that which is taken for all ONLINE training course.
|
6.7. Print the Characters starting with 2 , 5 from training type.
Eg. for ONLINE -> NLIN, for CLASSROOOM -> LASS
|
6.8. Print the fees total with respect to location
eg .
CHENNAI 135000
MUMBAI 55000
BANGALORE 84000
|
6.9. Print out the Location that has 2 'A's.
eg. BANGALORE
|
6.10. Print out the courses that starts with 'O'
|
6.11. Print out the courses that has "_".
|
6.12. Replace 'ONLINE' in TRAINING_TYPE as 'ONLION'
|
6.13. Which course has second highest duration.
|
6.14. What are the course names that has location ends with 'I'
|
6.15. Print the courses for which fees is between 10000 and 20000
|
6.16. Which course has maximum character and its count.
eg. ORACLE_DEVELOPER 15
|
6.17. Remove 'C' from COURSE_ID and find the highest number from it.
|
6.18. Print the record that has C012 two times
|
6.19. Find the Highest average of Classroom training Fees respective to location.
|
6.20. Find the sum of salaray with respective to training_type and location.
|