DEV Community: Tony Ochieng

SQL 101:INTRODUCTION TO SQL FOR DATA ANALYSIS

Tony Ochieng — Wed, 01 Mar 2023 03:23:06 +0000

What is SQL?
SQL (Structured Query Language) is a powerful programming language that is widely used for managing and manipulating data in a relational database. It provides a set of commands that allow you to query, filter, and transform data stored in tables, which makes it an essential tool for data analysts.
SQL is a standard language that is supported by many database management systems (DBMS) such as MySQL, PostgreSQL, Oracle, SQL Server, and many more. It offers a range of features that enable data analysts to extract, analyze, and visualize data, including selecting specific columns, filtering rows based on criteria, aggregating data using functions, and sorting and joining tables.
Why do data analysts need to learn SQL?
SQL is used extensively in data analysis because it allows analysts to retrieve and manipulate large datasets quickly and efficiently. It is also used for database administration tasks such as creating tables, modifying schema, and adding or deleting data. With SQL, data analysts can easily extract valuable insights from data, which is critical for making informed business decisions.
Here are some of the basic I got to understand in regards to SQL:-
Types of SQL commands

Data Definition Language(DDL)
Used to manipulate database structure

CREATE create a database or table.
ALTER Change the structure of the table such as changing table names, adding and deleting columns.
DROP delete database or table.

Data Manipulation Language(DML)
used to manipulate data in a database

INSERT insert new data into a table.
SELECT selects and displays columns.
DELETE delete data from a table.
UPDATE change or edit data in a table.

Data Control language(DCL)
used to control and manipulate the database permissions

GRANT used to grant admin permissions to user.
REVOKE used to revoke the access rights of a user.

Transaction Control Language
Deals with transactions in the database

COMMIT used to permanently store transactions in the database.
ROLLBACK used to return database to the last commit.
SAVEPOINT allows commands executed after they are set to be rolled back

Relational database management system(RDBMS)
Is a program that allows us to create, update and manage a relational database.

Types of database relationship

one-to-one each record in one table corresponds to exactly one record in another table and vice versa.
one-to-many each record in one table can correspond to one or more record in another table, but each record in the second table corresponds to only one record in the first table.
many-to-many each record in one table can correspond to one or many records in another table.

In SQL, a key is a field or combination of fields that uniquely identifies a row in a table. Keys are used to establish relationships between tables and to ensure data integrity by preventing duplicate rows.

There are several types of keys in SQL, including:

Primary key: A primary key is a field or combination of fields that uniquely identifies each row in a table. It cannot contain null values and each table can have only one primary key.

Foreign key: A foreign key is a field in one table that refers to the primary key of another table. It is used to establish relationships between tables.

Candidate key: A candidate key is a field or combination of fields that can be used as a primary key. It is unique and can be used to identify each row in a table.

Composite key: A composite key is a key that consists of multiple fields. It can be used to uniquely identify a row in a table when no single field can provide a unique identifier.

Unique key: A unique key is a key that ensures that the values in a field or combination of fields are unique. It can be used to prevent duplicate rows in a table.

Comments
Comments are used to enhance the readability of code. When written between code lines the interpreter identifies them and does not execute them. Comments can span across one or multiple lines.
Single line comment: You can use — — (two dashes) to comment out everything to the right of them on a given line

SELECT *  --This comment won't affect the way the code runs
FROM tablename

Multi line Comment : Comments across multiple lines use /* to begin the comment and */ to close.

SELECT *  /* Here's a comment so long and descriptive that
it could only fit on multiple lines. Fortunately,
it, too, will not affect how this code runs. */
FROM tablename

Creating a Database
One can use the CREATE DATABASE statement example;

CREATE DATABASE Customers;

CREATING A TABLE

CREATE TABLE customers(
id INTEGER,
first_name TEXT,
last_name TEXT
);

Fetching all columns from a table

SELECT *
FROM customers;

Inserting data into a table
Using the INSERT INTO statement. Basic syntax:

INSERT INTO customers (id, first_name, last_name, email)
VALUES (1, 'Tony', 'Ochieng', 'Tonyochieng@example.com');

This statement will insert a new row into the "customers" table with an "id" of 1, "first_name" of 'Tony', "last_name" of 'Ochieng', and "email" of 'Tonyochieng@example.com'.

Filtering Data
Filtering output in SQL is done using the SELECT statement and the WHERE clause. The WHERE clause allows you to specify conditions that the selected data must meet. Here's the basic syntax of the SELECT statement with the WHERE clause:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

