Data Retrieval Commands

SELEC FROM Command

Can I use select without from ?

1. Yes, using DUAL Tables.

2. Dual tables are dummy tables created by MySQL, help users to do certain obvious actions without referring to user

In SQL, you can use the SELECT statement without specifying a FROM clause, but it serves a different purpose when used this way. When you use SELECT without FROM, you are typically performing a query that doesn't involve selecting data from a database table but instead involves evaluating expressions or calculations.

Here are a few common scenarios where you might use SELECT without FROM:

1. Selecting Constants or Expressions: You can use SELECT to select constant values or calculate expressions without involving database tables. For example:

   SELECT 42; -- Selects the constant value 42
   SELECT 5 + 7; -- Selects the result of the expression 5 + 7 (12)

2. Using Built-in Functions: You can use SELECT to call built-in SQL functions and retrieve their results:

   SELECT CURRENT_DATE; -- Selects the current date
   SELECT UPPER('hello'); -- Selects 'HELLO'

3. Aliasing: You can also use SELECT without FROM to assign aliases to expressions or constants, which can be useful for making the query results more readable:

   SELECT 42 AS "Answer to Life, the Universe, and Everything";

4. Subqueries: You can use subqueries within a SELECT statement without specifying a FROM clause. These subqueries can return single values or result sets that are used in the main query.

   SELECT (SELECT MAX(salary) FROM employees) AS max_salary;

In summary, while the primary use of the SELECT statement is to retrieve data from database tables using the FROM clause, you can also use it without FROM for various other purposes, such as selecting constants, performing calculations, using functions, aliasing, and working with subqueries.

WHERE CLAUSE

The WHERE clause in SQL is used to filter rows from a table based on a specified condition. It allows you to retrieve only the rows that meet the specified criteria, excluding those that do not. The WHERE clause is commonly used in SELECT, UPDATE, and DELETE statements.

SELECT * FROM `worker` WHERE salary > 55000;

SELECT * FROM `worker` WHERE manager_id = 1;

SELECT * FROM `worker` WHERE department = "Engineering";

BETWEEN

[ RANGE1 , RANGE2 ] INCLUDED

SELECT * FROM `worker` WHERE salary BETWEEN 0 AND 55000;

IN

Reduces OR conditions;

SELECT * FROM `worker` WHERE department IN ('HR' , 'Account');

AND/OR/NOT

1. AND: WHERE cond1 AND cond2

2. OR: WHERE cond1 OR cond2

3. NOT: WHERE col_name NOT IN (1,2,3,4);

SELECT * FROM `worker` WHERE department NOT IN ('HR' , 'Account');