140 likes | 224 Views
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 ()
E N D
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 () 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.
Group Functions SELECT SUM(Salary), AVG(Salary), MAX(Salary), MIN(Salary) FROM EMPLOYEE;
Grouping Data • The rows in a table can be divided into different groups to treat each group separately.
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]];
Grouping Data SELECT DeptID, COUNT(*) FROM employee GROUP BY DeptID
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
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);
Table aliases • Table aliases are used to avoid using lengthy table names over and over again in a query.
Equijoin • SELECT student.Last STUDENT, faculty.Name Faculty, faculty.Phone TEL • FROM student, faculty • WHERE student.FacultyID = faculty.FacultyID
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;
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 (+);
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
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;