Oracle DML

1. Oracle DML Dr. Bernard Chen Ph.D. University of Central Arkansas

2. SQL code execution • To start: SQL> CONNECT system (and then type in password) • Then create a file: SQL> edit test (test is file name) remember to store the file in *.sql C:\oraclexe\app\oracle\product\11.2.0\server\bin • Once you done coding, execute the code: SQL> @c:\test.sql (test is file name)

3. RETRIEVING DATA FROM A TABLE • The main purpose of the SQL language is for querying the database • The most important statement or query is the SELECT query •  The general syntax is SELECT columnlist FROM tablename;

4. RETRIEVING DATA FROM A TABLE • Example: SELECT Last, First FROM student;

5. RETRIEVING DATA FROM A TABLE SELECT (*) • If you want to see all columns in a table, you do not have to list them all.  • You can use character asterisk (*) in place of the column list, and all columns will be displayed in the same order as the underlying table structure.

6. RETRIEVING DATA FROM A TABLE RESTRICTING DATA WITH A WHERE CLAUSE • A WHERE clause is used with the SELECT query to restrict rows picked • The general syntax of the WHERE clause is SELECT columnlist FROM tablename [WHERE condition(s)];

7. RETRIEVING DATA FROM A TABLE SELECT * FROM dept WHERE Location = ‘Monroe’; SELECT Lname, Fname, Salary, Deptid FROM employee WHERE Salary >= 50000;

8. RETRIEVING DATA FROM A TABLE SELECT Lname, Fname, Salary, Dno FROM employee WHERE Salary <= 50000 AND Salary >=25000; SELECT Lname, Fname, Salary, Dno FROM employee WHERE Salary BETWEEN 25000 AND 50000;

9. RETRIEVING DATA FROM A TABLE SORTING • The order of rows in a table is arbitrary. • You may want to see rows in a specific order based on a column or columns • For example, you may want to see employees in alphabetical order by their name

10. RETRIEVING DATA FROM A TABLE • The ORDER BY clause is used with the SELECT query to sort rows in a table.  • The general syntax is SELECT columnlist FROM tablename [WHERE condition(s)] [ORDER BY column|expression [ASC|DESC]];

11. RETRIEVING DATA FROM A TABLE SELECT Last, First FROM student ORDER BY Last; SELECT Last, First FROM student ORDER BY Last DESC;

12. Group Functions • The group functions perform an operation on a group of rows and return one result. Sum () Finds sum of all values in a column, ignores null values. Avg () Finds average of all values in a column, ignores null values. Max () Finds maximum value and ignores null values. Min () Finds minimum value and ignores null values. Count(), Count(*) Counts number of rows including nulls for *. Counts non-null values if column or expression is used as argument.

13. Group Functions SELECT SUM(Salary), AVG(Salary), MAX(Salary), MIN(Salary) FROM EMPLOYEE;

14. Grouping Data • The rows in a table can be divided into different groups to treat each group separately.

15. Grouping Data • The GROUP BY clause is used for grouping data. The general syntax is SELECT column, groupfunction (column) FROM tablename [WHERE condition(s)] [GROUP BY column|expression] [ORDER BY column|expression [ASC|DESC]];

16. Grouping Data SELECT DeptID, COUNT(*) FROM employee GROUP BY DeptID

17. Joins • When the required data is in more than one table, related tables are joined using a join condition. • In most cases, the common columns are the primary key in one table and a foreign key in another

18. Cartesian Product • SELECT Last, First, Name • FROM student, faculty;

19. Equijoin • The equijoin is a join with a join condition involving common columns from two tables. • Join Syntax: SELECT columnnames FROM tablenames WHERE join condition(s);

20. Equijoin • SELECT student.Last, faculty.Name • FROM student, faculty • WHERE student.FacultyID = faculty.FacultyID

21. Table Aliases • SELECT s.Last, f.Name • FROM student s, faculty f • WHERE student.FacultyID = faculty.FacultyID

22. Multiple Joins • SELECT e.Lname, d.DeptName, q.QualDesc • FROM EMPLOYEE e, Department d, QUALIFICATION q • WHERE e.DeptID = d.DeptID AND e.QualID = q.QualID

23. Creating a table using a subquery • You can create a table by using a nested SELECT query. • The Query will create a new table and populated it with the rows selected from the other table. CREATE TABLE tablename AS SELECT query

