SQL Readable Outputs - Oracle SQL Fundamentals
2 likes266 views
A practical approach towards learning SQL in Oracle 11g. Video lectures are prepared as well and can be found at given link https://www.youtube.com/channel/UCMv6HrS_4_GFWmLLFzL_U_A?view_as=subscriber
SQL Readable Outputs - Oracle SQL Fundamentals
- 1. SQL Fundamentals Oracle 11g M U H A M M A D WA H E E D O R AC L E DATA BA S E D E V E LO P E R E M A I L : m .wa h e e d 3 6 6 8 @ g m a i l . co m Lecture#9 Readable Outputs with iSQL*Plus
- 3. Substitution Variables(contd) Use iSQL*Plus substitution variables to: Store values temporarily - Single ampersand( & ) - Double ampersand( && ) - DEFINE command Pass variable values between SQL statements 3
- 4. Substitution Variables(contd) Use of & substitution variable: Use a variable with pre-fixed ampersand to prompt the user for a value 4
- 5. Substitution Variables(contd) Use of character and date substitution variables: Use single quotes with character and date values 5
- 6. Substitution Variables(contd) Use substitution variables to supplement the following : Entire SELECT statements Table names Column expressions WHERE conditions ORDER BY clauses 6
- 7. Substitution Variables(contd) Use substitution variables to supplement the following : Example: 7
- 8. Substitution Variables(contd) Use of DEFINE substitution variable: Creates a user variable with the CHAR data type DEFINE variable = value (if value includes spaces than use single quotes to enclose the value) A defined variable is available for the session 8
- 9. Substitution Variables(contd) Use of DEFINE and UNDEFINE commands: A variable remains defined until you either - Use the UNDEFINE command to clear it - Exit iSQL*Plus You can verify changes with DEFINE command 9
- 10. Substitution Variables(contd) Use of && with substitution variables: If you want to reuse a variable value without prompting the user each time 10
- 11. MERGE Statement It performs UPDATE if record exists and INSERT if record not found. Mostly used in data warehousing applications. Syntax: MERGE INTO <receiver_table_name> <receiver_table_alias> USING <sender_table_name> <table_alias> ON (<receiver_table_alias>.<column_name>= <sender_table_alias>.<column_name>) WHEN MATCHED THEN UPDATE SET <receiver_table_alias>.<column_name> = <sender_table_name>.<column_name> WHEN NOT MATCHED THEN INSERT VALUES(<sender_table_name>.<column_name> ,..); 11
- 12. MERGE Statement Syntax can be simply described as: 12
- 14. Feedback/Suggestions? Give your feedback at: m.waheed3668@gmail.com The feedback I get is that my books are honest. - Laurie Halse Anderson