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.
sql
SELECT * FROM employees;
Example:
sql
SELECT 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.
sql
SELECT product_name, price FROM products;
Example:
sql
SELECT department_name FROM departments;
This command retrieves the department names from the “departments” table.
Filters rows based on a condition.
sql
SELECT first_name, last_name FROM employees WHERE department = ‘Sales’;
Example:
sql
SELECT 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.
sql
SELECT first_name AS fname, last_name AS lname FROM employees;
Example:
sql
SELECT 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.
sql
SELECT first_name, last_name FROM employees WHERE department IN (‘Sales’, ‘Marketing’);
Example:
sql
SELECT 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.
sql
UPDATE employees SET salary = salary * 1.1 WHERE department = ‘Sales’; COMMIT;
Example:
sql
DELETE 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.
sql
DELETE FROM employees WHERE department = ‘Sales’; ROLLBACK;
Example:
sql
UPDATE 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.
sql
UPDATE employees SET salary = salary * 1.05 WHERE department = ‘IT’;
Example:
sql
UPDATE 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.
sql
INSERT INTO employees (first_name, last_name, department, salary) VALUES (‘John’, ‘Doe’, ‘HR’, 60000);
Example:
sql
INSERT 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.
sql
SELECT department_name FROM departments;
Example:
sql
SELECT category, SUM(price) FROM products GROUP BY category;
This command counts the number of employees in each department.
Filters groups based on aggregate properties.
sql
SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000;
Example:
sql
SELECT 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.
sql
SELECT SUM(salary) FROM employees WHERE department = ‘IT’;
Example:
sql
SELECT SUM(price) FROM products WHERE category = ‘Electronics’;
This command calculates the total salary of IT department employees.
Finds the minimum value in a column.
sql
SELECT MIN(salary) FROM employees;
Example:
sql
SELECT MIN(price) FROM products;
This command finds the minimum salary in the “employees” table.
Finds the maximum value in a column.
sql
SELECT MAX(salary) FROM employees;
Example:
sql
SELECT 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.
sql
SELECT employees.first_name, employees.last_name, departments.department_name FROM employees JOIN departments ON employees.department_id = departments.id;
Example:
sql
SELECT 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.
sql
SELECT first_name, last_name FROM employees WHERE department = ‘Sales’ AND salary > 50000;
Example:
sql
SELECT 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.
sql
SELECT first_name, last_name FROM employees WHERE department = ‘Sales’ OR department = ‘Marketing’;
Example:
sql
SELECT 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.
sql
SELECT 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:
sql
SELECT 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.
sql
SELECT first_name, last_name FROM employees WHERE manager_id IS NULL;
Example:
sql
SELECT 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.
sql
SELECT first_name, last_name FROM employees WHERE first_name LIKE ‘J%’;
Example:
sql
SELECT product_name FROM products WHERE product_name LIKE ‘%Phone’;
This command retrieves employees whose first names start with ‘J’.
Modifies an existing table structure.
sql
ALTER TABLE employees ADD birth_date DATE;
Example:
sql
ALTER 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.
sql
CREATE TABLE departments ( id INT PRIMARY KEY, department_name VARCHAR(50) );
Example:
sql
CREATE 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.
sql
DELETE FROM employees WHERE department = ‘Sales’;
Example:
sql
DELETE FROM products WHERE category = ‘Electronics’;
This command deletes all employees in the Sales department.
Deletes a database object like a table or index.
sql
DROP TABLE departments;
Example:
sql
DROP TABLE orders;
This command drops the “departments” table.
Sorts the result set of a query.
sql
SELECT first_name, last_name FROM employees ORDER BY last_name ASC;
Example:
sql
SELECT 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.
sql
SELECT COUNT(*) FROM employees WHERE department = ‘IT’;
Example:
sql
SELECT 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.
sql
SELECT AVG(salary) FROM employees WHERE department = ‘Sales’;
Example:
sql
SELECT 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.
sql
SELECT first_name, last_name FROM employees LIMIT 10;
Example:
sql
SELECT product_name, price FROM products LIMIT 5;
This command retrieves the first 10 employees from the “employees” table.
Selects distinct values from a column.
sql
SELECT DISTINCT department FROM employees;
Example:
sql
SELECT DISTINCT category FROM products;
This command retrieves unique department names from the “employees” table.
Joins two tables on a common column.
sql
SELECT employees.first_name, employees.last_name, departments.department_name FROM employees INNER JOIN departments ON employees.department_id = departments.id;
Example:
sql
SELECT 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.
sql
SELECT employees.first_name, employees.last_name, departments.department_name FROM employees LEFT JOIN departments ON employees.department_id = departments.id;
Example:
sql
SELECT 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.
sql
SELECT employees.first_name, employees.last_name, departments.department_name FROM employees RIGHT JOIN departments ON employees.department_id = departments.id;
Example:
sql
SELECT 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.
sql
SELECT employees.first_name, employees.last_name, departments.department_name FROM employees FULL OUTER JOIN departments ON employees.department_id = departments.id;
Example:
sql
SELECT 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.
sql
SELECT department, COUNT(*) FROM employees GROUP BY department;
Example:
sql
SELECT 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.
sql
SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 10;
Example:
sql
SELECT 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.
PhD Thesis Structure: A Step-by-Step Guide to Crafting a Masterpiece Writing a PhD thesis structure…
How AI Changes RPA: The Evolution from Human Labor to Intelligent Automation Automation is no…
Case Study: How AI-Driven Automation Transformed a Financial Services Firm As automation evolves, industries are…
22 Game-Changing YC Startup Tips You Can’t Afford to Miss in 2024 The startup world…
Mastering Major Decisions: A Comprehensive Guide to Making Big Choices Like a Leader Decision-making is…
The Principles Behind Great CEOs: Insights from Leadership of Jeff, Elon, Zuckerberg What separates a…