My Cloud Journey — Week 4: Databases (MySQL)

This Week's Learning: Databases and SQL

This week, I learned about the world of databases and SQL, exploring foundational concepts, different types of databases, and the powerful commands used to manage and manipulate data. Here’s a detailed breakdown of what I’ve studied:

What is a Database?

A database is an organized collection of data stored and accessed electronically. Databases are designed to manage large amounts of information efficiently, enabling the storage, retrieval, and management of data. They are essential in various applications, from small personal projects to large enterprise systems.

What is SQL?

SQL (Structured Query Language) is a standard programming language used to communicate with and manipulate databases. SQL allows for various operations on the data, such as querying, updating, inserting, and deleting data.

What is NoSQL?

NoSQL (Not Only SQL) refers to a variety of database technologies designed for managing unstructured or semi-structured data. Unlike traditional relational databases (SQL), NoSQL databases can handle large volumes of diverse data and are optimized for specific data models, such as key-value, document, column-family, or graph.

What is DBMS?

DBMS (Database Management System) is software that provides an interface for users to interact with databases. It manages the data, the database engine, and the database schema, facilitating the process of defining, creating, querying, updating, and administering databases.

What is RDBMS?

RDBMS (Relational Database Management System) is a type of DBMS that organizes data into tables (or relations) consisting of rows and columns. RDBMSs use SQL to manage and query data and ensure data integrity through relationships and constraints. An example is MySQL.

Key Concepts and Definitions

• Primary Key: A unique identifier for each row in a table. It must contain unique values and cannot be NULL. Each table can have only one primary key.
• Surrogate Key: An artificial key, usually a sequential number, used solely to uniquely identify records.
• Natural Key: A key that has a logical relationship with the data and is derived from the real-world meaning of the data.
• Foreign Key: A column or set of columns in one table that refers to the primary key in another table, establishing a relationship between the tables.
• Composite Key: A primary key consisting of two or more columns, which together uniquely identify each row in the table.

SQL Command Categories

• DQL (Data Query Language): Commands used to query and retrieve data from the database (e.g., SELECT).
• DDL (Data Definition Language): Commands used to define and modify database structures, such as tables and schemas (e.g., CREATE, ALTER, DROP).
• DCL (Data Control Language): Commands used to control access to data in the database (e.g., GRANT, REVOKE).
• DML (Data Manipulation Language): Commands used to manipulate data in existing database objects (e.g., INSERT, UPDATE, DELETE).

Data Types in DBMS

• Numeric Data Types: INT, FLOAT, DOUBLE, DECIMAL
• Character Data Types: CHAR, VARCHAR, TEXT
• Date and Time Data Types: DATE, TIME, DATETIME, TIMESTAMP, YEAR
• Binary Data Types: BINARY, VARBINARY, BLOB
• Boolean Data Type: BOOLEAN (or BOOL)
• Enumerated Data Type: ENUM

SQL Commands & Queries

• CREATE TABLE: Defines a new table and its columns.

  CREATE TABLE table_name (column1 datatype, column2 datatype, …);

• DESCRIBE: Provides a detailed description of the table structure.

  DESCRIBE table_name;

• DROP TABLE: Deletes a table and all its data.

  DROP TABLE table_name;

• ALTER TABLE (ADD): Adds a new column to an existing table.

  ALTER TABLE table_name ADD column_name datatype;

• ALTER TABLE (DROP COLUMN): Removes a column from an existing table.

  ALTER TABLE table_name DROP COLUMN column_name;

• SELECT * FROM: Retrieves all columns from a table.

  SELECT * FROM table_name;

Constraints

• NOT NULL: Ensures that a column cannot have a NULL value.

  CREATE TABLE table_name (column1 datatype NOT NULL);

• UNIQUE: Ensures that all values in a column are different.

  CREATE TABLE table_name (column1 datatype UNIQUE);

• DEFAULT: Sets a default value for a column when no value is specified.

  CREATE TABLE table_name (column1 datatype DEFAULT default_value);

• AUTO_INCREMENT: Automatically generates a unique number for new rows.

  CREATE TABLE table_name (column1 INT AUTO_INCREMENT);

Data Manipulation

• UPDATE: Modifies existing data in a table.

  UPDATE table_name SET column1 = value1 WHERE condition;

• SET: Used with the UPDATE command to specify the new values for the columns.

  UPDATE table_name SET column1 = value1 WHERE condition;

• WHERE: Filters records that meet a specified condition.

  SELECT column1, column2 FROM table_name WHERE condition;

Comparison Operators

• EQUALS (=): Checks if a column’s value is equal to a specified value.

  SELECT column1 FROM table_name WHERE column1 = value;

• NOT EQUALS (!= or <>): Checks if a column’s value is not equal to a specified value.

  SELECT column1 FROM table_name WHERE column1 != value; 
  SELECT column1 FROM table_name WHERE column1 <> value;

• GREATER THAN (>): Checks if a column’s value is greater than a specified value.

  SELECT column1 FROM table_name WHERE column1 > value;

• LESS THAN (<): Checks if a column’s value is less than a specified value.

  SELECT column1 FROM table_name WHERE column1 < value;

• GREATER THAN OR EQUAL (>=): Checks if a column’s value is greater than or equal to a specified value.

  SELECT column1 FROM table_name WHERE column1 >= value;

• LESS THAN OR EQUAL (<=): Checks if a column’s value is less than or equal to a specified value.

  SELECT column1 FROM table_name WHERE column1 <= value;

Sorting and Limiting Results

