1. What is SQL?
SQL stands for Structured Query Language is a domain-specific programming language for managing the data in Database Management Systems. In short, it is a computer language for storing, manipulating, and retrieving data stored in a relational database.
2. Why is it relevant for a learner?
SQL programming skills are highly desirable and required in the market, as there is massive use of Database Management Systems (DBMS) in almost every software application.
3. What is Database?
It is an organized collection of structured information, or data, typically stored electronically in a computer system. A database is usually controlled by a database management system (DBMS). Together, the data and the DBMS, along with the applications that are associated with them, are referred to as a database system, often shortened to just a database.
4. Different database software available in the market
- MySQL
- Microsoft Access
- Microsoft SQL Server
- FileMaker Pro
- Oracle Database
5. What is the difference between SQL and MySQL or SQL Server?
SQL communicates with databases thus providing ways of manipulating and creating databases. Whereas MYSQL uses SQL as its standard relational database language.
6. What are the various Data Definition Language (DDL) commands in SQL?
- CREATE
- ALTER
- DROP
7. What are various Data Manipulation Language (DML) commands in SQL?
- SELECT
- INSERT
- UPDATE
- DELETE
8. What are various Data Control Language (DCL) commands in SQL?
- GRANT
- REVOKE
9. What is a NULL value?
A NULL value in a table is a value in a field that appears to be blank, which means a field with a NULL value is a field with no value. Therefore, it is not the same as a zero or a blank space
10. What are the types of databases?
- Relational databases
- Object-oriented databases
- Distributed databases
- Data warehouses
- NoSQL databases
- Graph databases
- OLTP databases.
11. How have Databases evolved over time owing to advancements in technology?
Databases have evolved dramatically since their inception in the early 1960s. Navigational databases such as the hierarchical database (which relied on a tree-like model and allowed only a one-to-many relationship), and the network database (a more flexible model that allowed multiple relationships), were the original systems used to store and manipulate data. Although simple, these early systems were inflexible.
In the 1980s, relational databases became popular, followed by object-oriented databases in the 1990s. More recently, NoSQL databases came about as a response to the growth of the internet and the need for faster speed and processing of unstructured data. Today, cloud databases and self-driving databases are breaking new ground when it comes to how data is collected, stored, managed, and utilized.
12. What’s the difference between a spreadsheet and a database?
Spreadsheets were originally designed for one user, and their characteristics reflect that. They’re great for a single user or a small number of users who don’t need to do a lot of incredibly complicated data manipulation. Databases, on the other hand, are designed to hold much larger collections of organized information—massive amounts, sometimes. Databases allow multiple users at the same time to quickly and securely access and query the data using highly complex logic and language
13. What can be the challenges for the efficiency of databases?
- The explosion of data due to IoT
- Data breaches
- Keeping up with demand
- Managing and maintaining the database
- Scalability limits
14. How autonomous technology is improving database management?
With the tedious tasks automated, database administrators are freed up to do more strategic work. It is revolutionizing how companies are managing and securing their data
15. What is Database Management System (DBMS)?
DBMS facilitates oversight and control of databases, enabling a variety of administrative operations such as performance monitoring, tuning, and backup and recovery.
16. What is RDBMS?
RDBMS stands for Relational Database Management System. RDBMS stores the data into the collection of tables, which is related by common fields between the columns of the table. It also provides relational operators to manipulate the data stored in the tables. The data in an RDBMS is stored in database objects which are called tables
17. What is a relationship?
Database Relationship is defined as the connection between the tables in a database.
18. What are the various data relationships?
- One to One Relationship.
- One to Many Relationships.
- Many to One Relationship.
- Self-Referencing Relationship.
19. What’s a Table?
A table is basically a collection of related data entries, and it consists of numerous columns and rows.
20. What’s a Field in a Table?
Every table is broken up into smaller entities called fields. A field is a column in a table that is designed to maintain specific information about every record in the table.
A NULL value in a table is a value in a field that appears to be blank, which means a field with a NULL value is a field with no value.
21. How are the data integrities are categorized with each RDBMS?
- Entity Integrity − There are no duplicate rows in a table.
- Domain Integrity − Enforces valid entries for a given column by restricting the type, the format, or the range of values.
- Referential integrity − Rows cannot be deleted, which are used by other records.
- User-Defined Integrity − Enforces some specific business rules that do not fall into entity, domain, or referential integrity.
22. What is normalization?
Normalization is the process of minimizing redundancy and dependency by organizing fields and table of a database.
23. What are all the different normalizations?
First Normal Form (1NF): This should remove all the duplicate columns from the table. Creation of tables for the related data and identification of unique columns.
Second Normal Form (2NF): Meeting all requirements of the first normal form. Placing the subsets of data in separate tables and Creation of relationships between the tables using primary keys.
Third Normal Form (3NF): This should meet all requirements of 2NF. Removing the columns which are not dependent on primary key constraints.
Fourth Normal Form (4NF): Meeting all the requirements of the third normal form and should not have multi-valued dependencies.
24. What is Denormalization?
In this, we add redundant data to speed up complex queries involving multiple tables to join. Here, we attempt to optimize the read performance of a database by adding redundant data or by grouping the data. It is contrary to normalization.
25. What is a query?
A DB query is a code written to get the information back from the database. The query can be designed in such a way that it matched our expectation of the result set. Simply, a question to the Database.
26. What is subquery?
A subquery is a query within another query. The outer query is called the main query, and the inner query is called the subquery. A subquery is always executed first, and the result of the subquery is passed on to the main query.
27. What are the types of subquery?
There are two types of subqueries – Correlated and Non-Correlated.
A correlated subquery cannot be considered as an independent query, but it can refer to the column in a table listed in the FROM the list of the main query.
A Non-Correlated subquery can be considered as an independent query and the output of the subquery is substituted in the main query.
28. What is the difference between DELETE and TRUNCATE commands?
DELETE command is used to remove rows from the table, and the WHERE clause can be used for the conditional set of parameters. Commit and Rollback can be performed after DELETE statement.
TRUNCATE removes all rows from the table. The truncate operation cannot be rolled back.
29. What are Local variables?
Local variables are the variables that can be used or exist inside the function.
30. What are Global variables?
Global variables are the variables that can be used or exist throughout the program
31. What is a constraint?
The constraint can be used to specify the limit on the data type of table. It can be specified while creating or altering the table statement
32. What is a primary key?
A primary key is a combination of fields that uniquely specify a row. This is a special kind of unique key, and it has an implicit, NOT NULL constraint. It means Primary key values cannot be NULL.
33. 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. There can be many unique constraints defined per table, but only one Primary key constraint is defined per table.
34. What is a foreign key?
A foreign key is one table that can be related to the primary key of another table. The relationship needs to be created between two tables by referencing a foreign key with the primary key of another table.
35. What is the purpose of the condition operators BETWEEN and IN?
BETWEEN displays rows based on a range of values. Whereas, IN condition operator checks for a specific set of values.
36. What are the case manipulation functions of SQL?
- LOWER
- UPPER
- INITCAP
37. Which function returns the remainder in a division operation?
The MOD function returns the remainder in a division operation.
38. What is a constraint?
Constraints are used to specify some sort of rules for processing data and limiting the type of data that can go into a table.
39. What is a unique constraint?
Unique constraints ensure that all the values in a column are different.
40. How to drop a constraint?
Any constraint can be dropped using the ALTER TABLE command with the DROP CONSTRAINT option.
41. Can we remove rows from a table based on values from another table?
Yes, subqueries can be used to remove rows from a table based on values from another table.
42. How do you insert null values in a column while inserting data?
Null values can be inserted into a table in one of the following ways −
- Implicitly by omitting the column from the column list
- Explicitly by specifying the NULL keyword in the VALUES clause
43. How do you copy rows from one table to another?
The INSERT statement can be used to add rows to a table by copying from another table. In this case, a subquery is used in the place of the VALUES clause.
44. What is an index?
Indexes are used to find all rows matching with some columns and then to skim through only those subsets of the data to find the matches.
45. What are the types of indexes?
- Single-column Indexes: A single-column index is created for only one column of a table.
- Composite-column Indexes: A composite-column index is an index created for two or more columns of the table.
- Unique Indexes: Unique indexes are used for maintaining the data integrity of the table. They do not allow multiple values to be inserted into the table.
46. State the differences between clustered and non-clustered indexes
Clustered index: It is used to sort the rows of data by their key values. A clustered index is like the contents of a phone book. Since the data is located next to each other, it helps a lot in fetching data based on range-based queries. Also, the clustered index is related to how the data is stored. There is only one clustered index possible per table.
Non-clustered index: It stores data at one location and indexes at some other location. The index has pointers that point to the location of the data. As the index in the non-clustered index is stored in different places, there can be many non-clustered indexes for a table.
47. What do you know about CDC in SQL Server?
CDC is called change data capture. It captures recent INSERT, DELETE, and UPDATE activity applied to SQL Server tables. It records changes to SQL Server tables in a compatible format.
48. What is the COALESCE function?
The COALESCE function takes a set of inputs and returns the first non-null value.
49. What are Joins?
The Join clause is used to combine rows from two or more tables based on a related column between them.
50. What are the different types of joins?
- Inner Join: Returns records that have matching values in both tables.
- Left Join: Returns all the rows from the left-hand-side table even if there are no matches available in the right-hand-side table.
- Right Join: Returns all the rows from the right-hand-side table even if there are no matches available in the left-hand-side table.
- Full Join: Returns all the rows from the left-hand-side table and all the rows from the right-hand-side table.
51. What is Self-Join?
Self-Join in SQL is used for joining a table with itself. Here, depending upon some conditions, each row of the table is joined with itself and with other rows of the table.