christo-pr/basic-sql-exercise

Basic SQL Exercise to work with https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_in

Basic SQL Examples

DATABASE ERD:

1.- Basic DB Commands

• Create a Table

CREATE TABLE usuarios ( id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(20), description TEXT )

• Rename a table

ALTER TABLE usuarios RENAME TO users

• Delete a table

DROP TABLE usuarios

• Rename a column

ALTER TABLE users RENAME COLUMN name TO username

• Add a new column

ALTER TABLE users ADD COLUMN age

• Remove a column

ALTER TABLE users DROP COLUMN age

---

2.- Insert/Update/Delete Data

• Inserting data

INSERT INTO users (name, description) VALUES ('Christofer','Web Dev');

• Updating data

UPDATE users SET name = 'testing update' # All Rows will be affected

UPDATE users SET name = 'just one' WHERE id = 1 # Filtered condition

• Delete data

DELETE FROM users WHERE id = 1

3.- Basic querys

• no filter

SELECT * FROM Customers

• filtered info

SELECT * FROM Customers WHERE Country = "Mexico"

SELECT * FROM Products WHERE price BETWEEN 50 AND 200

SELECT * FROM Customers WHERE city IN ("Berlin", "London")

SELECT * FROM Customers WHERE ContactName LIKE '%Mar%'

• Ordered info

SELECT * FROM Customers ORDER BY customerID

• Paginated query

SELECT * FROM Customers LIMIT 5

• Offset paginated query

SELECT * FROM Customer LIMIT 5 OFFSET 5

---

4.- Aggregate functions

• Count the rows

SELECT COUNT(customerID) FROM Customers

• Sum rows

SELECT SUM(price) from Products

---

5.- Relationships

• Basic one-to-many query

SELECT *, o.orderDate FROM Customers as c
INNER JOIN orders as o
WHERE c.customerID = o.customerID

• Nested querys

SELECT *, sh.shipperName FROM Customers as c
INNER JOIN orders as o
ON c.customerID = o.customerID
INNER JOIN shippers as sh
ON o.shipperID = sh.shipperID