SQL Theoretical Questions

Following are the top 100 SQL Questions to clear your theoretical concepts. Few questions will be repeated from Day 10, 11, and 12.

1. What is a database?
-A database is a collection of information in an organized form for faster and better access, storage and manipulation. It can also be defined as a collection of tables, schema, views, and other database objects.

2. What is a Data warehouse?
-Data warehouse refers to a central repository of data from multiple sources of information. Those data are consolidated, transformed and made available for the mining as well as online processing.

3. What is SQL?
-SQL stands for Structured Query Language , and it is used to communicate with the Database. This is a standard language used to perform tasks such as retrieval, updation, insertion and deletion of data from a database.

4. What is a Table in a Database?
A table is a database object used to store records in a field in the form of columns and rows that holds data.

5. What is a primary key?
-A primary key is a combination of fields which uniquely specify a row. This is a special kind of unique key, and it has implicit NOT NULL constraint. It means, Primary key values cannot be NULL.

6. What is a unique key?
-A Unique key constraint uniquely identified each record in the database. This provides uniqueness for the column or set of columns.
A Primary key constraint has automatic unique constraint defined on it. But not, in the case of Unique Key.
There can be many unique constraint defined per table, but only one Primary key constraint defined per table.

7. What is a foreign key?
-A foreign key is one table which can be related to the primary key of another table. Relationship needs to be created between two tables by referencing foreign key with the primary key of another table

8. What are the popular Database Management Systems in the IT Industry?
Oracle, MySQL, Microsoft SQL Server, PostgreSQL, Sybase, MongoDB, DB2, and Microsoft Access etc.

9. What is a join?
-This is a keyword used to query data from more tables based on the relationship between the fields of the tables. Keys play a major role when JOINs are used.

10. What are the types of join and explain each?

There are various types of join which can be used to retrieve data and it depends on the relationship between tables.

• Inner Join.
• Inner join return rows when there is at least one match of rows between the tables.

• Right Join.Right join return rows which are common between the tables and all rows of Right hand side table. Simply, it returns all the rows from the right hand side table even though there are no matches in the left hand side table.

• Left Join.Left join return rows which are common between the tables and all rows of Left hand side table. Simply, it returns all the rows from Left hand side table even though there are no matches in the Right hand side 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.

11. What are the different types of SQL commands?
SQL commands are segregated into the following types:

• DDL – Data Definition Language
• DML – Data Manipulation Language
• DQL – Data Query Language
• DCL – Data Control Language
• TCL – Transaction Control Language

12. DDL Commands:

• CREATE: To create databases and database objects
• ALTER: To alter existing database objects
• DROP: To drop databases and databases objects
• TRUNCATE: To remove all records from a table but not its database structure
• RENAME: To rename database objects

13. DML Commands

• SELECT: To select specific data from a database
• INSERT: To insert new records into a table
• UPDATE: To update existing records
• DELETE: To delete existing records from a table

14. DCL Commands

• GRANT: To provide user access
• DENY: To deny permissions to users
• REVOKE: To remove user access

15. What is normalization?
-Normalization is the process of minimizing redundancy and dependency by organizing fields and table of a database. The main aim of Normalization is to add, delete or modify field that can be made in a single table.

16. What is Denormalization?
-DeNormalization is a technique used to access the data from higher to lower normal forms of database. It is also process of introducing redundancy into a table by incorporating data from the related tables.

17. What is an Index?
-An index is used to speed up the performance of queries. It makes faster retrieval of data from the table. The index can be created on one column or a group of columns.

18. What are all the different types of indexes?
-There are three types of indexes
1. Unique Index: Unique Indexes helps maintain data integrity by ensuring that no two rows of data in a table have identical key values. A unique index can be applied automatically when a primary key is defined. It ensures that the values in the index key columns are unique.
2. Clustered Index: Clustered Index reorders the physical order of the table and search based on the key values. There will be only one clustered index per table.
3. Non-Clustered Index: Non-Clustered Index doesn’t alter the physical order of the table and maintains a logical order of the data. Each table can have many non-clustered indexes.

19. What is a relationship and what are they?
-Database Relationship is defined as the connection between the tables in a database. There are various data basing relationships, and they are as follows:-
One to One Relationship.
One to Many Relationship.
Many to One Relationship.
Self-Referencing Relationship.

20. What are the advantages of Views?
Some of the advantages of Views are

1. Views occupy no space
2. Views are used to simply retrieve the results of complicated queries that need to be executed often.
3. Views are used to restrict access to the database or to hide data complexity.

21. What is a stored procedure?
-Stored Procedure is a function consists of many SQL statement to access the database system. Several SQL statements are consolidated into a stored procedure and execute them whenever and wherever required.

22. What is a relationship and what are they?
-Database Relationship is defined as the connection between the tables in a database. There are various database relationships namely
1. One to One Relationship
2. One to Many Relationship
3. Many to One Relationship
4. Self-Referencing Relationship

23. What is the difference between Local Variables and Global Variables?
Local Variables: Local variables can be used or exist only inside the function. These variables are not used or referred by any other functions. These are not known to other functions. Variables can be created whenever that function is called.
Global Variables: Global variables can be used or exist throughout the program. Same variable declared in global cannot be used in functions. Global variables cannot be created whenever that function is called.

24. What is the difference between DELETE and TRUNCATE commands?
-DELETE command is used to remove rows from the table, and WHERE clause can be used for conditional set of parameters. Commit and Rollback can be performed after delete statement.
TRUNCATE removes all rows from the table. Truncate operation cannot be rolled back.

25. What is a constraint?
Constraint can be used to specify the limit on the data type of table. Constraint can be specified while creating or altering the table statement. Sample of constraint are:
NOT NULL.
CHECK.
DEFAULT.
UNIQUE.
PRIMARY KEY.
FOREIGN KEY.

26. What is the difference between UNIQUE and PRIMARY KEY constraints?
-There should be only one PRIMARY KEY in a table whereas there can be any number of UNIQUE Keys.
PRIMARY KEY doesn’t allow NULL values whereas Unique key allows NULL values.

27. What is the difference between NULL value, Zero, and Blank space?
-As I mentioned earlier, Null value is field with no value which is different from zero value and blank space.
Null value is a field with no value.
Zero is a number
Blank space is the value we provide. The ASCII value of space is CHAR(32).

28. What is CLAUSE?
SQL clause is defined to limit the result set by providing condition to the query. This usually filters some rows from the whole set of records.
Example –
Query that has WHERE condition
Query that has HAVING condition.

29. What is Union, minus and Interact commands?
-UNION operator is used to combine the results of two tables, and it eliminates duplicate rows from the tables.
MINUS operator is used to return rows from the first query but not from the second query. Matching records of first and second query and other rows from the first query will be displayed as a result set.
INTERSECT operator is used to return rows returned by both the queries.

30. List out the ACID properties and explain? 
Following are the four properties of ACID. These guarantees that the database transactions are processed reliably.

• Atomicity
• Consistency
• Isolation
• Durability

We will keep updating this page to collate all the theoretical questions at one place for your ease.

Keep learning 🙂