Wipro Inteview Question

0

DBMS Interview Question

 1. 31. Diffrenciate between DBMS and RDBMS :

Database Management System (DBMS) is a software that is used to define, create and maintain a database and provides controlled access to the data. eg => Hadoop, NoSQL etc.

RDBMS is used to store or manage only the data that are in the form of tables
eg => MySQL, PostgreSQL, SQL server, Oracle etc.  


2. 32. What is Primary Key :
A Primary Key is a Column or a group of columns in a table that uniquely identifies the row of data in that table.

PK = Unique + Not Null


3. 33. What is Foreign Key:
  • A FOREIGN KEY  is a column (or group of columns) in one table, that refers to the PRIMARY Key in another table.
  • The table with the FOREIGN Key is called the child table, and the table with the PRIMARY KEY is called the referenced or parent table.
  • It maintains referential integrity  in database.

Example: of Question number 2 and 3 ::::

  • In a "Students" table, student_id can be a Primary Key because it uniquely identifies each student.
  • In an "Enrollment" table, student_id can be a Foreign Key that links to the student_id in the "Students" table.


4. 34. What are constraints & their types: 


SQL constraints are used to specify  rules for the data in a table.

Not Null => Ensures that a column cannot have a NULL value.
Unique => Ensures that all values in a column are different. 
Primary key => A Combination of a NOT NULL and UNIQUE.  Uniquely identifies each row in a table.
Foreign key => Prevents actions that would destory links between tables.
Check =>  Ensures that the values in a column satisfies a specific condition.
Default => Sets a default value for a column if no value is specified.


