-- Orders ---------------------------------------------------
CREATE TABLE Orders(
OrderID int NOT NULL Primary KEY,
CustomerID int NULL,
EmployeeID int NULL,
OrderDate datetime NULL ,
ShipperID int NULL ,
n1 INTEGER,n3 INTEGER,n2 INTEGER);
INSERT INTO Orders VALUES
(10306,6,1,'1996-03-12',3,12,12,1996),(
10251,8,2,'1996-01-12',1,1,11,1996),(
10264,2,1,'1996-04-12',3,4,11,1996),(
10269,8,1,'1996-05-12',1,5,11,1996),(
10274,5,2,'1996-06-12',1,6,11,1996),(
10278,5,1,'1996-07-12',2,7,11,1996),(
10283,10,4,'1996-08-12',3,8,11,1996),(
10298,7,2,'1996-12-12',2,11,11,1996),(
10299,7,4,'1996-12-12',2,11,11,1996),(
10304,8,1,'1996-12-12',2,12,11,1996)
;
-- SHIPPERS ---------------------------------------------------
CREATE TABLE Shippers(
ShipperID int NOT NULL Primary KEY,
ShipperName nvarchar(255) NULL,
Phone nvarchar(250) NOT NULL );
insert into Shippers values (1,'Speedy Express','(503) 555-9831)'),
(2,'United Package',' (503) 555-3199)'),
(3,'Federal Shipping','(503) 555-9931)');
-- employees ---------------------------------------------------
CREATE TABLE Employees(
EmployeeID int NOT NULL Primary KEY,
LastName nvarchar(255) NULL,
FirstName nvarchar(255) NULL,
BirthDate datetime NULL,
Photo nvarchar(255) NULL,
Notes text NULL );
insert into Employees values(1, 'Davolio','Nancy' ,'1968-2-08', 'EmpID.pic','Education includes a BA in psychology from Colorado State University. She also complete'),
(2,'Fuller','Andrew','1952-02-9', 'EmpID2.pic','Andrew received his BTS commercial and a Ph.D. in international marketing from the University of Dallas. He is fluent in French and Italian and reads German. He joined the company as a sales representative'),
(4,'Peacock','Margaret','1958-09-9', 'EmpID4.pic','Margaret holds a BA in English literature from Concordia College and an MA from the American Institute of Culinary Arts. She was temporarily assigned to the London office before returning to her permanent post in Seattle.'),
(5,'Buchanan','Steven' ,'1955-03-04','EmpID5.pic','Steven Buchanan graduated from St. Andrews University, Scotland, with a BSC degree. Upon joining the company as a sales representative, he spent 6 months in an orientation program at the Seattle office and then returned to his permanent post in London, where he was promoted to sales manager. Mr. Buchanan has completed the courses ' );
-- Customers ---------------------------------------------------
CREATE TABLE Customers(
CustomerID INTEGER NOT NULL Primary KEY,
CustomerName nvarchar(255) NULL,
ContactName nvarchar(255) NULL,
Address nvarchar(255) NULL,
City nvarchar(255) NULL,
PostalCode nvarchar(255) NULL,
Country nvarchar(255) NULL);
-- INSERT
INSERT INTO Customers VALUES
('1','Alfreds Futterkiste','Maria Anders','Obere tr.57','Berlin','12209','Germany')
,('2','Ana Trujillo Emparedados y helados','Ana Trujillo','Avda. de la Constitucin 2222','Mxico D.F.','5021','Mexico')
,('3','Antonio Moreno Taquera','Antonio Moreno','Mataderos 2312','Mxico D.F.','5023','Mexico')
,('4','Cactus Comidas para llevar','Patricio Simpson','Cerrito 333','Buenos Aires','1010','Argentina')
,('5','Centro comercial Moctezuma','Francisco Chang','Sierras de Granada 9993','Mxico D.F.','5022','Mexico')
,('6','Chop-suey Chinese','Yang Wang','Hauptstr. 29','Bern','3012','Switzerland')
,('7','Drachenblut Delikatessend','Sven Ottlieb','Walserweg 21','Aachen','52066','Germany')
,('8','Du monde entier','Janine Labrune',' rue des Cinquante Otages"','Nantes','44000','France')
,('9','Eastern Connection','Ann Devon','35 King George','London','WX3 6FW','UK')
,('10','Ernst Handel','Roland Mendel','Kirchgasse 6','Graz','8010','Austria')
,('11','Familia Arquibaldo','Aria Cruz','"Rua Ors92','So Paulo','05442-030','Brazil')
,('12','FISSA Fabrica Inter. alchichas S.A.','Diego Roel',' Moralzarzal86','Madrid','28034','Spain')
,('13','Folies gourmandes','Martine Ran184',' chausse de Tournai"','Lille','59000','France')
,('14','Frankenversand','Peter Franken','Berliner Platz 43','Mnchen','80805','Germany');
-- Products ---------------------------------------------------
create table products (pid INTEGER PRIMARY key, productname text , price float);
insert into products VALUES
(1,'Chais',223.489),
(2,'Chang',329.328479),
(3,'Ikura',49.2637),
(5,'Berliner',226.382),
(4,'Castel',273.478);
-- ADD Foreign keys ---------------------------------------------------
ALTER TABLE Orders
ADD FOREIGN KEY (ShipperID) REFERENCES Shippers(ShipperID);
ALTER TABLE Orders
ADD FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID);
ALTER TABLE Orders
ADD FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);
-- trim, ltrim, rtrim
-- length ,
-- upper, lower, concat left,
-- right, --replace
-- instr, mid ,
select concat(left(Firstname,1), '. ', Upper(Lastname)) as Fulname
, length(Notes) as Notes
, o.OrderID
, replace( right(c.PostalCode,3), '000', 'YYY') as EndOfPC
, c.Country
, instr( c.Country, 'land') as Land
, mid(c.Country, instr( c.Country, 'land'), 4) as Mids
, mid(c.Country, 1, instr( c.Country, 'land')-1) as Mids2
, concat( rtrim('f '),trim(' oo ') ,ltrim(' bar')) as Trims
from Employees e
join Orders o on e.EmployeeID = o.EmployeeID
join Customers c on o.CustomerID = c.CustomerID;
select productname as Name
, price
, ceil(price) as Ceils
, floor(price) as Floors
, round(price,2) as Nearest
from products;
select year(o.OrderDate) as Yrs
, month(o.OrderDate) as months
, day(o.OrderDate) as Days
, current_date() as Today
, CURRENT_TIMESTAMP() as TStamp
, ceil( DATEDIFF(current_date(), o.OrderDate) / 365 ) as Elapsed
, DATEDIFF('2022-12-25',current_date()) as DaysToGo
, floor ( DATEDIFF(current_date(), BirthDate )/ 365) as Age
, DATE_FORMAT( ADDDATE(BirthDate, INTERVAL 67 YEAR) , '%e-%b-%Y') as Retire
from Employees e
join Orders o on e.EmployeeID = o.EmployeeID
join Customers c on o.CustomerID = c.CustomerID;
-- for every year, for every month, count the orders
select year(o.OrderDate) as Yrs
, month(o.OrderDate) as Mnths
, count(o.OrderID) as OrderCount
from Employees e
join Orders o on e.EmployeeID = o.EmployeeID
join Customers c on o.CustomerID = c.CustomerID
group by year(o.OrderDate), month(o.OrderDate)
order by year(o.OrderDate), month(o.OrderDate);
-- for every month how many people have birthdays
select DATE_FORMAT( BirthDate, '%b') as Mnths
, count(e.EmployeeID) as Celebrations
from Employees e
group by DATE_FORMAT( BirthDate, '%b')
order by DATE_FORMAT( BirthDate, '%b');
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;