SQL Expressions: Uses and Examples

SQL is a globally operated Query Language that interacts with the databases. It assists us in finding, editing, and handling data effectively. A cheat sheet makes learning easier by giving a quick way to memorize important commands. In this tutorial, we'll go over primary SQL commands, clauses, joins, transactions, and much more to assist you in administering databases instantly and easily. To demonstrate these concepts, we will implement each command in MySQL. SQL Data Types A data type determines the kind of values that can be preserved in a column, outlined below with their explanations: INT: It keeps integers. CHAR(n): It saves a static-size string consisting of n characters. VARCHAR(n): It keeps a variable-length string, comprising a max of n characters. TEXT: It enables the storage of extensive text or strings. DATE: It lets us store dates. DATETIME: It saves dates & times. FLOAT: It stores floating-point digits. BOOLEAN: Postgres and MySQL offer BOOLEAN for storing true or false entries. In contrast, SQL Server utilizes BIT for this purpose. Basic SQL Commands Commands let us create tables, delete, and insert or edit records. For example: CREATE: Generates new databases and other objects. SHOW: Displays a list of all accessible databases and other objects. Postgres doesn’t offer SHOW; however, the equivalent functionality can be obtained in psql by utilizing meta-commands like \l, \dt, \dn, etc. USE: Switches the database. Postgres uses \c meta-command, instead. INSERT: Appends new entries into a designated table. SELECT: Displays information from the stated table(s). UPDATE: Ugrades existing entries in a table. DELETE: Removes desired or all rows. DROP: Permanently drops a database or other objects. Example 1: Create Database Let’s generate a database called hostman_info: CREATE DATABASE hostman_info; Now execute SHOW to justify the database creation: SHOW DATABASES; Now utilize hostman_info by employing the USE command: USE hostman_info; The screenshot below demonstrates that we have successfully established a connection with the hostman_info: Example 2: Create Table The below-stated query demonstrates the table creation with various data types: CREATE TABLE hostman_team ( Hostman_EID INT AUTO_INCREMENT PRIMARY KEY, Hostman_FName VARCHAR(30), Hostman_LName VARCHAR(30), Hostman_DOB DATE, Hostman_ESalary FLOAT, Hostman_EStatus BOOLEAN ); It constructs a new hostman_team table with the requested columns, which can be confirmed with this command: SHOW TABLES; Example 3: Insert Rows Once a table is formed, we can append new entries to the hostman_team table: INSERT INTO hostman_team (Hostman_FName, Hostman_LName, Hostman_DOB, Hostman_ESalary, Hostman_EStatus)VALUES ('Anees', 'Asghar', '1995-01-01', 60000, TRUE); Similarly, users can insert as many records as necessary with a single INSERT statement. In this scenario, each entry to be appended must be separated by a comma. INSERT INTO hostman_team (Hostman_FName, Hostman_LName, Hostman_DOB, Hostman_ESalary, Hostman_EStatus) VALUES ('Joe', 'Root', '1990-01-15', 65000, TRUE),    ('Steve', 'Smith', '1980-03-12', 70000, FALSE); Example 4: Fetch Records Next, execute SELECT to display data from hostman_team: SELECT * FROM hostman_team; Similarly, we can extract only the preferred columns by defining their names: SELECT Hostman_FName, Hostman_LName, Hostman_ESalary FROM hostman_team; Example 5: Update Table SQL gives another helpful statement called UPDATE that assists us in editing existing records: UPDATE hostman_teamSET Hostman_ESalary = 62000WHERE Hostman_EID = 1; To edit entries as per defined criteria, we can utilize UPDATE with the WHERE clause: UPDATE hostman_teamSET Hostman_ESalary = 75000WHERE Hostman_ESalary >= 65000; Example 6: Delete Data If a particular entry is no longer relevant, we can remove it: DELETE FROM hostman_teamWHERE Hostman_EID = 3; To clear all entries of hostman_team, utilize the subsequent query: DELETE FROM hostman_team; SQL SELECT Queries SQL presents various SELECT queries that let us collect data in different ways, including filtering, arranging, and limiting results according to our requirements: DISTINCT: It fetches distinct values while deleting duplicates. WHERE: Obtain the entries according to predetermined criteria. ORDER BY: It gives a certain order to the resultant table. LIMIT: It applies restrictions to the entries to be fetched. This would extract distinct firstNames from Hostman_team: SELECT DISTINCT Hostman_FName FROM hostman_team; Similarly, the subsequent query extracts entries from Hostman_team with EmpID 2 or above and then sorts them in descending sequence to exhibit only the topmost entry: SELECT * FROM hostman_team WHERE Hostman_EID >= 2 ORDER BY Hostman_EID DESC LIMIT 1; SQL Joins SQL comes up with distinct kinds of JOIN that let us merge rows from several tables using related columns. Let’s create a couple of tables titled Hostman_depts and Hostman_staff with the following structure: CREATE TABLE Hostman_depts ( HDptID INT AUTO_INCREMENT PRIMARY KEY, HDptName VARCHAR(255), HDptLocation VARCHAR(255) ); CREATE TABLE Hostman_staff ( HStaffID INT AUTO_INCREMENT PRIMARY KEY, HFirstName VARCHAR(255), HLastName VARCHAR(255), HEmail VARCHAR(255), HPhoneNumber VARCHAR(20), HHireDate DATE, HDptID INT, FOREIGN KEY (HDptID) REFERENCES Hostman_depts(HDptID) ON DELETE CASCADE ON UPDATE CASCADE ); The above query creates the hostman_staff table with a foreign key HDptID linking to the hostman_depts table. After creating the table, we insert some records in these tables, which are populated with the following query: SELECT * FROM Hostman_depts;SELECT * FROM Hostman_staff; INNER JOIN It fetches rows that have related records in both target tables: SELECT HStaffID, HFirstName, HLastName, HEmail, HDptName, HDptLocation FROM Hostman_staff INNER JOIN Hostman_depts ON Hostman_staff.HDptID = Hostman_depts.HDptID; We combine records where the HDptID in the Hostman_staff table corresponds to the HDptID in the Hostman_depts table: LEFT JOIN It fetches all data from the left table with associated entries from the right table. If unmatched, NULLs fill the right table's columns. The below query displays all staff members with their respective departments and addresses: SELECT HStaffID, HFirstName, HLastName, HEmail, HDptName, HDptLocation FROM Hostman_staff LEFT JOIN Hostman_depts ON Hostman_staff.HDptID = Hostman_depts.HDptID; Every record of Hostman_staff is returned, even if there is no related match in the Hostman_depts table: RIGHT JOIN It exhibits all details from the right table and associated entries from the left table. If unmatched, NULL will be displayed for the left table: SELECT HStaffID, HFirstName, HLastName, HEmail, HDptName, HDptLocation FROM Hostman_staff RIGHT JOIN Hostman_depts ON Hostman_staff.HDptID = Hostman_depts.HDptID; It displays all departments and enlisted staff members, with NULL entries when no staff is linked with a department: FULL JOIN It depicts all rows from both tables, with associated records where available. The resultant table possesses NULL values for unavailable records: SELECT HStaffID, HFirstName, HLastName, HEmail, HDptName, HDptLocation FROM Hostman_staff FULL JOIN Hostman_depts ON Hostman_staff.HDptID = Hostman_depts.HDptID; It exhibits all staff members with departments, even if no staff members are allocated to each department. Note: Some SQL versions may not directly support FULL OUTER JOIN. In this scenario, we can integrate LEFT and RIGHT JOIN with UNION to accomplish a similar functionality: SELECT HStaffID, HFirstName, HLastName, HEmail, HDptName, HDptLocation FROM Hostman_staff LEFT JOIN Hostman_depts ON Hostman_staff.HDptID = Hostman_depts.HDptID UNION SELECT HStaffID, HFirstName, HLastName, HEmail, HDptName, HDptLocation FROM Hostman_staff RIGHT JOIN Hostman_depts ON Hostman_staff.HDptID = Hostman_depts.HDptID; Aggregate Functions SQL offers distinct aggregate functions that execute computations on numerous rows and yield a single outcome: COUNT: Computes the total records. SUM: Finds the aggregate of the targeted column. AVG: Calculates column average. MIN: Extracts the column's minimal value. MAX: Locates the column's most elevated value. Let us invoke the aggregate methods to demonstrate their working in practice: SELECT COUNT(*) AS TotalStaff, MIN(HHireDate) AS EarliestHireDate, MAX(HHireDate) AS LatestHireDate FROM Hostman_staff; The outcome demonstrates TotalStaff, EarliestHireDate, and LatestHireDate: Grouping and Filtering in SQL SQL contains several clauses for grouping and filtering the table’s details, as illustrated below. GROUP BY It combines rows with identical entries in targeted columns into a single summary row: SELECT HDptID, COUNT(*) AS TotalStaff FROM Hostman_staff GROUP BY HDptID; The staff members are grouped by HDptID and show the total staff in each department: HAVING It sorts groups as per predefined aggregate criteria. It groups data after the aggregation, unlike WHERE, which filters rows before aggregation: SELECT HDptID, COUNT(*) AS TotalStaff FROM Hostman_staff GROUP BY HDptID HAVING COUNT(*) >3; It assembles staff by HDptID, computes staff members in each department, and demonstrates only departments exceeding 3 staff members: Aliases and Subqueries SQL aliases shorten table and column names, while subqueries assist us in returning data by embedding one query within another. Aliases They are temporary names allotted to tables or columns to make queries user-friendly: SELECT HFirstName AS FN, HLastName AS LN, HDptID AS DID FROM Hostman_staff AS HS; Subqueries SQL subqueries are referred to as the queries embedded inside another query and execute actions as per the outcomes of the outer query: SELECT HFirstName, HLastName, HDptID FROM Hostman_staff WHERE HDptID = ( SELECT HDptID FROM Hostman_staff GROUP BY HDptID ORDER BY COUNT(*) DESC LIMIT 1 ); It fetches staff members who are registered in the department with the highest number of staff: Indexes  Indexes boost the data fetching rate but consume more memory and demand extra supervision. Let’s create an index titled idx_HFirstName on the HFirstName column of Hostman_staff: CREATE INDEX idx_HFirstNameON Hostman_staff (HFirstName); To abolish an index, employ this query: DROP INDEX ON Hostman_staff; Constraints in SQL They impose limitations on table content to sustain precision and stability: PRIMARY KEY: Uniquely recognizes every row. FOREIGN KEY: Sustains referential integrity among tables. NOT NULL: Restrict NULL entries. UNIQUE: Accept distinct entries. CHECK: It applies a restriction check on the data. Let’s constructs a Hostman_orders table with the columns Hostman_OID, Hostman_ODate, Hostman_EID, and more: CREATE TABLE Hostman_orders ( Hostman_OID INT AUTO_INCREMENT PRIMARY KEY, Hostman_ODate DATE NOT NULL, Hostman_EID INT NOT NULL, Hostman_OrderAmount DECIMAL(10, 2) CHECK (Hostman_OrderAmount > 0), Hostman_Status VARCHAR(50) DEFAULT 'Pending', HDptID INT, FOREIGN KEY (HDptID) REFERENCES Hostman_depts(HDptID), FOREIGN KEY (Hostman_EID) REFERENCES Hostman_staff(HStaffID), CHECK (Hostman_ODate >= '2020-01-01') ); The Hostman_OID is set as the primary key, ensuring unique identification for each order. Hostman_ODate must keep a date on or after January 1, 2020. Hostman_EID must reference a valid HStaffID from the Hostman_staff table via a foreign key constraint. The HDptID references a valid HDptID from the Hostman_depts table through a foreign key constraint. Additionally, the Hostman_OrderAmount has a check constraint to ensure it holds a value greater than 0, and the Hostman_Status has a default value of Pending: Data Modifying Statements ALTER TABLE enables modifications to the table’s structure. These changes can involve adding, editing, or deleting columns: ALTER TABLE Hostman_staffADD HStaffEmail VARCHAR(100); It appends a column titled HStaffEmail in the Hostman_staff table: To edit the HStaffEmail column, we employ the MODIFY clause: ALTER TABLE Hostman_staffMODIFY COLUMN HStaffEmail TEXT; To delete EmpEmail from HostmanEmployee, we employ the DROP clause: ALTER TABLE Hostman_staffDROP COLUMN HStaffEmail; SQL Transactions SQL transactions make sures that multiple functions are carried out as one cohesive action to keep data precise and consistent: COMMIT: Finalizes and keeps any modifications made during the recent transaction. ROLLBACK: Cancels any modifications applied throughout the ongoing transaction, reversing all alterations. SAVEPOINT: Designates a precise point within a transaction to which it return if needed. ROLLBACK TO: Undoes modifications to the preferred savepoint if a problem emerges. Conclusion In this cheat sheet, we've gone over core SQL concepts for successfully supervising data in databases. Grasping fundamental SQL principles is vital for successfully manipulating and engaging with databases. We've also illustrated advanced concepts like transaction control, joins, aggregate functions, and SQL constraints that may assist you manage data more accurately. Hostman provides a SQL database hosting for your needs.

