 
-- create
CREATE TABLE LOGEMENT (
  idLogement INTEGER PRIMARY KEY,
  nomProprietaire TEXT NOT NULL,
  types TEXT NOT NULL,
  nbPieces int NOT NULL,
  surface float NOT NULL,
  etat TEXT NOT NULL,
  objectif TEXT NOT NULL,
  prix float,
  dateDispo DATE
);

CREATE TABLE TRANSACTION (
  idTransaction INTEGER PRIMARY KEY,
  tauxFixe float NOT NULL,
  tauxVariable float NOT NULL
);

CREATE TABLE ADRESSE (
  idAdresse INTEGER PRIMARY KEY,
  rue TEXT NOT NULL,
  numero INTEGER NOT NULL,
  codePostal INTEGER NOT NULL,
  ville TEXT NOT NULL
);

CREATE TABLE VISITE (
  idVisite INTEGER PRIMARY KEY,
  date DATE NOT NULL,
  heureDebut timestamp NOT NULL,
  heureFin timestamp NOT NULL
);

CREATE TABLE CLIENT (
  nom varchar(50) PRIMARY KEY
);

CREATE TABLE GARAGE (
  idGarage INTEGER PRIMARY KEY
);

CREATE TABLE ACHAT (
  idAchat INTEGER PRIMARY KEY,
  dateAchat DATE NOT NULL
);

CREATE TABLE LOCATION (
  idLocation INTEGER PRIMARY KEY,
  dureeLocation INTEGER NOT NULL
);


