Create table vendas ( id_nf int not null, id_item int not null, cod_prod int not null, valor_unit float not null, quantidade int not null, desconto float); INSERT INTO vendas VALUES (1,1,1,25.00,10,5), (1,2,2,13.50,3,0), (1,3,3,15.00,2,0), (1,4,4,10.00,1,0), (1,5,5,30.00,1,0), (2,1,3,15.00,4,0), (2,2,4,10.00,5,0), (2,3,5,30.00,7,0), (3,1,1,25.00,5,0), (3,2,4,10.00,4,0), (3,3,5,30.00,5,0), (3,4,2,13.50,7,0), (4,1,5,30.00,10,15), (4,2,4,10.00,12,5), (4,3,1,25.00,13,5), (4,4,2,13.50,15,5), (5,1,3,15.00,3,0), (5,2,5,30.00,6,0), (6,1,1,25.00,22,15), (6,2,3,15.00,25,20), (7,1,1,25.00,10,3), (7,2,2,13.50,10,4), (7,3,3,15.00,10,4), (7,4,5,30.00,10,1); select id_nf,id_item,cod_prod,valor_unit,desconto from vendas where desconto=0; alter table vendas add valor_vendido float; update vendas set valor_vendido= valor_vendido-(valor_vendido*(desconto/100)); select id_nf, id_item, cod_prod,valor_unit,valor_vendido from vendas where desconto <>0; alter table vendas add valor_total float; update vendas set valor_total=quantidade*valor_unit; select id_nf,id_item,cod_prod,valor_unit,valor_vendido from vendas where (valor_unit*(desconto/100)); select id_nf, valor_total from vendas order by valor_total desc; select id_nf, valor_vendido from vendas order by valor_total desc; select cod_prod, max(quantidade)as quantidade from vendas where quantidade <>0; select id_nf, cod_prod, quantidade from vendas where quantidade >10 group by id_nf, cod_prod; select id_nf, valor_total from vendas where valor_total>500; order by id_nf desc;
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.
MySQL is a open-source, free and very popular relational database management system which is developed, distributed and supported by Oracle corporation.
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
....);
CREATE TABLE EMPLOYEE (
empId INTEGER PRIMARY KEY,
name TEXT NOT NULL,
dept TEXT NOT NULL
);
ALTER TABLE Table_name ADD column_name datatype;
INSERT INTO EMPLOYEE VALUES (0001, 'Dave', 'Sales');
TRUNCATE table table_name;
DROP TABLE table_name;
RENAME TABLE table_name1 to new_table_name1;
--Line1;
/* Line1,
Line2 */
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
Note: Column names are optional.
INSERT INTO EMPLOYEE VALUES (0001, 'Ava', 'Sales');
SELECT column1, column2, ...
FROM table_name
[where condition];
SELECT * FROM EMPLOYEE where dept ='sales';
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
UPDATE EMPLOYEE SET dept = 'Sales' WHERE empId='0001';
DELETE FROM table_name where condition;
DELETE from EMPLOYEE where empId='0001';
CREATE INDEX index_name on table_name(column_name);
CREATE UNIQUE INDEX index_name on table_name(column_name);
DROP INDEX index_name ON table_name;
Creating a View:
CREATE VIEW View_name AS
Query;
SELECT * FROM View_name;
ALTER View View_name AS
Query;
DROP VIEW View_name;
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 } */
DROP TRIGGER [IF EXISTS] trigger_name;
CREATE PROCEDURE sp_name(p1 datatype)
BEGIN
/*Stored procedure code*/
END;
CALL sp_name;
DROP PROCEDURE sp_name;
SELECT * FROM TABLE1 INNER JOIN TABLE2 where condition;
SELECT * FROM TABLE1 LEFT JOIN TABLE2 ON condition;
SELECT * FROM TABLE1 RIGHT JOIN TABLE2 ON condition;
SELECT select_list from TABLE1 CROSS JOIN TABLE2;