CREATE TABLE Worker (
W_ID INT,
FIRST_NAME VARCHAR(25),
LAST_NAME VARCHAR(25),
DEPARTMENT VARCHAR(25),
JOINING_DATE DATETIME,
SALARY INT(15),
);
INSERT INTO Worker
(W_ID, FIRST_NAME, LAST_NAME,DEPARTMENT, JOINING_DATE,SALARY ) VALUES
(1, 'Harry', 'Porter', HR, '1/3/2016', 'HR'150000),
(2, 'Monica', 'Teres', Account, '14/3/2019',55000),
(3, 'Hannah', 'Muri', Admin, '22/4/2020', 85000),
(4, 'Cedric', 'Leo', HR, '7/1/2020', 75000),
(5, 'Cho', 'Len', Null, '25/9/2006', 200000),
(6, 'Luna', 'Costa', Admin, '1/1/2021',150000),
(7, 'Draco', 'Borna', HR, '3/5/2021', 80000),
(8, 'pask', 'Sijer', Account, '1/1/2020',45000)
(9, 'Goyle', 'Firzo',HR, '4/5/2021',85000);
CREATE TABLE Bonus (
WORKER_REF_ID INT,
BONUS_DATE DATETIME,
BONUS_AMOUNT INT(10),
FOREIGN KEY (WORKER_REF_ID)
REFERENCES Worker(WORKER_ID)
ON DELETE CASCADE);
INSERT INTO Bonus
(WORKER_REF_ID,BONUS_DATE,BONUS_AMOUNT) VALUES
(2,'20-3-2020',5000),
(1,'9-8-2021',7000),
(1,, '7-5-2021'9000),
(3, '7-2-2021',8500),
-using ARITHMETIC operator
1.Write an SQL query where bonus amount will be increasing into double and column
name will be double bonus?
Ans: Select bonus_ammount, 2*bonus_ammount AS double bonus
from bonus;
-using CONCATENATION operator
2.Write an SQL query to print the FIRST_NAME and LAST_NAME from Worker table into a
single column COMPLETE_NAME. A space char should separate them
Ans: Select CONCAT(FIRST_NAME, ' ', LAST_NAME) AS 'COMPLETE_NAME' from Worker;
-using COLUMN ALIAS
3.Write an SQL query to show the Annual salary of the workers
Ans: SELECT FIRST_NAME, salary*12 annsal
FROM worker
ORDER BY annsal;
-using LIKE operator
4.Write an SQL query to print details of Workers with DEPARTMENT name as �Admin�
Ans: Select * from Worker where DEPARTMENT like 'Admin%';
-using IS NULL operator
5.Write an SQL query to find the name of the worker who�s department value is null
Ans: SELECT FIRST_NAME, DEPARTMENT FROM worker WHERE DEPARTMENT IS NULL;
-using ORDER BY
6.Write an SQL query to show the salary of the workers in the descending order.
Ans: SELECT salary FROM worker
ORDER BY salary DESC;
-using SUBSTR function
7.Write an SQL query to print the first three characters of FIRST_NAME from Worker
table.
Ans: Select substring(FIRST_NAME,1,3) from Worker;
-using NVL function
8.Write an SQL query where if an worker has been not assigned to any department yet
department is NULL(n/a). Otherwise it will display the actual department name.)
Ans: SELECT first_name, NVL(Department,'n/a')
FROM workers;
-using MAX function
9.Write an SQL query to show the second highest salary from worker table.
Ans: Select max(Salary) from Worker
where Salary not in (Select max(Salary) from Worker);
-using SUM function
10.Write an SQL query to show the total salary of the workers
Ans: Select SUM(salary) from worker;
From employee;
-using GROUP BY clause
11.Write an SQL query to show the average salary of the specific grouped department
Ans: SELECT Department, AVG(salary)
FROM worker
GROUP BY Department;
-using HAVING clause
12.Write an SQL query to show the maximum salary of the specific grouped department
which is more than 75000 T.k)
Ans: SELECT deptno, max(sal)
FROM emp
GROUP BY deptno
HAVING max(salary)>75000;
13.-using SINGLE-ROW subquery
Write an SQL query to to fetch worker names,department and minimum salary from the
worker table.
SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME) As Worker_Name, Department,Salary
FROM worker where Salary=(Select MIN(Salary) from Worker);
14.-using MULTIPLE-ROW subquery
Write an SQL query to show worker�s First_names with salaries >= 50000 and <=
100000.
Ans: SELECT FIRST_NAME, Salary
FROM worker WHERE W_ID IN
(SELECT W_ID FROM worker
WHERE Salary BETWEEN 50000 AND 100000);
-using EQUIJOIN
15.Write an sql query which can join worker id, fetch worker names form worker
table and worker�s reference id, bonus amount from bonus table.
Ans: SELECT worker.w_id, worker.CONCAT(First_name, ' ', Last_Name) As
Worker_Name,bonus.Worker Ref_Id, bonus.bonus amount from worker,bonus
where w_id= Worker Ref _Id;
-using SIMPLE VIEW
16.Create a view, worker information, that contains details of worker�s in
department HR.
CREATE VIEW workers_info
AS
SELECT W_id, First_Name, Last_name, department, joining date
FROM Worker
Where department = �HR�;
-using COMPLEX VIEW
17.Write an sql query which creates a view that shows all worker�s Id and first
name who gets 7000 t.k as bonus..
CREATE VIEW bonus holder AS
SELECT Worker.W_id,Worker.First_Name, bonus.bonus ammount
FROM Worker,Bonus
WHERE bonus ammount = 7000;
Create a simple view as 'Managers_info' to show each manager's name, salry and
branch name where m_salary is greater then or equal to 85000
CREATE VIEW managers_info AS
SELECT m_name,m_salary,branch_name
FROM manager
Where m_salary>=85000;
Create a view as 'Employees_info' to show each employee's name, salry, post and
branch name
CREATE VIEW employees_info AS
SELECT e_name,e_salary,e_post,branch_name
FROM employee,manager
WHERE employee.m_id=manager.m_id;
CREATE VIEW employees_info AS
SELECT e_name,e_salary
FROM employee
WHERE e_post='QAT';
