DBMS Assignment 05-01-2024

DBMS_Assignment_part1.md

2024-01-05
10:00

Create Table

CREATE TABLE WORKER (
WORKERID INT PRIMARY KEY,
WORKERNAME VARCHAR(20) NOT NULL,
DEPARTMENT VARCHAR(20) NOT NULL,
SALARY INT,
DOJ DATETIME NOT NULL)

Insert Data

INSERT INTO WORKER VALUES (1, 'Anil', 'Accounts', 10000, '2020-01-01');
INSERT INTO WORKER VALUES (2, 'Anu', 'Admin', 11000, '2020-10-01');
INSERT INTO WORKER VALUES (3, 'Anil Kumar', 'HR', 5000, '2020-11-03');
INSERT INTO WORKER VALUES (4, 'Anuradh', 'Marketing', 12000, '2021-12-11');
INSERT INTO WORKER VALUES (5, 'Anuradh Kumar', 'Establishment', 8000, '2020-01-01');
INSERT INTO WORKER VALUES (6, 'Anoop', 'Finance', 10000, '1998-01-01');
INSERT INTO WORKER VALUES (7, 'Anoop Kumar', 'Stores', 9000, '2001-01-01');
INSERT INTO WORKER VALUES (8, 'Adarsh', 'Security', 7000, '2008-01-01');

Questions

Change name of worker of Anil Kumar to Anil Prasad

update WORKER set WORKERNAME='Anil Prasad' where WORKERNAME='Anil Kumar'

Change the salary of workers to 5500 whose salary is less than 6000

update WORKER set salary=5500 where salary<6000;

Display the worker details drawing max salary

select Top 1 * from WORKER order by SALARY desc

Display the worker details drawing min salary

select Top 1 * from WORKER order by SALARY asc

Display the highest 2 worker salary

select Top 2 * from WORKER order by SALARY desc

• Only if u want the second highest salary person only

select  * FROM WORKER where salary in (select top 2 salary from WORKER ORDER BY Salary DESC); 

Display oldest worker as per DOJ

select Top 1 * from WORKER order by DOJ asc

Display newest worker as per DOJ

select Top 1 * from WORKER order by DOJ desc

Display name of all workers and department in format “Anil is working in Account”

SELECT CONCAT(WORKERNAME, ' is working in ',DEPARTMENT)
FROM Worker;

Display name of a workers and salary in format “Anil salary is 10000”

SELECT CONCAT(WORKERNAME, ' salary is ',)
FROM Worker;

Sort the worker data based on Salary and then dept

select  * FROM WORKER order by SALARY,DEPARTMENT;

Sort the worker data based on Salary reverse order and then dept in ascending order

select  * FROM WORKER order by SALARY desc,DEPARTMENT asc;

Display the first character of all workers

Display whose name ends with S

Display workers who are working in Account, Security

Display workers who are not working in HR

Display workers whose salary is greater than 8000 and less than 12000

Display unique departments

Display count of all workers

Display count of workers in each department

Display avg salary for each department

Display departments