Software testing - Questions and Answers - SQL Interview Questions
Q. What does SQL stand for?
A. Structured Query Language
Q. What is a primary key?
A. Primary key : Each row of the data in a
table uniquely identified by a Primary Key
The column (columns) that has completely
unique data throughout the table is known as the primary key field.
Q. What is the main role of a primary key in
a table?
A. The main role of a primary key in a data
table is to maintain the internal integrity of a data table.
Q. What are foreign keys?
A. Foreign key field – is a field that
links one table to another table’s primary or foreign key.
Foreign Key : You can logically relate data
from multiple tables using Foreign Keys
Q. Can a table have more than one foreign key
defined?
A. A table can have any number of foreign
keys defined. It can have only one primary key defined.
Q. What is difference between UNIQUE and
PRIMARY KEY constraints?
A table can have only one PRIMARY KEY whereas
there can be any number of UNIQUE keys.
The columns that compose PK are automatically
define NOT NULL, whereas a column that compose a UNIQUE KEY can have null
values.
Q. Can a primary key contain more than one
columns?
Yes. Primary key created on more than one
column is called composite primary key.
Constraints
The Oracle Server uses constraints to
prevent invalid data entry into tables.
You can use constraints to do the following:
• Enforce rules on the data in a table
whenever a row is inserted, updated, or deleted from that
table. The constraint must be satisfied for
the operation to succeed.
• Prevent the deletion of a table if there
are dependencies from other tables
• Provide rules for Oracle tools, such as
Oracle Developer
DATA Integrity Constraints
Constraint Description
NOT NULL Specifies that the column cannot
contain a null value
UNIQUE Specifies a column or combination of
columns whose values must be
unique for all rows in the
table
PRIMARY KEY Uniquely identifies each row of
the table
FOREIGN KEY Establishes and enforces a
foreign key relationship between the
column and a column of the referenced table
CHECK Specifies a condition that must be true
Q What is an Index ?
An Index is an optional structure associated
with a table to have direct access to rows,which can be created to increase the
performance of data retrieval. Index can be created on one or more columns of a
table.
Indexes are automatically maintained and used
by ORACLE. Changes to table data are automatically incorporated into all
relevant indexes.
Q What is the Subquery ?
A Subquery is a query whose return values are
used in filtering conditions of the main query.
Q. What is correlated sub-query ?
A Correlated sub_query is a sub_query which
has reference to the main query.
Q. What is an Integrity Constraint ?
A Integrity constraint is a rule that
restricts values to a column in a table.
Q. What is Referential Integrity ?
A Maintaining data integrity through a set of
rules that restrict the values of one or more columns of the tables based on
the values of primary key or unique key of the referenced table.
Q. what is Case Function
Case facilitates conditional inquires by
doing the work of an if-then-else statement
Q. Decode function
Decode : facilitates conditional inquires by
doing the work of a case or if then else statement
Q. How you will avoid duplicating records in
a query?
A By using DISTINCT
Q. What is difference between Rename and
Alias?
Rename is a permanent name given to a table
or column whereas Alias is a temporary name given to a table or column which do
not exist once the SQL statement is executed.
Q. What is a view ?
A view is a virtual table based on one or
more tables.
Why Use views ?
To restrict data access
• To make complex queries easy
• To provide data
independence
• To present different views of the same data
Q. What are the advantages of Views ?
Advantages of Views
• Views restrict access to the data because
the view can display selective columns from the table.
• Views can be used to make simple queries to
retrieve the results of complicated queries. For
example, views can be used to query
information from multiple tables without the user knowing
how to write a join statement.
• Views provide data independence for ad hoc
users and application programs. One view can be used to retrieve data from
several tables.
• Views provide groups of users access to
data according to their particular criteria.
Provide an additional level of table
security, by restricting access to a predetermined set of rows and columns of a
table.
Hide data complexity.
Simplify commands for the user.
Present the data in a different perpecetive
from that of the base table.
Store complex queries.
Q. What are various privileges that a user
can grant to another user?
SELECT
CONNECT
RESOURCES
Q. What is schema?
A schema is collection of database objects of
a User.
Q. what is Table ?
A table is the basic unit of data storage in
an ORACLE database. The tables of a database hold all of the user accessible
data. Table data is stored in rows and columns.
Q. Do View contain Data?
Views do not contain or
store data.
Q. Can a View based on another View ?
Yes.
Q. What is a Sequence ?
A sequence generates a serial list of unique
numbers for numerical columns of a database's tables.
Q. What is a Synonym ?
A synonym is an alias for a table, view,
sequence or program unit.
There are two types of
Synonyms Private and Public.
A Private Synonyms can be accessed only by
the owner.
A Public synonyms can be accessed by any user
on the database.
Synonyms are used to : Mask the real name and
owner of an object.
Provide public access to an object
Provide location transparency for
tables,views or program units of a remote database.
Simplify the SQL statements for database
users.
Q. What is difference between TRUNCATE &
DELETE ?
TRUNCATE commits after deleting entire table
i.e., can not be rolled back. Database triggers do not fire on TRUNCATE
DELETE allows the filtered deletion. Deleted
records can be rolled back or committed.
Database triggers fire on DELETE.
Advantages of COMMIT and
ROLLBACK Statements
With COMMIT and ROLLBACK statements, you can:
• Ensure data consistency
• Preview data changes before making changes
permanent
• Group logically related operations
Q. Difference between SUBSTR and INSTR ?
INSTR (String1,String2(n,(m)),
INSTR returns the position of the mth
occurrence of the string 2 in
string1. The search begins from nth position
of string1.
SUBSTR (String1 n,m)
SUBSTR returns a character string of size m
in string1, starting from nth postion of string1.
Q. Explain UNION, MINUS, UNION ALL, INTERSECT
?
INTERSECT returns all distinct rows selected
by both queries.
MINUS - returns all distinct rows selected by
the first query but not by the second.
UNION - returns all distinct rows selected by
either query
UNION ALL - returns all rows selected by
either query, including all duplicates.
Q. What is ROWID ?
ROWID is a pseudo column attached to each row
of a table. It is 18 character long, blockno, rownumber are the components of
ROWID.
Q. What is the fastest way of accessing a row
in a table ?
Using ROWID.
Q. What is difference between CHAR and
VARCHAR2 ? , What is the maximum SIZE allowed for each type ?
CHAR pads blank spaces to
the maximum length. VARCHAR2 does not pad blank spaces. For CHAR it is 255 and
2000 for VARCHAR2.
Q. How many LONG columns are allowed in a
table ? Is it possible to use LONG columns in WHERE clause or ORDER BY ?
A Only one LONG columns is allowed. It is not
possible to use LONG column in WHERE or ORDER BY clause.
Q. What is Database Link ?
A database link is a named object that
describes a "path" from one database to another.
Private Database Link, Public Database Link
& Network Database Link.
Private database link is created on behalf of
a specific user. A private database link can be used only when the owner of the
link specifies a global object name in a SQL statement or in the definition of
the owner's views or procedures.
Public database link is created for the
special user group PUBLIC. A public database link can be used when any user in
the associated database specifies a global object name in a SQL statement or
object definition.
Network database link is created and managed
by a network domain service. A network database link can be used when any user
of any database in the network specifies a global object name in a SQL
statement or object definition.
Q. Which is more faster - IN or EXISTS?
EXISTS is more faster than IN because EXISTS
returns a Boolean value whereas IN returns a value.
Q. What is a join?
A. Join is a process of retrieve pieces of
data from different sets (tables) and returns them to the user or program as
one â€oejoinedâ_€ collection of data.
Types of Joins
• Equijoins
• Non-equijoins
• Outer joins
• Self joins
• Cross joins
• Natural joins
• Full or outer joins
Equijoins
To determine an employee’s department name,
you compare the value in the DEPARTMENT_ID
column in the EMPLOYEES table with the
DEPARTMENT_ID values in the DEPARTMENTS table. The relationship between the
EMPLOYEES and DEPARTMENTS tables is an equijoin—that is, values in the
DEPARTMENT_ID column
on both tables must be
equal. Frequently, this type of join involves primary and foreign key
complements.
Note: Equijoins are also called simple
joins or inner joins.
Non-Equijoins
A non-equijoin is a join condition containing
something other than an equality operator.
The relationship between the EMPLOYEES table
and the JOB_GRADES table has an
example of a non-equijoin. A relationship
between the two tables is that the SALARY
column in the EMPLOYEES table must be between
the values in the LOWEST_SALARY
and HIGHEST_SALARY columns of the JOB_GRADES
table. The relationship is
obtained using an operator other than equals
(=).
SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary
BETWEEN j.lowest_sal AND j.highest_sal;
Outer join : to also see rows that do
not meet the join condition
Returning Records with No
Direct Match with Outer Joins
If a row does not satisfy a join condition,
the row will not appear in the query result. For example, in the equijoin
condition of EMPLOYEES and DEPARTMENTS tables, employee Grant does not appear
because there is no department ID recorded for her in the EMPLOYEES table.
Instead of seeing 20 employees in the result set, you see 19 records.
SELECT e.last_name, e.department_id,
d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;
SELECT e.last_name, e.department_id,
d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id ;
Self Join :
Joining a Table to Itself
Sometimes you need to join a table to itself.
To find the name of each employee’s manager, you need
to join the EMPLOYEES table to itself, or
perform a self join. For example, to find the name of
Whalen’s manager, you need to:
• Find Whalen in the EMPLOYEES table by
looking at the LAST_NAME column.
• Find the manager number for Whalen by
looking at the MANAGER_ID column. Whalen’s
manager number is 101.
• Find the name of the manager with
EMPLOYEE_ID 101 by looking at the LAST_NAME
column. Kochhar’s employee number is 101, so
Kochhar is Whalen’s manager.
In this process, you look in the table twice.
The first time you look in the table to find Whalen in the
LAST_NAME column and
MANAGER_ID value of 101. The second time you look in the
EMPLOYEE_ID column to find 101 and the
LAST_NAME column to find Kochhar.
SELECT worker.last_name || ’ works for ’
|| manager.last_name
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id
;
Left Outer Join :
This query retrieves all rows in the
EMPLOYEES table, which is the left table even if there is no
match in the DEPARTMENTS table.
This query was completed in earlier releases
as follows:
SELECT e.last_name, e.department_id,
d.department_name
FROM employees e, departments d
WHERE d.department_id (+) = e.department_id;
SELECT e.last_name, e.department_id,
d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;
Right Outer Join :
SELECT e.last_name, e.department_id,
d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;
Example of RIGHT OUTER
JOIN
This query retrieves all rows in the
DEPARTMENTS table, which is the right table even if there is no match in the
EMPLOYEES table.
This query was completed in earlier releases
as follows:
SELECT e.last_name, e.department_id,
d.department_name
FROM employees e, departments d
WHERE d.department_id = e.department_id (+);
Full outer join
SELECT e.last_name, e.department_id,
d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON (e.department_id = d.department_id) ;
Example of FULL OUTER JOIN
This query retrieves all rows in the
EMPLOYEES table, even if there is no match in the
DEPARTMENTS table. It also retrieves all rows
in the DEPARTMENTS table, even if there is no match in the EMPLOYEES table.
Cartesian Products
A Cartesian product results in all
combinations of rows displayed. This is done by either omitting the WHERE
clause or specifying the CROSS JOIN clause.
Table Aliases
• Table aliases speed up database access.
• Table aliases can help to keep SQL code
smaller, by conserving memory.
Q. What kinds of joins do you know? Give
examples.
A. We have self join, outer joint (LEFT,
RIGHT), , cross-join ( Cartesian product n*m rows returned)
Exp:
outer joint
SELECT Employee.Name, Department. DeptName
FROM Employee, Department
WHERE Employee.Employee_ID =
Department.Employee_ID;
cross-join
SELECT * FROM table1, table2;
self join
SELECT e1.name | |’ ‘ | | e2.ename FROM
emp e1, emp e2 WHERE e1. emp_no = e2.emp_no;
The following summarizes the result of the
join operations:
_ The result of T1 INNER JOIN T2 consists of
their paired rows where the
join-condition is true.
_ The result of T1 LEFT OUTER JOIN T2
consists of their paired rows where
the join-condition is true and, for each
unpaired row of T1, the
concatenation of that row with the null row
of T2. All columns derived
from T2 allow null values.
_ The result of T1 RIGHT OUTER JOIN T2
consists of their paired rows
where the join-condition is true and, for
each unpaired row of T2, the
concatenation of that row with the null row
of T1. All columns derived
from T1 allow null values.
_ The result of T1 FULL OUTER JOIN T2
consists of their paired rows and,
for each unpaired row of T2, the
concatenation of that row with the null
row of T1 and, for each unpaired row of T1,
the concatenation of that row
with the null row of T2. All columns derived
from T1 and T2 allow null
values.
Q. How do you add record to a table?
A. INSERT into table_name VALUES (‘ALEX’
, 33 , ‘M’);
Q. How do you add a column to a table?
A. ALTER TABLE Department ADD (AGE, NUMBER);
Q. How do you change value of the field?
A. UPDATE EMP_table set number = 200 where
item_munber = ‘CD’;
update name_table set status = 'enable' where
phone = '4161112222';
update SERVICE_table set REQUEST_DATE =
to_date ('2006-03-04 09:29', 'yyyy-mm-dd hh24:MI') where phone = '4161112222';
Q. What does COMMIT do?
A. Saving all changes made by DML statements
Q. List all the possible values that can be
stored in a BOOLEAN data field.
A. There are only two values that can be
stored in a BOOLEAN data field:
-1(true) and 0(false).
Q. What is the highest value that can be
stored in a BYTE data field?
A. The highest value that can be stored in a
BYTE field is 255. or from -128
to 127. Byte is a set of Bits that represent
a single character.
Usually there are 8 Bits in a Byte, sometimes
more, depending on how
the measurement is being made. Each Char
requires one byte of memory
and can have a value from 0 to 255 (or 0 to
11111111 in binary).
Q. How many places to the right of the
decimal can be stored in a
CURRENCY data field?
A. The CURRENCY data type can store up to
four places to the right of the
decimal. Any data beyond the fourth place
will be truncated by Visual
Basic without reporting an error.
Q. What is a stored procedure?
A. A procedure is a group of PL/SQL statements
that can be called by
a name. Procedures do not return values they
perform tasks.
Q. Describe how NULLs work in SQL?
A. The NULL is how SQL handles missing
values.
Arifthmetic operation with NULL in SQL will
return a NULL.
Q. What is Normalization?
A. The process of table design is called
normalization.
Q. What is referential integrity constraints?
A. Referential integrity constraints are
rules
that are partnof the table in a database
schema.
Q. What is Trigger?
A. Trigger will execute a block of procedural
code against the database when a table event occurs. A2. A trigger defines a
set of actions that are performed in response
to an insert, update, or delete operation on
a specified table. When such an SQL operation is executed, in this case the
trigger has been activated.
Q. Which of the following WHERE clauses will
return only rows
that have a NULL in the PerDiemExpenses
column?
A. WHERE PerDiemExpenses <>
B. WHERE PerDiemExpenses IS NULL
C. WHERE PerDiemExpenses = NULL
D. WHERE PerDiemExpenses NOT IN (*)
A. B is correct � When searching for a NULL
value in a column, you must
use the keyword IS. No quotes are required
around the keyword NULL.
22. Q. You issue the
following query:SELECT FirstName FROM
StaffListWHERE FirstName LIKE'_A%'Which names
would be
returned by this query? Choose all that
apply.
A. Allen
B. CLARK
C. JACKSON
D. David
A. C is correct � Two wildcards are used
with the LIKE operator.
The underscore (_) stands for any one
character of any
case, and the percent sign (%) stands for any
number of
characters of any case including none.
Because this string
starts with an underscore rather than a
percent sign, it won't
return Allen or Clark because they represent
zero and two
characters before the "A". If the
LIKE string had been "%A%",
both of these values would have been
returned.
David was not returned because all non-wild
card characters
are case sensitive. Therefore, only strings
with an uppercase "A" as their
second letter are returned
Q. Write a SQL SELECT query that only returns
each city only once from Students table?
Do you need to order this list with an ORDER
BY clause?
A. SELECT DISTINCT City FROM Students;
The Distinct keyword automatically sorts all
data
in ascending order. However, if you want the
data
sorted in descending order, you have to use
an ORDER BY clause
Q. Write a SQL SELECT sample of the
concatenation operator.
A. SELECT LastName ||',' || FirstName, City
FROM Students;
Q. How to rename column in the SQL SELECT
query?
A. SELECT LastName ||',' || FirstName
AS "Student Name", City AS
"Home City"
"FROM StudentsORDER BY "Student
Name"
Q. Write SQL SELECT example how you limiting
the rows returned with a WHERE clause.
A. SELECT InstructorID, Salary FROM
Instructors
WHERE Salary > 5400 AND Salary < 6600;
Q. Write SQL SELECT query that returns the
first and
last name of each instructor, the Salary,
and gives each of them a number.
A. SELECT FirstName,
LastName, Salary, ROWNUM FROM Instructors;
Q. Which of the following functions can be
used only with numeric values?
(Choose all that apply.)
A. AVG
B. MIN
C. LENGTH
D. SUM
E. ROUND
A. A and D � Only A and D are correct. The
MIN function
works with any character, numeric, or date
datatype.
The LENGTH function is a character function
that returns
the number of letters in a character value.
The ROUND
function works with both numeric and date
values.
Q. Which function do you use to remove all
padded characters
to the right of a character value in a column
with a char datatype?
A. RTRIM
B. RPAD
C. TRIM
A. C � The TRIM function is used to remove
padded spaces.
LTRIM and RTRIM functions were included in
earlier versions
of Oracle, but Oracle 8i has replaced them
with a single
TRIM function
Q. Which statement do you use to eliminate
padded spaces
between the month and day values in a
function TO_CHAR(SYSDATE,'Month, DD, YYYY') ?
A. To remove padded spaces, you use the "fm"
prefix before the date element that contains
the spaces.
TO_CHAR(SYSDATE,'fmMonth DD, YYYY')
Q. Is the WHERE clause must appear always
before the GROUP BY clause in SQL SELECT ?
A. Yes.
The proper order for SQL SELECT
clauses is: SELECT, FROM, WHERE, GROUP BY,
HAVING, ORDER BY.
Only the SELECT and FROM clause are
mandatory.
Q. How Oracle executes a statement with
nested subqueries?
A. When Oracle executes a statement with
nested subqueries,
it always executes the innermost query first.
This query passes its
results to the next query and so on until it
reaches the outermost query.
It is the outermost query that returns a
result set.
Q. Which operator do you
use to return all of the rows
from one query except rows are returned in a
second query?
A. You use the MINUS operator to return all
rows from one query except
where duplicate rows are found in a second
query. The UNION operator
returns all rows from both queries minus
duplicates. The UNION ALL operator
returns all rows from both queries including
duplicates.
The INTERSECT operator returns only those
rows that exist in both queries.
Q. How you will create a column alias?
(Oracle 8i)
A. The AS keyword is optional when specifying
a column alias.
You must enclose the column alias in double
quotes when the alias
contains a space or lowercase letters. If you
specify an alias in l
owercase letters without double quotes, the
alias will appear in uppercase.
Q. Which of the following statements are Data
Manipulation Language commands?
A. INSERT
B. UPDATE
C. GRANT
D. TRUNCATE
E. CREATE
A. A and B � The INSERT and UPDATE
statements are
Data Manipulation Language (DML) commands.
GRANT is a Data Control Language (DCL)
command.
TRUNCATE and CREATE are Data Definition
Language (DDL) commands
Q. What is Oracle locking?
A. Oracle uses locking mechanisms to protect
data from
being destroyed by concurrent transactions.
Q. What Oracle lock modes do you know?
A. Oracle has two lock modes: shared or
exclusive.
Shared locks are set on database resources so
that many transactions
can access the resource.
Exclusive locks are set on resources that
ensure
one transaction has exclusive access to the
database resource
Q. What is query optimization?
A. Query optimization is the part of the
query
process in which the database system compares
different query strategies and chooses the
one with
the least expected cost
Q. What are the main components of Database
management systems software.
A. The database management system software
includes
components for storage management,
concurrency control, transaction
processing, database manipulation interface,
database definition interface,
and database control interface.
Q. What are the main
attributes of database management system?
A. A database management system is composed
of five elements: computer hardware, software, data, people (users), and
operations procedures.
Q. What is transaction?
A. A transaction is a collection of
applications
code and database manipulation code bound
into an indivisible unit of execution.
it consists from:
BEGIN-TRANSACTION Name
Code
END TRANSACTION Name
Q. What databases do you know?
Informix
DB2
SQL
Oracle
Q. Explain SQL SELECT example:
select j.FILE_NUM
from DB_name.job j, DB_name.address a
where j.JOB_TYPE ='C'
AND j.COMPANY_NAME = 'TEST6'
AND j.OFFICE_ID = '101'
AND j.ACTIVE_IND = 'Y'
AND a.ADDRESS_STATUS_ID = 'H'
AND a.OFFICE_ID = '101'
AND a.FILE_NUM = j.FILE_NUM order by
j.FILE_NUM;
Answer: j and a aliases for table names. this
is outer joint select statament from two tables.
Q. Describe some Conversion Functions that
you know
A. TO_CHAR converts a number / date to a
string.
TO_DATE converts a string (representing a
date) to a date.
TO_NUMBER converts a character string
containing digits to a numeric data type, it accepts one parameter which is a
column value or a string literal
Q. What does DML stand for?
A. DML is Data Manipulation Language
statements. (SELECT)
Q. What does DDL stand for?
A. DDL is Data Definition Language
statements. (CREATE)
Q. What does DCL stand for?
A. DCL is Data Control Language statements.
(COMMIT)
Q: Describe SQL comments.
A. SQL comments are introduced by two
consecutive hyphens
(--) and ended by the end of the line.
Q. In what sequence SQL statement are
processed?
A. The clauses of the subselect are processed
in the following sequence (DB2):
1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause
7. FETCH FIRST clause
Q. Describe TO_DATE function.
A. The TO_DATE function returns a timestamp
from a character string
that has been interpreted using a character
template.
TO_DATE is a synonym for TIMESTAMP_FORMAT.
Q. What is a pseudo column. Give some
examples?
It is a column that is not an actual column
in the table.
Eaxmple USER, UID, SYSDATE, ROWNUM, ROWID,
NULL, AND LEVEL.
Q. Suppose a customer table is having
different columns like customer no, payments.
What will be the query to select top three
max payments?
SELECT customer_no, payments from customer C1
WHERE 3<=(SELECT COUNT(*) from customer C2
WHERE C1.payment <= C2.payment)
Q. Find out nth highest salary from emp table
SELECT DISTINCT (a.sal) FROM EMP A
WHERE &N = (SELECT COUNT (DISTINCT
(b.sal)) FROM EMP B
WHERE a.sal<=b.sal);
Q. What are the difference between DDL, DML
and DCL commands?
DDL is Data Definition Language statements.
Some examples:
CREATE - to create objects in the database
ALTER - alters the structure of the database
DROP - delete objects from the database
TRUNCATE - remove all records from a table,
including all spaces allocated for
the records are removed
COMMENT - add comments to the data dictionary
GRANT - gives user's access privileges to
database
REVOKE - withdraw access privileges given
with the GRANT command
DML is Data Manipulation Language statements.
Some examples:
SELECT - retrieve data from the a database
INSERT - insert data into a table
UPDATE - updates existing
data within a table
DELETE - deletes all records from a table,
the space for the records remain
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency
DCL is Data Control Language statements. Some
examples:
COMMIT - save work done
SAVEPOINT - identify a point in a transaction
to which you can later roll back
ROLLBACK - restore database to original since
the last COMMIT
SET TRANSACTION - Change transaction options
like what rollback segment to use
Can we drop a column from a table?
yes. ALTER TABLE table_name DROP COLUMN
column_name;
Q. Describe some Group Functions that you
know
A. 1) The COUNT function tells you how many
rows were in the result set.
SELECT COUNT(*) FROM TESTING.QA
2) The AVG function tells you the average
value of a numeric column.
SELECT MAX(SALARY) FROM TESTING.QA
3) The MAX and MIN functions tell you the
maximum and minimum value of a numeric column.
SELECT MIN(SALARY) FROM TESTING.QA
4) The SUM function tells you the sum value
of a numeric column.
SELECT SUM(SALARY) FROM TESTING.QA
Group functions: Group functions operate on
sets of rows to give one result per group
Count function : COUNT(*) returns the
number of rows in a table.
SELECT COUNT(*)FROM employees
WHERE department_id = 50;
Having clause
If you restrict rows based on the result of a
group function, you must have a GROUP BY clause as well as the HAVING clause.
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING
MAX(salary)>10000 ;