sql success ch02

1

SQL Success

Chapter 2 slides

Stéphane Faroult

2

ALPHA

3

Don Chamberlin with Ray Boyce (+ 1974)

3

4

select ...

5

select ... from ...

6

select ... from ... where ...

7

Michael Stonebraker

QUEL

8

Michael Stonebraker

QUEL

range of e is employee retrieve (comp = e.salary / (e.age - 18)) where e.name = "Jones"

10

Oates Ellison

Miner

11

components

12

CREATE

Data Definition Language

13

ALTER


14

DROP


15

id title country year_released

Data Manipulation Language

INSERT id title country year_released 1 Casablanca us 1942 2 Blade Runner us 1982 3 On The Waterfront us 1954 4 Lawrence Of Arabia uk 1962 5 Annie Hall us 1977 6 Goodfellas us 1990

16

id title country year_released


INSERT id title country year_released 1 Casablanca us 1942 2 Blade Runner us 1982 3 On The Waterfront us 1954 4 Lawrence Of Arabia uk 1962 5 Annie Hall us 1977 6 Goodfellas us 1990 7 The Third Man uk 1949 8 Citizen Kane us 1941 9 Bicycle Thieves it 1948 10 The Battleship Potemkin ru 1925 11 Sholay in 1975 12 A Better Tomorrow hk 1986

17

id title country year_released 1 Casablanca us 1942 2 Blade Runner us 1982 3 On The Waterfront us 1954 4 Lawrence Of Arabia uk 1962 5 Annie Hall us 1977 6 Goodfellas us 1990 7 The Third Man uk 1949 8 Citizen Kane us 1941 9 Bicycle Thieves it 1948 10 The Battleship Potemkin ru 1925 11 Sholay in 1975 12 A Better Tomorrow hk 1986


UPDATE id title country year_released 1 Casablanca us 1942 2 Blade Runner us 1982 3 On The Waterfront us 1954 4 Lawrence Of Arabia gb 1962 5 Annie Hall us 1977 6 Goodfellas us 1990 7 The Third Man gb 1949 8 Citizen Kane us 1941 9 Bicycle Thieves it 1948 10 The Battleship Potemkin ru 1925 11 Sholay in 1975 12 A Better Tomorrow hk 1986

18

DELETE id title country year_released 1 Casablanca us 1942 2 Blade Runner us 1982 3 On The Waterfront us 1954 4 Lawrence Of Arabia gb 1962 5 Annie Hall us 1977 6 Goodfellas us 1990 7 The Third Man gb 1949 8 Citizen Kane us 1941 9 Bicycle Thieves it 1948 10 The Battleship Potemkin ru 1925 11 Sholay in 1975 12 A Better Tomorrow hk 1986


id title country year_released 1 Casablanca us 1942 2 Blade Runner us 1982 3 On The Waterfront us 1954 4 Lawrence Of Arabia gb 1962 6 Goodfellas us 1990 7 The Third Man gb 1949 8 Citizen Kane us 1941 9 Bicycle Thieves it 1948 10 The Battleship Potemkin ru 1925 11 Sholay in 1975 12 A Better Tomorrow hk 1986

19

id title country year_released 1 Casablanca us 1942 2 Blade Runner us 1982 3 On The Waterfront us 1954 4 Lawrence Of Arabia gb 1962 6 Goodfellas us 1990 7 The Third Man gb 1949 8 Citizen Kane us 1941 9 Bicycle Thieves it 1948 10 The Battleship Potemkin ru 1925 11 Sholay in 1975 12 A Better Tomorrow hk 1986

SELECT

21

EMEA Survey, Evans Data, 2011

22


23


29

Flickr: Jeff Kramer

30

DBMS

Client

31

DBMS

Client

32

DBMS

33

DBMS

34

DBMS

35

DBMS

36

Client

37

FTP ECHO SSH 7 21 22

23 25

88

80

110 143

38


23 25

88

80

110 143

39


23 25

88

80

110 143

40


23 25

88

80

110 143

41

$ mysql --user=donald --host=atlas --port=3306

41

42

$ psql --username donald --host atlas --port 5432


42

43



$ sqlplus donald@//atlas:1521/ORCL

43

44

$ clpplus donald@//atlas:50000/DB2




44

45

$ clpplus donald@//atlas:50000/DB2


ATLAS\SQLEXPRESS

Database Engine

SQL Server Authentication



45

donald

46

create table table_name

47

( , , )

create table table_name

48

cReaTE tABle

( , , )

table_name

49

tablename

TABLENAME

tABleNamE

=

=

50

4ME

51

4ME

52

MY_TABLE2

4ME

53

MY_TABLE2

MY TABLE

4ME

54

MY_TABLE2

MY TABLE

4ME

55

( , , )

create table table_name column_name

56

( , , )

datatype create table table_name

column_name

57

Text

58

Text

Number

59

Text

Number

Date

60

Text

Number

Date

Binary

61

Text

Number

Date

Binary

62

Text datatypes

char(length)

char

63

Text datatypes

char(length)

char(1)

64

Text datatypes

char(length)

varchar(max length)

varchar2(max length)

65

Text datatypes

char(length)

varchar(max length)

clob

66

Number datatypes

int

67

Number datatypes

int

float

68

numeric

Number datatypes

int

float

number

69

numeric (precision)

Number datatypes

int

float

70

999999 99

numeric (precision)

Number datatypes

int

float

71

999999 99

numeric

Number datatypes

int

float

(precision, scale)

72

999999. 99

numeric

Number datatypes

