| Unit-I | Unit-II | Unit-III | Unit-IV | Unit-V | |
| Part-I | Part-II | Part-I | Part-I | Part-I | Total |
| Part-III | Part-IV | Part-II | Part-II | Part-II | |
| Part-V | Part-III | ||||
3. Explain Select command with examples?
SELECT Statement
This statement is used for
retrieving information from the databases. It can be coupled with many clauses.
Let us discuss these clauses in more detail:
1. Using Arithmetic
operator
Example :
SELECT ENAME, ESAL, ESAL+300
FROM EMP;
2. Operator Precedence
The basic operators used in
SQL are * / + -
Operators of the same
priority are evaluated From Left to right
Parentheses are used to
force prioritized evaluation.
Example :
SELECT ENAME, ESAL, 12*ESAL+100
FROM EMP;
SELECT ENAME, ESAL, 12*(ESAL+100)
FROM EMP;
3. Using Column aliases
Example :
To print column names as
NAME and ANNUAL SALARY
SELECT ENAME “NAME”, ESAL*12
“ANNUAL SALARY” FROM EMP;
4. Concatenation operator
Example :
Printing name and job as one
string as column name employees:
SELECT ENAME||JOB
“EMPLOYEES”
FROM EMP;
5. Using Literal
Character String
Example :
To print <name> IS A
<job> as one string with column name employee
SELECT ENAME || ‘ IS A ’ ||
JOB AS “EMPLOYEE” FROM EMP;
6. To eliminate duplicate
rows (distinct operator)
Example :
SELECT DISTINCT DEPT_NO
FROM EMP;
7. Special comparison
operator used in where Clause
a. between. …and…It
gives range between two values (inclusive)
Example :
SELECT ENAME, ESAL FROM EMP WHERE ESAL BETWEEN 10000 AND 15000;
b. In (list): match
any of a list of values
Example :
SELECT EMPID, ENAME, ESAL
FROM EMP WHERE EMPID IN (101,
105, 109);
101, 105, and 109 are
Employee numbers
c. Like: match a
character pattern
• Like operator is used only
with char and Varchar2 to match a pattern
• % Denotes zero or many
characters
• _ Denotes one character
• Combination of % and_can
also be used
Example :
(I) List the names of
employees whose name starts with ‘s’
SELECT ENAME FROM EMP
WHERE ENAME LIKE ‘S%’;
(II) List the ename ending
with ‘s’
SELECT ENAME FROM EMP
WHERE ENAME LIKE ‘%S’;
(III) List ename having I as
a second character
SELECT ENAME FROM EMP
WHERE ENAME LIKE ‘_I%’;
d. Is null operator
Example :
to find employee whose
manage-id is not specified
SELECT ENAME, DEPT_NO FROM
EMP WHERE DEPT_NO IS NULL;
Example: To print those records of
salesman or president who are having salary
above 15000/-
SELECT ENAME, JOB, SAL FROM
EMP WHERE (JOB = ‘SALESMAN’ OR JOB =
‘PRESIDENT’) AND SAL>15000;
4.Explain Order By Clause With Example?
Order by clause
• It is used in the last
portion of select statement
• By using this rows can be
sorted
• By default it takes
ascending order
• DESC: is used for sorting
in descending order
• Sorting by column which is
not in select list is possible
• Sorting by column Alias
Example :
SELECT EMPNO, ENAME, eSAL*12
“ANNUAL” FROM EMP ORDER BY ANNUAL;
Example : Sorting by multiple columns;
ascending order on department number and descending order of salary in each
department.
SELECT ENAME, DEPT_NO, ESAL FROM EMP ORDER BY DEPT_NO, ESAL DESC;
5.Explain various Aggregate functions?
Aggregate functions
The ISO standard defines five aggregate functions:
•
COUNT – returns the number of values in a specified column
•
SUM – returns the sum of the values in a specified column
•
AVG – returns the average of the values in a specified column
•
MIN – returns the smallest value in a specified column
•
MAX – returns the largest value in a specified column
Example : Find the total number of
employees.
SELECT COUNT(*) FROM EMP;
Example : Find the minimum, maximum
and average salaries of employees of department D1.
SELECT MIN(ESAL), MAX(ESAL),
AVG(ESAL) FROM EMP WHERE DEPT_NO = ‘D1’ ;
6. Explain Group By clauses with example?
• It is used to group
database tuples on the basis of certain common attribute value such as
employees of a department.
• WHERE clause still can be
used, if needed.
Example: Find department number and
Number of Employees working in that department.
SELECT DEPT_NO, COUNT(EMPID)
FROM EMP GROUP BY DEPT_NO;
Please note that while using
group by and aggregate functions the only attributes that can be put in select
clause are the aggregated functions and the attributes that have been used for
grouping the information. For example, in the example 20, we cannot put ENAME
attribute in the SELECT clause as it will not have a distinct value for the
group. Please note the group here is created on DEPT_NO.
Having clause
• This clause is used for
creating conditions on grouped information.
Example : Find department number and
maximum salary of those departments where maximum salary is more than Rs
20000/-.
SELECT DEPT_NO, MAX(ESAL)
FROM EMP
GROUP BY DEPT_NO
HAVING MAX(ESAL) > 20000;
7.What is View Explain with example?
Views
·
View is a
virtual table that contains rows and columns, just like a real table.
·
It is used to
hide complexity of query from user.
·
A virtual
table does not exist physically, it is created by a SQL statement .
·
A view is defined as a query on one or more
base tables or views.
·
The DBMS stores the definition of the
view in the database.
The
format of the CREATE VIEW statement is:
CREATE VIEW ViewName AS select statement [WITH
CHECK OPTION]
The
select statement is known as the defining query.
If
WITH CHECK OPTION is specified, SQL ensures that if a row fails to satisfy the
WHERE clause of the defining query of the view, it is not added to the
underlying base table of the view .
EXAMPLE
Create
a horizontal view
Create
a view so that the manager at can see the details only for Employees who work
in his department.
A
horizontal view restricts a user’s access to selected rows of one or more
tables.
CREATE VIEW Manager3Emp_View
AS SELECT *
FROM Employee
WHERE Dept_no
= 1512;
This
creates a view called Manager3Emp_View with the same column names as the Employee
table but containing only those rows where the department number is 1512.
SELECT * FROM Manager3Emp_View;
Removing
a View (DROP VIEW)
A
view is removed from the database with the DROP VIEW statement:
DROP VIEW ViewName [RESTRICT | CASCADE]
DROP
VIEW causes the definition of the view to be deleted from the database.
•
If CASCADE is specified, DROP VIEW deletes all related dependent objects.
•
If RESTRICT is specified and there are any other objects that depend for their
existence being dropped, the command is rejected. The default setting is
RESTRICT.
For example, we could remove the
Manager3Emp_View using the following
statement:
DROP VIEW Manager3Emp_View;
8. Explain Joins with example?
Four types of join
operations are described below:
(1) Equi Join: A join
in which the joining condition is based on equality between values in the
common columns. Common columns appear (redundantly) in the result table.
Consider the following relations:
• customer (custid,
custname, ………..) and
• order (custid,
ordered,………………..)
Example : What are the names of all
customers who have placed orders?
SELECT CUSTOMER.CUSTOID, ORDER.CUSTOID, CUSTONAME, ORDERID FROM CUSTOMER, ORDER
WHERE CUSTOMER.CUSTOID=ORDER.CUSTOID;
(2) Natural Join: It is the same like
Equi join except one of the duplicate columns is eliminated in the result
table. The natural join is the most commonly used form of join operation.
Example :
SELECT CUSTOMER.CUTOID, CUSTONAME, ORDERID FROM CUSTOMER, ORDER WHERE
CUSTOMER.CUSTOID=ORDER.CUSTOID;
(3) Outer Join: The use of Outer Join
is that it even joins those tuples that do not have matching values in common
columns are also included in the result table. Outer join places null values in
columns where there is not a match between tables. A condition involving an
outer join is that it cannot use the IN operator or cannot be linked to another
condition by the OR operator.
Example : The following is an example
of left outer join (which only considers the non-matching tuples of table on
the left side of the join expression).
SELECT CUSTOMER.CUTOID, CUSTONAME, ORDERID FROM CUSTOMER LEFT OUTER
JOIN ORDER WHERE CUSTOMER.CUSTOID = ORDER.CUSTOID;
Output: The following result assumes a CUSTID in
CUSTOMER table who have not issued any order so far.
The other types of outer join are the Right
outer join or complete outer join.
Example : Find the name of each
employee’s manager name.
SELECT WORKER.ENAME || ‘WORK FOR’ || MANAGER.ENAME FROM EMP WORKER, EMP MANAGER WHERE WORKER.MGR=MANAGER.EMPNO;
Output:
|
Nirmal works for Boss |
|
Kailash works for Boss |
|
Veena works for Nirmal |
No comments:
Post a Comment