Компании
<<  Design Do-It-Yourself ABC Design  >>
Database Design
Database Design
Using APEX SQL editor
Using APEX SQL editor
Enter SQL command
Enter SQL command
Display the Table structure
Display the Table structure
Music Table Structure
Music Table Structure
Select command structure
Select command structure
Resulting Screen
Resulting Screen
SQL DESCRIBE
SQL DESCRIBE
Inserting Data
Inserting Data
Insert Data
Insert Data
Insert Data into table
Insert Data into table
Typical error messages
Typical error messages
Subset of data WHERE clause
Subset of data WHERE clause
ALTER table structure
ALTER table structure
Deleting a column from a table
Deleting a column from a table
Deleting a row
Deleting a row
Database Facts
Database Facts
Oracle Facts
Oracle Facts
Oracle Facts
Oracle Facts
Application Express SQL editor
Application Express SQL editor
Review the tables
Review the tables
Categories of SQL Statements
Categories of SQL Statements
Questions
Questions
KEYWORD, CLAUSE, STATEMENT
KEYWORD, CLAUSE, STATEMENT
Selection vs
Selection vs
Join
Join
SELECT statement
SELECT statement
SELECTION
SELECTION
Projections
Projections
Arithmetic Expressions
Arithmetic Expressions
Operator Precedence
Operator Precedence
Rewrite in order of precedence
Rewrite in order of precedence
What is null
What is null
Null Values in Arithmetic Expressions
Null Values in Arithmetic Expressions
Practice
Practice
Column Alias
Column Alias
Using Aliases
Using Aliases

Презентация: «Database Design». Автор: Marge Hohly. Файл: «Database Design.ppt». Размер zip-архива: 355 КБ.

Database Design

содержание презентации «Database Design.ppt»
СлайдТекст
1 Database Design

Database Design

Sections 12 & 15 - Introduction to Application Express (APEX) SQL editor, Introduction to SQL statements

2 Using APEX SQL editor

Using APEX SQL editor

You may either type the command into the SQL editor or use the cut and paste option If you are going to cut/paste the command copy the command from the word or PowerPoint document into NotePad. This will drop out hidden characters. You may need to do some editing to remove extra characters, or replace incorrect characters. Next copy the command from the NotePad into the editor

Marge Hohly

2

3 Enter SQL command

Enter SQL command

Marge Hohly

3

4 Display the Table structure

Display the Table structure

Enter the following command: DESCRIBE music; The structure of the table should be shown. Results on next slide

Marge Hohly

4

5 Music Table Structure

Music Table Structure

Marge Hohly

5

6 Select command structure

Select command structure

SELECT field1, field2, field3 FROM table_name WHERE condition; Try the following command SELECT employee_id, first_name, last_name, department_id FROM employees; SELECT employee_id, first_name, last_name, department_id FROM employees WHERE department_id = 90;

Marge Hohly

6

7 Resulting Screen

Resulting Screen

Marge Hohly

7

8 SQL DESCRIBE

SQL DESCRIBE

DESCRIBE <table name>; DESCRIBE employees; Try the last statement.

Marge Hohly

8

9 Inserting Data

Inserting Data

INSERT INTO tablename (column1, column2,....) VALUES(value1,value2,...); Remember character data needs to be enclosed in single quotes.

Marge Hohly

9

10 Insert Data

Insert Data

Note the data types for each column Inserting data into the table. Since the table is empty all fields need to be populated, so column names can be omitted INSERT INTO music VALUES (10,'Marge Hohly','Folk'); This will insert one record into the table Next display the contents of the table to view the data SELECT * FROM music;

Marge Hohly

10

11 Insert Data into table

Insert Data into table

INSERT INTO <table name> VALUES (value 1, value 2, value 3, etc); INSERT INTO music VALUES (10,'Marge Hohly','Folk');

Marge Hohly

11

12 Typical error messages

Typical error messages

The following statement has a spelling error: SELCT * FROM employees; The error message is: ORA-00900: invalid SQL statement The following statement incorrectly names the table employee instead of employees: SELECT * FROM employee; The error message is: ORA-00942: table or view does not exist Run the correct statement.

Marge Hohly

12

13 Subset of data WHERE clause

Subset of data WHERE clause

SELECT <column name 1, column name 2, etc.> FROM <table name> WHERE <condition>; SELECT first_name, last_name, salary FROM employees WHERE salary > 5000;

Marge Hohly

13

14 ALTER table structure

ALTER table structure

