IST 331 Notes - SELECT sum(balance),count(*), avr(balance),...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: SELECT sum(balance),count(*), avr(balance), repnum FROM customer GROUP BY repnum HAVING avg(balance) > 2000 ; having needs group by or it will not make any sense, having is a aggregate function having balance > age(balance) <~~ This will not work because the aggregate function needs to be right after the having statement A Cartesian product of two finite sets can be represented by a table, with one set as the rows and the other as the columns, and forming the ordered pairs, the cells of the table, by choosing the element of the set from the row and the column. SELECT * FROM rep,customer; Types of joins - Inner Join(natural Join) An inner join essentially combines the records from two tables (A and B) based on a given join-predicate. The SQL-engine computes the Cartesian product of all records in the tables. Thus, processing combines each record in table A with every record in table B. Only those records in the joined table that satisfy the join predicate remain. This type of join occurs the most commonly in applications, and represents the default join-type. Outer join - Left and Right outer join An outer join does not require each record in the two joined tables to have a matching record in the other table. The joined table retains each record�even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table(s) one retains the rows from (left, right, or both). The result of a left outer join for tables A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result�but with NULL in each column from B. This means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate). A right outer join closely resembles a left outer join, except with the tables reversed. Every record from the "right" table (B) will appear in the joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in A. A right outer join returns all the values from the right table and matched values from the left table ( NULL in case of no matching join predicate). SELECT customer.customernum, orders.orderdate, orders.ordernum, customername FROM customer inner join orders on customer.customernum = orders.customernum ORDER BY orderdate; SELECT customer.customernum, orders.orderdate, orders.ordernum, customername FROM customer, orders WHERE (((customer.customernum)=[orders].[customernum])) ORDER BY orderdate; SELECT customer.customernum, orders.orderdate, orders.ordernum, customername FROM customer left join orders on customer.customernum = orders.customernum; SELECT customer.customernum, orders.orderdate, orders.ordernum, customername FROM customer right join orders on customer.customernum = orders.customernum; SELECT customer.customernum FROM customer left JOIN orders on customer.customernum = orders.customernum WHERE orders.customernum not in (SELECT customer.customernum FROM customer right JOIN orders on customer.customernum = orders.customernum); SELECT customer.CustomerNum, customer.CustomerName FROM customer WHERE orders.customer not in (SELECT customer.customernum FROM customer right JOIN orders on customer.customernum = orders.customernum); ...
View Full Document

Ask a homework question - tutors are online