Sets 1 to 9

Here are the detailed solutions to the questions in each set from the provided document:

### SET-1

1. **Draw an ER Diagram using basic and advanced ER concepts for an Online book store:**
   - Identify entities such as `Books`, `Authors`, `Customers`, `Orders`, `Reviews`.
   - Relationships could include `authored_by` between `Books` and `Authors`, `placed_by` between `Orders` and `Customers`, `reviews` between `Customers` and `Books`.

2. **Create a PL/SQL function to calculate the factorial of a number:**
   ```sql
   CREATE OR REPLACE FUNCTION calculate_factorial(n IN NUMBER) RETURN NUMBER IS
     result NUMBER := 1;
   BEGIN
     FOR i IN 1..n LOOP
       result := result * i;
     END LOOP;
     RETURN result;
   END;
   ```

3. **Retrieve all nurses who are registered:**
   ```sql
   SELECT * FROM Nurse WHERE registered = 'Y';
   ```

4. **Add a new column "phone" to the nurse table:**
   ```sql
   ALTER TABLE Nurse ADD phone VARCHAR2(15);
   ```

5. **Update the registered status of Nurse 'Laverne Roberts' to 'N':**
   ```sql
   UPDATE Nurse SET registered = 'N' WHERE name = 'Laverne Roberts';
   ```

6. **Create a view to display the names and positions of nurses:**
   ```sql
   CREATE VIEW NurseNamesPositions AS
   SELECT name, position FROM Nurse;
   ```

7. **Retrieve the names and positions of all employees (nurses and physicians):**
   ```sql
   SELECT name, position FROM Nurse
   UNION
   SELECT name, position FROM Physician;
   ```

8. **Retrieve the names of all department heads and their respective department names:**
   ```sql
   SELECT d.name AS DepartmentName, e.name AS DepartmentHead
   FROM Department d
   JOIN Employee e ON d.head_id = e.id;
   ```

9. **Retrieve the names of nurses and their respective departments (using nested queries):**
   ```sql
   SELECT n.name, d.name AS DepartmentName
   FROM Nurse n
   JOIN Department d ON n.department_id = d.id;
   ```

10. **Remove the department with department id 2 from the department table:**
    ```sql
    DELETE FROM Department WHERE id = 2;
    ```

### SET-2

1. **Draw an ER Diagram using basic and advanced ER concepts for Online banking:**
   - Entities: `Customers`, `Accounts`, `Transactions`, `Loans`.
   - Relationships: `holds` between `Customers` and `Accounts`, `performs` between `Accounts` and `Transactions`, `has_loan` between `Customers` and `Loans`.

2. **Create a PL/SQL procedure with an OUT parameter to calculate the square and cube of a number:**
   ```sql
   CREATE OR REPLACE PROCEDURE calc_square_cube (n IN NUMBER, square OUT NUMBER, cube OUT NUMBER) AS
   BEGIN
     square := n * n;
     cube := n * n * n;
   END;
   ```

3. **Write a SQL query to display the students who are not from Telangana or Andhra Pradesh:**
   ```sql
   SELECT * FROM Students WHERE state NOT IN ('Telangana', 'Andhra Pradesh');
   ```

4. **Create a view to display the columns Sid Sname for students belonging to Telangana:**
   ```sql
   CREATE VIEW TelanganaStudents AS
   SELECT Sid, Sname FROM Students WHERE state = 'Telangana';
   ```

5. **Display all the female students enrolled under Compcourse and who belong to OBC:**
   ```sql
   SELECT * FROM Students WHERE gender = 'Female' AND course = 'Compcourse' AND category = 'OBC';
   ```

6. **Display the student ids, names, and their percentage:**
   ```sql
   SELECT Sid, Sname, (marks_obtained/total_marks)*100 AS percentage FROM Students;
   ```

7. **Display the students in the ascending order of their names for each course:**
   ```sql
   SELECT * FROM Students ORDER BY course, Sname ASC;
   ```

8. **Write a SQL query to delete all the students' records who have enrolled for Compcourse and who are born after 2002:**
   ```sql
   DELETE FROM Students WHERE course = 'Compcourse' AND birth_date > DATE '2002-01-01';
   ```

9. **Write a SQL query to resize the column state to varchar2(40):**
   ```sql
   ALTER TABLE Students MODIFY state VARCHAR2(40);
   ```

