"AND" Condition in SQL

The SQL "AND" condition allows you to create an SQL statement based on 2 or more conditions being met. It can be used in any valid SQL statement - SQL SELECT statement, SQL INSERT Statement, SQL UPDATE Statement, SQL DELETE Statement.

The syntax for the SQL "AND" condition is:

SELECT columns
FROM tables
WHERE column1 = 'value1'
and column2 = 'value2';

The SQL "AND" condition requires that each condition be must be met for the record to be included in the result set. In this case, column1 has to equal 'value1' and column2 has to equal 'value2'.

SQL "AND" Condition - SQL SELECT Statement example

The first SQL "AND" Condition example that we'll take a look at involves an SQL SELECT statement with 2 conditions:

SELECT *
FROM suppliers
WHERE city = 'New York'
and type = 'PC Manufacturer';

This SQL "AND" condition example would return all suppliers that reside in New York and are PC Manufacturers. Because the * is used in the SQL SELECT statement, all fields from the supplier table would appear in the result set.

SQL "AND" Condition - JOINING Tables example

Our next example demonstrates how the SQL "AND condition" can be used to join multiple tables in an SQL statement.

SELECT orders.order_id, suppliers.supplier_name
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id
and suppliers.supplier_name = 'IBM';

This SQL "AND" condition example would return all rows where the supplier_name is IBM. And the suppliers and orders tables are joined on supplier_id. You will notice that all of the fields are prefixed with the table names (ie: orders.order_id). This is required to eliminate any ambiguity as to which field is being referenced; as the same field name can exist in both the suppliers and orders tables.

In this case, the result set would only display the order_id and supplier_name fields (as listed in the first part of the select statement.).

SQL "AND" Condition - SQL INSERT Statement example

The SQL "AND" Condition can be used in the SQL INSERT statement.

For example:.

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE customer_name = 'IBM'
or city = 'New York';

This SQL "AND" Condition example would insert into the suppliers table, all account_no and name records from the customers table whose customer_name is IBM and reside in New York.

SQL "AND" Condition - SQL UPDATE Statement example

The SQL "AND" Condition can be used in the SQL UPDATE statement.

For example:.

UPDATE suppliers
SET supplier_name = 'HP'
WHERE supplier_name = 'IBM'
and state = 'California';

This SQL "AND" Condition example would update all supplier_name values in the suppliers table to HP where the supplier_name was IBM and resides in the state of California.

SQL "AND" Condition - SQL DELETE Statement example

The SQL "AND" Condition can be used in the SQL DELETE statement.

For example:.

DELETE FROM suppliers
WHERE supplier_name = 'IBM'
and product = 'PC computers';

This SQL "AND" Condition example would delete all suppliers from the suppliers table whose supplier_name was IBM and product was PC computers.

"OR" Condition in SQL

The SQL "OR" condition allows you to create an SQL statement where records are returned when any one of the conditions are met. It can be used in any valid SQL statement - SQL SELECT statement, SQL INSERT Statement, SQL UPDATE Statement, SQL DELETE Statement.

The syntax for the SQL OR condition is:

SELECT columns
FROM tables
WHERE column1 = 'value1'
or column2 = 'value2';

The SQL "OR" condition requires that any of the conditions be must be met for the record to be included in the result set. In this case, column1 has to equal 'value1' OR column2 has to equal 'value2'.

SQL "OR" Condition - SQL SELECT Statement example

The first SQL "OR" Condition example that we'll take a look at involves an SQL SELECT statement with 2 conditions:

SELECT *
FROM suppliers
WHERE city = 'New York'
or city = 'Newark';

This SQL "OR" Condition example would return all suppliers that reside in either New York or Newark. Because the * is used in the SELECT statement, all fields from the suppliers table would appear in the result set.

SQL "OR" Condition - SQL SELECT Statement with 3 conditions example

The next example SQL OR Condition example takes a look at an SQL SELECT statement with 3 conditions. If any of these conditions is met, the record will be included in the result set.

SELECT supplier_id
FROM suppliers
WHERE name = 'IBM'
or name = 'Hewlett Packard'
or name = 'Gateway';

This SQL "OR" Condition example would return all supplier_id values where the supplier's name is either IBM, Hewlett Packard or Gateway.

SQL "OR" Condition - SQL INSERT Statement example

