SQL Basics and Data Types

SQL Basics

• SQL stands for Structured Query Language
• Used to manage and manipulate data in relational databases
• Consists of a series of commands or statements to perform specific tasks

Data Types

• Integer (int): whole numbers, e.g. 1, 2, 3
• String (varchar, char): characters, e.g. 'hello', "hello"
• Date (date, datetime): dates and timestamps, e.g. '2022-01-01', '2022-01-01 12:00:00'
• Boolean (bool): true or false values

SQL Statements

DDL (Data Definition Language)

• CREATE: create a new database, table, or index
• ALTER: modify an existing database, table, or index
• DROP: delete a database, table, or index

DML (Data Manipulation Language)

• INSERT: add new data to a table
• UPDATE: modify existing data in a table
• DELETE: delete data from a table

DQL (Data Query Language)

• SELECT: retrieve data from a table
• FROM: specify the table(s) to retrieve data from
• WHERE: filter data based on conditions
• GROUP BY: group data based on one or more columns
• HAVING: filter grouped data based on conditions
• ORDER BY: sort data in ascending or descending order
• LIMIT/OFFSET: limit the number of rows returned

SQL Functions

• Aggregate functions:
  • SUM: calculate the total value of a column
  • AVG: calculate the average value of a column
  • MAX: return the maximum value of a column
  • MIN: return the minimum value of a column
  • COUNT: return the number of rows
• String functions:
  • CONCAT: concatenate strings
  • LENGTH: return the length of a string
  • LOWER/UPPER: convert strings to lowercase/uppercase
• Date functions:
  • NOW(): return the current date and time
  • DATE_FORMAT: format a date string

SQL Joins

• INNER JOIN: combine two tables based on a common column
• LEFT JOIN: combine two tables, returning all rows from the left table
• RIGHT JOIN: combine two tables, returning all rows from the right table
• FULL OUTER JOIN: combine two tables, returning all rows from both tables

SQL Subqueries

• A query nested inside another query
• Used to retrieve data that meets certain conditions
• Can be used in SELECT, FROM, WHERE, and HAVING clauses

SQL Basics

• SQL stands for Structured Query Language and is used to manage and manipulate data in relational databases
• Consists of a series of commands or statements to perform specific tasks

Data Types

• Integer (int): whole numbers, e.g. 1, 2, 3
• String (varchar, char): characters, e.g. 'hello', "hello"
• Date (date, datetime): dates and timestamps, e.g. '2022-01-01', '2022-01-01 12:00:00'
• Boolean (bool): true or false values

SQL Statements

DDL (Data Definition Language)

• CREATE: create a new database, table, or index
• ALTER: modify an existing database, table, or index
• DROP: delete a database, table, or index

DML (Data Manipulation Language)

• INSERT: add new data to a table
• UPDATE: modify existing data in a table
• DELETE: delete data from a table

DQL (Data Query Language)

• SELECT: retrieve data from a table
• FROM: specify the table(s) to retrieve data from
• WHERE: filter data based on conditions
• GROUP BY: group data based on one or more columns
• HAVING: filter grouped data based on conditions
• ORDER BY: sort data in ascending or descending order
• LIMIT/OFFSET: limit the number of rows returned

SQL Functions

Aggregate Functions

• SUM: calculate the total value of a column
• AVG: calculate the average value of a column
• MAX: return the maximum value of a column
• MIN: return the minimum value of a column
• COUNT: return the number of rows

String Functions

• CONCAT: concatenate strings
• LENGTH: return the length of a string
• LOWER/UPPER: convert strings to lowercase/uppercase

Date Functions

• NOW(): return the current date and time
• DATE_FORMAT: format a date string

SQL Joins

• INNER JOIN: combine two tables based on a common column
• LEFT JOIN: combine two tables, returning all rows from the left table
• RIGHT JOIN: combine two tables, returning all rows from the right table
• FULL OUTER JOIN: combine two tables, returning all rows from both tables

SQL Subqueries

• A query nested inside another query
• Used to retrieve data that meets certain conditions
• Can be used in SELECT, FROM, WHERE, and HAVING clauses

Test your understanding of SQL fundamentals, including data types such as integers, strings, dates, and booleans, and SQL statements like DDL.