M E S College of Engineering, Pune

Department of Electronics & Telecommunication Engineering

Class: TE
Subject: Database Management
Oral Questions

SQL Questions

1) What is SQL?

SQL stands for the Structured Query Language. It is the standard language used to maintain the
relational database and perform many different data manipulation operations on the data. SQL was
initially invented in 1970. It is a database language used for database creation, deletion, fetching and
modifying rows, etc. sometimes, it is pronounced as 'sequel.' We can also use it to handle organized
data comprised of entities (variables) and relations between different entities of the data.

2) What are the usages of SQL?

SQL is responsible for maintaining the relational data and the data structures present in the database.
Some of the common usages are given below:

o To execute queries against a database

o To retrieve data from a database
o To inserts records in a database
o To updates records in a database
o To delete records from a database
o To create new databases
o To create new tables in a database
o To create views in a database
o To perform complex operations on the database.

3) Does SQL support programming language features?

SQL refers to the Standard Query Language. Therefore, it is true that SQL is a language but does not
actually support the programming language. It is a common language that doesn't have a loop,
conditional statements, and logical operations. It cannot be used for anything other than data
manipulation. It is a command language to perform database operations. The primary purpose of SQL is
to retrieve, manipulate, update, delete, and perform complex operations like joins on the data present in
the database.
4) What are the subsets of SQL?

The following are the four significant subsets of the SQL:

o Data definition language (DDL): It defines the data structure that consists of commands like
CREATE, ALTER, DROP, etc.
o Data manipulation language (DML): It is used to manipulate existing data in the database.
The commands in this category are SELECT, UPDATE, INSERT, etc.
o Data control language (DCL): It controls access to the data stored in the database. The
commands in this category include GRANT and REVOKE.
o Transaction Control Language (TCL): It is used to deal with the transaction operations in the
database. The commands in this category are COMMIT, ROLLBACK, SET TRANSACTION,
SAVEPOINT, etc.

5) What is the purpose of DDL Language?

DDL stands for Data definition language. It is the subset of a database that defines the data structure of
the database when the database is created. For example, we can use the DDL commands to add,
remove, or modify tables. It consists of the following commands: CREATE, ALTER and DELETE
database objects such as schema, tables, indexes, view, sequence, etc.

Example

1. CREATE TABLE Students

2. (
3. Roll_no INT,
4. Name VARCHAR(45),
5. Branch VARCHAR(30),
6. );

6) What is the purpose of DML Language?

Data manipulation language makes the user able to retrieve and manipulate data in a relational database.
The DML commands can only perform read-only operations on data. We can perform the following
operations using DDL language:

o Insert data into the database through the INSERT command.

o Retrieve data from the database through the SELECT command.
o Update data in the database through the UPDATE command.
o Delete data from the database through the DELETE command.

Example

1. INSERT INTO Student VALUES (111, 'George', 'Computer Science')

7) What is the purpose of DCL Language?

Data control language allows users to control access and permission management to the database. It is
the subset of a database, which decides that what part of the database should be accessed by which user
at what point of time. It includes two commands, GRANT and REVOKE.

GRANT: It enables system administrators to assign privileges and roles to the specific user accounts to
perform specific tasks on the database.

REVOKE: It enables system administrators to revoke privileges and roles from the user accounts so
that they cannot use the previously assigned permission on the database.

Example

1. GRANT * ON mydb.Student TO javatpoint@localhsot;

8) What are tables and fields in the database?

A table is a set of organized data in the form of rows and columns. It enables users to store and display
records in the structure format. It is similar to worksheets in the spreadsheet application. Here rows
refer to the tuples, representing the simple data item, and columns are the attribute of the data items
present in a particular row. Columns can categorize as vertical, and Rows are horizontal.

Fields are the components to provide the structure for the table. It stores the same category of data in the
same data type. A table contains a fixed number of columns but can have any number of rows known as
the record. It is also called a column in the table of the database. It represents the attribute or
characteristics of the entity in the record.

