CREATE DATABASE testdb;
DROP DATABASE testdb;
CREATE TABLE student(
age int,
subject varchar(255),
name varchar(255));

select * from testtable;
DROP TABLE testtable;

CREATE TABLE college_student AS
SELECT age, subject from student;
select * from college_student;

ALTER TABLE college_student 
ADD department varchar(255);

ALTER TABLE college_student
DROP age;

ALTER TABLE college_student
MODIFY COLUMN department char(255);

CREATE TABLE teacher (
name VARCHAR(255) NOT NULL,
id INT PRIMARY KEY,
age INT,
CHECK (age > 18 AND age < 65));

SELECT * FROM teacher;

INSERT INTO teacher
VALUES ("Qadeer", 1, 80);

CREATE TABLE employee (
id INT PRIMARY KEY, 
age INT NOT NULL, 
salary INT NOT NULL, 
emp_id INT UNIQUE,
department VARCHAR(255),
CONSTRAINT CHK_EMP CHECK (age > 24 AND salary > 1000 AND age < 65)
);

ALTER TABLE employee
ADD CHECK (department  = "Physics");

ALTER TABLE employee
ADD CONSTRAINT CHK_AGE CHECK (age < 80),
ALTER salary SET DEFAULT 25000;

ALTER TABLE employee
ALTER salary DROP DEFAULT;

ALTER TABLE employee
DROP CONSTRAINT employee_chk_1;

SELECT age AS a, 
salary AS s, 
department AS d
FROM employee;

CREATE UNIQUE INDEX name_index
ON employee (name);

ALTER TABLE employee
ADD COLUMN name varchar(255);

ALTER TABLE employee AUTO_INCREMENT=1; 

ALTER TABLE employee
MODIFY id int AUTO_INCREMENT;

ALTER TABLE employee AUTO_INCREMENT=100;

INSERT INTO employee (name, age, salary)
VALUES ("Arham", 51, 250001);

SELECT * FROM employee;

ALTER TABLE employee 
MODIFY time_today DATETIME;

INSERT INTO employee (name, age, salary, time_today)
VALUES ("Naveed1", 51, 250001, "2022-08-31 23:01:01");

LOAD DATA LOCAL INFILE '/Users/aqadeer/Desktop/bank_customer.csv '
INTO TABLE bank
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

select * from bank;

CREATE TABLE bank (
customer_id INT,
credit_score INT, 
country VARCHAR(255),
gender VARCHAR(255),
age INT,
tenure INT,
balance INT,
products_number INT,
credit_card INT,
active_member INT,
estimated_salary FLOAT,
churn INT);

select @@GLOBAL.secure_file_priv;

SHOW VARIABLES LIKE "secure_file_priv";
set global secure_file_priv = "/Users/aqadeer/Desktop";

show variables like "local_infile";
set global local_infile = 1;

select * from bank where age > 40 AND age < 50;

ALTER TABLE bank
ADD COLUMN starting_age INT;

ALTER TABLE bank
DROP starting_age;

UPDATE bank SET starting_age = age - tenure;

Select DISTINCT age AS record_count FROM bank
 ORDER BY age DESC;
 
select * from bank;

SELECT country, age, tenure, balance, churn, starting_age FROM bank
 WHERE age > 50
 ORDER BY age;

select COUNT(*) from bank 
where age  = 24;

SELECT * FROM bank
WHERE estimated_salary LIKE "1____0"
ORDER BY age;

SELECT * FROM bank 
WHERE country NOT IN ("Germany", "France", "Spain") AND balance <= 100000;