Introduction
Hey there, fellow tech enthusiasts! Remember our deep dive into mastering relational database design? Well, grab your favorite mug of coffee because today, we're exploring the magic behind bringing database schema designs to life: SQL.
What is SQL?
Picture this: I was fresh into my first startup gig, excited and slightly terrified. One of my tasks was to work with SQL, or Structured Query Language. SQL is like the universal language for talking to relational database management systems (RDBMS). Even though we mostly used Object Relational Mappers (ORMs) to make our lives easier, knowing SQL was crucial for those quick, direct interactions with the database.
Basic SQL Queries
In the early days, I remember needing to pull data from our user database. Here are some basic queries that became my bread and butter:
-- Selecting all columns from a table
SELECT * FROM employees;
-- Selecting specific columns
SELECT first_name, last_name FROM employees;
-- Using DISTINCT to get unique values
SELECT DISTINCT department_id FROM employees;
-- Using WHERE to filter results
SELECT * FROM employees WHERE department_id = 10;
-- Using LIMIT to limit the number of results
SELECT * FROM employees LIMIT 5;
-- Using OFFSET to skip certain rows
SELECT * FROM employees OFFSET 5;
Filtering Data
Filtering data was another essential skill. I often needed to extract specific records based on conditions:
-- Using comparison operators
SELECT * FROM employees WHERE salary > 50000;
-- Using logical operators
SELECT * FROM employees WHERE department_id = 10 AND salary > 50000;
-- Using IN and NOT IN
SELECT * FROM employees WHERE department_id IN (10, 20);
-- Using BETWEEN
SELECT * FROM employees WHERE salary BETWEEN 40000 AND 60000;
-- Using LIKE for pattern matching
SELECT * FROM employees WHERE last_name LIKE 'S%';
Sorting Data
Sorting the data helped in organizing our outputs for better readability and analysis:
-- Sorting data in ascending order
SELECT * FROM employees ORDER BY salary;
-- Sorting data in descending order
SELECT * FROM employees ORDER BY salary DESC;
-- Sorting by multiple columns
SELECT * FROM employees ORDER BY department_id, salary DESC;
Aggregate Functions
Aggregate functions were my go-to for quick calculations on data sets:
-- Counting the number of rows
SELECT COUNT(*) FROM employees;
-- Calculating total salary
SELECT SUM(salary) FROM employees;
-- Finding average salary
SELECT AVG(salary) FROM employees;
-- Finding minimum salary
SELECT MIN(salary) FROM employees;
-- Finding maximum salary
SELECT MAX(salary) FROM employees;
Grouping Data
Grouping data allowed us to summarize information effectively, a handy trick for reports:
-- Grouping data by department
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;
-- Using HAVING to filter grouped data
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) > 5;
Joins
Joins were particularly useful when we needed to combine data from different tables:
-- Inner Join
SELECT * FROM employees INNER JOIN departments ON employees.department_id = departments.department_id;
-- Left Join
SELECT * FROM employees LEFT JOIN departments ON employees.department_id = departments.department_id;
-- Right Join
SELECT * FROM employees RIGHT JOIN departments ON employees.department_id = departments.department_id;
-- Full Outer Join
SELECT * FROM employees FULL OUTER JOIN departments ON employees.department_id = departments.department_id;
Subqueries
Subqueries helped us with more complex queries, often serving as the backbone for advanced data retrieval:
-- Subquery example
SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);
-- Correlated subquery example
SELECT * FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);
Views
Views simplified our interactions with complex queries:
-- Creating a view
CREATE VIEW high_paid_employees AS SELECT * FROM employees WHERE salary > 80000;
-- Updating a view
CREATE OR REPLACE VIEW high_paid_employees AS SELECT * FROM employees WHERE salary > 90000;
-- Dropping a view
DROP VIEW IF EXISTS high_paid_employees;
Indexing
Indexing was crucial for performance, especially as our data grew:
-- Creating an index
CREATE INDEX idx_lastname ON employees(last_name);
-- Dropping an index
DROP INDEX idx_lastname;
Transactions
Transactions ensured our database operations were reliable and consistent:
-- Beginning a transaction
BEGIN TRANSACTION;
-- Committing a transaction
COMMIT;
-- Rolling back a transaction
ROLLBACK;
Stored Procedures
Stored procedures encapsulated repetitive tasks, making our lives easier:
-- Creating a stored procedure
CREATE PROCEDURE get_employee (IN employee_id INT)
BEGIN
SELECT * FROM employees WHERE employee_id = employee_id;
END;
-- Executing a stored procedure
CALL get_employee(100);
-- Modifying a stored procedure
ALTER PROCEDURE get_employee (IN employee_id INT)
BEGIN
SELECT employee_id, first_name, last_name FROM employees WHERE employee_id = employee_id;
END;
-- Dropping a stored procedure
DROP PROCEDURE IF EXISTS get_employee;
Backup and Recovery
Backup and recovery were our safety nets, ensuring we never lost important data:
-- Creating a full backup
BACKUP DATABASE dbname TO disk = 'path_to_backup';
-- Creating a differential backup
BACKUP DATABASE dbname TO disk = 'path_to_backup' WITH DIFFERENTIAL;
-- Creating a transaction log backup
BACKUP LOG dbname TO disk = 'path_to_backup';
-- Restoring from a backup
RESTORE DATABASE dbname FROM disk = 'path_to_backup';
SQL Dialects and Vendor-Specific Extensions
While SQL is standardized, different RDBMSs have their own quirks. For instance, Oracle, SQL Server, MySQL, and PostgreSQL all have unique features and syntax. Choosing the right one often depends on your project's needs.
Conclusion
SQL is vast and can be overwhelming, but understanding its fundamentals is crucial for backend engineers. Whether you're dealing with transactions, backups, or just querying data, SQL is the backbone of database interactions. If you're looking to design your database schema, I highly recommend checking out dynobird.com for an excellent online database design tool. Happy querying!
So, there you have it—SQL through the eyes of a startup experience. From basic queries to advanced functions, SQL is an indispensable skill in the backend engineering toolkit.
Top comments (0)