SQL interview Questions / Data Analyst Interview questions

 

Q1:- What are the different types of statements supported by SQL

Ans:- SQL (Structured Query Language) is a programming language used to manage data stored in relational database management systems (RDBMS). There are several types of statements supported by SQL, including:

1.       SELECT statement: used to retrieve data from a database.

2.       INSERT statement: used to add new records to a database table.

3.       UPDATE statement: used to modify existing records in a database table.

4.       DELETE statement: used to delete records from a database table.

5.       CREATE statement: used to create database objects such as tables, views, and indices.

6.       ALTER statement: used to modify the structure of a database object.

7.       DROP statement: used to delete database objects such as tables, views, and indices.

8.       TRUNCATE statement: used to delete all records from a table, but unlike the DELETE statement, it preserves the structure of the table.

MERGE statement: used to merge data from multiple tables or sources into a single table.

These are the most commonly used SQL statements, but there are many others that are supported by different RDBMSs.

Q2:- How do you use the DISTINCT statement? What is its Use?

Ans:- The DISTINCT keyword is used in the SELECT statement to return only distinct (unique) values from the selected columns. For example:

CODE→ SELECT DISTINCT column1, column2 FROM table_name;

This SELECT statement will retrieve unique combinations of column1 and column2 from the table_name table. If there are multiple rows with the same values in column 1 and column 2, only one of those rows will be returned in the result set.

The DISTINCT keyword is often used to eliminate duplicate rows in the result set of a SELECT statement. For example, if you have a table of customer orders and you want to get a list of the unique products that have been ordered, you can use the DISTINCT keyword to eliminate duplicate rows from the result set.

CODE→ SELECT DISTINCT product FROM orders;

This SELECT statement will return a list of all the unique products that have been ordered, without any duplicates.

Q3:- What are the different Clauses used in SQL?

Ans:- In SQL, a clause is a part of a SQL statement that performs a specific task. Some common clauses used in SQL are:

1.       SELECT clause: specifies the columns that you want to retrieve from the database.

2.       FROM clause: specifies the table(s) from which you want to retrieve the data.

3.       WHERE clause: specifies a condition that must be met in order for a row to be included in the result set.

4.       GROUP BY clause: groups rows that have the same values in the specified column(s).

5.       HAVING clause: specifies a condition that must be met in order for a group to be included in the result set.

6.       ORDER BY clause: sorts the result set by the specified column(s).

7.       LIMIT clause: limits the number of rows returned in the result set.

These are some of the most commonly used clauses in SQL, but there are many others that are specific to different RDBMSs or that perform more specialized tasks.

Q4:- Why do use SQL constraints?

Ans:- SQL constraints are used to specify rules for the data in a table. If a constraint is specified, the data must follow the rule in order to be inserted or updated in the table.

Constraints are used to ensure the integrity and accuracy of the data in a table. They can be used to prevent data that is not valid or that does not follow certain rules from being inserted or updated in the table. For example, you can use a constraint to ensure that all values in a column are unique or to ensure that all values in a column are within a certain range.

Constraints can also be used to enforce relationships between tables. For example, you can use a foreign key constraint to ensure that the values in a column in one table are also present in a column in another table.

Overall, constraints are an important tool for maintaining the integrity and accuracy of the data in a database.

Q5:- Which Constraints we can use while creating a database in SQL?

Ans:- There are several constraints that you can use while creating a database in SQL:

1.       NOT NULL: specifies that a column cannot contain a NULL value.

2.       UNIQUE: specifies that all values in a column must be unique.

3.       PRIMARY KEY: specifies that a column or set of columns is the primary key for the table. A primary key is a column or set of columns that is used to uniquely identify each row in the table.

4.       FOREIGN KEY: specifies that a column is a foreign key, which means that it is a field that refers to the primary key of another table.

5.       CHECK: this specifies a condition that must be met in order for a row to be inserted or updated in the table.

6.       DEFAULT: specifies a default value for a column.

These are some of the most commonly used constraints in SQL, but there are many others that are specific to different RDBMSs or that perform more specialized tasks.

Q6:- What are the different joins used in SQL?

Ans:-In SQL, a join is used to combine rows from two or more tables based on a related column between them. There are several types of joins that you can use in SQL:

1.       INNER JOIN: returns rows that have matching values in both tables.

2.       LEFT JOIN: returns all rows from the left table, and any matching rows from the right table. If there is no match, NULL values are returned for the right table's columns.

3.       RIGHT JOIN: returns all rows from the right table, and any matching rows from the left table. If there is no match, NULL values are returned for the left table's columns.

4.       FULL OUTER JOIN: returns all rows from both tables, whether or not there is a match. If there is no match, NULL values are returned for the non-matching columns.

5.       SELF JOIN: a join that is used to compare values in a column to other values in the same column in the same table.

These are the most commonly used types of joins in SQL, but there are many others that are specific to different RDBMSs or that perform more specialized tasks.

Q6:-How many Aggregate functions are available in SQL?

Ans:- There are several aggregate functions available in SQL, including:

