Tag Archives: SQL

Day 12 – SQL COUNT(), AVG() and SUM() Functions

In SQL, the COUNT(), AVG(), and SUM() functions are used to retrieve summary information about data in a table. These functions are often used in combination with the SELECT statement to calculate totals, averages, and counts.

1. COUNT() function: The COUNT() function returns the number of rows that match a specified condition. The basic syntax is as follows:

SELECT COUNT(*) FROM table_name WHERE condition;

Here, * indicates that all rows in the table should be counted, and condition specifies the criteria for the rows to be included in the count.

For example, suppose you have a table called customers with columns customer_id, first_name, last_name, and email_address. If you want to count the number of customers in the table, you would use the following SQL statement:

SELECT COUNT(*) FROM customers;

This statement will return the total number of rows in the customers table.

1. AVG() function: The AVG() function returns the average value of a numeric column in a table. The basic syntax is as follows:

SELECT AVG(column_name) FROM table_name WHERE condition;

Here, column_name is the name of the numeric column for which you want to calculate the average, and condition specifies the criteria for the rows to be included in the calculation.

For example, suppose you have a table called sales with columns product_name and sales_amount. If you want to find the average sales amount for all products, you would use the following SQL statement:

SELECT AVG(sales_amount) FROM sales;

This statement will return the average sales amount for all products in the sales table.

1. SUM() function: The SUM() function returns the sum of the values in a numeric column in a table. The basic syntax is as follows:

SELECT SUM(column_name) FROM table_name WHERE condition;

Here, column_name is the name of the numeric column for which you want to calculate the sum, and condition specifies the criteria for the rows to be included in the calculation.

For example, suppose you have a table called sales with columns product_name and sales_amount. If you want to find the total sales amount for all products, you would use the following SQL statement:

SELECT SUM(sales_amount) FROM sales;

This statement will return the total sales amount for all products in the sales table.

Day 11 – SQL MIN() and MAX() Functions

In SQL, the MIN() and MAX() functions are used to find the minimum and maximum values of a column in a table, respectively. These functions are often used in combination with the SELECT statement to retrieve summary information about data.

The basic syntax for using the MIN() and MAX() functions is as follows:

SELECT MIN(column_name) FROM table_name;
SELECT MAX(column_name) FROM table_name;

Here, column_name is the name of the column for which you want to find the minimum or maximum value, and table_name is the name of the table that contains the column.

For example, suppose you have a table called sales with columns product_name and sales_amount. If you want to find the minimum and maximum sales amounts for all products, you would use the following SQL statements:

SELECT MIN(sales_amount) FROM sales;
SELECT MAX(sales_amount) FROM sales;

These statements will return the minimum and maximum sales amounts for all products in the sales table.

Note that if the column specified in the MIN() or MAX() function contains null values, those values will be ignored in the calculation.

Day 10 – SQL TOP, LIMIT, FETCH FIRST or ROWNUM Clause

SQL provides different ways to limit the number of rows returned in a query, depending on the database management system being used. Here are some examples:

1. TOP clause (used in SQL Server and Microsoft Access) The TOP clause is used to limit the number of rows returned by a query. The basic syntax is as follows:

SELECT TOP n column1, column2, ... FROM table_name WHERE condition;

Here, n is the maximum number of rows to be returned by the query, and column1, column2, etc. are the names of the columns to be selected from the table.

1. LIMIT clause (used in MySQL and PostgreSQL) The LIMIT clause is used to limit the number of rows returned by a query. The basic syntax is as follows:

SELECT column1, column2, ... FROM table_name WHERE condition LIMIT n;

Here, n is the maximum number of rows to be returned by the query, and column1, column2, etc. are the names of the columns to be selected from the table.

1. FETCH FIRST clause (used in Oracle and IBM DB2) The FETCH FIRST clause is used to limit the number of rows returned by a query. The basic syntax is as follows:

SELECT column1, column2, ... FROM table_name WHERE condition FETCH FIRST n ROWS ONLY;

Here, n is the maximum number of rows to be returned by the query, and column1, column2, etc. are the names of the columns to be selected from the table.

1. ROWNUM (used in Oracle) The ROWNUM is used to limit the number of rows returned by a query. The basic syntax is as follows:

