Prepare your SQL interview in less than 10 minutes (2/2)

1. What is SELECT statement ?

Ans: SELECT operator in SQL is used to select data from a database. The data returned is in the form of table, called the result-set.

SELECT * FROM db_name.table_name;

2. What is GROUP BY and ORDER BY clause?

Ans: GROUP BY clause in SQL is used to group records with identical data. It is used with aggregate functions to result out summarized data from database.

SELECT table_id, COUNT(1) FROM db_name.table_name
GROUP BY table_id;

ORDER BY clause used to sort records in ascending or descending order (using ASC or DESC) based on particular column field.

SELECT * FROM db_name.table_name
ORDER BY table_id DESC;

3. Difference between WHERE and HAVING clause?

Ans: WHERE clause in SQL is used to filter records that are necessary, based on specific conditions.

SELECT * FROM db_name.table_name
WHERE column_name='value';

HAVING clause in SQL is used to filter records in combination with the GROUP BY clause. It is different from WHERE as WHERE clause cannot filter aggregated records.

SELECT table_id, COUNT(1) FROM db_name.table_name
GROUP BY table_id
HAVING column_name = 'value';

4. What are the aggregate functions?

Ans : Aggregate functions are used to perform operations on a collection of values to return a single scalar value. They are often used with the GROUP BY and HAVING clauses of the SELECT statement.

Following are the examples of aggregate functions :-

• AVG() — Calculates the average of a collection of values.
• COUNT() — Counts the total number of records in a specific table or view. (It ignore null values)
• MIN() — Calculates the minimum of a collection of values.
• MAX() — Calculates the maximum of a collection of values.
• SUM() — Calculates the sum of a collection of values.
• FIRST() — Fetches the first element in a collection of values.
• LAST() — Fetches the last element in a collection of values.

5. What are scalar functions?

Ans : A scalar function returns a single value based on the input value.

Following are the examples of scalar functions:-

• LEN() — Calculates the total length of the given column field.
• UCASE() — Converts a collection of string values to uppercase characters.
• LCASE() — Converts a collection of string values to lowercase characters.
• MID() — Extracts substrings from a collection of string values in a table.
• CONCAT() — Concatenates two or more strings.
• RAND() — Generates a random collection of numbers of a given length.
• ROUND() — Calculates the round-off integer value for a numeric field (or decimal point values).
• NOW() — Returns the current date & time.
• FORMAT() — Sets the format to display a collection of values

6. What is the wildcard for SQL?

Ans: A wildcard character is used to substitute one or more characters in a string. They are used with the LIKE operator, used in a WHERE clause to search for a specified pattern in a column.

SELECT * FROM students WHERE first_name NOT LIKE 'N%'

7. Write a query to INSERT data in table.

INSERT INTO table_name VALUES (value1, value2, value3,...., valueN)

8. Write a query to UPDATE data in table

UPDATE table_name
SET column_name = value
WHERE column_name1 = 'value'; (Use condition)

9. CASE statement in SQL.

Ans : The CASE statement used to apply conditions and returns a value when the condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.

If there is no ELSE part and no conditions are true, it returns NULL.

CASE
    WHEN condition_1 THEN result_1
    WHEN condition_2 THEN result_2
    .
    . 
    .
    WHEN condition_N THEN result_N
    ELSE result
END;

10. what is IN operator?

Ans: The IN operator allows you to specify multiple values in WHERE clause.

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

That’s it for SQL preparation questions.

Hope this will help you to crack your SQL Interview. I’ll discuss more strategies in a future blog post!

Do like, follow for upcoming posts.