200+ SQL Developer Interview Questions and Answers

I have used multiple functions in SSAS for various purposes.

• Some commonly used functions in SSAS include SUM, AVG, COUNT, MIN, MAX, and DISTINCT.

• Functions can be used in measures, calculated columns, and calculated tables.

• Examples of functions used in SSAS include SUM([Sales Amount]), COUNT([Customer ID]), and AVG([Rating]).

Primary key is a unique identifier for each record in a database table.

• Primary key ensures each record in a table is uniquely identified.

• It must contain unique values and cannot have NULL values.

• Primary key can be a single column or a combination of columns.

• Example: 'id' column in a 'users' table can be a primary key.

SQL Developer interview question on removing duplicates, finding third highest salary, normalization, and indexes.

• To remove duplicates, use the DISTINCT keyword or GROUP BY clause

• To find third highest salary, use the LIMIT keyword with OFFSET 2

• Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity

• Indexes are used to improve query performance by creating a data structure that allows for faster data retrieval

• Drawbacks of in...read more

Stored Procedures are used to perform a set of actions, while Functions return a value.

• Stored Procedures can perform multiple actions and may or may not return a value

• Functions always return a value and are used for calculations or data retrieval

• Stored Procedures can have input/output parameters, while Functions only have input parameters

• Stored Procedures are called using EXECUTE command, while Functions are called in SELECT statement

DBMS stands for Database Management System, which is a software system that allows users to define, create, maintain and control access to databases.

• DBMS is a software system that allows users to interact with databases.

• It helps in defining, creating, maintaining, and controlling access to databases.

• Examples of DBMS include MySQL, Oracle Database, Microsoft SQL Server.

SQL is a programming language used for managing data in relational databases. Functions are reusable code blocks. Joins combine data from multiple tables. LIKE operator is used for pattern matching.

• SQL is a language for managing data in databases

• Functions are reusable code blocks

• Joins combine data from multiple tables

• LIKE operator is used for pattern matching, e.g. SELECT * FROM table WHERE column LIKE '%keyword%'

SQL concepts - Procedure, Functions, Joins, Sub queries, Constraints, Triggers, Package

• Procedure - a set of SQL statements that perform a specific task

• Functions - a set of SQL statements that return a single value

• Joins - used to combine rows from two or more tables based on a related column

• Sub queries - a query within another query

• Constraints - rules that enforce data integrity

• Triggers - a set of SQL statements that automatically execute in response to certain events

• Package -...read more

Object-oriented programming concepts that help in organizing and structuring code.

• Encapsulation: Bundling data and methods together in a class.

• Inheritance: Creating new classes from existing ones, inheriting their properties and behaviors.

• Polymorphism: Ability of objects to take on many forms, allowing different classes to be used interchangeably.

• Abstraction: Hiding complex implementation details and providing a simplified interface.

• Encapsulation: Binding data and methods tog...read more

Performance tuning of SQL query

• Identify slow queries using profiling tools

• Optimize query structure and use indexes

• Reduce data retrieval by filtering and limiting results

• Avoid using subqueries and nested queries

• Use appropriate data types and avoid unnecessary conversions

Different types of joins include inner join, outer join, left join, and right join. Self join is when a table is joined with itself.

• Inner join: Returns rows when there is a match in both tables

• Outer join: Returns all rows when there is a match in one of the tables

• Left join: Returns all rows from the left table and the matched rows from the right table

• Right join: Returns all rows from the right table and the matched rows from the left table

• Self join: Joining a table with itsel...read more

A set is a collection of unique values in SQL. Types of joins include inner, outer, left, right. Types of sets include union, intersect, except. WHERE is used for filtering rows, HAVING is used for filtering groups.

• Set is a collection of unique values in SQL

• Types of joins: inner, outer, left, right

• Types of sets: union, intersect, except

• WHERE is used for filtering rows

• HAVING is used for filtering groups

The NVL() function is used to replace NULL values with a specified value.

• NVL() is commonly used in SQL queries to handle NULL values.

• It takes two arguments: the value to be checked and the replacement value.

• If the value is NULL, the function returns the replacement value.

• If the value is not NULL, the function returns the original value.

• Example: SELECT NVL(column_name, 'N/A') FROM table_name;

Functions return a value while stored procedures do not. Functions can be used in SQL statements, while stored procedures cannot.

• Functions return a single value, while stored procedures do not necessarily return any value.

• Functions can be used in SQL statements like SELECT, WHERE, etc., while stored procedures cannot be used in such statements.

• Functions are called using SELECT statement, while stored procedures are called using EXECUTE or EXEC statement.

• Functions cannot modif...read more

Query to display nth highest salary in SQL

• Use the ORDER BY clause to sort salaries in descending order

• Use the DISTINCT keyword to eliminate duplicates

• Use the LIMIT clause to specify the nth highest salary

Joins are used to combine data from two or more tables based on a related column.

• Types of joins include inner join, left join, right join, and full outer join.

• Inner join returns only the matching rows from both tables.

