PART-1 : Introduction to SQL and Database Fundamentals

DATABASE

• A database is an organized collection of structured information or data stored electronically, allowing for easy access, management, and retrieval.

• A Database Management System (DBMS) is software that helps create, manage, and manipulate databases, allowing users to interact with data easily.

• DBMS is used in various industries, including healthcare, finance, retail, education, telecommunications, transportation and logistics, manufacturing, hospitality, government, and media and entertainment.

Types Of Databases

They are divided into two main categories, which are as follows:

1. Relational Databases: These databases organize data into structured tables with predefined relationships, using SQL for querying. Examples include MySQL, PostgreSQL, and Oracle Database.

2. Non-Relational Databases (NoSQL): These databases handle unstructured or semi-structured data and do not require a fixed schema, allowing for greater flexibility and scalability. Examples include MongoDB, Cassandra, and Redis.

MySQL & Workbench

• MySQL is an open-source relational database management system (RDBMS) that uses SQL for managing and manipulating structured data.

• MySQL Workbench is a visual database design and administration tool that allows users to manage MySQL databases through an intuitive graphical interface.

• MySQL Server is the core database engine of MySQL, handling data storage, processing, and multi-user access in a client-server setup.

• Learn how to install MySQL on your system.

SQL Data Types

An SQL developer needs to know what data type will be stored in each column when creating a table.

Like in other programming languages, SQL also has certain datatypes available. A brief idea of all the datatypes is discussed below.

The data type guideline for SQL helps understand what kind of data is expected in each column and determines how SQL will interact with the stored data. Read more

Constraints in SQL

Here’s a table of common SQL constraints with descriptions:

These constraints help enforce data integrity and consistency within SQL databases. Read more

CRUD Operations

These four operations are fundamental to managing data in relational databases and are often implemented through SQL commands.

1. Create: The process of adding new records or entries to a database, enabling the storage of new data.

2. Read: The operation of retrieving and viewing existing data from the database, often performed using queries to fetch specific information.

3. Update: The action of modifying or altering existing records in the database to reflect changes or corrections in the data.

4. Delete: The operation of removing existing records from the database, which permanently eliminates the specified data entries.

We will explore the SQL commands later in this series. Read more

SQL Commands

Here are the SQL commands used for each CRUD operation:

1. Create:

   • SQL Command: INSERT

   • Example:

       INSERT INTO table_name (column1, column2, column3) 
       VALUES (value1, value2, value3);
     

2. Read:

   • SQL Command: SELECT

   • Example:

       SELECT column1, column2 
       FROM table_name 
       WHERE condition;
     

3. Update:

   • SQL Command: UPDATE

   • Example:

       UPDATE table_name 
       SET column1 = value1, column2 = value2 
       WHERE condition;
     

4. Delete:

   • SQL Command: DELETE

   • Example:

       DELETE FROM table_name 
       WHERE condition;
     

Fieldnames in SQL

In SQL, field names (also known as column names) are the identifiers used to reference the attributes or properties of a table. Each field name corresponds to a specific type of data stored in a column of that table. Here are some key points about field names in SQL:

1. Definition: Field names are used to identify the columns in a database table, defining what type of data is stored in each column.

2. Naming Conventions:

   • Field names should be descriptive and indicative of the data they hold (e.g., first_name, last_name, email).

   • Typically, field names are written in lowercase or snake_case (using underscores to separate words).

   • Avoid using spaces, special characters, or reserved SQL keywords.

3. Examples of Field Names:

   • id - A unique identifier for each record (often used as a primary key).

   • created_at - A timestamp indicating when the record was created.

   • updated_at - A timestamp indicating when the record was last updated.

   • status - A field representing the state or condition of a record (e.g., active, inactive).

   • price - A numerical field for storing monetary values.

4. Field Name Usage:

   • Field names are used in SQL commands to specify which columns to select, update, or delete.

   • They can also be used in JOIN operations to relate data across different tables.

5. Case Sensitivity: Field names may be case-sensitive depending on the database system being used. It’s generally a good practice to maintain consistent casing.

Here’s a simple example of a table structure showing field names:

CREATE TABLE users (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    created_at DATETIME,
    updated_at DATETIME
);

In this example, id, first_name, last_name, email, created_at, and updated_at are all field names in the users table.

Comments

-- this is single line comment 
/*
--- multi line comment 
*/

Case Study 1

• Check existing databases on your system.

    SHOW DATABASES;
  

• STEP 1.1 : Create a database

    CREATE DATABASE IF NOT EXIST buildwithronnie
  

• STEP 1.2: Use a specific database / start working with a specific database

    USE buildwithronnie
  