5. 35. Differentiate Delete, Drop and Truncate:
   

  • What does DELETE do in SQL?

    • DELETE removes specific rows based on a condition. It's part of a transaction, so it can be rolled back, and triggers are fired. It keeps the table structure intact.

  • How is DROP different from DELETE?

    • DROP completely removes a table or database from the system, including its structure and data, and it’s irreversible. DROP doesn’t fire triggers, and once executed, the data is permanently deleted.

  • When would you use TRUNCATE instead of DELETE?

    • TRUNCATE is used to quickly remove all rows from a table while keeping the table structure for future use. It’s faster than DELETE, non-transactional, and cannot be rolled bac


    • 6.defference between primary key and foreign key ?

    1. Primary Key:

      • It uniquely identifies each record in a table.
      • It does not allow duplicate or NULL values.
      • It is used within the table where it is defined.

    2. Foreign Key:

      • It is a column in one table that refers to the Primary Key of another table.
      • It can have duplicate and NULL values (depending on constraints).
      • It is used to establish a relationship between two tables.

    Example:

    • In a "Students" table, student_id can be a Primary Key because it uniquely identifies each student.
    • In an "Enrollment" table, student_id can be a Foreign Key that links to the student_id in the "Students" table.


    7. Constraints and their type ?

    Constraints are rules applied to table columns in a database to ensure data integrity and accuracy. They restrict the type of data that can be stored in a table.

    Types of Constraints:

    1. NOT NULL

      • Ensures that a column cannot have NULL values.
      • Example: A name column in a "Students" table cannot be left empty.

    2. UNIQUE

      • Ensures all values in a column are unique (no duplicates).
      • Example: A email column in a "Users" table must have distinct values.

    3. PRIMARY KEY

      • Combines NOT NULL and UNIQUE constraints to uniquely identify each row in a table.
      • Example: student_id in a "Students" table.

    4. FOREIGN KEY

      • Establishes a relationship between two tables by referencing the Primary Key of another table.
      • Example: course_id in an "Enrollments" table linking to the "Courses" table.

    5. CHECK

      • Ensures that all values in a column satisfy a specific condition.
      • Example: A marks column must have values greater than or equal to 0.

    6. DEFAULT

      • Assigns a default value to a column if no value is provided during insertion.
      • Example: A status column with a default value of "active".

    7. INDEX (Optional Constraint)

      • Improves query performance by creating an index on one or more columns.
      • Example: Adding an index to the email column for faster searches.

    8. DDL and DML commands ?

    DDL (Data Definition Language) and DML (Data Manipulation Language) are two types of SQL commands used to work with databases. Here's the difference:

    DDL (Data Definition Language)

    • Purpose: {Doesn't work with actual data} Defines and modifies the structure of database objects like tables, schemas, and indexes.
    • Commands:
      1. CREATE: Creates a new database object (e.g., table, view).
        Example: CREATE TABLE Students (id INT, name VARCHAR(50));
      2. ALTER: Modifies an existing object structure.
        Example: ALTER TABLE Students ADD age INT;
      3. DROP: Deletes a database object permanently.
        Example: DROP TABLE Students;
      4. TRUNCATE: Removes all rows from a table but keeps its structure.
        Example: TRUNCATE TABLE Students;

    DML (Data Manipulation Language)

    • Purpose: {Work with actual data }Manipulates the data stored in database tables (inserts, updates, or deletes records).
    • Commands:
      1. INSERT: Adds new records to a table.
        Example: INSERT INTO Students (id, name) VALUES (1, 'John');
      2. UPDATE: Modifies existing records.
        Example: UPDATE Students SET name = 'Jane' WHERE id = 1;
      3. DELETE: Removes specific records from a table.
        Example: DELETE FROM Students WHERE id = 1;

    Key Differences:

    DDLDML
    Affects the structure of the database.Affects the data inside the database.
    Auto-committed (changes are permanent).Requires explicit commit or rollback.
    Examples: CREATE, ALTER, DROP.Examples: INSERT, UPDATE, DELETE.




    9. The difference between the GROUP BY and ORDER BY clauses in SQL is as follows:

    GROUP BY Clause

    • Purpose: Used to group rows with the same values in specified columns into summary rows, often used with aggregate functions like SUM(), AVG(), COUNT(), etc.
    • Focus: Groups data based on column values.
    • Example:
      sql
      SELECT department, COUNT(employee_id) 
FROM employees 
GROUP BY department;
      • Groups employees by department and counts the number of employees in each department.

    ORDER BY Clause

    • Purpose: Used to sort the rows in the result set based on one or more columns, either in ascending (ASC) or descending (DESC) order.
    • Focus: Sorts the final result.
    • Example:
      sql==>>
      SELECT employee_id, name, salary 
FROM employees 
ORDER BY salary DESC;
      • Sorts employees by their salary in descending order.

    Key Differences:

    GROUP BYORDER BY
    Groups rows based on column values.Sorts rows in ascending or descending order.
    Often used with aggregate functions.Does not require aggregate functions.
    The number of output rows is reduced.The number of output rows remains the same.
    Example: Summarizing .Example: Sorting data.
     

    10. Difference between WHERE and HAVING Clause: 

    WHERE Clause

    • Purpose: Filters rows before grouping or applying aggregate functions.
    • Works On: Individual rows of the table.
    • Cannot Use: Aggregate functions directly.
    • Example:
      sql

      SELECT * 
FROM employees 
WHERE salary > 50000;
      • Filters rows where the salary is greater than 50,000.

    HAVING Clause

    • Purpose: Filters groups of data after grouping is performed.
    • Works On: Groups created by the GROUP BY clause.
    • Can Use: Aggregate functions.
    • Example:
      sql

      SELECT department, AVG(salary) 
FROM employees 
GROUP BY department 
HAVING AVG(salary) > 60000;
      • Filters departments where the average salary is greater than 60,000.

    Key Differences:

    WHERE ClauseHAVING Clause
    Filters rows before grouping.Filters groups after grouping.
    Cannot be used with aggregate functions.Can use aggregate functions like SUM, AVG, etc.
    Used without GROUP BY.Usually used with GROUP BY.
    Example: Filters individual rows.Example: Filters grouped results.




    11. What Are Aggregate Functions in SQL and Can You Provide Exanples ? 

    Aggregate Functions in SQL are used to perform calculations on a set of rows and return a single value. These functions are commonly used with the GROUP BY clause to summarize data.

    Common Aggregate Functions

    1. SUM()

      • Calculates the total of numeric values in a column.
      • Example:
        sql
        SELECT SUM(salary) AS total_salary 
FROM employees;

    2. AVG()

      • Calculates the average of numeric values in a column.
      • Example:
        sql
        SELECT AVG(salary) AS average_salary 
FROM employees;

    3. COUNT()

      • Counts the number of rows or non-NULL values in a column.
      • Example:
        sql
        SELECT COUNT(employee_id) AS total_employees 
FROM employees;

    4. MAX()

      • Returns the maximum value in a column.
      • Example:
        sql
        SELECT MAX(salary) AS highest_salary 
FROM employees;

    5. MIN()

      • Returns the minimum value in a column.
      • Example:
        sql
        SELECT MIN(salary) AS lowest_salary 
FROM employees;

    Key Points:

    • Aggregate functions ignore NULL values (except COUNT(*) which includes all rows).
    • Commonly used with GROUP BY to calculate values for grouped data .


    12. Explain Indexing in SQL and What do you mean by Clustered Index ?

    What is Indexing in SQL?

    Indexing in SQL is a technique used to speed up the retrieval of data from a database table by creating pointers to the rows in the table. It works like a book's index, allowing quick access to specific rows without scanning the entire table.

    • Purpose: To enhance query performance and reduce query execution time.
    • Impact: While indexing improves read operations, it can slow down write operations (like INSERT, UPDATE, DELETE) because the index also needs to be updated.

    Types of Indexes in SQL:

    1. Clustered Index
    2. Non-Clustered Index
    3. Unique Index, Composite Index, etc.

    What is a Clustered Index?

    A Clustered Index determines the physical order of data in a table. It sorts and stores the rows of the table in the order of the index key. Each table can have only one clustered index because the data rows themselves can only be sorted in one order.

    • Key Characteristics:

      • The table itself is re-organized to match the index.
      • Faster for range-based queries (e.g., finding all rows between two values).
      • Automatically created when a Primary Key is defined (unless specified otherwise).

    • Example:
      Suppose you have a "Students" table:

      sql
      CREATE TABLE Students (
    student_id INT PRIMARY KEY, 
    name VARCHAR(50)
);
      • Here, student_id becomes the Clustered Index, and the rows in the table will be physically sorted by student_id.

    Clustered vs Non-Clustered Index:

    Clustered IndexNon-Clustered Index
    Sorts and stores data physically in table.Does not alter physical order of data.
    Only one per table.Multiple indexes can exist per table.
    Faster for range queries.Slower than clustered for range queries.

    Advantages of Clustered Index:

    • Speeds up data retrieval for queries that involve range scans or sorting.
    • Suitable for columns that are frequently used in searches or join conditions.

    Disadvantages:

    • Slower INSERT, UPDATE, and DELETE operations due to data reordering.
    • Not suitable for columns with frequent updates.
          

    13. What is Normalization and Expalin the Different types of Normal Forms.

    What is Normalization?

    Normalization is the process of organizing data in a database to eliminate redundancy (duplicate data) and ensure data dependencies are logical. The goal is to reduce anomalies during data insertion, deletion, and updating while improving data integrity.

    Types of Normal Forms (NF):

    1. First Normal Form (1NF):

      • Ensures that each column contains atomic (indivisible) values.
      • Eliminates repeating groups or arrays within columns.

    2. Second Normal Form (2NF):

      • Builds on 1NF and removes partial dependency (when a non-key column depends on part of a composite primary key).

    3. Third Normal Form (3NF):

      • Builds on 2NF and removes transitive dependency (when a non-key column depends on another non-key column).

    4. Boyce-Codd Normal Form (BCNF):

      • A stricter version of 3NF, ensuring that every determinant is a superkey.

    5. Fourth Normal Form (4NF):

      • Builds on BCNF and removes multi-valued dependencies.

    Key Points:

    • Each higher normal form addresses specific data anomalies.
    • Normalization improves data consistency, integrity, and efficiency.



    14.What do you mean by Union and Union All Operator in SQL ?

    Union and Union All Operators in SQL

    Both UNION and UNION ALL are used to combine the results of two or more SELECT statements into a single result set. However, they have key differences.

    1. UNION Operator

    • Purpose: Combines the result sets of two or more SELECT statements and removes duplicate rows from the final output.
    • Key Characteristics:
      • Eliminates duplicates automatically.
      • Performs a sorting operation to remove duplicates, which may reduce performance for large datasets.
    • Syntax:
      sql
      SELECT column1, column2 
FROM table1
UNION
SELECT column1, column2 
FROM table2;

    2. UNION ALL Operator

    • Purpose: Combines the result sets of two or more SELECT statements including duplicates.
    • Key Characteristics:
      • Retains all rows, including duplicates.
      • Faster than UNION because it does not perform sorting to eliminate duplicates.
    • Syntax:
      sql
      SELECT column1, column2 
FROM table1
UNION ALL
SELECT column1, column2 
FROM table2;

    Key Differences:

    FeatureUNIONUNION ALL
    DuplicatesRemoves duplicates.Retains duplicates.
    PerformanceSlower due to duplicate removal.Faster as no duplicate removal.
    Use CaseWhen unique results are needed.When duplicates are acceptable.

    Key Points:

    • Both operators require the SELECT statements to have the same number of columns and the columns to have compatible data types.
    • Use UNION for unique results and UNION ALL for faster performance with duplicates.


    15. How can you find the second highest salary in a Table ?

    To find the second highest salary in a table, you can use several approaches depending on the SQL dialect and requirements. Below are three common methods:

    1. Using DISTINCT and ORDER BY with LIMIT

    • Applicable for MySQL or databases that support LIMIT.
    • Query:
      sql
      SELECT DISTINCT salary 
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
      • Explanation:
        • DISTINCT removes duplicate salaries.
        • ORDER BY salary DESC sorts salaries in descending order.
        • LIMIT 1 OFFSET 1 skips the highest salary and fetches the next one.

    2. Using Subquery

    • Works in most SQL databases.
    • Query:
      sql
      SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
      • Explanation:
        • The inner query (SELECT MAX(salary) FROM employees) fetches the highest salary.
        • The outer query fetches the maximum salary that is less than the highest salary, i.e., the second highest.

    3. Using DENSE_RANK() (for databases that support window functions)

    • Applicable for SQL Server, PostgreSQL, Oracle, etc.
    • Query:
      sql
      SELECT salary AS second_highest_salary
FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
    FROM employees
) ranked
WHERE rank = 2;
      • Explanation:
        • The DENSE_RANK() function assigns a rank to each distinct salary in descending order.
        • The outer query filters the rows where the rank is 2, giving the second highest salary.

    Key Points:

    • Ensure there are at least two distinct salary values in the table; otherwise, the query might return NULL or no result.
    • Use DENSE_RANK() over RANK() if duplicate salaries exist, as RANK() may skip ranks when duplicates are present.


    16. What are Views in SQL ?

    A View in SQL is a virtual table based on the result of an SQL query. It does not store data itself but displays data from one or more underlying tables. Views simplify complex queries by saving them as reusable database objects.

    Key Features of Views:

    1. Virtual Table: A view does not store data but fetches it dynamically from the underlying tables when accessed.
    2. Simplifies Queries: Allows the reuse of complex SQL queries by saving them as a single object.
    3. Data Security: Restricts access to specific columns or rows, enhancing data security.
    4. Updatable: Some views allow data updates, which reflect back in the underlying table.

    Syntax to Create a View:

    sql
    CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

    Example:

    sql
    CREATE VIEW employee_view AS
