SQL for Everyone [Part 1]

Data is the new oil, and very much like oil - data needs to be mined, extracted and cleaned to be of real use. Everyone needs to work with data. And SQL helps you with exactly that - it helps you talk to data. Whatever you need, SQL gets it for you. So here’s some things I’ve learnt, using SQL as an analyst.

This is part 1, and ideal for someone who’s never studied/used SQL. If you’re someone who knows basics & are looking for usecases and examples, skip to part 2!

Basics of SQL (skip if you know already)

Structured Query Language (pronounced: ‘si-quel’ or S-Q-L) is a programming language designed for managing data in a relational database.

[What’s a query?] A code patch used to request data from database is called a query in SQL!

1. Data Types: Each column in a database table has a data type, which specifies the kind of data that it can store (e.g., string, numbers, dates).
2. Operators: used to filter, compare, and calculate data. Some common operators include = (equal to), < (less than), > (greater than), AND, OR, and IN.
3. Functions: SQL has many built-in functions that can be used to perform operations on data. Eg: SUM (to calculate the sum of a set of values), AVG (to calculate the average of a set of values), and COUNT (to count the number of rows in a table).
4. Clauses: Clauses are used to in a query. Some common clauses include laWHERE (to filter rows based on a condition), GROUP BY (to group rows based on a column or set of columns), and ORDER BY (to sort rows in a particular order).

5. Statements: SQL has several types of statements that can be used to perform different types of operations on a database.

   • DDL – Data Definition Language: Consists of statements used to alter schema: CREATE, ALTER, DROP, TRUNCATE Table Operations
   • DQL – Data Query Language: Query Operations to fetch data - SELECT Statements.
   • DML – Data Manipulation Language. Consists of commands that deals with the manipulation of data present in the database - INSERT, UPDATE, DELETE Table operations
   • DCL – Data Control Language. Deals with controls, rights & permissions of databases. Includes GRANT & REVOKE commands.
   • TCL – Transaction Control Language. It performs COMMIT and ROLLBACK operations.

   For most of the usecases, we use DDL, DQL & DML statements more than others. SQL syntax varies from database to database but the binding logic remains the same. Meaning, SQL has various dialect, like any other widely used language!

So, here’s what you can do with SQL!

• You can retrieve data from a database
• You can create a database/table.
• You can insert/update new records in a table.
• You can delete a table/database.

Let’s write a query to fetch total marks of all those students who scored more than 40 Marks in an exam and order them from top to bottom according to marks

SELECT student_table.name, 
SUM(student_table.marks) as sum_marks
FROM student_table
WHERE student_table.marks >= 40
GROUP BY 2
ORDER BY 2 DESC

That’s literally how easy SQL is!

For most commonly used functions and commands, let’s go to part 2where I detail every single command/function tat has come handy to me, in my experience - along with usecases & examples.