SQL & PL/SQL
Covers all SQL & PL/SQL Tutorial along with Interview questions and Objective Questions . SQL Encyclopedia.
Thursday, September 5, 2013
Sunday, July 21, 2013
Friday, July 19, 2013
Sunday, December 23, 2012
Spooling in SQL
SPOOL
This will record the data when you spool on, upto when you say
spool off. By default it will give lst
as extension.
Syntax:
Spool on | off |
out | <file_name>.[Extension]
Ex:
SQL> spool on
SQL> select * from dept;
DEPTNO DNAME LOC
---------
-------------- ----------
10
ACCOUNTING NEW YORK
20
RESEARCH DALLAS
30
SALES CHICAGO
40
OPERATIONS BOSTON
SQL> spool off
SQL> ed on.lst
SQL> select * from dept;
DEPTNO DNAME LOC
---------
-------------- ----------
10
ACCOUNTING NEW YORK
20
RESEARCH DALLAS
30
SALES CHICAGO
40
OPERATIONS BOSTON
SQL> spool off
Saturday, November 10, 2012
"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.
Subscribe to:
Posts (Atom)