SQL for Data Scientist

 

SQL for Data Scientist

Let’s delve straight into some SQL interview questions.

1.   What exactly is SQL?

SQL is an acronym for the structured query language. It is a database management system that allows you to access and manipulate data. In 1986, the American National Standards Institute (ANSI) approved SQL as a standard.

2.   What Can SQL do for you?

 

SQL is capable of running queries against a database.

 

•      SQL may be used to get information from a database.

•      SQL may be used to create new records in a database.

•      SQL may be used to update data in a database.

•      SQL can delete records from a database.

•      SQL can build new databases.

•      SQL can create new tables in a database.

•      In a database, SQL may build stored procedures.

•      In a database, SQL may be used to generate views.

•      Permissions can be established on tables, methods, and views in SQL.

Interview Questions for Basic SQL

1.   How do you distinguish between SQL and MySQL?

SQL is a standard language based on English. MySQL is a relational database management system (RDBMS). SQL is the foundation of a relational database, and it is used to retrieve and manage data. MySQL is a

relational database management system (RDMS), similar to SQL Server and Informix.

2.   What are the various SQL subsets?

 

Data Definition Language (DDL) lets you do things like CREATE, ALTER, and DELETE items on the database.

Data Manipulation Language (DML) allows you to alter and access data. It aids in inserting, updating, deleting, and retrieving data from a database.

Data Control Language (DCL) allows you to manage database access, grant and revoke access permissions.

3.   What do you mean by database management system (DBMS)? What are the many sorts of it?

A Database Management System (DBMS) is a software program that captures and analyzes data through interacting with the user, applications, and the database itself. A database is a collection of data that is organized.

A database management system (DBMS) allows users to interface. The database's data may be edited, retrieved, and destroyed, and it can be of any type, including strings, integers, and pictures.

There are two types of database management systems (DBMS):

 

•      Relational Database Management System (RDBMS): Information is organized into relationships (tables). MySQL is a good example.

•      Non-Relational Database Management System: This system has no relations, tuples, or attributes. A good example is MongoDB.

4.   In SQL, how do you define a table and a field?

A table is a logically organized collection of data in rows and columns. The number of columns in a table is referred to as a field. Consider the following scenario:

Fields: Student ID, Student Name, and Student Marks

5.   How do we define joins in SQL?

A join clause joins rows from two or more tables based on a common column. It's used to join two tables together or derive data from them. As seen below, there are four different types of joins:

 

•      Inner join: The most frequent join in SQL is the inner join. It's used to get all the rows from various tables that satisfy the joining requirement.

•      Full Join: When there is a match in any table, a full join returns all the records. As a result, all rows from the left-hand side table and all rows from the right-hand side table are returned.

•      Right Join: In SQL, a “right join” returns all rows from the right table but only matches records from the left table when the join condition is met.

•      Left Join: In SQL, a left join returns all of the data from the left table, but only the matching rows from the right table when the join condition is met.

6.     What is the difference between the SQL data types CHAR and VARCHAR2?

Both Char and Varchar2 are used for character strings. However, Varchar2 is used for variable-length strings, and Char is used for fixed-length strings. For instance, char (10) can only hold 10 characters and cannot store a string of any other length, but varchar2 (10) may store any length, i.e. 6, 8, 2.

7.   What are constraints?

In SQL, constraints are used to establish the table's data type limit. It may be supplied when the table statement is created or changed. The following are some examples of constraints:

•      UNIQUE

•      NOT NULL

•      FOREIGN KEY

•      DEFAULT

•      CHECK

•      PRIMARY KEY

8.   What is a foreign key?

A foreign key ensures referential integrity by connecting the data in two tables. The foreign key as defined in the child table references the primary key in the parent table. The foreign key constraint obstructs actions to terminate links between the child and parent tables.

9.   What is"data integrity"?

Data integrity refers to the consistency and correctness of data kept in a database. It also specifies integrity constraints, which are used to impose business rules on data when input into an application or database.

10.     What is the difference between a clustered and a non-clustered index?

The following are the distinctions between a clustered and non-clustered index in SQL:

 

•      Clustered indexes are utilized for quicker data retrieval from databases, whereas reading from non-clustered indexes takes longer.

•      A clustered index changes the way records are stored in a database by sorting rows by the clustered index column. A non-clustered index does not change the way records are stored but instead creates a separate object within a table that points back to the original table rows after searching.

There can only be one clustered index per table, although there can be numerous non clustered indexes.

11.  How would you write a SQL query to show the current date?

A built-in method in SQL called GetDate() returns the current timestamp/date.

12.   What exactly do you mean when you say "query optimization"?

Query optimization is the step in which a plan for evaluating a query that has the lowest projected cost is identified.

The following are some of the benefits of query optimization:

 

•    The result is delivered more quickly.

•    In less time, a higher number of queries may be run.

•    Reduces the complexity of time and space

13.   What is "denormalization"?

Denormalization is a technique for retrieving data from higher to lower levels of a database. It aids database administrators in improving the overall performance of the infrastructure by introducing redundancy into a table. It incorporates database queries that merge data from many tables into a single table to add redundant data to a table.

