SQL Interview Questions and Answers

Answer: SQL stands for Structured Query Language, which is used to manage relational databases. It is used for creating, modifying, and querying databases. The main components of SQL are Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), and Transaction Control Language (TCL).

Answer: There are four types of SQL commands: Data Definition Language (DDL): Used to create, modify, and delete database objects. Data Manipulation Language (DML): Used to insert, update, and delete data from tables. Data Control Language (DCL): Used to grant or revoke access to users. Transaction Control Language (TCL): Used to control the transactions in a database.

Answer: DDL (Data Definition Language) commands are used to create, alter, and drop objects in a database, while DML (Data Manipulation Language) commands are used to insert, update, and delete data from tables. DCL (Data Control Language) commands are used to control the access to the database objects and to grant or revoke permissions to users.

Answer: Normalization is the process of organizing data in a database to minimize redundancy and dependency. There are different types of normalization:

• First Normal Form (1NF): It ensures that each column in a table contains atomic values.
• Second Normal Form (2NF): It ensures that each non-key column is dependent on the primary key.
• Third Normal Form (3NF): It ensures that each non-key column is dependent only on the primary key and not on other non-key columns.

Answer: A primary key is a unique identifier for each record in a table. It is used to enforce the integrity of the data and to ensure that there are no duplicate records. A foreign key is a column that refers to the primary key of another table. It is used to establish a relationship between two tables.

Answer: An index is a data structure used to speed up the retrieval of data from a table. It is created on one or more columns of a table and allows the database to find and retrieve the data more quickly.

Answer: The SELECT statement is used to query data from one or more tables in a database. It allows you to retrieve specific columns and rows of data based on various conditions.

Answer: The basic syntax of the SELECT statement is:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

The SELECT clause specifies the columns to be retrieved, the FROM clause specifies the table(s) from which to retrieve the data, and the WHERE clause specifies the condition(s) for selecting the data.

Answer: The DISTINCT keyword is used in SQL to retrieve only unique values from a column or set of columns in a table. When the DISTINCT keyword is used, the database engine will compare each value in the specified column(s) and return only the distinct values.

Here's an example query using the DISTINCT keyword:

SELECT DISTINCT column_name
FROM table_name;

This query will return only the unique values in the specified column_name from the table_name table.

Note that the DISTINCT keyword applies to all columns in the SELECT clause, so if you want to retrieve distinct values for multiple columns, you need to include all of them in the SELECT clause.

Answer: The WHERE clause is a clause in SQL that is used to filter rows based on a specified condition or set of conditions. It is used with the SELECT, UPDATE, and DELETE statements.

Answer: The basic syntax of the WHERE clause is:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

The WHERE clause is used to filter rows based on a specified condition or set of conditions. The condition can include one or more operators such as comparison operators (=, <, >, <=, >=), logical operators (AND, OR, NOT), and other operators (IN, BETWEEN, LIKE, IS NULL).

The AND operator is used in SQL to combine multiple conditions and return only those rows that satisfy all the conditions. It is used in the WHERE clause of an SQL query to specify multiple conditions that must all be true for a row to be returned.

The OR operator is used in SQL to combine multiple conditions and return any rows that satisfy at least one of the conditions. For example:

SELECT * FROM customers
WHERE country = 'USA' OR country = 'Canada';

This SQL query will return all customers who are either from the USA or Canada.

The NOT operator is used in SQL to negate a condition and return rows that do not satisfy the condition. For example:

SELECT * FROM customers
WHERE NOT country = 'USA';

This SQL query will return all customers who are not from the USA.

You can use parentheses to group conditions together and create more complex conditions in an SQL query.

For example:

SELECT * FROM customers
WHERE (country = 'USA' AND state = 'CA') OR (country = 'Canada' AND city = 'Toronto');

This SQL query will return all customers who are either from the USA and California, or from Canada and Toronto.