Example

Table: Student

Field: Stud_rollno, Stud_name, Date of Birth, Branch, etc.

9) What is a primary key?

A primary key is a field or the combination of fields that uniquely identify each record in the table. It is
one of a special kind of unique key. If the column contains a primary key, it cannot be null or empty. A
table can have duplicate columns, but it cannot have more than one primary key. It always stores unique
values into a column. For example, the ROLL Number can be treated as the primary key for a student
in the university or college.

We can define a primary key into a student table as follows:

1. CREATE TABLE Student (
2. roll_number INT PRIMARY KEY,
3. name VARCHAR(45),
4. );

10) What is a foreign key?

The foreign key is used to link one or more tables together. It is also known as the referencing key. A
foreign key is specified as a key that is related to the primary key of another table. It means a foreign
key field in one table refers to the primary key field of the other table. It identifies each row of another
table uniquely that maintains the referential integrity. The primary key-foreign key relationship is a very
crucial relationship as it maintains the ACID properties of the database sometimes. It also prevents
actions that would destroy links between the child and parent tables.

We can define a foreign key into a table as follows:

1. CONSTRAINT constraint_name]
2. FOREIGN KEY [foreign_key_name] (col_name, ...)
3. REFERENCES parent_tbl_name (col_name,...)

11) What is a unique key?

A unique key is a single or combination of fields that ensure all values stores in the column will be
unique. It means a column cannot stores duplicate values. This key provides uniqueness for the column
or set of columns. For example, the email addresses and roll numbers of student's tables should be
unique. It can accept a null value but only one null value per column. It ensures the integrity of the
column or group of columns to store different values into a table.

We can define a foreign key into a table as follows:

1. CREATE TABLE table_name(

2. col1 datatype,
3. col2 datatype UNIQUE,
4. ...
5. );

12) What is the difference between a primary key and a unique key?

The primary key and unique key both are essential constraints of the SQL. The main difference among
them is that the primary key identifies each record in the table. In contrast, the unique key prevents
duplicate entries in a column except for a NULL value. The following comparison chart explains it
more clearly:
Primary Key Unique Key

The primary key act as a unique The unique key is also a unique identifier for records
identifier for each record in the table. when the primary key is not present in the table.

We cannot store NULL values in the We can store NULL value in the unique key column,
primary key column. but only one NULL is allowed.

We cannot change or delete the We can modify the unique key column values.
primary key column values.

13) What are the different types of SQL operators?

Operators are the special keywords or special characters reserved for performing particular operations.
They are also used in SQL queries. We can primarily use these operators within the WHERE clause of
SQL commands. It's a part of the command to filters data based on the specified condition. The SQL
operators can be categorized into the following types:

o Arithmetic operators: These operators are used to perform mathematical operations on

numerical data. The categories of this operators are addition (+), subtraction (-), multiplication
(*), division (/), remainder/modulus (%), etc.
o Logical operators: These operators evaluate the expressions and return their results in True or
False. This operator includes ALL, AND, ANY, ISNULL, EXISTS, BETWEEN, IN, LIKE,
NOT, OR, UNIQUE.
o Comparison operators: These operators are used to perform comparisons of two values and
check whether they are the same or not. It includes equal to (=), not equal to (!= or <>), less than
(<), greater than (>), less than or equal to (<=), greater than or equal to (>=), not less than (!<),
not greater than (!>), etc.
o Bitwise operators: It is used to do bit manipulations between two expressions of integer type. It
first performs conversion of integers into binary bits and then applied operators such as AND (&
symbol), OR (|, ^), NOT (~), etc.
o Compound operators: These operators perform operations on a variable before setting the
variable's result to the operation's result. It includes Add equals (+=), subtract equals (-=),
multiply equals (*=), divide equals (/=), modulo equals (%=), etc.
o String operators: These operators are primarily used to perform concatenation and pattern
matching of strings. It includes + (String concatenation), += (String concatenation assignment),
% (Wildcard), [] (Character(s) matches), [^] (Character(s) not to match), _ (Wildcard match one
character), etc.
14) What is a view in SQL?

