A Brief Overview of SQL Basics

If you prefer watching click here

BASIC TERMS TO UNDERSTAND

• Database: A collection of related Information

  Any collection of related information eg your phonebook,gallery, shopping list.

  Examples

  University : grades

  mpesa : transactions

  hospital : patient records

  pharmacy: drug names

  supermarket:products (skus)

• DBMS : Database management system.

  It is a software that allows us to access data in our database.

  please note its not the actual database

  its objective is to ensure an effective way of storing and retrieving data from the db.

  ADVANTAGES OF DBMS

•  easy management of large datasets

• ensures security

• allows for backup ability

• allows for export/import of data

• allows for access management

It is critical to note that a good DBMS shouls allow for Create, Read, Update and Delete Functionalities. What we shortly call CRUD.

TYPES OF DATABASES

• Relational Databases

• Non-relational databases

🔗 RELATIONAL DATABASES

In Relational Dbs data is stored in one or more tables. Each table has rows and columns. Each table has a unique Identifier called a Primary Key.

NON-RELATIONAL DATABASES

Data is not stored in Tables.

Data can be stored in:

• Key-value pairs

• Documents eg MongoDB

• Graphs

EXAMPLE RELATIONSHIP IN RELATIONAL DATABASE

The Ids are the primary Keys in the tables ,However the relation is on the courseId.

RDBMS

RDBMS is Relational Database Management System. It is for creating and maintaing relational databases.

Examples:

• Mysql

• Postregsql

• Mssql

• Oracle

So What is SQL????

🌐 SQL

SQL is structured Query Language. It is a standardized language for interacting with Relational Databases.

How About the Query in Sql???

• Queries are requests made to the database for specific information.

  eg looking for courses in the university portal or kuccps

SQL ELEMENTS

DDL - Data Definition Language

Involves commands that define the structure of the database itself. They are used to create, modify, or delete database objects such as tables, indexes, and views. Eg CREATE, ALTER, and DROP.

DML - Data Manipulation Language

DML commands that are used for managing data within database objects like tables. That is insertion, modification, and deletion of data.

Eg INSERT (to add new rows to a table), UPDATE (to modify existing data), and DELETE (to remove data from a database).

DCL - Data Control Language

DCL is used to control access to data in the database. It's primarily concerned with rights, permissions, and other controls of the database system.

Eg GRANT (to give access privileges) and REVOKE (to remove access privileges).

DQL- Data Query Language

DQL is used specifically for querying and retrieving data from a database. The primary, and most commonly used, Command is:

• SELECT: This command is used to select data from a database. It retrieves data from one or more tables and can be used in conjunction with various clauses to refine the data retrieval, such as WHERE (to filter records), GROUP BY (to group records), ORDER BY (to sort records), and JOIN (to combine rows from two or more tables).

• It is a fundamental aspect of SQL and is extensively used for data analysis, reporting, and data processing in databases.

DIFFERENCE BETWEEN DDL,DQL,DCL & DML

Unlike DDL, DML, and DCL, which alter the structure, manipulate, or control access to the database, DQL is solely focused on fetching and viewing the data.

DATATYPES

Data types in SQL define the kind of value that can be stored in a table column.

• Integer: Stores whole numbers without decimals. Examples include INT, SMALLINT, BIGINT.

• Decimal: Used for numbers with decimals. Examples include DECIMAL, NUMERIC.

• Float/Double: For floating-point numbers. FLOAT and DOUBLE represent numbers with fractional parts.

• Char: Stores fixed-length character strings.

• Varchar: For variable-length character strings.

• Boolean: Holds true or false values.

• Date/Time: For dates and times. Includes DATE, TIME, DATETIME, TIMESTAMP.

• Blob: For binary large objects like images, audio, etc.

• Text: For long text strings. Includes TEXT, TINYTEXT, MEDIUMTEXT, LONGTEXT.

Download the pdf for future reference.

This newsletter provides a condensed overview of the key concepts in SQL, perfect for beginners or as a quick refresher for the seasoned professionals. Keep an eye out for more deep dives into each topic in our upcoming issues! 🚀