1.       AVG: returns the average value of a set of values.

2.       COUNT: returns the number of rows in a table or the number of non-NULL values in a column.

3.       MAX: returns the maximum value in a set of values.

4.       MIN: returns the minimum value in a set of values.

5.       SUM: returns the sum of a set of values.

These are the most commonly used aggregate functions in SQL, but there are many others that are specific to different RDBMSs or that perform more specialized tasks.

Q7:- What are the Scalar functions in SQL?

Ans:- In SQL, a scalar function is a function that operates on a single value and returns a single value. Some examples of scalar functions in SQL are:

1.       ABS: returns the absolute value of a numeric expression.

2.       LEN: returns the length of a string.

3.       LOWER: converts a string to lowercase.

4.       UPPER: converts a string to uppercase.

5.       RAND: returns a random number between 0 and 1.

These are just a few examples of scalar functions in SQL. There are many others that are specific to different RDBMSs or that perform more specialized tasks. Scalar functions can be used in the SELECT, WHERE, GROUP BY, and HAVING clauses of a SELECT statement, as well as in the SET clause of an UPDATE statement.

Q8:- Which one is correct for the group by modifier?

1. Select Group_contact(username), height FROM users GroupBottom of Form users GROUP BY height WITH ROLLUP

2. Select GROUP_CONTACT(username) with ROLLUP, heigh FROM users GROUP BY height With ROLLUP

3. SELECT GROUP_CONCAT (username WITH ROLLUP), heigh FROM users GROUP BY height WITH ROLLUP

4. SELECT COUNT (username) OVER (PARTITION WITH ROLLUP) From users

Ans:- SELECT GROUP_CONCAT (username WITH ROLLUP), height FROM users GROUP BY height WITH ROLLUP is the correct statement for using the GROUP BY modifier in MySQL.

This statement will group the rows in the user's table by height and use the GROUP_CONCAT function to concatenate the values in the username column for each group. It will also generate additional rows that represent the grand total (all rows) and subtotals for each unique value in height.

Q9:- Which one is correct for MYSQL: Window function Frame Specification?

1. SELECT SUM (amount) OVER (ROWS BETWEEN UNKNOWN PRECEDING AND CURRENT ROW) FROM transactions

2. SELECT SUM(amount) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM transactions

3. SELECT SUM (amount) OVER (ROWS BETWEEN UNKNOWN PRECEDING AND UNBOUNDED ROW) FROM transactions

4. All expressions are correct

Ans:- SELECT SUM(amount) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM transactions is the correct statement for using a window function frame specification in MySQL.

This statement will use the SUM function to calculate the sum of the amount column for each row in the transactions table, using all rows from the start of the result set to the current row as the frame for the function.

The other expressions are not correct. The UNKNOWN keyword is not valid in a frame specification, and the BETWEEN keyword is spelled incorrectly.

Q9:- Which one is correct MYSQL: Row Functions

1. SELECT ROW_NUMBER() OVER() FROM transactions

2. SELECT FOUND_ROWS() FROM transactions

3. SELECT FOUND_ROWS() OVER() FROM transactions

4. SELECT ROW_NUMBER() FROM transactions

5. SELECT ROW_COUNT() FROM transactions

Ans:- SELECT ROW_NUMBER() OVER() FROM transactions is a correct statement for using the ROW_NUMBER row function in MySQL.

This statement will use the ROW_NUMBER function to generate a row number for each row in the transactions table. The OVER() clause specifies that the function should be applied to the entire result set.

The other expressions are not correct. FOUND_ROWS is not a row function, and ROW_COUNT is not a function in MySQL.

Q10:- Which one is correct MYSQL: CHECK Constraint

1. Foreign key referential actions (ON UPDATE, ON DELETE) are prohibited on columns used in CHECK constraints.

2. The CHECK constraint is used to limit the range of values that can be placed in a column.

3. If the constraint expression evaluates to a data type that differs from the declared column type, implicit coercion to the declared type occurs according to the usual MYSQL type conversion rules.

 4. None of the above, all expressions are correct.

Ans:- All of the expressions are correct.

It is not allowed to specify foreign key referential actions (ON UPDATE, ON DELETE) on columns that are used in CHECK constraints in MySQL.

The CHECK constraint is used to limit the range of values that can be placed in a column by specifying a Boolean expression. If the expression evaluates to TRUE, the value is allowed; if it evaluates to FALSE, the value is not allowed.

If the constraint expression evaluates to a data type that differs from the declared column type, MySQL will try to implicitly coerce the value to the declared type according to the usual type conversion rules.

For example, consider the following CREATE TABLE statement:

CODE→ CREATE TABLE students ( id INT PRIMARY KEY, name VARCHAR(255) NOT NULL, age INT CHECK (age > 0 AND age < 130) );

This statement creates a table called students with three columns: id, name, and age. The age column has a CHECK constraint that allows only values between 0 and 130 (inclusive). If you try to insert a value outside of this range, the constraint will prevent the insertion and return an error.