SQL - interview questions Flashcards
Q: What are the Constraints?
A: Constraints are used to specify the limit on the data type of the table. It can be specified while creating or altering the table statement. The sample of constraints are: • NOT NULL • CHECK • DEFAULT • UNIQUE • PRIMARY KEY • FOREIGN KEY
Q: What do you mean by data integrity?
A: Data Integrity defines accuracy as well as the consistency of the data stored in a database. It also defines integrity constraints to enforce business rules on the data when it is entered into an application or a database.
Q: What is an Index?
A: An index refers to a performance tuning method of allowing faster retrieval of records from the table. An index creates an entry for each value and hence it will be faster to retrieve data.
Q: Explain the different types of Normalization.
A: There are many successive levels of normalization. These are called normal forms. Each consecutive normal form depends on the previous one. The first three normal forms are usually adequate.
- First Normal Form (1NF) – No repeating groups within rows
- Second Normal Form (2NF) – Every non-key (supporting) column value is dependent on the whole primary key.
- Third Normal Form (3NF) – Dependent solely on the primary key and no other non-key (supporting) column value.
Q: What is ACID property in a database?
A: ACID stands for Atomicity, Consistency, Isolation, and Durability. It is used to ensure that the data transactions are processed reliably in a database system.
Atomicity: Atomicity refers to the transactions that are completely done or failed where transaction refers to a single logical operation of a data. It means if one part of any transaction fails, the entire transaction fails and the database state is left unchanged.
Consistency: Consistency ensures that the data must meet all the validation rules. In simple words, you can say that your transaction never leaves the database without completing its state.
Isolation: The main goal of isolation is concurrency control.
Durability: Durability means that if a transaction has been committed, it will occur whatever may come in between such as power loss, crash or any sort of error.
Q: Write an SQL query to get the third-highest salary of an employee from employee_table?
A: SELECT TOP 1 salary FROM( SELECT TOP 3 salary FROM employee_table ORDER BY salary DESC) AS emp ORDER BY salary ASC;
Q: Write an SQL query to find the names of employees start with ‘A’?
A: The LIKE operator of SQL is used for this purpose. It is used to fetch filtered data by searching for a particular pattern in where clause. The Syntax for using LIKE is,
SELECT column1,column2 FROM table_name WHERE column_name LIKE pattern;
LIKE: operator name
pattern: exact value extracted from the pattern to get related data in result set.
Q: What do you mean by data definition language?
A: Data definition language or DDL allows you to execute queries like CREATE, DROP and ALTER. That is those queries which define the data.
Q: What is the difference between primary key and unique constraints?
A: Primary key cannot have a NULL value, the unique constraints can have NULL values. There is only one primary key in a table, but there can be multiple uniques constraints. The primary key creates the clustered index automatically but the unique key does not.
Q: What do you mean by foreign key?
A: A Foreign key is a field that can uniquely identify each row in another table. And this constraint is used to specify a field as a foreign key. That is this field points to the primary key of another table. This usually creates a kind of link between the two tables.
Q: What is the difference between DELETE and TRUNCATE statements?
DELETE: Delete command is used to delete a row in a table.
You can rollback data after using the delete statement. It is slower than the truncate statement
Truncate: Truncate is used to delete all the rows from a table. It is faster. Then delete
Q: What is a Relationship and what are they?
A: Relation or links are between entities that have something to do with each other. Relationships are defined as the connection between the tables in a database. There are various relationships, namely: • One to One Relationship. • One to Many Relationship. • Many to One Relationship. • Self-Referencing Relationship.
Q: How can you insert NULL values in a column while inserting the data?
A: NULL values can be inserted in the following ways:
• Implicitly by omitting column from column list.
• Explicitly by specifying NULL keyword in the VALUES clause
Q: What is the difference between ‘HAVING’ CLAUSE and a ‘WHERE’ CLAUSE?
A: HAVING clause can be used only with the SELECT statement. It is usually used in a GROUP BY clause and whenever GROUP BY is not used, HAVING behaves like a WHERE clause.
Having Clause is used only with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.
Q: How can you fetch the first 5 characters of the string?
A: There are a lot of ways to fetch characters from a string. For example:
Select SUBSTRING(StudentName,1,5) as studentname from student
