1. What is normalization?
Normalization is a database organizing technique that reduces repetition in a set of relations. Normalization is the process of applying the concept of normal forms to a relation.
1NF: A relationship is in 1NF if it has an atomic value.
2NF: A relation must be in 1NF, and all non-key attributes must be functionally dependent on the primary key to be in 2NF.
3NF: A relationship must be in 2NF and have no transition dependency to be in 3NF.
BCNF: A good illustration of 3NF is Boy Codd’s Normal form.
Are you looking after Career Guidance from Experts?
Want Free Career Counseling?
Just fill in your details, and one of our expert will call you !
2. Describe the Distinction Between DDL and DML.
- While DML is known as Data Manipulation Language, DDL stands for Data Definition Language.
- DDL generates a schema with certain limitations. DML updates, adds or retrieves data from that schema.
- The table’s column property is defined via DDL. DML gives the table’s row attribute.
- DDL commands include things like create, modify, drop, rename, and so on, while DML commands include things like merge, update, and insert.
- While DML uses the WHERE clause, DDL does not.
3. What are SQL’s ACID Properties?
The following are the properties of ACID:
- Atomicity: Data modifications must resemble a single operation.
- Consistency: BeforeThe data must be identical before and after the transaction.
- Isolation: There are no obstacles when conducting several transactions.
- Durability: In the event of a system failure, the transaction is successful.
Click here to master SQL for your data science interview.
4. How can I create a query to identify every duplicate in a table?
The following methods can be used in a general situation to identify every duplicate in a table:
- To group all of the rows by target columns, use the GROUP BY command. Here, you can check for duplication in the target columns.
- To determine whether a group contains more than one element, use the HAVING command in conjunction with the COUNT function.
5. How Does a Clustered Index Differ from a Non-Clustered Index?
The distinctions between a clustered and non-clustered index are as follows:
- Like a dictionary, a clustered index specifies the order in which the index or table should be sorted. A non-clustered index gathers all the information in one location and stores it elsewhere.
- Compared to the non-clustered index, the clustered index is faster.
- Compared to a non-clustered index, a clustered one requires less memory.
6. What does denormalization mean?
Denormalization is a database optimization approach that involves adding redundant data to the table or tables. The method is helpful since it lessens the need for expensive database joins.
7. Collation: What is it?
Collation in an SQL server gives the database’s data case sensitivity, accent, and sorting rules. It uses collation to define the bit patterns corresponding to each character in the database.
The following are a few of the collation levels:
- Sensitive to case (_CS)
- sensitive to accents (_AS)
- sensitive to kana (_KS)
- sensitive to width (_WS)
- (_VSS)a Variation-selector-sensitive
- Binary (_BIN)
- Point in binary code (_BIN2)
8. How Do Inner, Outer, and Full Outer Joins Differ From One Another?
The distinctions between inner, outer, and full outer joins are as follows:
- The outputs of an inner join are the rows that both tables share.
- A complete outer join returns every record from both tables.
- An outer join gives back the values from one or both tables.
Learn advanced SQL techniques used by professionals. Click to begin!
9. Describe the Distinction Between a Union and a Join.
The distinctions between a union and a join are as follows:
- Union joins the set of two separate SELECT operations, while join pulls the matched records from the tables.
- While Union eliminates duplicate data (rows) from chosen statements, join does not do so.
10. Describe how UNION and UNION ALL differ from one another.
While Union maintains all records, including duplicates, Union maintains unique records. Additionally, the Union prints concatenated results after performing a deduplication step before sending the data.
11. Create a query to print the table’s highest salary.
Examine a table named EMP that has the data below.-
Name and Salary
John 990000
Ellie 100000
Nick 400000
Sam 500,000.
The query would be:
SELECT * FROM EMP WHERE salary=(select Max(salary) FROM EMP);
12. How Do Inclusive and Shared Locks Differ From One Another?
- The inclusive lock has both reading and writing capabilities, whereas the shared lock only has read capabilities.
- While an inclusive lock stops others from reading or updating the data, a shared lock stops others from making changes.
- One transaction can hold an exclusive lock on a specific item, while any number of transactions can hold a shared lock.
Master SQL for Data Science interviews with expert-curated Q&As
13. Describe the SQL Transpose Mechanism.
- Transposing a row or column into a specific format in SQL allows you to view the data differently.
- Transposing a row to a column or a column to a row is a simple transposition.
- Joined transposition and dynamic transposition are common transpose techniques.
For a successful result, data analysis is done using all transposition approaches.
14. Describe How the B-trees Index Operates.
- The term “B-tree” refers to a self-balancing search tree where every leaf is at the same level. Data must be inserted at the leaf node of a B-tree.
- The tree in the B-trees index has a payload, which is a key-value pair. The actual data record is the source of this value.
- Using a B-tree index causes the database to search for a key corresponding to the B-tree and retrieve the index.
15. What Impact Do Delete and Truncate Have on an Identity?
The distinctions between the DELETE and TRUNCATE commands are as follows:
- While TRUNCATE removes all the data from the rows, DELETE removes a single row from the table.
- Compared to the DDL command TRUNCATE, the DML command DELETE is slower.
- While TRUNCATE does not activate, the DELETE command does.
16. What Does Having a Database Index Cost?
- It takes up all of your space. The index will be bigger if your table is larger.
- It is also necessary to ADD, DELETE, or UPDATE specific table rows to our index.
- Generally speaking, if a table in the indexed column is often needed, an index must be made on the table.
Connect with industry experts and get all your questions answered!
Meet the industry person, to clear your doubts !
17. What Benefits Does Having an Index Offer?
The benefits of having an index are as follows:
- It makes the SELECT query faster.
- Retrieving data becomes quite simple.
- You can sort using indexes.
- It eliminates duplicates and makes a row distinctive.
- Setting the index to fill-text index allows for the insertion of large string text.
18. Describe the database’s indexing.
Indexing is a data structure strategy that improves database speed by minimizing disk access during query processing.
- The qualities of indexing are as follows:
- Access Types: According to the type of search
- Depending on search time, access time
- Time of Insertion: Depending on rapid insertion
- Deletion Time: Predicated on speedy removal
- Space Overhead: Predicated on more space above.
19. OLAP and OLTP: What are they?
Online Analytical Processing (OLAP)
Businesses must understand a data set known as online analytical processing (OLAP), which gives finance executives and business teams information about their users and the performance of several connected elements. A company may have various data from its business processes and initiatives that enable teams to do more detailed performance analysis.
The team needs consistent, real-time, and more adaptable procedures to to examine the data and process it further for additional analysis because these data are housed in multiple database systems. Software solutions for evaluating corporate data and gaining database insights are part of Online Analytical Processing or OLAP.
One application of OLAP is the Spotify song recommendation engine, which uses user preferences and listening history to create a playlist for users automatically.
Claim your free expert counseling session today!
Do you want to book a FREE Demo Session?
Online Transaction Processing(OLTP)
An essential component of any business is the transaction. When a consumer consumes a business’s services, the business receives payment. Users who pay for the services they want instantly receive an invoice. The transaction database is necessary when we need to keep track of every user’s transaction information. On the other hand, OLTP provides transaction-oriented applications that fall under a three-tier design. Businesses can do specific tasks related to the transaction database with the aid of OLTP. Online banking transactions, for instance, accomplish the same goal through OLTP, which is to manipulate user transaction data.
20. Under what circumstances is CASE WHEN applicable?
Large datasets can have their values changed, and databases queried using SQL. Additionally, it offers a method for data manipulation in situations with several examples.
A table might, for instance, include all of the employees (name), their experience (EXP), and their joining date (ac_date).
To query the data, we can now add “Is a senior” to a new column called “exp_level” if the experience is more significant than (>) “4” and NULL to the field otherwise.
You can use the CASE WHEN statement to query the database in these situations.
The If/else condition in programming languages such as C, C++, Python, and others is comparable to the CASE WHEN statement.
One set of WHEN and THEN statements comes after the CASE statement. Nevertheless, depending on your needs, you can include nested sentences.
Finishing the CASE statement involves adding the last line, which begins with ELSE and ends with END AS.
The employee. database in the example above contains information about a company’s employees, including their name, year of hire, experience, and other details. In this example, using the CASE WHEN command,
SELECT employee_name,
exp,
CASE WHEN exp > 4 THEN ‘Is a Senior’
ELSE NULL END AS exp_level
FROM xyz.marketing_employees
21. Describe the Cases for Using HAVING as opposed to WHERE.
- SQL’s HAVING Clause
SQL provides HAVING clauses to filter the collection of data in a database.
Company XYZ, for instance, has a roster of workers with more than a year of experience.
The employee name, joining information, experience, and experience level are among the rows and columns in the database.
A list of workers with more than five years of experience is available for retrieval.
You can regulate the information and change the data with a lot of data thanks to the HAVING clause.
The HAVING condition is the syntax for the HAVING clause.
In the example, we can use the following database query to obtain the list of workers with more than 5 years of experience:
SELECT employee_name, exp_level,
FROM EMPLOYEE
GROUP BY experience
HAVING COUNT(experience) > 5;
- SQL’s WHERE Clause
You use the WHERE clause when you wish to filter the records according to a specific criterion.
We can obtain the list of all employees with precisely five years of experience in a corporation, for instance, by searching through an employee table. The WHERE condition is the syntax for the WHERE clause.
By running the following database query, we can retrieve the names of the employees in the example who have five years of experience:
SELECT employee_name, exp_level,
FROM EMPLOYEE
WHERE experience= 5;
22. PL/SQL: What is it?
The block-structured language PL/SQL enables programmers to use procedural statements to harness the power of SQL.
It is a procedural language with numerous POP features, including the ability to make decisions.
With the help of comprehensive error checking, PL/SQL may execute numerous queries with a single block instruction.
PL/SQL is an application development extension for SQL.
23. Nested Triggers: What Are They?
SQL provides Data Manipulation Language (DML) and Data Definition Language (DDL) to perform specific tasks when querying the database.
Certain activities are automatically carried out on the database when a specific kind of operation is carried out on it.
We refer to these kinds of database-based operations as “nested triggers.”
The two primary categories into which SQL divides nested triggers are INSTEAD OF triggers and AFTER triggers.
The AFTER trigger, as its name implies, is run following a DML or DDL operation on the database.
Nevertheless, DML and DDL operations are replaced by the INSTEAD OF trigger.
24. In SQL, what is ETL?
“Extract, Transform, and Load” is what ETL stands for. It creates data visualization and analysis utilizing the data warehousing paradigm. Data integration is its more general use.
ETL is the process of taking data from one system and storing or copying it to another destination system with a different representation.
25. What are Checkpoints and Commits?
After the current transaction concludes, the commit ensures that the data is consistent and kept in its changed state. Using a commit adds a new entry to the log memory.
The purpose of a checkpoint is to write all committed changes to disk up the system change number in the header and control files.
