lab assignment(07/11/23)

CREATE TABLE employee_29(
emp_no NUMBER(10) PRIMARY KEY,
ename VARCHAR2(30),
dept_no NUMBER(3),
contact NUMBER(10) CONSTRAINT uc_contact UNIQUE,
address VARCHAR2(200)
);

INSERT ALL
  INTO employee_29 (emp_no, ename, dept_no, contact, address) VALUES (1000001, 'Rajesh', 101, 9876543210, '123, ABC Street')
  INTO employee_29 (emp_no, ename, dept_no, contact, address) VALUES (2000002, 'Suresh', 102, 8765432109, '456, XYZ Street')
  INTO employee_29 (emp_no, ename, dept_no, contact, address) VALUES (3000003, 'Amit', 103, 7654321098, '789, PQR Street')
  INTO employee_29 (emp_no, ename, dept_no, contact, address) VALUES (4000005, 'Priya', 104, 6543210987, '321, LMN Street')
  INTO employee_29 (emp_no, ename, dept_no, contact, address) VALUES (5000006, 'Anita', 105, 5432109876, '654, EFG Street')
SELECT * FROM dual;

SELECT * FROM EMPLOYEE_29;

SELECT Ename,Contact FROM employee_29;

SELECT dept_no, COUNT(*) AS num_employees
FROM employee_29
GROUP BY dept_no;

SELECT AVG(LENGTH(contact)) AS avg_contact_length
FROM employee_29;

SELECT *
FROM employee_29
ORDER BY dept_no, ename;

SELECT *
FROM employee_29
WHERE ename LIKE '%A%';