📚 Python Day 11 | Mastering SQL: A Beginner's Guide to Database Management

Introduction

Imagine you have a large collection of books in your personal library. Each book has a title, author, and other information such as publication year, genre, and publisher. If you wanted to find a specific book, you would need to manually search through each book until you found the one you were looking for. This process can be time-consuming and inefficient.

Now, imagine that you had a database of your book collection. The database would be structured in a way that makes it easy to search and update. Each book would be represented as a row in a table, with each column representing a different piece of information about the book. You could search for a book by title, author, or any other criteria, and the database would quickly retrieve the information you need.

A database is a structured set of data. It is structured in tables that are easy to search and update. By organizing data into tables and making it easy to search and update, databases help businesses make informed decisions, improve efficiency, and save time and money.

Database Key Terms

A table holds a collection of records for a particular theme in rows and columns. Each row represents a single record or item, and each column represents a specific attribute or field.

Each record in the table needs a unique identifier. A primary key is used to give each record a unique code. A primary key can never be repeated. This ensures that each record in the table is unique. This is very important when dealing with thousands or even millions of records.

A flat file database is a database that contains a single table.

A relational database contains more than one table. The most common model for a database is a relational model. The data in the tables are linked using relationships. Relationships can be:

• One to one

• One to many

• Many to many

In relational database, Data doesn’t need to be repeated and relationships can be made using the source table’s unique identifier. A primary key is unique to the original source of the data.

When you link to a source table’s primary key you use a foreign key. A foreign key can be repeated because it is a link back to the primary key in the source table.

SQL

SQL stands for Structured Query Language. It is a language used to communicate with a database. You can use SQL to manipulate databases and retrieve records.

SQL is widely used. It is a standard language that comes in many varieties. The most popular are MySQL and SQLite. For this tutorial you will be using SQLite.

SQL uses recognisable commands that are similar to the English language, making it easy for users to write and understand queries.

The commands used in SQL are called keywords, and they are used to perform various operations on a database, such as creating tables, inserting data, updating records, and retrieving information.

Some of the most commonly used SQL keywords include SELECT, FROM, WHERE, INSERT, UPDATE, and DELETE. These keywords are designed to be intuitive and easy to understand, even for those who are not familiar with programming or database management.

For example when you shop for items online you will typically be searching a database that uses SQL. Your search terms will be added to SQL statements and the relevant records will be retrieved from the database.

A SELECT query could be used to retrieve data from the database.

• SELECT the item, price and description columns.

• FROM the tblProducts table.

• WHERE the criteria has been met
  

SELECT item, price, description 
FROM tblProducts 
WHERE keyword = "toy";

SQL Searches

After installing SQLite, download and open dbMusic. Then Complete the following activities.

• Explore a Database
  A3 Worksheet.

• Retrieving Data with SQL
  A1 Worksheet.

• Retrieving data from more than one table
  A2 Worksheet

Data Types

Fields in a database (as with variables in a programming language) have data types associated with them. Data types specify the type of value the field can hold.

MySQL and SQLite are both relational database management systems that provide different data types to define the type of data that can be stored in a field.

For example, MySQL provides data types like INT, VARCHAR, TEXT, DATE, etc., while SQLite provides data types like INTEGER, TEXT, REAL, etc.

In conclusion, data types are essential elements in any database that define the type of data that can be stored in a particular field. Choosing the appropriate data type for each field is crucial to ensure the accuracy and consistency of data stored in the database.

With the wide range of data types available in database management systems like MySQL and SQLite, developers can create more robust and efficient databases capable of handling various types of data.

Insert, update, delete

Insert, update, and delete are the three fundamental operations in database management used to add, modify, and remove data. They are crucial for maintaining database integrity and accuracy.

Inserting data

An SQL INSERT statement allows you to add data into pre-existing tables.

The statement allows you to specify:

• The table which you intend to insert data into.
• Which fields to use.
• Which VALUES to enter into those fields.

INSERT INTO tblFriends (firstname,surname) 
VALUES(“Rachel”, “Green”), (“Phoebe”,”Buffay”),(“Chandler”, “Bing”);

INSERT INTO tblMembers (Firstname,Surname, Email, Password)
VALUES(“Nicole”, “Battle”,”Nbat@mail.com”,”Nr5@Wd”);

Updating Data

An SQL UPDATE statement allows you to amend existing records in tables.

To update data in a table, you are required to specify the following:

• The table which you intend to UPDATE
• The new value that you want to SET
• WHERE a condition is being met

UPDATE tblUsers 
SET Firstname = “James” 
WHERE userid = 8;

Deleting Data

An SQL DELETE statement allows you to remove existing records from a table.

To delete data from a table, you are required to specify the following:

• The table which you intend to DELETE FROM
• WHERE a condition is being met

DELETE FROM tblStudents 
WHERE date_of_birth < 31/08/2002;

Solutions:

Explore a Database Solutions

Retrieving Data with SQL Worksheet Solutions

Retrieving data from more than one table Solutions