SQL

In several pitches I was asked if I am familiar with SQL.

First, I was wondering as I consider such things as basics as datacrunshing tasks are (unfortunatelly) the foundation on any data science project.


However, it turned out, that my clients are right to ask such questions as there are many people out there which are claimining to have profound hands-on IT skills after having watched a 15 minutes video on Youtube.

Thus, to save both parties time, I decided to elaborate my answer more in detail.

First, to provide the structure it’s best to seperate usecase of database design and usage.


SQL Data Definition Language (DDL)

  • Use the CREATE TABLE command to design data tables.
  • Use the ALTER TABLE command to modify a table.
  • Use the DROP TABLE command to delete a table.
  • Use appropriate data types for numeric and text data.
  • Use primary keys to manage table data.
  • Use foreign keys to establish table joins.
  • Use Unique constraint to manage table data.
  • Use NOT NULL constraint to manage table data.
  • Use VIEW to create a permanent virtual table.

SQL Data Manipulation Language (DML)

  • Use INSERT sql command to add data to tables.
  • Use UPDATE sql command to modify current table data.
  • Use DELETE sql command to remove current table data.
  • Demonstrate constraint behavior against INSERT, UPDATE, and DELETE commands.
  • Use SELECT sql command to query one and multiple tables.
  • Use SELECT sql command with WHERE clauses to filter and refine queries.
  • Use SELECT sql command with WHERE clauses and boolean operators (AND, OR, NOT, LIKE, IN, BETWEEN) to filter and refine queries.
  • Use JOIN sql command and understand variants (INNER, OUTER) to create table relationships.
  • Use aggregate functions (SUM, MIN, MAX, AVG) to restrict returned data set.
  • Use HAVING clause to restrict returned data set.
  • Use GROUP BY clause to organize returned data set.
  • Understand string and formatting functions, and how to use a special purpose table (such as dual) to test their usage.
  • USE string, date, and time functions to build columns on the fly.

And yes, I no the difference between SQL and noSQL Databases -) and Snowflake vs. Star Designs and practical applications for database normalization up to BCNF.


But that’s a different story and should be posted at a different time.