Mastering MySQL RDBMS: Comprehensive Bootcamp for Beginners

Creating tables is the foundation of any relational database. In MySQL, you define a table's structure using various datatypes that specify the kind of data each column can hold. Here’s how you create a table and define columns with different datatypes:

```

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) UNIQUE,

    hire_date DATE,

    salary DECIMAL(10, 2),

    department_id INT

);

```

In this example:

• employee_id is an integer that auto-increments with each new record and serves as the primary key.

• first_name and last_name are variable character strings up to 50 characters.

• email is a unique variable character string up to 100 characters.

• hire_date is a date datatype.

• salary is a decimal with 10 digits, 2 of which are after the decimal point.

• department_id is an integer.

The WHERE clause is used to filter records that meet certain conditions. It allows you to specify which rows should be included in the results.

```

SELECT * FROM employees

WHERE department_id = 5 AND salary > 50000;

```

This query selects all employees who work in department 5 and earn more than $50,000.

The DISTINCT keyword is used to return only distinct (different) values, eliminating duplicates. The ALIAS keyword allows you to assign a temporary name to a column or table.

```

SELECT DISTINCT department_id FROM employees;

SELECT first_name AS fname, last_name AS lname FROM employees;

```

The first query retrieves all unique department IDs. The second query selects employees' first and last names and renames the columns to fname and lname respectively.

The ORDER BY clause is used to sort the result set in ascending or descending order.

```

SELECT * FROM employees

ORDER BY last_name ASC;

SELECT * FROM employees

ORDER BY salary DESC;

```

The first query sorts employees by their last names in ascending order. The second query sorts employees by their salaries in descending order.

The LIMIT clause is used to specify the number of records to return. OFFSET specifies the number of rows to skip before starting to return rows.

```

SELECT * FROM employees

ORDER BY hire_date DESC

LIMIT 10;

SELECT * FROM employees

ORDER BY hire_date DESC

LIMIT 10 OFFSET 5;

```

The first query retrieves the 10 most recently hired employees. The second query skips the first 5 and then retrieves the next 10 most recently hired employees.

Aggregate functions perform calculations on a set of values and return a single value. Common aggregate functions include COUNT, SUM, AVG, MIN, and MAX.

```

SELECT COUNT(*) AS total_employees FROM employees;

SELECT AVG(salary) AS average_salary FROM employees;

SELECT MIN(hire_date) AS earliest_hire FROM employees;

```

The first query counts the total number of employees. The second query calculates the average salary of all employees. The third query finds the earliest hire date among the employees.

The GROUP BY clause is used to arrange identical data into groups. The HAVING clause is used to filter groups based on a condition, often used with aggregate functions.

```

SELECT department_id, COUNT(*) AS total_employees

FROM employees

GROUP BY department_id;

SELECT department_id, AVG(salary) AS average_salary

FROM employees

GROUP BY department_id

HAVING AVG(salary) > 60000;

```

The first query groups employees by department_id and counts the total employees in each department. The second query groups employees by department_id and shows the average salary for each department, but only for those departments where the average salary is greater than $60,000.

Constraints are rules enforced on data columns to ensure data integrity. Common constraints include PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK.

```

CREATE TABLE orders (

    order_id INT AUTO_INCREMENT PRIMARY KEY,

    order_date DATE NOT NULL,

    customer_id INT,

    amount DECIMAL(10, 2) CHECK (amount > 0),

    CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

);

```

In this example:

• PRIMARY KEY ensures each order_id is unique and not null.

• NOT NULL ensures order_date is always provided.

• CHECK ensures the amount is greater than 0.

• FOREIGN KEY creates a relationship between the orders table and the customers table, enforcing referential integrity.

Logical operators are used to combine multiple conditions in a SQL query.

```

SELECT * FROM employees

WHERE department_id = 5 AND salary > 50000;

SELECT * FROM employees

WHERE department_id = 5 OR salary > 50000;

SELECT * FROM employees

WHERE NOT department_id = 5;

```

The first query selects employees in department 5 who earn more than $50,000. The second query selects employees either in department 5 or earning more than $50,000. The third query selects employees who are not in department 5.

The IN operator allows you to specify multiple values in a WHERE clause

```

SELECT * FROM employees

WHERE department_id IN (3, 5, 7);

```

This query selects all employees who are in departments 3, 5, or 7.

The LIKE operator is used to search for a specified pattern in a column.

