LabSQLQueries.sql

use lab0325;

CREATE TABLE Worker
(
WORKER_ID INT(3) NOT NULL PRIMARY KEY AUTO_INCREMENT,
FIRST_NAME VARCHAR(18),
LAST_NAME VARCHAR(18),
SALARY INT(10),
JOINING_DATE DATETIME,
DEPARTMENT VARCHAR(18)
);

INSERT INTO Worker 
VALUES (001, 'Monika', 'Arora', 100000, '2014-02-20 09:00:00', 'HR'), 
(002, 'Niharika', 'Verma', 80000, '2014-06-11 09:00:00', 'Admin'), 
(003, 'Vishal', 'Singhal', 300000, '2014-02-20 09:00:00', 'HR'), 
(004, 'Amitabh', 'Singh', 500000, '2014-02-20 09:00:00', 'Admin'),
(005, 'Vivek', 'Bhati', 500000, '2014-06-11 09:00:00', 'Admin'),
(006, 'Vipul', 'Diwan', 200000, '2014-06-11 09:00:00', 'Account'),
(007, 'Satish', 'Kumar', 75000, '2014-01-20 09:00:00', 'Account'),
(008, 'Geetika', 'Chauhan', 90000, '2014-04-11 09:00:00', 'Admin');

SELECT * FROM Worker;

CREATE TABLE Bonus
(
WORKER_REF_ID INT(1),
BONUS_DATE DATETIME,
BONUS_AMOUNT INT(10),
FOREIGN KEY (WORKER_REF_ID) REFERENCES Worker (WORKER_ID) ON DELETE CASCADE 
);

INSERT INTO Bonus
VALUES(1, '2016-02-20 00:00:00', 5000),
(2, '2016-06-11 00:00:00', 3000),
(3, '2016-02-20 00:00:00', 4000),
(1, '2016-02-20 00:00:00', 4500),
(2, '2016-06-11 00:00:00', 3500);

SELECT * FROM Bonus;

CREATE TABLE TITLE
(
WORKER_REF_ID INT(1),
WORKER_TITLE VARCHAR(18),
AFFECTED_FROM DATETIME,
FOREIGN KEY (WORKER_REF_ID) REFERENCES Worker (WORKER_ID) ON DELETE CASCADE
);

INSERT INTO TITLE
VALUES(1, 'Manager', '2016-02-20 00:00:00'),
(2, 'Executive', '2016-06-11 00:00:00'),
(8, 'Executive', '2016-06-11 00:00:00'),
(5, 'Manager', '2016-06-11 00:00:00'),
(4, 'Asst. Manager', '2016-06-11 00:00:00'),
(7, 'Executive', '2016-06-11 00:00:00'),
(6, 'Lead', '2016-06-11 00:00:00'),
(3, 'Lead', '2016-06-11 00:00:00');

SELECT * FROM TITLE;

SELECT FIRST_NAME AS WORKER_NAME
FROM WORKER; 

SELECT UPPER(FIRST_NAME) FROM WORKER;

SELECT DISTINCT DEPARTMENT FROM WORKER;

SELECT SUBSTR(FIRST_NAME, 1, 3) FROM WORKER;

SELECT INSTR(FIRST_NAME, BINARY'a') FROM WORKER
WHERE FIRST_NAME = 'Amitabh'; 

SELECT RTRIM(FIRST_NAME) FROM WORKER;

SELECT LTRIM(DEPARTMENT) FROM WORKER;

SELECT DISTINCT DEPARTMENT, LENGTH(DEPARTMENT) FROM WORKER;

SELECT REPLACE(FIRST_NAME, 'a', 'A') FROM WORKER;

SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME) AS COMPLETE_NAME FROM WORKER;

SELECT * FROM WORKER
ORDER BY FIRST_NAME ASC;

SELECT * FROM WORKER
ORDER BY FIRST_NAME ASC, DEPARTMENT DESC;

SELECT * FROM WORKER
WHERE FIRST_NAME IN ('Vipul', 'Satish');

SELECT * FROM WORKER
WHERE FIRST_NAME NOT IN ('Vipul', 'Satish');

SELECT * FROM WORKER
WHERE DEPARTMENT = 'Admin';

SELECT * FROM WORKER
WHERE FIRST_NAME LIKE '%a%';

SELECT * FROM WORKER
WHERE FIRST_NAME LIKE '%a';

SELECT * FROM WORKER
WHERE FIRST_NAME LIKE '%h' AND CHAR_LENGTH(FIRST_NAME) = 6;