In the world of database management, transactions are crucial to ensuring the integrity and consistency of data. SQL transactions allow multiple database operations to be executed as a single, cohesive unit, which either succeeds completely or fails without leaving partial changes. In this article, we’ll explore what SQL transactions are, the ACID properties that guarantee their reliability, and how to effectively manage transactions in SQL database. What Are SQL Transactions? An SQL transaction is a sequence of one or more SQL operations executed as a unit. A transaction ensures that either all operations within it are successfully applied to the database or none are, maintaining data consistency. Transactions are essential for managing data in multi-step processes such as banking transactions, inventory management, or any other system where consistency is key. In this article, We'll create two tables: one for accounts to simulate banking transactions and another for products to simulate product updates. Here's the SQL script to create these tables: CREATE DATABASE bank_store; USE bank_store; -- Table for bank accounts CREATE TABLE accounts ( account_id VARCHAR(10) PRIMARY KEY, account_name VARCHAR(50), balance DECIMAL(10, 2) ); -- Insert some initial data into accounts INSERT INTO accounts (account_id, account_name, balance) VALUES ('A', 'Alice', 1000.00), ('B', 'Bob', 500.00); -- Table for products in a store CREATE TABLE products ( product_id INT PRIMARY KEY, product_name VARCHAR(50), category VARCHAR(50), price DECIMAL(10, 2) ); -- Insert some initial data into products INSERT INTO products (product_id, product_name, category, price) VALUES (1, 'Laptop', 'Electronics', 1000.00), (2, 'Smartphone', 'Electronics', 800.00), (3, 'Jeans', 'Clothing', 50.00), (4, 'Jacket', 'Clothing', 100.00); -- Prevent auto commit of transactions SET autocommit = 0; ACID Properties of Transactions The reliability of SQL transactions is governed by four essential properties, known as ACID: Atomicity: Ensures that all operations within a transaction are treated as a single unit. If any part of the transaction fails, the entire transaction is rolled back. Consistency: Guarantees that a transaction brings the database from one valid state to another. The database’s integrity constraints must be maintained before and after the transaction. Isolation: Ensures that the operations in a transaction are invisible to other transactions until the transaction is complete. This prevents concurrency issues such as dirty reads and race conditions. Durability: Once a transaction is committed, its changes are permanently saved in the database, even in the event of a system crash. These ACID properties ensure that transactions are reliable and maintain data integrity. Let’s focus on the atomicity and isolation of a transaction. We’ll try to update Alice’s and Bob’s accounts atomically. If any step fails, the whole transaction will be rolled back. START TRANSACTION; -- Deduct $200 from Alice's account UPDATE accounts SET balance = balance - 200 WHERE account_id = 'A'; -- This line will cause an error (because there's no Account C), and the transaction will be rolled back UPDATE accounts SET balance = balance + 200 WHERE account_id = 'C'; -- Rollback the transaction if an error occurs ROLLBACK; -- Verify the rollback SELECT * FROM accounts; Here, the update for Bob (Account B) would fail because there is no Account C. As a result, both updates will be rolled back. Starting a Transaction A transaction begins with an explicit command in SQL. Depending on the database management system (DBMS), this might be: BEGIN TRANSACTION; Once a transaction is started, every operation executed will be part of the transaction until it is either committed or rolled back. Here's an example where we increase the price of all electronics by 10%: START TRANSACTION; -- Increase the price of all products in the 'Electronics' category by 10% UPDATE products SET price = price * 1.10 WHERE category = 'Electronics'; -- Commit the changes COMMIT; -- Verify the update SELECT * FROM products; The output looks like this: Committing a Transaction Committing a transaction means making all changes permanent in the database. Once committed, the changes cannot be undone unless another transaction is initiated to modify them. In SQL, a commit is executed using the following command: COMMIT; This marks the successful completion of the transaction, ensuring all operations have been applied. START TRANSACTION; -- Increase the price of clothing items by 15% UPDATE products SET price = price * 1.15 WHERE category = 'Clothing'; -- Commit the transaction COMMIT; -- Verify the changes SELECT * FROM products; And the output is: Rolling Back a Transaction If something goes wrong during a transaction, or if a condition fails, the entire transaction can be reverted to its initial state using a rollback. This prevents incomplete or incorrect data from being saved in the database. The rollback is triggered by the following command: ROLLBACK; This undoes all the changes made by the transaction up to that point. START TRANSACTION; -- Increase the price of electronics by 10% UPDATE products SET price = price * 1.10 WHERE category = 'Electronics'; -- Simulate an error -- Let's say we realize we made a mistake and want to cancel the operation ROLLBACK; -- Verify the rollback SELECT * FROM products; The output should be the same as before: Savepoints and Nested Transactions In more complex transactions, you might want to partially roll back specific operations while still retaining others. This is where savepoints come into play. A savepoint marks a specific point within a transaction to which you can roll back without affecting the entire transaction. You can define a savepoint with: SAVEPOINT savepoint_name; If an error occurs, you can roll back to a specific savepoint: ROLLBACK TO savepoint_name; Nested transactions involve starting a new transaction within the scope of an existing one. They provide more granular control over transaction management, though support for nested transactions varies across different DBMSs. Example: START TRANSACTION; -- Increase the price of electronics by 5% UPDATE products SET price = price * 1.05 WHERE category = 'Electronics'; SAVEPOINT electronics_update; -- Increase the price of clothing by 20% UPDATE products SET price = price * 1.20 WHERE category = 'Clothing'; -- Simulate an error in the clothing update ROLLBACK TO electronics_update; -- Commit the transaction COMMIT; -- Verify the changes SELECT * FROM products; Here, the price increase for clothing items will be rolled back, but the price increase for electronics will remain: Best Practices for Transaction Management Effective transaction management is essential to ensure that your database operations are reliable and consistent. Here are some best practices to follow: Keep transactions short: Long-running transactions can lock resources and reduce concurrency. Always try to limit the number of operations within a transaction. Handle errors gracefully: Use try-catch blocks in your application logic to catch exceptions and ensure proper rollback when needed. Use savepoints wisely: Only set savepoints when necessary, and avoid overusing them in simple transactions as they can introduce unnecessary complexity. Avoid unnecessary locking: Make sure that your transactions do not lock more rows or tables than needed. This helps avoid deadlocks and improves performance in concurrent environments. Test thoroughly: Always test transactions under different scenarios, including failure conditions, to ensure they behave as expected. Conclusion SQL transactions play a critical role in maintaining the reliability, consistency, and integrity of data in a database. Understanding the ACID properties, along with knowing when and how to commit or roll back transactions, is fundamental to good database management. By applying best practices, you can ensure that your transactions are efficient and error-proof.