```

SELECT * FROM employees

WHERE first_name LIKE 'A%';

SELECT * FROM employees

WHERE email LIKE '%@example.com';

```

The first query selects all employees whose first names start with 'A'. The second query selects all employees whose email addresses end with '@example.com'.

The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.

```

SELECT * FROM employees

WHERE hire_date BETWEEN '2020-01-01' AND '2021-12-31';

SELECT * FROM employees

WHERE salary BETWEEN 40000 AND 60000;

```

The first query selects all employees who were hired between January 1, 2020, and December 31, 2021. The second query

The UPDATE statement is used to modify existing records in a table, while the DELETE statement is used to remove records from a table.

```

UPDATE employees

SET salary = salary * 1.10

WHERE department_id = 5;

```

This query increases the salary of all employees in department 5 by 10%.

```

DELETE FROM employees

WHERE department_id = 5 AND salary < 30000;

```

This query deletes all employees in department 5 who earn less than $30,000.

Transactions are sequences of SQL operations that are executed as a single unit of work. They are used to ensure data integrity, especially in scenarios involving multiple related changes. Transactions are typically managed using the BEGIN, COMMIT, and ROLLBACK statements.

```

START TRANSACTION;

UPDATE accounts

SET balance = balance - 100

WHERE account_id = 1;

UPDATE accounts

SET balance = balance + 100

WHERE account_id = 2;

COMMIT;

```

In this example, $100 is transferred from account 1 to account 2. If any part of the transaction fails, a ROLLBACK can be issued instead of COMMIT to undo the changes and ensure data consistency.

A PRIMARY KEY uniquely identifies each record in a table, while a FOREIGN KEY establishes a link between records in two tables.

```

CREATE TABLE departments (

    department_id INT AUTO_INCREMENT PRIMARY KEY,

    department_name VARCHAR(50) NOT NULL

);

```

Here, department_id is the primary key for the departments table.

```

CREATE TABLE employees (

    employee_id INT AUTO_INCREMENT PRIMARY KEY,

    first_name VARCHAR(50) NOT NULL,

    last_name VARCHAR(50) NOT NULL,

    department_id INT,

    FOREIGN KEY (department_id) REFERENCES departments(department_id)

);

```

In this example:

• employee_id is the primary key for the employees table.

• department_id is a foreign key in the employees table that references department_id in the departments table, creating a relationship between the two tables.

The INNER JOIN keyword selects records that have matching values in both tables.

```

SELECT employees.first_name, employees.last_name, departments.department_name

FROM employees

INNER JOIN departments ON employees.department_id = departments.department_id;

```

This query selects the first and last names of employees along with their department names, showing only the records where there is a match between employees.department_id and departments.department_id.

The CROSS JOIN keyword returns the Cartesian product of two tables, meaning it combines all rows from the first table with all rows from the second table.

```

SELECT employees.first_name, departments.department_name

FROM employees

CROSS JOIN departments;

```

This query returns every possible combination of employees.first_name and departments.department_name.

The LEFT JOIN keyword returns all records from the left table and the matched records from the right table. The RIGHT JOIN keyword returns all records from the right table and the matched records from the left table.

```

SELECT employees.first_name, employees.last_name, departments.department_name

FROM employees

LEFT JOIN departments ON employees.department_id = departments.department_id;

```

This query returns all employees, including those without a matching department, with NULL values for departments that don't have a match.

```

SELECT employees.first_name, employees.last_name, departments.department_name

FROM employees

RIGHT JOIN departments ON employees.department_id = departments.department_id;

```

This query returns all departments, including those without a matching employee, with NULL values for employees that don't have a match.

Multiple joins are used to combine more than two tables.

```

SELECT employees.first_name, employees.last_name, departments.department_name, locations.location_name

FROM employees

INNER JOIN departments ON employees.department_id = departments.department_id

INNER JOIN locations ON departments.location_id = locations.location_id;

```

This query joins three tables: employees, departments, and locations, to display employee names, their department names, and the location names.

A subquery is a query within another query. It can be used to return data that will be used in the main query as a condition.

```

SELECT first_name, last_name

FROM employees

WHERE salary > (SELECT AVG(salary) FROM employees);

```

This query selects the first and last names of employees whose salaries are greater than the average salary of all employees. The subquery (SELECT AVG(salary) FROM employees) calculates the average salary.

Refer this lecture for sample database. attached link redirects to sample database available on Github

Refer this lecture for sample database. attached link redirects to sample database available on Github