Some common mistakes to avoid when using AND, OR, and NOT operators in SQL queries include forgetting to use parentheses to group conditions together.

Yes, you can sort the results of an SQL query by multiple columns by specifying multiple column names in the ORDER BY clause separated by commas. For example:

SELECT * FROM customers
ORDER BY last_name ASC, first_name ASC;

This SQL query will return all customers in ascending order by last name and then by first name.

To use the INSERT INTO statement in SQL, you specify the name of the table you want to insert data into, followed by the column names (optional) and the values you want to insert. For example:

INSERT INTO customers (first_name, last_name, email)
VALUES ('John', 'Doe', 'johndoe@example.com');

This SQL query will insert a new row into the "customers" table with the values 'John', 'Doe', and 'johndoe@example.com' in the columns "first_name", "last_name", and "email", respectively.

To check for NULL values in a SQL query, you can use the IS NULL operator. For example:

SELECT *
FROM customers
WHERE email IS NULL;

This SQL query will return all rows from the "customers" table where the "email" column contains a NULL value.

To update all rows in a table, you can omit the WHERE clause from the UPDATE statement. For example:

UPDATE customers
SET status = 'active';

This SQL query will update the value of the "status" column in all rows of the "customers" table to 'active'.

A SQL function is a subprogram that can be called by a SQL statement to perform a specific task. It returns a value or values, and can accept input parameters to modify its behavior.

The syntax for calling a SQL function is:

SELECT function_name(parameter1, parameter2, ...);

For example, to call the MAX function to return the maximum value in a column:

SELECT MAX(column_name) FROM table_name;

• COUNT: returns the number of rows in a table that meet a certain condition
• SUM: returns the sum of values in a column
• AVG: returns the average of values in a column
• MIN: returns the minimum value in a column
• MAX: returns the maximum value in a column
• CONCAT: concatenates two or more strings
• SUBSTRING: extracts a substring from a string

The % wildcard character represents any number of characters, including zero characters. For example, the pattern 'b%' would match any string that starts with the letter 'b', such as 'blue', 'big', and 'box'.

The _ wildcard character represents a single character. For example, the pattern 'a_c' would match any string that starts with the letter 'a', followed by any single character, and ends with the letter 'c', such as 'abc', 'arc', and 'alc'.

The SQL BETWEEN statement is used to select values within a specified range. It allows you to retrieve all records whose value falls within the specified range, inclusive of the endpoints.

Using the SQL BETWEEN statement is equivalent to using the >= and <= operators. However, the SQL BETWEEN statement is more concise and easier to read, especially when the range is large.

SQL aliases are used to give a table or column a temporary name in a SQL query. This makes the query more concise and easier to read.

An SQL inner join is used to combine rows from two or more tables based on a matching column. It only returns rows where the join condition is true for both tables.

Imagine you have two tables of information, one with information about people and another with information about their pets. The people table has columns for their name and age, and the pets table has columns for the pet's name, type, and the owner's name.