SQL, also known as the Structured Query Language, is one of the most widely used tools for managing information in relational databases. It offers numerous functions and tools for analyzing and organizing data. One often overlooked but highly useful feature of SQL is the use of counters. The COUNT() function serves as this counter, allowing you to count the number of rows in a table or calculate the number of unique values in a specific column. Despite its apparent simplicity, COUNT() can sometimes pose challenges, especially when dealing with large datasets or unfamiliar data structures. In this article, we'll explore how to use the COUNT() function in SQL to count rows. Through practical examples, we will see how this function can simplify database analysis. By mastering this material, you'll be able to use COUNT() effectively for data analysis and processing. The COUNT() Function A counter in an SQL table, or a "row counter," is a functionality that allows you to count the number of rows in a table that meet a specific condition. This can be useful in various situations, from determining the number of records in a table to more complex analytical queries that count unique records based on certain criteria. One of the most common uses of the row counter in SQL is through the COUNT() function. This built-in function calculates the number of rows in a table or the number of unique values in a column. It can be applied to a single column or to the entire table. The syntax for the COUNT() function in SQL is as follows: SELECT COUNT(column_name)FROM table_nameWHERE condition; column_name: The name of the column whose rows you want to count. table_name: The name of the table from which the data is taken. condition: An optional condition to filter the rows before counting. If your goal is to count all rows in a table, regardless of their specific values, you can use the * symbol instead of specifying a column name, as shown below: SELECT COUNT(*)FROM table_name; Usage Examples Let's look at several examples of how to use the COUNT() operator in SQL. For this tutorial, we will use the SQLite database management system. You can download the DB Browser for SQLite, a free, open-source program for creating and editing SQLite databases. Start the DB Browser for SQLite and go to the "SQL" tab, where we will write queries to create tables and count rows. Counting Total Records in a Table Suppose you have a table named Purchases that contains order information: PurchaseID BuyerID Product 1 101 Apple 2 102 Banana 3 103 Cherry 4 101 Banana 5 104 Cherry To create this table, use the following query: CREATE TABLE Purchases ( PurchaseID INTEGER PRIMARY KEY, BuyerID INTEGER, Product TEXT ); After running the SQL query, go to the "DB Structure" tab to verify that the table was successfully created. Now, let's populate the table with data by running the following query: INSERT INTO Purchases (PurchaseID, BuyerID, Product) VALUES (1, 101, 'Apple'); INSERT INTO Purchases (PurchaseID, BuyerID, Product) VALUES (2, 102, 'Banana'); INSERT INTO Purchases (PurchaseID, BuyerID, Product) VALUES (3, 103, 'Cherry'); INSERT INTO Purchases (PurchaseID, BuyerID, Product) VALUES (4, 101, 'Banana'); INSERT INTO Purchases (PurchaseID, BuyerID, Product) VALUES (5, 104, 'Cherry'); Now, if you want to know the total number of orders, you can use a simple COUNT() with the * symbol: SELECT COUNT(*)FROM Purchases; This will count and return the total number of records in the Purchases table. The result of this query will be 5. Counting Unique Records in a Column Suppose you have a Buyers table, and you want to know how many unique countries are represented among your customers' addresses: BuyerID FirstName BuyerCountry 101 John Canada 102 Alice USA 103 Bob USA 104 Diego Mexico 105 Peter Canada Create the Buyers table as follows: CREATE TABLE Buyers ( BuyerID INTEGER PRIMARY KEY, FirstName TEXT, BuyerCountry TEXT ); And fill it with data: INSERT INTO Buyers (BuyerID, FirstName, BuyerCountry) VALUES (101, 'John', 'Canada'); INSERT INTO Buyers (BuyerID, FirstName, BuyerCountry) VALUES (102, 'Alice', 'USA'); INSERT INTO Buyers (BuyerID, FirstName, BuyerCountry) VALUES (103, 'Bob', 'USA'); INSERT INTO Buyers (BuyerID, FirstName, BuyerCountry) VALUES (104, 'Diego', 'Mexico'); INSERT INTO Buyers (BuyerID, FirstName, BuyerCountry) VALUES (105, 'Peter', 'Canada'); You can see that the BuyerCountry column includes several countries, but some are repeated (e.g., Canada). If you want to count the number of unique countries, you can use COUNT() with the DISTINCT keyword: SELECT COUNT(DISTINCT BuyerCountry)FROM Buyers; This will count and return the number of unique countries in the BuyerCountry column of the Buyers table. The result of this query will be 2. Counting with a Condition (Data Filtering) Sometimes, you only want to count rows that meet a certain condition. Orders in this table are made by customers from different countries: PurchaseID BuyerID Product BuyerCountry 1 101 Apple Canada 2 102 Banana USA 3 103 Cherry USA 4 101 Banana Canada 5 104 Cherry Canada We already have this Purchases table, but it lacks the BuyerCountry column. Let’s add it: ALTER TABLE PurchasesADD BuyerCountry TEXT; And populate it with data: UPDATE Purchases SET BuyerCountry = 'Canada' WHERE BuyerID IN (101, 104); UPDATE Purchases SET BuyerCountry = 'USA' WHERE BuyerID IN (102, 103); Suppose you want to know how many orders were made by customers from Canada. You can use COUNT() with the WHERE condition: SELECT COUNT(*)FROM PurchasesWHERE BuyerCountry = 'Canada'; This SQL query will count and return only the orders made by customers from Canada. The result of this query will be 3. Counting with Data Grouping (GROUP BY) When you need to count records within a specific group, the GROUP BY function is used. Example table Purchases: PurchaseID BuyerID Product 1 101 Apple 2 102 Banana 3 103 Cherry 4 101 Banana 5 104 Cherry 6 101 Apple Let’s modify the existing Purchases table by adding a new row: INSERT INTO Purchases (PurchaseID, BuyerID, Product)VALUES (6, 101, 'Apple'); For example, if you want to find out how many orders each customer has placed, you can run this query: SELECT BuyerID, COUNT(*)FROM PurchasesGROUP BY BuyerID; This query groups the orders by BuyerID and counts the number of orders for each customer. The result will be: BuyerID COUNT(*) 101 3 102 1 103 1 104 1 Combining COUNT and AVG Imagine you have a Ratings table with product ratings by users: RatingID ProductID UserID Rating 1 101 1 4.0 2 102 2 3.5 3 101 3 4.5 4 103 1 5.0 5 101 2 3.0 6 102 3 4.5 Create the table: CREATE TABLE Ratings ( RatingID INT PRIMARY KEY, ProductID INT, UserID INT, Rating DECIMAL(2,1) ); INSERT INTO Ratings (RatingID, ProductID, UserID, Rating) VALUES (1, 101, 1, 4.0), (2, 102, 2, 3.5), (3, 101, 3, 4.5), (4, 103, 1, 5.0), (5, 101, 2, 3.0), (6, 102, 3, 4.5); To find out how many ratings each product received and their average rating, you can run this query: SELECT ProductID, COUNT(*), AVG(Rating)FROM RatingsGROUP BY ProductID; The result will be: ProductID COUNT(*) AVG(Rating) 101 3 3.83 102 2 4.0 103 1 5.0 Using COUNT with HAVING If you want to filter the count results, use HAVING. Suppose you have a large Ratings table, and you want to find which products received more than 100 ratings: SELECT ProductID, COUNT(*)FROM RatingsGROUP BY ProductIDHAVING COUNT(*) > 100; In this case, the result will be 0, as no product has received more than 100 ratings in the current data. The HAVING clause is used after GROUP BY. Using COUNT with JOIN We have two tables: Orders and Customers. Our Orders table: OrderID ProductID CustomerID 1 201 1 2 202 1 3 203 2 4 204 3 You can create it like this: CREATE TABLE Orders ( OrderID INT PRIMARY KEY, ProductID INT, CustomerID INT ); INSERT INTO Orders (OrderID, ProductID, CustomerID) VALUES (1, 201, 1), (2, 202, 1), (3, 203, 2), (4, 204, 3); The Customers table: CustomerID FirstName CustomerCountry 1 John Canada 2 Jack USA 3 Diego Mexico Create the Customers table: CREATE TABLE Customers ( CustomerID INT PRIMARY KEY, FirstName VARCHAR(255), CustomerCountry VARCHAR(255) ); INSERT INTO Customers (CustomerID, FirstName, CustomerCountry) VALUES (1, 'John', 'Canada'), (2, 'Jack', 'USA'), (3, 'Diego', 'Mexico'); To join the two tables and count how many orders were placed by customers from Canada, run the following query: SELECT COUNT(*)FROM OrdersLEFT JOIN Customers ON Customers.CustomerID = Orders.CustomerIDWHERE Customers.CustomerCountry = 'Canada'; This query will return the total number of orders made by customers from Canada. Sorting Data with ORDER BY Suppose you have an Employees table with employee details: EmployeeID FirstName LastName Salary 1 Diego Alonso 55000 2 Peter Smith 75000 3 Alexa Brown 70000 4 James Smith 80000 5 Maria Alonso 90000 Create the Employees table: CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(255), LastName VARCHAR(255), Salary INT ); INSERT INTO Employees (EmployeeID, FirstName, LastName, Salary) VALUES (1, 'Diego', 'Alonso', 55000), (2, 'Peter', 'Smith', 75000), (3, 'Alexa', 'Brown', 70000), (4, 'James', 'Smith', 80000), (5, 'Maria', 'Alonso', 90000); You want to know how many employees earn more than 60,000, grouped by salary, and sorted in descending order. Run the query: SELECT Salary, COUNT(*) FROM Employees WHERE Salary > 60000 GROUP BY Salary ORDER BY Salary DESC; The result will be: Salary COUNT(*) 90000 1 80000 1 75000 1 70000 1 Counting with Multiple Conditions (AND/OR) Suppose you have a Sales table with product sales data: SaleID Product Price Quantity 1 Apple 20 5 2 Banana 15 10 3 Cherry 10 15 4 Apple 20 20 5 Banana 15 25 Create the Sales table: CREATE TABLE Sales ( SaleID INT PRIMARY KEY, Product VARCHAR(255), Price INT, Quantity INT ); INSERT INTO Sales (SaleID, Product, Price, Quantity) VALUES (1, 'Apple', 20, 5), (2, 'Banana', 15, 10), (3, 'Cherry', 10, 15), (4, 'Apple', 20, 20), (5, 'Banana', 15, 25); To find how many times the product Apple was sold in quantities less than 10 or Banana in quantities greater than 20, use this query: SELECT COUNT(*)FROM SalesWHERE (Product = 'Apple' AND Quantity < 10) OR (Product = 'Banana' AND Quantity > 20); This query will return 2 based on the specified conditions. Key Points When Using COUNT() Handling NULL Values: COUNT(column_name) excludes rows with NULL values. To include them, use COUNT(*). Using DISTINCT: COUNT() with DISTINCT counts only unique values in a column. GROUP BY: Often used with COUNT() to count the number of rows in each group. Integer Results: COUNT() always returns an integer result, regardless of the argument’s data type. Subqueries: COUNT() can be used in subqueries to count rows meeting specific criteria. Triggers: COUNT() can be used in SQL triggers to count modified rows. Large Data: For large datasets, COUNT() queries can take time, so optimization may be necessary. JOIN Queries: Complex relationships in JOIN queries require a solid understanding of the database structure for accurate counting. Conclusion We've covered the main features of the COUNT() function and applied it to practical SQL examples. With this knowledge, you can effectively process and analyze data, regardless of its size or complexity. Hostman provides a SQL database hosting for your needs.