A view is a database object that has no values. It is a virtual table that contains a subset of data within a
table. It looks like an actual table containing rows and columns, but it takes less space because it is not
present physically. It is operated similarly to the base table but does not contain any data of its own. Its
name is always unique. A view can have data from one or more tables. If any changes occur in the
underlying table, the same changes reflected in the views also.

The primary use of a view is to implement the security mechanism. It is the searchable object where we
can use a query to search the view as we use for the table. It only shows the data returned by the query
that was declared when the view was created.

We can create a view by using the following syntax:

1. CREATE VIEW view_name AS

2. SELECT column_lists FROM table_name
3. WHERE condition;

15) What is an Index in SQL?

An index is a disc structure associated with a table or view that speeds up row retrieval. It reduces the
cost of the query because the query's high cost will lead to a fall in its performance. It is used to increase
the performance and allow faster retrieval of records from the table. Indexing reduces the number of
data pages we need to visit to find a particular data page. It also has a unique value meaning that the
index cannot be duplicated. An index creates an entry for each value which makes it faster to retrieve
data.

For example: Suppose we have a book which carries the details of the countries. If you want to find out
information about India, why will you go through every page of that book? You could directly go to the
index. Then from the index, you can go to that particular page where all the information about India is
given.

16) What are the different types of indexes in SQL?

SQL indexes are nothing more than a technique of minimizing the query's cost. The higher the query's
cost, the worse the query's performance. The following are the different types of Indexes supported in
SQL:
o Unique Index
o Clustered Index
o Non-Clustered Index
o Bit-Map Index
o Normal Index
o Composite Index
o B-Tree Index
o Function-Based Index

17) What is the unique index?

UNIQUE INDEX is used to enforce the uniqueness of values in single or multiple columns. We can
create more than one unique index in a single table. For creating a unique index, the user has to check
the data in the column because the unique indexes are used when any column of the table has unique
values. This indexing does not allow the field to have duplicate values if the column is unique indexed.
A unique index can be applied automatically when a primary key is defined.

We can create it by using the following syntax:

1. CREATE UNIQUE INDEX index_name

2. ON table_name (index_column1, index_column2,...);

Example

1. CREATE TABLE Employee (

2. ID int AUTO_INCREMENT PRIMARY KEY,
3. Name varchar(45),
4. Phone varchar(15),
5. City varchar(25),
6. );

Suppose we want to make a Phone column as a unique index. We can do this like below:

1. CREATE UNIQUE INDEX index_name_phone ON Employee (Phone);

18) What are the differences between SQL, MySQL, and SQL Server?

The following comparison chart explains their main differences:

SQL MySQL SQL Server

SQL or Structured Query MySQL is the popular database SQL Server is an RDBMS
Language is useful for management system used for database system mainly
managing our relational managing the relational database. developed for the Windows
databases. It is used to query It is a fast, scalable, and easy-to- system to store, retrieve, and
and operate the database. use database. access data requested by the
developer.

SQL first appeared in 1974. MySQL first appeared on May SQL Server first appeared on
23, 1995. April 24, 1989.

SQL was developed by IBM MySQL was developed by Oracle SQL Server was developed by
Corporation. Corporation. Microsoft Company.

SQL is a query language for MySQL is database software that SQL Server is also a software
managing databases. uses SQL language to conduct that uses SQL language to
with the database. conduct with the database.

SQL has no variables. MySQL can use variables SQL Server can use variables
constraints and data types. constraints and data types.

SQL is a programming MySQL is software, so it gets SQL Server is also software, so it

language, so that it does not get frequent updation. gets frequent updation.
any updates. Its commands are
always fixed and remain the
same.

19) What is the difference between SQL and PL/SQL?

The following comparison chart explains their main differences:

SQL PL/SQL

