Oracle SQL Functions - Lab Exercise

Practice questions
1. Write an SQL query to display the current system date (SYSDATE) in the format DD/MM/YYYY (e.g., 24/07/2007).
2. Write an SQL query to display the current system date (SYSDATE) in the format DD-MM-YYYY:HH24:MI:SS (e.g., 24-07-2010:23:00:00), ensuring that the time is included when accessing the date.
3.Create a table TESDB_TAB with a tdate column of type DATE. Insert four rows into the table with the current system date (SYSDATE). After committing the changes, explain why the query SELECT * FROM TESDB_TAB WHERE tdate = SYSDATE; returns 0 records.
4. Write an SQL query that prints the SYSDATE twice in one statement, formatted as DD-MON-YYYY (e.g., 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.
5.1) Which course in the students table has the highest fees? Write a query to retrieve the course details with the maximum fee.
5.2) Retrieve a list of all courses offered in Chennai.
5.3) Fetch all courses that are offered as online training in Bangalore.
5.4) Fetch the fees for the Oracle DBA online training course in Chennai.
5.5) List the courses with IDs starting from c001 to c006.
5.6) Display the sum of fees for all online training courses.
5.7) Write a query to extract the characters starting from the 2nd and 5th positions in the TRAINING_TYPE.
5.8) Retrieve the total fees grouped by location
5.9) Display locations where the letter 'A' appears twice.
5.10) Write a query to display courses that have names starting with '0'.
5.11) Find courses with underscores in their names.
5.12) Replace the term 'ONLINE' with 'ONLION' in the TRAINING_TYPE field.
5.13) Write a query to find the course with the second highest duration.
5.14) List the course names where the location ends with the letter 'I'.
5.15) Write a query to find courses where the fee falls between 10,000 and 20,000.
5.16) Find the course with the highest character count and show the count.
5.17) Find the highest number from COURSE_ID after removing the character 'C'.
5.18) Find and display the record where C012 appears twice.
5.19) Retrieve the location with the highest average fees for classroom training.
5.20) Calculate the total salary based on TRAINING_TYPE and LOCATION