54 possible SQL questions that could boost your confidence for your next Database Developer job interview. Well, there are more, but this distilled set of questions covers all SQL functionalities which will enable you to get better prepared to face an interview. You could also enhance your SQL skills with the best SQL Tutorials recommended for you.
Here we go…
SQL Interview Questions
Question: What is the difference between DBMS and RDBMS?
Ans: DBMSs are software applications that help you build and maintain databases. RDBMS is a subset of DBMS and it is a database management system based on the relational model of the DBMS.
Question: Can we embed Pl/SQL in SQL? Justify your answers
Ans: PL/SQL is a procedural language and it has one or more SQL statements in it. So SQL can be embedded in a PL/SQL block however PL/SQL cannot be embedded in a SQL as SQL executes a single query at a time.
Question: What do you mean by data manipulation language – DML?
Ans: DML includes most common SQL statements to store, modify, delete and retrieve data. They are SELECT, UPDATE, INSERT and DELETE.
Question: What is a join in SQL? What are the types of joins?
Ans: A join is used to query data from multiple tables based on the relationship between the fields.
There are 4 types of joins.
- Inner Join.
Rows are returned when there is at least one match of rows between the tables.
- Right Join.
Right join returns all rows from the right table and those which are common between the tables. If there are no matching rows in the left table it will still return all the rows from the right table.
- Left Join.
Left join returns all rows from the Left table and those which are common between the tables. If there are no matching rows in the right table it will still return all the rows from the left table.
- Full Join.
Full join return rows when there are matching rows in any one of the tables. This means it returns all the rows from the left-hand side table and all the rows from the right-hand side table.
Question: What is the difference between CHAR and VARCHAR2 datatype in SQL?
Ans: CHAR is used to store fixed-length character strings and VARCHAR2 is used to store variable-length character strings
Question: What is a trigger?
Ans: Triggers are stored programs that get automatically executed when an event such as INSERT, DELETE, UPDATE(DML) statement occurs. Triggers can also be evoked in response to Data definition statements(DDL) and database operations, for example, SERVERERROR, LOGON.
Question: What are SQL constraints give examples
Ans: SQL constraints are used to specify the limit on the data type of a table. They are like rules on columns of a table. Examples: NOT NULL, PRIMARY KEY.
Question: What are ACID properties in a transaction
Ans: In order to maintain consistency in a database ‘before and after’ transactions certain properties are followed. They are
- Atomicity: which means the transaction must happen fully and cannot be left midway.
- Consistency: To maintain integrity constraints hence valid data enters the database
- Isolation: Controls Concurrency
- Durability: Once a transaction is committed it remains committed
Question: What is SAVEPOINT in a transaction control
Ans: A SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain point without rolling back the entire transaction.
Question: What are the scalar functions in SQL? Give an example
Ans: Scalar Functions are used to return a single value based on the input values. Scalar Functions are as follows:
- UCASE(): Converts the specified field in upper case
- LCASE(): Converts the specified field in lower case
Question: What is a cursor and when do you use it?
Ans: A cursor is a database object which is used to manipulate data by traversing row by row in a result set. A cursor is used when you need to retrieve data, one row at a time from a result set and when you need to update records one row at a time
Question: What is a set-based solution?
Ans: Cursors operate on individual rows and in case of a set, it works on a resultant set of data which could be a table/view or a join of both. The resultant set is an output of a SQL query.
Question: What is a forward cursor?
Ans: Forward cursors support fetching of rows from start to end from a result set. You cannot go to the previous row in the result set.
Question: State one situation where the set-based solution is advantageous over the cursor-based solution
Ans: Set-based solutions provide better performance as they work on a result set and not on one row at a time. They are concise and more readable.
Question: What is normalization and what are the normal forms
Ans: Normalization is a process in database design to minimize data redundancy and dependency. The database is divided into two or more tables and relationships are defined between them.
- First Normal Form: Every record is unique in a table and is identified by a primary or a composite key
- Second Normal Form: The table must be in First Normal Form and it should have a single column as its primary key
- Third Normal Form: The table must be in Second Normal Form and must have no transitive functional dependencies. I.e a non-key column must not be dependent on another non-key column within the same table
Question: What is de-normalization and when do you go for it?
Ans: De-normalization is a technique sometimes used to improve performance so the table design allows redundant data to avoid complex joins. If the application involves heavy read operations then de-normalization is used at an expense of the write operations performance.
Question: What is a primary key, a foreign key and unique key
- Primary key is a field in the table which uniquely identifies a row. It cannot be NULL
- Foreign key is a field in one table which is a primary key in another table. A relationship is created between the two tables by referencing the foreign key of one table with the primary key of another table.
- Unique Key uniquely identifies a record in a table. There can be many unique key constraints defined on a table.
Question: What are clustered indices and non-clustered indices?
Ans: A table can have only one clustered index. In this type of index it reorders the table based on the key values and physically stores them in that order.
Non-clustered index does not have physical ordering of the data in the table it has a logical order.
Question: What is T-SQL?
Ans: It is an extension of SQL(Structured Query Language) developed by Sybase and used by Microsoft.
Question: What are system functions and give one example
Ans: System functions are operations performed on the database server and values are returned accordingly. Example @@ERROR – Returns 0 if the previous Transact-SQL statement encountered no errors. Otherwise returns an error number.
Question: What is a transaction log?
Ans: A log is an audit trail file where the history of actions executed by the DBMS is stored.
Question: How do you maintain database integrity where deletions from one table will automatically cause deletions in another table?
Ans: ON DELETE CASCADE is a command that is used when deletions happen in the parent table and all child records are automatically deleted and the child table is referenced by the foreign key in the parent table.
Question: What is the difference between SQL and MySQL
Ans: SQL is a structured query language used to access the DBMS whereas MYSQL is an Open Source Relational DBMS.
Question: Can we use TRUNCATE with a WHERE clause?
Ans: No we cannot use TRUNCATE with the WHERE clause.
Question: Define COMMIT
Ans: When a COMMIT is used in a transaction all changes made in the transaction are written into the database permanently.
Question: What does CHECK CONSTRAINT do?
Ans: Check Constraint limits the values that can enter a column in a database table. It is used as an integrity constraint check.
Question: What is a schema?
Ans: A schema is a collection of database objects in a database for a particular user/owner. Objects can be tables, views, indices and so on.
Question: How can you create an empty table from an existing table?
CREATE TABLE NEW_TABLE_NAME AS SELECT [column1, column2 ……column] FROM EXISTING_TABLE_NAME [WHERE ]
Question: What is a composite key?
Ans: When more than one column is used to define the primary key, it is called a composite key
Question: How do you sort records in a table?
Ans: The ORDER BY Clause is used to sort records in a table
Question: What is a shared lock?
Ans: When two transactions are granted read access to the same data they are given a shared lock. This enables reading the same data and data is not updated until the shared lock is released.
Question: What is a deadlock?
Ans: It is an unwanted situation where two or more transactions are waiting indefinitely for one another to release the locks.
Question: What is lock escalation?
Ans: Lock escalation is a process of converting row or page locks into table locks. It is an optimization technique used by RDBMS like SQL Server dynamically.
Question: What is SQL injection?
Ans: SQL injection is a code injection technique used to hack data-driven applications.
Question: What are views and why are they used?
Ans: SQL views are virtual tables created from one or more tables. Views are a subset of data hence it can limit the degree of exposure of data from the tables.
Question: How do we avoid getting duplicate entries in a query?
Ans: The Select DISTINCT is used to get distinct data from tables using a query
Question: Give an example of a comparison operator in SQL
Ans: EQUAL TO written as = is used to compare data values
Question: What is a Subquery?
Ans: A subQuery is a SQL query nested into a larger Query.
Question: What is a Non-correlated subquery
Ans: A Non-Correlated subquery is an independent query and the output of subquery is substituted in the main query.
Question: What is a SYSTEM Privilege?
Ans: Rights are given to a user usually by the DBA to perform a particular action on the database schema objects like creating tablespaces.
Question: What are Object Privileges?
Ans: An object-level privilege is a permission granted to a database user account or role to perform some action on a database object. These object privileges include SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX on tables and so on.
Question: What does the BCP command do?
Ans: The BCP (Bulk Copy) is a utility or a tool that exports/imports data from a table into a file and vice versa
Question: What is a NULL Value field?
Ans: A NULL value is a field with No Value.
Question: What does the VARIANCE function do?
Ans: This function returns the VARIANCE of a set of numbers
Question: What is the role of GRANT and REVOKE commands?
Ans: The GRANT command enables privileges on the database objects and the REVOKE command removes them. They are DCL commands
Question: What is a UNION operator?
Ans: The UNION operator combines the results of two or more Select statements by removing duplicate rows. The columns and the data types must be the same in the SELECT statements.
Question: Where are stored procedures stored in the database?
Ans: Stored Procedures are stored in the Data Dictionary of the Database.
Question: Can we call Stored Procedure inside Stored Procedure?
Ans: Yes we can call a stored procedure from another stored procedure. For example Procedure2 is the procedure which is called from Procedure1. Both Procedure1 and Procedure2 can have business logic implemented in it.
Create PROCEDURE Procedure1 AS BEGIN Exec Procedure2 END
Question: Does the data stored in the stored procedure increase access time or execution time? Explain
Ans: Data stored in stored procedures can be retrieved much faster than the data stored in the SQL database. Data can be precompiled and stored in stored procedures. This reduces the time gap between query and compiling as the data has been pre-compiled and stored in the procedure.
Question: Can a Stored Procedure contain a return value?
Ans: Procedures may or may not return values.
Question: Can a View be active if the Base table is dropped?
Ans: No, the view cannot be active if the parent table is dropped.
Question: What is a One-Many Relationship in SQL?
Ans: In a One-Many relationship, a record in One Table can be associated or related with Many records in another table.
Question: What is a Natural Join?
Ans: A Natural join by default is an Inner Join that creates an implicit join based on the common columns in the two tables being joined
Question: What is a Cross Join?
Ans: In a SQL cross join, a combination of every row from the two tables is included in the result set. This is also called Cross Product Join. For example, if table A has 10 rows and table B has 20 rows the result set will have 10 * 20 = 200 rows provided there is NO Where clause in the SQL statement.