The SQL "OR" Condition can be used in the SQL INSERT statement.

For example:.

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE city = 'New York'
or city = 'Newark';

This SQL "OR" Condition example would insert into the suppliers table, all account_no and name records from the customers table that reside in either New York or Newark.

SQL "OR" Condition - SQL UPDATE Statement example

The SQL "OR" Condition can be used in the SQL UPDATE statement.

For example:.

UPDATE suppliers
SET supplier_name = 'HP'
WHERE supplier_name = 'IBM'
or supplier_name = 'Dell';

This SQL "OR" Condition example would update all supplier_name values in the suppliers table to HP where the supplier_name was either IBM or Dell.

SQL "OR" Condition - SQL DELETE Statement example

The SQL "OR" Condition can be used in the SQL DELETE statement.

For example:.

DELETE FROM suppliers
WHERE supplier_name = 'IBM'
or supplier_name = 'Dell';

This SQL "OR" Condition example would delete all suppliers from the suppliers table whose supplier_name was either IBM or Dell.

"AND" and "OR" Conditions in SQL

The SQL "AND" condition and SQL "OR" condition can be combined in a single SQL statement. It can be used in any valid SQL statement - SQL SELECT statement, SQL INSERT Statement, SQL UPDATE Statement, SQL DELETE Statement.

When combining these conditions, it is important to use brackets so that the database knows what order to evaluate each condition.

Example #1

The first example that we'll take a look at an example that combines the SQL "AND" and SQL "OR" conditions.

SELECT *
FROM suppliers
WHERE city = 'New York' and name = 'IBM')
or (city = 'Newark');

This SQL SELECT statement would return all suppliers that reside in New York whose name is IBM and all suppliers that reside in Newark. The brackets determine what order the AND and OR conditions are evaluated in.

Example #2

The next example takes a look at a more complex statement.

For example:

SELECT supplier_id
FROM suppliers
WHERE (name = 'IBM')
or (name = 'Hewlett Packard' and city = 'Atlantic City')
or (name = 'Gateway' and status = 'Active' and city = 'Burma');

This SQL SELECT statement would return all supplier_id values where the supplier's name is IBM or the name is Hewlett Packard and the city is Atlantic City or the name is Gateway, the status is Active, and the city is Burma.

PSEUDO COLUMN in SQL

A pseudo-column is an Oracle assigned value (pseudo-field) used in the same context as an Oracle Database column, but not stored on disk. SQL and PL/SQL recognizes the following SQL pseudocolumns, which return specific data items: SYSDATE, SYSTIMESTAMP, ROWID, ROWNUM, UID, USER, LEVEL, CURRVAL, NEXTVAL, ORA_ROWSCN, etc.


1) Pseudo columns behave like a table column, but it is not actually stored in a table.

2) Upon pseudo columns only select statements can be implemented. Insert, delete,update cannot be implemented.

Currval, (can be used in set clause of an update, values clause of an insert)

Nextval, (can be used in set clause of an update, values clause of an insert)

Level, Rowid, Rownum.


Download here PDF on Pseudo column and functions in SQL

SQL> SELECT sysdate, systimestamp FROM dual;
SYSDATE   SYSTIMESTAMP
--------- ----------------------------------------
13-DEC-07 13-DEC-07 10.02.31.956842 AM +02:00

SQL> select ename, ORA_ROWSCN, SCN_TO_TIMESTAMP(ORA_ROWSCN) from emp where empno=7369;
ENAME      ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------- ---------- ----------------------------------------------------------------
SMITH         2113048 20/12/2008 16:59:51.000

How to RENAME the TABLE name in ORACLE

There are two ways of renaming a table in Oracle:

Method 1: Simple

rename {old_table_name} to {new_table_name}

Example:

rename CUSTOMER to CUSTOMER_BACKUP

Method 2: Not so Complex

alter table {old_table_name} rename to {new_table_name};

Example:

alter table CUSTOMER rename to CUSTOMER_BACKUP;

The minimum version that supports table renaming is Oracle 8i. All the dependencies of the table will automatically updated. No need of updating them after wards.

Question on SQL part - 1

1. A user is setting up a join operation between tables EMP and DEPT. There are some employees in the EMP table that the user wants returned by the query, but theemployees are not assigned to departments yet. Which SELECT statement is most appropriate for this user?