SQL is a database structured query PL/SQL or Procedural Language/Structured Query

language used to communicate with Language is a dialect of SQL used to enhance the
relational databases. It was developed by capabilities of SQL. Oracle Corporation developed it in
IBM Corporations and first appeared in the early 90's. It uses SQL as its database language.
1974.

SQL is a declarative and data-oriented PL/SQL is a procedural and application-oriented

language. language.

SQL has no variables. PL/SQL can use variables constraints and data types.
SQL can execute only a single query at a PL/SQL can execute a whole block of code at once.
time.

SQL query can be embedded in PL/SQL cannot be embedded in SQL as SQL does not
PL/SQL. support any programming language and keywords.

SQL can directly interact with the PL/SQL cannot directly interact with the database
database server. server.

SQL is like the source of data that we PL/SQL provides a platform where SQL data will be
need to display. shown.

20) Is it possible to sort a column using a column alias?

Yes. We can use the alias method in the ORDER BY instead of the WHERE clause for sorting a
column.

21) What is the SQL query to display the current date?

There is a built-in function in SQL called GetDate(), which is used to return the current timestamp.

22) Which are joins in SQL? Name the most commonly used SQL joins?

SQL joins are used to retrieve data from multiple tables into a meaningful result set. It is performed
whenever you need to fetch records from two or more tables. They are used with SELECT statement
and join conditions.

The following are the most commonly used joins in SQL:

o INNER JOIN
o LEFT OUTER JOIN
o RIGHT OUTER JOIN

23) What are the different types of joins in SQL?

Joins are used to merge two tables or retrieve data from tables. It depends on the relationship between
tables. According to the ANSI standard, the following are the different types of joins used in SQL:

o INNER JOIN
o SELF JOIN
o LEFT OUTER JOIN
o RIGHT OUTER JOIN
o FULL OUTER JOIN
o CROSS JOIN
24) What is INNER JOIN in SQL?

Inner join returns only those records from the tables that match the specified condition and hides other
rows and columns. In simple words, it fetches rows when there is at least one match of rows between
the tables is found. INNER JOIN keyword joins the matching records from two tables. It is assumed as
a default join, so it is optional to use the INNER keyword with the query.

The below visual representation explain this join more clearly:

The following syntax illustrates the INNER JOIN:

1. SELECT column_lists
2. FROM table1
3. INNER JOIN table2 ON join_condition1
4. INNER JOIN table3 ON join_condition2
5. ...;

25) What is the Right JOIN in SQL?

The Right join is used to retrieve all rows from the right-hand table and only those rows from the other
table that fulfilled the join condition. It returns all the rows from the right-hand side table even though
there are no matches in the left-hand side table. If it finds unmatched records from the left side table, it
returns a Null value. This join is also known as Right Outer Join.

The below visual representation explain this join more clearly:

The following syntax illustrates the RIGHT JOIN:

1. SELECT colum_lists
2. FROM table1
3. RIGHT JOIN table2
4. ON join_condition;

26) What is Left Join in SQL?

The Left Join is used to fetch all rows from the left-hand table and common records between the
specified tables. It returns all the rows from the left-hand side table even though there are no matches on
the right-hand side table. If it will not find any matching record from the right side table, then it returns
null. This join can also be called a Left Outer Join.

The following visual representation explains it more clearly:

The following syntax illustrates the RIGHT JOIN:

1. SELECT colum_lists
2. FROM table1
3. LEFT JOIN table2
4. ON join_condition;

27) What is Full Join in SQL?

The Full Join results from a combination of both left and right join that contains all the records from
both tables. It fetches rows when there are matching rows in any one of the tables. This means it returns
all the rows from the left-hand side table and all the rows from the right-hand side tables. If a match is
not found, it puts NULL value. It is also known as FULL OUTER JOIN.

The following visual representation explains it more clearly:

The following syntax illustrates the FULL JOIN:

1. SELECT * FROM table1

2. FULL OUTER JOIN table2
3. ON join_condition;