• ORDER BY: Sorts the result set of a query by one or more columns.

  SELECT column1, column2 FROM table_name ORDER BY column1 ASC/DESC;

• DESC: Sorts the result set in descending order.

  SELECT column1, column2 FROM table_name ORDER BY column1 DESC;

• ASC: Sorts the result set in ascending order.

  SELECT column1, column2 FROM table_name ORDER BY column1 ASC;

• LIMIT: Specifies the number of rows to return.

  SELECT column1, column2 FROM table_name LIMIT number;

Combining Conditions

• AND: Combines two or more conditions in a WHERE clause, and all conditions must be true.

  SELECT column1, column2 FROM table_name WHERE condition1 AND condition2;

• OR: Combines two or more conditions in a WHERE clause, and at least one condition must be true.

  SELECT column1, column2 FROM table_name WHERE condition1 OR condition2;

Set Operations

• IN: Checks if a column’s value is within a specified set of values.

  SELECT column1, column2 FROM table_name WHERE column1 IN (value1, value2, …);

Foreign Keys and Referential Integrity

• FOREIGN KEY: Establishes a relationship between columns in two tables, ensuring the integrity of the data.

  FOREIGN KEY (column_name) REFERENCES other_table (other_column);

• REFERENCES: Specifies the table and column that the foreign key references.

  FOREIGN KEY (column_name) REFERENCES other_table (other_column);

• ON DELETE SET NULL: Sets the foreign key to NULL when the referenced row is deleted.

  FOREIGN KEY (column_name) REFERENCES other_table (other_column) ON DELETE SET NULL;

• ON DELETE CASCADE: Deletes related rows in the child table when the referenced row is deleted.

  FOREIGN KEY (column_name) REFERENCES other_table (other_column) ON DELETE CASCADE;

Aggregation Functions

• DISTINCT: Retrieves unique values from a column, removing duplicates.

  SELECT DISTINCT column1 FROM table_name;

• COUNT: Counts the number of rows that match a specified condition.

  SELECT COUNT(column1) FROM table_name WHERE condition;

• AVG: Calculates the average value of a numeric column.

  SELECT AVG(column1) FROM table_name;

• **SUM

:** Calculates the total sum of a numeric column.

  SELECT SUM(column1) FROM table_name;

Wildcards and Pattern Matching

• WILDCARDS: Used with the LIKE operator to search for a specified pattern in a column.
  • %: Zero, one, or multiple characters.
  • _: A single character.

  SELECT column1 FROM table_name WHERE column1 LIKE ‘pattern’;

Joins

• INNER JOIN: Selects records that have matching values in both tables.

  SELECT table1.column1, table2.column2 FROM table1 INNER JOIN table2 ON table1.common_field = table2.common_field;

• LEFT JOIN: Selects all records from the left table and the matched records from the right table. The result is NULL from the right side if there is no match.

  SELECT table1.column1, table2.column2 FROM table1 LEFT JOIN table2 ON table1.common_field = table2.common_field;

• RIGHT JOIN: Selects all records from the right table and the matched records from the left table. The result is NULL from the left side if there is no match.

  SELECT table1.column1, table2.column2 FROM table1 RIGHT JOIN table2 ON table1.common_field = table2.common_field;

• FULL OUTER JOIN: Selects all records when there is a match in either left or right table. Records not matching will contain NULL.

  SELECT table1.column1, table2.column2 FROM table1 FULL OUTER JOIN table2 ON table1.common_field = table2.common_field;

Nested Queries

• NESTED QUERIES: A query within another query, also known as a subquery.

  SELECT column1 FROM table_name WHERE column1 = (SELECT column1 FROM table_name WHERE condition);

Triggers

• TRIGGERS (DELIMITER): A set of instructions that are executed automatically in response to a specified event on a specified table.

  DELIMITER //
  CREATE TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name
  FOR EACH ROW 
  BEGIN 
      -- Trigger logic 
  END; 
  // 
  DELIMITER ;

Conclusion

This comprehensive study of databases and SQL has equipped me with the knowledge and skills to design, manage, and manipulate data effectively. Understanding databases is crucial for cloud engineering. In cloud environments, databases often need to be scalable, highly available, and capable of handling large volumes of data. My learning this week has laid the groundwork for working with cloud-based database services in the future. I’m excited to continue exploring and applying these concepts in real-world scenarios!

🔜 𝐍𝐞𝐱𝐭 𝐂𝐡𝐚𝐥𝐥𝐞𝐧𝐠𝐞: 𝐀𝐖𝐒 𝐂𝐞𝐫𝐭𝐢𝐟𝐢𝐞𝐝 𝐂𝐥𝐨𝐮𝐝 𝐏𝐫𝐚𝐜𝐭𝐢𝐭𝐢𝐨𝐧𝐞𝐫

I’m thrilled to announce that next week, I’ll be starting the 𝑨𝑾𝑺 𝑪𝒆𝒓𝒕𝒊𝒇𝒊𝒆𝒅 𝑪𝒍𝒐𝒖𝒅 𝑷𝒓𝒂𝒄𝒕𝒊𝒕𝒊𝒐𝒏𝒆𝒓 𝑪𝒆𝒓𝒕𝒊𝒇𝒊𝒄𝒂𝒕𝒊𝒐𝒏 𝑪𝒐𝒖𝒓𝒔𝒆 (𝑪𝑳𝑭-𝑪02). This course will mark the beginning of my journey into the world of AWS, the leading cloud service provider. I can’t wait to share my insights and progress with you all.

Asif Khan — Aspiring Cloud Architect | Weekly Cloud Learning Chronicler

LinkedIn/Twitter/GitHub