ALTER TABLE <table name> ADD (<new_column_name> <data type>); Try to modify the structure of the MUSIC table Add a column (which you will misspell) COUNTRI instead of COUNTRY). ALTER TABLE my_music ADD (countri VARCHAR2(20));

Marge Hohly

14

15 Deleting a column from a table

Deleting a column from a table

ALTER TABLE <table name> DROP COLUMN <column_name>; Now delete the column (COUNTRI) you just added.

Marge Hohly

15

16 Deleting a row

Deleting a row

DELETE from <table name> WHERE <column_name> = 'some value' ; DELETE from music WHERE musicid = 10; DELETE from music WHERE type = ‘Folk’;

Marge Hohly

16

17 Database Facts

Database Facts

Currently 20% of the world's data resides in relational RDBMSs. In the next two years, databases are expected to grow larger than 100 terabytes. A database this big would be able to store 100,000 copies of the Encyclopedia Britannica or 200,000 hours of music or about 10 billion web pages. The top 10 world's largest databases using the Oracle RDBMS are: France Telecom, 29.2TB -- a communications company (a TB is a terabyte equivalent to 1,000 gigabytes) Amazon.com with, 13 TB -- selling books and merchandise The Claria Corporation,12TB -- Internet behavioral marketing company tracking Internet user behavior

Marge Hohly

17

18 Oracle Facts

Oracle Facts

One of Oracles first customers was the CIA, for which they created a relational database management system. Much of Oracles early success was owed to picking up IBM as a major customer. 2nd half of the ‘90s was spend retooling Oracle products in line with that online strategy (the internet) Ellison said in 1998: “If the internet turns out not to be the future of computing, we’re toast. But if it is, we’re golden.” Oracle was the first enterprise software to fully embrace the Java Programming language back in 1998. Oracle has purchased 57 different companies in the last 5 years. The buying frenzy was launched with the $10.3 billion acquisition of PeopleSoft in 2005, which put Oracle squarely in the ERP software fray.

Marge Hohly

18

19 Oracle Facts

Oracle Facts

The latest mega-purchase: Sun Microsystems, a $7.4 billion acquisition due to close this year if regulators don't object. Oracle today now has 345,000 customers worldwide. Oracle employs more than 73,201 people around the globe. Some of the top 10 world’s largest databases using Oracle RDBMS are: France Telecom, 29.2 TB – a communications company (a TB is a terebyte equivalent to 1,000 gigabytes) Amazon.com with, 13TB – selling books and merchandise The Clarian Corporation, 13TB – Internet behavioral marketing company tracking Internet user behavior

Marge Hohly

19

20 Application Express SQL editor

Application Express SQL editor

The SQL course will use the three following sets of database tables for examples and practice exercises. Oracle tables: COUNTRIES, REGIONS, DEPARTMENTS, EMPLOYEES, JOBS, JOB_HISTORY AND JOB_GRADES DJs on Demand database tables: D_CDS, D_PACKAGES, D_TYPES, D_THEMES, D_CLIENTS, D_VENUES, D_SONGS, D_TRACK_LISTINGS, D_PARTNERS, D_EVENTS, D_PLAY_LIST_ITEMS, D_JOB_ASSIGNMENTS Global Fast Foods database tables: F_CUSTOMERS, F_REGULAR_MENUS, F_PROMOTIONAL_MENUS, F_SHIFTS, F_STAFFS, F_FOOD_ITEMS, F_ORDERS, F_ORDER_LINES, F_SHIFT_ASSIGNMENTS Print out these tables for your reference when using the Application Express editor These tables are available on the Student Resource web page for this class

Marge Hohly

20

21 Review the tables

Review the tables

There are six properties of tables in a relational database: Property 1: Entries in columns are single-valued. Property 2: Entries in columns are of the same kind. Property 3: Each row is unique. Property 4: Sequence of columns is insignificant. Property 5: Sequence of rows is insignificant. Property 6: Each column has a unique name.

Marge Hohly

21

22 Categories of SQL Statements

Categories of SQL Statements

Data manipulation language (DML) statements Begin with INSERT, UPDATE, DELETE, or MERGE Used to modify the table by entering new rows, changing existing rows, or removing existing rows. Data definition language (DDL) statements set up, change, and remove data structures from the database. The keywords CREATE, ALTER, DROP, RENAME, and TRUNCATE begin DDL statements. Transaction control (TCL) statements are used to manage the changes made by DML statements. Changes to the data are executed using COMMIT, ROLLBACK, and SAVEPOINT. TCL changes can be grouped together into logical transactions. Data control language (DCL) keywords GRANT and REVOKE are used to give or remove access rights to the database and the structures within it.