SELECT employee_id, name, salary
FROM employees
WHERE department = 'IT';
    • This creates a view called employee_view that contains employee details from the IT department.

    Types of Views:

    1. Simple View:
      • Based on a single table.
      • Does not use aggregate functions.
    2. Complex View:
      • Based on multiple tables or includes aggregate functions and joins.

    Advantages of Views:

    • Simplifies complex queries for end-users.
    • Improves data security by restricting access to sensitive data.
    • Provides a consistent interface even if the underlying table structure changes.

    Limitations of Views:

    • Views can become slow for complex queries with large datasets.
    • Not all views are updatable (e.g., views with joins, aggregate functions, or groupings).



    17. How can you convert a Text into Date Format ? Consider Text as "20-11-2024" ?

    1. MySQL

    Use the STR_TO_DATE() function.

    sql
    SELECT STR_TO_DATE('20-11-2024', '%d-%m-%Y') AS formatted_date;
    • Explanation:
      • %d → Day (2 digits).
      • %m → Month (2 digits).
      • %Y → Year (4 digits).




    18.What are Triggers in SQL ? 

    What are Triggers in SQL?

    A Trigger in SQL is a special type of stored procedure that automatically executes in response to certain events on a table or view. Triggers are primarily used to enforce business rules, maintain data integrity, and perform automated actions.

    Key Characteristics of Triggers:

    1. Event-Driven: Triggers activate automatically when specific database events occur, such as INSERT, UPDATE, or DELETE.
    2. Associated with Tables/Views: Triggers are tied to a specific table or view.
    3. No Manual Execution: Unlike stored procedures, triggers cannot be manually invoked; they run automatically when the specified event occurs.

    Types of Triggers:

    1. Based on Timing:

      • BEFORE Trigger: Executes before the triggering event occurs.
      • AFTER Trigger: Executes after the triggering event occurs.

    2. Based on Event:

      • INSERT Trigger: Activates when a new row is inserted into a table.
      • UPDATE Trigger: Activates when a row is updated.
      • DELETE Trigger: Activates when a row is deleted.

    Comments

    Post a Comment