14.   What are the differences between entities and relationships?

Entities are real-world people, places, and things whose data may be kept in a database. Tables are used to contain information about a single type of object. A customer table, for example, is used to hold customer information in a bank database. Each client's information is stored in the customer database as a collection of characteristics (columns inside the table).

Relationships are connections or connections between things that have something in common. The customer name, for example, is linked to the customer account number and contact information, which may be stored in the same database. There may also be connections between different tables (for example, customer to accounts).

15.   What is an index?

An index is a performance optimization strategy for retrieving records from a table quickly. Because an index makes an entry for each value, retrieving data is faster.

16.   Describe the various types of indexes in SQL.

In SQL, there are three types of indexes:

•      Unique Index: If the column is unique indexed, this index prevents duplicate values in the field. A unique index can be applied automatically if the main key is provided.

•      Clustered Index: This index reorders the table's physical order and searches based on key values. There can only be one clustered index per table.

•      Non-Clustered Index: Non-clustered indexes do not change the physical order of the database and keep the data in a logical order. There might be a lot of nonclustered indexes in a table.

17.   What is normalization, and what are its benefits?

The practice of structuring data in SQL to prevent duplication and redundancy is known as normalization. The following are some of the benefits:

•    Improved database management

•    Tables with smaller rows are added to the mix.

•    Efficient data access

•    Greater queries flexibility

•    Locate the information quickly.

•    Security is easier to implement.

•    Allows for easy customization/customization

•    Data duplication and redundancy are reduced.

•    More compact database.

•    Ensure that data is consistent after it has been modified.

18.   Describe the various forms of normalization.

There are several levels of normalization to choose from. These are referred to as normal forms. Each subsequent normal form is dependent on the one before it. In most cases, the first three normal forms are sufficient.

 

First Normal Form (1NF) – There are no repeating groups in between rows

Second Normal Form (2NF) – Every non-key (supporting) column value relies on the primary key.

Third Normal Form (3NF) – Dependent solely on the primary key and no other non-key (supporting) column value.

19.   In a database, what is the ACID property?

Atomicity, Consistency, Isolation, and Durability (ACID) is used to verify that data transactions in a database system are processed reliably.

 

Atomicity: Atomicity relates to completed or failed transactions. A transaction refers to a single logical data operation. It means that if one portion of a transaction fails, the full transaction fails as well, leaving the database state unaltered.

Consistency: Consistency guarantees that the data adheres to all validation standards. In basic terms, your transaction never leaves the database before it has completed its state.

Isolation: The main purpose of isolation is concurrency control. Durability: Durability refers to the fact that once a transaction has been committed, it will occur regardless of what happens in the meantime, such as a power outage, a crash, or any other type of mistake.

20.   What is "Trigger" in SQL?

Triggers are a stored procedure in SQL that is configured to execute automatically in situ or after data changes. When an insert, update, or other query is run against a specified table, it allows you to run a batch of code.

21.   What are the different types of SQL operators?

In SQL, there are three operators available: Logical Operators

Arithmetic Operators Comparison Operators

 

22.   Do NULL values have the same meaning as zero or a blank space?

A null value is not confused with a value of zero or a blank space. A null value denotes an unavailable, unknown, assigned, or not applicable value,

whereas a zero denotes a number and a blank space denotes a character.

23.   What is the difference between a natural join and a cross join?

The natural join is dependent on all columns in both tables having the same name and data types, whereas the cross join creates the cross product or Cartesian product of two tables.

24.   What is a subquery in SQL?

A subquery is a query defined inside another query to get data or information from the database. The outer query of a subquery is referred to as the main query. In contrast, the inner query is referred to as the subquery. Subqueries are always processed first, and the subquery's result is then passed on to the main query. It may be nested within any query, including SELECT, UPDATE, and OTHER. Any comparison operators, such as >, or

=, can be used in a subquery.

25.   What are the various forms of subqueries?

Correlated and Non-Correlated subqueries are the two forms of the subquery.

 

Correlated subqueries: These queries pick data from a table that the outer query refers to. It is not considered an independent query because it refers to another table and column.

Non-Correlated subquery: This query is a stand-alone query in which a subquery's output is used to replace the main query results.

 

DBMS and RDBMS

1.     What is a database management system (DBMS), and what is its purpose? Use examples to explain RDBMS.

The database management system, or DBMS, is a collection of applications or programs that allow users to construct and maintain databases. A database management system (DBMS) offers a tool or interface for executing different database activities such as adding, removing, updating, etc. It is software that allows data to be stored more compactly and securely

than a file-based system. A database management system (DBMS) assists a user in overcoming issues such as data inconsistency, data redundancy, and other issues in a database, making it more comfortable and organized to use.

Examples of prominent DBMS systems are file systems, XML, the Windows Registry, and other DBMS systems.

RDBMS stands for Relational Database Management System, and it was first introduced in the 1970s to make it easier to access and store data than DBMS. In contrast to DBMS, which stores data as files, RDBMS stores data as tables. Unlike DBMS, storing data in rows and columns makes it easier to locate specific values in the database and more efficient.

