@final aspen Create a table EMPLOYEE with following schema:
(Emp_no, E_name, E_address, E_ph_no, Dept_no, Dept_name,Job_id , Salary)
Solution:
CREATE TABLE EMPLOYEE (
Emp_no INT,
E_name VARCHAR(50),
E_address VARCHAR(100),
E_ph_no VARCHAR(20),
Dept_no INT,
Dept_name VARCHAR(50),
Job_id INT,
Salary DECIMAL(10,2)
);
Write SQL queries for following question:
1. Insert at least 5 rows in the table.
INSERT INTO EMPLOYEE (Emp_no, E_name, E_address, E_ph_no, Dept_no, Dept_name, Job_id, Salary)
VALUES
('1', 'Hamza', 'Main St, New York', '555-1234', '1', 'Electrical', '1', '50000'),
('2', 'Hassam', 'Broadway, New York', '555-5678', '2', 'Mechanical', '2', '60000'),
('3', 'Muttayab', 'Fifth Ave, New York', '555-9012', '3', 'Sales', '3', '70000'),
('4', 'Zain', 'Park Ave New York', '555-3456', '1', 'Electrical', '1', '55000'),
('5', 'Bilal', 'Lexington Ave New York', '555-7890', '2', 'Mechanical', '2', '65000');
2. Display all the information of EMP table.
SELECT * FROM EMPLOYEE;
3. Display the record of each employee who works in department Electrical.
SELECT * FROM EMPLOYEE WHERE Dept_name = 'Electrical';
4. Update the city of Emp_no-12 with current city as Lahore.
UPDATE EMPLOYEE SET E_address = 'Lahore' WHERE Emp_no = 12;
5. Display the details of Employee who works in department MECH.
SELECT * FROM EMPLOYEE WHERE Dept_name = 'Mechanical';
6. Display the complete record of employees working in SALES Department.
SELECT * FROM EMPLOYEE WHERE Dept_name = 'Sales';
rewrite answers to make it look like another persons assignment