Оцените презентацию от 1 до 5 баллов!
Тип файла:
ppt / pptx (powerpoint)
Всего слайдов:
54 слайда
Для класса:
1,2,3,4,5,6,7,8,9,10,11
Размер файла:
1.04 MB
Просмотров:
60
Скачиваний:
0
Автор:
неизвестен
Слайды и текст к этой презентации:
№1 слайд![. Databases and JDBC .](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img0.jpg)
Содержание слайда: 8. Databases and JDBC
1. Introduction to Databases
№2 слайд![Relational DBMS A DBMS in](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img1.jpg)
Содержание слайда: Relational DBMS
A DBMS in which data is stored in tables and the relationships among the data are also stored in tables
The data can be accessed or reassembled in many different ways without having to change the table forms.
№3 слайд![Relational DBMS Commercial](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img2.jpg)
Содержание слайда: Relational DBMS
Commercial
Oracle
MS SQL Server
DB2
№4 слайд![Cash Management System](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img3.jpg)
Содержание слайда: Cash Management System
№5 слайд![Merchant Info Name Bank Bank](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img4.jpg)
Содержание слайда: Merchant Info
Name
Bank
Bank account
Charge percent
Aggregation period
Minimal sum
№6 слайд![Customer Info Name Address](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img5.jpg)
Содержание слайда: Customer Info
Name
Address
Email
Credit card No
Credit card type
Credit card maturity date
№7 слайд![Payment info Date Customer](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img6.jpg)
Содержание слайда: Payment info
Date
Customer
Merchant
Goods description
Sum
№8 слайд![Java DB Java DB is Oracle s](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img7.jpg)
Содержание слайда: Java DB
Java DB is Oracle's supported distribution of the Apache Derby open source database
It supports standard ANSI/ISO SQL through the JDBC and Java EE APIs
Java DB is included in the JDK
http://www.oracle.com/technetwork/java/javadb/overview/index.html
№9 слайд![Eclipse amp Java DB Creating](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img8.jpg)
Содержание слайда: Eclipse & Java DB
Creating a Driver Definition for Apache Derby
Creating an Apache Derby Connection Profile
Connecting to Apache Derby
Creating and Executing a SQL Query
№10 слайд![Driver Definition of Start](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img9.jpg)
Содержание слайда: Driver Definition (1 of 2)
Start Eclipse
Menu Window -> Preferences
Expand Data Management -> Connectivity -> Driver Definitions
Click Add button
Select “Derby Embedded JDBC Driver” in Name/Type tab
№11 слайд![Driver Definition of Select](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img10.jpg)
Содержание слайда: Driver Definition (2 of 2)
Select derby.jar in Jar list tab and click Add JAR/Zip button
Select full path to derby.jar (usually C:\Program Files\Java\jdk1.7.0_05\db\lib)
Click Open button
Click Ok button
№12 слайд![Eclipse amp Java DB Creating](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img11.jpg)
Содержание слайда: Eclipse & Java DB
Creating a Driver Definition for Apache Derby
Creating an Apache Derby Connection Profile
Connecting to Apache Derby
Creating and Executing a SQL Query
№13 слайд![Connection Profile Switch to](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img12.jpg)
Содержание слайда: Connection Profile
Switch to the Database Development perspective
In Data Source Explorer, right-click Database Connections and select New
Select Derby, change Name of profile (optionally) and click Next
Select Database location and click Finish
№14 слайд![Eclipse amp Java DB Creating](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img13.jpg)
Содержание слайда: Eclipse & Java DB
Creating a Driver Definition for Apache Derby
Creating an Apache Derby Connection Profile
Connecting to Apache Derby
Creating and Executing a SQL Query
№15 слайд![Connecting to the Database In](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img14.jpg)
Содержание слайда: Connecting to the Database
In the Database Development perspective, expand Database Connections in the Data Source Explorer
Right-click the connection profile that you created and select Connect
№16 слайд![Eclipse amp Java DB Creating](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img15.jpg)
Содержание слайда: Eclipse & Java DB
Creating a Driver Definition for Apache Derby
Creating an Apache Derby Connection Profile
Connecting to Apache Derby
Creating and Executing a SQL Query
№17 слайд![SQL Query In the Database](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img16.jpg)
Содержание слайда: SQL Query
In the Database Development perspective, expand Database Connections in the Data Source Explorer
Right-click the connection profile that you created and select “Open SQL Scrapbook”
Select database
Create SQL query in the editor field
Right-click in the editor and select Execute All.
№18 слайд![Merchant Info Name Bank Bank](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img17.jpg)
Содержание слайда: Merchant Info
Name
Bank
Bank account
Charge percent
Aggregation period
Minimal sum
№19 слайд![Create Merchant Table CREATE](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img18.jpg)
Содержание слайда: Create Merchant Table
CREATE TABLE merchant
(
id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
name VARCHAR(60) NOT NULL,
bankName VARCHAR (100) NOT NULL,
swift VARCHAR (40) NOT NULL,
account VARCHAR (20) NOT NULL,
charge DECIMAL(5,2) NOT NULL,
period SMALLINT NOT NULL,
minSum DECIMAL (19,2) NOT NULL,
total DECIMAL(19,2),
PRIMARY KEY (id)
);
№20 слайд![Fill Merchant Table INSERT](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img19.jpg)
Содержание слайда: Fill Merchant Table
INSERT INTO merchant
(name, charge, period, minSum,
bankName, swift, account)
VALUES('Jim Smith Ltd.', 5.1, 1, 100.0,
'Chase Manhatten', 'AA245BXW',
'247991002');
№21 слайд![Display Merchant Data](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img20.jpg)
Содержание слайда: Display Merchant Data
№22 слайд![Create Customer Table](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img21.jpg)
Содержание слайда: Create Customer Table
Customer Info
Name
Address
Email
Credit card No
Credit card type
Credit card maturity date
№23 слайд![Create Customer Table CREATE](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img22.jpg)
Содержание слайда: Create Customer Table
CREATE TABLE customer
(
id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
name VARCHAR(60) NOT NULL,
address VARCHAR(300) NOT NULL,
email VARCHAR(90) NOT NULL,
ccNo VARCHAR(20) NOT NULL,
ccType VARCHAR(60) NOT NULL,
maturity DATE,
PRIMARY KEY (id)
);
№24 слайд![Fill Customer Table INSERT](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img23.jpg)
Содержание слайда: Fill Customer Table
INSERT INTO customer
(name, address, email, ccNo, ccType, maturity)
values('Dan Nelis',
'Vosselaar st. 19, Trnaut, Belgium', 'Dan@adw.com',
'11345694671214',
'MasterCard',
'2014-07-31');
№25 слайд![Display Customer Data select](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img24.jpg)
Содержание слайда: Display Customer Data
select * from customer
№26 слайд![Create Payment Table Payment](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img25.jpg)
Содержание слайда: Create Payment Table
Payment info
Date
Customer
Merchant
Goods description
Sum
№27 слайд![Create Payment Table CREATE](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img26.jpg)
Содержание слайда: Create Payment Table
CREATE TABLE payment
(
id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
dt TIMESTAMP NOT NULL,
merchantId INT CONSTRAINT mer_fk references merchant,
customerId INT CONSTRAINT cust_fk references customer,
goods VARCHAR(500),
total DECIMAL(15,2),
charge DECIMAL(15,2),
PRIMARY KEY (id)
);
№28 слайд![Fill Payment Table insert](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img27.jpg)
Содержание слайда: Fill Payment Table
insert into payment
(dt, merchantId, customerId, goods, total)
values('2012-07-12 10:00:14', 3, 1,
'CD Europe Maps', 12.08);
№29 слайд![Display Payment Data Select](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img28.jpg)
Содержание слайда: Display Payment Data
Select * from payment
№30 слайд![CM Database Schema](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img29.jpg)
Содержание слайда: CM Database Schema
№31 слайд![Select Statement Don t use in](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img30.jpg)
Содержание слайда: Select Statement
Don’t use * in select!
№32 слайд![Select Statement SELECT dt,](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img31.jpg)
Содержание слайда: Select Statement
SELECT dt, merchantId, customerId, goods, total FROM payment WHERE merchantId = 3;
№33 слайд![Select Statement SELECT dt,](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img32.jpg)
Содержание слайда: Select Statement
SELECT dt, merchantId, customerId, goods, total FROM payment WHERE merchantId = 3;
№34 слайд![Select Statement SELECT dt,](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img33.jpg)
Содержание слайда: Select Statement
SELECT dt, merchantId, customerId, goods, total FROM payment ORDER BY merchantId;
№35 слайд![Select Statement SELECT dt,](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img34.jpg)
Содержание слайда: Select Statement
SELECT dt, merchantId, customerId, goods, total FROM payment ORDER BY merchantId;
№36 слайд![Select Statement SELECT sum](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img35.jpg)
Содержание слайда: Select Statement
SELECT sum(total) FROM payment WHERE customerId = 2;
№37 слайд![Select Statement SELECT sum](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img36.jpg)
Содержание слайда: Select Statement
SELECT sum(total) FROM payment WHERE customerId = 2;
Output is 2134.45
№38 слайд![Select Statement SELECT](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img37.jpg)
Содержание слайда: Select Statement
SELECT merchantId, count(*) as n, sum(total) as total FROM payment GROUP BY merchantId;
№39 слайд![Select Statement SELECT](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img38.jpg)
Содержание слайда: Select Statement
SELECT merchantId, count(*) as n, sum(total) as total FROM payment GROUP BY merchantId;
№40 слайд![Select Statement SELECT](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img39.jpg)
Содержание слайда: Select Statement
SELECT customerId, sum(total) FROM payment GROUP BY customerId HAVING count(*)>2;
№41 слайд![Select Statement SELECT](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img40.jpg)
Содержание слайда: Select Statement
SELECT customerId, sum(total) FROM payment GROUP BY customerId HAVING count(*)>2;
№42 слайд![Join Operations SELECT p.dt,](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img41.jpg)
Содержание слайда: Join Operations
SELECT p.dt, m.name as merchant, c.name as customer,
p.goods, p.total
FROM payment p
LEFT OUTER JOIN merchant m on m.id = p.merchantId
LEFT OUTER JOIN customer c on c.id = p.customerId;
SELECT p.dt, m.name as merchant, c.name as customer, p.goods, p.total
FROM payment p, merchant m, customer c
WHERE m.id = p.merchantId and c.id = p.customerId;
№43 слайд![Join Operations](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img42.jpg)
Содержание слайда: Join Operations
№44 слайд![Update Payments](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img43.jpg)
Содержание слайда: Update Payments
№45 слайд![Update Statement UPDATE](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img44.jpg)
Содержание слайда: Update Statement
UPDATE payment SET charge = total * 0.034 WHERE id = 1;
№46 слайд![Update Statement UPDATE](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img45.jpg)
Содержание слайда: Update Statement
UPDATE payment SET charge = total * 0.034 WHERE id = 1;
№47 слайд![Update Statement UPDATE](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img46.jpg)
Содержание слайда: Update Statement
UPDATE payment
SET charge = (SELECT p.total * m.charge / 100.0
FROM payment p, merchant m
WHERE m.id = p.merchantId and p.id = 2)
WHERE id = 2;
№48 слайд![Update Statement](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img47.jpg)
Содержание слайда: Update Statement
№49 слайд![Update Statement UPDATE](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img48.jpg)
Содержание слайда: Update Statement
UPDATE payment p SET charge = total * (SELECT charge FROM merchant m WHERE m.id = p.merchantId) / 100.0
№50 слайд![Update Statement](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img49.jpg)
Содержание слайда: Update Statement
№51 слайд![Update Merchants](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img50.jpg)
Содержание слайда: Update Merchants
№52 слайд![Update Merchants UPDATE](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img51.jpg)
Содержание слайда: Update Merchants
UPDATE merchant m SET total =
(SELECT sum(total - charge)
FROM payment p WHERE p.merchantId=m.id)
№53 слайд![Update Merchants](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img52.jpg)
Содержание слайда: Update Merchants
№54 слайд![Manuals http docs.oracle.com](/documents_6/0b46a011e99c3a7f92be8a2db875e3ba/img53.jpg)
Содержание слайда: Manuals
http://docs.oracle.com/javadb/10.8.2.2/ref/refderby.pdf
http://docs.oracle.com/javadb/10.8.2.2/devguide/derbydev.pdf