• Left join returns all rows from the left table and matching rows from the right table.

• Right join returns all rows from the right table and matching rows from the left table.

• Full outer join returns all rows from both tables, with NULL values for non-matching rows...read more

Set operators are used to combine the result sets of two or more SELECT statements.

• Set operators include UNION, UNION ALL, INTERSECT, and EXCEPT.

• UNION combines the results of two SELECT statements and removes duplicates.

• UNION ALL combines the results of two SELECT statements and includes duplicates.

• INTERSECT returns only the common rows between two SELECT statements.

• EXCEPT returns only the rows from the first SELECT statement that are not in the second SELECT statement.

Code to generate Fibonacci series

• Start with 0 and 1 as the first two numbers

• Add the previous two numbers to get the next number in the series

• Repeat until desired number of terms is reached

Synonyms are database objects that allow users to create aliases for other database objects.

• Synonyms can be used to simplify complex queries by providing a shorter name for a table or view.

• They can also be used to provide a layer of abstraction between the user and the underlying database objects.

• Synonyms can be created for tables, views, stored procedures, and other database objects.

• They can be used to reference objects in other databases or even on other servers.

• Synonyms ca...read more

I cannot disclose my personal email id for privacy reasons.

• I prefer to communicate through official channels provided by the company.

• Sharing personal email id can lead to security risks.

• I can provide an alternate email id for official communication.

Joins are used to combine rows from two or more tables based on a related column between them.

• Types of joins include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

• Optimization techniques for joins include using indexes on join columns, avoiding unnecessary joins, and using appropriate join algorithms like nested loops or hash joins.

• Example: SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;

A unique key is a column or a set of columns in a database table that uniquely identifies each row.

• A unique key ensures that no two rows in a table have the same values for the specified column(s).

• It is used to enforce data integrity and prevent duplicate entries.

• A table can have multiple unique keys, but each unique key can only have one row with a specific set of values.

• Unique keys can be used as a reference for relationships between tables.

A view in SQL is a virtual table based on the result set of a SELECT query. There are different types of views like simple, complex, indexed, etc.

• Views are used to simplify complex queries by storing them as a virtual table.

• Types of views include simple views, complex views, indexed views, etc.

• Views can be used to restrict access to certain columns or rows of a table.

• Views can improve performance by pre-computing expensive queries and storing the results.

A database is a collection of organized data that can be easily accessed, managed, and updated. Data is any information that can be stored and processed.

• A database is a software system that stores and manages data

• Data is any information that can be stored and processed, such as text, numbers, images, and videos

• Examples of databases include MySQL, Oracle, and SQL Server

• Examples of data include customer names, addresses, and purchase history

Difference between delete vs drop vs truncate

• DELETE is used to remove specific rows from a table

• DROP is used to remove an entire table from the database

• TRUNCATE is used to remove all rows from a table

• DELETE can be rolled back, DROP and TRUNCATE cannot be rolled back

• DELETE is slower than TRUNCATE and DROP

Constraints are rules that are enforced on data columns in a table to ensure data integrity and accuracy.

• Constraints can be used to enforce uniqueness, primary keys, foreign keys, and check conditions on data.

• Examples include UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK constraints.

• Constraints help maintain data integrity by preventing invalid data from being inserted into tables.

Join is used to combine rows from two or more tables based on a related column between them.

• Join is used to retrieve data from multiple tables in a single query.

• There are different types of joins like inner join, left join, right join, and full outer join.

• Example: SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;

Query to find sales from the last quarter

• Use the SUM() function to calculate total sales

• Filter the data based on the date range of the last quarter

• Group by the relevant time period (e.g. month) to get a breakdown of sales

A view is a virtual table created by a query. It can be used to simplify complex queries, provide security, and improve performance.

• Views can hide complexity by encapsulating multiple tables into a single virtual table.

• Views can restrict access to certain columns or rows, providing security.

• Views can improve performance by pre-computing joins or aggregations.

• Example: CREATE VIEW vw_employee AS SELECT emp_id, emp_name FROM employees WHERE emp_dept = 'IT';

Indexes are used to improve query performance. Clustered index determines physical order of data in a table.

• Indexes are used to quickly retrieve data from a table

• Clustered index determines the physical order of data in a table

• Clustered index is created on the primary key column by default

• Example: A clustered index on a customer ID column in a sales table would physically order the data by customer ID

An index is a database object that improves the speed of data retrieval operations on a table.

• Indexes are used to quickly locate data without having to search every row in a table.

• They are created on one or more columns of a table.

• Indexes can be clustered or non-clustered.

• Clustered indexes determine the physical order of data in a table.

• Non-clustered indexes are separate structures that contain a copy of the indexed columns and a pointer to the actual data.

• Indexes can improve...read more

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity.

• Normalization involves breaking down data into smaller, more manageable tables

• It helps in reducing data redundancy by storing data in a structured manner

• Normalization ensures data integrity by minimizing data anomalies

