SQL Fundamentals

Lecture Notes

## Introduction to SQL Structured Query Language (SQL) is a standardized programming language that is used to manage relational databases and perform various operations on the data in them. Created in the 1970s, SQL is highly popular not only among database administrators but also among developers writing data integration scripts and data analysts looking to set up and run analytical queries. If you are completely new to programming, SQL is a great place to start. For further reading on Python integration, please refer to our [Python for Data Analysis](/courses/data-analyst-bootcamp/1) module. ## Core Concepts ### 1. DDL (Data Definition Language) DDL changes the structure of the table like creating a table, deleting a table, altering a table, etc. All the commands of DDL are auto-committed, which means it permanently saves all the changes in the database. * **CREATE**: It is used to create a new table in the database. * **ALTER**: It is used to alter the structure of the database. This change could be either modifying the characteristics of an existing attribute or adding a new attribute. * **DROP**: It is used to delete both the structure and record stored in the table. ### 2. DML (Data Manipulation Language) DML commands are used to modify the database. It is responsible for all form of changes in the database. * **INSERT**: It is used to insert data into the row of a table. * **UPDATE**: It is used to update or modify the value of a column in the table. * **DELETE**: It is used to remove one or more rows from a table. ### Example: Creating and Inserting Data Let us look at a comprehensive example. Suppose we want to create a database for managing students. ```sql -- Creating the Table CREATE TABLE Students ( StudentID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), EnrollmentDate DATE ); -- Inserting Records INSERT INTO Students (StudentID, FirstName, LastName, EnrollmentDate) VALUES (1, 'John', 'Doe', '2023-08-01'), (2, 'Jane', 'Smith', '2023-08-02'), (3, 'Alice', 'Johnson', '2023-08-03'); ``` ### Aggregation and Grouping One of the most powerful features of SQL is its ability to aggregate data using functions like `SUM()`, `COUNT()`, `AVG()`, `MAX()`, and `MIN()`. ```sql -- Finding the total number of enrolled students per year SELECT EXTRACT(YEAR FROM EnrollmentDate) AS Year, COUNT(StudentID) AS TotalStudents FROM Students GROUP BY EXTRACT(YEAR FROM EnrollmentDate) ORDER BY Year DESC; ``` ## Complexity Analysis of SQL Queries When writing SQL queries, understanding execution plans is critical. A Full Table Scan (O(N)) evaluates every row, whereas an Index Seek (O(log N)) uses B-Tree structures to jump directly to the relevant records. As a data analyst, you must always ensure your `WHERE` clauses target indexed columns when dealing with millions of rows. > **Tip:** Always use `EXPLAIN` or `EXPLAIN ANALYZE` before running complex `JOIN` operations on production databases.