10. **Write a SQL query to display all the student names where the length of the name is 5 characters:**
    ```sql
    SELECT Sname FROM Students WHERE LENGTH(Sname) = 5;
    ```

### SET-3

1. **Draw an ER Diagram using basic and advanced ER concepts for Online Auction System:**
   - Entities: `Auctions`, `Bidders`, `Items`, `Bids`.
   - Relationships: `places` between `Bidders` and `Bids`, `belongs_to` between `Bids` and `Auctions`, `offers` between `Auctions` and `Items`.

2. **Write a PL/SQL program to find the area of a circle:**
   ```sql
   CREATE OR REPLACE PROCEDURE find_area_circle (radius IN NUMBER, area OUT NUMBER) AS
   BEGIN
     area := 3.14159 * radius * radius;
   END;
   ```

3. **Change the position of Physician 'John Dorian' to 'Senior Staff Internist':**
   ```sql
   UPDATE Physician SET position = 'Senior Staff Internist' WHERE name = 'John Dorian';
   ```

4. **Create a view to display the names and positions of physicians who are not interns:**
   ```sql
   CREATE VIEW PhysiciansNotInterns AS
   SELECT name, position FROM Physician WHERE position != 'Intern';
   ```

5. **Retrieve all physicians who are attending physicians:**
   ```sql
   SELECT * FROM Physician WHERE position = 'Attending Physician';
   ```

6. **Retrieve the names of physicians and the departments they belong to (using nested queries):**
   ```sql
   SELECT p.name, d.name AS DepartmentName
   FROM Physician p
   JOIN Department d ON p.department_id = d.id;
   ```

7. **Retrieve the names of physicians who have "Physician" in their position:**
   ```sql
   SELECT name FROM Physician WHERE position LIKE '%Physician%';
   ```

8. **Retrieve the names of nurses who are registered and belong to the Surgery department:**
   ```sql
   SELECT n.name FROM Nurse n
   JOIN Department d ON n.department_id = d.id
   WHERE n.registered = 'Y' AND d.name = 'Surgery';
   ```

9. **Retrieve the names and total number of physicians in each department:**
   ```sql
   SELECT d.name AS DepartmentName, COUNT(p.id) AS NumberOfPhysicians
   FROM Department d
   JOIN Physician p ON d.id = p.department_id
   GROUP BY d.name;
   ```

10. **Delete all nurses who are not registered:**
    ```sql
    DELETE FROM Nurse WHERE registered = 'N';
    ```

### SET-4

1. **Draw an ER Diagram using basic and advanced ER concepts for Library Management system:**
   - Entities: `Books`, `Authors`, `Members`, `Loans`.
   - Relationships: `written_by` between `Books` and `Authors`, `borrowed_by` between `Loans` and `Members`, `contains` between `Loans` and `Books`.

2. **Create a PL/SQL function with parameters to calculate the area of a rectangle:**
   ```sql
   CREATE OR REPLACE FUNCTION calculate_area_rectangle(length IN NUMBER, width IN NUMBER) RETURN NUMBER IS
     area NUMBER;
   BEGIN
     area := length * width;
     RETURN area;
   END;
   ```

3. **Write a query in SQL to display all the information of the employees:**
   ```sql
   SELECT * FROM Employees;
   ```

4. **Write a query in SQL to find the salaries of all employees:**
   ```sql
   SELECT Salary FROM Employees;
   ```

5. **Write a query in SQL to display the unique designations for the employees:**
   ```sql
   SELECT DISTINCT Designation FROM Employees;
   ```

6. **Write a query in SQL to list the emp_name and salary increased by 15%:**
   ```sql
   SELECT emp_name, salary * 1.15 AS IncreasedSalary FROM Employees;
   ```

7. **Write a query in SQL to list the emp_id, salary, and dept_id of all the employees:**
   ```sql
   SELECT emp_id, salary, dept_id FROM Employees;
   ```

8. **Write a query in SQL to list the employees who joined before 1991:**
   ```sql
   SELECT * FROM Employees WHERE joining_date < DATE '1991-01-01';
   ```

9. **Write a query in SQL to display the average salaries of all the employees who work as ANALYST:**
   ```sql
   SELECT AVG(salary) AS AvgSalary FROM Employees WHERE designation = 'ANALYST';
   ```