Marge Hohly

22

23 Questions

Questions

The Fast Foods database consists of how many tables? ____ tables How is the F_SHIFTS table related to the F_STAFFS table? What are the names of the columns in the F_CUSTOMERS table? How many rows of data have been entered in the F_PROMOTIONAL_MENUS table? In the F_FOOD_ITEMS table, column _________ is a foreign-key column. What table and column is this key referencing? List the primary key to foreign key relationships required to go from the F_SHIFTS table to the F_REGULAR_MENUS table. Which table(s) contain null values?

Marge Hohly

23

24 KEYWORD, CLAUSE, STATEMENT

KEYWORD, CLAUSE, STATEMENT

Throughout this course, the words keyword, clause, and statement are used as follows: A keyword refers to an individual SQL element. For example, SELECT and FROM are keywords. A clause is a part of a SQL statement. SELECT employee_id, last_name, .... is a clause. A statement is a combination of two or more clauses. SELECT * FROM employees; is a SQL statement.

Marge Hohly

24

25 Selection vs

Selection vs

Projection

10

John

Doe

4000

20

Jane

Jones

3000

30

Sylvia

Smith

5000

40

Hai

Nguyen

6000

SELECT salary FROM employees WHERE last_name like ‘Smith’; Selection (row) Projection (column)

ID

First_name

Last_name

salary

Marge Hohly

25

26 Join

Join

Marge Hohly

26

27 SELECT statement

SELECT statement

SELECT statements can provide the same information depending on how they are written Example: SELECT * FROM d_songs; SELECT id, title, duration, artist, type_code FROM d_songs;

Marge Hohly

27

28 SELECTION

SELECTION

SELECT * FROM employees WHERE department_id = 60; SELECT * FROM employees WHERE salary > 10000;

Marge Hohly

28

29 Projections

Projections

A subset of columns SELECT first_name, last_name, salary FROM employees; SELECT id, title, artist FROM d_songs;

Marge Hohly

29

30 Arithmetic Expressions

Arithmetic Expressions

Create expressions with number and date data by using arithmetic operators.

Operator

Description

+

Add

-

Subtract

*

Multiply

/

Divide

Marge Hohly

30

31 Operator Precedence

Operator Precedence

Operator Precedence Multiplication and division take priority over addition and subtraction. Operators of the same priority are evaluated from left to right. Parentheses are used to force prioritized evaluation and to clarify statements. Remember: Please excuse my dear aunt Sally

() ^ * / + -

Marge Hohly

31

32 Rewrite in order of precedence

Rewrite in order of precedence

You want to calculate the annual salary if the employee received $100 raise each month Revise the following statement in the correct order of precedence to get the desired results Run each example in the editor SELECT last_name, salary, 12*salary+100 FROM employees;

Marge Hohly

32

33 What is null

What is null

If a row lacks the data value for a particular column, that value is said to be null, or to contain a null. A null is a value that is unavailable, unassigned, unknown, or inapplicable. A null is not the same as zero. Zero is a number. A null is not a space. Space is a character.

Marge Hohly

33

34 Null Values in Arithmetic Expressions

Null Values in Arithmetic Expressions

SELECT last_name, salary, commission_pct, salary*commission_pct FROM employees;

2500

(null)

(null)

10500

.2

2100

11000

.3

3300

8600

.2

1720

7000

.15

1050

(null)

(null)

(null)

SALARY

COMMISSION_PCT

SALARY*COMMISSION_PCT

Marge Hohly

34

35 Practice

Practice

SELECT * FROM d_songs; SELECT id, title, duration, artist, type_code FROM d_songs; SELECT id, title, artist, FROM d_songs; SELECT last_name, salary, salary – 300 FROM employees; SELECT last_name, salary, salary * 1.05 FROM employees;

Marge Hohly

35

36 Column Alias

Column Alias

Renames a column heading Is useful in naming columns of derived values Immediately follow the column name Uses optional AS keyword between the column name and alias Required double quotation marks if it contains spaces or special characters or is case sensitive

Marge Hohly

36

37 Using Aliases

Using Aliases

NAME

SALARY

Annual Salary

Whalen

4400

52800

Hartstein

13000

156000

Fay

6000

72000

SELECT last_name name, salary AS Salary, salary*12 “Annual Salary” FROM employees;

Marge Hohly

37

«Database Design»
http://900igr.net/prezentacija/ekonomika/database-design-167747.html
cсылка на страницу
Урок

Экономика

125 тем
Слайды