SQL Interview question and answer

What is SQL?

SQL stands for Structured Query Language. It interacts with databases, creates tables, retrieves data, updates records, and performs various other operations. SQL is an ANSI standard language. Programmers use it to handle and work with data kept in relational database management systems (RDBMS).

Explain the difference between SQL and MySQL.

Structured Query Language, or SQL, and MySQL are two distinct but related concepts:

Structured Query Language, or SQL:

• Relational database management is done with a standardized programming language called SQL.
• It offers a common interface for working with databases, which includes maintaining, defining, and querying data.
• 
  Users may employ SQL with a variety of relational database systems, including MySQL, PostgreSQL, SQLite, Oracle, Microsoft SQL Server, and others. It is not restricted to any one particular database management system (DBMS).
• SQL is categorized into Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), and Transaction Control Language (TCL).

MySQL

• MySQL, a specific relational database management system (RDBMS), utilizes SQL as its query language.
• Web applications frequently employ this open-source relational database system, which is among the most well-liked ones.
• Due to its reputation for speed, dependability, and user-friendliness, MySQL is a preferred option among developers and businesses.
• For better performance and usability, MySQL offers extensions and optimizations in addition to full SQL support for database interaction.

What are the different types of SQL statements and commands?

Statements in the Data Definition Language (DDL):

used for defining, changing, and removing database objects, including views, tables, indexes, and schemas.
Examples include CREATE VIEW, CREATE INDEX, CREATE TABLE, ALTER TABLE, DROP TABLE, and so on.

Statements in Data Manipulation Language (DML):

used for entering, updating, removing, and retrieving data from the database tables.
Examples include DELETE FROM, MERGE, UPDATE, INSERT INTO, and SELECT.

Statements in Data Control Language (DCL):

used to manage permission granting and revocation and restrict access to data within the database.
Examples: GRANT, REVOKE.

Statements in Transaction Control Language (TCL):

used to maintain data consistency and integrity by managing transactions inside the database.
COMMIT, ROLLBACK, SAVEPOINT, and SET TRANSACTION are a few examples.

Statements in Data Query Language (DQL):
Users primarily use a subset of DML to retrieve data from the database.

Examples: SELECT.

What is a database?

Various methods access a structured form of data storage known as a database. It includes schemas, tables, queries, and views. Database Management System (DBMS), allowing efficient storage, retrieval, and manipulation of data.

Examples include the School Management Database and the Bank Management Database.

Difference between CHAR and VARCHAR2 datatype?

CHAR: fixed-length character string.

VARCHAR2: variable-length character string.

What are tables and fields?

Tables:

In a relational database, tables serve as the basic organizational and storage structure for data.
A table is an arrangement of linked data with rows and columns.

In a table, each row denotes a single record or entity, and each column denotes a particular field or characteristic of that object.
Each table typically contains one or more columns, with each column defining a specific type of data that it can hold.
Typically, they select names that correspond to the kind of information they hold.

Fields:

Fields represent individual data points in a table, sometimes called columns or attributes.
Each field is associated with a specific form of data, such as a date, integer, text, or boolean value.

Fields shape the structure of the data stored in the table, encompassing the data type, size, and constraints, like whether a field must be present or permitted to contain null values.

Fields provide the means to organize and categorize data within the table, allowing users to query and manipulate the data effectively.

What is a primary key in SQL?

Every entry in a database table has a unique identifier called a primary key. It guarantees the unique identification of every record and contributes to the preservation of data integrity. A column or set of columns that uniquely identify each row in a table is known as the primary key in the majority of databases.

Uniqueness: A primary key constraint ensures that every value in the selected column(s) is unique across all rows in the table.


Non-null: It further ensures that each entry in the table has a valid identifier by guaranteeing that the values in the column(s) designated as the primary key cannot be NULL.

Indexed: To improve efficiency for search and retrieval operations, the majority of database systems automatically build an index on the main key column or columns.

Single or Composite: A primary key may be made up of a single column or a combination of several columns.

Here’s an example of defining a primary key in SQL:

CREATE stable Employees.

    EmployeeID INT PRIMARY KEY,

    FirstName VARCHAR(50),

    LastName VARCHAR(50),

    …

);

In this example, EmployeeID is designated as the primary key for the Employees table. It must contain unique values and cannot be null.

What is a foreign key in SQL?

