Fresher Mock Interview SQL | Technical Round | SQL Interview for Fresher | HR Interview

SQL Mock Interview Summary

Key Concepts:

• Primary Key, Foreign Key
• Joins (Inner, Left Outer, Right Outer, Full Outer)
• Views (Standard, Materialized)
• DDL, DML
• DUAL table
• SYSDATE
• Aggregate Functions (MIN, MAX, SUM, COUNT, AVG)
• WHERE vs. HAVING
• DELETE vs. TRUNCATE
• Finding the second maximum salary
• Maximum salary in a group

Primary and Foreign Keys

• Primary Key: A unique key in a table, with only one allowed per table. It cannot contain NULL values.
• Dropping a Primary Key: Achieved using the ALTER TABLE table_name DROP PRIMARY KEY command.
• Foreign Key: Used to establish relationships between two or more tables. A table can have multiple foreign keys, and they can accept NULL values.
• Parent-Child Table Relationship: Data cannot be deleted from a parent table if it exists in a child table without first deleting it from the child table. Attempting to do so will result in an error.

Joins

• Types of Joins: Inner Join, Left Join (Left Outer Join), Right Join (Right Outer Join), Full Outer Join, and Cross Join.
• Inner Join: Returns common values between two tables.
• Left Outer Join: Returns all records from the left table and the matching records from the right table. If there is no match, the right side will contain NULL values.
• Right Outer Join: Returns all records from the right table and the matching records from the left table. If there is no match, the left side will contain NULL values.
• Full Outer Join: Returns all records from both tables. If there is no match, the missing side will contain NULL values.
• Joining Multiple Tables: It is possible to perform joins on three or more tables.

Views

• Definition: A dynamically constructed virtual table used to simplify complex queries.
• Usage: Useful for frequently accessed specific data subsets, such as SELECT * FROM table_name WHERE salary BETWEEN 50000 AND 60000. Instead of writing the entire query, a view can be created and called using SELECT * FROM view_name.
• Row IDs: Tables and standard views have the same row IDs.
• Materialized View: A better version of a standard view where the values are stored in an actual table.
• Data Persistence: If the main table is dropped, a materialized view will still exist because it stores the data physically. A standard view will not exist because it relies on the base table.
• Physical Space: Materialized views take up physical storage space.
• Row IDs (Materialized View): Tables and materialized views have different row IDs.
• Data Synchronization: Changes in the base table are reflected dynamically in the materialized view.
• DDL and DML Operations: DDL (Data Definition Language) commands are not possible in standard views, but they are possible in materialized views. DML (Data Manipulation Language) operations are not possible in materialized views.
• Dropping Views: Standard views can be dropped.

DUAL and SYSDATE

• DUAL: A dummy table used in SQL, often for testing or simple queries.
• SYSDATE: Used as a reference to fetch the current date.

Aggregate Functions

• Types: Minimum (MIN), Maximum (MAX), Sum (SUM), Count (COUNT), and Average (AVG).
• Finding Total Salary: SELECT SUM(salary) FROM table_name.

WHERE vs. HAVING

• WHERE: Used to filter records before grouping. Can be used anywhere in the query. Does not require aggregate functions.
• HAVING: Used to filter records after grouping, typically with GROUP BY. Requires aggregate functions.
• Performance: HAVING is generally faster because it filters data specifically using aggregate functions.

DELETE vs. TRUNCATE

• DELETE: Used to delete specific rows or all rows from a table.
• TRUNCATE: Used to delete all rows from a table.
• Rollback: DELETE operations can be rolled back, while TRUNCATE operations cannot.
• Speed: TRUNCATE is faster than DELETE.

Practical SQL Queries

• Finding the Second Maximum Salary: Achieved using nested SELECT statements and the MAX function.
• Finding the Maximum Salary in a Group: Achieved using GROUP BY clause and the MAX function.
• Displaying Maximum Salaries Greater Than a Value: Achieved using GROUP BY, MAX function, and HAVING clause to filter the results.

Conclusion

The mock interview covered a wide range of SQL concepts, from basic key constraints and joins to more advanced topics like views, aggregate functions, and data manipulation. The candidate demonstrated a good understanding of the fundamentals but needs more practice with complex queries and real-world scenarios. The interviewer suggested focusing on practical exercises, practicing in front of a mirror or with peers, and continuing to refine SQL skills through consistent practice.