MySQL

SQL (Structured Query Language) is a standard programming language used for managing and manipulating relational databases. SQL is used to perform tasks such as creating tables, inserting data, updating records, and querying data from databases. In MySQL, SQL commands allow you to interact with the database and perform operations like defining tables, adding rows, and modifying the database structure.

Below is a breakdown of common SQL operations with examples focusing on creating and managing tables for a company, its employees, departments, and branches.

1. Creating a Table in SQL

The CREATE TABLE command is used to define a new table in the database. Each table is made up of columns that represent the fields of the entity being described. Here's how to create a table for a company:

CREATE TABLE company (
  company_id INT AUTO_INCREMENT PRIMARY KEY,
  company_name VARCHAR(100) NOT NULL,
  founded_year YEAR NOT NULL,
  company_address VARCHAR(255),
  company_phone VARCHAR(20)
);

1. company_id: An integer column that serves as the primary key and uniquely identifies each company.
2. company_name: A string column (VARCHAR) that stores the company's name and cannot be NULL.
3. founded_year: A YEAR column storing the year the company was founded.
4. company_address: AVARCHARcolumn storing the company's address (optional).
5. company_phone: A VARCHARcolumn storing the company's contact number (optional).

2. Inserting Data into Tables in SQL

After defining the table, you can add rows using theINSERT INTOcommand. Each row represents a new record.

INSERT INTO company (company_name, founded_year, company_address, company_phone)
VALUES ('Tech Innovations', 2010, '123 Tech Street, Silicon Valley', '+1234567890');

1. company_name: 'Tech Innovations' is inserted into the company name column.
2. founded_year: 2010 is inserted into the founded year column.
3. company_address: '123 Tech Street, Silicon Valley' is inserted into the address column.
4. company_phone: '+1234567890' is inserted into the phone number column.

3. Querying Data from Tables in SQL

The SELECT statement is used to retrieve data from a table. You can specify the columns you want to see or use * to select all columns.

SELECT * FROM employee;

This query retrieves all the rows and columns from the company table.

1. You can also limit the columns to view specific data:

SELECT company_name, company_phone FROM company;

2. SELECT: Retrieves the columns company_name and company_phone from the company table.

4. Updating Data in Tables in SQL

The UPDATEcommand is used to modify existing rows in a table. You can update one or more columns in specific rows using the WHEREclause.

UPDATE company
SET company_phone = '+0987654321'
WHERE company_id = 1;

1. SET: Modifies the company_phone column for the row where the company_idequals 1.
2. This query updates the phone number for the company with company_id = 1.

5. Deleting Data from Tables in SQL

The DELETE statement allows you to remove rows from a table. Be cautious when using this, especially without aWHERE clause.

DELETE FROM company WHERE company_id = 1;

1. DELETE: Removes the row where company_id = 1 from the companytable.
2. If the WHERE clause is omitted, all rows will be deleted, so always use it carefully.

6. Creating More Tables for Employees, Departments, and Branches

You can define more tables to represent other entities like employees, departments, and branches in the same way. For example, here’s how you might create a table for employees:

CREATE TABLE employees (
  employee_id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  email VARCHAR(100),
  hire_date DATE NOT NULL,
  salary DECIMAL(10, 2),
  department_id INT,
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

1. employee_id: A unique identifier for each employee.
2. first_name and last_name: Name fields (required).
3. email: Optional email address for the employee.
4. hire_date: The date when the employee was hired.
5. salary: The employee’s salary stored as a decimal value.
6. department_id: A foreign key referencing the departments table.

7. Adding Foreign Keys for Relationships in SQL

Foreign keys are used to link related tables. For example, the department_id in the employees table links each employee to a department in the departments table:

CREATE TABLE departments (
  department_id INT AUTO_INCREMENT PRIMARY KEY,
  department_name VARCHAR(100) NOT NULL
);

1. department_id: A unique ID for each department.
2. department_name: The name of the department (e.g., "HR", "Finance").

Theemployeestable’sdepartment_id column references this department_id.

8. Creating a Table for Branches in SQL

Finally, here’s a table forbranches, where each branch belongs to a company:

CREATE TABLE branches (
  branch_id INT AUTO_INCREMENT PRIMARY KEY,
  branch_name VARCHAR(100) NOT NULL,
  location VARCHAR(255) NOT NULL,
  branch_manager_id INT,
  FOREIGN KEY (branch_manager_id) REFERENCES employees(employee_id)
);

1. branch_id: The unique identifier for each branch.
2. branch_name: The name of the branch (e.g., "New York Branch").
3. location: The physical location of the branch.
4. branch_manager_id: References the employee who manages the branch.

This overview of SQL basics, with examples focused on company, employees, departments, and branches, should help beginners understand how SQL works and how it can be used to manage data in MySQL.

To gain complete access, login with gmail or outlook, no need of signup, click here

Introduction to SQL

SQL (Structured Query Language) is a standard language for storing, manipulating, and retrieving data in databases.

1. Creating a Table

To store data, we first create a table. Here's how to create anAuthorstable:

CREATE TABLE Authors (
  AuthorID INT,
  FirstName VARCHAR(255),
  LastName VARCHAR(255),
  BirthYear INT
);

• CREATE TABLE Authors- starts the command to create a new table named 'Authors'.
• AuthorID int, FirstName varchar(255), etc.- defines the columns and their data types.

2. Inserting Data

Once a table is created, you can add data to it:

INSERT INTO Authors (AuthorID, FirstName, LastName, BirthYear)
VALUES (1, 'Jane', 'Austen', 1775);

• INSERT INTO Authors- specifies the table to insert data into.
• VALUES (1, 'Jane', 'Austen',1775)- defines the data being inserted.

3. Selecting Data

To retrieve and view data from the table:

SELECT FirstName, LastName FROM Authors;

• SELECT FirstName, LastName- indicates the columns to retrieve.
• FROM Authors- specifies the table to select data from.

4. Updating Data

If data needs correction or updating:

UPDATE Authors
SET BirthYear = 1776
WHERE AuthorID = 1;

• UPDATE Authors- indicates the table where the update will occur.
• SET BirthYear = 1776- specifies the new value for a column.
• WHERE AuthorID = 1- identifies which record(s) to update.

5. Deleting Data

To remove data from the table:

DELETE FROM Authors
WHERE AuthorID = 1;

• DELETE FROM Authors- specifies from which table to delete data.
• WHERE AuthorID = 1- identifies which record(s) to delete.

Each SQL statement serves a specific function, allowing for efficient management and manipulation of data within databases.

Understanding SQL Command Categories

Data Definition Language (DDL)

DDL commands define, alter, and manage the structure of database objects like tables and indexes. These commands affect the schema and architecture of the database rather than the data itself.

• CREATE: Creates new database objects.
• ALTER: Modifies existing database objects.
• DROP: Deletes objects from the database.
• TRUNCATE: Removes all records from a table, deleting the space allocated for the records.

Data Manipulation Language (DML)

DML commands are used for managing data within database tables. These commands allow adding, updating, or deleting data.

• INSERT: Adds new rows to a table.
• UPDATE: Modifies existing data within a table.
• DELETE: Removes rows from a table.
• SELECT: Queries and retrieves data based on specific criteria. Often considered part of DQL, but crucial for data manipulation.

Data Query Language (DQL)

DQL focuses on querying and retrieving data. It allows fetching and organizing data from one or more tables.

• SELECT: The primary command used to query the database for specific information, utilizing clauses likeWHERE,GROUP BY, andORDER BYto refine results.