INSERT INTO LOGEMENT VALUES (1, 'Dupont', 'Maison', 13, 300, 'Bon', 'Vente', 1200000, '2022-02-06');
INSERT INTO LOGEMENT VALUES (2, 'Pommier', 'Maison', 11, 200, 'Bon', 'Vente', 700000, '2022-02-06');
INSERT INTO LOGEMENT VALUES (3, 'Cerisier', 'Maison', 9, 190, 'Bon', 'Vente', 600000, '2022-02-06');
INSERT INTO LOGEMENT VALUES (4, 'Bananier', 'Maison', 8, 180, 'Bon', 'Vente', 550000, '2022-02-06');
INSERT INTO LOGEMENT VALUES (5, 'Pigny', 'Maison', 8, 165, 'Bon', 'Vente', 500000, '2022-02-06');
INSERT INTO LOGEMENT VALUES (6, 'Mouffron', 'Maison', 7, 135, 'Bon', 'Vente', 470000, '2022-02-06');
INSERT INTO LOGEMENT VALUES (7, 'Meunier', 'Maison', 7, 130, 'Bon', 'Vente', 450000, '2022-02-06');
INSERT INTO LOGEMENT VALUES (8, 'Paskevych', 'Maison', 7, 128, 'Bon', 'Vente', 450000, '2022-02-06');
INSERT INTO LOGEMENT VALUES (9, 'Lacour', 'Maison', 7, 122, 'Bon', 'Vente', 435000, '2022-02-06');
INSERT INTO LOGEMENT VALUES (10, 'Duchesnes', 'Maison', 6, 120, 'Bon', 'Vente', 425000, '2022-02-06');
INSERT INTO LOGEMENT VALUES (11, 'Tchenquela', 'Maison', 6, 115, 'Bon', 'Vente', 395000, '2022-02-06');
INSERT INTO LOGEMENT VALUES (12, 'Jourdain', 'Maison', 6, 110, 'Bon', 'Vente', 385000, '2022-02-06');
INSERT INTO LOGEMENT VALUES (13, 'Chihi', 'Maison', 5, 105, 'Bon', 'Vente', 380000, '2022-02-06');
INSERT INTO LOGEMENT VALUES (14, 'Leducq', 'Maison', 5, 100, 'Bon', 'Vente', 370000, '2022-02-06');
INSERT INTO LOGEMENT VALUES (15, 'Sing', 'Maison', 5, 95, 'Bon', 'Vente', 360000, '2022-02-06');
INSERT INTO LOGEMENT VALUES (16, 'Randrianasolo', 'Maison', 5, 95, 'Bon', 'Vente', 330000, '2022-02-06');
INSERT INTO LOGEMENT VALUES (17, 'Diallo', 'Maison', 5, 85, 'Bon', 'Vente', 315000, '2022-02-06');
INSERT INTO LOGEMENT VALUES (18, 'Berhe', 'Maison', 5, 80, 'Bon', 'Vente', 300000, '2022-02-06');
INSERT INTO LOGEMENT VALUES (19, 'Baur', 'Maison', 5, 80, 'Bon', 'Vente', 285000, '2022-02-06');
INSERT INTO LOGEMENT VALUES (20, 'Stajic', 'Maison', 5, 75, 'Bon', 'Vente', 275000, '2022-02-06');
INSERT INTO LOGEMENT VALUES (21, 'Pommier', 'Maison', 5, 120, 'Très Bon', 'Location', 1500, '2022-02-06');
INSERT INTO LOGEMENT VALUES (22, 'Cerisier', 'Maison', 5, 120, 'Bon', 'Location', 1700, '2022-02-06');
INSERT INTO LOGEMENT VALUES (23, 'Poirier', 'Maison', 11, 180, 'Bon', 'Location', 2200, '2022-02-06');
INSERT INTO LOGEMENT VALUES (24, 'Dubois', 'Maison', 7, 115, 'Très Bon', 'Location', 2500, '2022-02-06');
INSERT INTO LOGEMENT VALUES (25, 'Debré', 'Maison', 6, 120, 'Bon', 'Location', 1900, '2022-02-06');
INSERT INTO LOGEMENT VALUES (26, 'Duchamp', 'Maison', 5, 100, 'Moyen', 'Location', 1300, '2022-02-06');
INSERT INTO LOGEMENT VALUES (27, 'Jacques', 'Maison', 5, 120, 'Bon', 'Location', 1400, '2022-02-06');
INSERT INTO LOGEMENT VALUES (28, 'Dupont', 'Maison', 5, 105, 'Mauvais', 'Location', 1500, '2022-02-06');
INSERT INTO LOGEMENT VALUES (29, 'Dupont', 'Appartement', 3, 120, 'Bon', 'Location', 600, '2022-02-06');
INSERT INTO LOGEMENT VALUES (30, 'Martin', 'Appartement', 4, 120, 'Bon', 'Location', 800, '2022-02-06');
INSERT INTO LOGEMENT VALUES (31, 'Mbappe', 'Appartement', 6, 120, 'Bon', 'Location', 900, '2022-02-06');
INSERT INTO LOGEMENT VALUES (32, 'Mouffron', 'Appartement', 6, 120, 'Bon', 'Location', 1200, '2022-02-06');
INSERT INTO LOGEMENT VALUES (33, 'Pigny', 'Appartement', 2, 120, 'Bon', 'Location', 450, '2022-02-06');
INSERT INTO LOGEMENT VALUES (34, 'Kabambi', 'Appartement', 2, 120, 'Bon', 'Location', 500, '2022-02-06');
INSERT INTO LOGEMENT VALUES (35, 'Kamara', 'Appartement', 2, 120, 'Bon', 'Location', 550, '2022-02-06');
INSERT INTO LOGEMENT VALUES (36, 'Macron', 'Appartement', 3, 40, 'Très Bon', 'Vente', 180000, '2022-02-06');
INSERT INTO LOGEMENT VALUES (37, 'Trump', 'Appartement', 4, 50, 'Bon', 'Vente', 210000, '2022-02-06');
INSERT INTO LOGEMENT VALUES (38, 'Obama', 'Appartement', 5, 70, 'Moyen', 'Vente', 240000, '2022-02-06');
INSERT INTO LOGEMENT VALUES (39, 'Dupont', 'Appartement', 6, 80, 'Mauvais', 'Vente', 255000, '2022-02-06');
INSERT INTO LOGEMENT VALUES (40, 'Borne', 'Appartement', 7, 90, 'Moyen', 'Vente', 258000, '2022-02-06');
INSERT INTO LOGEMENT VALUES (41, 'Dupont', 'Appartement', 9, 120, 'Très Bon', 'Vente', 259000, '2022-02-06');