28) What is a "TRIGGER" in SQL?

A trigger is a set of SQL statements that reside in a system catalog. It is a special type of stored
procedure that is invoked automatically in response to an event. It allows us to execute a batch of code
when an insert, update or delete command is run against a specific table because the trigger is the set of
activated actions whenever DML commands are given to the system.

SQL triggers have two main components one is action, and another is an event. When certain actions
are taken, an event occurs as a result of those actions.

We use the CREATE TRIGGER statement for creating a trigger in SQL. Here is the syntax:

1. CREATE TRIGGER trigger_name

2. (AFTER | BEFORE) (INSERT | UPDATE | DELETE)
3. ON table_name FOR EACH ROW
4. BEGIN
5. --variable declarations
6. --trigger code
7. END;

29) What is self-join and what is the requirement of self-join?

A SELF JOIN is used to join a table with itself. This join can be performed using table aliases, which
allow us to avoid repeating the same table name in a single sentence. It will throw an error if we use the
same table name more than once in a single query without using table aliases.

A SELF JOIN is required when we want to combine data with other data in the same table itself. It is
often very useful to convert a hierarchical structure to a flat structure.

The following syntax illustrates the SELF JOIN:

1. SELECT column_lists
2. FROM table1 AS T1, table1 AS T2
3. WHERE join_conditions;

Example

Suppose we have a table 'Student' having the following data:

If we want to get retrieve the student_id and name from the table where student_id is equal, and
course_id is not equal, it can be done by using the self-join:

4. SELECT s1.student_id, s1.name

5. FROM student AS s1, student s2
6. WHERE s1.student_id=s2.student_id
7. AND s1.course_id<>s2.course_id;

Here is the result:

30) What are the set operators in SQL?

We use the set operators to merge data from one or more tables of the same kind. Although the set
operators are like SQL joins, there is a significant distinction. SQL joins combine columns from
separate tables, whereas SQL set operators combine rows from different queries. SQL queries that
contain set operations are called compound queries. The set operators in SQL are categories into four
different types:
A. UNION: It combines two or more results from multiple SELECT queries into a single result set. It
has a default feature to remove the duplicate rows from the tables. The following syntax illustrates the
Union operator:

1. SELECT columns FROM table1

2. UNION
3. SELECT columns FROM table2;

B. UNION ALL: This operator is similar to the Union operator, but it does not remove the duplicate
rows from the output of the SELECT statements. The following syntax illustrates the UNION ALL
operator:

1. SELECT columns FROM table1

2. UNION ALL
3. SELECT columns FROM table2;

C. INTERSECT: This operator returns the common records from two or more SELECT statements. It
always retrieves unique records and arranges them in ascending order by default. Here, the number of
columns and data types should be the same. The following syntax illustrates the INTERSECT operator:

1. SELECT columns FROM table1

2. INTERSECT
3. SELECT columns FROM table2;

D. MINUS: This operator returns the records from the first query, which is not found in the second
query. It does not return duplicate values. The following syntax illustrates the MINUS operator:

1. SELECT columns FROM table1

2. MINUS
3. SELECT columns FROM table2;
31) What is the difference between IN and BETWEEN operators?

The following comparison chart explains their main differences:

BETWEEN Operator IN Operator

This operator is used to selects the range of It is a logical operator to determine whether or
data between two values. The values can be not a specific value exists within a set of values.
numbers, text, and dates as well. This operator reduces the use of multiple OR
conditions with the query.

It returns records whose column value lies in It compares the specified column's value and
between the defined range. returns the records when the match exists in the
set of values.

The following syntax illustrates this The following syntax illustrates this operator:
operator: SELECT * FROM table_name
SELECT * FROM table_name WHERE column_name IN ('value1','value 2');
WHERE column_name BETWEEN 'value1'
AND 'value2';

32) What is a constraint? Tell me about its various levels.