The condition in the WHERE clause is a logical expression that evaluates to true or false. Only the rows that satisfy the condition are returned in the output.

For example, let's say you have a table called "employees" with columns for "id", "name", "department", and "salary". If you want to select all employees in the "sales" department with a salary greater than $50,000, you can use the following statement:

SELECT id, name, salary
FROM employees
WHERE department = 'sales' AND salary > 50000;

This statement will select the "id", "name", and "salary" columns from the "employees" table where the "department" is 'sales' and the "salary" is greater than 50000. Only the rows that meet these conditions will be returned in the output.

You can also use other operators in the WHERE clause to create more complex conditions, such as OR, NOT, and comparison operators like =, <>, <, >, <=, and >=.

Querying Multiple tables
Querying multiple tables in SQL is done using the JOIN operation, which allows you to combine data from two or more tables based on a common column. There are several types of joins in SQL, including INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

Here's an example to illustrate how to join two tables named "customers" and "orders". The "customers" table has columns for "id", "first_name", "last_name", and "email", and the "orders" table has columns for "id", "customer_id", "product", and "price".

To select all orders made by a customer with a given email address, you can use an INNER JOIN like this:

SELECT customers.first_name, customers.last_name, orders.product, orders.price
FROM customers
INNER JOIN orders
ON customers.id = orders.customer_id
WHERE customers.email = 'Tonyochieng@example.com';

This statement will select the "first_name" and "last_name" columns from the "customers" table and the "product" and "price" columns from the "orders" table where the customer's email address is 'Tonyochieng@example.com'. The INNER JOIN is used to join the two tables based on the "id" column in the "customers" table and the "customer_id" column in the "orders" table.

If you want to include all customers in the output, even if they haven't made any orders, you can use a LEFT JOIN like this:

SELECT customers.first_name, customers.last_name, orders.product, orders.price
FROM customers
LEFT JOIN orders
ON customers.id = orders.customer_id
WHERE customers.email = 'Tonyochieng@example.com';

This statement will return all customers in the "customers" table, along with any orders they have made, if any. If a customer has not made any orders, the "product" and "price" columns in the output will be NULL. The LEFT JOIN is used to include all rows from the "customers" table, even if there is no matching row in the "orders" table.

RIGHT JOIN is a type of join operation that returns all the rows from the right table and matching rows from the left table. If there are no matching rows in the left table, the result will contain NULL values.

The syntax for a RIGHT JOIN:

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

In this syntax, table1 is the left table and table2 is the right table. The ON clause specifies the condition for the join.
FULL OUTER JOIN (or simply a FULL JOIN) is a type of join operation that returns all the rows from both the left and right tables, including any non-matching rows. If there are no matching rows in one of the tables, the result will contain NULL values.

The syntax for a FULL OUTER JOIN:

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

Here's an example to illustrate how a FULL OUTER JOIN works. Let's say you have two tables, "orders" and "customers", with the following columns:

"orders" table: "order_id", "customer_id", "product", "price"
"customers" table: "customer_id", "first_name", "last_name", "email"
If you want to retrieve all the customers and all the orders, including any non-matching rows, you can use a FULL OUTER JOIN like this:

SELECT customers.first_name, customers.last_name, orders.product, orders.price
FROM orders
FULL OUTER JOIN customers
ON orders.customer_id = customers.customer_id;

In conclusion, SQL is a powerful and widely used language that data analysts must have in their toolkit. It allows analysts to work with large datasets, manipulate data, and extract valuable insights that can drive business decisions. With this introduction to SQL, you are now ready to start learning more about its features and capabilities.

Python 101: Introduction to Python for Data Science

Tony Ochieng — Wed, 01 Mar 2023 03:22:49 +0000

Python is a popular programming language for data science due to its simplicity, versatility, and wide range of libraries and tools available for data analysis, visualization, and machine learning.
Here are some of the libraries and tools commonly used in Python for data science:

NumPy: a library for numerical computing with support for arrays, matrices, and mathematical operations.

Pandas: a library for data manipulation and analysis with support for reading and writing various data formats, such as CSV, Excel, and SQL databases.

Matplotlib: a library for data visualization with support for creating a wide range of plots and charts.

Seaborn: a library for data visualization that provides a high-level interface for creating statistical graphics.
**
Scikit-learn**: a library for machine learning with support for classification, regression, clustering, and other algorithms.

