Getting into SQL For Beginners:

Starting new endeavors can feel daunting, but every journey begins with a single step. Learning SQL opens doors to new opportunities. This guide simplifies the fundamentals of SQL, teaching you its essential elements.

1.DDL(Data Definition Language)

DDL defines and modifies the database structure and schema, determining table appearance and data content. There are various commands that are used to achieve this:

CREATE SCHEMA- is a standard SQL statement used to create new namespaces within a database, grouping related objects like tables.
CREATE TABLE- creates a new table and defines its columns.
ALTER- command modifies existing tables by adding, renaming, or changing the data type of columns.
DROP-permanently removes unnecessary columns.
TRUNCATE- It removes all the rows from the table while leaving the structure intact.

This example demonstrates creating a schema for Greenwood Academy ,the table students and altering:

CREATE SCHEMA greenwood_academy;
CREATE TABLE greenwood_academy.students(
student_id INT PRIMARY KEY,
first_name  VARCHAR(50) NOT null,
last_name   VARCHAR(50) NOT null,
gender  VARCHAR(1));

• Notice the punctuation marks on the schema name where the _ is used as a space.

• The _VARCHAR(50)_is used to indicate that it can store a text with around 50 strings

• When creating a table, it's advisable to include the schema name separated by a full stop (e.g., schema_name.table_name). This ensures the table is created within the specified schema, such as greenwood_academy.

To add a new column, use ALTER TABLE.

ALTER TABLE greenwood_academy.students
add column phone_number VARCHAR(20);

2.DML(Data Manipulation Language)

DML helps to manipulate the data records within the structure where commands like:
INSERT- adds new rows of data into a database table.
UPDATE- modifies or changes existing records within a table.
DELETE- removes existing rows
SELECT*- retrieves and read data from a database.
Example:

update greenwood_academy.students
set city= 'Nairobi'
where student_id =5;

This code means that where the student_id is 5 the students city should be changed to Nairobi.

3.SQL Joins

SQL Joins are used to combine data from two/more tables using a related column:
Inner Joins- Returns only records that match in both tables.(Its like a command that only requests for matching records only and leaves out the others) eg.;

select c.name,o.product_id, quantity --This shows the columns one want to see in the final results.
from  duka1.duka_customers c --the `c` is an alias we give to the customers table.
inner join duka1.duka_orders o -- the `o` is an alias given to the orders table.
on c.customer_id = o.customer_id;

Left Joins-A left join returns all rows from the left table, and the matching rows from the right table. If no match is found in the right table, NULL values are returned for right table columns. A right join performs the inverse. eg;

select dc.name
from duka1.duka_customers dc 
left join duka1.duka_orders t on dc.customer_id = t.customer_id
where t.order_id is null;

the null shows the results of a customer with no orders.
Full Outer Join - Brings together everything from both tables even if they are not matching.

4.SUBQUERIES & CTES

A subquery is a query embedded within another SQL statement, whereas a CTE (Common Table Expression) is a named, temporary result set defined with the WITH clause at the start of a query.
They are useful in different scenarios where the subquery is used when writing simple calculations while CTES are used with complex multi-step queries/calculation.
If done correctly they all give the same results.
An example showing the difference between a CTE and a subquery:

--SUBQUERY WAY--
select score_id,member_id,score 
from study_group.quiz_scores qs
where score > ( select avg(score) from study_group.quiz_scores) order by score desc;

--THE CTE way- This creates a temporary result at first called the group_avg which then used by the main query.
with group_avg as 
( select avg(score) as avg_score
from study_group.quiz_scores)
select score_id, member_id,score
from study_group.quiz_scores
where score > ( select avg_score from group_avg)
order by score desc;

• The subguery puts the calculation directly inside the WHERE while the CTE puts it separately using WITH.

• In simple terms the CTE does the avg first then uses it while the subquery calculates the avg as it uses it.

5.SQL FUNCTIONS

SQL functions help to manipulate data, perform calculations and format outputs directly within database queries.
They are categorized into 2 where:

• scalar Functions - they operate on a single row and return one value per row.eg;** UPPER(),LOWER(),ROUND(),LENGTH()**

select first_name, UPPER (first_name) as name_upper,
       last_name, LOWER(last_name) as name_lower
  from greenwood_academy.students;

as renames the column.

-Aggregate functions - they operate on a collection of rows to return a single summarized value.eg. Count(),AVG(),SUM(),MIN/MAX()...
Example:

select 
     COUNT(*) as total_results,
     AVG(marks) as average_mark,
     MIN(marks) as lowest_mark,
     MAX(marks) as highest_mark,
     SUM(marks) as total_marks
     from greenwood_academy.exam_results;

Conclusion

Mastering DDL, DML, joins, subqueries, CTEs, and SQL functions is crucial for SQL beginners. Consistent practice with these concepts will enable confident data analysis.