The constraint is used to specify the rule and regulations that allows or restricts what values/data will be
stored in the table. It ensures data accuracy and integrity inside the table. It enforces us to store valid
data and prevents us from storing irrelevant data. If any interruption occurs between the constraint and
data action, the action is failed. Some of the most commonly used constraints are NOT NULL,
PRIMARY KEY, FOREIGN KEY, AUTO_INCREMENT, UNIQUE KEY, etc.

The following syntax illustrates us to create a constraint for a table:

1. CREATE TABLE table_name (

2. column1 datatype constraint,
3. column2 datatype constraint,
4. .........
5. );

SQL categories the constraints into two levels:

Column Level Constraints: These constraints are only applied to a single column and limit the type of
data that can be stored in that column.

Table Level Constraints: These constraints are applied to the entire table and limit the type of data that
can be entered.
33) How to write an SQL query to find students' names start with 'A'?

We can write the following query to get the student details whose name starts with A:

1. SELECT * FROM student WHERE stud_name like 'A%';

Here is the demo example where we have a table named student that contains two names starting with
the 'A' character.

34) Write the SQL query to get the third maximum salary of an employee from a table named
employees.

The following query is the simplest way to get the third maximum salary of an employee:

1. SELECT * FROM `employees` ORDER BY `salary` DESC LIMIT 1 OFFSET 2

Here is the demo example that shows how to get the third maximum salary of an employee.
The following are the alternative way to get the third-highest salary of an employee:

A. Using LIMIT Keyword

1. SELECT salary FROM employees

2. ORDER BY salary DESC
3. LIMIT 2, 1;

B. Using Subquery

1. SELECT salary
2. FROM
3. (SELECT salary
4. FROM employees
5. ORDER BY salary DESC
6. LIMIT 3) AS Temp
7. ORDER BY salary LIMIT 1;

C. Using TOP Keyword

1. SELECT TOP 1 salary

2. FROM
3. (SELECT DISTINCT TOP 3 salary
4. FROM employees
5. ORDER BY salary DESC) AS Temp
6. ORDER BY salary ASC;

35) What is the difference between DELETE and TRUNCATE statements in SQL?

The main difference between them is that the delete statement deletes data without resetting a table's
identity, whereas the truncate command resets a particular table's identity. The following comparison
chart explains it more clearly:

No. DELETE TRUNCATE

1) The delete statement removes single or The truncate command deletes the whole
multiple rows from an existing table contents of an existing table without the
depending on the specified condition. table itself. It preserves the table
structure or schema.

2) DELETE is a DML command. TRUNCATE is a DML command.

3) We can use the WHERE clause in the We cannot use the WHERE clause
DELETE command. with TRUNCATE.
4) DELETE statement is used to delete a TRUNCATE statement is used to
row from a table. remove all the rows from a table.

5) DELETE is slower because it maintained TRUNCATE statement is faster than

the log. DELETE statement as it deletes entire
data at a time without maintaining
transaction logs.

6) You can roll back data after using the It is not possible to roll back after using
DELETE statement. the TRUNCATE statement.

7) DELETE query takes more space. TRUNCATE query occupies less space.

36) What is the ACID property in a database?

The ACID properties are meant for the transaction that goes through a different group of tasks. A
transaction is a single logical order of data. It provides properties to maintain consistency before and
after the transaction in a database. It also ensures that the data transactions are processed reliably in a
database system.

The ACID property is an acronym for Atomicity, Consistency, Isolation, and Durability.

Atomicity: It ensures that all statements or operations within the transaction unit must be executed
successfully. If one part of the transaction fails, the entire transaction fails, and the database state is left
unchanged. Its main features are COMMIT, ROLLBACK, and AUTO-COMMIT.

Consistency: This property ensures that the data must meet all validation rules. In simple words, we
can say that the database changes state only when a transaction will be committed successfully. It also
protects data from crashes.

Isolation: This property guarantees that the concurrent property of execution in the transaction unit
must be operated independently. It also ensures that statements are transparent to each other. The main
goal of providing isolation is to control concurrency in a database.

