MY_SQL_newBoston

SELECT name, zip FROM customers ORDER BY zip DESC, name;
SELECT name, zip FROM customers ORDER BY zip DESC, name;
SELECT NAME, id FROM customers ORDER BY id DESC LIMIT 1;
SELECT name FROM customers ORDER BY name LIMIT 1;

*-----------------------------*
SELECT id, name, city FROM customers WHERE id = 55;
SELECT id, name, city FROM customers WHERE id != 54;
SELECT id, name, city FROM customers WHERE id < 4;
SELECT id, name, city FROM customers WHERE id BETWEEN 10 AND 10;
SELECT id, name, state FROM customers WHERE state = 'CA';

/*---------------------------------------*/
SELECT name, state, city FROM customers WHERE state = 'CA' AND
city = 'Hollywood';

SELECT name, state, city FROM customers WHERE city = 'Boston' OR state = 'CA';
SELECT name, id, city FROM customers WHERE id = 1 OR id = 2 AND city = 'Raleigh';
SELECT name, id, city FROM customers WHERE (id = 1 OR id = 2) AND city = 'Raleigh';
/*----------------------------*///////////////////////

SELECT name, state FROM customers WHERE state = 'CA' OR state = 'NY' OR state = 'NC';
SELECT name, state FROM customers WHERE state IN ('CA', 'NY', 'NC');
SELECT name, state FROM customers WHERE state NOT IN ('CA', 'NY', 'NC') ORDER BY state;

/*------------------------------------*/
SELECT name FROM items WHERE name LIKE 'new%';
SELECT name FROM items WHERE name LIKE '%computer%';
SELECT name FROM items WHERE name LIKE '%computer%';
SELECT city FROM customers WHERE city LIKE 'H%D';
/*------------------------------------*/

SELECT name FROM items WHERE name LIKE '_%BOXES OF FROGS';

/*------------------------------------*/

/*-----------------REGULAR EXPRESSIONS--------*/
SELECT name FROM items WHERE name REGEXP 'new';
SELECT name FROM items WHERE name REGEXP '.boxes';
SELECT name FROM items WHERE name REGEXP '.new';
SELECT name FROM items WHERE name REGEXP 'gold|car';
SELECT name FROM items WHERE name REGEXP '[127345] boxes of frogs';
SELECT name FROM items WHERE name REGEXP '[^12345] boxes of frogs';
SELECT name FROM items WHERE name REGEXP '[^1-7] boxes of frogs';
/*------------------------------------*/

/*-----------------CREATING CUSTOM COLUMNS--------*/
SELECT customers CONCAT(name,', ', state, ', ', city) AS new_adress FROM customers GROUP BY custumers;
SELECT name, cost, cost-1 AS sale_price FROM items;

/*-----------------------------------------------*/


/*-----------------MORE ABOUT FUNCTIONS------------------*/
FUNCTION IS A NAME THAT DOES SOMETHING
SELECT name, UPPER(name) AS UPPER_CASE FROM customers;
SELECT cost, SQRT(cost) AS SQUERE_ROOT FROM items;
/*-----------------------------------------------------*/

/*---------------AGREGATE FUNCTIONS---------------------------*/
SELECT AVG(cost) FROM items;
SELECT SUM(bids) FROM items AS sum_of_bids;
/*--------------------------------------------*/

/*---------------AGREGATE FUNCTIONS---------------------------*/
SELECT COUNT(name) FROM items WHERE seller_id = 6;
SELECT AVG(cost) FROM items WHERE seller_id = 6;
/*------------------------------*/

SELECT COUNT(*) AS item_count,
MAX(cost) AS MAX,
AVG(cost) AS avg
FROM items WHERE seller_id = 12;

/*------Group by-------*/

MAX(cost) AS MAX,
MIN(cost) AS MIN,
MAX(cost) - MIN(cost) AS difeer,
AVG(cost) AS avg
FROM items GROUP BY seller_id;
/*-----------------------------------*/
SELECT seller_id, COUNT(*) AS item_count,
MAX(cost) AS MAX,
MIN(cost) AS MIN,
MAX(cost) - MIN(cost) AS difeer,
AVG(cost) AS avg
FROM items
GROUP BY seller_id
/*
FOR EACH SELLER
*/
HAVING COUNT(*)>=3;
/*---------------------*/
SELECT seller_id,
COUNT(*)
FROM items;
/*----------------------*/
SELECT seller_id,
COUNT(*) AS item_count
FROM items
GROUP BY seller_id
HAVING COUNT(*)>=3;
/*
GROUP use with HAVING


*/
/*-------------------------*/