MySQL, Oracle DB, are good examples of RDBMS systems.

2.   What is a database?

A database is a collection of well-organized, consistent, and logical data and can be readily updated, accessed, and controlled. Most databases are made up of tables or objects (everything generated with the create command is a database object) that include entries and fields. A tuple or row represents a single entry in a table. The main components of data storage are attributes and columns, which carry information about a specific element of the database. A database management system (DBMS) pulls data from a database using queries submitted by the user.

3.    What drawbacks of traditional file-based systems make a database management system (DBS) a superior option?

The lack of indexing in a typical file-based system leaves us little choice but to scan the whole page, making content access time-consuming and sluggish. The other issue is redundancy and inconsistency, as files often include duplicate and redundant data, and updating one causes all of them to become inconsistent. Traditional file-based systems make it more difficult to access data since it is disorganized.

Another drawback is the absence of concurrency management, which causes one action to lock the entire page, unlike DBMS, which allows several operations to operate on the same file simultaneously.

Integrity checking, data isolation, atomicity, security, and other difficulties with traditional file-based systems have all been addressed by DBMSs.

4.     Describe some of the benefits of a database management system (DBS).

The   following   are   some   of   the   benefits   of   employing   a   database management system (DBS).

 

Data Sharing: Data from a single database may be shared by several users simultaneously. End-users can also respond fast to changes in the database environment because of this sharing.

Integrity restrictions: The presence of such limitations allows for the ordered and refined storage of data.

Controlling database redundancy: Provides a means for integrating all data in a single database, eliminating redundancy in a database.

Data Independence: This allows you to change the data structure without affecting the composition of any of the application programs that are currently running.

Provides backup and recovery facility: It may be configured to automatically generate a backup of the data and restore the data in a database when needed.

Data Security: A database management system (DBMS) provides the capabilities needed to make data storage and transmission more dependable and secure. Some common technologies used to safeguard data in a DBMS include authentication (the act of granting restricted access to a user) and encryption (encrypting sensitive data such as OTP, credit card information, and so on).

5.   Describe the different DBMS languages.

The following are some of the DBMS languages:

 

DDL (Data Definition Language) is a language that includes commands for defining databases.

CREATE, ALTER, DROP, TRUNCATE, RENAME, and so on.

DML (Data Manipulation Language) is a set of instructions that may alter data in a database. SELECT, UPDATE, INSERT, DELETE, and so on.

DCL (Data Control Language): It offers instructions for dealing with the database system's user permissions and controls.

GRANT and REVOKE, for example.

TCL (Transaction Control Language) is a programming language that offers instructions for dealing with database transactions. COMMIT, ROLLBACK, and SAVEPOINT are a few examples.

6.        What   does   it   mean   to   have   ACID   qualities    in   a   database management system (DBMS)?

In a database management system, ACID stands for Atomicity, Consistency, Isolation, and Durability. These features enable a safe and secure exchange of data among different users.

 

Atomicity: This attribute supports the notion of either running the whole query or doing nothing at all, which means that if a database update occurs, it should either be reflected across the entire database or not at all.

Consistency: This feature guarantees that data is consistent before and after a transaction in a database.

Isolation: This characteristic assures that each transaction is separate from the others, and this suggests that the status of one ongoing transaction has no bearing on the condition.

Durability: This attribute guarantees that data is not destroyed in the event of a system failure or restart and that it is available in the same condition as before the failure or restart.

7.   Are NULL values in a database the same as blank space or zero?

No, a null value is different from zero and blank space. It denotes a value that is assigned, unknown, unavailable, or not applicable, as opposed to blank space, which denotes a character, and zero, which denotes a number.

For instance, a null value in the "number of courses" taken by a student indicates that the value is unknown, but a value of 0 indicates that the

student has not taken any courses.

8.   What does Data Warehousing mean?

Data warehousing is the process of gathering, extracting, processing, and importing data from numerous sources and storing it in a single database. A data warehouse may be considered a central repository for data analytics that receives data from transactional systems and other relational databases. A data warehouse is a collection of historical data from an organization that aids in decision-making.

9.        Describe   the   various    data   abstraction    layers   in    a   database management system (DBMS).

Data abstraction is the process of concealing extraneous elements from consumers. There are three degrees of data abstraction:

 

Physical Level: This is the lowest level, and the database management system maintains it. The contents of this level are often concealed from system admins, developers, and users, and it comprises data storage descriptions.

Conceptual or logical level: Developers and system administrators operate at the conceptual or logical level, which specifies what data is kept in the database and how the data points are related.

External or View level: This level only depicts a portion of the database and keeps the table structure and actual storage specifics hidden from users. The result of a query is an example of data abstraction at the View level. A view is a virtual table formed by choosing fields from multiple database tables.

10.     What does an entity-relationship (E-R) model mean? Define an entity, entity type, and entity set in a database management system.

A diagrammatic approach to database architecture in which real-world things are represented as entities and connections between them are indicated is known as an entity-relationship model.

Entity: A real-world object with attributes that indicate the item's qualities is defined as an entity. A student, an employee, or a teacher, for example, symbolizes an entity.

