Download as PDF, PPTX
![View Table Structure 16 •Syntax: DESC[CRIBE] <table_name>; bracket’s enclosed part is optional.](https://image.slidesharecdn.com/lecture5-180408101216/75/Basics-of-SELECT-Statement-Oracle-SQL-16-2048.jpg)
![View Table SQL Structure 17 •Syntax: SELECT DBMS_METADATA.GET_DDL(‘TABLE’,‘<table_name>'[,‘<user _name/schema>']) from DUAL; bracket’s enclosed part is optional.](https://image.slidesharecdn.com/lecture5-180408101216/75/Basics-of-SELECT-Statement-Oracle-SQL-17-2048.jpg)
Uploaded byMuhammadWaheed44
Basics of SELECT Statement - Oracle SQL
This document provides an overview of SQL fundamentals and the SELECT statement in Oracle 11g. It discusses the properties of database transactions including atomicity, consistency, isolation, and durability. It then covers the basics of the SELECT statement and how to identify columns and tables. The document proceeds to explain arithmetic expressions, column and table aliases, concatenation operators, and how to eliminate duplicate records using DISTINCT. It also discusses viewing table structures, the WHERE clause and various comparison/logical operators that can be used. The document concludes with an overview of the ORDER BY clause and rules of precedence.
Basics of SELECT Statement - Oracle SQL
- 1. SQL Fundamentals Oracle11g 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#5 Basics of SELECT Statement
- 2. Transaction Properties •All databasetransactions are ACID. Atomicity Consistency Isolation Durability 2
- 3. Transaction Properties(cont’d) •All databasetransactions are ACID. Atomicity: The entire sequence of actions must be either completed or aborted. The transaction cannot be partially successful. Consistency: The transaction takes the resources from one consistent state to another. Isolation:A transaction's effect is not visible to other transactions until the transaction is committed. Durability:Changes made by the committed transaction are permanent and must survive system failure. 3
- 4. SELECT Statement •SELECT identifieswhat columns. •FROM identifies which table. 4
- 5. Arithmetic Expressions •Create expressionswith number and date by using following arithmetic operators: Divide ( / ) Multiply ( * ) Add ( + ) Subtract ( - ) •You can use arithmetic operators in any SQL clause except FROM clause. •Operator precedence is applied by default. We need to use parenthesis for custom precedence. 5
- 6. Arithmetic Expressions(cont’d) •Example: SELECT std_name,std_age+ 30 FROM student; •Example: SELECT std_name,std_marks + 10/100 FROM student; *enforcement of custom operator precedence is (std_marks + 10)/100. 6
- 7. Aliases •Oracle ALIASES canbe used to create a temporary name for columns or tables. •It has two types: column alias, table alias •COLUMN ALIASES are used to make column headings in your result set easier to read. •TABLE ALIASES are used to shorten your SQL to make it easier to read or when you are listing the same table more than once in the FROM clause. 7
- 8. Aliases(cont’d) •Syntax: Column Alias :<column_name> AS <alias_name> Table Alias : <table_name> <alias_name> •Alias name can not contain special characters but if it is desired then use double-quotes i.e. “<alias_name>”. •*Remember: ‘AS’ is only used with column not table. 8
- 9. Column Aliases(cont’d) •Example: SELECT std_idAS ID , std_name AS NAME FROM student; •To have special character in alias name follow following example: SELECT std_id AS “Student ID” , std_name AS “Student Name” FROM student; or SELECT std_id AS id, std_name AS "Student Name" FROM student; 9
- 10. Table Aliases(cont’d) •Example: SELECT s.std_id, s.std_name FROM student s; •To have special character in alias name follow following example: SELECT s.std_id AS “Student ID” , s.std_name AS “Student Name” FROM student s; 10
- 11.
- 12. Concatenation Operator 12 •Concatenates columnsor character strings to other column. •It is represented by vertical bars ( || ).
- 13. Concatenation(cont’d) 13 •Example: SELECT std_id||std_name FROMstudent; •Example: SELECT std_id||std_name AS “name”FROM student; •Example: SELECT std_id||std_name||std_age FROM student;
- 14. Concatenation(cont’d) 14 •Using Literal characterstring. •It is neither column name nor a column alias rather it is a string enclosed in single quotes. •Example: SELECT std_name|| ‘is’ || age || ‘years old’ AS “Student Age” FROM student;
- 15. Duplicate Records 15 •By defaultSELECT selects all rows from a table including duplicate records. •Duplicate records can be eliminated by using keyword “DISTINCT”. •Example: SELECT DISTINCT std_name FROM student;
- 16. View Table Structure 16 •Syntax: DESC[CRIBE]<table_name>; bracket’s enclosed part is optional.
- 17. View Table SQLStructure 17 •Syntax: SELECT DBMS_METADATA.GET_DDL(‘TABLE’,‘<table_name>'[,‘<user _name/schema>']) from DUAL; bracket’s enclosed part is optional.
- 18. WHERE Clause 18 •Restricts therecords to be displayed. •Character string and date are enclosed in single quotes. •Character values are case sensitive while date values are format sensitive. •Default date format is ‘DD-MON-YY’.
- 19. Comparison Conditions/Operators 19 •There arefollowing conditions: = >= < <= <> (not equal to) •Example: SELECT * FROM student WHERE std_id<>1;
- 20. Comparison Conditions/Operators(cont’d) 20 •Few other operators/conditionsare: > BETWEEN <lower_limit> AND <upper_limit> > IN/MEMBERSHIP (set/list of values) > LIKE > IS NULL
- 21. BETWEEN … AND… Condition 21 •Use the BETWEEN condition to display rows based on a range of values. •Example: SELECT * FROM student WHERE std_age BETWEEN 17 AND 21;
- 22. IN/ MEMBERSHIP Condition 22 •Useto test/compare values from list of available values. •Example: let we have library defaulters with IDs 102,140,450 etc. SELECT * FROM student WHERE std_id IN ( 102,140,450);
- 23. Wildcard 23 •Databases often treat% and _ as wildcard characters. •Pattern-match queries in the SQL repository (such as CONTAINS, STARTS WITH, or ENDS WITH), assume that a query that includes % or _ is intended as a literal search including those characters and is not intended to include wildcard characters.
- 24. LIKE Condition/Operator 24 •The OracleLIKE condition allows wildcards to be used in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement. This allows you to perform pattern matching.
- 25. LIKE Condition/Operator(cont’d) 25 •The SQLLIKE clause is used to compare a value to the existing records in the database records partially/fully. •There are two wildcards used in conjunction with the LIKE operator: percent sign (%) underscore (_)
- 26.
- 27. NULL Condition 27 •Compare recordsto find NULL values by using ‘IS NULL’. •Example: all students having no contact info SELECT * FROM student WHERE std_contact IS NULL;
- 28. LOGICAL Conditions •A logicalcondition combines the result of two columns or inverts the value of a single column. •There are following logical operators: AND OR NOT 28
- 29. AND - LogicalConditions(cont’d) •AND requires both conditions to be true. •Example: SELECT * FROM student WHERE std_id=1 AND age=21; •Example: SELECT * FROM student WHERE std_id <=5 AND std_name LIKE ‘%a%’; 29
- 30. OR - LogicalConditions(cont’d) •OR requires both conditions to be true. •Example: SELECT * FROM student WHERE std_id=1 OR age=21; •Example: SELECT * FROM student WHERE std_id <=5 OR std_name LIKE ‘%a%’; 30
- 31. NOT - LogicalConditions(cont’d) •NOT inverses the resulting value. •NOT is used with other SQL operators e.g BETWEEN,IN,LIKE. •Examples: SELECT * FROM student WHERE std_id NOT BETWEEN 1 AND 5; WHERE std_id NOT IN (101,140,450); WHERE std_name NOT LIKE ‘%A%’; WHERE std_contact IS NOT NULL; •Example: SELECT * FROM student WHERE std_id <=5 OR std_name LIKE ‘%a%’; 31
- 32.
- 33. Rules of Precedence(cont’d) •Example: SELECT* FROM student WHERE tch_name LIKE ‘%a%’ OR tch_id >5 AND tch_salary>=20000; *it evaluates as “select all columns if tch_id is greater than 5 and tch_salary is greater or equal to 20000, or if the tch_name containing ‘a’. ** to perform the OR operation first apply parenthesis. 33
- 34. ORDER BY Clause •Sortthe resulting rows in following orders: ASC: ascending order(by default) DESC: descending order •It is used with select statement. 34
- 35. ORDER BY Clause(cont’d) •Example: SELECT* FROM student ORDER BY dob; or SELECT * FROM student ORDER BY dob DESC; •ORDER BY on multiple columns: SELECT * FROM student ORDER BY dob,std_name DESC; 35
- 36.
- 37. Feedback/Suggestions? Give your feedbackat: m.waheed3668@gmail.com