SELECT seller_id,
COUNT(*) AS item_count
FROM items
GROUP BY seller_id
HAVING COUNT(*)>=3;
/*------------------------*/
SELECT seller_id,
COUNT(*) AS item_count
FROM items
GROUP BY seller_id
HAVING COUNT(*)>=0
ORDER BY item_count DESC;


/*-------Subqueries-------*/
SubquerY - ITS A QUERY INSIDE OTHER QUERY

/*----------------------------------*/
SELECT name, cost FROM items WHERE cost>(
SELECT AVG(cost) FROM items
) ORDER BY cost DESC;
/*---------------------------------*/

/*----------------21_Another Subquery Example-------------*/
/*
SELECT seller_id FROM items WHERE name LIKE'%boxes of frogs'
*/
SELECT name, MIN(cost) FROM items WHERE name LIKE '%boxes of frogs'
AND seller_id IN (
/*
68, 6, 18
*/
SELECT seller_id FROM items WHERE name LIKE'%boxes of frogs'
)
/*---------------------------------*/

/*--------------22_How to Join Tables----------------*/
SELECT customers.id, customers.name, items.name, items.cost
FROM customers, items
WHERE customers.id=seller_id
/*
THIS IS WHERE MAGIC HAPPENS
*/
ORDER BY customers.id
/*---------------------------------*/

/*------------23_Outer Joins--------*/
/*nick names
SELECT i.seller_id, i.name, c.id
FROM customers AS c, items AS i
WHERE i.seller_id = c.id
*/

/*-----------
/*--------------------------------------*/
SELECT seller_id, customers.name, items.name FROM customers, items WHERE
customers.id = seller_id;
*/
SELECT customers.name, items.name
FROM
customers LEFT OUTER JOIN items ON customers.id=seller_id
/*
from                       in
in customers there peaple who have no product in items
they LEFT (it could be right) OUTER JOIN items
/*-------------------------------*/

/*---------------24_UNION-----------------*/
SELECT name, cost, bids FROM items WHERE bids>100
UNION
SELECT name, cost, bids FROM items WHERE cost>1000;
/*-------------------------------------------*/

/*---------FULL TEXT SEARCHING-------*/
SELECT name, cost FROM items WHERE MATCH(name)
Against('baby -coat'IN BOOLEAN MODE)


/*----------------------------26_INSERT INTO--------------------------*/
INSERT INTO items VALUES('101','bakon strips','9.95','1','0');
INSERT INTO items (id,name,cost, seller_id, bids)
            VALUES ('404','abricosos', '20.0','990','54')

/*-------------------------------------------*/


/*-----------------27 - How to Insert Multiple Rows---------------*/
INSERT INTO items (id, name, cost, seller_id, bids) VALUES
('508','teens pans','200','54','800'),
('524','porn star','50000','208','11111'),
('625','BDSM','13','54','808080')

/*---------------------------------------*/
INSERT INTO (id, name, cost, seller_id, bids) SELECT
id, name, cost, seller_id, bids FROM faketable;
/*---------------------------------------*/


/*---------28 - UPDATE & DELETE--------*/
UPDATE items SET name = 'maxhardcore' WHERE  id = '404';
/*------------------------------------*/
DELETE FROM items WHERE id = '404';
/*-------------------------*/

/*----------------29_CREATE TABLE + NOT_NULL AUTO INCREMENT------------------------*/

CREATE TABLE users_404(
id INT,
uesername VARCHAR(30),
passvord VARCHAR(20),
PRIMARY KEY(id)
);
ALTER TABLE users_404 CHANGE id id INT(10)AUTO_INCREMENT NOT NULL;



CREATE TABLE little_people(
id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(30) NOT NULL,
password VARCHAR (10) NOT NULL,
PRIMARY KEY(id)
)
/*---------------------------------------------*/

/*-----------------------31 - ALTER / DROP / RENAME TABLE----*/

ALTER TABLE items ADD COLUMN dyatly VARCHAR (52);
ALTER TABLE items DROP COLUMN dyatly
/*---------------------------------------------------------*/
ALTER TABLE items ADD COLUMN dyatly VARCHAR (52);
ALTER TABLE items DROP COLUMN dyatly;
DROP TABLE little_people;
RENAME TABLE customers TO customers_404;
CREATE TABLE stunning_copy SELECT * FROM customers_404;
/*----------------------------------------*/

/*---------------------32 - Views--------------------------*/
views IS a TEMPORARY TABLES
views don't contain any real data, it holds data from another tables (a view into another table)
VIEWS UPDATES AUTOMATICALLY


/*------------------------------------------------------*/
CREATE VIEW most_bids AS SELECT id, name, bids
FROM items ORDER BY bids DESC;
/*--------------------------------------------*/