Entity Type: This is a group of entities with the same properties. An entity type is represented by one or more linked tables in a database. Entity type or attributes may be thought of as a trait that distinguishes the entity from others. A student, for example, is an entity with properties such as student id, student name, and so on.

Entity Set: An entity set is a collection of all the entities in a database that belongs to a given entity type. An entity set, for example, is a collection of all students, employees, teachers, and other individuals.

11.      What is the difference between intension and extension in a database?

The main distinction between intension and extension in a database is as follows:

 

Intension: Intension, also known as database schema, describes the database's description. It is specified throughout the database's construction and typically remains unmodified.

Extension, on the other hand, is a measurement of the number of tuples in a database at any particular moment in time. The snapshot of a database is also known as the extension of a database. The value of the extension changes when tuples are created, modified, or deleted in the database.

12.     Describe the differences between the DELETE and TRUNCATE commands in a database management system.

DELETE command: this command is used to delete rows from a table based on the WHERE clause's condition.

 

It just deletes the rows that the WHERE clause specifies. If necessary, it can be rolled back.

It keeps a record to lock the table row before removing it, making it sluggish.

The TRUNCATE command is used to delete all data from a table in a database. Consequently, making it similar to a DELETE command without a WHERE clause.

It deletes all of the data from a database table.

It may be rolled back if necessary. (Truncate can be rolled back, but it's hard and can result in data loss depending on the database version.)

It doesn't keep a log and deletes the entire table at once, so it's quick.

13.     Define lock. Explain the significant differences between a shared lock and an exclusive lock in a database transaction.

A database lock is a method that prevents two or more database users from updating the same piece of data at the same time. When a single database user or session obtains a lock, no other database user or session may edit the data until the lock is released.

 

Shared lock: A shared lock is necessary for reading a data item, and in a shared lock, many transactions can hold a lock on the same data item. A shared lock allows many transactions to read the data items.

Exclusive lock: A lock on any transaction that will conduct a write operation is an exclusive lock. This form of lock avoids inconsistency in the database by allowing only one transaction at a time.

14.   What do normalization and denormalization mean?

Normalization is breaking up data into numerous tables to reduce duplication. Normalization allows for more efficient storage space and makes maintaining database integrity.

Denormalization is the reversal of normalization, in which tables that have been normalized are combined into a single table to speed up data retrieval. By reversing the normalization, the JOIN operation allows us to produce a denormalized data representation.

RDBMS

1.       What   are   the   various   characteristics   of   a   relational   database management system (RDBMS)?

 

Name: Each relation should have a distinct name from all other relations in a relational database.

Attributes: An attribute is a name given to each column in a relation.

Tuples: Each row in a relation is referred to as a tuple. A tuple is a container for a set of attribute values.

2.   What is the E-R Model, and how does it work?

The E-R model stands for Entity-Relationship. The E-R model is based on a real-world environment that consists of entities and related objects. A set of characteristics is used to represent entities in a database.

3.   What does an object-oriented model entail?

The object-oriented paradigm is built on the concept of collections of items. Values are saved in instance variables within an object and stored. Classes are made up of objects with the same values and use the same methods.

4.   What are the three different degrees of data abstraction?

 

Physical   level:   This   is   the   most   fundamental   level   of abstraction, describing how data is stored.

Logical level: The logical level of abstraction explains the types of data recorded in a database and their relationships.

View level: This is the most abstract level, and it describes the entire database.

5.     What are the differences between Codd's 12 Relational Database Rules?

Edgar F. Codd presented a set of thirteen rules (numbered zero to twelve) that he called Codd's 12 rules.

Codd's rules are as follows:

Rule 0: The system must meet Relational, Database, and Management Systems requirements.

Rule 1: The information rule: Every piece of data in the database must be represented uniquely, most notably name values in column locations inside a distinct table row.

Rule 2: The second rule is the assured access rule, which states that all data must be ingressive. Every scalar value in the database must be correctly/logically addressable.

Rule 3: Null values must be treated consistently: The DBMS must allow each tuple to be null.

Rule 4: Based on the relational paradigm, an active online catalog (database structure): The system must provide an online, relational, or other structure that is ingressive to authorized users via frequent queries.

Rule 5: The sublanguage of complete data: The system must support at least one relational language that meets the following criteria:

 

1.   That has a linear syntax

2.   That can be utilized interactively as well as within application applications.

3.   Data definition (DDL), data manipulation (DML), security and integrity restrictions, and transaction management activities are all supported (begin, commit, and roll back).

 

Rule 6: The view update rule: The system must upgrade any views that theoretically improve.

Rule 7: Insert, update, and delete at the highest level: The system must support insert, update, and remove operators at the highest level.

Rule 8: Physical data independence: Changing the physical level (how data is stored, for example, using arrays or linked lists) should not change the application.

Rule 9: Logical data independence: Changing the logical level (tables, columns, rows, and so on) should not need changing the application.

Rule 10: Integrity independence: Each application program's integrity restrictions must be recognized and kept separately in the catalog.

Rule 11: Distribution independence: Users should not see how pieces of a database are distributed to multiple sites.

Rule 12: The nonsubversion rule: If a low-level (i.e., records) interface is provided, that interface cannot be used to subvert the system.

6.    What is the definition of normalization? What, therefore, explains the various normalizing forms?

Database normalization is a method of structuring data to reduce data redundancy. As a result, data consistency is ensured. Data redundancy has drawbacks, including wasted disk space, data inconsistency, and delayed DML (Data Manipulation Language) searches. Normalization forms include 1NF, 2NF, 3NF, BCNF, 4NF, 5NF, ONF, and DKNF.

 

1.1NF: Each column's data should contain atomic number multiple values separated by a comma. There are no recurring column groupings in the table, and the main key is used to identify each entry individually.

2.2NF: – The table should satisfy all of 1NF's requirements, and redundant data should be moved to a separate table. Furthermore, it uses foreign keys to construct a link between these tables.

3.3NF: A 3NF table must meet all of the 1NF and 2NF requirements. There are no characteristics in 3NF that are partially reliant on the main key.

7.    What are primary key, a foreign key, a candidate key, and a super key?

 

The main key is the key that prevents duplicate and null values from being stored. A primary key can be specified at the column or table level, and per table, only one primary key is permitted. Foreign key: a foreign key only admits values from the linked column, and it accepts null or duplicate values. It can be

classified as either a column or a table level, and it can point to a column in a unique/primary key table.

Candidate Key: A Candidate key is the smallest super key; no subset of Candidate key qualities may be used as a super key.

A super key: is a collection of related schema characteristics on which all other schema elements are partially reliant. The values of super key attributes cannot be identical in any two rows.

8.   What are the various types of indexes?

The following are examples of indexes:

 

Clustered index: This is where data is physically stored on the hard drive. As a result, a database table can only have one clustered index.

Non-clustered index: This index type does not define physical data but defines logical ordering. B-Tree or B+ trees are commonly used for this purpose.

9.    What are the benefits of a relational database management system (RDBMS)?

Controlling Redundancy is the answer.

 

Integrity is something that can be enforced.

 

•    It       is       possible       to       prevent inconsistency.

•    It's possible to share data.

•    Standards are enforceable.

10.   What are some RDBMS subsystems?

RDBMS subsystems are Language processing, Input-output, security, storage management, distribution control, logging and recovery, transaction control, and memory management.

11.  What is Buffer Manager, and how does it work?

The Buffer Manager collects data from disk storage and chooses what data should be stored in cache memory for speedier processing.

MYSQL

MySQL is a relational database management system that is free and open- source (RDBMS). It works both on the web and on the server. MySQL is a fast, dependable, and simple database, and it's a free and open-source program. MySQL is a database management system that runs on many systems and employs standard SQL. It's a SQL database management system that's multithreaded and multi-user.

Tables are used to store information in a MySQL database. A table is a set of columns and rows that hold linked information.

MySQL includes standalone clients that allow users to communicate directly with a MySQL database using SQL. Still, MySQL is more common to be used in conjunction with other programs to create applications that require relational database functionality.

Over 11 million people use MySQL.

Basic MYSQL Interview Questions

1.   What exactly is MySQL?

MySQL is a scalable web server database management system, and it can expand with the website. MySQL is by far the most widely used open- source SQL database management system, developed by Oracle Corporation.

2.   What are a few of the benefits of MySQL?

•      MySQL is a flexible database that operates on any operating system.

•      MySQL is focused on performance.

•      SQL at the Enterprise Level MySQL had been deficient in sophisticated functionality like subqueries, views, and stored procedures for quite some time.

•      Indexing and Searching of Full-Text Documents

•      Query Caching: This significantly improves MySQL's performance.

•      Replication: A MySQL server may be copied on another, with many benefits.

•      Security and configuration

3.   What exactly do you mean when you say "databases"?

A database is a structured collection of data saved in a computer system and organized to be found quickly. Information may be quickly found via databases.

4.   What does SQL stand for in MySQL?

SQL stands for Structured Query Language in MySQL. Other databases, such as Oracle and Microsoft SQL Server, also employ this language. To submit queries from a database, use instructions like the ones below:

It's worth noting that SQL doesn't care about the case. However, writing SQL keywords in CAPS and other names and variables in a small case is a good practice.

5.   What is a MySQL database made out of?

A MySQL database comprises one or more tables, each with its own set of entries or rows. The data is included in numerous columns or fields inside these rows.

6.   What are your options for interacting with MySQL?

You may communicate with MySQL in three different ways: Via a web interface

Using a command line

Through a programming language

7.   What are MySQL Database Queries, and How do I use them?

An inquiry is a request or a precise question. A database may be queried for specific information, and a record returned.

8.   In MySQL, what is a BLOB?

The abbreviation BLOB denotes a big binary object, and its purpose is to store a changeable amount of information.

There are four different kinds of BLOBs:

 

TINYBLOB MEDIUMBLOB BLOB LONGBLOB

A BLOB may store a lot of information. Documents, photos, and even films are examples. If necessary, you may save the whole manuscript as a BLOB file.

9.   What is the procedure for adding users to MySQL?

By executing the CREATE command and giving the required credentials, you may create a User. Consider the following scenario:

CREATE USER 'testuser' WITH' sample password' AS IDENTIFIER.

10.   What exactly are MySQL's "Views"?

A view in MySQL is a collection of rows that are returned when a certain query is run. A 'virtual table' is another name for this. Views make it simple to find out how to make a query available via an alias.

Views provide the following advantages: Security

Simplicity

Maintainability

11.  Define MySQL Triggers?

A trigger is a job that runs in reaction to a predefined database event, such as adding a new record to a table. This event entails entering, altering, or removing table data, and the action might take place before or immediately after any such event.

Triggers serve a variety of functions, including:

 

•     Validation

•     Audit Trails

•     Referential integrity enforcement

12.   In MySQL, how many triggers are possible?

There are six triggers that may be used in the MySQL database: After Insert

Before Insert

Before Delete Before Update After Update After Delete

13.   What exactly is a MySQL server?

The server, mySQLd, is the heart of a MySQL installation; it handles all database and table management.

14.   What are MySQL's client and utility programs?

To interact with the server, you can use several MySQL applications. Some of the most significant administrative responsibilities are outlined below:

 

mySQL—An     interactive    application    for    sending    SQL commands to a server and viewing the results. MySQL may also run batch scripts (text files containing SQL statements). mySQLadmin—An administrative application for activities like shutting down the server, reviewing its setup, and monitoring its

status if it doesn't appear to be working properly. mySQLdump—A     utility                                for        backing           up   and   transferring databases from one server to another.

mySQLcheck and myisamchk—Programs that let you check, analyze, and optimize tables, as well as repair them if they become damaged. MyISAM tables and, to a lesser extent, tables for other storage engines are supported by mySQLcheck. Only MyISAM tables should be used with myisamchk.

14.   What are the different types of MySQL relationships?

In MySQL, there are three types of relationships:

 

•      One-o-One: When two things have a one-to-one relationship, they are usually included as columns in the same table.

•      One-to-Many: When one row in one database is linked to many rows in another table, this is known as a one-to-many (or many-to-one) connection.

•      Many-to-Many: Many rows in one table are connected to many rows in another table in a many-to-many connection. Add a third table with the same key column as the other tables 29 to establish this link.

15.   What is MySQL Scaling?

In MySQL, scaling capacity refers to the system's ability to manage demand, and it's helpful to consider load from a variety of perspectives, including:

 

Quantity of information Amount of users

Size of related datasets User activity

16.   What is SQL Sharding?

Sharding divides huge tables into smaller portions (called shards) distributed across different servers. The benefit of sharding is that searches, maintenance, and other operations are quicker because the sharded database is typically much smaller than the original.

Unique Constraints

The rule that states that the values of a key are valid only if they are unique is known as the unique constraint. A unique key has just one set of values, and a unique index is utilized to apply a unique restriction. During the execution of INSERT and UPDATE commands, the database manager utilizes the unique index to guarantee that the values of the key are unique.

There are two kinds of Unique constraints:

A CREATE TABLE or ALTER TABLE command can specify a unique key as a primary key. There can't be more than one main key in a base table. A CHECK constraint will be introduced automatically to enforce the requirement that NULL values are not permitted in the primary key fields. The main index is a unique index on a primary key.

The UNIQUE clause of the CREATE TABLE or ALTER TABLE statement may be used to establish unique keys. There can be many sets of UNIQUE keys in a base table, and there are no restrictions on the number of null values that can be used.

The parent key is a unique key referenced by the foreign key of a referential constraint. The main key or a UNIQUE key is a parent key, and the default parent key is its main key when a base table is designated as a parent in a referential constraint.

When a unique constraint is defined, the unique index used to enforce it is constructed implicitly. Alternatively, the CREATE UNIQUE INDEX statement can be used to define it.

1.   What are constraints?

A constraint is an attribute of a table column that conducts data validation. Constraints help to ensure data integrity by prohibiting the entry of incorrect data.

2.   What do you mean when you say "data integrity"?

The consistency and correctness of data kept in a database are data integrity.

3.   Is it possible to add constraints to a table that already contains data?

Yes, but it also depends on the data. For example, if a column contains null values and adds a not-null constraint, you must first replace all null values with some values.

4.   Can a table have more than one primary key?

No table can only have one primary key

5.   What is the definition of a foreign key?

In one table, an FK refers to a PK in another. It prohibits any operations that might break the linkages between tables and the data values they represent. FKs are used to ensure that referential integrity is maintained.

6.   What is the difference between primary and unique key constraints?

 

A null value will be allowed if the constraint is unique. A unique constraint will allow just one null value if a field is nullable.

SQL Server allows for several unique constraints per table, but MySQL only allows for a single primary key.

7.   Is it possible to use Unique key restrictions across multiple columns?

Yes! Unique key constraints can be imposed on a composite of many fields to assure record uniqueness.

Example: City + State in the StateList table

8.      When you add a unique key constraint, which index does the database construct by default?

A nonclustered index is constructed when you add a unique key constraint.

9.   What does it mean when you say "default constraints"?

When no value is supplied in the Insert or Update statement, a default constraint inserts a value in the column.

10.   What kinds of data integrity are there?

There are three types of integrity in relational databases.

 

Entity Integrity (unique constraints, primary key) Domain Integrity (check constraints, data type)

 

Clustered and Non-Clustered Indexes

1.   What exactly is an index?

An index is a database object that the SQL server uses to improve query performance by allowing query access to rows in the data table. We can save time and increase the speed of database queries and applications by employing indexes.

When constructing an index on a column, SQL Server creates a second index table. When a user tries to obtain data from an existing table that relies on the index table, SQL Server goes straight to the table and quickly retrieves the data.

250 indexes may be used in a table. The index type describes how SQL Server stores the index internally.

2.   Why are indexes required in SQL Server?

Queries employ indexes to discover data from tables quickly. Tables and views both have indexes. The index on a table or view is quite similar to the index in a book.

If a book doesn't contain an index and we're asked to find a certain chapter, we'll have to browse through the whole book, beginning with the first page. If we have the index, on the other hand, we look up the chapter's page number in the index and then proceed to that page number to find the chapter.

Table and View indexes can help the query discover data fast in the same way. In reality, the presence of the appropriate indexes may significantly enhance query performance. If there is no index to aid the query, the query

engine will go over each row in the table from beginning to end. This is referred to as a Table Scan, and the performance of a table scan is poor.

3.   What are the different types of indexes in SQL Server?

 

Clustered Index

Non-Clustered Index

4.   What is a Clustered Index?

In the case of a clustered index, the data in the index table will be arranged the same way as the data in the real table.

The index, for example, is where we discover the beginning of a book. The term "clustered table" refers to a table that has a clustered index.

The data rows in a table without a clustered index are kept unordered. A table can only have one clustered index, which is constructed when the table's main key constraint is invoked.

A clustered index determines the physical order of data in a table. As a result, a table can only have one clustered index.

5.   What is a non-clustered index?

In a non-clustered index, the data in the index table will be organized differently than the data in the real database. A non-clustered index is similar to a textbook index. The data is kept in one location, while the index is kept in another. The index will contain references to the data's storage place.

A table can contain more than one non-clustered index since the non- clustered index is kept independently from the actual data, similar to how a book can have an index by chapters at the beginning and another index by common phrases at the conclusion.

The data is stored in the index in ascending or descending order of the index key, which has no bearing on data storage in the table. We can define a maximum of 249 non clustered indexes in a database.

6.   In SQL Server, what is the difference between a clustered and a non- clustered index?

One of the most common SQL Server Indexes Interview Questions is this one. Let's look at the differences. There can only be one clustered index per table, although several non-clustered indexes can be.

The Clustered Index is quicker than the Non-Clustered Index by a little margin. When a Non-Clustered Index is used, an extra lookup from the Non-Clustered Index to the table is required to retrieve the actual data. A clustered index defines the row storage order in the database and does not require additional disk space. Still, a non-clustered index is kept independently from the table and thus requires additional storage space.

A clustered index is a sort of index that reorders the actual storage of entries in a table. As a result, a table can only have one clustered index. A non- clustered index is one in which the logical order of the index differs from the physical order in which the rows are written.

7.   What is a SQL Server Unique Index?

If the "UNIQUE" option is used to build the index, the column on which the index is formed will not allow duplicate values, acting as a unique constraint. Unique clustered or unique non-clustered constraints are both possible.

If clustered or non-clustered is not provided when building an index, it will be non-clustered by default. A unique index is used to ensure that key values in the index are unique.

8.   When does SQL Server make use of indexes?

SQL Server utilizes a table's indexes if the select, update, or delete statement included a "WHERE" condition and the where condition field was an indexed column. If an "ORDER BY" phrase is included in the select statement, indexes will be used as well.

Note: When SQL Server searches the database for information, it first determines the optimum execution plan for retrieving the data and then employs that plan, a full-page scan or an index scan.

9.   When should a table's indexes be created?

If a table column is regularly used in a condition or order by clause, we must establish an index on it. It is not recommended that an index be created for each column since many indexes might reduce database performance. Any change to the data should be reflected in all index tables.

10.      What is the maximum number of clustered and non-clustered indexes per table?

Clustered Index: Each table has only one Clustered Index. A clustered index stores all of the data for a single table, ordered by the index key. The Phone Book exemplifies the Clustered Index.

Non-Clustered Index: Each table can include many Non-Clustered Indexes. A Non-Clustered Index is an index found in the back of a book.

 

1 Clustered Index + 249 Nonclustered Index = 250 Index in SQL Server 2005

1 Clustered Index + 999 Nonclustered Index = 1000 Index in SQL Server 2008.

11.  Clustered or non-clustered index, which is faster?

The Clustered Index is quicker than the Non-Clustered Index by a little margin. When a Non-Clustered Index is used, an extra lookup from the Non-Clustered Index to the table is required to retrieve the actual data.

12.   In SQL Server, what is a Composite Index? What are the benefits of utilizing a SQL Server Composite Index? What exactly is a Covering Query?

A composite index is a two-or-more-column index, and composite indexes can be both clustered and non-clustered. A covering query is one in which all of the information can be acquired from an index. A clustered index always covers a query if chosen by the query optimizer because it contains all the data in a table.

13.   What are the various index settings available for a table?

One of the following index configurations can be applied to a table: There are no indexes.

A clustered index

Many non-clustered indexes and a clustered index A non-clustered index

Many non-clustered indexes

14.    What is the table's name with neither a Cluster nor a Noncluster Index? What is the purpose of it?

Heap or unindexed table Heap is the name given to it by Microsoft Press Books and Book On-Line (BOL). A heap is a table that does not have a clustered index and does not have pointers connecting the pages. The only structures that connect the pages in a table are the IAM pages.

Unindexed tables are ideal for storing data quickly. It is often preferable to remove all indexes from a table before doing a large number of inserts and then to restore those indexes.

Data Integrity

1.   What is data integrity?

The total correctness, completeness, and consistency of data are known as data integrity. Data integrity also refers to the data's safety and security in regulatory compliance, such as GDPR compliance. It is kept up-to-date by a set of processes, regulations, and standards that were put in place during the design phase. The information in a database will stay full, accurate, and dependable no matter how long it is held or how often it is accessed if the data integrity is protected.

The importance of data integrity in defending oneself against data loss or a data leak cannot be overstated: you must first guarantee that internal users are handling data appropriately to keep your data secure from harmful outside influences. You can ensure that sensitive data is never miscategorized or stored wrongly by implementing suitable data validation and error checking, therefore exposing you to possible danger.

2.   What are data Integrity Types?

There must be a proper understanding of the two forms of data integrity, physical and logical, for maintaining data integrity. Both hierarchical and relational databases are collections of procedures and methods that maintain data integrity.

3.   What is Physical Integrity?

Physical integrity refers to safeguarding data's completeness and correctness during storage and retrieval. Physical integrity is jeopardized when natural calamities hit, electricity goes out, or hackers interrupt database functionality. Data processing managers, system programmers, applications programmers, and internal auditors may be unable to access correct data due to human mistakes, storage degradation, and many other difficulties.

4.   what is Logical Integrity?

In a relational database, logical integrity ensures that data remains intact when utilized in various ways. Logical integrity, like physical integrity, protects data from human mistakes and hackers, but differently. Logic integrity may be divided into four categories:

5.   Explain the Integrity of entities

Entity integrity relies on generating primary keys to guarantee that data isn't shown more than once and that no field in a database is null. These unique values identify pieces of data. It's a characteristic of relational systems, which store data in tables that may be connected and used in many ways.

6.   What is Referential Consistency?

The term "referential integrity" refers to a set of procedures that ensure that data is saved and utilized consistently. Only appropriate modifications, additions, or deletions of data are made, thanks to rules in the database's structure concerning how foreign keys are utilized. Rules may contain limits that prevent redundant data input, ensure proper data entry, and prohibit entering data that does not apply.

7.   What is Domain Integrity?

Domain integrity is a set of operations that ensures that each piece of data in a domain is accurate. A domain is a set of permitted values that a column can hold in this context. Constraints and other measures that limit the format, kind, and amount of data submitted might be included.

8.   User-defined integrity

User-defined integrity refers to the rules and limitations that users create to meet their requirements. When it comes to data security, entity, referential, and domain integrity aren't always adequate, and business rules must frequently be considered and included in data integrity safeguards.

9.   What are the risks to data integrity?

The integrity of data recorded in a database can be affected for many reasons. The following are a few examples:

 

Human error: Data integrity is jeopardized when people enter information erroneously, duplicate or delete data, fail to follow proper protocols, or make mistakes when implementing procedures designed to protect data.

A transfer error occurs when data cannot be correctly transferred from one point in a database. In a relational database, transfer errors occur when data is present in the destination table but not in the source table.

Viruses and bugs: Spyware, malware, and viruses are programs that can infiltrate a computer and change, erase, or steal data.

Sudden computer or server breakdowns, as well as issues with how a computer or other device performs, are instances of serious failures that might indicate that your hardware has been hacked. Compromise hardware might cause data to be rendered inaccurately or incompletely. Also, they might limit or reduce data access or make information difficult to utilize.

The following steps can be taken to reduce or remove data integrity risks:

Limiting data access and modifying permissions to prevent unauthorized parties from making changes to data

Validating data, both when it's collected and utilized, ensures that it's accurate.

Using logs to track when data is added, edited, or removed is a good way to back up data.

Internal audits are carried out regularly. Using software to spot errors

 

SQL Cursor

1.   What is a cursor in SQL Server?

A cursor is a database object that represents a result set and handles data one row at a time.

2.   How to utilize the Transact-SQL Cursor

Make a cursor declaration, Activate the cursor, Row by row, get the data. Deallocate cursor, Close cursor.

3.   Define the different sorts of cursor locks

There are three different types of locks. ONLY READ: This stops the table from being updated.

4.   Tips for cursor optimization

When not in use, close the pointer. Remember to deallocate the cursor after closing it.

5.   The cursor's disadvantages and limitations

Cursor consumes network resources by requiring a round-trip each time it pulls a record.