10 Common SQL Queries Every Beginner Should Know

Are you ready to take your SQL skills to the next level? As a beginner, you might feel overwhelmed by the vast amount of information and the seemingly never-ending learning curve. However, by mastering the following 10 common SQL queries, you will gain a solid foundation and be able to execute everyday tasks with ease.

1. SELECT Statement

The SELECT statement is the most basic and essential SQL statement that retrieves data from a database. With SELECT, you can extract specific columns or all columns of data, filter and sort data, and join data from multiple tables. Whether you want to fetch a single row or millions of rows, SELECT has got you covered.

-- Fetch all columns from the Customers table
SELECT * FROM Customers;

-- Fetch only the CustomerName and City columns from Customers
SELECT CustomerName, City FROM Customers;

-- Filter the rows that meet a certain condition
SELECT * FROM Customers WHERE Country = 'USA';

-- Sort the query result by a specific column
SELECT * FROM Customers ORDER BY CustomerName;

-- Join multiple tables and retrieve the combined data
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

2. INSERT Statement

The INSERT statement adds new data to a table. It allows you to specify the columns and values to be inserted, and handle possible errors such as duplicate or missing data.

-- Insert a new record into the Customers table
INSERT INTO Customers (CustomerName, ContactName, Country)
VALUES ('Alfreds Futterkiste', 'Maria Anders', 'Germany');

-- Insert multiple records into the Orders table
INSERT INTO Orders (CustomerID, OrderDate)
VALUES (1, '2020-05-02'), (2, '2020-05-03');

3. UPDATE Statement

The UPDATE statement modifies existing data in a table. It allows you to set new values for specific columns, update multiple rows at once, and use conditions to identify the rows to be updated.

-- Update the City of a specific customer
UPDATE Customers SET City = 'Madrid'
WHERE CustomerName = 'Antonio Moreno Taquería';

-- Update the Price of all products in the Beverages category
UPDATE Products SET Price = Price * 1.1
WHERE CategoryID = 1;

4. DELETE Statement

The DELETE statement removes one or more rows from a table. It allows you to specify conditions that determine which rows to delete, and to cascade the deletion to related tables.

-- Delete a specific order from the Orders table
DELETE FROM Orders
WHERE OrderID = 10248;

-- Delete all orders from a specific customer
DELETE FROM Orders
WHERE CustomerID = 1;

-- Cascade the deletion to the OrderDetails table
DELETE FROM Customers
WHERE CustomerID = 1;

5. GROUP BY Clause

The GROUP BY clause groups the query result by one or more columns, and allows you to perform aggregate functions on the grouped data, such as COUNT, SUM, AVG, MAX, and MIN. It is useful when you want to analyze and summarize data in a meaningful way.

-- Count the number of customers per country
SELECT Country, COUNT(*) AS NumCustomers
FROM Customers
GROUP BY Country
ORDER BY NumCustomers DESC;

-- Calculate the average unit price and the total revenue per category
SELECT CategoryName, AVG(Products.UnitPrice) AS AvgPrice, SUM(OrderDetails.Quantity * OrderDetails.UnitPrice) AS TotalRevenue
FROM Categories
INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
INNER JOIN OrderDetails ON Products.ProductID = OrderDetails.ProductID
GROUP BY CategoryName
HAVING TotalRevenue > 10000
ORDER BY AvgPrice DESC;

6. JOIN Clause

The JOIN clause combines rows from different tables based on a related column, and returns a unified result set. It is essential for querying data that is distributed across multiple tables and extracting valuable insights from it.

-- Join the Customers and Orders tables on the CustomerID column
SELECT Customers.CustomerName, Orders.OrderDate
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

-- Join the Products and Categories tables on the CategoryID column
SELECT Products.ProductName, Categories.CategoryName
FROM Products
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID;

7. UNION Clause

The UNION clause combines the results of two or more SELECT statements into a single result set, and removes duplicates. It is useful when you want to combine data from similar tables or queries that share the same structure.

-- Union the results of two SELECT statements
SELECT ProductName FROM Products WHERE CategoryID = 1
UNION
SELECT ProductName FROM Products WHERE CategoryID = 2;

8. EXISTS Clause

The EXISTS clause tests whether a subquery returns any rows, and returns a Boolean value. It is useful when you want to check whether a certain condition holds true or false in relation to another table.

-- Check whether any customer has placed an order
SELECT EXISTS (SELECT * FROM Orders);

-- Check whether any product is out of stock
SELECT EXISTS (SELECT * FROM Products WHERE UnitsInStock = 0);

9. Subquery

A subquery is a query that is nested inside another query, and returns a result set that is used by the outer query as a criteria or a value. It is useful when you want to retrieve data that depends on the results of another query.

-- Retrieve the list of customers who have placed at least one order
SELECT CustomerName FROM Customers
WHERE CustomerID IN (SELECT DISTINCT CustomerID FROM Orders);

-- Calculate the average unit price of the products in each category
SELECT CategoryName, AVG(UnitPrice) AS AvgPrice
FROM Products
WHERE CategoryID IN (SELECT CategoryID FROM Categories WHERE CategoryName LIKE 'B%')
GROUP BY CategoryName;

10. Views

A view is a virtual table that is based on the definition of another table or query, and returns a subset of the data or a transformed version of it. It is useful when you want to simplify complex queries, secure sensitive data, or provide a customized perspective on the data.

-- Create a view that shows the list of customers who are based in the USA
CREATE VIEW USACustomers AS
SELECT CustomerName, City, State
FROM Customers
WHERE Country = 'USA';

-- Select data from the view
SELECT * FROM USACustomers;

-- Modify the view definition
ALTER VIEW USACustomers AS
SELECT CustomerName, State, Phone
FROM Customers
WHERE Country = 'USA';

Conclusion

Congratulations, you have learned the 10 common SQL queries every beginner should know! By mastering SELECT, INSERT, UPDATE, DELETE, GROUP BY, JOIN, UNION, EXISTS, subqueries, and views, you now have a solid foundation to build upon and explore the exciting world of SQL.

Keep in mind that SQL is a vast and constantly evolving field, and there is always more to learn and discover. By practicing these queries, experimenting with different data sets, and reading resources such as learnsql.cloud, you will continue to improve your skills and become a proficient SQL user. So, what are you waiting for? Let's start querying!

Editor Recommended Sites