SQL THEORY QUESTIONS

1. Define Database
A database is an organized collection of data stored in a computer system and usually controlled by a
database management system (DBMS).
2. Define DBMS.
A Database Management System (DBMS) is a software system that allows users to create, define,
manipulate and manage databases.

3. Define the following :

i) Degree ii) Cardinality iii) Domain iv) Tuple v) Attribute.
i) Degree : Total no of attributes/columns present in a relation/table is called degree.
ii) Cardinality : Total no if tuples present in a relation or Rows present in a table, is called cardinality.
iii) Domain: Total range of accepted values for an attribute of the relation is called the domain.
iv) Tuple & attribute : The rows are referred to as tuples in DBMS or records, and the columns are
referred to as attributes.

4. Difference between DDL and DML.

DDL DML

It helps us define a database's It allows us to manipulate, i.e.,

structure or schema and deals with retrieve, update, and delete the data
how data is stored in the database. stored in the database.

The full form of DDL is Data The full form of DML is Data
Definition Language. Manipulation Language.
 The commonly used commands
The commonly used commands
under DDL language are:
under DML language are:
o CREATE
o INSERT
o DROP
o UPDATE
o ALTER
o DELETE
o TRUNCATE
o SELECT
o RENAME

DDL commands have no use of a The DML statements can use a

WHERE clause because here, WHERE clause while manipulating
filtration of records is not possible. data in a database.

The DML commands will affect

The DDL command affects the
the single or multiple records based
entire database or table.
on the specified condition.

5. Difference between WHERE and HAVING clause.

WHERE Clause HAVING Clause

It is used to perform filtration on

It is used to perform filtration on
groups.
individual rows.

It is implemented in row It is implemented in column

operations. operations.

The HAVING clause can work

The WHERE clause does not allow
with aggregate functions.
to work with aggregate functions.

The GROUP BY clause comes The GROUP BY clause comes

after the WHERE clause. before the HAVING clause.

6. Difference between ALTER and UPDATE Clause.

ALTER Command UPDATE Command

ALTER command is Data Definition Language UPDATE Command is a Data

(DDL). Manipulation Language (DML).

Alter command will perform the action on structure Update command will perform on the data
level and not on the data level. level.
 ALTER Command UPDATE Command

ALTER Command is used to add, delete, modify the UPDATE Command is used to update
attributes of the relations (tables) in the database. existing records in a database.

7. Difference between CHAR and VARCHAR

CHAR VARCHAR

CHAR datatype is used to store VARCHAR datatype is used to store character strings
character strings of fixed length of variable length

In CHAR, If the length of the string In VARCHAR, If the length of the string is less than
is less than set or fixed-length then it the set or fixed-length then it will store as it is without
is padded with extra memory space. padded with extra memory spaces.

8. Difference between UNIQUE and PRIMARY KEY Constraints.

PRIMARY KEY UNIQUE KEY

The primary key is used as a

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

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

It enforces entity integrity. It enforces unique data.

Each table supports only one

A table can have more than one unique key.
primary key.

7. Differentiate between COUNT(*) and COUNT( ) functions in SQL with appropriate example.

COUNT(*) returns the count of all rows in the table, whereas COUNT() is used with Column_Name
passed as an argument and counts the number of non-NULL values in a column that is given as argument.

Consider the table DEPARTMENT

Dept_id Dept_name Select count(*) from department;

210 HR
211 SALES Count(*)
212 NULL 4
213 IT
 Select count(dept_name) from department;

Count(*)
3

8. What is a not null constraint in SQL?

A not null constraint is a restriction that does not allow a column to be left blank during insertion

9. What is the use of ORDER BY and GROUP BY Clause? Or Difference between ORDER BY and
GROUP BY Clause.
ORDER BY GROUP BY
The ORDER BY clause sorts the data in GROUP BY clause groups the tuples(rows)
ascending or descending order. based on the similarities of columns.
It is not mandatory to use aggregates functions It is mandatory to use aggregates functions in
in the ORDER BY. the ORDER BY.
10. Difference between single row function and aggregate function.

SINGLE ROW FUNCTION AGGREGATE FUNCTION

The single-row functions are divided into Aggregative Functions (also known as Group
groups according to data types such as functions) are used to summarize a group of
NUMERIC functions, CHAR functions, and values into a single result.
DATETIME functions.
11. Difference between DROP and DELETE command.

DELETE DROP
It removes some or all the tuples from a table.
It removes the entire schema, table, domain,
or constraints from the database.
It is a Data Manipulation Language command It is a Data Definition Language command.
WHERE clause is mainly used along with the No clause is required along with the DROP
DELETE command. command.
12. Difference between NATURAL JOIN and EQUI JOIN.

NATURAL JOIN EQUI JOIN

It joins the tables based on the same column It joins the tables based on the equality or
names and their data types. matching column values in the associated
tables.
It always returns unique columns in the result It can return all attributes of both tables along
set. with duplicate columns that match the join
condition.
13. Difference between column constraint and table constraint?
Column constraint applies only to individual columns whereas table constraints apply to groups of one
or more columns.
14. Difference between LIKE and IN operator.
LIKE uses wildcards, which are used to query similar values, but IN returns precise record sets
based on specific values.
15. Define Cartesian Product.
A Cartesian product is the result of joining every row in one table with every row in another table. It is
also known as cross join.
16. Categorize the following commands as DDL or DML: CREATE, DROP, UPDATE, INSERT
DDL : CREATE , DROP DML : UPDATE , INSERT