10. **Create a view to display the employees whose name consists third letter as ‘A’ and salary is between 2600 to 3200:**
    ```sql
    CREATE VIEW SpecificEmployees

 AS
    SELECT * FROM Employees
    WHERE SUBSTR(emp_name, 3, 1) = 'A' AND salary BETWEEN 2600 AND 3200;
    ```

### SET-5

1. **Draw an ER Diagram using basic and advanced ER concepts for Hospital Management system:**
   - Entities: `Patients`, `Doctors`, `Nurses`, `Departments`, `Appointments`.
   - Relationships: `assigned_to` between `Patients` and `Doctors`, `works_in` between `Nurses` and `Departments`, `scheduled_for` between `Appointments` and `Patients`.

2. **Write a PL/SQL Function program to use INOUT:**
   ```sql
   CREATE OR REPLACE PROCEDURE calc_area_perimeter_rectangle(length IN NUMBER, width IN NUMBER, area OUT NUMBER, perimeter OUT NUMBER) AS
   BEGIN
     area := length * width;
     perimeter := 2 * (length + width);
   END;
   ```

3. **Display the structure of nurse and physician tables:**
   ```sql
   DESC Nurse;
   DESC Physician;
   ```

4. **Retrieve the names of all physicians who have a Senior Attending position:**
   ```sql
   SELECT name FROM Physician WHERE position = 'Senior Attending';
   ```

5. **Retrieve the names of physicians who are not heads of any department:**
   ```sql
   SELECT p.name FROM Physician p
   LEFT JOIN Department d ON p.id = d.head_id
   WHERE d.head_id IS NULL;
   ```

6. **Retrieve the names of physicians whose positions start with 'Surgical':**
   ```sql
   SELECT name FROM Physician WHERE position LIKE 'Surgical%';
   ```

7. **Retrieve the names of physicians who have the same position as Nurse 'Carla Espinosa':**
   ```sql
   SELECT p.name FROM Physician p
   JOIN Nurse n ON p.position = n.position
   WHERE n.name = 'Carla Espinosa';
   ```

8. **Modify the data type of the "ssn" column in the physician table:**
   ```sql
   ALTER TABLE Physician MODIFY ssn VARCHAR2(20);
   ```

9. **Remove the department with department id 1 from the department table:**
    ```sql
    DELETE FROM Department WHERE id = 1;
    ```

10. **Create a view to display the names and SSNs of physicians with the position 'Attending Physician':**
    ```sql
    CREATE VIEW AttendingPhysicians AS
    SELECT name, ssn FROM Physician WHERE position = 'Attending Physician';
    ```

### SET-6

1. **Draw an ER Diagram using basic and advanced ER concepts for Online food order system:**
   - Entities: `Customers`, `Restaurants`, `Orders`, `MenuItems`, `Delivery`.
   - Relationships: `places` between `Customers` and `Orders`, `contains` between `Orders` and `MenuItems`, `delivered_by` between `Delivery` and `Orders`.

2. **Create a PL/SQL procedure with parameters to calculate the square of a number:**
   ```sql
   CREATE OR REPLACE PROCEDURE calc_square (n IN NUMBER, square OUT NUMBER) AS
   BEGIN
     square := n * n;
   END;
   ```

3. **Display Supplier numbers and Supplier names whose name starts with ‘R’:**
   ```sql
   SELECT supplier_number, supplier_name FROM Suppliers WHERE supplier_name LIKE 'R%';
   ```

4. **Display the name of suppliers who supply Processors and whose city is Delhi:**
   ```sql
   SELECT s.supplier_name FROM Suppliers s
   JOIN Supplies sp ON s.supplier_id = sp.supplier_id
   WHERE sp.item_name = 'Processor' AND s.city = 'Delhi';
   ```

5. **Display the names of suppliers who supply the same items as supplied by Ramesh:**
   ```sql
   SELECT DISTINCT s2.supplier_name FROM Suppliers s1
   JOIN Supplies sp1 ON s1.supplier_id = sp1.supplier_id
   JOIN Supplies sp2 ON sp1.item_name = sp2.item_name
   JOIN Suppliers s2 ON sp2.supplier_id = s2.supplier_id
   WHERE s1.supplier_name = 'Ramesh' AND s2.supplier_name != 'Ramesh';
   ```

6. **Write a SQL query to increase the price of Keyboard by 200:**
   ```sql
   UPDATE Items SET price = price + 200 WHERE item_name = 'Keyboard';
   ```

