Use Microsoft SQL Server Management Studio for this practical i. Create a database called ‘ORG’ ii. create and populate the following tables: First table CREATE TABLE Worker ( WORKER_ID INT NOT NULL PRIMARY KEY, FIRST_NAME CHAR(25), LAST_NAME CHAR(25), SALARY INT, JOINING_DATE DATETIME, DEPARTMENT CHAR(25) ); 2 INSERT INTO Worker (WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT) 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') ); Second table CREATE TABLE Bonus ( WORKER_REF_ID INT, BONUS_AMOUNT INT, BONUS_DATE DATETIME, FOREIGN KEY (WORKER_REF_ID) REFERENCES Worker(WORKER_ID) ); INSERT INTO Bonus (WORKER_REF_ID, BONUS_AMOUNT, BONUS_DATE) VALUES (001, 5000, '2016-02-20'), (002, 3000, '2016-06-11'), (003, 4000, '2016-02-20'), (001, 4500, '2016-02-20'), (002, 3500, '2016-06-11'); Third table CREATE TABLE Title (3 WORKER_REF_ID INT, WORKER_TITLE CHAR(25), AFFECTED_FROM DATETIME, FOREIGN KEY (WORKER_REF_ID) REFERENCES Worker(WORKER_ID) ); INSERT INTO Title (WORKER_REF_ID, WORKER_TITLE, AFFECTED_FROM) VALUES (001, 'Manager', '2016-02-20 00:00:00'), (002, 'Executive', '2016-06-11 00:00:00'), (005, 'Manager', '2016-06-11 00:00:00'), (004, 'Asst. Manager', '2016-06-11 00:00:00'), (003, 'Lead', '2016-06-11 00:00:00') ; iii. Write an SQL query to print details for Workers with the first name as “Vishal” and “Monika” from Worker table. iv. Write an SQL query to print details of the Workers whose SALARY lies between 100000 and 500000. v. Write an SQL query to print details of the Workers who have joined in Feb’2014. vi. Write an SQL query to fetch the count of employees working in the department ‘Admin’.
Use Microsoft SQL Server Management Studio for this practical
i. Create a
ii. create and populate the following tables:
First table
CREATE TABLE Worker (
WORKER_ID INT NOT NULL PRIMARY KEY,
FIRST_NAME CHAR(25),
LAST_NAME CHAR(25),
SALARY INT,
JOINING_DATE DATETIME,
DEPARTMENT CHAR(25) );
2
INSERT INTO Worker
(WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT) 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')
);
Second table
CREATE TABLE Bonus (
WORKER_REF_ID INT,
BONUS_AMOUNT INT,
BONUS_DATE DATETIME,
FOREIGN KEY (WORKER_REF_ID) REFERENCES Worker(WORKER_ID)
);
INSERT INTO Bonus
(WORKER_REF_ID, BONUS_AMOUNT, BONUS_DATE) VALUES
(001, 5000, '2016-02-20'),
(002, 3000, '2016-06-11'),
(003, 4000, '2016-02-20'),
(001, 4500, '2016-02-20'),
(002, 3500, '2016-06-11');
Third table
CREATE TABLE Title (3
WORKER_REF_ID INT,
WORKER_TITLE CHAR(25),
AFFECTED_FROM DATETIME,
FOREIGN KEY (WORKER_REF_ID) REFERENCES Worker(WORKER_ID)
);
INSERT INTO Title
(WORKER_REF_ID, WORKER_TITLE, AFFECTED_FROM) VALUES
(001, 'Manager', '2016-02-20 00:00:00'),
(002, 'Executive', '2016-06-11 00:00:00'),
(005, 'Manager', '2016-06-11 00:00:00'),
(004, 'Asst. Manager', '2016-06-11 00:00:00'),
(003, 'Lead', '2016-06-11 00:00:00') ;
iii. Write an SQL query to print details for Workers with the first name as “Vishal” and “Monika” from Worker table.
iv. Write an SQL query to print details of the Workers whose SALARY lies between 100000 and 500000.
v. Write an SQL query to print details of the Workers who have joined in Feb’2014.
vi. Write an SQL query to fetch the count of employees working in the department ‘Admin’.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
