SQL Snippets.sql

# SUB QUERIESS

```SQL

SELECT name, MIN(cost) FROM items WHERE name LIKE '%frog%' AND seller_id IN (
    
    SELECT DISTINCT seller_id FROM items WHERE name LIKE '%frog%'
	    
    );

```

# Joint tables
```SQL

SELECT customers.id, customers.name, customers.city, items.name AS product_name, items.cost, items.bids
FROM customers, items
WHERE customers.id = items.seller_id /*çComo estan relacionadas las dos tablas que tienen en comun¿*/
ORDER BY customers.id
```

```SQL
SELECT customers.id, customers.name, customers.city, items.name AS product_name, items.cost, items.bids
FROM customers LEFT OUTER JOIN items
ON customers.id = items.seller_id
ORDER BY customers.id
```

# Full text mode
```SQL
/*ALTER TABLE items ADD FULLTEXT(name)*/

SELECT name, cost FROM items WHERE Match(name) Against('+baby -seat' IN BOOLEAN MODE)
```

```SQL
INSERT INTO items(id, name, cost, seller_id, bids) VALUES
('103', 'Arduino Galileo', '12', '1', 5),
('104', 'PC gamer', '400', '1', 0),
('105', 'Ball footbal', '10', '1', '0');
SELECT * FROM items;
```

# Backup table
```SQL
CREATE TABLE users4 LIKE users3;
INSERT INTO users4 SELECT * FROM users3;

SELECT * FROM users4;
```

# Creating views
```SQL
CREATE VIEW top_10 AS
SELECT id, name, seller_id, bids FROM items ORDER BY bids DESC LIMIT 10;
```

# Foreign key on existent tables
```SQL
ALTER TABLE items ADD CONSTRAINT fkName FOREIGN KEY (seller_id) REFERENCES customers(id)
```

# Why auto_increment column change to other value when a fk constraint fail?


SELECT seller_id, COUNT(seller_id) item_count FROM items WHERE seller_id!=1 GROUP BY seller_id ORDER BY item_count DESC;