Basic queriesObjectivesCreating Search Conditions in SQL QueriesComparison OperatorsDefining Search ExpressionsSlide 6Select-From-Where StatementsMeaning of Single-Relation QueryOperational SemanticsSlide 10Slide 11Dual tableShowing only unique valuesOrder bySlide 15* In SELECT clausesChanging column ordersSlide 18Renaming AttributesconcatenateExpressions in SELECT ClausesAnother Example: Constant ExpressionsComplex Conditions in WHERE ClauseSlide 24Important PointsUse in operator to find whether a tuple is inside a list of tuplesPatternsExampleSlide 29Use functionsCreating Complex Search ConditionsRetrieving Data from a Single Database TableSorting Query OutputUsing Calculations in SQL QueriesAggregation functions group by havingSlide 36Oracle8i SQL Group FunctionsGroup ByHavingGROUP BY...Group byExample using the SUM functionAggregate functionsFive aggregate functionsSlide 45The syntax for the GROUP BY functionSlide 47HAVING... ExampleThe syntax for the HAVING function is:Slide 50Slide 51Slide 52Slide 53Formatting Numbers and DatesFormat ModelsNumerical Format ModelsDate Format ModelsSlide 58JoinWhy we need to select information from multiple tablesSlide 61Slide 62You need to retrieveSlide 64Slide 65Slide 66Slide 67Slide 68What is an outer joinOuter join in Oracle 8iSlide 71Slide 72Joining Multiple TablesInner JoinJoining Via Linking TableUsing a Query Design DiagramOuter JoinSelf-JoinSelf-Join ExampleUsing Set Operators To Combine Query ResultsSet OperatorsSQL NULL (unknown) and three-valued logicNULL BasicsNull valuesSQL built-in functionsSlide 86User defined function!Slide 88Types of built-in FunctionsSingle row (Scalar) functionsSingle row functions categoriesSystem variablesSystem variables Advanced Functions:Number functionsMore mathemetical functionsText functionsCharacter / String FunctionsData Type Conversion FunctionsDate functionsMore Date functionOther functionsNVLBasic queriesSen Zhang2Objectives•Create search conditions in SQL queries•Learn how to write SQL queries to retrieve data from a single database table•Create SQL queries that perform calculations on retrieved data•Use SQL group functions to summarize retrieved data•Learn how to create SQL queries that join multiple tables•Understand how to combine query results using set operators3Creating Search Conditions in SQL Queries•An expression that seeks to match specific table records•Used in SELECT, UPDATE and DELETE statements•WHERE fieldname comparison_operator search_expression•WHERE S_ID = 14Comparison Operators5Defining Search Expressions•Character strings –Must be enclosed in single quotes–Case sensitive•Dates–Use to_date function with date string and format model•Intervals–Use to_yminterval and to_dsinterval with interval string format model6DML - Queries (the Select statement)select attribute listfrom table listwhere conditiongroup by expressionhaving expressionorder by expression ;Select fname, salary from employee where salary > 30000;  fname, salary(salary>30000( Employee))7Select-From-Where Statements•The principal form of a query is:SELECT desired attributesFROM one or more tablesWHERE condition about tuples of the tables8Meaning of Single-Relation Query•Begin with the relation in the FROM clause.•Apply the selection indicated by the WHERE clause.•Apply the extended projection indicated by the SELECT clause.9Operational Semantics•To implement this algorithm, think of a tuple variable ranging over each tuple of the relation mentioned in FROM.•Check if the “current” tuple satisfies the WHERE clause. It means whether the “current tuple” makes the where clause as a whole true or not.•If true, compute the attributes or expressions of the SELECT clause using the components of this tuple.10SQL> select fname, salary from employee where salary>30000;FNAME SALARY------------ ---------Ramirez 43000Reale 38000Wolons 55000SQL> select fname, salary from employee;FNAME SALARY------------ ---------Ramirez 43000Reale 38000Smith 25000Warren 25000Wolons 5500011Duplicates are possible!Assuming that there are two people sharing the same first name!SQL> select fname, salary from employee;FNAME SALARY------------ ---------Ramirez 43000Reale 38000Smith 25000Warren 25000Wolons 55000Wolons 4353412Dual table•Dual table is a special table –that contains one column (named dummy) and one row (whose value is simply X). •Dual table’s data is never meant to be used directly. Instead, the DUAL table is provided to support on-the-fly calculations independent from any tables.•Example–Select 18*20 from dual;13Showing only unique valuesSelect distinct salary From employeeOrder by salary ;Select distinct salary From employeeOrder by salary desc;14Order by •Which columns?•What sorting direction?15Order by•Order by clause can be used together with select command to achieve the purpose.•Sort the return data on attributes of select statements•Sorting direction could be either descending or Ascending •Select * from employee order by salary desc;16* In SELECT clauses•When there is one relation in the FROM clause, * in the SELECT clause stands for “all attributes of this relation.”SELECT *FROM employeesWHERE salary > 30000;17Changing column ordersSQL> select fname, lname from employee;FNAME LNAME------------ ------------Ramirez DamianReale MichaelSmith JasonWarren SamanthaWolons Aimee18SQL> select lname, fname from employee;LNAME FNAME------------ ------------Damian RamirezMichael RealeJason SmithSamantha WarrenAimee Wolons19Renaming Attributes•If you want the result to have different attribute names, use “AS <new name>” to rename an attribute.SELECT fname AS firstname, salary as monthlyincomeFROM employeeWHERE salary>3000;“As” is optional in Oracle SQL20concatenate•select fname || ‘ ‘ || lname as "some body" from employee;21Expressions in SELECT Clauses•Any expression that makes sense can appear as an element of a SELECT clause.SELECT fname, lname,salary * 12 AS YearincomeFROM employee;22Another Example: Constant ExpressionsSELECT fname,‘Low income’ AS whoneedhelpFROM employeeWHERE salary < 3000;23Complex Conditions in WHERE Clause•find the salary SELECT salaryFROM employeeWHERE