Scribd
Upload
20 views2 pages

SQL Assignment 1

The document contains SQL commands for creating and managing an Employee database table. It includes commands for inserting employee records, querying employee information based on various criteria, updating records, and deleting employees from the HR department. Key operations include filtering by salary, name initials, joining dates, and department-specific calculations like average and total salaries.

Uploaded by

skylarzhang66
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
20 views2 pages

SQL Assignment 1

The document contains SQL commands for creating and managing an Employee database table. It includes commands for inserting employee records, querying employee information based on various criteria, updating records, and deleting employees from the HR department. Key operations include filtering by salary, name initials, joining dates, and department-specific calculations like average and total salaries.

Uploaded by

skylarzhang66
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 2

--- Q1. Create a database table as shown above.

CREATE TABLE Employee (


WORKER_ID INT,
FIRST_NAME CHAR(20),
LAST_NAME CHAR(20),
SALARY INT,
STARTING_DATE datetime,
DEPARTMENT CHAR(20)
);

INSERT INTO Employee (WORKER_ID, FIRST_NAME, LAST_NAME, SALARY,


STARTING_DATE, DEPARTMENT) VALUES
(001, 'Monika', 'Arora', 100000, '2011-02-20', 'HR'),
(002, 'Johnson', 'Verma', 80000, '2019-06-11', 'Admin'),
(003, 'Vis', 'Singhal', 300000, '2018-02-20', 'HR'),
(004, 'Sally', 'Sima', 500000, '2014-02-20', 'Admin'),
(005, 'Vivek', 'Bhati', 500000, '2016-06-11', 'Admin'),
(006, 'Shane', 'Diwan', 200000, '2014-06-11', 'Account'),
(007, 'Santo', 'Kumar', 75000, '2014-01-20', 'Account'),
(008, 'Giann', 'Chauhan', 90000, '2015-04-11', 'Admin');

--- Q2. Show all the employee info if his/her salary is greater or equal to 100000.
SELECT*FROM Employee WHERE SALARY >= 100000;

--- Q3. Show all the employee info if his/her first name’s initial is ‘V’.
SELECT*FROM Employee WHERE FIRST_NAME LIKE 'V%';

--- Q4. Show all the employee info if he/she joined the company before 2018-01-01.
SELECT*FROM Employee WHERE STARTING_DATE < '2018-01-01';

--- Q5. Display only the names of employee who is in the Account Department.
SELECT FIRST_NAME, LAST_NAME FROM Employee WHERE DEPARTMENT =
'Account';

--- Q6. Display the average salary among all the Admin employees.
SELECT AVG(SALARY) FROM Employee WHERE DEPARTMENT = 'Admin';

--- Q7. Display the total salary for Admin Department.


SELECT SUM(SALARY) FROM Employee WHERE DEPARTMENT = 'Admin';

--- Q8. Display the whole table using ascending order by employee’s last name (A - Z).
SELECT * FROM Employee ORDER BY LAST_NAME ASC;

--- Q9. Update the staring date of the employee who named Johnson to 2020-06-11.
UPDATE EMPLOYEE SET STARTING_DATE = '2020-06-11' WHERE FIRST_NAME =
'Johnson';

--- Q10. Remove all HR department employees from the table.


DELETE FROM Employee WHERE DEPARTMENT = 'HR';

You might also like