Transcript table - Binus Repository

Populating and Querying tables
Insert and mostly View (DML)
Contents of this lecture
• Inserting data into a single table.
• Queries
–
–
–
–
–
On two tables.
On two joined tables.
On more than two joined tables.
Lop-sided queries (outer joins).
Tables joined to themselves.
– Group data using the GROUP BY clause
– Include or exclude grouped rows by using the HAVING clause
Inserting data into a table
• To add a row of data to a table
INSERT INTO <table> VALUES
(value1, value2, …valueN)
• If the value list matches the column list
exactly, there is no need to list the column
names.
• If the value list does not match the column
list exactly, there is a need to list the column
names.
Example
• insert into Expert values (2222221,'Dr.
O''Meara','Orthopaedic');
• Where the expert table was set up as:CREATE TABLE Expert (
Expert_Id numeric(7,0),
Expert_Name varchar(50),
Expertise_area varchar(15),
PRIMARY KEY (Expert_Id))
Note also, if you want to put an apostrophe in a string, put in two
apostrophes.
Referential integrity
• The above tables have no foreign keys and a
straightforward insert does not compromise
referential integrity.
• If a table is constrained by foreign key values,
then there is a need to see what is in the other
table.
• Retrieving data from a database
– Data can be retrieved from a table or set of tables using
the SELECT statement
Common errors on insert
• Server: Msg 2627, Level 14, State 1, Line 1 Violation of
PRIMARY KEY constraint 'PK__Contract__05D8E0BE'.
Cannot insert duplicate key in object 'Contract'.
– This happens when you put a value in the primary key field that
already appears in the file – i.e. record already exists.
• INSERT statement conflicted with COLUMN FOREIGN
KEY constraint 'FK__Contract__Custom__06CD04F7'.
The conflict occurred in database 'pobyrne', table
'customer', column 'Customer_number'.
– In this statement, I tried to add a contract to the contract table,
referencing a customer that was not in the customer table. The
contract table has customer_number as a foreign key referencing
the customer table.
Enquiry Access Paths
• Traversing a
data model
• Start with a
data model or
ERD.
Customer
Staff
Supplier
Customer Id
Customer Name
Customer Address
Staff name
Staff no
Staff role
Supplier Id
Supplier Name
Supplier Address
Amount Owed
Stock
Docket
DocketNo
Order Date
*Customer Id
DocketPaidSig
DocketRcvdSig
*Staff no
Order Line
*DocketNo
*Stock Code
QuantityRequired
Stock Code
Stock Description
Unit Price
*Supplier Id
UnitCostPrice
Stock level
Reorder level
SupplierOrder
*Supplier Id
SupplierOrderNo
SupplierOrderDate
DeliveredDate
SupplierOrderLine
*SupplierOrderNo
*Stock Code
StockRequired
Customer
Staff
Customer Id
Customer Name
Customer Address
Staff name
Staff no
Staff role
ERD
Docket
DocketNo
Order Date
*Customer Id
DocketPaidSig
DocketRcvdSig
*Staff no
Supplier
Supplier Id
Supplier Name
Supplier Address
Amount Owed
Stock
SupplierOrder
Stock Code
Stock Description
Unit Price
*Supplier Id
UnitCostPrice
Stock level
Reorder level
*Supplier Id
SupplierOrderNo
SupplierOrderDate
DeliveredDate
Order Line
*DocketNo
*Stock Code
QuantityRequired
SupplierOrderLine
*SupplierOrderNo
*Stock Code
StockRequired
Entry Point
Which customers have bought stock
supplied by ‘supplierId’?
Which customers have bought stock supplied by
OPERATION LIST
Supplier
Set of
Stock
Stock
Set of
Order Line
Order Line
Docket
Customer
What are the sales that this staff member made?
Customer
Staff
Supplier
Customer Id
Customer Name
Customer Address
Staff name
Staff no
Staff role
Supplier Id
Supplier Name
Supplier Address
Amount Owed
Docket
DocketNo
Order Date
*Customer Id
DocketPaidSig
DocketRcvdSig
*Staff no
Stock
SupplierOrder
Stock Code
Stock Description
Unit Price
*Supplier Id
UnitCostPrice
Stock level
Reorder level
*Supplier Id
SupplierOrderNo
SupplierOrderDate
DeliveredDate
Order Line
*DocketNo
*Stock Code
QuantityRequired
SupplierOrderLine
*SupplierOrderNo
*Stock Code
StockRequired
What are the sales that this staff member made?
Staff no
Staff
Set of
Docket
Docket
Set of
Order Line
Order Line
Multi-table queries
Revisiting database structure
External
Schema
Conceptual
Schema
Internal
Schema
Physical
Schema
The external schema
– Level visible to user
– Multiple views of the
system
• e.g. View an order - see
limited product and
customer information
– Only the database
Administrator may
access the whole
database at this level
• Each external view is
defined by means of an
external schema
– Provides definitions of each
external view.
– Written in a Data Definition
Language.
– individual to the user or
user group.
– accessed through a 3GL, a
query language or a special
purpose forms or menubased language
Queries
• User creates a query, to provide a specific view
of the data
• Database application converts this into a
statement in SQL
• DBMS executes the SQL statement
• BASE TABLE - a table storing data
• DERIVED TABLE - virtual result table from a
query
Query essentials
• Selection
– This is where rows from a table are extracted on the
basis of the value of one or more of the fields
• Projection
– This is where columns from a table are extracted on the
basis of the value of one or more of the fields
• Join
– This is where rows and columns from more than one
table are extracted
Selection
• Selects only those ROWS which satisfy the
selection criteria
• Example:
– Set up a query to display claims where the state
is trivial (T):
Select * from Claim where Claim_state = 'T'
Projection
• A projection of a table onto a subset of its
attributes (i.e. throw away unnecessary
attributes)
• e.g. project Expert Name and Expertise from
Expert:
– Select Expert_Name, Expertise_area from Expert
Joins
• Tables can be joined together on fields
which have the same attributes:
SELECT * FROM products, suppliers
WHERE products.supplierId =
suppliers.supplierId;
– Note: Tables can be joined in this way even if
they were set up without a foreign key.
More usually, the query uses ‘join’
• Select * from products join suppliers on
products.supplierId = suppliers.supplierId
3-table join
• If you have a query that involves more than
one relationship, you must join over
relationships
• For example (Northwind),
– “Show me the sales for each product, ordered
by category and product , giving the
categoryname and product name.
Categories
Shippers
Orders
CategoryId
CategoryName
Description
Picture
ShipperId
CompanyName
Phone
OrderDate
RequiredDate
ShippedDate
Freight
ShipName
ShipAddress
ShipCity
ShipRegion
ShipPostalCode
ShipCountry
OrderId
*ShipVia
*CustomerId
*EmployeeId
Products
Suppliers
SupplierId
CompanyName
ContactName
ContactTitle
Address
City
Region
PostalCode
Country
Phone
Fax
HomePage
ProductId
ProductName
*CategoryId
QuantityPerUnit
UnitPrice
UnitsInStock
UnitsOnOrder
ReorderLevel
Discontinued
*SupplierId
Order details
*OrderId
*ProductId
UnitPrice
Quantity
Discount
Note:
ShipVia is the name of
the foreign key in Order
Details that refers to
the Shippers entity.
ReportsTo is the name of
the foreign key in the
recursive relationship
in the Employee entity
NorthWind ERD
Customers
CustomerId
CompanyName
ContactName
ContactTitle
Address
City
Region
PostalCode
Country
Phone
Fax
Employee
EmployeeId
LastName
FirstName
Title
TitleOfCourtesy
BirthDate
HireDate
Address
City
Region
PostalCode
Country
HomePhone
Extension
Photo
Notes
PhotoPath
*ReportsTo
Enquiry Access Path
Sales for product by category
OPERATION LIST
Entry Point
Categories
Set of
Products
Products
Set of
Order details
Order details
Joins required
• Categories needs to join with products
• Products needs to join with Order details
• Joins can be nested
A join B on A.x = B.x
join C on B.y = C.y
Join D on C.z = D.z
etc.
• In this example, there are 2 joins
• Categories join products on
Categories.categoryId = Products.CategoryId
• Products join [order details] on
products.productId = [order
details].productId
– There is also a derived column, from the
[order details] table. Income = (unitPrice
* quantity) - discount
Query
use Northwind;
Select categoryName, ProductName,
(([order details].unitPrice*Quantity)Discount)
as income
from
categories join products on
categories.categoryid = products.categoryid
join [order details]
on products.productid = [order
details].productid;
Result
categoryName
--------------Beverages
Beverages
Beverages
Beverages
Beverages
Beverages
Beverages
Beverages
Beverages
Beverages
Beverages
Beverages
Beverages
Beverages
ProductName
---------------------------------------Chai
Chai
Chai
Chai
Chai
Chai
Chai
Chai
Chai
Chai
Chai
Chai
Chai
Chai
income
-----------------------647.79999
259.20001
288.0
215.85001
172.8
215.85001
144.0
345.60001
216.0
719.79999
143.85001
180.0
360.0
54.0
Improve layout of output
Select categoryName,
cast(ProductName as char(12))as Product,
cast(([order details].unitPrice*Quantity)Discount as decimal(10,2)) as income
from
categories join products on
categories.categoryid = products.categoryid
join [order details] on
products.productid = [order
details].productid
Improved output
categoryName
--------------Beverages
Beverages
Beverages
Beverages
Beverages
Beverages
Beverages
Beverages
Beverages
Beverages
Product
-----------Chai
Chai
Chai
Chai
Chai
Chai
Chai
Chai
Chai
Chai
income
-----------647.80
259.20
288.00
215.85
172.80
215.85
144.00
345.60
216.00
719.80
Tugas Individu
1. Kunjungi
http://www.sqlsource.com/
http://www.mhhe.com/mannino
2. Membuat ringkasan hasil kunjungan ke
URL di atas.