A.      select e.empid, d.head from emp e, dept d;

B.      select e.empid, d.head from emp e, dept d where e.dept# = d.dept#;

C.      select e.empid, d.head from emp e, dept d where e.dept# = d.dept# (+);

D.     select e.empid, d.head from emp e, dept d where e.dept# (+) = d.dept#;

2. Developer ANJU executes the following statement: CREATE TABLE animals AS SELECT * from MASTER.ANIMALS; What is the effect of this statement?

A.      A table named ANIMALS will be created in the MASTER schema with the same data as the ANIMALS table owned by ANJU.

B.      A table named ANJU will be created in the ANIMALS schema with the same data as the ANIMALS table owned by MASTER.

C.      A table named ANIMALS will be created in the ANJU schema with the same data as the ANIMALS table owned by MASTER.

D.     A table named MASTER will be created in the ANIMALS schema with the same data as the ANJU table owned by ANIMALS.

3. User JANKO would like to insert a row into the EMPLOYEE table. The table has three columns: EMPID, LASTNAME, and SALARY. The user would like to enter data for EMPID 59694, LASTNAME Harris, but no salary. Which statement would work best?

A.      INSERT INTO employee VALUES (59694,'HARRIS', NULL);

B.      INSERT INTO employee VALUES (59694,'HARRIS');

C.      INSERT INTO employee (EMPID, LASTNAME, SALARY) VALUES (59694,'HARRIS');

D.     INSERT INTO employee (SELECT 59694 FROM 'HARRIS');

4. Which three of the following are valid database datatypes in Oracle? (Choose three.)

A.      CHAR

B.      VARCHAR2

C.      BOOLEAN

D.     NUMBER

5. Omitting the WHERE clause from a DELETE statement has which of the following effects?

A.      The delete statement will fail because there are no records to delete.

B.      The delete statement will prompt the user to enter criteria for the deletion

C.      The delete statement will fail because of syntax error.

D.     The delete statement will remove all records from the table.

6. Dropping a table has which of the following effects on a nonunique index created for the table?

A.      No effect.

B.      The index will be dropped.

C.      The index will be rendered invalid.

D.     The index will contain NULL values.

7. To increase the number of null-able columns for a table,

A.      Use the alter table statement.

B.      Ensure that all column values are NULL for all rows.

C.      First increase the size of adjacent column datatypes, then add the column.

D.    Add the column, populate the column, then add the NOT NULL constraint.

8. Which line of the following statement will produce an error?

A.      CREATE TABLE goods

B.      (good_no NUMBER,

C.      good_name VARCHAR2 check(good_name in (SELECT name FROM avail_goods)),

D.     CONSTRAINT pk_goods_01

E.      PRIMARY KEY (goodno));

F.      There are no errors in this statement.

9. MAXVALUE is a valid parameter for sequence creation.

A.      A. TRUE

B.      B. FALSE

10. Which function below can best be categorized as similar in function to an IF-THEN-ELSE statement?

A.      SQRT

B.      DECODE

C.      NEW_TIME

D.     ROWIDTOCHAR

Answers:

1. Option D
2. Option C
3. Option A
4. Option A, B and D
5. Option D
6. Option B
7. Option A
8. Option C
9. Option A
10. Option B

Question on SQL Part - 2

Question: 1

Which of the following statements contains an error?

A.     SELECT * FROM emp WHERE empid = 493945;

B.     SELECT empid FROM emp WHERE empid= 493945;

C.     SELECT empid FROM emp;

D.     SELECT empid WHERE empid = 56949 AND lastname = 'SMITH';

Answer: Option D

Reason: There is no FROM clause after the SELECT list in the query.

 Question: 2

Which of the following correctly describes how to specify a column alias? 

A.     Place the alias at the beginning of the statement to describe the table.

B.     Place the alias after each column, separated by white space, to describe the column.

C.     Place the alias after each column, separated by a comma, to describe the column.

D.     Place the alias at the end of the statement to describe the table.

Answer: Option B

Reason: Alias names for columns have to be specified just after the column in the SELECT list. It has to be separated from the column name with a white space.

Example: SELECT sysdate my_date FROM dual;

In the above example my_date field is the alias for the pseudo-column sysdate in the SELECT list.

Question: 3

The NVL function

A.     Assists in the distribution of output across multiple columns.

