SQL

DATABASE

---

A database is a structured collection of data that is organized and stored in a way that allows for efficient retrieval and manipulation of that data.
They are used to persist data beyond the lifespan of an application or server, and offer features such as atomicity, consistency, isolation, and durability (ACID) to ensure data integrity.
The ACID properties of a database refer to four key features that ensure data consistency and reliability:

• Atomicity: Transactions are treated as a single, indivisible unit of work, so if any part of the transaction fails, the entire transaction is rolled back to its original state.
• Consistency: Data is subject to predefined rules and constraints, and any attempted modification must meet these criteria. If not, the transaction is rolled back.
• Isolation: Transactions are isolated from each other, so that concurrent transactions do not interfere with one another. Each transaction operates as if it were the only one executing.
• Durability: Once a transaction is committed, it is guaranteed to persist, even in the face of system failures or power outages.

In addition to the ACID properties, a solid database also provides strong performance and scalability. Since databases deal with input/output (I/O) operations, which can be a bottleneck, their performance can significantly affect the overall performance of an application. A good database is designed to handle large volumes of data and many concurrent requests efficiently, while also being able to scale to handle increasing demand over time. This means that inserting one user in a small collection of data should take about the same time as inserting one user in a very large collection of data, without causing any significant slowdowns or performance issues.
CRUD operations refer to the four basic operations that can be performed on data in a database:

• Create: Adding new data to the database
• Read: Retrieving data from the database
• Update: Modifying existing data in the database
• Delete: Removing data from the database

A reliable and efficient database should allow for all four of these operations to be performed, as they are essential for managing and manipulating data in a persistent and secure manner.

DBMS

---

A Database Management System (DBMS) is a software system that is used to manage and control access to a database. It provides an interface for users to interact with the database, allowing them to perform tasks such as inserting, updating, deleting, and querying data. The DBMS is responsible for managing the storage and retrieval of data, ensuring the data is consistent, and enforcing data security and access controls.

TYPES OF DBMS

There are several types of Database Management Systems (DBMS), including:

• Relational DBMS (RDBMS): These systems store and manage data in tables that are related to each other through keys or relationships. Examples include MySQL, Oracle, Microsoft SQL Server, and PostgreSQL.
• NoSQL DBMS: These systems store and manage data in non-tabular structures, such as key-value, document, column-family, and graph databases. Examples include MongoDB, Couchbase, Cassandra, and Neo4j.
• Object-oriented DBMS (OODBMS): These systems store and manage data in objects, which can include attributes and methods. Examples include Objectivity/DB and ObjectStore.
• Hierarchical DBMS: These systems store and manage data in a tree- like structure, with parent-child relationships. Examples include IBM's Information Management System (IMS) and Windows Registry.
• Network DBMS: These systems store and manage data in a network- like structure, with records linked by pointers. Examples include Integrated Data Store (IDS) and CA-IDMS.
• Cloud-based DBMS: These systems store and manage data in the cloud, allowing for scalability and flexibility. Examples include Amazon Web Services (AWS), Relational Database Service (RDS), Microsoft Azure SQL Database, and Google Cloud SQL.

SQL

SQL (Structured Query Language) is a programming language used to manage and manipulate relational databases. It is commonly used for tasks such as creating, updating, and querying data in databases. SQL is used by many relational database management systems (RDBMS) such as MySQL, Oracle, and Microsoft SQL Server.

MySQL

MySQL is a free, open-source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL) to manage and store data. It is one of the most popular databases in use today and is commonly used for web applications, data warehousing, and e-commerce. MySQL is known for its scalability, reliability, and ease of use, and it supports a variety of programming languages and platforms. It is developed and maintained by Oracle Corporation and is available for Windows, Linux, and macOS. Some popular applications that use MySQL include WordPress, Drupal, Joomla, and Magento.

INSTALLATION

---

• Open a terminal window
• Update the package index and upgrade the system with the command:

sudo apt update
sudo apt upgrade

• Install the MySQL server package with the command:

sudo apt install mysql-server

• After the installation is complete, start the MySQL service and enable it to start automatically on boot with the command:

sudo systemctl start mysql or sudo service mysql start
sudo systemctl enable mysql 

• Run the security script to secure the installation by setting the root password and removing insecure default settings:

sudo mysql_secure_installation

Follow the on-screen prompts to answer the questions and set the root password.
MySQL is now installed on your Ubuntu 20.04 system. You can access it by running the mysql command from the terminal.

Creating a dedicated User

Upon installation, MySQL creates a root user account which you can use to manage your database. This user has full privileges over the MySQL server, meaning it has complete control over every database, table, user, and so on. Because of this, it’s best to avoid using this account outside of administrative functions. This step outlines how to use the root MySQL user to create a new user account and grant it privileges.
You must invoke mysql with sudo privileges to gain access to the root MySQL user:

sudo mysql

Once you have access to the MySQL prompt, you can create a new user with a CREATE USER statement. These follow this general syntax:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

After creating your new user, you can grant them the appropriate privileges. The general syntax for granting user privileges is as follows:

GRANT PRIVILEGE ON 'database'.'table' TO 'username'@'host';

The PRIVILEGE value in this example syntax defines what actions the user is allowed to perform on the specified database and table.
You can grant multiple privileges to the same user in one command by separating each with a comma.
You can also grant a user privileges globally by entering asterisks (*) in place of the database and table names. In SQL, asterisks are special characters used to represent “all” databases or tables.

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, INDEX, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'sammy'@'localhost' WITH GRANT OPTION;