7. **Write a SQL query to display supplier numbers, supplier names, and item_price for suppliers in Delhi in the ascending order of item_price:**
   ```sql
   SELECT s.supplier_number, s.supplier_name, i.price
   FROM Suppliers s
   JOIN Supplies sp ON s.supplier_id = sp.supplier_id
   JOIN Items i ON sp.item_id = i.item_id
   WHERE s.city = 'Delhi'
   ORDER BY i.price ASC;
   ```

8. **Write a SQL query to add a new column called CONTACTNO:**
   ```sql
   ALTER TABLE Suppliers ADD CONTACTNO VARCHAR2(15);
   ```

9. **Delete the record whose item_price is the lowest of all the items supplied:**
   ```sql
   DELETE FROM Items WHERE price = (SELECT MIN(price) FROM Items);
   ```

10. **Create a view on the table which displays only supplier numbers and supplier names:**
    ```sql
    CREATE VIEW SupplierInfo AS
    SELECT supplier_number, supplier_name FROM Suppliers;
    ```

### SET-7

1. **Draw an ER Diagram using basic and advanced ER concepts for Hotel Management system:**
   - Entities: `Guests`, `Rooms`, `Reservations`, `Services`, `Staff`.
   - Relationships: `reserves` between `Guests` and `Reservations`, `provides` between `Services` and `Guests`, `assigned_to` between `Staff` and `Rooms`.

2. **Write a PL/SQL Program to print the salary changes when the salary is changed by using a trigger:**
   ```sql
   CREATE OR REPLACE TRIGGER salary_change_trigger
   BEFORE UPDATE OF salary ON Employees
   FOR EACH ROW
   BEGIN
     DBMS_OUTPUT.PUT_LINE('Old Salary: ' || :OLD.salary || ', New Salary: ' || :NEW.salary);
   END;
   ```

3. **Find courses with credit hours greater than the average:**
   ```sql
   SELECT * FROM Courses WHERE credit_hours > (SELECT AVG(credit_hours) FROM Courses);
   ```

4. **Retrieve students with last name starting with 'D':**
   ```sql
   SELECT * FROM Students WHERE last_name LIKE 'D%';
   ```

5. **Change the instructor's name of a specific course:**
   ```sql
   UPDATE Courses SET instructor_name = 'New Instructor' WHERE course_id = 101;
   ```

6. **Create a view for students with GPA > 3.8:**
   ```sql
   CREATE VIEW HighGPAStudents AS
   SELECT * FROM Students WHERE GPA > 3.8;
   ```

7. **Find courses taken by students with high GPA:**
   ```sql
   SELECT DISTINCT c.course_name FROM Courses c
   JOIN Enrollments e ON c.course_id = e.course_id
   JOIN Students s ON e.student_id = s.student_id
   WHERE s.GPA > 3.8;
   ```

8. **Retrieve students enrolled in multiple courses:**
   ```sql
   SELECT s.student_name FROM Students s
   JOIN Enrollments e ON s.student_id = e.student_id
   GROUP BY s.student_name HAVING COUNT(e.course_id) > 1;
   ```

9. **Count students enrolled in courses with credit hours less than 4:**
   ```sql
   SELECT c.course_name, COUNT(e.student_id) AS NumberOfStudents
   FROM Courses c
   JOIN Enrollments e ON c.course_id = e.course_id
   WHERE c.credit_hours < 4
   GROUP BY c.course_name;
   ```

10. **Alter the students table to add a new column 'Rank':**
    ```sql
    ALTER TABLE Students ADD Rank NUMBER;
    ```

### SET-8

1. **Draw an ER Diagram using basic and advanced ER concepts for “Flipkart”:**
   - Entities: `Users`, `Products`, `Orders`, `Categories`, `Reviews`.
   - Relationships: `places` between `Users` and `Orders`, `contains` between `Orders` and `Products`, `categorized_as` between `Products` and `Categories`.

2. **Create a trigger to update the Instructor's Name in Courses table when it is updated in Students table:**
   ```sql
   CREATE OR REPLACE TRIGGER update_instructor_name
   AFTER UPDATE OF instructor_name ON Students
   FOR EACH ROW
   BEGIN
     UPDATE Courses SET instructor_name = :NEW.instructor_name
     WHERE instructor_id = :OLD.instructor_id;
   END;
   ```

3. **Alter students table to add Email column:**
   ```sql
   ALTER TABLE Students ADD Email VARCHAR2(50);
   ```