int

float

(precision, scale)

73

Date datatypes

datetime

date

74

Date datatypes

datetime

timestamp

75

varbinary(max length)

Binary datatypes

76

varbinary(max length)

Binary datatypes

blob

77

create table people (

)

peopleid number, first_name varchar2(30), surname varchar2(30), born number(4),

died number(4)

78

create table people (

)

peopleid int, first_name varchar(30), surname varchar(30), born numeric(4),

died numeric(4)

79 Flickr:Daniel Moyle

80

create table people (peopleid int first_name varchar(30), surname varchar(30) born numeric(4),

died numeric(4))

,

,

81


died numeric(4))

,

not null,

82


died numeric(4))

not null,

not null,

83


died numeric(4))

not null,

not null,

84


died numeric(4))

not null,

not null,

87

comment on column people.surname is 'Surname or stage name';

-- comments in an SQL statement start with a double dash

88



89



90

create table people (peopleid int not null, first_name varchar(30), surname varchar(30) not null, born numeric(4),

died numeric(4))

45

91

No woman should ever be quite accurate about her age. It looks so calculating.

Oscar Wilde The Importance of Being Earnest.

92

create table people (peopleid int not null, first_name varchar(30), surname varchar(30) not null, born numeric(4)

died numeric(4)) not null, ,

93

Hepburn

Surname

Audrey

Firstname

4-May-1929

Birthdate Picture

94

Hepburn

Surname

Audrey

Firstname

4-May-1929

Birthdate Picture

Hepburn Audrey 4-May-1929

95

Hepburn

Surname

Audrey

Firstname

4-May-1929

Birthdate Picture

Hepburn Audrey 4-May-1929

98

CONSTRAINTS

99

first_name varchar(30), surname varchar(30) not null, born numeric(4) not null, died numeric(4))

create table people (peopleid int not null ,

100

first_name varchar(30), surname varchar(30) not null, born numeric(4) not null, died numeric(4))

primary key, create table people (peopleid int not null ,

101

create table people (peopleid int not null primary key, first_name varchar(30), surname varchar(30) not null, born numeric(4) not null, died numeric(4) )

102

, unique (first_name, surname))

create table people (peopleid int not null primary key, first_name varchar(30), surname varchar(30) not null, born numeric(4) not null, died numeric(4)

103

'audrey'

'AUDREY'

'Audrey'

For Oracle, PostgreSQL and DB2 ...

104

'audrey'

'AUDREY'

'Audrey'

For Oracle, PostgreSQL and DB2 ...

... not for SQL Server, MySQL or SQLite ...

105

create table people (peopleid int not null primary key, first_name varchar(30),

surname varchar(30) not null, born numeric(4) not null, died numeric(4), unique (first_name, surname))

106

create table people (peopleid int not null primary key, first_name varchar(30) surname varchar(30) not null born numeric(4) not null, died numeric(4), unique (first_name, surname))

, check (first_name = upper(first_name)),

,

check (surname = upper(surname)),

107

Chris Date

Picture: Douglas Robertson

108

Chris Date

Picture: Douglas Robertson

109

create table movies (movieid int not null primary key, title varchar(60) not null, country char(2) not null, year_released numeric(4) not null check(year_released >= 1895), unique (title, country, year_released))

110

COUNTRIES country_code country_name continent

United States us AMERICA

China cn ASIA

United Kingdom uk EUROPE

Brazil br AMERICA

India in ASIA

Italy it EUROPE

Mexico mx AMERICA

Russia ru EUROPE

Germany de EUROPE

111

COUNTRIES country_code country_name continent

United States us AMERICA

China cn ASIA

United Kingdom uk EUROPE

Brazil br AMERICA

India in ASIA

Italy it EUROPE

Mexico mx AMERICA

Russia ru EUROPE

Germany de EUROPE

112

create table movies (movieid int not null primary key, title varchar(60) not null, country char(2) not null, year_released numeric(4) not null check(year_released >= 1895), unique (title, country, year_released)

foreign key(country) references countries(country_code))

113

credits

114

movies

credits

115

movies people

credits

116

movies people

credits

118

PROGRAM

119

PROGRAM

120

Prepare the background

121

INSERT SOME_TABLE column1 column2

122


123


124

insert into table_name (list of columns) values (list of values)

125

insert into countries(country_code, country_name, continent) values('us', 'United States', 'AMERICA')

126

Hello World

Reminder

127

HELLO WORLD

Reminder

128

select * from countries

129

select * from countries

130

1960

131

insert into movies(movieid, title, country, year_released) values (123, 'L''Avventura', 'it', 1960)

132

insert into movies(movieid, title, country, year_released) values (123, 'L''Avventura', 'it', 1960)

''

133 Flickr: Jason Dgreat

Entering

a date?

134

'07/20/1969'

'MM/DD/YYYY'

to_date( ) ,

135

SYSDATE

136

CURDATE()

SYSDATE

137

SYSDATE

CURDATE()

GETDATE()

138 FLickr:Steve Jurvetson

139

SQL : THE database language.

140

SQL : THE database language. Connection : server, port, database, username, password.

141

SQL : THE database language. Connection : server, port, database, username, password. Create table: data types + constraints.

142

SQL : THE database language. Connection : server, port, database, username, password. Create table: data types + constraints.

Keywords and identifiers: not case-sensitive Data : can be CASE-SENSITIVE

sql success ch02

Education

table table

host atlas

username donald

number datatypes int66

table people peopleid

sqlplus donald

number datatypes int

clpplus donald