INSERT INTO ADRESSE VALUES (1, 'rue du centre', 33, '91300', 'Massy');
INSERT INTO ADRESSE VALUES (2, 'rue victor hugo', 28, '91300', 'Palaiseau');
INSERT INTO ADRESSE VALUES (3, 'rue de paris', 95, '91400', 'Orsay');
INSERT INTO ADRESSE VALUES (4, 'rue marie curie', 128, '91300', 'Villejuif');
INSERT INTO ADRESSE VALUES (5, 'rue emile zola', 2, '75000', 'Paris');
INSERT INTO ADRESSE VALUES (6, 'avenue des lilas', 35, '75000', 'Paris');
INSERT INTO ADRESSE VALUES (7, 'rue de monod', 24, '75000', 'Paris');
INSERT INTO ADRESSE VALUES (8, 'rue charles baudelaire', 18, '75000', 'Paris');
INSERT INTO ADRESSE VALUES (9, 'rue du nord', 19, '75000', 'Paris');
INSERT INTO ADRESSE VALUES (10, 'rue du sud', 98, '75000', 'Paris');
INSERT INTO ADRESSE VALUES (11, 'rue de gaulle', 25, '75000', 'Paris');
INSERT INTO ADRESSE VALUES (12, 'rue jean moulin', 34, '75000', 'Paris');
INSERT INTO ADRESSE VALUES (13, 'rue mitterand', 78, '75000', 'Paris');
INSERT INTO ADRESSE VALUES (14, 'rue pompidou', 67, '91120', 'Palaiseau');
INSERT INTO ADRESSE VALUES (15, 'rue chirac', 56, '91120', 'Palaiseau');
INSERT INTO ADRESSE VALUES (16, 'rue elisabeth II', 58, '91120', 'Palaiseau');
INSERT INTO ADRESSE VALUES (17, 'rue du bourg', 59, '91120', 'Palaiseau');
INSERT INTO ADRESSE VALUES (18, 'rue des cigales', 12, '91120', 'Palaiseau');
INSERT INTO ADRESSE VALUES (19, 'rue des moineaux', 18, '91400', 'Orsay');
INSERT INTO ADRESSE VALUES (20, 'rue des paquerettes', 9, '91400', 'Orsay');
INSERT INTO ADRESSE VALUES (21, 'rue des capucines', 65, '91400', 'Orsay');
INSERT INTO ADRESSE VALUES (22, 'rue de la foret', 24, '91400', 'Orsay');
INSERT INTO ADRESSE VALUES (23, 'impasse', 79, '91400', 'Orsay');
INSERT INTO ADRESSE VALUES (24, 'rue des champs', 45, '91400', 'Orsay');
INSERT INTO ADRESSE VALUES (25, 'rue alesia', 3, '91400', 'Orsay');
INSERT INTO ADRESSE VALUES (26, 'rue de neuilly', 4, '91400', 'Orsay');
INSERT INTO ADRESSE VALUES (27, 'rue de pantin', 6, '91400', 'Orsay');
INSERT INTO ADRESSE VALUES (28, 'rue de montreuil', 61, '91400', 'Orsay');
INSERT INTO ADRESSE VALUES (29, 'rue de vincennes', 49, '91400', 'Orsay');
INSERT INTO ADRESSE VALUES (30, 'rue de vitry', 74, '94800', 'Villejuif');
INSERT INTO ADRESSE VALUES (31, 'rue de villejuif', 77, '94800', 'Villejuif');
INSERT INTO ADRESSE VALUES (32, 'rue de gorki', 58, '94800', 'Villejuif');
INSERT INTO ADRESSE VALUES (33, 'rue de rungis', 65, '94800', 'Villejuif');
INSERT INTO ADRESSE VALUES (34, 'rue de massy', 45, '94800', 'Villejuif');
INSERT INTO ADRESSE VALUES (35, 'rue leonard de vinci', 28, '94800', 'Villejuif');
INSERT INTO ADRESSE VALUES (36, 'rue de saint exupery', 98, '94800', 'Villejuif');
INSERT INTO ADRESSE VALUES (37, 'rue de pasteur', 94, '94800', 'Villejuif');
INSERT INTO ADRESSE VALUES (38, 'rue napoleon', 45, '94800', 'Villejuif');
INSERT INTO ADRESSE VALUES (39, 'rue louis XIV', 75, '94800', 'Villejuif');
INSERT INTO ADRESSE VALUES (40, 'rue jeanne darc', 89, '94800', 'Villejuif');
INSERT INTO ADRESSE VALUES (41, 'boulevard des champs', 84, '94800', 'Villejuif');

