1. What is SQL?
Answer: The standard language for managing and interacting with relational databases is called Structured Query Language, or SQL. It enables us to carry out a number of tasks, including creating database structures, updating records, removing data, and querying data. SQL is a crucial tool for effectively managing massive volumes of structured data.
2. What are the different types of SQL statements?
Answer: This is the most asked question in the interview. Based on their functionality, SQL statements are divided into the following categories:
DDL (Data Definition Language): These statements provide or alter the database's structure. For example:
CREATE is used to create a new table.
ALTER makes changes to an existing table's structure.
DROP removes a table from the database.
DML (Data Manipulation Language): These statements are used to manage the data in the tables. For example:
SELECT is used to retrieve data.
INSERT is used to add new data.
UPDATE modifies existing records.
DELETE removes records.
DCL (Data Control Language): Data access is managed by DCL (Data Control Language) statements. For example:
GRANT gives users access permissions.
REVOKE eliminates permissions that have already been granted.
TCL (Transaction Control Language): Database transactions are managed by these TCL (Transaction Control Language) statements. For example:
COMMIT saves changes made by a transaction.
ROLLBACK undoes changes if an error occurs. If an error happens, ROLLBACK reverses the modifications.
3. What is a primary key?
Answer: A primary key is a column, or group of columns, in a table that uniquely identifies every. It prevents NULL values and guarantees that no two rows have the same value in the primary key column(s). There can only be one primary key in a table. For instance, since every student has a unique ID, the StudentID column in a Students table may serve as a primary key.
4. What is a foreign key?
Answer: A foreign key is a column in one table that creates a connection with the primary key of another table. By upholding the requirement that the value in the foreign key column correspond to a value in the referenced primary key column, it guarantees data integrity. A table can have multiple foreign keys as it can link to multiple tables.
5. What is a unique key?
Answer: A unique key is a column, or set of columns, that guarantees each value in the key is different across table rows. A table may have more than one unique key, and unlike a primary key, a unique key may permit NULL values. To guarantee that no two employees have the same email address, for instance, the Email column in an Employees table might serve as a unique key. We can have more than one unique key in a table.
6. What is the difference between DELETE and TRUNCATE?
Answer: Difference between DELETE and TRUNCATE:
Feature | DELETE | TRUNCATE |
Classification | Part of DML (Data Manipulation Language). | Part of DDL (Data Definition Language). |
Purpose | Removes specific rows from a table based on a condition. | Removes all rows from a table. |
Condition | Can use a WHERE clause to specify rows to delete. | Cannot use a WHERE clause; all rows are removed. |
Rollback | Changes can be rolled back if a transaction is used. | Changes cannot be rolled back. |
Performance | Slower as it logs each row deletion. | Faster as it doesn't log individual row deletions. |
7. What is a view in SQL?
Answer: In SQL, a view is a virtual table that is produced by running a query. It offers a dynamic representation of the data from one or more tables rather than storing the data itself. Views are helpful for:
Simplifying difficult questions.
Limiting access to specific rows or columns in order to add an extra degree of security.
Displaying derived or aggregated info.
8. What are joins in SQL?
Answer: Joins in SQL are used to combine rows from two or more tables based on a related column between them. They allow you to retrieve data that is spread across multiple tables by creating a logical relationship between them. For example, if we have a Customers table and an Orders table, we can use a join to find all orders made by a specific customer.
9. What are the types of joins in SQL?
Answer: n SQL, joins are used to group rows from two or more tables together according to a common column. By creating logical associations, they enable us to get data from several tables. In SQL, the main kinds of joins are:
INNER JOIN
Returns rows from both tables that have the same values.
Rows in neither table that have a match are not included.
LEFT OUTER JOIN (LEFT JOIN)
Returns the matched rows from the right table along with every row from the left table.
NULL values are returned for the right table's columns if there is no match.
RIGHT OUTER JOIN (RIGHT JOIN)
Returns the matched rows from the left table along with every row from the right table.
Columns in the left table yield NULL values if there is no match.
FULL OUTER JOIN (FULL JOIN)
When either table matches, rows are returned.
NULL values for the columns of that table will be present in rows when there is no match in one of the tables.
CROSS JOIN
Yields the two tables' Cartesian product.
Each row in the second table corresponds to every row in the first table.
SELF-JOIN
Connects itself to a table.
Frequently employed when contrasting rows in the same table.
10. What is normalization?
Reduce the amount of duplicate data.
Make sure the data is consistent.
Make updates and maintenance easier.
11. What is the difference between WHERE and HAVING?
WHERE | HAVING | |
Purpose | Filters rows before any grouping or aggregation is performed. | Filters groups or aggregated data after grouping is done. |
Used With | Non-aggregated columns. | Aggregate functions like SUM, COUNT, etc. |
Execution Order | Applied first, before grouping. | Applied after the GROUP BY clause. |
Example | SELECT * FROM Employees WHERE Department = 'Sales'; | SELECT Department, COUNT(*) FROM Employees GROUP BY Department HAVING COUNT(*) > 10; |
12. What are constraints in SQL?
Answer: Constraints are rules applied to table columns to ensure data integrity. Examples include:
NOT NULL: Ensures a column cannot have NULL values.
UNIQUE: Ensures all values in a column are unique.
CHECK: Ensures values meet a specific condition.
13. What is a subquery?
Answer: A query nestled inside another query is called a subquery. The SELECT, FROM, WHERE, and HAVING clauses can all make use of it. For sophisticated inquiries where data depends on other queries, subqueries are frequently utilized. A subquery could return a single value or multiple values.
14. What is the difference between CHAR and VARCHAR?
Answer: CHAR and VARCHAR are used to store character strings.
Feature | CHAR | VARCHAR |
Length | CHAR is Fixed-length string data type. | VARCHAR is Variable-length string data type. |
Storage | This allocates the defined length, even if the actual data is shorter. | This allocates only the length of the data stored. |
Performance | CHAR is faster for fixed-length data. | VARCHAR is more efficient for varying-length data. |
Padding | It pads with spaces to reach the defined length. | It does not pad with spaces. |
15. How do you change the data type of a column?
Answer: Use the ALTER TABLE statement to modify a column's data type:
16. What is the difference between INNER JOIN and OUTER JOIN?
Answer:
Feature | INNER JOIN | OUTER JOIN |
Definition | Combines rows with matching values in both tables. | Combines matching rows and also includes nonmatching rows from one or both tables. |
Output | It returns only matching rows. | It returns all matching rows and non-matching rows with NULL for missing values. |
Types | It does not have any types. | OUTER JOIN includes LEFT JOIN, RIGHT JOIN, and FULL JOIN. |
Example | SELECT * FROM A INNER JOIN B ON A.id = B.id; | SELECT * FROM A LEFT JOIN B ON A.id = B.id; |
17. What is the difference between UNION and UNION ALL?
Answer:
Feature | UNION | UNION ALL |
Definition | Combines the results of two or more queries and removes duplicates. | Combines the results of two or more queries and includes duplicates. |
Performance | It is slower due to duplicate elimination. | It is faster as it does not check for duplicates. |
Use Case | UNION is used when duplicate rows need to be avoided. | UNION ALL is used when duplicates are acceptable or desirable. |
Example | SELECT City FROM Customers UNION SELECT City FROM Suppliers; | SELECT City FROM Customers UNION ALL SELECT City FROM Suppliers; |
18. What are the types of relationships in databases?
Answer:
Relationship Types:
One-to-One: A table's rows match those of another table one to one.
Example: One address in another table is connected to a person's profile.
One-to-Many: A table's rows match several rows in another table.
Example: a client may place several orders.
Many-to-Many: This technique, which is frequently used with a junction table, matches many rows in one table to numerous rows in another table.
Example: An educational system's students and courses
19. What is a composite key?
Answer: A table's composite key is a combination of two or more columns that gives each row a unique identity. It is employed when rows cannot be individually identified by a single column.
For instance, OrderID and ProductID together can function as a composite key in an OrderDetails table to guarantee that every row is uniquely recognized.
20. What is a schema in SQL?
Answer: A logical framework called a schema is used to arrange and structure database objects including views, tables, stored procedures, functions, and indexes. It facilitates efficient database object management and makes it possible for users to access particular items without difficulty.
Example: Tables like Employees, Departments, and Salaries may be found in an HR schema in an employee database.
21. What is the purpose of COALESCE in SQL?
Answer: The COALESCE function, returns first non-NULL value in a list of expressions, which handles NULL values. This is very helpful for giving default settings when NULL is present.
Syntax:
Example:
Suppose there is a Users table with a Phone column that has some NULL values. To return the first available contact information (either Phone or Email), we can use:
22. Write a SQL query to fetch all records from a given table say, employees table.
Answer: To retrieve all records from a table, use the SELECT * statement:
23. How do you retrieve unique values from a column?
24. What are window functions in SQL?
Answer: window functions carry out computations across a collection of rows connected to the current row , without collapsing them into a single output row. They are frequently employed for moving averages, running totals, and ranking.
Typical Window Features:
ROW_NUMBER(): Gives every row in a partition a distinct number.
RANK(): Gives each row a rank, omitting duplicate ranks.
With DENSE_RANK(), a rank is assigned without ignoring duplicates.
NTILE(): Creates a certain number of groups from rows.
25. What is the difference between RANK() and DENSE_RANK()?
Answer:
Feature | RANK() | DENSE_RANK() |
Handling of Duplicates | Skips ranks if there are duplicates | Does not skip ranks for duplicates. |
Result | The rank numbers may have gaps. | The rank numbers are continuous. |
Example | If two employees have the same salary, they will share the same rank, but the next rank will skip. | If two employees have the same salary, they will share the same rank, and the next rank will follow directly. |
Syntax Example | RANK() OVER (ORDER BY salary DESC) | DENSE_RANK() OVER (ORDER BY salary DESC) |
26. How do you filter records using a specific condition?
Answer: You can filter records using the WHERE clause:
27. What is a domain in SQL?
Answer: In SQL, a domain is a range of values that a column in a database table may contain. It specifies the ranges and data types that are acceptable for the values in a certain column. The domain would be the range of integers from 1 to 100, for instance, if a column was designed to hold only integers between 1 and 100. Domains aid in maintaining the integrity and consistency of data.
For instance, if we wish to limit the values in the age column of a users database to integers between 18 and 100, we would design a domain that does just that.
28. What is the difference between a table and a view?
Answer:
Feature | Table | View |
Data Storage | Physically stores data in the database | Does not store data; it is a virtual table |
Definition | A table is a collection of data organized into rows and columns. | A view is a virtual table created by a query that can pull data from one or more tables. |
Modifiable | A table can be modified with DML operations (INSERT, UPDATE, DELETE) | A view may or may not be modifiable, depending on the underlying query. |
Purpose | Used to store and organize data persistently. | Used to simplify complex queries or present data in a specific format. |
29. How do you retrieve records in ascending order?
Answer: The ORDER BY clause combined with the ASC keyword is used to get records in ascending order. If no keyword is supplied, ORDER BY sorts by default in ascending order; nevertheless, ASC expressly indicates the sorting direction.
Syntax:
30. What is the purpose of the DEFAULT constraint?
Answer: The DEFAULT constraint provides a default value for a column when no value is specified during insertion. When no value is supplied during an insert operation, a column's default value is assigned using the DEFAULT constraint. In the event that the user does not specifically insert a value, it guarantees that the column has a predefined value.
As an illustration, let's say we have a students table with a column status that, in the absence of a value, should default to "active"
31. What is the difference between SQL and MySQL?
Answer: SQL is a standard language for managing relational databases, while MySQL is a specific relational database management system (RDBMS) that uses SQL.
Feature | SQL | MySQL |
Definition | SQL (Structured Query Language) is a standardized language used for managing and manipulating relational databases. | MySQL is an open-source relational database management system (RDBMS) that uses SQL as its query language. |
Purpose | SQL is a query language used for interacting with databases (creating, retrieving, updating, and deleting data). | MySQL is a database management system that implements SQL for managing and manipulating data stored in a database. |
Scope | SQL is a language used in all relational database systems. | MySQL is one specific RDBMS that uses SQL. |
32. What are NULL values in SQL?
Answer: In a database, NULL stands for missing or undefined values. It's critical to realize that NULL is not equivalent to any other value, including zero (0) and an empty string (''). The value is either unknown or has not been assigned, as indicated by NULL.
Use of NULL: Any column that permits it, with the exception of those that have the NOT NULL constraint, may be allocated NULL.
NULL vs. Zero: NULL is the lack of a value, whereas zero (0) is a numeric value.
NULL vs. Empty String: While NULL indicates that there is no value present, an empty string ('') is nevertheless regarded as a value.
33. What is a candidate key?
Answer: A candidate key is a column in a table, or a group of columns, that can be used to uniquely identify each row. There may be more than one candidate key in a table, and each one may serve as the primary key. Only one candidate key is selected as the primary key, though.
Uniqueness: Every candidate key needs to have a different value for every row.
Absence of NULL values: To guarantee that it can uniquely identify each table row, a candidate key cannot contain NULL values.
34. What is an alternate key?
Answer: Any candidate key that wasn't chosen to be a table's primary key is called an alternate key. Although there may be more than one candidate key in a table, only one of them becomes the primary key; the others are known as alternate keys.
35. How do you get the total number of records in a table?
Answer: The COUNT() function is used to determine the total number of records in a table. The COUNT() method counts the number of rows that satisfy a given criteria, or counts all of the table's rows in the absence of a condition.
Syntax:
36. What is SQL aggregate functions?
Answer: Aggregate functions perform calculations on multiple rows and return a single value. Before using the function, records are usually grouped into subgroups using these functions in conjunction with the GROUP BY clause.
Typical SQL aggregate functions consist of:
SUM(): Determines a numeric column's total sum
AVG(): Determines a numeric column's average value.
COUNT(): Determines how many rows (or non-NULL values) there are in a given column.
MAX(): Determines a column's maximum value.
MIN(): Determines a column's lowest value.
37. How do you update a specific record in a table?
Answer: Use the UPDATE statement to modify a record:
38. What is a DBMS?
Answer: Software that allows users to create, administer, and interact with databases is known as a database management system, or DBMS. It offers the features required to save, retrieve, update, and work with data. Data security, integrity, and effective access are all aided by a DBMS.
Essential Tasks:
Data retrieval and storage
Data manipulation
Recovery and backup
Control of access and security
For instance, Oracle DB, PostgreSQL, and MySQL are well-known DBMS programs.
39. What is an RDBMS?
Answer: RDBMS(Relational Database Management System) is one kind of DBMS that stores data in tables made up of rows and columns. Primary keys and foreign keys are used to support associations between tables, and structured query language (SQL) is used to interact with the data.
Key Features: Tables (relations) are used to store data.
Data is arranged in columns and rows.
Employs SQL for data management and querying.
Supports the links between tables and the integrity of the data.
For instance, RDBMSs include Microsoft SQL Server, PostgreSQL, and MySQL.
40. What is the difference between DBMS and RDBMS?
Answer:
Aspect | DBMS | RDBMS |
Data Storage | Stores data as files (e.g., flat files) | Stores data in tables (relations) |
Relationships | Does not support relationships between data | Supports relationships using primary and foreign keys |
Data Integrity | Lacks support for data integrity constraints | Ensures data integrity through constraints (e.g., PRIMARY KEY, FOREIGN KEY) |
Query Language | Does not use SQL for data manipulation | Uses SQL to interact with data |
Examples | Microsoft Access, FoxPro, or SQLite | MySQL, PostgreSQL, Oracle, MS SQL Server |
41. How do you add a new column to an existing table?
Answer: Use the ALTER TABLE statement to add a new column:
42. What is the role of SQL in RDBMS?
Answer: SQL (Structured Query Language) is the standard language for maintaining and dealing with relational databases in an RDBMS. In addition to defining and maintaining database schemas, it enables users to query, change, and manage data contained in tables.
SQL's Role:
Querying: Use SELECT to retrieve particular data from the database.
Data manipulation: Use the INSERT, UPDATE, and DELETE commands to add, edit, and remove data.
Data Definition: Use CREATE, ALTER, and DROP to define and change database structures.
Data Control: Use GRANT and REVOKE to manage security and permissions.
43. What is cardinality in databases?
Answer: Cardinality refers to the number of relationships between rows in one table and rows in another table in databases. It specifies whether two tables have a one-to-one, oneto-many, or many-to-many relationship.
44. What is data redundancy?
Answer: The term "data redundancy" describes the needless repetition of information in a database. This happens when the same piece of data is kept in several locations, which increases the risk of inconsistency, causes storage inefficiencies, and raises maintenance expenses.
For instance, keeping a customer's address in several tables (such as order records and customer information) when it might be kept in a single table.
45. What is a unique constraint?
Answer: The UNIQUE constraint ensures that all values in a column are distinct.
46. What is a NOT NULL constraint?
Answer: A column cannot contain NULL values because to the NOT NULL constraint. It ensures that if a row is added or modified, a field must always contain a value.
For instance, to guarantee that no employee record is created without a valid birthdate, a NOT NULL constraint can be applied to a birthdate column in a workers table.
47. What are SQL data types?
Answer: The sorts of data that can be placed in a column are specified by SQL data types. These data types aid in carrying out appropriate operations on the data and guarantee that it is stored in a consistent fashion.
Typical SQL data types include:
INTEGER: For whole numbers, such as 5, 100, etc.
VARCHAR: Used for variable-length strings (e.g., 'John', 'hello').
DATE: Used to store date values ('2024-12-01', for example).
For floating-point numbers (such as 3.14 and 100.5), use FLOAT.
48. What are scalar functions in SQL?
Answer: Scalar functions return a single value and include functions like LEN(), UPPER(), and ROUND(). Depending on the values entered, scalar functions in SQL return a single value. These functions work with discrete values and produce a string, number, or date as their output.
Scalar function examples include:
LEN(): Gives back a string's length.
UPPER(): Changes a string's capitalization.
ROUND(): Rounds a numeric value to a specified decimal place.
49. What is the GROUP BY clause?
Answer: The GROUP BY clause, which is frequently used with aggregate methods like SUM(), AVG(), COUNT(), etc., aggregates rows with the same values in designated columns into aggregated data. It makes it possible to operate on sets of rows as opposed to single rows.
50. What is the HAVING clause?
Answer: The HAVING clause is used to filter grouped data, similar to the WHERE clause for individual rows. To filter the results of a GROUP BY query, use the HAVING clause.
Although it is used after the grouping process, it functions similarly to the WHERE clause and enables conditions to be applied to aggregated data.
Kommentare