A LEFT JOIN looks at both tables and finds all the rows in the left table (in this case, the people table) and combines them with the matching rows from the right table (in this case, the pets table) based on a specified column (in this case, the owner's name).

For example, if you wanted to see a list of all the people and their pets, even if some people don't have pets, you would use a LEFT JOIN to combine the people table with the pets table based on the owner's name. The resulting table would have all the people from the people table, and any pets that belong to them, but would also show NULL values for any people who don't have pets.

So, a LEFT JOIN is like taking the left table and adding any matching rows from the right table, but also including any rows from the left table that don't have matches in the right table (and filling in NULL values for any missing information from the right table).

A SQL LEFT JOIN returns all rows from the left table and matching rows from the right table. If there is no matching row in the right table, NULL values are used for all the columns of the right table.

The difference between a SQL LEFT JOIN and a SQL INNER JOIN is that a LEFT JOIN returns all rows from the left table and matching rows from the right table, while an INNER JOIN only returns rows where there is a match between both tables.

Yes, more than two tables can be joined using a SQL LEFT JOIN. For example, to join three tables "customers", "orders", and "products" based on the "customer_id" and "product_id" columns:

SELECT customers.customer_name, orders.order_id, products.product_name
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id
LEFT JOIN products
ON orders.product_id = products.product_id;

You have two tables of information, One with information about people and another with information about their pets. The people table has columns for their name and age, and the pets table has columns for the pet's name, type, and owner's name.

A RIGHT JOIN is similar to a LEFT JOIN, but it starts with the right table (in this case, the pets table) and combines it with the matching rows from the left table (in this case, the people table) based on a specified column (in this case, the owner's name).

For example, if you wanted to see a list of all the pets and their owners, even if some pets don't have owners, you would use a RIGHT JOIN to combine the pets table with the people table based on the owner's name. The resulting table would have all the pets from the pets table, and any people that own them, but would also show NULL values for any pets that don't have owners.

So, a RIGHT JOIN is like taking the right table and adding any matching rows from the left table, but also including any rows from the right table that don't have matches in the left table (and filling in NULL values for any missing information from the left table).

For Example, we have two tables of information, one with information about students and another with information about their grades. The student's table has columns for their name and grade level, and the grades table has columns for the student's name, subject, and grade.

A FULL OUTER JOIN combines the left and right tables together based on a specified column (in this case, the student's name), but includes all rows from both tables, even if there are no matches.

For example, if you wanted to see a list of all students and their grades, including any students who don't have grades and any grades that don't have students, you would use a FULL OUTER JOIN to combine the student's table with the grades table based on the student's name. The resulting table would have all the students from the student's table, and any grades that belong to them, but would also include any grades that don't have students and any students who don't have grades.

So, a FULL OUTER JOIN combines the left and right tables together, showing all rows from both tables and filling in NULL values for any missing information. It's like taking a LEFT JOIN and a RIGHT JOIN and combining them together into one table.

A SQL UNION is used to combine the results of two or more SELECT statements into a single result set.

A SQL GROUP BY clause is used to group rows that have the same values in a specific column or columns and then perform calculations or aggregations on those groups.

For example, if you wanted to see the total sales for each customer, you would use a GROUP BY clause to group the rows in the orders table by the customer column. You could then use an aggregate function, such as SUM, to calculate the total amount of each customer's orders.

SQL aggregate functions are functions used to perform calculations on a set of values and return a single value as a result. These functions are often used with the GROUP BY clause to calculate aggregate values for groups of rows.

A stored procedure is a pre-written SQL code that is saved in the database server and can be executed by calling it from an application or another SQL script. It is a set of SQL statements that perform a specific task or a set of tasks.

For example, if you have a database that contains customer information, you could create a stored procedure that takes a customer ID as an input parameter, retrieves all the orders placed by that customer, and calculates the total amount of all orders.

Once the stored procedure is created, you can call it from your application or another SQL script, passing the customer ID as a parameter, and it will return the total amount of orders for that customer.

A SQL ALTER statement is used to modify the structure of an existing database object, such as a table, index, or view.

For example, if you have a table of products and you want to add a new column for the product weight, you could use the ALTER statement to add the new column to the table.

Some common types of SQL constraints include:

• NOT NULL: ensures that a column cannot contain a null value
• UNIQUE: ensures that each row in a column contains a unique value
• PRIMARY KEY: creates a unique index for a column or set of columns and enforces the uniqueness of the values in that column or set of columns
• FOREIGN KEY: ensures that the values in a column match the values in another column, typically in a different table
• CHECK: ensures that the values in a column meet a specific condition or range of conditions

A SQL CHECK constraint is a constraint that is used to limit the values that can be inserted into a column or group of columns based on a specific condition or set of conditions.

For example, if you have a table of students and you want to ensure that the age of each student is between 18 and 25, you could use a CHECK constraint to enforce this condition: