Course Code: CACS202 | Tribhuvan University BCA 3rd Semester

---

Laboratory Sheet 1: DDL & Integrity Constraints

```sql CREATE TABLE departments ( dept_id INT PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(50) NOT NULL UNIQUE, location VARCHAR(100) DEFAULT 'Kathmandu' );

CREATE TABLE employees ( emp_id INT PRIMARY KEY AUTO_INCREMENT, emp_name VARCHAR(100) NOT NULL, salary DECIMAL(10, 2) CHECK (salary >= 15000), hire_date DATE NOT NULL, dept_id INT, FOREIGN KEY (dept_id) REFERENCES departments(dept_id) ON DELETE SET NULL ); ```

Laboratory Sheet 2: Complex Joins & Aggregate Queries

``sql -- Retrieve Department Name, Total Employees, and Average Salary SELECT d.dept_name, COUNT(e.emp_id) AS total_employees, AVG(e.salary) AS average_salary FROM departments d LEFT JOIN employees e ON d.dept_id = e.dept_id GROUP BY d.dept_id, d.dept_name HAVING COUNT(e.emp_id) > 2 ORDER BY average_salary DESC; ``