Page 53 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Database Management Systems/Managing Database

Notes SELECT expression AS column name
Where
Expression: It refers to mathematical or string expression, which is specified on column names
and constants.
Column Name: It refers to the column’s new name in the resultant query. It can also include
aggregate functions/operators such as SUM, AVG, COUNT, MIN, MAX etc. It also allows the use
of standard ready to use functions such as sqrt, mod etc.

Querys

Query (a): Find the names of all the employees who are working for “operation” department.
Solution:
SELECT E.ename

FROM Employee E, Department D
WHERE E.DNo = D.DNo AND
D.Dname = “operation”. The answer is “David”
Query (b): Calculate the increment in the salary of the employees who are working on two
different projects carried out at the same location.
Solution:
SELECT E.ename, E.esal + 1000 As salary

FROM Employee, Department D , Department D
l 2
WHERE D .Dept_managerid = E.eid AND
1
D .Dlocation = D .Dlocation AND
1 2
D .PNO <> D .PNO
l 2
This will result in a new relation when ‘esaF coTumn is renamed as ‘salary’ by using AS
Result: Use of ‘AS’ clause
Ename Salary
David 000

Sam 000
With select command we can select specific columns by specifying the names of the columns. But
by using ‘*’ with select command all the columns can be selected at once (no need to mention the
names of all the columns).
Query (c): List all the information about the employees whose salary is greater than or equal
to 20000.
Solution:
SELECT *
FROM Employee E

WHERE E.esal > = 20000.

46 LOVELY PROFESSIONAL UNIVERSITY