This article will provide you with a list of real-world Oracle SQL interview questions that have been asked at companies such as Google, Oracle, Amazon, and Microsoft, among others. Each question has a perfectly written answer right next to it, saving you time while you prepare for your interview. This will assist you in brushing up on your SQL skills, regaining trust, and preparing for a job!
Q1. What exactly is SQL?
Ans: The SQL is a term for Structured Query Language. SQL is a query language that is used to manage relational databases and perform a variety of data manipulation operations on them. It’s a database language that can be used to create, delete, fetch, and modify rows, among other things. It is also pronounced as sequel.
Q2. What are Aggregate Functions?
Ans: An aggregate function is a scalar function that performs operations on a set of values and returns a single scalar value. The SELECT statement’s GROUP BY and HAVING clauses are often used for aggregate functions. The following are some of the most commonly used SQL aggregate functions:
- AVG() is a function that calculates the average of a set of values.
- COUNT() is a function that counts the total number of records in a table or view.
- MIN() – Finds the lowest value in a set of values.
- MAX() – Finds the highest value in a set of values.
- SUM() – Computes the sum of a set of values.
- FIRST() – It will return the first value in a set of values.
- LAST() – Retrieves the last value in a list of values.
Q3. How do I make empty tables with the same structure as a table that already exists?
Ans: Using the INTO operator to fetch the records of one table into a new table while setting a WHERE clause to false for all records, it is possible to create empty tables with the same structure. As a result, SQL creates a new table with a duplicate structure to accept the fetched data, but nothing is inserted into the new table because the WHERE clause is active.
CREATE TABLE TABLE_NAME
AS SELECT * FROM EMP
Q4. What exactly is a SELECT statement?
Ans: In SQL, the SELECT operator is used to retrieve data from a database. The data is saved in a result table known as the result-set.
SELECT * FROM apps.EMP;
Q5. What do the statements TRUNCATE, DELETE, and DROP mean?
Ans: TRUNCATE: The TRUNCATE command is used to remove all of the rows from a table and permanently free up the space it occupies.
DELETE: To delete rows from a table, use the DELETE expression.
DROP: To delete an object from the database, use the DROP command. When you drop a table, all of its rows are removed from the database, as well as the table’s structure.
Q6. What is the difference between a DBMS and an RDBMS?
Ans: Following are the major differences between DBMS and RDBMS
- RDBMS stores data in the form of tables, while DBMS stores data in the form of files.
- Single users are supported by DBMS, while multiple users are supported by RDBMS.
- Client-server architecture is not supported by DBMS, but it is supported by RDBMS.
- The software and hardware requirements for DBMS are poor, whereas the requirements for RDBMS are higher.
- Data redundancy is normal in DBMSs, while keys and indexes do not enable data redundancy in RDBMSs.
Q7. What is the difference between the NVL and NVL2 functions?
Ans: NVL(expr1, expr2): NVL() transforms a null value to a valid value in SQL.
The NVL2 function evaluates the first expression (expr1, expr2, expr3). The NVL2 function returns the second expression if the first expression is not null.
Q8. What is the SQL DECODE function?
Ans: The DECODE function in Oracle lets us apply procedural if-then-else logic to our queries. DECODE compares each search value to the expression one by one. The Oracle Database returns the corresponding result if expression is equal to a search. If no match is found, the default value is used.
Q9. What is the SQL INSTR and SUBSTR function?
Ans: The Oracle INSTR function is used to find the position of a substring in a string and search for it in the string. If the function finds a substring that is equal to substring, it returns an integer indicating the position of the substring’s first character. The function returns zero if no such substring is found.
Q10. What is SQL ‘Group by’ and ‘Having’ Clause?
Ans: Group By: To group similar data into groups, the SQL GROUP BY clause is used in combination with the SELECT statement. In a SELECT sentence, the GROUP BY clause comes after the WHERE clause and before the ORDER BY clause.
Having: The SELECT statement’s HAVING clause is an optional clause. It’s used to filter the GROUP BY clause’s returned groups of rows. As a result, the HAVING clause is often used in combination with the GROUP BY clause.