Teste

In [1]:

#!pip install ipython-sql

#!pip install ipython-sql

In [2]:

%load_ext sql

%load_ext sql

In [3]:

%sql sqlite:///contatos.db

%sql sqlite:///contatos.db

In [4]:

%%sql

CREATE TABLE CLIENTES (
    CLIENTE_ID INTEGER PRIMARY KEY,
    NOME TEXT NOT NULL,
    EMAIL TEXT NOT NULL UNIQUE,
    TELEFONE TEXT NOT NULL UNIQUE
);

%%sql CREATE TABLE CLIENTES ( CLIENTE_ID INTEGER PRIMARY KEY, NOME TEXT NOT NULL, EMAIL TEXT NOT NULL UNIQUE, TELEFONE TEXT NOT NULL UNIQUE );

 * sqlite:///contatos.db
(sqlite3.OperationalError) table CLIENTES already exists
[SQL: CREATE TABLE CLIENTES (
    CLIENTE_ID INTEGER PRIMARY KEY,
    NOME TEXT NOT NULL,
    EMAIL TEXT NOT NULL UNIQUE,
    TELEFONE TEXT NOT NULL UNIQUE
);]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

In [5]:

%%sql

INSERT INTO CLIENTES (NOME, EMAIL, TELEFONE) 
VALUES
    ('Leonardo' , 'leonardo@gmail.com', '9999-9999' ),
    ('Rafael' , 'rafael@gmail.com', '9889-8998' ),
    ('Michelangelo' , 'michelangelo@gmail.com', '9997-7999' ),
    ('Donatello' , 'donatello@gmail.com', '9777-7779' );

%%sql INSERT INTO CLIENTES (NOME, EMAIL, TELEFONE) VALUES ('Leonardo' , 'leonardo@gmail.com', '9999-9999' ), ('Rafael' , 'rafael@gmail.com', '9889-8998' ), ('Michelangelo' , 'michelangelo@gmail.com', '9997-7999' ), ('Donatello' , 'donatello@gmail.com', '9777-7779' );

 * sqlite:///contatos.db
(sqlite3.IntegrityError) UNIQUE constraint failed: CLIENTES.TELEFONE
[SQL: INSERT INTO CLIENTES (NOME, EMAIL, TELEFONE) 
VALUES
    ('Leonardo' , 'leonardo@gmail.com', '9999-9999' ),
    ('Rafael' , 'rafael@gmail.com', '9889-8998' ),
    ('Michelangelo' , 'michelangelo@gmail.com', '9997-7999' ),
    ('Donatello' , 'donatello@gmail.com', '9777-7779' );]
(Background on this error at: https://sqlalche.me/e/20/gkpj)

In [6]:

%%sql

INSERT INTO CLIENTES (NOME, EMAIL, TELEFONE) 
VALUES ('Leonardo' , 'leo@gmail.com', '1999-9999' )

%%sql INSERT INTO CLIENTES (NOME, EMAIL, TELEFONE) VALUES ('Leonardo' , 'leo@gmail.com', '1999-9999' )

 * sqlite:///contatos.db
(sqlite3.IntegrityError) UNIQUE constraint failed: CLIENTES.TELEFONE
[SQL: INSERT INTO CLIENTES (NOME, EMAIL, TELEFONE) 
VALUES ('Leonardo' , 'leo@gmail.com', '1999-9999' )]
(Background on this error at: https://sqlalche.me/e/20/gkpj)

In [7]:

%%sql

SELECT * FROM CLIENTES;

%%sql SELECT * FROM CLIENTES;

 * sqlite:///contatos.db
Done.

Out[7]:

CLIENTE_ID	NOME	EMAIL	TELEFONE	AGE	DURATION
1	Leonardo	leonardo@gmail.com	9999-9999	None	None
2	Rafael	rafael@gmail.com	9889-8998	None	None
3	Michelangelo	michelangelo@gmail.com	9997-7999	None	None
4	Donatello	donatello@gmail.com	9777-7779	None	None
5	Leonardo	leo@gmail.com	1999-9999	None	None

In [8]:

%%sql

SELECT * FROM CLIENTES WHERE NOME = 'Leonardo';

%%sql SELECT * FROM CLIENTES WHERE NOME = 'Leonardo';

 * sqlite:///contatos.db
Done.

Out[8]:

CLIENTE_ID	NOME	EMAIL	TELEFONE	AGE	DURATION
1	Leonardo	leonardo@gmail.com	9999-9999	None	None
5	Leonardo	leo@gmail.com	1999-9999	None	None

In [9]:

%%sql

SELECT DISTINCT NOME FROM CLIENTES

%%sql SELECT DISTINCT NOME FROM CLIENTES

 * sqlite:///contatos.db
Done.

Out[9]:

NOME
Leonardo
Rafael
Michelangelo
Donatello

In [10]:

%%sql 

SELECT * from clientes where nome = 'Leonardo' and email = 'leo@gmail.com'

%%sql SELECT * from clientes where nome = 'Leonardo' and email = 'leo@gmail.com'

 * sqlite:///contatos.db
Done.

Out[10]:

CLIENTE_ID	NOME	EMAIL	TELEFONE	AGE	DURATION
5	Leonardo	leo@gmail.com	1999-9999	None	None

In [11]:

%%sql

select * from clientes ORDER BY NOME DESC

%%sql select * from clientes ORDER BY NOME DESC

 * sqlite:///contatos.db
Done.

Out[11]:

CLIENTE_ID	NOME	EMAIL	TELEFONE	AGE	DURATION
2	Rafael	rafael@gmail.com	9889-8998	None	None
3	Michelangelo	michelangelo@gmail.com	9997-7999	None	None
1	Leonardo	leonardo@gmail.com	9999-9999	None	None
5	Leonardo	leo@gmail.com	1999-9999	None	None
4	Donatello	donatello@gmail.com	9777-7779	None	None

In [12]:

%%sql

select count(*) from clientes
select sum(TELEFONE) from clientes
select avg(TELEFONE) from clientes

%%sql select count(*) from clientes select sum(TELEFONE) from clientes select avg(TELEFONE) from clientes

 * sqlite:///contatos.db
(sqlite3.OperationalError) near "select": syntax error
[SQL: select count(*) from clientes
select sum(TELEFONE) from clientes
select avg(TELEFONE) from clientes]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

In [13]:

%%sql

ALTER TABLE CLIENTES add DURATION;

%%sql ALTER TABLE CLIENTES add DURATION;

 * sqlite:///contatos.db
(sqlite3.OperationalError) duplicate column name: DURATION
[SQL: ALTER TABLE CLIENTES add DURATION;]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

In [14]:

%%sql

delete from clientes

%%sql delete from clientes

 * sqlite:///contatos.db
5 rows affected.

Out[14]:

[]