Adventures in Machine Learning

Mastering SQL Fundamentals: Basics Clauses Operators and More

SQL Basics Cheat Sheet

If you are new to SQL, the process of learning this query language can be overwhelming. However, with a little guidance, mastering the basics can be accomplished quickly.

In this article, you will be provided with a cheat sheet that will focus primarily on the syntax of basic clauses, writing different conditions, and examples.

Syntax of Basic Clauses

To get started with SQL, it is essential to understand the syntax of basic clauses. The following clauses are used frequently when writing SQL queries: SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY.

  • SELECT: This clause is used to select columns from a table.
  • FROM: This clause is used to specify the table(s) from which the data is being selected.
  • WHERE: This clause is used to set conditions for the data that needs to be selected, based on certain criteria.
  • GROUP BY: This clause is used to group data by a particular column.
  • HAVING: This clause is used to filter groups by a certain criterion.
  • ORDER BY: This clause is used to sort data in a particular order.

Writing Different Conditions

Writing different conditions is an essential part of learning SQL. Understanding how to create conditions in SQL can help you filter data in a way that suits your requirements.

To write different conditions, understanding the following operators is necessary: =, <>, >, >=, <, and <=.

  • =: This operator is used to compare the value of a column with a specific value.
  • <>: This operator is used to find values that are not equal to a particular value.
  • >, >=: These operators are used to find values that are greater than or greater than/equal to a particular value, respectively.
  • <, <=: These operators are used to find values that are less than or less than/equal to a particular value, respectively.

Examples

Example 1: SELECT and WHERE

SELECT first_name, last_name
FROM employees
WHERE department_id = 2;

This query selects the first name and last name columns from the employees table and displays only the rows where the department ID is 2.

Example 2: GROUP BY, HAVING, and ORDER BY

SELECT COUNT(*), department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10
ORDER BY COUNT(*) DESC;

This query counts the number of employees by department and displays those departments that have more than 10 employees sorted in a descending order based on the total number of employees.

SQL

SQL (Structured Query Language) is a language used for managing databases. It is a universal language used to interact with databases and often used to extract data from large databases.

SQL is a standard for database interaction and supported by almost all the popular database vendors, such as Oracle, MySQL, SQL Server, and PostgreSQL.

Importance of SQL

SQL can be a powerful tool for managing and manipulating data. It is among the top skills required by most organizations today.

The importance of SQL lies in the fact that businesses today collect a lot of data, and analyzing the data can provide valuable insights that can ultimately improve business decisions. Combine this with the increasing reliance on data-driven decision-making across all industries, and the importance of learning SQL should be clear.

Conclusion

In conclusion, SQL is an essential language, even for those who are not specifically working in the technical field. It is a necessary skill for conducting business analysis, data science, and almost every industry that deals with large amounts of data.

With this cheat sheet, you should now have a basic understanding of SQL, which can help you get started with the language. Keep practicing and experimenting with SQL – the more comfortable you are with it, the faster and more efficient you can make queries and automate analysis.

Sample Data

If you are learning SQL, the best way to practice is by working with sample data. By using sample data, you can gain an understanding of how SQL works and how queries are formulated.

In this article, we will discuss querying a single table, fetching specific columns, and sorting data.

Querying a Single Table

Querying a single table is the most basic SQL operation. Usually, a database contains several tables, but in this case, we will query only one.

To query a single table, we use the SELECT statement.

SELECT *
FROM employees;

This query selects and displays all columns from the employees table in the database. The asterisk symbol (*) is a wildcard operator that represents all columns.

Use column names or wildcard operators to specify which columns you want to select.

Fetching Specific Columns

In some cases, you may not want all the columns from a table. Rather, you may only need specific columns.

By fetching specific columns, you can reduce the amount of data that is returned by your query, making your data retrieval faster and more efficient.

SELECT first_name, last_name, email
FROM employees;

In this example, the query selects only the first_name, last_name, and email columns from the employees table. You can add or remove the column names as needed to suit your query.

Sorting Data

Sorting data is essential for analyzing data and making it easier to interpret. SQL provides the ORDER BY clause to sort data.

You can sort data in ascending or descending order, based on specified columns.

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC;

This query selects first_name, last_name, and salary columns from employees table, sorted in descending order based on the salary column. You can use ASC (ascending) or DESC (descending) to specify the sort order.

Filtering the Output