TensorFlow: a library for building and training machine learning models with support for deep learning, natural language processing, and other advanced techniques.

To get started with Python for data science, the first step is to learn python programming basics;

Syntax: Python code is written using a simple, easy-to-read syntax that is similar to natural language. For example, to print the phrase "Hello, world!" to the console, you can use the following code:

print("Hello, world!")

Variables
You can use variables to store and manipulate data in your program. Here are some basics of working with variables in Python:

Variable Names: In Python, variable names can consist of letters, numbers, and underscores, but must start with a letter or underscore. Variable names are case-sensitive, so "myVar" and "myvar" are different variables.

Assigning Values to Variables: You can assign a value to a variable using the assignment operator "=" like so:

my_var = 42

Data Types: Python is dynamically typed, which means that the data type of a variable is inferred from the value it holds. Some common data types include:
Integers: Whole numbers like 42, -10, or 0.

Floating-point numbers: Decimal numbers like 3.14, -2.5, or 0.0.

Strings: Sequences of characters enclosed in single or double quotes, like "hello", "world", or "42".

Booleans: True or False values, which are used to represent logical conditions.

Working with Variables: Once you have assigned a value to a variable, you can use it in your code. For example, you can print the value of a variable using the print() function like so:

print(my_var)

Control Structures
Control structures in Python are used to control the flow of execution in a program. They allow you to make decisions and repeat actions based on conditions in your code. Here are some of the most commonly used control structures in Python:
If-else statements: An if-else statement allows you to execute different code depending on a condition. For example:

age = 20

if age >= 18:
    print("You are an adult.")
else:
    print("You are not yet an adult.")

In this example, if the value of the variable "age" is greater than or equal to 18, the program will print "You are an adult." Otherwise, it will print "You are not yet an adult."
While loops: A while loop allows you to repeat a block of code while a condition is true. For example:

count = 0

while count < 5:
    print(count)
    count += 1

In this example, the program will print the values 0 through 4, because the "count" variable is initially set to 0 and incremented by 1 each time the loop runs, until it reaches 5.
For loops: A for loop allows you to iterate over a sequence of values, such as a list or a string. For example:

fruits = ["apple", "banana", "cherry"]

for fruit in fruits:
    print(fruit)

In this example, the program will print each item in the "fruits" list.
Break and continue statements: You can use the break and continue statements to control the behavior of loops. The break statement immediately exits the loop, while the continue statement skips to the next iteration. For example:

count = 0

while True:
    if count == 5:
        break
    if count == 3:
        count += 1
        continue
    print(count)
    count += 1

In this example, the program will print the values 0 through 4, but skip over the value 3.
Functions
Functions are a key feature of Python and allow you to group and reuse code. A function is a block of code that performs a specific task, which can be called from other parts of your code. Here are some basics of working with functions in Python:

Defining a Function: You can define a function using the def keyword, followed by the function name and a set of parentheses that may contain parameters, and finally a colon to start the function block. Here is an example of a simple function that adds two numbers:

def add_numbers(a, b):
    return a + b

In this example, the function is called "add_numbers" and takes two parameters "a" and "b", and returns the sum of the two numbers.
Calling a Function: Once you have defined a function, you can call it from other parts of your code. To call a function, simply use the function name followed by parentheses, and pass any required parameters inside the parentheses. Here is an example of calling the "add_numbers" function:

result = add_numbers(2, 3)
print(result)

In this example, the function is called with the values 2 and 3, which are passed as the "a" and "b" parameters. The result of the function is then assigned to a variable called "result" and printed to the console.
Default Parameters: You can define default parameter values for a function, which are used if a value is not provided when the function is called. Here is an example:

def say_hello(name="World"):
    print("Hello, " + name + "!")

In this example, the "say_hello" function takes a single parameter "name", which has a default value of "World". If no parameter is provided when the function is called, the default value will be used. Here is an example of calling the function:

say_hello()          # Prints "Hello, World!"
say_hello("Alice")   # Prints "Hello, Alice!"

Returning Values: A function can return a value using the return statement. Here is an example:

def get_square(x):
    return x * x

In this example, the "get_square" function takes a single parameter "x" and returns its square. Here is an example of calling the function:

result = get_square(5)
print(result)   # Prints 25

These are just some of the basics of Python. As you continue to learn and explore the language, you will encounter many more features and capabilities that make Python a powerful and versatile tool for programming.