A database is a structured repository for storing, organizing, and managing data. It consists of collections of data organized into tables, which can be interrelated. Data in a database can include various types such as text, numbers, dates, and multimedia.

To create a database, use the SQL command:

CREATE DATABASE inventory;

Tables are defined with specific columns and data types. For example, to create a table for storing employee information:

CREATE TABLE employees (
    emp_id INT NOT NULL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    hire_date DATE,
    salary DECIMAL(10,2)
) DEFAULT CHARSET=utf8;

Key concepts include:

• Primary Key: A unique identifier for each row in a table. It ensures no duplicate values and can be a single column or a combination (composite key).
• Data Types: Common types include:
  • INT for integers.
  • VARCHAR for variable-length strings.
  • DATE and DATETIME for dates and timestamps.
  • DECIMAL for precise decimal numbers.

Insert data into a table using INSERT statements. Examples: Insert a single row with partial fields:

INSERT INTO employees (emp_id, first_name) VALUES (101, 'Alice');

Insert a full row:

INSERT INTO employees VALUES (102, 'Bob', 'Smith', 'Engineering', '2023-01-15', 75000.00);

Insert multiple rows at once:

INSERT INTO employees VALUES 
(103, 'Charlie', 'Brown', 'Sales', '2023-02-20', 60000.00),
(104, 'Diana', 'Prince', 'HR', '2023-03-10', 65000.00);

Update existing data with UPDATE. Modify all rows:

UPDATE employees SET department = 'Operations';

Update specific rows based on a condition:

UPDATE employees SET salary = 80000.00 WHERE emp_id = 101;

Delete data or structures. Remove all data from a table:

DELETE FROM employees;

Delete specific rows:

DELETE FROM employees WHERE department = 'Sales';

Drop a table entirely:

DROP TABLE employees;

Drop a database:

DROP DATABASE inventory;

Common considerations:

• Use commas to separate column definitions when creating tables; no separator is needed after the last column.
• For VARCHAR fields, enclose string values in single quotes.
• Ensure data langth does not exceed declared column lengths.
• Primary key values must be unique.
• NOT NULL columns require values during insertion.
• To support multilingual data, set the charset to utf8.
• Insert multiple rows by separating value sets with commas within parentheses.