Structured Query Language (SQL) stands as the cornerstone of database management and data manipulation. Whether you’re a beginner just starting your coding journey or an experienced developer preparing for technical interviews at top tech companies, having a solid grasp of SQL is essential. This comprehensive SQL cheat sheet will serve as your quick reference guide to the most commonly used SQL commands, functions, and techniques.<\/p>\n
SQL commands are generally divided into several categories:<\/p>\n
CREATE DATABASE database_name;<\/code><\/pre>\nCreating a Table<\/h3>\nCREATE TABLE table_name (\n column1 datatype constraints,\n column2 datatype constraints,\n column3 datatype constraints,\n ...\n);<\/code><\/pre>\nExample of Creating a User Table<\/h3>\nCREATE TABLE users (\n user_id INT PRIMARY KEY AUTO_INCREMENT,\n username VARCHAR(50) NOT NULL UNIQUE,\n email VARCHAR(100) NOT NULL UNIQUE,\n password VARCHAR(255) NOT NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);<\/code><\/pre>\nAltering a Table<\/h3>\n-- Adding a column\nALTER TABLE table_name ADD column_name datatype constraints;\n\n-- Modifying a column\nALTER TABLE table_name MODIFY column_name new_datatype new_constraints;\n\n-- Dropping a column\nALTER TABLE table_name DROP COLUMN column_name;<\/code><\/pre>\nDropping a Table or Database<\/h3>\n-- Dropping a table\nDROP TABLE table_name;\n\n-- Dropping a database\nDROP DATABASE database_name;<\/code><\/pre>\nSELECT Statements and Data Retrieval<\/h2>\nBasic SELECT Statement<\/h3>\nSELECT column1, column2, ...\nFROM table_name;<\/code><\/pre>\nSelecting All Columns<\/h3>\nSELECT * FROM table_name;<\/code><\/pre>\nSelecting Distinct Values<\/h3>\nSELECT DISTINCT column_name FROM table_name;<\/code><\/pre>\nUsing Aliases<\/h3>\n-- Column alias\nSELECT column_name AS alias_name FROM table_name;\n\n-- Table alias\nSELECT a.column_name FROM table_name AS a;<\/code><\/pre>\nFiltering and Sorting Data<\/h2>\nWHERE Clause<\/h3>\nSELECT column1, column2, ...\nFROM table_name\nWHERE condition;<\/code><\/pre>\nCommon Operators in WHERE Clause<\/h3>\n-- Comparison operators\nWHERE column_name = value\nWHERE column_name > value\nWHERE column_name < value\nWHERE column_name >= value\nWHERE column_name <= value\nWHERE column_name <> value -- Not equal to\n\n-- Logical operators\nWHERE condition1 AND condition2\nWHERE condition1 OR condition2\nWHERE NOT condition\n\n-- BETWEEN operator\nWHERE column_name BETWEEN value1 AND value2\n\n-- IN operator\nWHERE column_name IN (value1, value2, ...)\n\n-- LIKE operator (pattern matching)\nWHERE column_name LIKE 'pattern'\n-- % represents zero, one, or multiple characters\n-- _ represents a single character<\/code><\/pre>\nSorting Results with ORDER BY<\/h3>\nSELECT column1, column2, ...\nFROM table_name\nORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...<\/code><\/pre>\nLimiting Results<\/h3>\n-- MySQL, PostgreSQL, SQLite\nSELECT column1, column2, ...\nFROM table_name\nLIMIT number;\n\n-- SQL Server\nSELECT TOP number column1, column2, ...\nFROM table_name;\n\n-- Oracle\nSELECT column1, column2, ...\nFROM table_name\nWHERE ROWNUM <= number;<\/code><\/pre>\nJoins and Relationships<\/h2>\nTypes of Joins<\/h3>\n-- INNER JOIN\nSELECT columns\nFROM table1\nINNER JOIN table2 ON table1.column = table2.column;\n\n-- LEFT JOIN (LEFT OUTER JOIN)\nSELECT columns\nFROM table1\nLEFT JOIN table2 ON table1.column = table2.column;\n\n-- RIGHT JOIN (RIGHT OUTER JOIN)\nSELECT columns\nFROM table1\nRIGHT JOIN table2 ON table1.column = table2.column;\n\n-- FULL JOIN (FULL OUTER JOIN)\nSELECT columns\nFROM table1\nFULL JOIN table2 ON table1.column = table2.column;\n\n-- SELF JOIN\nSELECT a.column, b.column\nFROM table_name AS a\nJOIN table_name AS b ON a.common_column = b.common_column;<\/code><\/pre>\nExample of Multiple Joins<\/h3>\nSELECT o.order_id, c.customer_name, p.product_name\nFROM orders o\nJOIN customers c ON o.customer_id = c.customer_id\nJOIN products p ON o.product_id = p.product_id;<\/code><\/pre>\nAggregation Functions<\/h2>\nCommon Aggregate Functions<\/h3>\n-- COUNT\nSELECT COUNT(column_name) FROM table_name;\nSELECT COUNT(*) FROM table_name;\n\n-- SUM\nSELECT SUM(column_name) FROM table_name;\n\n-- AVG\nSELECT AVG(column_name) FROM table_name;\n\n-- MIN\nSELECT MIN(column_name) FROM table_name;\n\n-- MAX\nSELECT MAX(column_name) FROM table_name;<\/code><\/pre>\nGROUP BY Clause<\/h3>\nSELECT column1, COUNT(column2)\nFROM table_name\nGROUP BY column1;<\/code><\/pre>\nHAVING Clause<\/h3>\nSELECT column1, COUNT(column2)\nFROM table_name\nGROUP BY column1\nHAVING COUNT(column2) > value;<\/code><\/pre>\nSubqueries<\/h2>\nSubquery in WHERE Clause<\/h3>\nSELECT column_name\nFROM table1\nWHERE column_name IN (SELECT column_name FROM table2);<\/code><\/pre>\nSubquery with EXISTS<\/h3>\nSELECT column_name\nFROM table1\nWHERE EXISTS (SELECT column_name FROM table2 WHERE condition);<\/code><\/pre>\nSubquery in FROM Clause<\/h3>\nSELECT a.column\nFROM (SELECT column FROM table WHERE condition) AS a;<\/code><\/pre>\nViews<\/h2>\nCreating a View<\/h3>\nCREATE VIEW view_name AS\nSELECT column1, column2, ...\nFROM table_name\nWHERE condition;<\/code><\/pre>\nUsing a View<\/h3>\nSELECT * FROM view_name;<\/code><\/pre>\nDropping a View<\/h3>\nDROP VIEW view_name;<\/code><\/pre>\nTransactions<\/h2>\nTransaction Control<\/h3>\n-- Start a transaction\nBEGIN TRANSACTION;\n\n-- Commit changes\nCOMMIT;\n\n-- Rollback changes\nROLLBACK;\n\n-- Create a savepoint\nSAVEPOINT savepoint_name;\n\n-- Rollback to a savepoint\nROLLBACK TO savepoint_name;<\/code><\/pre>\nIndexes<\/h2>\nCreating an Index<\/h3>\n-- Simple index\nCREATE INDEX index_name ON table_name (column_name);\n\n-- Unique index\nCREATE UNIQUE INDEX index_name ON table_name (column_name);\n\n-- Composite index (multiple columns)\nCREATE INDEX index_name ON table_name (column1, column2);<\/code><\/pre>\nDropping an Index<\/h3>\nDROP INDEX index_name;<\/code><\/pre>\nAdvanced SQL Techniques<\/h2>\nCommon Table Expressions (CTE)<\/h3>\nWITH cte_name AS (\n SELECT column1, column2\n FROM table_name\n WHERE condition\n)\nSELECT * FROM cte_name;<\/code><\/pre>\nWindow Functions<\/h3>\n-- ROW_NUMBER()\nSELECT column1, column2,\nROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2) AS row_num\nFROM table_name;\n\n-- RANK()\nSELECT column1, column2,\nRANK() OVER (PARTITION BY column1 ORDER BY column2) AS rank_num\nFROM table_name;\n\n-- DENSE_RANK()\nSELECT column1, column2,\nDENSE_RANK() OVER (PARTITION BY column1 ORDER BY column2) AS dense_rank_num\nFROM table_name;<\/code><\/pre>\nCASE Statement<\/h3>\nSELECT column1,\nCASE\n WHEN condition1 THEN result1\n WHEN condition2 THEN result2\n ELSE result3\nEND AS new_column\nFROM table_name;<\/code><\/pre>\nString Functions<\/h3>\n-- CONCAT\nSELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;\n\n-- SUBSTRING\nSELECT SUBSTRING(column_name, start, length) FROM table_name;\n\n-- UPPER and LOWER\nSELECT UPPER(column_name), LOWER(column_name) FROM table_name;<\/code><\/pre>\nDate Functions<\/h3>\n-- Current date and time\nSELECT CURRENT_DATE, CURRENT_TIMESTAMP;\n\n-- Date formatting (varies by database system)\nSELECT DATE_FORMAT(date_column, '%Y-%m-%d') FROM table_name;\n\n-- Date arithmetic\nSELECT date_column + INTERVAL 1 DAY FROM table_name;<\/code><\/pre>\nSQL for Technical Interviews<\/h2>\n
When preparing for technical interviews at top tech companies, focus on these SQL concepts:<\/p>\n
1. Complex Joins and Subqueries<\/h3>\n
Practice combining multiple tables and writing nested subqueries to solve complex data problems.<\/p>\n
-- Example: Find customers who have placed more than 3 orders\nSELECT c.customer_name\nFROM customers c\nWHERE (\n SELECT COUNT(*)\n FROM orders o\n WHERE o.customer_id = c.customer_id\n) > 3;<\/code><\/pre>\n2. Window Functions for Analytics<\/h3>\n
Window functions are commonly tested in interviews for data analysis roles.<\/p>\n
-- Example: Calculate running total of sales\nSELECT \n order_date,\n order_amount,\n SUM(order_amount) OVER (ORDER BY order_date) AS running_total\nFROM orders;<\/code><\/pre>\n3. Recursive Queries<\/h3>\n
Recursive CTEs are powerful for handling hierarchical data.<\/p>\n
-- Example: Employee hierarchy\nWITH RECURSIVE employee_hierarchy AS (\n -- Base case: top-level employees (no manager)\n SELECT id, name, manager_id, 1 AS level\n FROM employees\n WHERE manager_id IS NULL\n \n UNION ALL\n \n -- Recursive case: employees with managers\n SELECT e.id, e.name, e.manager_id, eh.level + 1\n FROM employees e\n JOIN employee_hierarchy eh ON e.manager_id = eh.id\n)\nSELECT * FROM employee_hierarchy;<\/code><\/pre>\n4. Data Aggregation Challenges<\/h3>\n
Practice complex GROUP BY scenarios combined with HAVING clauses.<\/p>\n
-- Example: Find products that have been ordered more than average\nSELECT p.product_name, COUNT(o.order_id) AS order_count\nFROM products p\nJOIN orders o ON p.product_id = o.product_id\nGROUP BY p.product_name\nHAVING COUNT(o.order_id) > (\n SELECT AVG(product_order_count)\n FROM (\n SELECT COUNT(order_id) AS product_order_count\n FROM orders\n GROUP BY product_id\n ) AS avg_orders\n);<\/code><\/pre>\n5. Optimization Questions<\/h3>\n
Be prepared to discuss how to optimize slow queries using indexes and query restructuring.<\/p>\n
Conclusion<\/h2>\n
This SQL cheat sheet covers the essential commands and techniques you'll need for everyday database operations and technical interviews. SQL remains one of the most valuable skills for developers, data analysts, and anyone working with structured data. Regular practice with real-world scenarios will help solidify your understanding and prepare you for the complex SQL challenges often found in technical interviews at top tech companies.<\/p>\n
Remember that different database systems (MySQL, PostgreSQL, SQL Server, Oracle, etc.) may have slight variations in syntax. Always refer to the specific documentation for your database system when needed.<\/p>\n
As you continue to build your coding skills through platforms like AlgoCademy, incorporating SQL practice into your routine will strengthen your ability to work with data effectively. Whether you're building applications that interact with databases or preparing for coding interviews, a strong command of SQL will serve as a valuable asset throughout your programming career.<\/p>\n","protected":false},"excerpt":{"rendered":"
Structured Query Language (SQL) stands as the cornerstone of database management and data manipulation. Whether you’re a beginner just starting…<\/p>\n","protected":false},"author":1,"featured_media":7804,"comment_status":"","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23],"tags":[],"class_list":["post-7805","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-problem-solving"],"_links":{"self":[{"href":"https:\/\/algocademy.com\/blog\/wp-json\/wp\/v2\/posts\/7805"}],"collection":[{"href":"https:\/\/algocademy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/algocademy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/algocademy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/algocademy.com\/blog\/wp-json\/wp\/v2\/comments?post=7805"}],"version-history":[{"count":0,"href":"https:\/\/algocademy.com\/blog\/wp-json\/wp\/v2\/posts\/7805\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/algocademy.com\/blog\/wp-json\/wp\/v2\/media\/7804"}],"wp:attachment":[{"href":"https:\/\/algocademy.com\/blog\/wp-json\/wp\/v2\/media?parent=7805"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/algocademy.com\/blog\/wp-json\/wp\/v2\/categories?post=7805"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/algocademy.com\/blog\/wp-json\/wp\/v2\/tags?post=7805"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}