learnfastaa.com

SQL Database

What is Data type?

Database in SQL is a structured collection of data that is organized and stored for easy retrieval and manipulation.

It typically consists of tables, each having rows and columns, where each column represents a different attribute of the data, and each row represents a unique records.

Database Creation:

To create a database, you use the 'CREATE DATABASE' statement.

                                  
                                    CREATE DATABASE mydatabase;

Database Selection:

Once created, you can select a database to work with using the 'USE' statement.

                                  
                                    USE mydatabase;

Table Creation:

Tables are used to organize and store data. You can define the table structure using the 'CREATE TABLE' statement

                                  
                                    CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

Inserting Data:

You use the 'INSERT INTO' statement to add data to a table.

                                  
                                    INSERT INTO users (id, username, email) VALUES (1, 'john_doe', 'john@example.com');

Selecting Data:

The 'SELECT' statement is used to query the database and retrieve data.

                                  
                                    SELECT * FROM users;

Updating Data:

To modify existing data, you use the 'UPDATE' statement.

                                  
                                    UPDATE users SET email = 'new_email@example.com' WHERE id = 1;

Deleting Data:

The 'DELETE' statement is used to remove records from a table.

                                  
                                    DELETE FROM users WHERE id = 1;

Indexes:

Indexes improve the speed of data retrieval operations on a table. You can add an index to a column using the 'CREATE INDEX' statement.

                                  
                                    CREATE INDEX idx_username ON users (username);

Relationships:

Relationships between tables are established using keys—primary keys and foreign keys. For example, in a user and orders scenario:

                                  
                                    CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    total_amount DECIMAL(8, 2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Views:

Views are virtual tables generated by a query. They allow you to simplify complex queries.

                                  
                                    CREATE VIEW active_users AS
SELECT * FROM users WHERE status = 'active';

Stored Procedures:

Stored procedures are precompiled SQL statements that can be stored and reused.

                                  
                                    CREATE PROCEDURE GetUserById(IN userId INT)
BEGIN
    SELECT * FROM users WHERE id = userId;
END;

Transactions:

Transactions ensure the consistency and integrity of the database. They are initiated using the 'BEGIN TRANSACTION' statement.

                                  
                                    BEGIN TRANSACTION;
-- SQL statements
COMMIT;