Durability: This property guarantees that once a transaction has been committed, it persists
permanently even if the system crashes, power loss, or failed.

37) Is a blank space or zero the same as a NULL value?

No. The NULL value is not the same as zero or a blank space. The following points explain their main
differences:

o A NULL value is a value, which is 'unavailable, unassigned, unknown or not applicable.' It

would be used in the absence of any value. We can perform arithmetic operations on it. On the
other hand, zero is a number, and a blank space is treated as a character.
o The NULL value can be treated as an unknown and missing value, but zero and blank spaces
differ from the NULL value.
o We can compare a blank space or a zero to another blank space or a zero. On the other hand, one
NULL may not be the same as another NULL. NULL indicates that no data has been provided
or that no data exists.

38) What are functions and their usage in SQL?

SQL functions are simple code snippets that are frequently used and re-used in database systems for
data processing and manipulation. Functions are the measured values. It always performs a specific
task. The following rules should be remembered while creating functions:

o A function should have a name, and the name cannot begin with a special character such as @,
$, #, or other similar characters.
o Functions can only work with the SELECT statements.
o Every time a function is called, it compiles.
o Functions must return value or result.
o Functions are always used with input parameters.

SQL categories the functions into two types:

o User-Defined Function: Functions created by a user based on their needs are termed user-
defined functions.
o System Defined Function: Functions whose definition is defined by the system are termed
system-defined functions. They are built-in database functions.

SQL functions are used for the following purposes:

o To perform calculations on data

o To modify individual data items
o To manipulate the output
o To format dates and numbers
o To convert data types

40) What is the default ordering of data using the ORDER BY clause? How could it be changed?

The ORDER BY clause is used to sort the table data either in ascending or descending order. By
default, it will sort the table in ascending order. If we want to change its default behavior, we need to
use the DESC keyword after the column name in the ORDER BY clause.

The syntax to do this is given below:

1. SELECT expressions FROM tables

2. WHERE conditions
3. ORDER BY expression [ASC | DESC];
We have taken a customer table in the previous example. Now, we will demonstrate the ORDER BY
clause on them as well.

In the below output, we can see that the first query will sort the table data in ascending order based on
the name column. However, if we run the second query by specifying the DESC keyword, the table's
order is changed in descending order.

41) Is the following query returns the output?

1. SELECT subject_code, AVG (marks)

2. FROM Students
3. WHERE AVG(marks) > 70
4. GROUP BY subject_code;

Answer: No. The above query does not return the output because we cannot use the WHERE clause to
restrict the groups. We need to use the HAVING clause instead of the WHERE clause to get the correct
output.

42) What is the difference between the WHERE and HAVING clauses?

The main difference is that the WHERE clause is used to filter records before any groupings are
established, whereas the HAVING clause is used to filter values from a group. The below comparison
chart explains the most common differences:

WHERE HAVING

This clause is implemented in row This clause is implemented in column operations.

operations.
It does not allow to work with aggregate It can work with aggregate functions.
functions.

This clause can be used with the This clause can only be used with the SELECT
SELECT, UPDATE, and DELETE statement.
statements.

43) How many Aggregate functions are available in SQL?

The aggregate function is used to determine and calculate several values in a table and return the result
as a single number. For example, the average of all values, the sum of all values, and the maximum and
minimum value among particular groupings of values.

The following syntax illustrates how to use aggregate functions:

1. function_name (DISTINCT | ALL expression)

SQL provides seven (7) aggregate functions, which are given below:

o AVG(): This function is used to returns the average value from specified columns.
o COUNT(): This function is used to returns the number of table rows, including rows with null
values.
o MAX(): This function is used to returns the largest value among the group.
o MIN(): This function is used to returns the smallest value among the group.
o SUM(): This function is used to returns the total summed values(non-null) of the specified
column.
o FIRST(): This function is used to returns the first value of an expression.
o LAST(): This function is used to returns the last value of an expression.