SQL is the standard language for dealing with Relational Databases. SQL can be used to insert, search, update, and delete database records. SQL can do lots of other operations, including optimizing and maintenance of databases.
This post will help you to brush up on your SQL skills, regain your confidence and be job-ready!
Here are some common interview questions that you might encounter during an interview involving SQL:
1. What is SQL and what is it used for?
Ans: SQL stands for Structured Query Language. SQL lets you access and manipulate databases. SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987.
SQL is widely used in business and in other types of database administration. It is often the default tool for “operating” on the conventional database, to alter tabled data, retrieve data or otherwise manipulate an existing data set.
2. Can you explain the difference between a primary key and a foreign key?
Ans:
| Primary key | Foreign key |
| A primary key generally focuses on the uniqueness of the table. It assures the value in the specific column is unique. | A foreign key is generally used to build a relationship between the two tables. |
| The primary key doesn’t allow null values. | Foreign key accepts multiple null values. |
| In the primary key, the value cannot be removed from the parent table. | In this, the value can be deleted from the child table. |
| Its restriction can be completely defined on the temporary tables. | Its restriction cannot be defined on the global or local temporary tables. |
3. How do you create a new table in a database?
Ans: The CREATE TABLE statement is used to create a new table in a database.
Syntax :
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);The column parameters specify the names of the columns of the table.
The datatype parameter specifies the type of data the column can hold (e.g. varchar, integer, date, etc.).
4. How do you use the GROUP BY clause in a SELECT statement?
Ans :
The GROUP BY Statement in SQL is used to arrange identical data into groups with the help of some functions. i.e if a particular column has same values in different rows then it will arrange these rows in a group. Important Points:
- GROUP BY clause is used with the SELECT statement.
- In the query, GROUP BY clause is placed after the WHERE clause.
- In the query, GROUP BY clause is placed before ORDER BY clause if used any.
- In the query , Group BY clause is placed before Having clause .
- Place condition in the having clause
5. What is a Recursive Stored Procedure?
Ans : Recursive stored procedure refers to a stored procedure which calls by itself until it reaches some boundary condition. This recursive function or procedure helps the programmers to use the same set of code n number of times.
6. What is Data Integrity?
Ans : Data integrity refers to the overall accuracy, completeness, and reliability of data. It can be specified by the lack of variation between two instances or consecutive updates of a record, indicating that your information is error-free.
7.How do you use the UPDATE statement to modify data in a database?
Ans : The UPDATE statement in SQL is used to update the data of an existing table in database. We can update single columns as well as multiple columns using UPDATE statement as per our requirement.
Syntax :
UPDATE table_name SET column1 = value1, column2 = value2,...
WHERE condition;
table_name: name of the table
column1: name of first , second, third column....
value1: new value for first, second, third column....
condition: condition to select the rows for which the
values of columns needs to be updated.8. How do you use the DELETE statement to remove data from a database?
Ans : The DELETE Statement in SQL is used to delete existing records from a table. We can delete a single record or multiple records depending on the condition we specify in the WHERE clause.
Syntax:
DELETE FROM table_name WHERE some_condition;
table_name: name of the table
some_condition: condition to choose particular record.9. Can you explain the difference between a subquery and a correlated subquery?
Ans :
| Subquery | Correlated subquery |
| A subquery can define as one query embedded in another query. | Correlated Subquery is a type of subquery. |
| Subquery is executed only once | Correlated Subquery is different from the normal subquery in terms of execution. |
| In a normal subquery, the outer query is dependent on the inner query for execution | Each time the inner query gets executed it goes to the outer query for values |
| The inner query is only executed once to return the values required by the outer query to execute | The Correlated subquery is slower compared to a normal subquery. |
10. What is a Self-Join?
Ans : A self join is a join in which a table is joined with itself (which is also called Unary relationships), especially when the table has a FOREIGN KEY which references its own PRIMARY KEY. To join a table itself means that each row of the table is combined with itself and with every other row of the table.
11. What is a Cross-Join?
Ans: In SQL, CROSS JOINs are used to combine each row of one table with each row of another table, and return the Cartesian product of the sets of rows from the tables that are joined. When to use the CROSS JOIN? The CROSS JOIN query in SQL is used to generate all combinations of records in two tables.
12. What is a UNIQUE constraint?
Ans : The UNIQUE constraint ensures that all values in a column are different. Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint.
13.What is the difference between SQL and MySQL?
Ans :
| SQL | MySQL |
| SQL is a query programming language that manages RDBMS. | MySQL is a relational database management system that uses SQL. |
| SQL is primarily used to query and operate database systems. | MySQL allows you to handle, store, modify and delete data and store data in an organized way. |
| SQL follows a simple standard format without many or regular updates. | MySQL has numerous variants and gets frequent updates. |
| SQL supports only a single storage engine. | MySQL offers support for multiple storage engines along with plug-in storage, making it more flexible. |
14. What are ACID properties? Is PostgreSQL compliant with ACID?
Ans :
ACID stands for Atomicity, Consistency, Isolation, Durability. They are database transaction properties which are used for guaranteeing data validity in case of errors and failures.
- Atomicity: This property ensures that the transaction is completed in all-or-nothing way.
- Consistency: This ensures that updates made to the database is valid and follows rules and restrictions.
- Isolation: This property ensures integrity of transaction that are visible to all other transactions.
- Durability: This property ensures that the committed transactions are stored permanently in the database.
PostgreSQL is compliant with ACID properties.
15. What do you understand by command enable-debug?
Ans : he command enable-debug is used for enabling the compilation of all libraries and applications. When this is enabled, the system processes get hindered and generally also increases the size of the binary file. Hence, it is not recommended to switch this on in the production environment. This is most commonly used by developers to debug the bugs in their scripts and help them spot the issues.
It’s important to remember that these are just examples, and the specific questions you’ll be asked during your interview will depend on the role you’re applying for and the interviewer. Be prepared to answer both general and role-specific questions.
