--MOVZU 2

--creat structure 
CREATE TABLE Customer(
	 CustomerID INT PRIMARY KEY,
	 CustomerName VARCHAR(50),
	 LastName VARCHAR(50),
	 Country VARCHAR(50),
	 Age int(2),
 	Phone int(10)
 	);
 	
 	CREATE DATABASE databasename;
 	
--basqa cedvelden cedvel yaratmaq
CREATE TABLE new_table_name AS
    SELECT column1, column2,…
    FROM existing_table_name
    WHERE ….;

--drop
DROP object object_name ;
DROP DATABASE IF EXISTS DatabaseName;

--alter (20 balliq)
ALTER TABLE Students 
ADD Email varchar(255);  --add

ALTER TABLE Students
DROP COLUMN Email;       --drop

ALTER TABLE table_name
MODIFY column_name column_type;  --modify

ALTER TABLE table_name [alter_option ...];
ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE CUSTOMERS DROP COLUMN SEX;
ALTER TABLE table_name 
ADD INDEX index_name [index_type] 
ALTER TABLE CUSTOMERS ADD INDEX name_index (NAME);
ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE CUSTOMERS DROP INDEX name_index;

CREATE TABLE EMPLOYEES(
   ID          INT NOT NULL,
   NAME        VARCHAR (20) NOT NULL,
   AGE         INT NOT NULL,
   ADDRESS     CHAR (25),
   SALARY      DECIMAL (18, 2)
);
ALTER TABLE EMPLOYEES 
ADD CONSTRAINT MyPrimaryKey 
PRIMARY KEY(ID);

ALTER TABLE table_name DROP PRIMARY KEY;

ALTER TABLE table_name 
RENAME COLUMN old_column_name to new_column_name;
ALTER TABLE CUSTOMERS RENAME COLUMN name to full_name;

ALTER TABLE table_name MODIFY COLUMN column_name datatype;
ALTER TABLE CUSTOMERS MODIFY COLUMN ID DECIMAL(18, 4);

--MOVZU 3
INSERT INTO table_name
VALUES (value1, value2,....) 

INSERT INTO table_name (column1, column2,...)
VALUES (value1, value2,....) 

INSERT INTO Persons 
VALUES ('Hetland', 'Camilla', 'Hagabakka 24', 'Sandnes') 

INSERT INTO Persons (LastName, Address)
VALUES ('Rasmussen', 'Storgt 67') 

UPDATE table_name
SET column_name = new_value
WHERE column_name = some_value 

UPDATE Person SET FirstName = 'Nina'
WHERE LastName = 'Rasmussen' 

DELETE FROM table_name
WHERE column_name = some_value 

DELETE FROM Person WHERE LastName = 'Rasmussen' 

TRUNCATE TABLE students;

--20 balliq

CREATE TABLE employees (
	id INT PRIMARY KEY, 
	name VARCHAR(50), 
	surname VARCHAR(50), 
	salary DECIMAL(10, 2), 
	date DATE
);
SELECT * FROM employees WHERE name LIKE '%s%' AND salary BETWEEN 300 AND 500;

--MOVZU 7 (20 balliq)
CREATE ROLE admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE employees TO admin;
GRANT admin TO username;
REVOKE SELECT, INSERT, UPDATE, DELETE ON TABLE employees FROM admin;

--second variant
CREATE USER 'username' IDENTIFIED BY 'password';
GRANT INSERT, DELETE, UPDATE ON database_name.table_name TO 'username';
FLUSH PRIVILEGES;

--example
CREATE USER 'john_doe' IDENTIFIED BY 'secure_password';
GRANT INSERT, DELETE, UPDATE ON company_db.employees TO 'john_doe';
FLUSH PRIVILEGES;