SQL.md

Create table

CREATE TABLE Users (
	id MEDIUMINT AUTO_INCREMENT,
  email VARCHAR(50),
  password VARCHAR(50),
  PRIMARY KEY(id)
)

create table in MysqlServer

CREATE TABLE Users (
  Personid int IDENTITY(1,1) PRIMARY KEY,
  email VARCHAR(50),
  password VARCHAR(50)
)

Select all SELECT * FROM Products

Select row in PostgresSQL SELECT * FROM "Schema_Name"."Table_Name" WHERE SKU_ID = 20149;

Delete All rows DELETE FROM Customers

Delete row DELETE FROM Customers WHERE CustomerId=1

Update UPDATE Customers SET City='Kairo', country="Egypt" WHERE CustomerID=1

Limit and offset SELECT * from Customers LIMIT 5 OFFSET 5 or short version SELECT * from Customers LIMIT 5,5

Get Max value SELECT MAX(Price) FROM Products

Calculate Tax SELECT price, price + 10 AS tax FROM [Products]

Math round SELECT ROUND(AVG(price), 2) FROM [Products]

Select all where the first letter of the City is an "a" or a "c" or an "s". SELECT * FROM Customers WHERE City LIKE '[acs]%';

Select all records where the first letter of the City starts with anything from an "a" to an "f" SELECT * FROM Customers WHERE City LIKE '[a-f]%';

Get sum SELECT *, sum(Price) as Summary FROM [products] group by SupplierID

Sum alt

SELECT ProductName, SUM(Price) as Total 
FROM Products
GROUP BY ProductName

Join two tables by same key id

SELECT * FROM [Customers]
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID

With aliases

SELECT * FROM [Customers] as c
INNER JOIN Orders as o
ON c.CustomerID = o.CustomerID

Join two tables by same key id

SELECT c.CustomerID, c.CustomerName, o.OrderDate
FROM [Customers] as c
JOIN Orders as o
ON c.CustomerID = o.CustomerID

Join and add last column

SELECT c.*, o.OrderDate
FROM [Customers] as c
JOIN Orders as o
ON c.CustomerID = o.CustomerID

Insert new rows insert into shippers values(6, 'name', 'phone')

Insert rows from other table

INSERT INTO Shippers
SELECT OrderID, OrderDate, ShipperID
FROM Orders

Having

SELECT ProductName, SUM(Price) as Total 
FROM Products
GROUP BY ProductName
HAVING SUM(Price) > 15

Get total, round and group

SELECT ProductName, ROUND(SUM(Price), 2) as Total 
FROM Products
GROUP BY SupplierID

Add new column to table

ALTER TABLE Customers
ADD Email varchar(50)

ALTER TABLE Customers
ADD Birthday DATE

Select IN

SELECT * FROM [Orders]
WHERE OrderID IN (10248, 10249)
--WHERE OrderID NOT IN (10248, 10249)

Clear table trick

DELETE FROM table WHERE sku_id <> 0;

Postgres change column type

ALTER TABLE
    "schema"."table"
ALTER COLUMN
    column_name TYPE TIMESTAMP;

Postgres create table ultile primary keys

CREATE TABLE "scherma"."newtable_name" (
    SKU_ID integer NOT NULL,
	STORE_ID integer NOT NULL,
	SS_COEF numeric(9,1) DEFAULT NULL,
	UPDATED_BY integer DEFAULT NULL,
	UPDATED timestamp DEFAULT NULL,
    PRIMARY KEY (SKU_ID,STORE_ID)
);

Oracle create table

CREATE TABLE SKU_STORE_SS_COEF (
  SKU_ID number NOT NULL,
  STORE_ID number NOT NULL,
  SS_COEF numeric(9,1),
  UPDATED_BY number,
  UPDATED date,
  CONSTRAINT store_pk PRIMARY KEY (SKU_ID, STORE_ID)
 )

MySQL modify column

ALTER TABLE table_name
MODIFY COLUMN column_name decimal(5,2);

MySQL rename table

RENAME TABLE old_table TO new_table;

MySQL add new primary key after column

ALTER TABLE `demo`.`FCST_ADJUSTMENTS` 
ADD COLUMN `Week` INT(11) NOT NULL AFTER `UPDATED`,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`SKU_ID`, `STORE_ID`, `Week`);
;

MySQL add new primary key first column

ALTER TABLE `demo`.`FCST_ADJUSTMENTS` 
ADD COLUMN `WEEK` INT(11) NOT NULL FIRST,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`WEEK`, `SKU_ID`, `STORE_ID`);
;

MySQL rename column

ALTER TABLE MASTER_FCST
CHANGE COLUMN FIX fix tinyint(1) NOT NULL DEFAULT '0'

ORACLE add column default not null

ALTER TABLE MASTER_FCST ADD FIX NUMBER(1,0) DEFAULT 0;
ALTER TABLE MASTER_FCST MODIFY FIX NOT NULL;

CROSS JOIN (cartesian)

SELECT P.SKU_ID, P.ARTICLE_NAME, S.STORE_ID, S.STORE_NAME
FROM PRODUCTS as P
CROSS JOIN STORES as S
WHERE P.SKU_ID in (55,56) and S.STORE_ID in(1,2)

DenisDov/SQL.md