In SQL, a foreign key is a column or a set of columns in a table that establishes a relationship between two tables.

Referential Integrity: By establishing a connection between the data in two related tables, a foreign key protects referential integrity. It ensures that the values in one table’s foreign key column(s) match the values in another table’s primary key column(s).

Relationship: They call the table with the corresponding primary key the parent table or referenced table, while the table with the foreign key is referred to as the child table or referencing table.

Constraint: Similar to a primary key, a foreign key serves as a constraint in relational databases to preserve the consistency and integrity of data.

Actions on Update/Delete: When entries in the linked table are changed or deleted, foreign key constraints can additionally indicate what should happen.

Here’s an example of defining a foreign key in SQL:

CREATE TABLE Orders (

    OrderID INT PRIMARY KEY,

    CustomerID INT,

    OrderDate DATE,

    …

    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)

);

In this example, CustomerID in theOrders table is a foreign key that references the CustomerID column in the Customers table.

What is the difference between DELETE and TRUNCATE commands?

The DELETE and TRUNCATE commands in SQL are both used to remove data from a table, but they differ in functionality and the way they affect the table.

DELETE:

• A DML (Data Manipulation Language) command called DELETE is used to delete one or more rows from a table under certain circumstances.
• DELETE can be selective, allowing you to delete just those rows that satisfy the given requirements by using a WHERE clause.
• DELETE creates unique delete operations for each row that satisfy the requirement since it acts on a row-by-row basis.
• A transaction can reverse DELETE statements if needed because they are recorded in the transaction log.
• DELETE initiates any linked triggers and preserves the integrity of related data.

TRUNCATE

• A DDL (Data Definition Language) command called TRUNCATE is used to eliminate every record from a table.
• TRUNCATE clears every record from the table without taking any constraints into account. It simply erases every piece of data from the table, leaving it empty.
• Because TRUNCATE does not create separate delete operations for each row, it is quicker than DELETE. Rather, it resets identification columns and deallocates data pages.
• Since TRUNCATE is not recorded in the transaction log, it cannot be undone. Running TRUNCATE results in permanent loss of data.
• If the table has an identity seed, TRUNCATE returns it to its starting value.

There are no related triggers activated by TRUNCATE.

What is a SQL join? Explain the different types of joins.

An SQL join is a clause used to combine rows from two or more tables based on related columns between them. It allows you to retrieve data from multiple tables simultaneously, creating a virtual table that contains columns from both tables.

There are several types of SQL joins:

1. INNER JOIN:

• Returns rows when there is at least one match in both tables based on the join condition.
  • If no matching rows are found, the INNER JOIN will return an empty result set.
  • Syntax: SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;

• LEFT JOIN (or LEFT OUTER JOIN):

• Returns all rows from the left table (table 1) and the matched rows from the right table (table 2). If there are no matches, NULL values are returned for the columns from the right table.
  • Syntax: SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column;

• RIGHT JOIN (or RIGHT OUTER JOIN):

• Returns all rows from the right table (table 2) and the matched rows from the left table (table 1). If there are no matches, NULL values are returned for the columns from the left table.
  • Syntax: SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;

• FULL JOIN (or FULL OUTER JOIN):

• Returns all rows when there is a match in either left or right table. If there is no match, NULL values are returned for the columns from the table without a match.
  • Syntax: SELECT * FROM table1 FULL JOIN table2 ON table1.column = table2.column;

• CROSS JOIN:

• It returns the Cartesian product of the two tables, meaning it combines every row from the first table with every row from the second table.
  • Syntax: SELECT * FROM table1; CROSS JOIN table2;

What are normalization and denormalization in SQL databases?

Two complimentary methods called normalization and denormalization are employed in relational databases to enhance data throughput, minimize redundancy, and maximize data storage:
Normalization:

• The process of normalizing data in a database involves dividing huge databases into smaller, related tables and developing relationships between them to eliminate repetition and dependence.
• Normalization aims to reduce data abnormalities (such as update, insert, and delete anomalies) and guarantee data integrity. It also aims to remove redundant data.
• Decomposing bigger tables into smaller, more manageable tables and creating primary key and foreign key restrictions to build linkages between them are common steps in the normalization process.
• There are usually several normal forms involved in the normalization process (e.g., First Normal Form (1NF), Second Normal Form (2NF), Third Normal Form (3NF), etc.), each addressing specific types of data redundancy and dependency