24. Creating a table using a subquery • CREATE TABLE temp • AS • SELECT Employee ID, Lname, Fname, Salary • FROM employee • WHERE DeptID=20; • DESCRIBE TABLE • SELECT * FROM temp;

25. Set Theory Union Intersect Minus

26. Set theory Syntax Generally, the syntax would looks like: Query Set Operation Query

27. Set theory Syntax Example: Select last from student UNION Select last from faculty

28. UNION Example: To retrieve the social security numbers of all employees who either work in department 5 (RESULT1 below) or directly supervise an employee who works in department 5 (RESULT2 below)

29. UNION DEP5_EMPS  DNO=5 (EMPLOYEE) RESULT1  SSN(DEP5_EMPS) RESULT2  SUPERSSN(DEP5_EMPS) RESULT  RESULT1  RESULT2 The union operation produces the tuples that are in either RESULT1 or RESULT2 or both

30. UNION DEP5_EMPS  DNO=5 (EMPLOYEE) RESULT1  SSN(DEP5_EMPS) RESULT2  SUPERSSN(DEP5_EMPS) RESULT  RESULT1  RESULT2 Select ssn from employee where dno=5 UNION Select superssn from employee where dno=5

31. Intersect Select ssn from employee where dno=5 INTERSECT Select superssn from employee where dno=5

32. Minus Select ssn from employee where dno=5 MINUS Select superssn from employee where dno=5

33. Relational Algebra Overview • Relational Algebra consists of several groups of operations • Unary Relational Operations • SELECT (symbol:  (sigma)) • PROJECT (symbol:  (pi)) • RENAME (symbol: (rho)) • Relational Algebra Operations From Set Theory • UNION (  ), INTERSECTION ( ), DIFFERENCE (or MINUS, – ) • CARTESIAN PRODUCT ( x ) • Binary Relational Operations • JOIN (several variations of JOIN exist) • DIVISION • Additional Relational Operations • OUTER JOINS, OUTER UNION • AGGREGATE FUNCTIONS

34. Relational Algebra Overview • Relational Algebra consists of several groups of operations • Unary Relational Operations • SELECT (symbol:  (sigma)) • PROJECT (symbol:  (pi)) • RENAME (symbol: (rho)) • Relational Algebra Operations From Set Theory • UNION (  ), INTERSECTION ( ), DIFFERENCE (or MINUS, – ) • CARTESIAN PRODUCT ( x ) • Binary Relational Operations • JOIN (several variations of JOIN exist) • DIVISION • Additional Relational Operations • OUTER JOINS, OUTER UNION • AGGREGATE FUNCTIONS

35. Convert

36. Aggregate Functions and Grouping • Use of the Aggregate Functional operation Ʒ • ƷMAX Salary (EMPLOYEE) retrieves the maximum salary value from the EMPLOYEE relation • ƷMIN Salary (EMPLOYEE) retrieves the minimum Salary value from the EMPLOYEE relation • ƷSUM Salary (EMPLOYEE) retrieves the sum of the Salary from the EMPLOYEE relation • ƷCOUNT SSN, AVERAGE Salary (EMPLOYEE) computes the count (number) of employees and their average salary

37. Aggregate Functions and Grouping • Grouping can be combined with Aggregate Functions • Example: For each department, retrieve the DNO, COUNT SSN, and AVERAGE SALARY • A variation of aggregate operation Ʒ allows this: • Grouping attribute placed to left of symbol • Aggregate functions to right of symbol • DNOƷ COUNT SSN, AVERAGE Salary (EMPLOYEE)

38. Group Functions

39. Grouping Data • The GROUP BY clause is used for grouping data. The general syntax is SELECT column, groupfunction (column) FROM tablename [WHERE condition(s)] [GROUP BY column|expression] [ORDER BY column|expression [ASC|DESC]];

40. JOIN • JOIN Operation (denoted by ) • The sequence of CARTESIAN PRODECT followed by SELECT is used quite commonly to identify and select related tuples from two relations • This operation is very important for any relational database with more than a single relation, because it allows us combine related tuples from various relations

41. JOIN • The general form of a join operation on two relations R(A1, A2, . . ., An) and S(B1, B2, . . ., Bm) is: R <join condition>S • where R and S can be any relations that result from general relational algebra expressions.

42. Join

43. Set Theory Union Intersect Minus Generally, the syntax would looks like: Query Set Operation Query