Top 15 most important SQL basic concepts for Interview - SET 1

                                        

1. What are the different types of SQL commands? Can you explain those commands?

DDDL— Data Definition Language (CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME)

DML— Data Manipulation Language (SELECT, INSERT, UPDATE, DELETE, MERGE)

DDCL— Data Control Language (GRANT, REVOKE)

TCL — Transaction Control Language (COMMIT, ROLLBACK, SAVEPOINT)

 

2. What is an Index in SQL?

An index is performance-tuning method of allowing faster retrieval of records from the table. An index creates an entry for each value, and it will be faster to retrieve data.

Syntax

CREATE INDEX index_name ON table_name;

DROP INDEX index_name ON table_name;

 

4. What is a view in SQL?

A view is a virtual table, which consists of a subset of data contained in a table.

View can be create on one or many tables

 

CREATE VIEW view name AS SELECT columnl, column2.....

FROM table _ name WHERE [condition];

 

Advantage

It does not occupy Space

It used to retry the result of complex queries that executes often

It restricting data access

 

 

 5. What is a Normalization?

NORMALIZATION is a database design technique that reduces data redundancy and eliminates Undesirable characteristics like Insertion, Update and Deletion Anomalies.

Normalization rule divides larger table into smaller table and links them using relationship

Purpose of normalization in SQL is eliminate redundant or repetitive data and ensure that data is sorted logically

Database normalization process is divides into following categories

First Normal Form (INF)

Second Normal Form (2NF)

Third Normal Form (3NF)

Boyce-Codd Normal Form (BCNF)

Fourth Normal Form (4NF)

Fifth Normal Form (5NF)

6NF (Sixth Normal Form)

               

 

6. What is a Primary Key in SQL?

A primary key is a field in a table, which uniquely identifies each row/record in a database table.

Primary keys must contain unique values.

A primary key column cannot have NULL values.

A table can have only one primary key

A table cannot accept duplicate record

 

CREATE TABLE EMPLOYEE (EMPID INT, NAME VARCHAR (20), AGE INT, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (EMPID));   

 

 

7. What is a Foreign Key in SQL ?

> A foreign key is a key used to link two tables together.

A foreign key in one table used to point primary key in another table.

 

CREATE TABLE EMPLOYEE(

EMP_ID NOT NULL,

NAME VARCHAR (20) NOT NULL,

AGE INT NOT NULL,

ADDRESS CHAR (25),

SALARY DECIMAL (18, 2),

PRIMARY KEY (EMP_ID)

);

 

 

CREATE TABLE COMPANY(

COMPANY ID INT NOT NULL,

COMPANY_NAME VARCHAR (20) NOT NULL,

COMPANY_DOMAIN VARCHAR (20) NOT NULL,

COMPANY _ ADDRESS CHAR (25) ,

EMPLOYEE_ID INT references EMPLOYEE(EMP_ID)

PRIMARY KEY (COMPANY_ID)

);

 

 

8. What is a Query?

> A database query is a request for data or information from a database table or

Combination of tables.

> A database query can be either a select query or an action query.

 

 

9. What is a Sub-query?

> A Subquery is a SQL query within another query.

It is a subset of a Select statement whose return values are used in filtering the

Conditions of the main query.

 

10. What is a Stored procedure ?

A Stored Procedure is a collection of SQL statements that have been created and stored in the

database to perform a particular task.

 

11. What is a Trigger?

A DB trigger is a code or programs that automatically execute with response to some event on a table or view in a database.

It helps to maintain the integrity of the database.

  

12. Explain Distinct in SQL with example?

DISTINCT statement is used with the SELECT statement.

If the records contain duplicate values, then DISTINCT is used to select different values among duplicate records.

Syntax:

SELECT DISTINCT column_name(s) FROM table _ name;

SELECT DISTINCT emp_no FROM Employee;

               

 

13. What are SQL constraints?

Constraints are the rules that we can apply on the type of data in a table.

We can specify the limit on the type of data that can be stored in a particular column in a table using constraints.

NOT NULL - if specify we cannot insert null value               

UNIQUE – If specify all values must be unique (values cannot be duplicate)

PRIMARY KEY – If specify - it

FOREIGN KEY – It is fields, which can uniquely identify each row on another table.

CHECK – It helps to validate values of column to meet a particular condition. It ensure the values in column meets a condition.

DEFAULT – Specify the default values in column. If we forget to insert values, it should take default specified value.

 

 

14. What is the difference between Delete, Truncate and Drop command?

➢Delete command is a DML command; it is used to delete rows from a table. It can be rolled back.

➢Truncate is a DDL command, it is used to delete all the rows from the table and free the space containing the table. It cannot be rolled back.

➢Drop is a DDL command, it removes the complete data along with the table structure (unlike truncate command that removes only the rows). All the tables' rows, indexes, and privileges will also be removed.

 

15. What is a Join and What are the different types at joins!

Join is used to combine data or rows from two or more tables based on a common field between them.

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL JOIN                

Top 15 SQL Queries Interview Questions and Answers for Software Testing professionals - SET 2