Starting SQL basic information
1. creating a database
CREATE DATABASE (name of database);
2. creating tables
CREATE TABLE name of the table (
column1 data type
column2 data type
...
);
Ex.
CREATE TABLE students (
id INT, -> id: A column that stores integer data (`INT`).
name VARCHAR(50), -> A column that can store a string of up to 50 characters (`VARCHAR(50)`).
age INT -> A column that stores integer data (`INT`).
);
Since I need to make a sparta_employees, I can make like
CREATE TABLE sparta_employees (
id INT,
name VARCHAR(50),
positions VARCHAR(100),
salary INT,
hire_date DATE
);
Adding Data
Insert into (name of table) (column 1, column 2,...) values ( value 1, value 2, .....)
INSERT INTO sparta_employees (id, name, positions, salary, hire_date)
VALUES (1, '르탄이', '개발자', 30000, '2022-05-06');
INSERT INTO sparta_employees (id, name, positions, salary, hire_date)
VALUES (2, '배캠이', 'PM', 40000, '2021-09-25');
INSERT INTO sparta_employees (id, name, positions, salary, hire_date)
VALUES (3, '구구이', '파트장', 35000, '2023-06-01');
INSERT INTO sparta_employees (id, name, positions, salary, hire_date)
VALUES (4, '이션이', '팀장', 50000, '2021-07-09');
Data Query
Select column1(or *), column2.... FROM (name of table)
Ex. Select name from sparta_employees
to get details,
WHERE, ORDER BY, LIMIT
WHERE -> query data only under specific condition
Where salary > 30000
gets the data named salary only over 30000
ORDER BY -> Line up the query data
select name, salary from sparta_employees ORDER BY name DESC; (DECS -> Descending, ASC -> Ascending)
gets the data named name and salary, but sorted by name in descending order (DESC).
LIMIT -> Limit the number of data
select name, salary from sparta_employees LIMIT 5;
gets the data named name and salary but only gets 5 column
Editing data(UPDATE)
UPDATE (name of table) SET column 1 = value 1, column 2 = value 2 where (condition)
UPDATE sparta_employees SET salary = 60000 where id = 1
In sparta_employees table, change the salary to 60000 which has id of 1(must define which column to change it (where))
Delete data(DELETE)
DELETE FROM (name of table) where (condition)
DELETE FROM sparta_employees where id = 1
It will delete column itself(all data) which has id of 1
Primary Key: A column (or combination of columns) that uniquely identifies each row in a table.
Foreign Key: A column that references the primary key of another table, indicating a relationship between tables.
Ex.
CREATE TABLE numbers (
id INT PRIMARY KEY -> It will have its data
name VARCHAR(50) FOREIGN KEY (name) REFERENCES name(id) -> It will get the data from another table
Aggregate Functions
COUNT(): Counts the number of rows.
SELECT COUNT(name of column) FROM (name of table)
SELECT COUNT(name) FROM (sparta_employees)
SUM(): Calculates the sum of numeric values.
SELECT SUM(name of column) FROM (name of table)
SELECT COUNT(salary) FROM (sparta_employees)
AVG(): Calculates the average of numeric values.
SELECT AVG(salary) FROM (name of table)
SELECT COUNT() FROM (sparta_employees
MAX(): Finds the maximum value.
SELECT MAX(salary) FROM (name of table)
SELECT COUNT() FROM (sparta_employees)
MIN(): Finds the minimum value
SELECT MIN(salary) FROM (name of table)
SELECT COUNT() FROM (sparta_employees)
But also can be used like this.
select food_preparation_time,
delivery_time,
food_preparation_time + delivery_time as total_time
from food_orders
food_preparation_time + delivery_time as total_time
as -> used when changing the name.
meaning, food_preparation_time + delivery_time and the answer will be shown as total_time
select count(1) count_of_orders,
count(distinct customer_id) count_of_customers
from food_orders
(1 or *) -> count all the columns
distinct -> removes duplicates
Group by
select cuisine_type,
sum(price) sum_of_price
from food_orders
group by cuisine_type
It will work like distinct but will add and group all the prices.