1. What is SQL?
SQL (Structured Query Language) is a domain-specific programming language used for managing and manipulating relational databases. It allows users to perform tasks such as querying, updating, inserting, and deleting data in a structured and efficient manner. SQL is widely used in database management, data analysis, and reporting, making it a fundamental tool for working with structured data.
2. What is a Database?
A database is a structured collection of data organized and stored electronically. It is designed to efficiently store, manage, and retrieve data, making it accessible for various applications. They come in different types, including relational databases, NoSQL databases, and more, each with its own unique characteristics and use cases.
3. What is DBMS?
DBMS stands for Database Management System, and it is software that allows users to interact with a database. It provides an interface for data entry, storage, retrieval, and management. A DBMS helps users interact with the database without needing to understand the internal details of how data is stored and organized. It offers data security, data integrity, and data independence, making it easier to manage and work with data.
4. What is RDBMS?
RDBMS, on the other hand, stands for Relational Database Management System. It is a specific type of DBMS that manages data using a structure based on the relational model. In RDBMS, data is organized into tables (relations), and relationships between tables are defined using keys. The most common RDBMS are MySQL, Oracle, Microsoft SQL Server, and PostgreSQL.
5. Difference Between DBMS and RDBMS?
The key differences between a generic DBMS and an RDBMS are:
Data Structure: In a DBMS, data can be stored in various ways, such as hierarchically or as a flat file. In an RDBMS, data is stored in structured tables with predefined schemas.
Data Integrity: RDBMS enforces referential integrity, meaning it ensures that relationships between tables are maintained and that data remains consistent. Generic DBMS may not provide these features.
Query Language: RDBMS uses SQL (Structured Query Language) for querying and managing data, which is a powerful and standardized language. Generic DBMS might have proprietary query languages or limited query capabilities.
Scalability: RDBMS is designed for structured data, and it excels at handling complex queries for structured data. Generic DBMS may be more flexible but less efficient for structured data.
Examples: MySQL, Oracle, and PostgreSQL are examples of RDBMS, while MongoDB and Cassandra are examples of NoSQL databases that are not based on the relational model
6. Difference Between SQL and PL/SQL.
Below is the few difference between SQL and PL/SQL:
| SQL | PL/SQL |
|---|---|
| SQL is used for querying and manipulating data in relational databases. | PL/SQL is used for developing applications, particularly stored procedures, triggers, and functions. |
| SQL is a Declarative language. | PL/SQL is a Procedural language with control flow and logic capabilities. |
| SQL Primarily used for data retrieval, insertion, updating, and deletion. | PL/SQL is used for creating stored procedures and business logic. |
| Lacks control structures like loops, conditionals, and exception handling. | Includes loops, conditionals, and error-handling capabilities. |
| SQL can Execute single statements at a time and return results to the client. | PL/SQL can execute multiple SQL and PL/SQL statements together within a block. |
| Generally faster for range-based queries (e.g., date ranges) or specific lookup by the clustered key. | Improves SELECT, JOIN, and WHERE clause queries on indexed columns. |
| Automatically enforces a unique constraint on the clustered key. | Can be used to enforce unique constraints, but it's not automatic. |
7. What are the subsets of SQL?
SQL (Structured Query Language) has several subsets or categories that define its functionalities and purposes. Here are some key subsets of SQL:
1. DDL (Data Definition Language): It is used to define and manage the structure of the database.
Example:
- CREATE: Creates database objects like tables, indexes, views, etc.
- ALTER: Modifies existing database objects.
- DROP: Deletes database objects.
2. DML (Data Manipulation Language): It is used to manage and manipulate data stored in the database.
Example:
- SELECT: Retrieves data from one or more tables.
- INSERT: Adds new rows of data to a table.
- UPDATE: Modifies existing data in a table.
- DELETE: Removes rows from a table.
3. DCL (Data Control Language): It is used to manage access to data stored in the database.
Example:
- GRANT: Provides specific privileges to users.
- REVOKE: Removes specific privileges from users.
4. TCL (Transaction Control Language): It is used to manage transactions within a database.
Example:
- COMMIT: Saves all the changes made during the current transaction.
- ROLLBACK: Undoes changes made during the current transaction.
- SAVEPOINT: Sets a point in a transaction to which you can later roll back.
6. Joint Query Language (JQL): It deals with the joining of tables.
Example:
- JOIN: Combines rows from two or more tables based on a related column.
These subsets together cover a wide range of functionalities, allowing developers and database administrators to interact with and manage relational databases effectively.
8. What is a primary key in SQL, and why is it used?
A primary key is a unique identifier for each record in a table. It ensures data integrity and enforces uniqueness.
The primary key serves several important purposes:
- Uniqueness: It ensures that each row in the table is unique based on the values in the primary key column(s). This uniqueness constraint helps maintain data integrity and prevents the insertion of duplicate records.
- Identification: It provides a way to uniquely identify and retrieve individual records from the table. This is essential for data retrieval and manipulation.
- Data Integrity: By enforcing uniqueness, the primary key helps maintain data integrity and accuracy within the database. It prevents duplicate, inconsistent, or conflicting data from being stored.
- Referential Integrity: Primary keys often serve as reference points for relationships between tables in a relational database. They are used as foreign keys in related tables, establishing referential integrity and enforcing consistency across related data.
- Indexing: Primary key columns are indexed automatically in most database management systems (DBMS). This indexing improves query performance when searching for specific records.
Example: In this example, the EmployeeID column is designated as the primary key.
CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), );
9. What is a foreign key in SQL, and how does it relate to a primary key?
In SQL, a foreign key is a field or a set of fields in a table that is used to establish a link between data in two related tables. The purpose of a foreign key is to maintain referential integrity and enforce relationships between tables. It acts as a link between data in different tables, creating associations between records.
Example: In this example, the "Orders" table has a foreign key, "CustomerID," which references the "CustomerID" primary key in the "Customers" table. This foreign key relationship ensures that every order is associated with a valid customer.
CREATE TABLE Orders ( OrderID INT PRIMARY KEY, CustomerID INT, OrderDate DATE, -- Other columns FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) );
10. What are Tables and Fileds?
Tables and fields (also referred to as columns) are fundamental components that define the structure of a relational database.
Tables: A table is a database object that represents a collection of related data. It is structured as rows and columns, with each row representing a single record (data entry), and each column defining a specific attribute or property of the data.
Fields: A field, also known as a column, is a named attribute or property within a table. Each field defines the type of data it can hold, and it corresponds to a specific piece of information about the records in the table.
SQL Query to Create Table and its Fileds:
CREATE TABLE Books ( BookID INT PRIMARY KEY, Title VARCHAR(255), Author VARCHAR(100), PublicationYear INT, Price DECIMAL(10, 2) );
11. What are the Constraints in SQL?
Constraints in SQL are rules and conditions applied to a table's columns or a combination of columns to enforce data integrity and maintain the accuracy and consistency of the data stored in a database. They help define and restrict the values that can be inserted, updated, or deleted in a table. Constraints serve as safeguards against data that might violate the rules of the database schema.
List of Constraints:
- PRIMARY KEY: Ensures uniqueness and non-null values for identifying rows.
- FOREIGN KEY: Establishes relationships between tables for referential integrity.
- INDEX: Enhances query performance by creating a structured data access path.
- UNIQUE: Enforces uniqueness but allows null values.
- DEFAULT: Provides a predefined value for a column when no value is specified during an INSERT operation.
- CHECK: Validates data based on custom conditions or expressions.
- NOT NULL: Requires a column to have non-null values.
12. What is the difference between WHERE and HAVING Operators in SQL?
Here are the few difference between WHERE and HAVING Clause:
| WHERE Clause | HAVING Clause |
|---|---|
| Used with the SELECT, UPDATE, and DELETE statements. | Used with the SELECT statement, specifically with aggregate functions like COUNT, SUM, AVG, etc., when using the GROUP BY clause. |
| Appears before the GROUP BY clause (if used) and after the FROM clause. | Appears after the GROUP BY clause. |
| Filters rows before they are grouped and aggregated. | Filters the results of aggregate functions after grouping. |
| Typically used with aggregate functions like COUNT, SUM, AVG, etc. | Used in conjunction with aggregate functions in the SELECT statement. |
| Example: SELECT * FROM employees WHERE salary > 50000; | Example: SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 55000; |
13. What is the difference between BETWEEN and IN Operators in SQL?
Here are the few difference between BETWEEN and IN Clause:
| BETWEEN Clause | IN Clause |
|---|---|
| Filters results within a specified range. | Specifies multiple values in a WHERE clause. |
| Example: SELECT * FROM table WHERE column BETWEEN 10 AND 50; | Example: SELECT * FROM table WHERE column IN (value1, value2, ...); |
| Commonly used for numerical or date ranges. | Used with discrete, specific values. |
| Inclusive of both endpoints in the range. | Matches any value in the specified list. |
| Numeric or date ranges. | Specific values, often in a list. |
| May be more efficient for large ranges. | Efficient for small, specific value lists. |
14. What is JOIN? List of JOINS present in SQL?
In SQL, a JOIN is used to combine rows from two or more tables based on a related column between them. Below are the list of JOINS available to use in SQL.
- INNER JOIN: The INNER JOIN keyword selects records that have matching values in both tables.
Example:
SELECT orders.OrderID, customers.CustomerName FROM orders INNER JOIN customers ON orders.CustomerID = customers.CustomerID;
- LEFT JOIN: The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.
Example:
SELECT customers.CustomerName, orders.OrderID FROM customers LEFT JOIN orders ON customers.CustomerID = orders.CustomerID;
- RIGHT JOIN: The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.
Example:
SELECT customers.CustomerName, orders.OrderID FROM customers RIGHT JOIN orders ON customers.CustomerID = orders.CustomerID;
- FULL JOIN: The FULL JOIN keyword returns all records when there is a match in either left (table1) or right (table2) table records.
Example:
SELECT customers.CustomerName, orders.OrderID FROM customers FULL JOIN orders ON customers.CustomerID = orders.CustomerID;
- SELF JOIN: A self join is a regular join, but the table is joined with itself.
Example:
SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2 FROM customers A, customers B WHERE A.CustomerID = B.CustomerID;
- CROSS JOIN: The CROSS JOIN keyword returns the Cartesian product of the two tables, i.e., all possible combinations of rows.
Example:
SELECT customers.CustomerName, products.ProductName FROM customers CROSS JOIN products;
15. What is the difference between CHAR and VARCHAR2?
CHAR is a fixed-length data type, which means it always allocates a fixed amount of storage for each value, padding with spaces if the actual string is shorter than the allocated space.
VARCHAR2 is a variable-length data type. It only stores the actual length of the data plus some additional bytes for metadata.
16. What is Data Integrity?
Data Integrity in the context of databases refers to the accuracy, consistency, and reliability of data stored in a database. It ensures that the data is valid, and it accurately represents the real-world information it is supposed to reflect. Ensuring data integrity is crucial for the reliability of a database. It prevents issues like duplicate records, inaccurate relationships between tables, or the storage of incorrect data types.
17. Different Types of Relationships in SQL.
In SQL, relations refer to the tables in a relational database. There are different types of relationships between tables, and they are classified as follows:
- 1. One-to-One (1:1) Relationship: In a one-to-one relationship, each record in the first table is related to only one record in the second table, and vice versa. This type of relationship is relatively uncommon. Example: A table of employees may be related to a table of employee addresses, where each employee has only one corresponding address.
- 2. One-to-Many (1:N) Relationship: In a one-to-many relationship, each record in the first table can be related to multiple records in the second table, but each record in the second table is related to only one record in the first table. Example: A table of customers may be related to a table of orders, where each customer can have multiple orders, but each order is associated with only one customer.
- 3. Many-to-One (N:1) Relationship: This is essentially the reverse of a one-to-many relationship. Each record in the first table is related to only one record in the second table, but each record in the second table can be related to multiple records in the first table. Example: A table of orders may be related to a table of customers, where each order is associated with only one customer, but each customer can have multiple orders.
- 4. Many-to-Many (N:N) Relationship: In a many-to-many relationship, each record in the first table can be related to multiple records in the second table, and vice versa. This relationship is implemented using a junction table or associative entity. Example: A table of students may be related to a table of courses, where each student can enroll in multiple courses, and each course can have multiple students.
These relationships are fundamental in designing relational databases, and they help establish connections between different entities, ensuring data integrity and providing a structured way to organize information.
18. What is VIEW in SQL?
In SQL, a VIEW is a virtual table that is based on the result of a SELECT query. It does not store the data itself but provides a way to represent the result set of a SELECT statement as if it were a table. This allows users to encapsulate complex queries and make them appear as simple tables.
Key characteristics of views in SQL:
- Virtual Table: A view is not a physical table. It's a saved SQL query that can be treated like a table.
- Data Security: Views can be used to restrict access to specific columns in a table. For example, a view might expose only certain columns while hiding others.
- Simplified Queries: Views can simplify complex queries by abstracting the underlying data model. They allow users to interact with the database using a more straightforward representation.
- Reusable Queries: Once a view is defined, it can be reused in other queries. This promotes code reusability and simplifies maintenance.
- Joining Tables: Views can be used to join tables and present the joined data as a single virtual table, making it easier to work with related data.
Example:
CREATE VIEW EmployeeInfo AS SELECT EmployeeID, FirstName, LastName, Department FROM Employees WHERE Department = 'IT';
19. What is the difference between DELETE and TRUNCATE?
In SQL, DELETE and TRUNCATE are both used to remove data from a table, but they differ in their operations. DELETE is a DML (Data Manipulation Language) command that removes rows from a table based on a specified condition, such as a WHERE clause. It is more flexible as it allows deleting specific rows, and it can be rolled back if used within a transaction. On the other hand, TRUNCATE is a DDL (Data Definition Language) command that removes all rows from a table but does not log individual row deletions.
TRUNCATE is faster than DELETE because it does not generate individual row delete statements, making it more efficient for large-scale operations. However, TRUNCATE cannot be rolled back, and it does not allow the use of a WHERE clause to conditionally delete rows. The choice between DELETE and TRUNCATE depends on the specific requirements of the operation.
20. What is the difference between DELETE and DROP?
In SQL, DELETE and DROP serve different purposes. DELETE is a DML (Data Manipulation Language) command used to remove rows from a table based on a specified condition, such as a WHERE clause. It is a reversible operation, and the removed data can be rolled back if used within a transaction. On the other hand, DROP is a DDL (Data Definition Language) command used to delete an entire table, database, or other database objects. Unlike DELETE, DROP is irreversible, and it permanently removes the specified object and its data. The choice between DELETE and DROP depends on the scope of the operation, with DELETE being focused on individual rows, and DROP being used for entire objects.
No comments:
Post a Comment