Author Archives: Febronei

Day 14 – SQL IN Operator

In SQL, the IN operator is used to specify multiple values in a WHERE clause. The IN operator allows you to specify a list of values, and the WHERE clause will return any rows where the specified column matches any of the values in the list.

The basic syntax for using the IN operator is as follows:

SELECT column_name FROM table_name WHERE column_name IN (value1, value2, value3, ...);

Here, column_name is the name of the column you want to search, table_name is the name of the table containing the column, and value1, value2, value3, etc. are the values you want to search for.

For example, suppose you have a table called employees with columns employee_id, first_name, last_name, and department. If you want to find all employees who work in the “Sales” or “Marketing” departments, you would use the following SQL statement:

SELECT * FROM employees WHERE department IN ('Sales', 'Marketing');

This statement will return all rows from the employees table where the department column matches either “Sales” or “Marketing”.

The IN operator can also be used with a subquery to search for values in another table. For example, suppose you have a table called sales with columns product_id, product_name, and price, and you want to find all products with a price higher than the average price. You could use the following SQL statement:

SELECT product_name, price FROM sales WHERE price > (SELECT AVG(price) FROM sales);

This statement will return all rows from the sales table where the price column is higher than the average price of all products in the sales table.

Day 13 – SQL LIKE Operator

In SQL, the LIKE operator is used to search for patterns in a column of text data. The LIKE operator is often used in combination with wildcard characters to search for specific patterns.

The basic syntax for using the LIKE operator is as follows:

SELECT column_name FROM table_name WHERE column_name LIKE pattern;

Here, column_name is the name of the column you want to search, table_name is the name of the table containing the column, and pattern is the search pattern you want to use.

There are two wildcard characters that can be used with the LIKE operator:

1. % (percent sign): This represents zero or more characters. For example, the pattern '%test%' would match any string that contains the word “test” anywhere in the column.
2. _ (underscore): This represents a single character. For example, the pattern 'a_%' would match any string that starts with the letter “a” followed by any single character.

For example, suppose you have a table called employees with columns employee_id, first_name, last_name, and email_address. If you want to find all employees whose email address ends with “@example.com”, you would use the following SQL statement:

SELECT * FROM employees WHERE email_address LIKE '%@example.com';

This statement will return all rows from the employees table where the email_address column ends with “@example.com”.

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.