• STEP 2.1 : Create a Table [Run this after adding fields, data types, and constraints]

    CREATE TABLE employee (
    -- feild names , data types , constraints
    )
  

• STEP 2.2 : Initializing Field Names to the Table

    CREATE TABLE employee (
    -- initialized field names for our table 
    EID  
    FirstName 
    LastName 
    Age 
    Salary 
    Location 
    )
  

• STEP 2.3: Initializing Data Types to the Table

    CREATE TABLE employee (
    -- initialized datatypes to the feild names 
    EID  INT 
    FirstName varchar(50) 
    LastName varchar(50)
    Age INT 
    Salary INT 
    Location varchar(50)
    )
  

• STEP 2.4: Initializing Constraints to the Table

    CREATE TABLE employee (
    -- initialized constraints to feild names
    -- primary key constraint is NOT NULL by default 
    EID  INT AUTO_INCREMENT PRIMARY KEY, 
  
    FirstName varchar(50)  NOT NULL,
    LastName varchar(50)  NOT NULL,
    Age INT  NOT NULL,
    Salary INT  NOT NULL,
    Location varchar(50)  NOT NULL
    )
  

• Composite PRIMARY KEY [this is another way to declare a PRIMARY KEY constraint]

    CREATE TABLE employee (
    EID  INT AUTO_INCREMENT,
    PH_NO varchat(15),   -- new field added 
    FirstName varchar(50)  NOT NULL,
    LastName varchar(50)  NOT NULL,
    Age INT  NOT NULL,
    Salary INT  NOT NULL,
    Location varchar(50)  NOT NULL
    /*
    -- A composite primary key uses multiple columns to uniquely identify 
    each record in a table.
    */
    PRIMARY KEY(EID,PH_NO)
  
    )
  

• Display a list of all tables in the current database

    CREATE TABLE employee (
    EID  INT AUTO_INCREMENT, 
    PH_NO varchat(15), 
    FirstName varchar(50)  NOT NULL,
    LastName varchar(50)  NOT NULL,
    Age INT  NOT NULL,
    Salary INT  NOT NULL,
    Location varchar(50)  NOT NULL
    PRIMARY KEY(EID,PH_NO)
  
    SHOW TABLES 
    -- This command will list all tables in the database currently selected.
  
    )
  

• Display the structure of a table

    CREATE TABLE employee (
    EID  INT AUTO_INCREMENT,  
    PH_NO varchat(15), 
    FirstName varchar(50)  NOT NULL,
    LastName varchar(50)  NOT NULL,
    Age INT  NOT NULL,
    Salary INT  NOT NULL,
    Location varchar(50)  NOT NULL
    PRIMARY KEY(EID,PH_NO)
  
    /*
    -- This command will show information about each column along with it’s 
    field names, data type, constraints
    */ 
    DESC employee 
    )
  

• Manual Insertion

    CREATE TABLE employee (
    EID  INT AUTO_INCREMENT, 
    PH_NO varchat(15),  
    FirstName varchar(50)  NOT NULL,
    LastName varchar(50)  NOT NULL,
    Age INT  NOT NULL,
    Salary INT  NOT NULL,
    Location varchar(50)  NOT NULL
    PRIMARY KEY(EID,PH_NO)
    )
  
    -- Insert values for 5 employees
    -- is used to add new records (rows) to a table
    INSERT INTO employee 
    (EID, PH_NO, FirstName, LastName, Age, Salary, Location) 
    VALUES
    (1, '1234567890', 'Vicky', 'Singh', 25, 200000, 'Hyderabad'),
    (2, '9876543210', 'Farhaan', 'Modi', 30, 250000, 'Delhi'),
    (3, '1122334455', 'Nisha', 'Kumari', 28, 220000, 'Mumbai'),
    (4, '5566778899', 'Soorya', 'Yadev', 35, 100000, 'Bangalore'),
    (5, '6677889900', 'Khushi', 'Gour', 27, 340000, 'Chennai');
  

• Read Table

    CREATE TABLE employee (
    EID  INT AUTO_INCREMENT, 
    PH_NO varchat(15),  
    FirstName varchar(50)  NOT NULL,
    LastName varchar(50)  NOT NULL,
    Age INT  NOT NULL,
    Salary INT  NOT NULL,
    Location varchar(50)  NOT NULL
    PRIMARY KEY(EID,PH_NO)
    )
    INSERT INTO employee 
    (EID, PH_NO, FirstName, LastName, Age, Salary, Location) 
    VALUES
    (1, '1234567890', 'Vicky', 'Singh', 25, 200000, 'Hyderabad'),
    (2, '9876543210', 'Farhaan', 'Modi', 30, 250000, 'Delhi'),
    (3, '1122334455', 'Nisha', 'Kumari', 28, 220000, 'Mumbai'),
    (4, '5566778899', 'Soorya', 'Yadev', 35, 100000, 'Bangalore'),
    (5, '6677889900', 'Khushi', 'Gour', 27, 340000, 'Chennai');
  
    /*
    --will display all the data from the employee table, including every column 
    (EID, PH_NO, FirstName, LastName, Age, Salary, Location) 
    for each row in the table. 
    Basically retrieves all columns and rows from a specified table
    */
    SELECT * FROM employee;
  
    /*
    EXPLANATION : 
  
    -- SELECT *: The asterisk (*) selects all columns in the table.
    -- FROM employee: Specifies the employee table as the source of the data.
    -- This command provides a complete view of the data in employee
    */
  