Note that this statement also includes WITH GRANT OPTION. This will allow your MySQL user to grant any permissions that it has to other users on the system. The above command grants a user global privileges to CREATE, ALTER, and DROP databases, tables, and users, as well as the power to INSERT, UPDATE, and DELETE data from any table on the server. It also grants the user the ability to query data with SELECT, create foreign keys with the REFERENCES keyword, and perform FLUSH operations with the RELOAD privilege. However, you should only grant users the permissions they need.

GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION;

The above command grants superuser privileges to the user on all databases and tables. It’s good practice to run the FLUSH PRIVILEGES command. This will free up any memory that the server cached as a result of the preceding CREATE USER and GRANT statements:

FLUSH PRIVILEGES;

Then you can exit the MySQL client:

exit

In the future, to log in as your new MySQL user, you’d use a command like the following:

mysql -u sammy -p

The -p flag will cause the MySQL client to prompt you for your MySQL user’s password in order to authenticate.

Testing MySQL

check its status.

systemctl status mysql.service

or

sudo service mysql status

This will show you the current status of the MySQL service, whether it is running or not.
If the MySQL service is not running, you can start it with the following command:

sudo systemctl start mysql

or

sudo service mysql start

If the MySQL service is running, but you want to stop it, you can use the following command:

sudo service mysql stop

For an additional check, you can try connecting to the database using the mysqladmin tool, which is a client that lets you run administrative commands. For example, this command says to connect as a MySQL user named sammy (-u sammy), prompt for a password (-p), and return the version.

sudo mysqladmin -p -u sammy version

BASIC SQL STATEMENTS

---

SQL statements can be broadly categorized into two types: Data Definition Language (DDL) and Data Manipulation Language (DML).
DDL statements are used to create, modify, and delete database objects such as tables, indexes, and views. Some examples of DDL statements are:

• CREATE TABLE: used to create a new table in a database

  CREATE TABLE students (id INT, name VARCHAR(50), age INT);

• ALTER TABLE: used to modify the structure of an existing table

  ALTER TABLE students ADD email VARCHAR(50);

• DROP TABLE: used to delete a table from the database

  DROP TABLE students;

• CREATE INDEX: creates an index on one or more columns of a table.

   CREATE INDEX idx_name ON students (name);

DML statements are used to retrieve, insert, update, and delete data in a database. Some examples of DML statements are:

• SELECT: used to retrieve data from one or more tables in a database.

  SELECT name, age FROM students WHERE age > 18;

• INSERT: used to insert new rows of data into a table

  INSERT INTO students (id, name, age) VALUES (1, 'John', 20);

• UPDATE: used to modify existing data in a table

  UPDATE students SET age = 21 WHERE id = 1;

• DELETE: used to delete rows of data from a table

  DELETE FROM students WHERE age < 18;

BASIC SQL CLAUSES

---

In SQL, a clause is a keyword or a combination of keywords and expressions that are used to define a specific action within a SQL statement. Here are some common SQL clauses and their uses:

• SELECT: used to retrieve data from one or more tables in a database.

  SELECT column1, column2 FROM table1;

• FROM: used to specify the table(s) from which to retrieve data.

• WHERE: used to filter data based on one or more conditions.

  SELECT column1, column2 FROM table1 WHERE column3 = 'value';

• GROUP BY: used to group rows based on one or more columns.

  SELECT score, COUNT(*) AS number FROM second_table GROUP BY score 
  ORDER BY number DESC;

• HAVING: used to filter data based on conditions applied to groups.

• ORDER BY: used to sort data in ascending or descending order.

  SELECT score, name FROM second_table ORDER BY score DESC;

• LIMIT: used to limit the number of rows returned by a query.

  SELECT column1, column2 FROM table1 LIMIT 10;

• COUNT: used to count the number of rows that meet a specified condition.

  SELECT COUNT(*) FROM table1 WHERE column3 = 'value';

• SUM: used to calculate the sum of a column.

  SELECT SUM(column1) FROM table1;

• MAX: used to retrieve the maximum value of a column.

  SELECT MAX(column1) FROM table1;

• MIN: used to retrieve the minimum value of a column.

  SELECT MIN(column1) FROM table1;

• AVG: used to calculate the average value of a column.

  SELECT AVG(column1) as average FROM table1;

• JOIN: used to combine rows from two or more tables based on a related column.

• INNER JOIN: used to combine only the matching rows from two or more tables.

• LEFT JOIN: used to combine all rows from the left table and the matching rows from the right table.

• RIGHT JOIN: used to combine all rows from the right table and the matching rows from the left table.

• FULL OUTER JOIN: used to combine all rows from both tables.

These clauses can be combined in various ways to form complex SQL statements that retrieve, filter, and manipulate data in a database.

Backtick and an apostrophe

A backtick is used to enclose identifiers that contain special characters, spaces, or reserved keywords. This allows these identifiers to be used as column or table names without causing syntax errors. For example, the following SQL statement uses a backtick to enclose the column name that contains a space:

SELECT `column name` FROM table1;

On the other hand, an apostrophe is used to delimit strings. When using strings in SQL queries, the values must be enclosed in apostrophes. For example, the following SQL statement uses apostrophes to enclose the string value:

SELECT * FROM table1 WHERE column1 = 'string value';