Header Ads

Header ADS

SQL Interview Question

August 09, 2023 0

SQL Interview Question


Topic Introduction: Here we will show provable interview questions on SQL for Oracle Developer. We have divided it into two parts. One is theory another is Query.

Theory


What is the difference between SQL and MySQL?
What do you mean by DBMS and RDBMS?
DBMS stands for Database Management System and RDBMS for the Relational Database Management system.
What does schema mean?
A schema is a collection of database objects. A schema is owned by a database user and has the same name as that user. Schema objects are logical structures created by users.
Explain the order of execution of SQL.
1. From
2. Where
3. Group by
4. Having
5. Select
6. Order by
7. Limit
What do you mean by table, field, and records in SQL?
A table has records (rows) and fields (columns). Fields have different data types, such as text, numbers, dates, and hyperlinks. A record: Contains specific data, like information about a particular employee or a product.
What is Constraint?
SQL constraints are used to specify rules for the data in a table. Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.
What are Constraints?
NOT NULL
UNIQUE
DEFAULT
INDEX
CHECK
What are the types of keys?
Primary Key
Unique Key
Foreign Key
Composite key
What is the Primary key?
In Oracle, the Primary key is the column that has unique values, and it cannot be null. In a table, there can be only one primary key.
What is a Unique key?
In Oracle, the Unique key is the column that must be unique values, and it can be null. In a table, there can be multiple Unique key.
What is a Composite key?
In Oracle, the composite key can be defined as a combination of multiple columns, and these columns are used to identify all the rows that are involved uniquely.
What is the difference between the Primary key and the Unique key?
The primary key cannot be null on the other hand the Unique key can be null. In a table, there can be only one primary key but the Unique key can be multiple.
What is the difference between the Primary key and the Secondary key?
A primary key is the field in a database that is the primary key used to uniquely identify a record in a database. A secondary key is an additional key, or alternate key, which can be use in addition to the primary key to locate specific data.
What is the datatype in Oracle SQL?
What is the difference between the CHAR and VARCHAR2 datatype in SQL?
CHAR stores only fixed-length character string data types whereas VARCHAR stores variable-length string where an upper limit of length is specified.
What is the difference between varchar and nvarchar?
Similar for char and nchar?
What are the differences between like and wildcard?
Like operator is used to search a defined string pattern in the column and return those rows. This will give you students whose name starts with N. Where as the WILDCARD operator is used to search a more specific pattern in the column and return the matching rows.
List the different types of relationships in SQL.
One-to-One
One-to-Many
Many-to-One
Many-to-Many
Self-Referencing Relationships
What are the differences between Case and Decode in Oracle SQL?
DECODE is a function. CASE is a statement. DECODE is not used in the WHERE clause. CASE is used in the WHERE clause.
What is the usage of the NVL() function?
NVL replaces a null with a string.
Explain character manipulation functions? Explains its different types in SQL.
CONCAT
SUBSTR
LENGTH
INSTR
LPAD
RPAD
TRIM
REPLACE
What is the join and, join type?
Join
Left  Join
Right Join
Full Join

Different types of operators?
Unary and Binary Operators.
Arithmetic Operators: (+, -, *, /)
Comparison Operators: ( =, >,>=,<,<=, <>/!=/^=, Between, IN, LIKE, IS NULL, IS NOT NULL, etc).
Logical Operators: (And, OR, NOT)
Set operators: (UNION, UNION ALL, MINUS, INTERSECT).
Some other Built-In Operators.
User-defined operators.
What is UNION on SQL?
What is UNION ALL on SQL?
What is INTERSECT on SQL?
What is MINUS on SQL?
What is the difference between UNION and UNION ALL?
UNION ALL keeps all of the records from each of the original data sets, UNION removes any duplicate records.
What is the use of the Group By in SQL?
The GROUP BY Statement is used to arrange identical data into groups with the help of some functions.
What is the difference between where and having clauses?
The WHERE clause is used to specify a condition for filtering records before any groupings are made, while the HAVING clause is used to specify a condition for filtering values from a group.

