<aside> 💡 Reference Video: https://www.youtube.com/watch?v=5OdVJbNCSso

</aside>

CREATE TABLE employees 
(
	employee_id INT, 
	first_name VARCHAR (50),
	last_name VARCHAR (50),
	hourly_pay DECIMAL (5,2), 
// so hourly_pay can 
//the column can store decimal numbers with a total of 5 digits, out of which 2 digits are reserved for the fractional part (decimal places). The remaining 3 digits are used for the integral part of the number.
	hire_date DATE //there are also DATETIME (YYYY-MM-DD hh:mm:ss) and TIME (hh:mm:ss) datatype
); 

//to show the entire table
SELECT * FROM employees; 

//rename table
RENAME TABLE original_name TO new_name;

//DELETE
DROP TABLE name_of_table;

//MODIFY TABLE
ALTER TABLE table_name
ADD phone_number VARCHAR (15);

//RENMAE A COLUMN
ALTER TABLE employees
RENAME COLUMN phone_number TO email;

//MODIFY A PROPERTY OF A COLUMN
ALTER TABLE employees
MODIFY COLUMN email VARCHAR (100)

//change poisitioning of a column
ALTER TABLE employees
MODIFY email VARCHAR (100)
AFTER first_name;
//FIRST; //another way 

//deleter a column
ALTER TABLE employees
DROP COLUMN email; 

//2nd sitting : add rows

//add row
INSERT INTO employees
VALUES (1, "Engene", "Krabs", 25.50, "2023-12-30"); //following order and data type, seperated with comma
[](<https://heytamjid.notion.site/3c5c0343da7040c883d5ae87d4a46507>)
//add multiple rows
INSERT INTO employees
VALUES (1, "Engene", "Krabs", 25.50, "2023-12-30"), //date is string with YYYY-MM-DD format
				(2, "Hey", "tamjid", 12.00, "2022-11-30"); 

//to insert into only certain columns
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (6, "Riham", "Asrafi"); 

INSERT INTO employees ( first_name, last_name, employee_id) //so this order matters here, not the original order in table
VALUES ("Riham", "Asrafi", 6); //this will add another row with same info. 

// select 28:00 

//show certain columns
SELECT last_name, first_name FROM employees; //order is upto you

SELECT * FROM employees WHERE employee_id =  5; //not == //aslo != exists

SELECT first_name FROM employees WHERE hourly_pay >=  5.00;

SELECT * FROM employees WHERE hire_date IS NULL; //you could say IS NOT NULL, but you can't use = or != here (when using WHERE). weird

//update DATA/row in a table
UPDATE employees //not ALTER //employees is the table name
SET hourly_pay = 10.25, hire_date = "2022-02-01", last_name = NULL //here = NULL is fine
where employee_id = 3; 

//to update all the rows at once
UPDATE employees 
SET hourly_pay = 10.25; //for all records

//another example where we update a table. suppose there is another column for yearly salary
UPDATE employees
SET salary = hourly_pay*40*52; 

//delete row from a table

DELETE FROM employees; //this will delete all rows at once

DELETE FROM employees
WHERE employeee_id = 2;

mySQL is NOT case sensitive

Auto-commit and stuffs at 37:00

SET AUTOCOMMIT = OFF; //you need to manually save all changes //by default it is on

COMMIT; //this will create a safepoint 

ROLLBACK; //this will restore data from the previous safe point

Untitled

CURRENT_DATE() + 1 will store tomorrow’s date.

CURRENT_DATE() - 1 will store yesterday’s date.

You can do something similar with CURRENT_TIME() and NOW() as well where you can add/subtract SECONDS.

//unique constraint on a table that is already created 
ALTER TABLE table_name
ADD CONSTRAINT
UNIQUE (name_of_the_column_you_want_to_be_unique); 

//not null constraint on a column - the column's value can't be null for any record
ALTER TABLE table_name
MODIFY column_name column_type (column_limit) NOT NULL; //weird syntax

//check constraint, when a record will we added, if hourly_pay is less than 10.00, it will not be added 
CREATE TABLE employees 
(
	employee_id INT, 
	first_name VARCHAR (50),
	last_name VARCHAR (50),
	hourly_pay DECIMAL (5,2),
	hire_date DATE, //comma
  CONSTRAINT constraint_name_to_identify CHECK (hourly_pay >= 10.00) //no comma as the last one

); 

//but what if we've already created a table and now wanna add a check constrint?
ALTER TABLE employees
ADD CONSTRAINT constraint_name_to_identify CHECK (hourly_pay >= 10.00); 

//delete a check
ALTER TABLE employees
DROP CHECK constraint_name_to_identify; 

//adding a default constraint while creating a table 
CREATE TABLE products 
(
product_id INT,
product_name VARCHAR (25),
price DECIMAL (5,2) DEFAULT 0 
//so this will set prices 0.00 automatically if no value is specified while creating a record/row
//BUT EIKHETRE row add korar somoi INSERT INTO table_name (diye ekhane column_name gulo specify kore record er values gulo add korte hobe)...
);

//to add default constraint to a created table 
ALTER TABLE products
ALTER price SET DEFAULT 0;

PRIMARY KEY

Primary Key must be unique and not NULL for each record. A table cannot contain multiple primary key.

CREATE TABLE trnx
(
trnx_id INT PRIMARY KEY,
amount DECIMAL (5,2)
);

//if the table/column already exists
ALTER TABLE trnx
ADD CONSTRAINT
PRIMARY KEY (trnx_id);

AUTO_INCREMENT

CREATE TABLE trnx
(
trnx_id INT PRIMARY KEY AUTO_INCREMENT, //default it'll start at 1 
//you CANT set AUTO_INCREMENT other than 1 here. if you wanna do that, you MUST do that when altering table. see below:
amount DECIMAL (5,2)
);

//if the table already exists or if we dont want to start at 1
ALTER TABLE trnx
AUTO_INCREMENT = 1000; 

//inserting records
INSERT INTO trnx (amount) //as we are not inserting record for all existing columns, we must specify which columns wer are adding records into
VALUES (4.34);