select * from ADRESSE

created 2 years ago

MySQL online editor

Write, Run & Share MySQL queries online using OneCompiler's MySQL online editor and compiler for free. It's one of the robust, feature-rich online editor and compiler for MySQL. Getting started with the OneCompiler's MySQL editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose language as 'MySQL' and start writing queries to learn and test online without worrying about tedious process of installation.

About MySQL

MySQL is a open-source, free and very popular relational database management system which is developed, distributed and supported by Oracle corporation.

Key Features:

Open-source relational database management systems.
Reliable, very fast and easy to use database server.
Works on client-server model.
Highly Secure and Scalable
High Performance
High productivity as it uses stored procedures, triggers, views to write a highly productive code.
Supports large databases efficiently.
Supports many operating systems like Linux*,CentOS*, Solaris*,Ubuntu*,Windows*, MacOS*,FreeBSD* and others.

Syntax help

Commands

1. CREATE

CREATE TABLE table_name (
                column1 datatype,
                column2 datatype,
                ....);

Example

CREATE TABLE EMPLOYEE (
  empId INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL
);

2. ALTER

ALTER TABLE Table_name ADD column_name datatype;

Example

INSERT INTO EMPLOYEE VALUES (0001, 'Dave', 'Sales');

3. TRUNCATE

TRUNCATE table table_name;

4. DROP

DROP TABLE table_name;

5. RENAME

RENAME TABLE table_name1 to new_table_name1;

6. COMMENT

Single-Line Comments:

 --Line1;

Multi-Line comments:

   /* Line1,
   Line2 */

DML Commands

1. INSERT

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

Note: Column names are optional.

Example

INSERT INTO EMPLOYEE VALUES (0001, 'Ava', 'Sales');

2. SELECT

SELECT column1, column2, ...
FROM table_name
[where condition];

Example

SELECT * FROM EMPLOYEE where dept ='sales';

3. UPDATE

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example

UPDATE EMPLOYEE SET dept = 'Sales' WHERE empId='0001';

4. DELETE

DELETE FROM table_name where condition;

Example

DELETE from EMPLOYEE where empId='0001';

Indexes

1. CREATE INDEX

  CREATE INDEX index_name on table_name(column_name);

To Create Unique index:

  CREATE UNIQUE INDEX index_name on table_name(column_name);

2. DROP INDEX

DROP INDEX index_name ON table_name;

Views

1. Create a View

Creating a View:
CREATE VIEW View_name AS 
Query;

2. How to call view

SELECT * FROM View_name;

3. Altering a View

ALTER View View_name AS 
Query;

4. Deleting a View

DROP VIEW View_name;

Triggers

1. Create a Trigger

CREATE TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW [trigger_order] trigger_body
/* where
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
trigger_order: { FOLLOWS | PRECEDES } */

2. Drop a Trigger

DROP TRIGGER [IF EXISTS] trigger_name;

Stored Procedures

1. Create a Stored Procedure

CREATE PROCEDURE sp_name(p1 datatype)
BEGIN
/*Stored procedure code*/
END;

2. How to call Stored procedure

CALL sp_name;

3. How to delete stored procedure

DROP PROCEDURE sp_name;

Joins

1. INNER JOIN

SELECT * FROM TABLE1 INNER JOIN TABLE2 where condition;

2. LEFT JOIN

SELECT * FROM TABLE1 LEFT JOIN TABLE2 ON condition;

3. RIGHT JOIN

SELECT * FROM TABLE1 RIGHT JOIN TABLE2 ON condition;

4. CROSS JOIN

SELECT select_list from TABLE1 CROSS JOIN TABLE2;