Practical no 4

// Some code
show databases;

use mysql_practical;

drop table emp;

create table emp(
EMPNO int,
ENAME VARCHAR(50),
JOB VARCHAR(20),
MGR INT,
HIREDATE DATE,
SAL INT,
COMM INT,
DEPTNO INT
);


-- alter
-- alter table emp set job = "Sr Manager"
-- where deptno = 1; 


SELECT * FROM EMP;
update emp set ename = "BLAKE"
where deptno = 1;

drop table emp;



INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES
(7698, 'BLAKE', 'MANAGER', 7893, '1981-05-01', 2850.00, 30.00, 1),
(7782, 'CLARK', 'MANAGER', 7893, '1981-06-09', 4450.00, NULL, 10),
(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.00, NULL, 30),
(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 4450.00, NULL, 10),
(8001, 'IVAN', 'MANAGER', 7839, '1922-06-29', 6000.00, NULL, 10),
(7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3500.00, NULL, 20),
(7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3500.00, NULL, 20),
(7369, 'SMITH', 'CLERK', 7902, '1981-06-09', 1300.00, NULL, 20),
(7499, 'ALLEN', 'SALESMAN', 7698, '1981-06-09', 1600.00, 300.00, 30),
(7521, 'WARD', 'SALESMAN', 7698, '1981-06-09', 1250.00, 500.00, 30),
(7654, 'MARTIN', 'SALESMAN', 7698, '1981-06-09', 1250.00, 1400.00, 30),
(7844, 'TURNER', 'SALESMAN', 7698, '1981-06-09', 1500.00, 0.00, 30),
(7876, 'ADAMS', 'CLERK', 7788, '1981-06-09', 1600.00, NULL, 20),
(7900, 'JAMES', 'CLERK', 7698, '1981-06-09', 4000.00, NULL, 30),
(7839, 'KING', 'PRESIDENT', NULL, '1981-06-09', 7000.00, NULL, 10),
(7934, 'MILLER', 'CLERK', 7782, '1981-06-09', 3300.00, NULL, 20),
(9000, 'RAHUL', 'MANAGER', NULL, NULL, 6000.00, 500.00, NULL),
(9020, 'AJAY', 'SALESMAN', NULL, NULL, 9000.00, 900.00, NULL),
(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.00, NULL, 30),
(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 4450.00, NULL, 10),
(8001, 'IVAN', 'MANAGER', 7839, '1922-06-29', 6000.00, NULL, 10),
(7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3500.00, NULL, 20),
(7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3500.00, NULL, 20),
(7369, 'SMITH', 'CLERK', 7902, '1981-06-09', 1300.00, NULL, 20),
(7499, 'ALLEN', 'SALESMAN', 7698, '1981-06-09', 1600.00, 300.00, 30),
(7521, 'WARD', 'SALESMAN', 7698, '1981-06-09', 1250.00, 500.00, 30),
(7654, 'MARTIN', 'SALESMAN', 7698, '1981-06-09', 1250.00, 1400.00, 30),
(7844, 'TURNER', 'SALESMAN', 7698, '1981-06-09', 1500.00, 0.00, 30),
(7876, 'ADAMS', 'CLERK', 7788, '1981-06-09', 1600.00, NULL, 20),
(7900, 'JAMES', 'CLERK', 7698, '1981-06-09', 4000.00, NULL, 30),
(7839, 'KING', 'PRESIDENT', NULL, '1981-06-09', 7000.00, NULL, 10),
(7934, 'MILLER', 'CLERK', 7782, '1981-06-09', 3300.00, NULL, 20),
(9000, 'RAHUL', 'MANAGER', NULL, NULL, 6000.00, 500.00, NULL),
(9020, 'AJAY', 'SALESMAN', NULL, NULL, 9000.00, 900.00, NULL);



select  ENAME|| 'is working as' ||job as emp_detail from emp;

SELECT ename, eno, sal, 
      first_name||' has salary '||salary as "new"  FROM myTable