ANSWER THE FOLLOWING QUESTIONS BASED ON THE TABLES AND DATABASE SQL SCRIPTING GIVEN.
Table – Worker
FIRST_NAME
Copyright By PowCoder代写 加微信 powcoder
JOINING_DATE
DEPARTMENT
2014-02-20 09:00:00
2014-06-11 09:00:00
2014-02-20 09:00:00
2014-02-20 09:00:00
2014-06-11 09:00:00
2014-06-11 09:00:00
2014-01-20 09:00:00
2014-04-11 09:00:00
Table – Bonus
WORKER_REF_ID
BONUS_DATE
BONUS_AMOUNT
2016-02-20 00:00:00
2016-06-11 00:00:00
2016-02-20 00:00:00
2016-02-20 00:00:00
2016-06-11 00:00:00
Table – Title
WORKER_REF_ID
WORKER_TITLE
AFFECTED_FROM
2016-02-20 00:00:00
2016-06-11 00:00:00
2016-06-11 00:00:00
2016-06-11 00:00:00
Asst. Manager
2016-06-11 00:00:00
2016-06-11 00:00:00
2016-06-11 00:00:00
2016-06-11 00:00:00
CREATE DATABASE ORG;
SHOW DATABASES;
CREATE TABLE Worker (
WORKER_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
FIRST_NAME CHAR(25),
LAST_NAME CHAR(25),
SALARY INT(15),
JOINING_DATE DATETIME,
DEPARTMENT CHAR(25)
INSERT INTO Worker
(WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT) VALUES
(001, ‘Monika’, ‘Arora’, 100000, ’14-02-20 09.00.00′, ‘HR’),
(002, ‘Niharika’, ‘Verma’, 80000, ’14-06-11 09.00.00′, ‘Admin’),
(003, ‘Vishal’, ‘Singhal’, 300000, ’14-02-20 09.00.00′, ‘HR’),
(004, ‘Amitabh’, ‘Singh’, 500000, ’14-02-20 09.00.00′, ‘Admin’),
(005, ‘Vivek’, ‘Bhati’, 500000, ’14-06-11 09.00.00′, ‘Admin’),
(006, ‘Vipul’, ‘Diwan’, 200000, ’14-06-11 09.00.00′, ‘Account’),
(007, ‘Satish’, ‘Kumar’, 75000, ’14-01-20 09.00.00′, ‘Account’),
(008, ‘Geetika’, ‘Chauhan’, 90000, ’14-04-11 09.00.00′, ‘Admin’);
CREATE TABLE Bonus (
WORKER_REF_ID INT,
BONUS_AMOUNT INT(10),
BONUS_DATE DATETIME,
FOREIGN KEY (WORKER_REF_ID)
REFERENCES Worker(WORKER_ID)
ON DELETE CASCADE
INSERT INTO Bonus
(WORKER_REF_ID, BONUS_AMOUNT, BONUS_DATE) VALUES
(001, 5000, ’16-02-20′),
(002, 3000, ’16-06-11′),
(003, 4000, ’16-02-20′),
(001, 4500, ’16-02-20′),
(002, 3500, ’16-06-11′);
CREATE TABLE Title (
WORKER_REF_ID INT,
WORKER_TITLE CHAR(25),
AFFECTED_FROM DATETIME,
FOREIGN KEY (WORKER_REF_ID)
REFERENCES Worker(WORKER_ID)
ON DELETE CASCADE
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’),
(008, ‘Executive’, ‘2016-06-11 00:00:00’),
(005, ‘Manager’, ‘2016-06-11 00:00:00’),
(004, ‘Asst. Manager’, ‘2016-06-11 00:00:00’),
(007, ‘Executive’, ‘2016-06-11 00:00:00’),
(006, ‘Lead’, ‘2016-06-11 00:00:00’),
(003, ‘Lead’, ‘2016-06-11 00:00:00’);
QUESTIONS- Write an SQL query to list the following:-
1) To list all workers
2) To list workers whose id = 005.
3) To list workers that belongs to account department
4) Show the worker ID whose bonus amount is 5000
5) Show worker whose last name = kumar
6) Show all details of employee 008
7) Show the title where worker ID = 5
8) To fetch “FIRST_NAME” from Worker table
9) To show all Worker details from the Worker table order by FIRST_NAME Ascending and DEPARTMENT Descending.
10) To showdetails of the Workers whose FIRST_NAME contains ‘a’.
11) To show details of the Workers whose SALARY lies between 100000 and 500000.
12) To show details of the Workers who have joined in Feb’2014.
13) Insert new values into the worker table
14) Insert new values into the bonus table
15) Delete an employee whose name is kumar
16) Delete an employee whose id is = 1 and title is manager
17) Delete employee name =
18) Delete employee whose salary is above 50000
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com