02 - sql

 sql:

SQL is used to communicate with relational databases

Some common relational database management systems that use SQL include Microsoft SQL Server, MySQL, PostgreSQL, MariaDB, and Oracle.


The tables in the database are related to one another using key columns that uniquely identify the particular entity represented..

✅ Primary Key:

"A primary key uniquely identifies each record in a table and cannot be NULL or duplicate. For example, EmployeeID in the Employees table."

🔗 Foreign Key:

"A foreign key links two tables by referencing the primary key of another table, ensuring data consistency. For example, DeptID in Employees refers to DeptID in Departments."


In SQL Server, schemas create logical namespaces to organize tables within a SQL database, helping avoid name conflicts. Tables can share names across schemas (e.g., Sales.Order and Production.Order) and are often referenced using schema-qualified names like Sales.Order for clarity.


In SQL Server, the fully qualified name of a table includes four levels:
ServerName.DatabaseName.SchemaName.TableName
Example: Server1.StoreDB.Sales.Order

In BigQuery (GCP), the structure is similar but uses a three-level hierarchy:
ProjectID.DatasetID.TableName
Example: myproject.sales_dataset.order


the SQL statements are grouped together into several different types of statements. These different types are:

DML (Data Manipulation Language): Used to query and modify data – includes SELECT, INSERT, UPDATE, DELETE (DML statements are commonly used for CRUD operations: Create, Read, Update, Delete.)

DDL (Data Definition Language): Used to create and manage database structures – includes CREATE, ALTER, DROP.

DCL (Data Control Language): Used to control access and permissions – includes GRANT, REVOKE, DENY.

TCL (Transaction Control Language): Used to manage transactions – includes COMMIT, ROLLBACK, SAVEPOINT.


=============

Working with Data Types in SQL

  • CAST, CONVERT, STR
    Used to convert data types explicitly (e.g., string to number, number to string).
    ⚠️ If conversion fails, these can cause errors and break your query.

  • TRY_CAST, TRY_CONVERT
    Safer versions of CAST and CONVERT.
    Instead of errors, they return NULL when conversion fails, keeping your query running smoothly.

Handling NULLS:

What is NULL?

  • NULL means no value or unknown — it’s different from zero, blank, or an empty string.

  • It represents missing or not yet provided information (e.g., no email supplied yet).

  • Conversion functions may return NULL if data can't convert properly.

Why is NULL tricky?

  • NULL is not equal to anything, not even itself (NULL <> NULL).

  • It’s not greater or less than any value — comparisons with NULL result in unknown.

How to work with NULL in T-SQL:

  • Use functions like ISNULL() or COALESCE() to replace NULLs with default values.

  • Use TRY_CAST and TRY_CONVERT to safely convert data and return NULL instead of errors.

  • NULLIF - The NULLIF function allows you to return NULL under certain conditions.







Previous Post Next Post