Transcription
Oracle Database 11gSQL Fundamentals – Lab 2
SQL Functions
Single-Row Functions
Character FunctionsThese functions convert case for character strings.FunctionResultLOWER('SQL Course')sql courseUPPER('SQL Course')SQL COURSEINITCAP('SQL Course') Sql Course4
Using Case Manipulation FunctionsDisplay the employee number, name, and departmentnumber for employee Higgins:SELECT employee id, last name, department idFROMemployeesWHERE last name 'higgins';no rows selectedSELECT employee id, last name, department idFROMemployeesWHERE LOWER(last name) 'higgins';5
Working with Dates The default date display format is DD-MON-RR.SELECT last name, hire dateFROMemployeesWHERE last name like 'G%';6
Using Arithmetic Operatorswith DatesSELECT last name, (SYSDATE-hire date)/7 AS WEEKSFROMemployeesWHERE department id 90;SYSDATE is a function that returns current Date andTimeSubtract two dates to find the number of daysbetween those dates7
Data Type ConversionTO NUMBERNUMBERTO DATECHARACTERTO CHARTO CHAR8DATE
General Functions: NVL Function Converts a null to an actual value. Data types must match:Data types that can be used are date, character, andnumber.– NVL (commission pct, 0)– NVL (hire date, '01-JAN-97')– NVL (job id, 'No Job Yet')9
Using the NVL FunctionSELECT last name, salary, NVL(commission pct, 0),1(salary*12) (salary*12*NVL(commission pct, 0)) AN SALFROM employees; 11022
Summarizing Data UsingGroup Functions
Group Functions Types of Group Functions include:- AVG- COUNT- MAX- MIN- SUM Group functions operate on set of values to return ONEvalue. Group Functions SyntaxSELECTFROM[WHERE[GROUP BY[ORDER BY[column,] group function(column), .tablecondition]column]column];12
Using the AVG and SUM FunctionsYou can use AVG and SUM for numeric data.SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)FROMemployeesWHERE job id LIKE '%REP%';13
Using the MIN and MAX FunctionsYou can use MIN and MAX for any data type.SELECT MIN(hire date), MAX(hire date)FROMemployees;14
Using the COUNT FunctionCOUNT(*) returns the number of rows in a table.SELECT COUNT(*)FROMemployees;COUNT(*) returns the number of rows in a table that satisfy thecriteria of the SELECT statement, including duplicate rows androws containing null values in any of the columns.15
Using the COUNT Function COUNT(column) returns the number of rows with nonnull values for the column.SELECT COUNT(commission pct)FROMemployeesWHERE department id 80;16
Using the DISTINCT Keyword COUNT(DISTINCT column) returns the number ofdistinct non-null values of the column.Display the number of distinct department values in theEMPLOYEES table.SELECT COUNT(DISTINCT department id)FROMemployees;17
Group Functions and Null Values Group functions ignore null values in the column.SELECT AVG(commission pct)FROMemployees; The average is calculated as the total commissionpaid divided by the number of employees receiving acommission (4).18
Using the NVL Functionwith Group Functions The NVL function forces group functions to includenull values.SELECT AVG(NVL(commission pct, 0))FROMemployees; Theaverage is calculated as the total commissionpaid to all employees divided by the total number ofemployees in the company (20).19
Group Functions Display maximum salary of all employees.Select MAX (Salary)From Employees; Display maximum salary of employees in department 20.Select MAX (Salary)From EmployeesWhere department id 20;20
Grouping What if we need to display max salary ofemployees in each department?Solution :We need to repeat the last select statement x timeswhere x is the number of departments in the system.OR Use Group By Clause21
Creating Groups of Data:The GROUP BY Clause SyntaxSELECTFROM[WHERE[GROUP BY[ORDER BYcolumn, group function(column)tablecondition]group by column ]column]; Divide rows in a table into smaller groups by usingthe GROUP BY clause. If the group-by column contains null values, a groupwill be created for them.22
Using the GROUP BY Clause Display the average salary for each departmentSELECTdepartment id, AVG(salary)FROMemployeesGROUP BY department id ;23
Using the GROUP BY Clause The GROUP BY column does not have to be in theSELECT list.SELECTAVG(salary)FROMemployeesGROUP BY department id ;Is the query objective clear in this case ?24
Illegal QueriesUsing Group Functions Any column in the SELECT list that isnot an aggregate function must be in the GROUP BYclause.SELECT department id, COUNT(last name)FROMemployees;SELECT department id, COUNT(last name)*ERROR at line 1:ORA-00937: not a single-group group function25
SQL Statement Execution1. Table is identified due to FROM clause.2. Rows are selected due to the WHERE condition.3. Rows are grouped due to the GROUP BY clause.4. The GROUP FUNCTION is applied to each group.5. OREDER BY clause sorts results.26
SQL Statement Execution: ExampleSELECTdepartment id, AVG(salary)FROMemployeesGROUP BY department id ;– The FROM clause specifies the tables that the database must access: theEMPLOYEES table.– The WHERE clause specifies the rows to be retrieved. Since there is noWHERE clause, all rows are retrieved by default.– The GROUP BY clause specifies how the rows should be grouped. Therows are being grouped by department number.– The AVG function applied to the salary column will calculate the averagesalary for each department.– The SELECT clause displays department number and average salary foreach department.27
Illegal QueriesUsing Group Functions You cannot use the WHERE clause to restrict groups.You cannot use group functions in the WHERE clause.SELECTFROMWHEREGROUP BYdepartment id, AVG(salary)employeesAVG(salary) 8000department id;WHEREAVG(salary) 8000*ERROR at line 3:ORA-00934: group function is not allowed here28
Having Clause
Excluding Group Results: The HAVINGClause To restrict the group results, that is displayonly groups that satisfy a specific condition,we use HAVING clause.SELECTFROM[WHERE[GROUP BY[HAVING[ORDER BYcolumn, group functiontablecondition]group by expression]group condition]column];30
Excluding Group Results: The HAVINGClauseUse the HAVING clause to restrict groups:1. Table is identified due to FROM clause.2. Rows are selected due to the WHERE condition.3. Rows are grouped due to the GROUP BY clause.4. The group function is applied to each group.5. Groups matching HAVING clause are returned.6. The ORDER BY clause sorts results.31
Using the HAVING ClauseSELECTFROMGROUP BYHAVINGdepartment id, MAX(salary)employeesdepartment idMAX(salary) 10000 ;32
General SyntaxSELECT[DISTINCT] { * column [alias], }FROMtable[WHEREcondition (s)][GROUP BYgroup-by column][HAVINGgroup condition][ORDER BY{column alias} [ASC DESC]];33
Subqueries
Subquery SyntaxSELECTFROMWHEREselect listtablecolumn operator(SELECTFROMselect listtable); The subquery (inner query) executes once before themain query (outer query). The result of the subquery is used by the main query. You can place the subquery in a number of SQL clauses,including the following:–WHERE clause–HAVING clause35
Subquery Enclose subqueries in parentheses. Place subqueries on the right side of the comparisoncondition. The ORDER BY clause cannot be used in thesubquery. They can be very useful when you need to select rowsfrom a table with a condition that depends on the datain the table itself, or data from another table.36
Using a SubqueryWho gets a higher salary than employee number 141 ?SELECT last name11000FROMemployeesWHERE salary (SELECT salaryFROMemployeesWHERE employee id 141);37
Types of Subqueries–Single-row subqueryMain querySubquery–returnsST CLERKMultiple-row subqueryMain querySubqueryreturnsST CLERKSA MANSingle-row subqueries:Queries that return only one row from inner SELECT statementMultiple-row subqueries:Queries that return more than one row from inner SELECT statement38
Using Subqueries Usesingle-rowoperatorswithsingle-rowsubqueries, and use multiple-row operators withmultiple-row subqueries.39
Single-Row Subqueries Return only one rowUse single-row comparison operatorsOperatorMeaning Equal to Greater than Greater than or equal to Less than Less than or equal to Not equal to40
Multiple-Row Subqueries Returns more than one row Use multiple-row comparison operators, suchas IN.41
Single-Row Subqueries Display the employees whose job ID is the same asthat of employee 141:SELECT last name, job idFROMemployeesWHERE job id (SELECT job idFROMemployeesWHERE employee id 141);42
Single-Row Subqueries Display information about the employees who work inthe Sales department:SELECT last name, job idFROMemployeesWHERE departmentid (SELECT departmentidFROMdepartmentsWHERE departmentName ‘Sales’);43
Using Group Functions in a SubqueryDisplay the employee last name, job ID, and salary of allemployees whose salary is equal to the minimum salarySELECT last name, job id, salaryFROMemployees2500WHERE salary (SELECT MIN(salary)FROMemployees);44
The HAVING Clause with Subqueries The Oracle server executes subqueries first.The Oracle server returns results into the HAVINGclause of the main query. Display all the departments that have a minimumsalary greater than that of department 50.SELECTFROMGROUP BYHAVINGdepartment id, MIN(salary)employeesdepartment id2500MIN(salary) (SELECT MIN(salary)FROMemployeesWHERE department id 50);45
What Is Wrong with This Statement?SELECT employee id, last nameFROMemployeesWHERE salary (SELECTMIN(salary)FROMemployeesGROUP BY department id);ERROR at line 4:ORA-01427: single-row subquery returns more thanone rowSingle-row operator with multiple-row subqueryHow to correct this error ? Change the operator to IN46
PracticeSQL Developer
4848
Writing SQL Statements SQL statements are not case sensitive. SQL statements can be on one or more lines. Keywords cannot be abbreviated. Clauses are usually placed on separate lines. Indents are used to enhance readability.49
Practice Displaytheminimum,maximum,average salary for each job type.50sum,and
SELECT job id, MAX(salary), MIN(salary), SUM(salary), AVG(salary)FROM EMPLOYEESGROUP BY job id51
Thank You
27 SQL Statement Execution: Example – The FROM clause specifies the tables that the database must access: the EMPLOYEES table. – The WHERE clause specifies the rows to be retrieved. Since there is no WHERE clause, all rows are retrieved by default. – The GROUP BY clause specifies how the rows should be
