ALTER TABLE learners RENAME TO student;

    alter TABLE user1 DROP COLUMN age;

ALTER TABLE user1 add COLUMN demo int;

ALTER TABLE user1 ALTER COLUMN demo TYPE TEXT;

ALTER TABLE user1 alter column country set DEFAULT 'bangladesh';

insert into user1 values (5,'Imdad','[email protected]');

    ALTER TABLE user1 RENAME COLUMN demo to country;

    ALTER TABLE user1 alter COLUMN country set NOT NULL;

    ALTER TABLE user1 add constraint unique_email UNIQUE(email);

    CREATE TABLE
        Department(
            deptID SERIAL PRIMARY KEY,
            deptName VARCHAR(50)
        );

    INSERT INTO Employee VALUES(1,'KHorshed',1);


    SELECT * from employee;

    CREATE Table courses (
    course_id SERIAL PRIMARY KEY,
    course_name VARCHAR(255) NOT NULL,
    description VARCHAR(255),
    published_date DATE

    );

    #####- create courses table data ::::
    INSERT INTO courses(course_name,description, published_date)
    VALUES
    ('frontend developer','A complete frontend developer','2023-07-13'),
    ('backend developer','A complete backend developer',NULL),
    ('complete web developer','A  complete web developer','2023-05-10');

    UPDATE courses
    set
    description ='be a smart tech learner'
    WHERE course_id > 1 or  course_id  < 5
    ;

    SELECT * from department;

    SELECT name,email,joining_date from employee;

    SELECT * from employee
    WHERE salary > 55000 and salary < 60000 and name <> 'Olivia';

        SELECT * FROM employee
        ORDER BY name
        ASC;

            SELECT * FROM employee
            ORDER BY name
            DESC LIMIT 5 OFFSET 1;

        SELECT * FROM employee ORDER BY salary DESC
         LIMIT 1;

        SELECT * FROM employee ORDER BY salary DESC
            LIMIT 1 OFFSET 2;

    SELECT * from employee
    WHERE empid  NOT IN (2,3,5,10);

    SELECT * from employee
    WHERE salary BETWEEN 50000 AND 60000;

    SELECT * from employee
    WHERE name  LIKE '%Ja%';

    SELECT * from employee
    WHERE name  LIKE 'J%';

    SELECT * from employee
    WHERE name  LIKE '%s';

    SELECT * from employee
    WHERE name  LIKE '_m%';

    SELECT * from employee
        WHERE name  LIKE '__m%';

    SELECT * from employee
    WHERE name  LIKE 'J%s';

    SELECT * from employee WHERE deptid IS  NUll;

CREATE Table department2(
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100)
);

INSERT INTO department2 (department_id, department_name)
VALUES
    (1, 'Executive'),
    (2, 'HR'),
    (3, 'Sales'),
    (4, 'Development'),
    (5, 'Support'),
    (6, 'Research');

CREATE Table employee2(
    employee_id INT PRIMARY KEY,
    full_name VARCHAR(200),
    department_id INT,
    job_role VARCHAR(100),
    manager_id INT,
    FOREIGN KEY (department_id) REFERENCES department2(department_id)
);

INSERT INTO employee2 (employee_id, full_name, department_id, job_role, manager_id)
VALUES
    (1, 'John Doe', 3, 'Sales Manager', NULL),
    (2, 'Jane Smith', 2, 'HR Specialist', 1),
    (3, 'Michael Johnson', 4, 'Software Developer', 1),
    (4, 'Alice Brown', 2, 'HR Assistant', 2),
    (5, 'Robert White', 3, 'Sales Representative', 1),
    (6, 'Emily Williams', 4, 'Software Engineer', 3),
    (7, 'Daniel Lee', 5, 'Technical Support Specialist', 6),
    (8, 'Sophia Martinez', 6, 'Research Scientist', NULL),
    (9, 'David Johnson', 3, 'Sales Representative', 1),
    (10, 'Olivia Brown', 4, 'Software Developer', 3),
    (11, 'James Smith', 5, 'Technical Support Specialist', 6),
    (12, 'Ella Garcia', 2, 'HR Specialist', 2),
    (13, 'Matthew Davis', 4, 'Software Engineer', 3),
    (14, 'Ava Wilson', 6, 'Research Scientist', NULL),
    (15, 'Liam Mitchell', 3, 'Sales Representative', 5);




    SELECT * from department2;

    SELECT * from employee2;

    SELECT employee2.full_name,employee2.job_role,employee2.manager_id,department2.department_name
    FROM employee2
    INNER JOIN department2  ON  department2.department_id = employee2.department_id ;

    SELECT *
    FROM employee2
    LEFT JOIN department2  ON  department2.department_id = employee2.department_id ;

    SELECT *
    FROM employee2
    RIGHT JOIN department2  ON  department2.department_id = employee2.department_id ;

    SELECT *
    FROM employee2
    FULL JOIN department2  ON  department2.department_id = employee2.department_id ;

    SELECT *
    from employee
    NATURAL JOIN department2;

    SELECT * from employee CROSS JOIN department2;

    SELECT * from employee;

    SELECT AVG(salary) as AverageSalary from employee;

    SELECT MAX(salary) as MaximumSalary from employee;

    SELECT SUM(salary) as TotalSalary from employee;

    SELECT deptid, AVG(salary) from employee GROUP BY deptid;