B.     Allows the user to specify alternate output for non-null column values.

C.     Allows the user to specify alternate output for null column values.

D.     Nullifies the value of the column output.

Answer: Option C

Reason: The NVL function is used to substitute NULL values with a meaningful value to be returned by the query.

Example: SELECT ename, NVL(last_salary,0)  FROM emp;

Question: 4

Output from a table called PLAYS with two columns, PLAY_NAME and AUTHOR, is shown below. Which of the following SQL statements produced it?

PLAY_TABLE
-------------------------------------
"Midsummer Night's Dream", SHAKESPEARE
"Waiting For Godot", BECKETT
"The Glass Menagerie", WILLIAMS

A.     SELECT play_name || author FROM plays;

B.     SELECT play_name, author FROM plays;

C.     SELECT play_name||', ' || author FROM plays;

D. SELECT play_name||', ' || author PLAY_TABLE FROM plays;

Answer: Option D

Reason: Though Option C might be confused with Option D for the correct answer, a closer evaluation of the output with both of the queries will reveal that Option D is the correct one. If you have not still got the gist of what made Option D the answer, check that the column header in the output is having PLAY_TABLE. As this field is not in the table, it could be only an alias for the column which have did the work. There is no column-alias for Option C, but it is present in Option D.

Question: 5

Issuing the DEFINE_EDITOR="emacs" will produce which outcome?

A.     The emacs editor will become the SQL*Plus default text editor.

B.     The emacs editor will start running immediately.

C.     The emacs editor will no longer be used by SQL*Plus as the default text editor.

D. The emacs editor will be deleted from the system.

Answer: Option A

Reason: This SQL *Plus command will set the value for the variable DEFINE_EDITOR. Later on if we are using the text editor to edit the queries typed previously, SQL *Plus uses the value set in this variable to identify what is the text editor which has to be used.

Question: 6

The user issues the following statement. What will be displayed if the EMPID selected is 60494?

SELECT DECODE(empid,38475, ‘Terminated’,60494, ‘LOA’, ‘ACTIVE’) FROM  emp;

A.     60494

B.     LOA

C.     Terminated

D.     ACTIVE

Answer: Option B

Reason: The DECODE function can evaluate multiple conditions at one stretch. The above DECODE function evaluates to the following IF sequence.

Example: 

IF empid = 38475 Then

Return ‘Terminated’;

ELSIF empid = 60494 Then

Return ‘LOA’;

ELSE

Return ‘ACTIVE’;

      END IF;

Question: 7

SELECT (TO_CHAR(NVL(SQRT(59483), ‘INVALID’)) FROM DUAL is a valid SQL statement.

A.     TRUE

B.     FALSE

Answer: Option B

Reason: There is an extra parenthesis before the TO_CHAR function to make the select statement syntactically incorrect.

Question: 8

The appropriate table to use when performing arithmetic calculations on values defined within the SELECT statement (not pulled from a table column) is

A.     EMP

B.     The table containing the column values

C.     DUAL

D.     An Oracle-defined table

Answer: Option C

Reason: DUAL is an Oracle pseudo-table which can be used either for arithmetic calculations or for selecting pseudo-columns like SYSDATE from this table.

Example: SELECT sysdate+10 FROM DUAL;

Question: 9

Which of the following is not a group function?

A.     avg( )

B.     sqrt( )

C.     sum( )

D.     max( )

Answer: Option B

Reason: sqrt() function is the only function among the list that accepts one value instead of a set of values. If we issue the sqrt() function against a set of records in a table, it will return square root for each of the records in the table. But if we issue the same command against avg(), sum(), max() functions it will return one row only based on the function logic.

Example: SELECT avg(sal) FROM emp;

SELECT sqrt(144) FROM dual;

SELECT sum(sal) FROM emp;

SELECT max(sal) FROM emp; 

Question: 10

The default character for specifying runtime variables in SELECT statements is

A.     Ampersand

B.     Ellipses

C.     Quotation marks

D.     Asterisk

Answer: Option A

Reason: & is the variable we can use inside SQL *Plus to accept values from user.

Example: INSERT INTO emp VALUES (&empno, ‘&ename’, &sal);

In the above INSERT statement, SQL *Plus will prompt each time for three variables viz., &empno, &ename and &sal. Then it swaps the user entered values with the &variables and inserts the same in the table.