• There are different normal forms such as 1NF, 2NF, 3NF, BCNF, and 4NF

Synonyms are alternate names for database objects. They are used to simplify queries and provide security.

• Synonyms are used to provide an alternate name for a database object such as a table, view, stored procedure, or function.

• They can simplify queries by providing a shorter or more meaningful name for an object.

• Synonyms can also be used to provide security by allowing users to access an object without knowing its actual name or location.

• They are created using the CREATE SYN...read more

COALESCE is a function that returns the first non-null value in a list of expressions.

• COALESCE is used to simplify complex SQL statements.

• It can be used with any data type.

• It takes two or more arguments and returns the first non-null value.

• If all arguments are null, it returns null.

• Example: COALESCE(column1, column2, 'N/A') will return the value of column1 if it's not null, else column2 if it's not null, else 'N/A'.

Different types of joins in SQL and their explanations

• Inner Join - returns rows when there is at least one match in both tables

• Left Join - returns all rows from the left table and the matched rows from the right table

• Right Join - returns all rows from the right table and the matched rows from the left table

• Full Outer Join - returns rows when there is a match in one of the tables

• Cross Join - returns the Cartesian product of the two tables

SQL join types allow combining data from multiple tables based on a specified condition.

• Inner Join: Returns rows that have matching values in both tables.

• Left Join: Returns all rows from the left table and the matched rows from the right table.

• Right Join: Returns all rows from the right table and the matched rows from the left table.

• Full Outer Join: Returns all rows when there is a match in either left or right table.

• Self Join: Joining a table with itself to combine rows base...read more

Joins are used in SQL to combine rows from two or more tables based on a related column between them.

• There are different types of joins: inner join, left join, right join, and full outer join.

• Inner join returns only the matching rows from both tables.

• Left join returns all the rows from the left table and the matching rows from the right table.

• Right join returns all the rows from the right table and the matching rows from the left table.

• Full outer join returns all the rows fro...read more

The syntax of a procedure in SQL is similar to that of a function, but it does not return a value.

• CREATE PROCEDURE procedure_name

• AS

• BEGIN

• -- SQL statements

• END;

SQL is a programming language used to manage and manipulate relational databases. Joins are used to combine data from multiple tables.

• SQL stands for Structured Query Language

• It is used to create, modify, and query databases

• Joins are used to combine data from two or more tables based on a related column

• Types of joins include inner join, left join, right join, and full outer join

• Example: SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column

Use conditional logic to check if table exists before inserting data.

• Check if table exists using system tables like information_schema.tables

• Use dynamic SQL to conditionally insert data if table exists

• Handle errors gracefully if table does not exist

Calculate running total and 2nd highest salary for each department in SQL.

• Use window functions like ROW_NUMBER() and SUM() to calculate running total.

• Use a subquery to find the 2nd highest salary for each department.

Stored procedures are used to store SQL queries for reuse. Cursors are used to iterate through a result set. Joins combine data from multiple tables. CTEs are temporary result sets.

• Stored procedures are used to store SQL queries for reuse

• Cursors are used to iterate through a result set

• Joins combine data from multiple tables

• Common Table Expressions (CTEs) are temporary result sets

Constraints are rules that are applied to a table column or a group of columns.

• Constraints ensure data accuracy and consistency in a database.

• They can be used to enforce rules like uniqueness, primary key, foreign key, etc.

• Constraints can be added when creating a table or altered later using ALTER TABLE statement.

• Examples of constraints include NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY, etc.

Use a self join to find the manager of the employee.

• Join the employee table with itself on the manager_id and employee_id columns

• Select the manager's details based on the employee's manager_id

SUM in window functions calculates the sum of a specified column over a window of rows.

• Calculates the sum of a specified column over a window of rows

• Can be used with other window functions like ROW_NUMBER, RANK, etc.

• Example: SELECT SUM(sales) OVER (PARTITION BY department) AS department_total_sales FROM sales_data;

Use the INSERT INTO statement with multiple value sets separated by commas

• Use the INSERT INTO statement followed by the table name

• List the column names in parentheses after the table name

• Use the VALUES keyword followed by multiple value sets in parentheses, separated by commas

• Example: INSERT INTO table_name (column1, column2) VALUES (value1, value2), (value3, value4)

SQL constraints are rules that are applied to a table's columns to ensure data integrity.

• Constraints are used to enforce rules and restrictions on data in SQL tables.

• They help maintain data integrity by preventing invalid or inconsistent data from being inserted or updated.

• Common types of constraints include primary key, foreign key, unique, and check constraints.

• For example, a primary key constraint ensures that each row in a table has a unique identifier.

• Constraints can be ...read more

Different types of joins in SQL include inner join, outer join, left join, right join, and full join.

• Inner join: Returns rows when there is a match in both tables

• Outer join: Returns all rows from one table and only matching rows from the other table

• Left join: Returns all rows from the left table and the matched rows from the right table

• Right join: Returns all rows from the right table and the matched rows from the left table

• Full join: Returns rows when there is a match in one...read more