1 / 14

Working with Tables: Join, Functions and Grouping

Working with Tables: Join, Functions and Grouping. Dr. Bernard Chen Ph.D. University of Central Arkansas Fall 2008. 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 ()

yank
Télécharger la présentation

Working with Tables: Join, Functions and Grouping

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Working with Tables: Join, Functions and Grouping Dr. Bernard Chen Ph.D. University of Central Arkansas Fall 2008

  2. 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.

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

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

  5. 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]];

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

  7. 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

  8. 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);

  9. Table aliases • Table aliases are used to avoid using lengthy table names over and over again in a query.

  10. Equijoin • SELECT student.Last STUDENT, faculty.Name Faculty, faculty.Phone TEL • FROM student, faculty • WHERE student.FacultyID = faculty.FacultyID

  11. Outer Join • The (+) operator can be used on any side of the join condition, but it can’t be used on both sides in one condition. The general syntax is SELECT tablename1.columnname, tablename2.columnname FROM tablename1, tablename2 WHERE tablename1.columnname (+) = tablename2.columnname;

  12. Outer Join • The join condition will look different if the (+) operator is used on the right hand side. For example, WHERE tablename1.columnname = tablename2.columnname (+);

  13. 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

  14. 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;

More Related