SELECT column1, column2, ... FROM table_name WHERE ROWNUM <= n;

Here, n is the maximum number of rows to be returned by the query, and column1, column2, etc. are the names of the columns to be selected from the table.

Note that the specific syntax and functionality of these clauses may vary slightly depending on the database management system being used.

Day 08 – SQL UPDATE Statement

The SQL UPDATE statement is used to modify existing records in a table. The basic syntax of an UPDATE statement is:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

In this syntax, table_name is the name of the table that you want to update records in, and column1, column2, etc. are the names of the columns that you want to update. The SET keyword is used to specify the new values to be assigned to each column. The WHERE clause is used to specify the condition that must be met in order for the update to occur. If no WHERE clause is specified, all records in the table will be updated.

Here’s an example of an UPDATE statement that updates the salary of an employee in a table named “employees”:

UPDATE employees
SET salary = 60000
WHERE id = 1234;

In this example, the UPDATE statement is used to update the salary of the employee with an ID of 1234 to 60000.

You can also update multiple columns at once with a single UPDATE statement. Here’s an example:

UPDATE employees
SET salary = 60000, job_title = 'Manager'
WHERE id = 1234;

In this example, the UPDATE statement updates both the salary and job_title columns of the employee with an ID of 1234.

The UPDATE statement is a fundamental SQL statement that is used to modify existing records in a table. It is commonly used in conjunction with other SQL statements, such as SELECT and JOIN, to manage and manipulate data in a database. When using the UPDATE statement, it’s important to specify the WHERE clause carefully to ensure that only the intended records are updated.

Day 09 – DELETE statement

The SQL DELETE statement is used to delete one or more records from a table. The basic syntax for the DELETE statement is as follows:

DELETE FROM table_name WHERE condition;

Here, table_name is the name of the table from which you want to delete records, and condition specifies the criteria that must be met in order for the record to be deleted.

For example, suppose you have a table called customers with columns customer_id, first_name, last_name, and email_address. If you want to delete all records where the customer_id is equal to 5, you would use the following SQL statement:

DELETE FROM customers WHERE customer_id = 5;

This statement will delete all records in the customers table where the customer_id is equal to 5.

It is important to be cautious when using the DELETE statement, as it permanently removes data from a table. You should always double-check the WHERE clause to ensure that you are only deleting the records you intend to delete.

Day 07 – SQL NULL Values

In SQL, NULL is a special marker used to indicate that a data value does not exist in the database. It is not the same as an empty string or a zero value. A NULL value indicates that the value is unknown, missing, or not applicable.

When creating tables in SQL, you can specify which columns are allowed to contain NULL values by using the NULL or NOT NULL keywords in the column definition. If a column is defined as NULL, it can contain a NULL value. If a column is defined as NOT NULL, it cannot contain a NULL value.

Here are some examples of using NULL values in SQL:

1. Inserting a NULL value into a column:

INSERT INTO employees (first_name, last_name, email, hire_date, salary)
VALUES ('John', 'Doe', NULL, '2022-03-31', 50000);

In this example, the email column is allowed to contain NULL values, so we insert a NULL value for the email address of the employee.

1. Filtering NULL values in a WHERE clause:

SELECT * FROM employees
WHERE email IS NULL;

In this example, we use the IS NULL operator to filter out all employees who do not have an email address.

1. Handling NULL values in calculations:

SELECT (salary * 0.10) AS bonus FROM employees;

In this example, if an employee’s salary is NULL, the result of the calculation will also be NULL. To handle this, you can use the COALESCE function to replace NULL values with a default value:

SELECT (COALESCE(salary, 0) * 0.10) AS bonus FROM employees;

In this example, if an employee’s salary is NULL, the COALESCE function will replace it with 0, and the calculation will still produce a result.

Working with NULL values is an important aspect of SQL data management. Understanding how to handle NULL values properly can help you avoid errors and ensure that your queries produce accurate results.

Day 06 – SQL INSERT INTO statement

SQL INSERT INTO statement is used to insert new records into a table. The basic syntax of an INSERT INTO statement is:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

In this syntax, table_name is the name of the table that you want to insert records into, and column1, column2, column3, etc. are the names of the columns that you want to insert data into. The VALUES keyword is used to specify the values to be inserted into each column.

