SQL Assignment
Q4.
1.
Create table Worker (
WORKER_ID int,
FIRST_NAME varchar,
LAST_NAME varchar,
SALARY int,
JOINING_DATE date,
DEPARTMENT varchar);
2. WORKER TABLE
insert into Worker (WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT)
values ('001', 'Niharika', ‘Verma', '80000', '2014-06-11 09:00:00', 'Admin');
insert into Worker (WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT)
values ('002', 'Monika', 'Arora', '100000', '2014-02-20 09:00:00', 'HR');
insert into Worker (WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT)
values (‘003’, ‘Vishal Singhal’, ‘300000’, ‘2014-02-20 09:00:00’, ‘HR’);
insert into Worker (WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT)
values (' 004’, ‘Amitabh Singh’, ‘500000’, ‘2014-02-20 09:00:00’, ‘Admin’);
insert into Worker (WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT)
values (' 005’, ‘Vivek Bhati’, ‘500000’, ‘2014-06-11 09:00:00’, ‘Admin’);
insert into Worker (WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT)
values (' 006’, ‘Vipul Diwan’, ‘200000’, ‘2014-06-11 09:00:00’, ‘Account’);
insert into Worker (WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT)
values (' 007’, ‘Satish Kumar’, ‘75000’, ‘2014-01-20 09:00:00’, ‘Account’);
insert into Worker (WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT)
values (‘008’, ‘Geetika Chauhan’, 90000’, ‘2014-04-11 09:00:00’, ‘Admin’);
SQL Assignment
BONUS TABLE
create table BONUS
(
WORKER_REF_ID int,
BONUS_DATE date,
BONUS_AMOUNT int);
insert into BONUS (WORKER_REF_ID, BONUS_DATE, BONUS_AMOUNT)
values ('1', '2016-02-20 00:00:00', '5000');
insert into BONUS (WORKER_REF_ID, BONUS_DATE, BONUS_AMOUNT)
values ('2', '2016-06-11 00:00:00', '3000');
insert into BONUS (WORKER_REF_ID, BONUS_DATE, BONUS_AMOUNT)
values ('3','2016-02-20 00:00:00','4000');
insert into BONUS (WORKER_REF_ID, BONUS_DATE, BONUS_AMOUNT)
values ('1','2016-02-20 00:00:00','4500');
insert into BONUS (WORKER_REF_ID, BONUS_DATE, BONUS_AMOUNT)
values ('2','2016-06-11 00:00:00','3500');
TITLE TABLE
create table TITLE
(
WORKER_REF_ID int,
WORKER_TITLE varchar,
AFFECTED_FROM date);
insert into TITLE (WORKER_REF_ID, WORKER_TITLE, AFFECTED_FROM)
values ('1', 'Manager', '2016-02-20 00:00:00');
insert into TITLE (WORKER_REF_ID, WORKER_TITLE, AFFECTED_FROM)
values ('2', 'Executive', '2016-06-11 00:00:00');
insert into TITLE (WORKER_REF_ID, WORKER_TITLE, AFFECTED_FROM)
values ('8', 'Executive', '2016-02-20 00:00:00');
SQL Assignment
insert into TITLE (WORKER_REF_ID, WORKER_TITLE, AFFECTED_FROM)
values ('5', 'Manager', '2016-06-11 00:00:00');
insert into TITLE (WORKER_REF_ID, WORKER_TITLE, AFFECTED_FROM)
values ('4', 'Asst.Manager', '2016-02-20 00:00:00');
insert into TITLE (WORKER_REF_ID, WORKER_TITLE, AFFECTED_FROM)
values ('7', 'Executive', '2016-06-11 00:00:00');
insert into TITLE (WORKER_REF_ID, WORKER_TITLE, AFFECTED_FROM)
values ('6', 'Lead', '2016-02-20 00:00:00');
insert into TITLE (WORKER_REF_ID, WORKER_TITLE, AFFECTED_FROM)
values ('3', 'Lead', '2016-06-11 00:00:00');
3. select FIRST_NAME as WORKER_NAME from WORKER;
4. select UPPER(FIRST_NAME) from Worker;
5. select distinct DEPARTMENT from Worker;
6. select * from Worker order by FIRST_NAME asc;
7. select * from Worker order by FIRST_NAME asc, DEPARTMENT desc;
8. select * from Worker where FIRST_NAME = 'Vipul';
9. select * from Worker where FIRST_NAME like '%s%';
10. select * from Worker where FIRST_NAME like '%a';
11. select count(*) from Worker where DEPARTMENT = 'Admin';
12. select count(*) from Worker where SALARY > '100000';
