SQL Practice - I

[anuragrawat7]

Relational Database

• It is a type of database that store and organizes the data in a structured way.
• Data in a relational database is stored in various data tables, each of which has a unique key identifying every row.

RDBMS benefits and limitations

• Structured Data: RDBMS allows data storage in a structured way, using rows and columns in tables. This makes it easy to manipulate the data using SQL (Structured Query Language), ensuring efficient and flexible usage.

• ACID Properties: ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure reliable and safe data manipulation in a RDBMS, making it suitable for mission-critical applications.

• Normalization: RDBMS supports data normalization, a process that organizes data in a way that reduces data redundancy and improves data integrity.

• Scalability: RDBMS generally provide good scalability options, allowing for the addition of more storage or computational resources as the data and workload grow.

• Data Integrity: RDBMS provides mechanisms like constraints, primary keys, and foreign keys to enforce data integrity and consistency, ensuring that the data is accurate and reliable.

• Security: RDBMS offer various security features such as user authentication, access control, and data encryption to protect sensitive data.

Here are some of the limitations of using an RDBMS:

• Complexity: Setting up and managing an RDBMS can be complex, especially for large applications. It requires technical knowledge and skills to manage, tune, and optimize the database.

• Cost: RDBMSs can be expensive, both in terms of licensing fees and the computational and storage resources they require.

• Fixed Schema: RDBMS follows a rigid schema for data organization, which means any changes to the schema can be time-consuming and complicated.

• Handling of Unstructured Data: RDBMSs are not suitable for handling unstructured data like multimedia files, social media posts, and sensor data, as their relational structure is optimized for structured data.

• Horizontal Scalability: RDBMSs are not as easily horizontally scalable as NoSQL databases. Scaling horizontally, which involves adding more machines to the system, can be challenging in terms of cost and complexity.

[anuragrawat7]

JOINs

• SQL Joins are used to retrieve data from two or more data tables, based on a related column between them. The key types of JOINs include:

1. INNER JOIN: This type of join returns records with matching values in both tables. SELECT table1.column1, table2.column2... FROM table1 INNER JOIN table2 ON table1.matching_column = table2.matching_column;

2. LEFT (OUTER) JOIN: Returns all records from the left table, and matched records from the right table. SELECT table1.column1, table2.column2... FROM table1 LEFT JOIN table2 ON table1.matching_column = table2.matching_column;

3. RIGHT (OUTER) JOIN: Returns all records from the right table, and matched records from the left table. SELECT table1.column1, table2.column2... FROM table1 RIGHT JOIN table2 ON table1.matching_column = table2.matching_column;

4. FULL (OUTER) JOIN: Returns all records when either a match is found in either left (table1) or right (table2) table records. SELECT table1.column1, table2.column2... FROM table1 FULL JOIN table2 ON table1.matching_column = table2.matching_column;

5. SELF JOIN: A self join is a join in which a table is joined with itself. SELECT a.column_name, b.column_name... FROM table_name AS a, table_name AS b WHERE condition;

6. CARTESIAN JOIN: If WHERE clause is omitted, the join operation produces a Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. SELECT table1.column1, table2.column2... FROM table1, table2;

[anuragrawat7]

Data Constraints

Data constraints in SQL are used to specify rules for the data in a table. Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table.

Types of SQL Data Constraints

1. NOT NULL Constraint: Ensures that a column cannot have a NULL value. CREATE TABLE Students ( ID int NOT NULL, Name varchar(255) NOT NULL, Age int );

2. UNIQUE Constraint: Ensures that all values in a column are different. CREATE TABLE Students ( ID int NOT NULL UNIQUE, Name varchar(255) NOT NULL, Age int );

3. PRIMARY KEY Constraint: Uniquely identifies each record in a database table. Primary keys must contain UNIQUE values. Exactly the same as the UNIQUE constraint but there can be many unique constraints in a table, but only one PRIMARY KEY constraint per table. CREATE TABLE Students ( ID int NOT NULL, Name varchar(255) NOT NULL, Age int, PRIMARY KEY (ID) );

4. FOREIGN KEY Constraint: Prevents actions that would destroy links between tables. A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table. CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, ID int, PRIMARY KEY (OrderID), FOREIGN KEY (ID) REFERENCES Students(ID) );

5. CHECK Constraint: The CHECK constraint ensures that all values in a column satisfies certain conditions. CREATE TABLE Students ( ID int NOT NULL, Name varchar(255) NOT NULL, Age int, CHECK (Age>=18) );

6. DEFAULT Constraint: Provides a default value for a column when none is specified. CREATE TABLE Students ( ID int NOT NULL, Name varchar(255) NOT NULL, Age int, City varchar(255) DEFAULT 'Unknown' );

7. INDEX Constraint: Used to create and retrieve data from the database very quickly. CREATE INDEX idx_name ON Students (Name);