• Delete a Record from the Table

    CREATE TABLE employee (
    EID  INT AUTO_INCREMENT,
    PH_NO varchat(15),
    FirstName varchar(50)  NOT NULL,
    LastName varchar(50)  NOT NULL,
    Age INT  NOT NULL,
    Salary INT  NOT NULL,
    Location varchar(50)  NOT NULL
    PRIMARY KEY(EID,PH_NO)
    )
    INSERT INTO employee
    (EID, PH_NO, FirstName, LastName, Age, Salary, Location)
    VALUES
    (1, '1234567890', 'Vicky', 'Singh', 25, 200000, 'Hyderabad'),
    (2, '9876543210', 'Farhaan', 'Modi', 30, 250000, 'Delhi'),
    (3, '1122334455', 'Nisha', 'Kumari', 28, 220000, 'Mumbai'),
    (4, '5566778899', 'Soorya', 'Yadev', 35, 100000, 'Bangalore'),
    (5, '6677889900', 'Khushi', 'Gour', 27, 340000, 'Chennai');
  
    -- Delete the record of EID = 4
    -- Delete is used to delete specific rows from a table based on a condition.
    DELETE FROM employee
    WHERE EID = 4
  
    /*
    EXPLANATION:
  
    - - DELETE FROM employee: Deletes rows from the employee table.
    -- WHERE EID = 4: Specifies the condition to delete the row where the
    EID column is equal to 4.
    -- The WHERE clause is crucial to ensure that only the row(s)
    matching the condition are deleted. If you omit the WHERE clause,
    all rows in the table will be deleted.
    -- This action is permanent. If you accidentally delete data,
    it cannot be undone unless you have a backup.
    */
  

• Delete Table from the Database

    CREATE TABLE employee (
    EID  INT AUTO_INCREMENT, 
    PH_NO varchat(15),  
    FirstName varchar(50)  NOT NULL,
    LastName varchar(50)  NOT NULL,
    Age INT  NOT NULL,
    Salary INT  NOT NULL,
    Location varchar(50)  NOT NULL
    PRIMARY KEY(EID,PH_NO)
    )
    INSERT INTO employee 
    (EID, PH_NO, FirstName, LastName, Age, Salary, Location) 
    VALUES
    (1, '1234567890', 'Vicky', 'Singh', 25, 200000, 'Hyderabad'),
    (2, '9876543210', 'Farhaan', 'Modi', 30, 250000, 'Delhi'),
    (3, '1122334455', 'Nisha', 'Kumari', 28, 220000, 'Mumbai'),
    (4, '5566778899', 'Soorya', 'Yadev', 35, 100000, 'Bangalore'),
    (5, '6677889900', 'Khushi', 'Gour', 27, 340000, 'Chennai');
  
    /*
     This command is used to delete an entire table, 
    including all of its data and structure, from the database. 
    Once executed, this action cannot be undone and the data 
    will be permanently lost.
    */
    DROP employee;
  

Key Differences

1. Update vs Alter

• DML Read more

• DDL Read more

• SQL Commands are mainly categorized into five categories, Read more

  

2. Data structure vs Databases

Auto Increment On Case Study-1

Q1. Convert the employee table so that the EID column starts auto-incrementing from the value 200 from CASE STUDY 1.

ALTER TABLE employee1 AUTO_INCREMENT = 200;

Filter Operation On Case Study-1

Q1. Give me the employee details having salary more than 200000.

-- this query will give full details of the employee
SELECT * FROM employee WHERE Salary > 200000

-- this query will provide the user with only limited info 
SELECT FirstName, LastName FROM employee 
WHERE Salary > 200000

Q2. Give me the records of the employee having age more than 25.

-- this query will give full details of the employee
SELECT * FROM employee WHERE Age > 25

-- this query will provide the user with only limited info 
SELECT FirstName, LastName FROM employee 
WHERE Salary > 25

Update Operation On Case Study-1

Q1. Update the last name of EID 3

UPDATE employee SET LastName = "Tudu"
UPDATE EID = 3