4. **Update Email for a specific student:**
   ```sql
   UPDATE Students SET Email = 'newemail@example.com' WHERE student_id = 101;
   ```

5. **Find courses with enrollments having students with GPA less than any specific value:**
   ```sql
   SELECT DISTINCT c.course_name FROM Courses c
   JOIN Enrollments e ON c.course_id = e.course_id
   JOIN Students s ON e.student_id = s.student_id
   WHERE s.GPA <

 3.0;
   ```

6. **Create a view for students in a specific course:**
   ```sql
   CREATE VIEW SpecificCourseStudents AS
   SELECT s.student_name FROM Students s
   JOIN Enrollments e ON s.student_id = e.student_id
   WHERE e.course_id = 101;
   ```

7. **Retrieve students enrolled in courses taught by a specific instructor:**
   ```sql
   SELECT s.student_name FROM Students s
   JOIN Enrollments e ON s.student_id = e.student_id
   JOIN Courses c ON e.course_id = c.course_id
   WHERE c.instructor_name = 'Dr. Smith';
   ```

8. **Count the number of students per course:**
   ```sql
   SELECT c.course_name, COUNT(e.student_id) AS NumberOfStudents
   FROM Courses c
   JOIN Enrollments e ON c.course_id = e.course_id
   GROUP BY c.course_name;
   ```

9. **Find courses not taken by any students:**
   ```sql
   SELECT * FROM Courses c
   WHERE NOT EXISTS (
     SELECT 1 FROM Enrollments e WHERE c.course_id = e.course_id
   );
   ```

10. **Alter the table to drop the Email column:**
    ```sql
    ALTER TABLE Students DROP COLUMN Email;
    ```

### SET-9

1. **Draw an ER Diagram using basic and advanced ER concepts for Inventory Management System:**
   - Entities: `Products`, `Suppliers`, `Orders`, `Warehouses`.
   - Relationships: `supplies` between `Suppliers` and `Products`, `stored_in` between `Products` and `Warehouses`, `contains` between `Orders` and `Products`.

2. **Create a trigger to log changes in inventory quantities:**
   ```sql
   CREATE OR REPLACE TRIGGER log_inventory_changes
   AFTER UPDATE OF quantity ON Inventory
   FOR EACH ROW
   BEGIN
     INSERT INTO InventoryLog (product_id, old_quantity, new_quantity, change_date)
     VALUES (:OLD.product_id, :OLD.quantity, :NEW.quantity, SYSDATE);
   END;
   ```

3. **Add a new column 'Discount' to the Orders table:**
   ```sql
   ALTER TABLE Orders ADD Discount NUMBER(5,2);
   ```

4. **Update the discount for a specific order:**
   ```sql
   UPDATE Orders SET Discount = 10 WHERE order_id = 102;
   ```

5. **Find products with quantity less than a specified threshold:**
   ```sql
   SELECT * FROM Products WHERE quantity < 50;
   ```

6. **Create a view to display the details of products from a specific supplier:**
   ```sql
   CREATE VIEW SupplierProducts AS
   SELECT p.* FROM Products p
   JOIN Supplies s ON p.product_id = s.product_id
   WHERE s.supplier_id = 201;
   ```

7. **Retrieve orders containing products from a specific warehouse:**
   ```sql
   SELECT o.order_id, o.order_date FROM Orders o
   JOIN OrderDetails od ON o.order_id = od.order_id
   JOIN Products p ON od.product_id = p.product_id
   WHERE p.warehouse_id = 301;
   ```

8. **Count the number of products supplied by each supplier:**
   ```sql
   SELECT s.supplier_name, COUNT(p.product_id) AS NumberOfProducts
   FROM Suppliers s
   JOIN Supplies sp ON s.supplier_id = sp.supplier_id
   JOIN Products p ON sp.product_id = p.product_id
   GROUP BY s.supplier_name;
   ```

9. **Find suppliers who do not supply any products:**
   ```sql
   SELECT * FROM Suppliers s
   WHERE NOT EXISTS (
     SELECT 1 FROM Supplies sp WHERE s.supplier_id = sp.supplier_id
   );
   ```

10. **Alter the Orders table to drop the Discount column:**
    ```sql
    ALTER TABLE Orders DROP COLUMN Discount;
    ```

This detailed summary provides the necessary SQL statements and PL/SQL code snippets required for each of the tasks in the provided document, along with a brief explanation of the ER diagrams that should be created for each scenario.