What are some of the most essential aggregate functions?
AVG
COUNT
MIN
MAX
SUM
What are the different types of subqueries?
Single-row subqueries
Multi-row subqueries
Correlated subqueries
What is faster between CTE and Subquery?
CTEs are much more readable than subqueries when you're writing a complex report.
What is the role of indexes? 
Indexes are used to retrieve data from the database more quickly than otherwise.
What is a type of Index?
Index Characteristics.
B-Tree Indexes.
Bitmap Indexes.
Function-Based Indexes.
Application Domain Indexes.
Index Storage.
What are the key differences between clustered and non-clustered indexes?
A clustered index is used to define the order or to sort the table or arrange the data by alphabetical order just like a dictionary. A non-clustered index collects the data at one place and records it at another place. It is faster than a non-clustered index. It is slower than the clustered index.
What are the different types of SQL commands?
Data Definition Language (DDL)
Data Manipulation Language (DML)
Data Control Language (DCL)
Transaction Control Language (TCL)
Data Query Language (DQL)
What is the difference between DELETE and TRUNCATE?
The DELETE command is used to delete particular records from a table. The TRUNCATE command is used to delete the complete data from the table. After DELETE we can rollback data but TRUNCATE can not be rollback.
What is the difference between DML, DDL, and DCL?
What is the difference between normalization and denormalization?
How do you delete a column?
What is the view?
Do we use the variable in view?
No, we can't use variables in a view.
What are the limitations of view?
The maximum number of tables that can be referenced in the definition of a view is 61. View processing is not optimized: It is not possible to create an index on a view. Indexes can be used for views processed using the merge algorithm. You cannot pass parameters to SQL views. Views cannot be created on Temporary Tables.
Explain all types of window functions or analytical functions.
RANK 
DENSE_RANK
ROW_NUM
LEAD
LAG
What is the MERGE statement in Oracle SQL?
Use the MERGE statement to select rows from one or more sources for update or insertion into a table or view



Query



  SELECT d.department_name,
         SUM (e.salary) dept_salary,
         ROUND (ratio_to_report (SUM (e.salary)) OVER () * 100, 2) salary_pct
    FROM employees e, departments d
   WHERE e.department_id = d.department_id
GROUP BY d.department_name;
SELECT *
  FROM (SELECT last_name,
               job_id,
               salary,
               DENSE_RANK () OVER (ORDER BY salary DESC) ranking
          FROM employees)
 WHERE ranking < 6  
CREATE TABLE new_table
  AS (SELECT * 
      FROM old_table WHERE 1=2);
SELECT LAST_NAME, rn
  FROM (SELECT LAST_NAME,
               ROW_NUMBER () OVER (PARTITION BY LAST_NAME ORDER BY ROWID) rn
          FROM employees)
 WHERE rn > 1
DELETE FROM employees
      WHERE ROWID IN
                (SELECT rid
                   FROM (SELECT ROWID
                                    rid,
                                ROW_NUMBER () 
                                OVER (PARTITION BY LAST_NAME ORDER BY ROWID)
                                    rn
                           FROM employees)
                  WHERE rn <> 1);
SELECT SYSDATE + 45 / 86400 "SYSDATE+45"
FROM DUAL;
Total Salary Without Using Group Function
 WITH
    DT
    AS
        (
        SELECT TO_DATE(TO_CHAR(:EDATE, 'DD-MON-RR HH24:MI:SS'),'DD-MON-RR HH24:MI:SS')- TO_DATE(TO_CHAR(:SDATE, 'DD-MON-RR HH24:MI:SS'),'DD-MON-RR HH24:MI:SS') INTER FROM DUAL
        )
    --SELECT TRUNC (24 * INTER) ||' Hour '  || ROUND((24 * INTER - TRUNC (24 * INTER)) * 60)||' Minute' HOUR_MIN
    SELECT TRUNC (24 * INTER) HOURS, ROUND((24 * INTER - TRUNC (24 * INTER)) * 60) MINS
    FROM DT;
Even numbers:  MOD (column name, 2) = 0
SELECT employee_id
  FROM EMPLOYEES
 WHERE MOD (employee_id, 2) = 0;

Odd numbers:   MOD (column name, 2) = 1
SELECT employee_id
  FROM EMPLOYEES
 WHERE MOD (employee_id, 2) = 1;
SELECT e.last_name,
       e.department_id,
       d.department_id,
       d.department_name
  FROM employees e, departments d
 WHERE e.department_id = d.department_id(+)
UNION ALL
SELECT e.last_name,
       e.department_id,
       d.department_id,
       d.department_name
  FROM employees e, departments d
 WHERE e.department_id(+) = d.department_id AND e.department_id IS NULL;
The second-highest salary of an employee
Write retention query in SQL?
Write query for a cumulative sum in SQL?
  SELECT department_name,
         COUNT (employee_id)                              Department_Emp,
         SUM (COUNT (*)) OVER (ORDER BY department_name)  Total_Emp,
         SUM (salary)                                     Department_Sal,
         SUM (SUM (salary)) OVER (ORDER BY department_name) Total_Sal
    FROM employees e, departments d
   WHERE e.department_id = d.department_id
GROUP BY department_name;




Tags:

No comments

Subscribe to: Post Comments ( Atom )
Theme images by Deejpilot. Powered by Blogger.