Q1. Finding the Repeated Row in a Table

Question: There is a table where only one row is fully repeated. Write a query to find the repeated row.

Table Example:

Name	Section
abc	CS1
bcd	CS2
abc	CS1

Query:

SELECT name, section
FROM tbl
GROUP BY name, section
HAVING COUNT(*) > 1;

Explanation: This query groups rows by name and section, and then filters those groups having more than one occurrence.

Q2. Query to Find the 2nd Highest Salary

Query 1:

SELECT MAX(salary) 
FROM EMPLOYEES 
WHERE salary < (SELECT MAX(salary) FROM EMPLOYEES);

Query 2:

SELECT MAX(salary) 
FROM EMPLOYEES 
WHERE salary <> (SELECT MAX(salary) FROM EMPLOYEES);

Explanation: Both queries retrieve the second highest salary by excluding the highest salary.

Q3. Counting Rows Based on a Condition

Given Table:

ID	Salary	DeptName
1	10000	EC
2	40000	EC
3	30000	CS
4	40000	ME
5	50000	ME
6	60000	ME
7	70000	CS

Query:

SELECT E.ID
FROM Employee E
WHERE EXISTS (
    SELECT 1
    FROM Employee E2
    WHERE E2.DeptName = 'CS'
    AND E.salary > E2.salary
);

Result: The query returns IDs 2, 4, 5, 6, and 7 because their salaries are higher than the minimum salary in the ‘CS’ department (30000).

Q4. Writing a Trigger to Update Salaries

Trigger:

CREATE OR REPLACE TRIGGER update_trig
AFTER UPDATE ON Dept
FOR EACH ROW
BEGIN
    UPDATE Emp
    SET sal = sal + 100
    WHERE dept_no = :NEW.dept_no;
END;

Explanation: This trigger increments the salary of all employees in the updated department by 100.

Q5. Finding Students with Above-Average Marks

Query:

SELECT student, marks
FROM table
WHERE marks > (SELECT AVG(marks) FROM table);

Explanation: This query selects students whose marks are greater than the average marks.

Q6. Finding the Third-Highest Salary

Query:

SELECT Emp1.Name
FROM Employee Emp1
WHERE 2 = (
    SELECT COUNT(DISTINCT(Emp2.Salary))
    FROM Employee Emp2
    WHERE Emp2.Salary > Emp1.Salary
);

Explanation: The query identifies employees whose salary is the third-highest by counting distinct higher salaries.

Q7. Difference Between WHERE and HAVING Clauses

Explanation:

• WHERE Clause: Used to filter rows before any groupings are made.
• HAVING Clause: Used to filter groups after aggregations have been applied.

Example:

SELECT Student, SUM(Score) AS total
FROM Marks
GROUP BY Student
HAVING total > 70;

Explanation: The HAVING clause filters students based on their total score after the aggregation.

Q8. Difference Between Primary Key and Unique Key

Primary Key:

• Uniquely identifies each row in a table.
• Cannot contain NULL values.
• There can only be one primary key per table.

Unique Key:

• Ensures all values in a column are unique, but allows NULLs.
• Multiple unique keys can exist in a table.
• Can have a single NULL value.

Q9. Materialized vs Dynamic Views

Materialized Views:

• Disk-based.
• Updated periodically based on the query definition.
• Must be synchronized with its base tables.

Dynamic Views:

• Virtual and computed on-the-fly.
• The query is re-executed each time the view is accessed.

Q10. Embedded vs Dynamic SQL

Embedded SQL:

• SQL statements are hard-coded into the application.
• Compiled at compile time.

Dynamic SQL:

• SQL statements are constructed at runtime.
• Allows for more flexible queries.
• Compiled at run time.

Comparison:

• Embedded SQL: Faster and more efficient for static queries.
• Dynamic SQL: More flexible, suitable for dynamic query generation.

Q11. Difference Between CHAR and VARCHAR

CHAR:

• Fixed length.
• Faster performance for fixed-length data.
• Uses static memory allocation.

VARCHAR:

• Variable length.
• More efficient for variable-length data.
• Uses dynamic memory allocation.

Conclusion: CHAR is best for fields with a fixed length, while VARCHAR is ideal for variable-length data.

---

Feel free to let me know if you need any more details or further modifications!