Here’s an example of an INSERT INTO statement that inserts a new record into a table named “employees”:

INSERT INTO employees (first_name, last_name, email, hire_date)
VALUES ('John', 'Doe', 'johndoe@email.com', '2022-03-31');

In this example, the INSERT INTO statement is used to insert a new record into the “employees” table with the values ‘John’ for the first_name column, ‘Doe’ for the last_name column, ‘johndoe@email.com‘ for the email column, and ‘2022-03-31’ for the hire_date column.

You can also insert multiple records into a table with a single INSERT INTO statement. To do this, you simply separate each set of values with a comma. Here’s an example:

INSERT INTO employees (first_name, last_name, email, hire_date)
VALUES
('Jane', 'Smith', 'janesmith@email.com', '2022-03-30'),
('Bob', 'Johnson', 'bobjohnson@email.com', '2022-03-29');

In this example, the INSERT INTO statement inserts two new records into the “employees” table with the specified values.

The INSERT INTO statement is a fundamental SQL statement that is used to add new records to a table. It is commonly used in conjunction with other SQL statements, such as SELECT and UPDATE, to manage and manipulate data in a database.

Day 04 – logical operators – AND, OR, and NOT

SQL provides three logical operators – AND, OR, and NOT – that can be used in conjunction with the WHERE clause to filter data based on multiple conditions. These operators are used to combine one or more conditions to create more complex conditions for filtering data. Here’s a brief overview of each operator:

1. AND – The AND operator is used to retrieve rows that satisfy multiple conditions. If you specify multiple conditions separated by the AND operator, all the conditions must be true for the row to be retrieved.

Example:

SELECT * FROM orders
WHERE customer_id = 1234 AND order_date >= '2022-01-01';

This SQL statement retrieves all orders where the customer ID is 1234 and the order date is on or after January 1, 2022.

1. OR – The OR operator is used to retrieve rows that satisfy at least one of the specified conditions. If you specify multiple conditions separated by the OR operator, the row will be retrieved if any one of the conditions is true.

Example:

SELECT * FROM orders
WHERE customer_id = 1234 OR order_date >= '2022-01-01';

This SQL statement retrieves all orders where the customer ID is 1234 OR the order date is on or after January 1, 2022.

1. NOT – The NOT operator is used to retrieve rows that do not satisfy a specified condition. If you specify a condition after the NOT operator, the row will be retrieved only if the condition is false.

Example:

SELECT * FROM customers
WHERE NOT country = 'USA';

This SQL statement retrieves all customers where the country is not ‘USA’.

By using the AND, OR, and NOT operators in combination with the WHERE clause, you can create more complex conditions to filter and retrieve data from a table.

Day 03 – SQL WHERE Clause

WHERE clause is used to filter data based on a specified condition or set of conditions. It is used in conjunction with the SELECT statement to retrieve only the rows from a table that meet the specified criteria. The basic syntax of a SELECT statement with a WHERE clause is:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

In this syntax, column1, column2, etc. are the names of the columns that you want to retrieve data from, and table_name is the name of the table that you want to retrieve data from. The condition is a logical expression that evaluates to true or false for each row in the table.

Here’s an example of a SELECT statement with a WHERE clause that retrieves all the rows from a table named “customers” where the “country” column is equal to ‘USA’:

SELECT * FROM customers
WHERE country = 'USA';

In this example, the WHERE clause is used to filter the data and retrieve only the rows where the “country” column is equal to ‘USA’.

You can also use the WHERE clause with other logical operators such as < (less than), > (greater than), <= (less than or equal to), >= (greater than or equal to), <> (not equal to), AND, OR, and NOT. Here’s an example:

SELECT * FROM orders
WHERE customer_id = 1234 AND order_date >= '2022-01-01';

In this example, the WHERE clause is used to retrieve only the rows from the “orders” table where the “customer_id” column is equal to 1234 AND the “order_date” column is greater than or equal to January 1, 2022.

The WHERE clause is a powerful tool for filtering and selecting data from a table based on specific criteria. It allows you to retrieve only the data that is relevant to your analysis or processing, making it easier to work with large amounts of data.