18-01-2013, 02:40 PM
MORE ON DATABASES AND SQL
DATABASES.pdf (Size: 4.08 MB / Downloads: 50)
Learning Objectives
Puzzle
After studying this lesson the students will be able to:
Define the terms:
(i) Group (Aggregate) functions, Constraints
(ii) Cartesian Product, Join, Referential Integrity, Foreign Key.
Write queries using aggregate functions and GROUP BY clause.
Access data from multiple tables
Create tables with PRIMARY KEY and NOT NULL constraints
Add a constraint to a table, remove a constraint from a table, modify a
column of a table using ALTER TABLE command.
SUM() :
SUM() function is used to find the total value of any column or an expression based on a
column. SUM() also takes one argument which can be any column name or a valid
expression involving a column name. Like AVG(), the argument of SUM() function can be
of numeric (int/decimal) type only. Sums of String and Date type data are not defined.
e.g.
Displaying Data from Multiple Tables
In each situation that we have faced so far, the data was extracted from a single table.
There was no need to refer to more than one tables in the same statement. But many
times, in real applications of databases, it is required to produce reports which need data
from more than one tables. To understand this consider the following situations:
The management of the shoe factory wants a report of orders which lists three
columns: Order_No, corresponding customer name, and phone number.
- (MT-1)
In this case order number will be taken from Orders table and corresponding
customer name from Customers table.
The management wants a four-column report containing order_no, order_qty,
name of the corresponding shoe and its cost. - (MT-2)
In this case order number and order quantity will be taken from Orders table
and corresponding shoe name and cost from Shoes table.
The management wants the names of customers who have placed any order of
quantity more than 300. - (MT-3)
In this case Order quantity will be checked in Orders table and for each record
with quantity more than 300, corresponding Customer name will be taken
from Customers table.
The management wants a report in which with each Order_No management
needs name of the corresponding customer and also the total cost (Order
quantity x Cost of the shoe) of the order are shown. - (MT-4)
In this case order number will be taken from Orders table and corresponding
customer name from Customers table. For the cost of each order the quantity
will be taken from Orders table and the Cost from Shoes table