SQL LABS - Functions

FUNCTIONS

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.



1. Which course has maximum fees.


2. List out the courses that is taken in Chennai.


3. List out the courses that is taken online training from Bangalore.


4. What is the fees for online oracle dba training in chennai.


5. List out the course name from c001 to c006


6. Print the total fees that which is taken for all ONLINE training course.

7. Print the Characters starting with 2 , 5 from training type.\
Eg. for ONLINE -> NLIN, for CLASSROOOM -> LASS


8. Print the fees total with respect to location
eg .
CHENNAI 135000
MUMBAI 55000
BANGALORE 84000


9. Print out the Location that has 2 'A's.
eg. BANGALORE


10. Print out the courses that starts with 'O'


11. Print out the courses that has "_".

12. Replace 'ONLINE' in TRAINING_TYPE as 'ONLION'


13. Which course has second highest duration.


14. What are the course names that has location ends with 'I'
 

15. Print the courses for which fees is between 10000 and 20000


16. Which course has maximum character and its count.
eg. ORACLE_DEVELOPER 15


17. Remove 'C' from COURSE_ID and find the highest number from it.


18. Print the record that has C012 two times


19. Find the Highest average of Classroom training Fees respective to location.


20. Find the sum of salaray with respective to training_type and location.

21.print the sysdate in '24/07/2010' format;

22.print the sysdate in '24-07-2010:23:00:00' format;

23.Print sysdate twice
ex.
04-MAY-2021
04-MAY-2021