1CREATE TABLE ot.persons(
2 person_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
3 first_name VARCHAR2(50) NOT NULL,
4 last_name VARCHAR2(50) NOT NULL,
5 PRIMARY KEY(person_id)
6);
1/*Deleting whole table with data */
2DROP TABLE vehicles;
3DROP TABLE owners;
4
5/*First create only this table */
6CREATE TABLE owners (
7 owner_id NUMBER,
8 first_name VARCHAR2(50 CHAR) NOT NULL,
9
10 CONSTRAINT owners_pk PRIMARY KEY(owner_id)
11);
12
13/* One owner got many vehicles, one to many relation
14 or one vehicle got many owners */
15CREATE TABLE vehicles (
16 vehicle_id NUMBER,
17 vehicle_name VARCHAR2(50 CHAR) NOT NULL,
18
19 /* CONSTRAINT vehicle_pk PRIMARY KEY(vehicle_id) --If we had made PRIMARY KEY here it will be one to one relation */
20 CONSTRAINT vehicle_owner_fk FOREIGN KEY(vehicle_id) REFERENCES owners(owner_id) ON DELETE SET NULL
21);
22
23
24INSERT INTO owners VALUES(1, 'Abbi');
25INSERT INTO owners VALUES(2, 'Beatrix');
26INSERT INTO owners VALUES(3, 'Caila');
27INSERT INTO owners VALUES(4, 'Dea');
28INSERT INTO owners VALUES(5, 'Elise');
29
30INSERT INTO vehicles VALUES(1, 'car');
31INSERT INTO vehicles VALUES(2, 'motorcycle');
32INSERT INTO vehicles VALUES(3, 'airplane1');
33INSERT INTO vehicles VALUES(4, 'airplane1');
34INSERT INTO vehicles VALUES(4, 'steamer');
35INSERT INTO vehicles VALUES(4, 'submarine');
36COMMIT;
37
38DELETE FROM projects WHERE vehicle_id = 4 AND vehicle_name = 'submarine';
39
40/*
41Sometimes Oracle SQL Dev don't like this sign ';',
42Primary Key is always NOT NULL UNIQUE,
43single PK PRIMARY KEY(user_id), allowed combination:
44(1,1) for example (user_id, project_id)
45(2,1)
46(3,1)
47(4,2)
48
49*/
50-- one line comment
51
52
1CREATE TABLE utilisateur
2(
3 id INT PRIMARY KEY NOT NULL,
4 nom VARCHAR(100),
5 prenom VARCHAR(100),
6 email VARCHAR(255),
7 date_naissance DATE,
8 pays VARCHAR(255),
9 ville VARCHAR(255),
10 code_postal VARCHAR(5),
11 nombre_achat INT
12)