Denormalization:

• The procedure that comes after normalization is called denormalization. To reduce the requirement for joins and aggregations and enhance query speed, redundant data is added to one or more tables.
• When the database is optimized for workloads that include a lot of reading and read speed is crucial, denormalization is frequently employed.
• Denormalization may entail aggregating data to provide pre-calculated results, adding duplicate columns, or replicating data from adjacent tables.
• Although denormalization might lead to better query speed, it can also result in data duplication and add to the complexity of data integrity enforcement and maintenance.
• Denormalization should be done carefully and properly with normalization to guarantee data consistency and integrity.

What is a subquery in SQL?

A subquery is a SQL query that is nestled inside another SQL query. It is sometimes referred to as an inner query or nested query. Based on the outcome of another query, it enables you to obtain data from one or more tables. Subqueries are utilized in the SELECT, INSERT, UPDATE, and DELETE statements, among other SQL statement sections.

Syntax: A subquery is enclosed within parentheses and can be placed in different parts of a SQL statement, such as the WHERE clause, FROM clause, HAVING clause, or even within another subquery.

SELECT column1, column2

FROM table1 WHERE column1 IN (SELECT column1 FROM table2 WHERE condition);

How do you optimize SQL queries?

To optimize SQL queries:

1. Use indexes for frequently accessed columns.
2. Retrieve only the necessary data.
3. Minimize JOIN operations.
4. Avoid unnecessary subqueries.
5. Analyze and optimize query execution plans.
6. Use appropriate database configurations.
7. Regularly update the statistics.

What is a trigger in SQL?

In SQL, developers design triggers as special types of stored procedures to automatically execute in response to specific events or actions performed on a table or view within a database. These occasions usually include the INSERT, UPDATE, and DELETE commands. Developers use triggers to automate database processes, preserve data integrity, and enforce business rules.
Timing: Triggers can be categorized according to when they are executed:
BEFORE Trigger: Takes action prior to the triggering incident. This method allows changing the data before adding, modifying, or removing it.
After the trigger: takes action following the occurrence of the trigger. It can be utilized to carry out tasks in response to modifications produced by the triggering event.

Explain ACID properties in SQL?

The ACID properties (Atomicity, Consistency, Isolation, Durability) ensure that database transactions are executed reliably, maintain data integrity, and provide consistency in a multi-user environment.

Atomicity:

A transaction is seen as a single unit of work, either completely performed or not at all, thanks to atomicity.
This feature guarantees data consistency and integrity by preventing transactions from being completed partially.

Consistency:

A transaction moving the database from one valid state to another is guaranteed by consistency.
Transactions follow predetermined guidelines and limitations to maintain the database’s overall consistency.

Isolation:

The process of isolation makes sure that one transaction’s execution is separate from that of other concurrent transactions.
By preventing interference between transactions, isolation makes sure that the result of one transaction doesn’t influence the result of another.

Durability:

Durability ensures that, even in the case of a system crash or failure, the modifications made by a committed transaction are preserved indefinitely.
This characteristic guarantees that data modifications will survive, offering dependability and recoverability in the event of system failures

The WHERE clause: How is it used?

You may get just the rows that meet the criteria you specify by using the WHERE clause in SQL queries to selectively filter results based on predefined parameters. As example:


WHERE department = ‘HR’, SELECT * FROM employees;

Aggregate functions: what are they?

In SQL, aggregate functions calculate a single result after applying a set of values to their computations.


SUM: To determine the total of a column’s values.
COUNT: To determine how many rows or non-null values there are in a column.
AVG: To determine the column’s average of values.
MIN: To obtain a column’s lowest value.
MAX: To obtain a column’s maximum value.

And what distinguishes UNION from UNION ALL?

While UNION ALL combines the results without eliminating duplicates, UNION merges the results of two or more SELECT queries while deleting duplicate entries. UNION ALL is quicker, but duplicate rows could be included.

Which kinds of SQL commands are there?

SELECT: Calls up information from a database.
INSERT Updates a table with new records.
UPDATE: Updates data that already exists in a table.
DELETE: Takes entries out of a table.
CREATE: Produces a fresh table, view, or database.
ALTER: Adjusts the current object structure of the database.
DROP: Removes an already-existing database entry.