Oracle-SQL-Interview Questions Set 02

21. What is a SQL comment?

A human-readable clarification on what a particular piece of code does. SQL code comments can be single-line (preceded by a double dash –) or span over multiple lines (as follows: /*comment_text*/). When the SQL engine runs, it ignores code comments. The purpose of adding SQL code comments is to make the code more comprehensive for those people who will read it in the future.

22. What is a SQL operator?

A reserved character, a combination of characters, or a keyword used in SQL queries to perform a specific operation. SQL operators are commonly used with the WHERE clause to set a condition (or conditions) for filtering the data.

23. What types of SQL operators do you know?

1. Arithmetic (+, -, *, /, etc.)
2. Comparison (>, <, =, >=, etc.)
3. Compound (+=, -=, *=, /=, etc.)
4. Logical (AND, OR, NOT, BETWEEN, etc.)
5. String (%, _, +, ^, etc.)
6. Set (UNION, UNION ALL, INTERSECT, and MINUS (or EXCEPT))

24. What is an alias? 

A temporary name given to a table (or a column in a table) while executing a certain SQL query. Aliases are used to improve the code readability and make the code more compact. An alias is introduced with the AS keyword:

SELECT col_1 AS column

FROM table_name;

25. What is a clause?

A condition imposed on a SQL query to filter the data to obtain the desired result. Some examples are WHERE, LIMIT, HAVING, LIKE, AND, OR, ORDER BY, etc.

26. What are some common statements used with the SELECT query?

The most common ones are FROM, GROUP BY, JOIN, WHERE, ORDER BY, LIMIT, and HAVING.

27. How to create a table?

 Using the CREATE TABLE statement. For example, to create a table with three columns of predefined datatypes, we apply the following syntax:

CREATE TABLE table_name (col_1 datatype, col_2 datatype,col_3 datatype);

28. How to update a table?

Using the UPDATE statement.

The syntax is:

  UPDATE table_name

  SET col_1 = value_1, column_2 = value_2

  WHERE condition;

29. How to delete a table from a database?

 Using the DROP TABLE statement. The syntax is: DROP TABLE table_name;.

30. How to get the count of records in a table?

 Using the COUNT() aggregate function with the asterisk passed as its argument: SELECT COUNT(*) FROM table_name;.

31. How to sort records in a table?

   Using the ORDER BY statement:

  SELECT * FROM table_name

  ORDER BY col_1;

We can specify that we need a descending order using the DESC keyword; otherwise, the order will be ascending by default. Also, we can sort by more than one column and specify for each one, ascending or descending order separately. For example:

  SELECT * FROM table_name

  ORDER BY col_1 DESC, col_3, col_6 DESC;

32. How to select all columns from a table?

Using the asterisk * with the SELECT statement. The syntax is: SELECT * FROM table_name;.

33. How to select common records from two tables?

  Using the INTERSECT statement:

  SELECT * FROM table_1

  INTERSECT

  SELECT * FROM table_1;

34. What is the DISTINCT statement and how do you use it?

This statement is used with the SELECT statement to filter out duplicates and return only unique values from a column of a table. The syntax is:

SELECT DISTINCT col_1

FROM table_name;

35. What are entities? Give some examples.

An entity is a real-world object, creature, place, or phenomenon for which the data can be gathered and stored in a database table. Each entity corresponds to a row in a table, while the table’s columns describe its properties. Some examples of entities are bank transactions, students in a school, cars sold, etc.

36. What are relationships? Give some examples.

Relationships are the connections and correlations between entities, basically meaning how two or more tables of a database are related to one another. For example, we can find an ID of the same client in a table on sales data and in a customer table.

37. What is NULL value? How is it different from zero or a blank space?

A NULL value indicates the absence of data for a certain cell of a table. Instead, zero is a valid    numeric value, and an empty string is a legal string of zero length.

38. What is a function in SQL, and why use functions?

A database object representing a set of SQL statements frequently used for a certain task. A function takes in some input parameters, performs calculations or other manipulations on them, and returns the result. Functions help improve code readability and avoid repetition of the same code snippets.

39. What types of SQL functions do you know?

Aggregate functions – work on multiple, usually grouped records for the provided columns of a table, and return a single value (usually by group).

Scalar functions – work on each individual value and return a single value. On the other hand, SQL functions can be built-in (defined by the system) or user-defined (created by the user for their specific needs).

40. What aggregate functions do you know?

 AVG() – returns the average value

 SUM() – returns the sum of values

 MIN() – returns the minimum value

 MAX() – returns the maximum value

 COUNT() – returns the number of rows, including those with null values

 FIRST() – returns the first value from a column

 LAST()– returns the last value from a column