Filtering the output is a powerful feature of SQL that allows you to limit or refine the data that you retrieve from a table. A filter is created using the WHERE clause.

Comparison Operators

SQL comparison operators are used to compare two values and return a Boolean result, either true or false. Comparison operators include =, <>, >, >=, <, and <=.

SELECT first_name, last_name, salary
FROM employees
WHERE salary > 50000;

This query selects first_name, last_name, and salary columns from employees table where salary is greater than $50,000.

Text Operators

SQL text operators work with string data and are used to search or manipulate text values. Text operators include LIKE, IN, and NOT IN.

SELECT first_name, last_name, email
FROM employees
WHERE email LIKE '%@gmail.com';

This query selects first_name, last_name, and email columns from employees table where email includes ‘@gmail.com’.

Other Operators

Other operators include logical and arithmetic operators. The AND, OR, and NOT operators are logical operators, and +,-, *, and / are arithmetic operators.

SELECT first_name, last_name, salary
FROM employees
WHERE salary > 50000 AND salary < 75000;

This query selects first_name, last_name, and salary columns from employees table where salary is greater than $50,000 and less than $75,000.

Conclusion

By mastering single table queries, fetching specific columns, sorting data, and filtering the output using comparison, text, and other operators, you can begin to take full advantage of SQL’s powerful features. Practice with sample data, and you will gain increased confidence and proficiency that will allow you to work with real-world data efficiently.

As you become more familiar with SQL, you can begin to work on more complex queries by linking tables and using advanced operators.

Querying Multiple Tables

When working with large databases, queries often involve more than one table. Querying multiple tables allows you to combine data from different tables based on a common field.

SQL provides different types of joins to help you merge data from multiple tables.

Inner Join

Inner join is the most common type of join in SQL. This join returns only those rows that have matching values in both tables.

SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers
ON orders.customer_id=customers.customer_id;

In this example, the INNER JOIN clause combines the orders and customers tables based on the values in the customer_id column. It selects the order_id from the orders table and the customer name from the customers table where the join condition is true.

Left Join

A left join returns all the rows from the left table and matching rows from the right table.

SELECT orders.order_id, customers.customer_name
FROM orders
LEFT JOIN customers
ON orders.customer_id=customers.customer_id;

This left join returns all orders from the orders table, and only matching customers from the customers table. If there is no matching customer, the customer_name column returns NULL.

Right Join

A right join returns all rows from the right table and matching rows from the left table.

SELECT orders.order_id, customers.customer_name
FROM orders
RIGHT JOIN customers
ON orders.customer_id=customers.customer_id;

This right join returns all customers from the customers table and only matching orders from the orders table. If there is no matching order, the order_id column returns NULL.

Full Join

A full join returns all rows from both the left and the right tables.

SELECT orders.order_id, customers.customer_name
FROM orders
FULL OUTER JOIN customers
ON orders.customer_id=customers.customer_id;

Full outer join is not supported by all SQL databases. For example, MySQL does not support full outer join.

Cross Join

Cross join is also known as a Cartesian join. This join returns all possible combinations of rows from both tables.

SELECT orders.order_id, customers.customer_name
FROM orders
CROSS JOIN customers;

In this example, the cross join returns every possible combination of the order_id and customer_name columns, with no criteria for matching rows.

Natural Join

A natural join is a join that is performed implicitly. It matches columns with the same name(s) in both tables.

SELECT orders.order_id, customers.customer_name
FROM orders
NATURAL JOIN customers;

In this natural join example, the join keys are implicit, and SQL matches columns that have the same name in both tables.

Aggregation and Grouping

When working with data, it’s not always necessary to show each row of a table. Instead, you might want to summarize information by grouping data by specific values or applying a function to a group of rows.

SQL provides several functions and keywords to help you aggregate data and group it to generate meaningful insights.

Group By

The GROUP BY clause groups rows that have the same value, and returns the result in a summarized format.

SELECT employee_id, COUNT(*), MAX(salary)
FROM employees
GROUP BY employee_id;

The query above groups employees by employee_id and then returns the count of employees and the maximum salary per employee ID.

Aggregate Functions

Aggregate functions operate on groups of rows and return a single value for each group.

SELECT AVG(salary), SUM(salary), MIN(salary), MAX(salary)
FROM employees;

In this example, we show the average, sum, minimum, and maximum salary of all employees in the table.

Example Queries

