Data corner: SQL (data definition language, data database, aggregate data, modify data, and JOIN clause)

Skill 1: How to use Data Definition Language (DDL) to manage the structure of a database?

Overview:

• Data Definition Language in SQL refers to a set of commands used to define and manage the structure of a database. It includes statements such as CREATE, DROP, ALTER, TRUNCATE, COMMENT, and RENAME, allowing users to create or modify database objects like tables, indexes, and views.

Resources: 

1. What is DDL — Data Definition Language 
2. Create Table
3. Drop Command and Truncate Command
4. Alter Table Statement
5. Writing Comments in any SQL Snippet 
6. How to Rename your Table

If you want a way more in-depth look into what you can do:

• Create or Replace a Table, Variant Syntax of Create Table 
• 10 Examples of ALTER Table Command in SQL

Skill 2: How to use SELECT statement to retrieve data from a database?

Overview:

• The SQL SELECT statement is used to retrieve data from one or more tables in your SQL database. It allows users to specify the columns they want to retrieve and the table from which to fetch the data. By using SELECT, FROM, WHERE, ORDER BY, LIMIT, SELECT DISTINCT, LIKE, and BETWEEN statements, users can customize and refine their queries to meet specific criteria.

Resources: 

1. Tutorial on SQL Select (Beginners)  and Here’s Part II

If you want a way more in-depth look into what you can do:

• Tutorial Subqueries (Advanced SQL)

Skill 3: How to aggregate data in SQL?

Overview:

• Data aggregation in SQL involves the process of summarizing or combining data from multiple rows into a single value. This is typically achieved using aggregation functions such as COUNT, SUM, AVG, MIN, and MAX, which operate on a set of rows to produce a consolidated result. Aggregation is commonly used with the GROUP BY clause to organize data based on specific columns, facilitating the analysis of summarized information at different levels.

Resources: 

1. Count Function — returns the number of rows that matches a specified criterion
2. Sum Function — returns the total sum of a numeric column
3. AVG Function — returns the average value of a numeric column
4. MIN Function (returns the smallest value of the selected column) and MAX Function ( returns the largest value of the selected column)

Full Video on Basic Aggregate Functions (Functions Above)

Video on Advanced Aggregate Functions (Groups by, Having vs. Where)

If you want a way more in-depth look into what you can do:

• 15 Basic SQL Aggregate Functions and How To Use Them
• How to Calculate Multiple Aggregate Functions in a Single Query

Skill 4: How to modify data in SQL?

Overview:

• Data modification skills in SQL refer to the ability to alter the contents of a database by inserting, updating, or deleting records. This involves using SQL statements such as INSERT INTO, UPDATE, and DELETE. These skills are essential for maintaining and managing the data within a database, ensuring that it stays accurate, relevant, and up-to-date.

Resources: 

1. Insert Into Statement — statement is used to insert new records in a table
   • Video Explanation 
2. Update Statement — statement is used to modify the existing records in a table
   • Video Explanation 
3. Delete Statement — statement used to delete existing records in a table.
   • Video Explanation 

If you want a way more in-depth look into what you can do:

• Another data manipulation language: Lock Table
• SQL Server DML – MERGE Statement
• SQL Best Practices for Deleting and Updating data

Skill 5: How to practise JOIN clause in SQL?

Overview:

• In SQL, a JOIN clause is used to combine rows from two or more tables based on a related column, creating a result set that includes columns from both tables. It allows for the retrieval of comprehensive information by establishing relationships between tables through common fields. Common types of joins include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN, each determining how matching and non-matching rows from the tables are included in the result.

Resources: 

1. Join Clause — used to combine rows from two or more tables, based on a related column between them.
2. Inner Join Keyword —  selects records that have matching values in both tables 
3. Left Join Keyword — returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match. 
4. Right Join Keyword —  returns all records from the right table (table2), and the matching records from the left table (table1). The result is 0 records from the left side, if there is no match.
5. Full Join Keyword — returns all records when there is a match in left (table1) or right (table2) table records.

Video on SQL Joins Explained 

Video on SQL Joins Examples

If you want a way more in-depth look into what you can do:

• Self Join
• 6 SQL Joins you MUST know