SQL (Structured Query Language) is a powerful language used to manage and manipulate databases. Knowing how to use SQL commands effectively is essential for data analysts, developers, and database administrators. This guide provides a comprehensive overview of the most important SQL commands, accompanied by practical examples.
Retrieves data from a database.
sqlSELECT * FROM employees;
Example:
sqlSELECT first_name, last_name FROM employees;
This command selects all records from the “employees” table, retrieving only the first and last names of each employee.
Specifies the table to retrieve data from.
sqlSELECT product_name, price FROM products;
Example:
sqlSELECT department_name FROM departments;
This command retrieves the department names from the “departments” table.
Filters rows based on a condition.
sqlSELECT first_name, last_name FROM employees WHERE department = ‘Sales’;
Example:
sqlSELECT product_name, price FROM products WHERE price > 100;
This command retrieves product names and prices from the “products” table where the price is greater than 100.
Renames a column or table with an alias.
sqlSELECT first_name AS fname, last_name AS lname FROM employees;
Example:
sqlSELECT product_name AS pname, price AS cost FROM products;
This command renames the “product_name” column to “pname” and “price” column to “cost” in the result set.
Checks if a value matches any value in a list.
sqlSELECT first_name, last_name FROM employees WHERE department IN (‘Sales’, ‘Marketing’);
Example:
sqlSELECT product_name FROM products WHERE category IN (‘Electronics’, ‘Furniture’);
This command retrieves product names from the “products” table where the category is either ‘Electronics’ or ‘Furniture’.
Saves all changes made during the current transaction.
sqlUPDATE employees SET salary = salary * 1.1 WHERE department = ‘Sales’; COMMIT;
Example:
sqlDELETE FROM employees WHERE department = ‘Sales’; COMMIT;
This command increases the salary of employees in the Sales department by 10% and saves the changes.
Undoes all changes made during the current transaction.
sqlDELETE FROM employees WHERE department = ‘Sales’; ROLLBACK;
Example:
sqlUPDATE employees SET salary = 50000 WHERE department = ‘HR’; ROLLBACK;
This command deletes records of employees in the Sales department and then undoes the deletion.
Modifies existing data in a table.
sqlUPDATE employees SET salary = salary * 1.05 WHERE department = ‘IT’;
Example:
sqlUPDATE products SET price = price * 1.2 WHERE category = ‘Electronics’;
This command updates the salary of IT department employees by 5%.
Adds new rows to a table.
sqlINSERT INTO employees (first_name, last_name, department, salary) VALUES (‘John’, ‘Doe’, ‘HR’, 60000);
Example:
sqlINSERT INTO products (product_name, category, price) VALUES (‘Laptop’, ‘Electronics’, 800);
This command inserts a new employee record into the “employees” table.
Groups rows sharing a property for aggregate functions.
sqlSELECT department_name FROM departments;
Example:
sqlSELECT category, SUM(price) FROM products GROUP BY category;
This command counts the number of employees in each department.
Filters groups based on aggregate properties.
sqlSELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000;
Example:
sqlSELECT category, COUNT(*) FROM products GROUP BY category HAVING COUNT(*) > 10;
This command finds departments with an average salary greater than 50,000.
Calculates the total of a numeric column.
sqlSELECT SUM(salary) FROM employees WHERE department = ‘IT’;
Example:
sqlSELECT SUM(price) FROM products WHERE category = ‘Electronics’;
This command calculates the total salary of IT department employees.
Finds the minimum value in a column.
sqlSELECT MIN(salary) FROM employees;
Example:
sqlSELECT MIN(price) FROM products;
This command finds the minimum salary in the “employees” table.
Finds the maximum value in a column.
sqlSELECT MAX(salary) FROM employees;
Example:
sqlSELECT MAX(price) FROM products;
This command finds the maximum salary in the “employees” table.
Combines rows from two or more tables based on related columns.
sqlSELECT employees.first_name, employees.last_name, departments.department_name FROM employees JOIN departments ON employees.department_id = departments.id;
Example:
sqlSELECT orders.order_id, customers.customer_name FROM orders JOIN customers ON orders.customer_id = customers.id;
This command joins the “employees” and “departments” tables based on the department_id.
Combines two or more conditions in a query.
sqlSELECT first_name, last_name FROM employees WHERE department = ‘Sales’ AND salary > 50000;
Example:
sqlSELECT product_name, price FROM products WHERE category = ‘Electronics’ AND price > 500;
This command retrieves employees in the Sales department with a salary greater than 50,000.
Combines two or more conditions in a query where any condition can be true.
sqlSELECT first_name, last_name FROM employees WHERE department = ‘Sales’ OR department = ‘Marketing’;
Example:
sqlSELECT product_name FROM products WHERE category = ‘Electronics’ OR category = ‘Furniture’;
This command retrieves employees in either the Sales or Marketing departments.
Provides conditional logic in a query.
sqlSELECT first_name, last_name, CASE WHEN salary > 70000 THEN ‘High’ WHEN salary BETWEEN 50000 AND 70000 THEN ‘Medium’ ELSE ‘Low’ END AS salary_level FROM employees;
Example:
sqlSELECT product_name, CASE WHEN price > 1000 THEN ‘Expensive’ WHEN price BETWEEN 500 AND 1000 THEN ‘Moderate’ ELSE ‘Cheap’ END AS price_range FROM products;
This command categorizes employees’ salary levels as ‘High’, ‘Medium’, or ‘Low’.
Checks for null values.
sqlSELECT first_name, last_name FROM employees WHERE manager_id IS NULL;
Example:
sqlSELECT product_name FROM products WHERE price IS NULL;
This command retrieves employees who do not have a manager.
Searches for a specified pattern in a column.
sqlSELECT first_name, last_name FROM employees WHERE first_name LIKE ‘J%’;
Example:
sqlSELECT product_name FROM products WHERE product_name LIKE ‘%Phone’;
This command retrieves employees whose first names start with ‘J’.
Modifies an existing table structure.
sqlALTER TABLE employees ADD birth_date DATE;
Example:
sqlALTER TABLE products ADD stock_quantity INT;
This command adds a new column “birth_date” to the “employees” table.
Defines a new table, database, or other database object.
sqlCREATE TABLE departments ( id INT PRIMARY KEY, department_name VARCHAR(50) );
Example:
sqlCREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, order_date DATE );
This command creates a new “departments” table.
Removes data from a table.
sqlDELETE FROM employees WHERE department = ‘Sales’;
Example:
sqlDELETE FROM products WHERE category = ‘Electronics’;
This command deletes all employees in the Sales department.
Deletes a database object like a table or index.
sqlDROP TABLE departments;
Example:
sqlDROP TABLE orders;
This command drops the “departments” table.
Sorts the result set of a query.
sqlSELECT first_name, last_name FROM employees ORDER BY last_name ASC;
Example:
sqlSELECT product_name, price FROM products ORDER BY price DESC;
This command sorts employees by last name in ascending order.
Counts the number of rows in a table.
sqlSELECT COUNT(*) FROM employees WHERE department = ‘IT’;
Example:
sqlSELECT COUNT(*) FROM products WHERE category = ‘Electronics’;
This command counts the number of employees in the IT department.
Calculates the average value of a numeric column.
sqlSELECT AVG(salary) FROM employees WHERE department = ‘Sales’;
Example:
sqlSELECT AVG(price) FROM products WHERE category = ‘Furniture’;
This command calculates the average salary of employees in the Sales department.
Restricts the number of rows returned by a query.
sqlSELECT first_name, last_name FROM employees LIMIT 10;
Example:
sqlSELECT product_name, price FROM products LIMIT 5;
This command retrieves the first 10 employees from the “employees” table.
Selects distinct values from a column.
sqlSELECT DISTINCT department FROM employees;
Example:
sqlSELECT DISTINCT category FROM products;
This command retrieves unique department names from the “employees” table.
Joins two tables on a common column.
sqlSELECT employees.first_name, employees.last_name, departments.department_name FROM employees INNER JOIN departments ON employees.department_id = departments.id;
Example:
sqlSELECT orders.order_id, customers.customer_name FROM orders INNER JOIN customers ON orders.customer_id = customers.id;
This command performs an inner join between the “employees” and “departments” tables.
Left join two tables.
sqlSELECT employees.first_name, employees.last_name, departments.department_name FROM employees LEFT JOIN departments ON employees.department_id = departments.id;
Example:
sqlSELECT orders.order_id, customers.customer_name FROM orders LEFT JOIN customers ON orders.customer_id = customers.id;
This command performs a left join between the “employees” and “departments” tables.
Right join two tables.
sqlSELECT employees.first_name, employees.last_name, departments.department_name FROM employees RIGHT JOIN departments ON employees.department_id = departments.id;
Example:
sqlSELECT orders.order_id, customers.customer_name FROM orders RIGHT JOIN customers ON orders.customer_id = customers.id;
This command performs a right join between the “employees” and “departments” tables.
Full join two tables.
sqlSELECT employees.first_name, employees.last_name, departments.department_name FROM employees FULL OUTER JOIN departments ON employees.department_id = departments.id;
Example:
sqlSELECT orders.order_id, customers.customer_name FROM orders FULL OUTER JOIN customers ON orders.customer_id = customers.id;
This command performs a full outer join between the “employees” and “departments” tables.
Group records by a column.
sqlSELECT department, COUNT(*) FROM employees GROUP BY department;
Example:
sqlSELECT category, COUNT(*) FROM products GROUP BY category;
This command groups employees by department and counts the number of employees in each department.
Filter groups by a condition.
sqlSELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 10;
Example:
sqlSELECT category, COUNT(*) FROM products GROUP BY category HAVING COUNT(*) > 5;
This command filters groups to only include departments with more than 10 employees.
Mastering these SQL commands is crucial for anyone working with databases. Whether you’re retrieving data, updating records, or managing database structures, these commands provide the tools needed to interact effectively with your database. Practice using these commands to become proficient in SQL and enhance your data management skills.
YC Startup Ideas and Guidance 1. Government Software Idea:AI software to automate government administrative tasks.Target…
The Three Circles of Influence: Focus on What Truly Matters In an increasingly complex world,…
Introduction In the realm of programming, choosing the right language is crucial for the success…
Introduction: Challenging Common Misconceptions About Learning When we think about learning a new skill—whether coding,…
Introduction: The Power of ChatGPT Prompt Frameworks In the rapidly evolving world of artificial intelligence,…
Introduction: Embracing Timeless Life Lessons for a Fulfilling Life Life is a journey filled with…