SELECT department_name, COUNT(*), AVG(salary)
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id
GROUP BY department_name;

This query joins the employees and departments tables based on department_id and then groups the results by department_name. It shows a count of employees and their average salary for each department.

SELECT customers.customer_name, COUNT(orders.order_id), SUM(order_details.quantity*order_details.unit_price)
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id
LEFT JOIN order_details
ON orders.order_id = order_details.order_id
GROUP BY customers.customer_name;

In this query, we join the customers, orders, and order_details tables. Using a left join, we ensure that customers without orders are also included.

Then, grouping results by customer name, we count orders and show the sum of the product of quantity and unit price for each customer.

Conclusion

In this article, we discussed querying multiple tables with different types of joins, including inner, left, right, full, cross, and natural joins. We also covered aggregation and grouping with SQL, including the group by clause and aggregate functions.

By understanding these concepts, you can work with more complex databases and create queries to extract valuable insights from your data.

Subqueries

Subqueries are queries that are nested within other queries. They allow you to write complex queries that retrieve data from multiple tables.

In this article, we will discuss three types of subqueries: single value, multiple values, and correlated subqueries.

Single Value Subquery

A single value subquery returns one value that is compared to another value. It is used primarily in conditions where only one value is expected.

SELECT product_name, unit_price
FROM products
WHERE unit_price >
(
   SELECT AVG(unit_price)
   FROM products
);

In this example, we determine all products whose unit_price is above the average unit price of all products in the table.

Multiple Values Subquery

A multiple values subquery can return more than one value in a list format. This type of subquery can be used in conditions where multiple values are expected.

SELECT customer_name, city
FROM customers
WHERE customer_id IN
(
   SELECT customer_id
   FROM orders
   WHERE shipped_date IS NULL
);

In this example, we select all customers located in the city where orders haven’t been shipped yet.

Correlated Subquery

A correlated subquery is a subquery that depends on the outer query. It is used to filter or narrow search criteria based on the result of the outer query.

SELECT product_name, unit_price
FROM products p1
WHERE unit_price >
(
   SELECT AVG(unit_price)
   FROM products p2
   WHERE p1.category_id = p2.category_id
);

In this example, we use a correlated subquery to compare the average unit price of each product category to that of other products in the same category.

Set Operations

Set operations in SQL allow you to combine data from multiple tables that have the same structure. The most commonly used set operations are UNION, INTERSECT, and EXCEPT.

Union

The UNION operator combines the result sets of two or more SELECT statements. Each SELECT statement must have the same number of columns and compatible data types.

SELECT product_id, product_name, unit_price 
FROM products

UNION

SELECT product_id, product_name, unit_price FROM discontinued_products;

In this example, we combine the result sets of two SELECT statements returning all product IDs, names, and unit prices from both the products and discontinued_products tables.

Intersect

The INTERSECT operator returns only the common records of two SELECT statements. The number and order of columns must be the same in both SELECT statements.

SELECT employee_id 
FROM employees

INTERSECT

SELECT employee_id FROM sales;

This example returns only the employee IDs that appear in both the employees and sales tables.

Except

The EXCEPT operator returns only the records that appear in the first SELECT statement, but not in the second. The number and order of columns must be the same in both SELECT statements.

SELECT customer_id FROM customers
EXCEPT
SELECT customer_id 
FROM orders;

In this example, we return only the customer IDs who have not yet placed an order.

Conclusion

In practice, subqueries and set operations in SQL can vastly improve your database querying skills. Single value subqueries allow you to retrieve only one value, multiple values subqueries allow you to retrieve multiple values, and correlated subqueries can narrow down the search criteria based on the outer query.

Meanwhile, set operations like UNION, INTERSECT, and EXCEPT allow you to combine data from multiple tables that have the same structure. Understanding the syntax and purpose of subqueries and set operations can make you a more efficient and effective database analyst.

With practice, you can start leveraging these techniques to write more complex SQL queries that can meet your specific business needs. In conclusion, understanding SQL’s basics, its features like querying single table, filtering output, multiple tables’ querying, aggregation, and grouping, subqueries, and set operations is essential for anyone working with databases.

SQL is a valuable tool used across various industries and can help businesses to make accurate and data-driven decisions. By mastering SQL’s fundamentals, including clauses, operators, and the various techniques covered in this article, you can effectively interact with databases and extract valuable insights from your data.

Popular Posts