SQL - Basics

Basics

SQL basics include creating databases, navigating database shells, defining views, and writing simple conditions.

Creating Database

CREATE DATABASE NAME

Command Line Operations

Command	Description
\l	See all databases
\c name	Connect to specific database
\d	Display all tables in database
psql --username=freecodecamp --dbname=postgres	Connect with credentials

Views

A view is a virtual table produced by a saved SELECT statement. When queried, the view reflects updates in the underlying tables.

CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

SQL Vs NoSQL

SQL databases are relational, structured, and table-based. NoSQL databases such as Mongodb are non-relational and can handle flexible or semi-structured data.

Comparison Operators

Operator	Meaning
>	Greater than or after
<	Less than or before
=	Equal to
>=	Greater than or equal to
<=	Less than or equal to
<>	Not equal to

LIKE Operator

LIKE performs pattern matching with wildcards.

WHERE column LIKE '%A'   -- last character is A
WHERE column LIKE 'A%'   -- first character is A
WHERE column LIKE '__t%' -- third character is t

Count Operations

COUNT(field_name)          -- count non-missing values
COUNT(*)                   -- count all rows
COUNT(DISTINCT field_name) -- count unique values

ROUND Function

SELECT ROUND(AVG(budget), 5) AS avg_budget
FROM films;
 
SELECT ROUND(AVG(budget), -2) AS avg_budget_hundreds
FROM films;

Negative rounding rounds to larger units. ROUND(n, -2) rounds to hundreds.