Questions (SQL)

1. What is DBMS? (RETIVEING, STORING, ANALYSING)

2. What is RDBMS?
3. TYPES OF DBMS?
4. What are crud operations for DBMS?
5. What are ACID properties of DBMS?
6. What are properties of transaction? ACID same
7. What is difference between structured and unstructured data with examples?
8. What is data warehouse?
9. Difference between data warehouse an Rdbms?
10. What is SQL and its advantages? Easy to learn, accessible, used for complex operation?
11. What is difference between table, schema , database?
12. Types of software for Dbms? SQLITE , ,MYSQL, MSSQL, ORACLE DB2 , MONGO DB
13. What is the difference between SQL and MySQL?
SQL is a standard language for retrieving and manipulating structured databases. On the
contrary, MySQL is a relational database management system, like SQL Server, Oracle or IBM
DB2, that is used to manage SQL databases
14. What is difference between table and field?
A table is an organized collection of data stored in the form of rows and columns. Columns can
be categorized as vertical and rows as horizontal. The columns in a table are called fields while
the rows can be referred to as records.
Table = rows +columns(records + fields)
15. What are relationships , describe three types of relationships? One to one , one to many and
many to many.
16. What are commands of SQL? DDL,DML ,DCL,TCL
TCL:
SAVEPOINT: It is used to roll the transaction back to a certain point without rolling back the
entire transaction.
Rollback: Rollback command is used to undo transactions that have not already been
saved to the database.
Commit: Commit command is used to save all the transactions to the database.
DCL :
Grant: It is used to give user access privileges to a database.
Revoke: It is used to take back permissions from the use
17. Why is Select command is DML?
DML command is manipulation and access the data and select command is use to access the
data but not manipulate it so it is partially a DML command.
18. Difference between truncate, delete, drop?
19. What are data types in SQL?
Number , Characters,datetime,Boolean
20. What is difference between char and varchar?
Varchar is variable length and char is fixed length , char includes missing space but varchar
doesn’t take missing spaces. Char is faster then varchar because of fixed length.
21. What are uses of alter command?
To add a column , to rename a column, to modify a column , to drop a column
 Questions (SQL)
22. What are anomalies and its types ?
A database anomaly is a fault in a database that usually emerges as a result of shoddy planning
and storing everything in a flat database. In most cases, this is removed through the
normalization procedure, which involves the joining and splitting of tables. The purpose of the
normalization process is to minimise the negative impacts of generating tables that would
generate anomalies in the DB.
Update Anomaly
Employee David has two rows in the table given above since he works in two different
departments. If we want to change David’s address, we must do so in two rows, else the data
would become inconsistent.’
If the proper address is updated in one of the departments but not in another, David will have
two different addresses in the database, which is incorrect and leads to inconsistent data.

Insert Anomaly
If a new worker joins the firm and is currently unassigned to any department, we will be unable
to put the data into the table because the w_dept field does not allow nulls.

Delete Anomaly
If the corporation closes the department F890 at some point in the future, deleting the rows
with w_dept as F890 will also erase the information of employee Mike, who is solely assigned to
this department.

23. What are constraints in Sql? Not Null , Primary , Unique ,Foreign , check, Default , Index
Primary key : its uniquely identifies each record in a column as unique and non-repetitive.
Foreign key : Used to remove anomalies by setting relationship between tables. It is used to link
two tables and also called as referencing key. There is a parent child relation ship
Not Null : It enforce our column not to accept null values means field should always contain
some values.
Unique : It ensures that all columns are unique.
Check : It is used to put conditions on field.
Index : it is used to index in table, used to increase speed of accessing data. User cannot see it.
Only used on columns that are searched more often.
24. What are constraints ?
Constraints are used to impose rules and regulation in table and column level.
25. What are different keys in Sql ? Primary , unique , Foreign , Composite
26. Difference between unique and primary key?
27. Difference between cluster and non cluster index ?
Cluster index physically stores data of table in order of key values and data is restored everytime
whenever a new value is inserted or updated. No separate storage required. It is always index id
as 1. Primary key creates cluster index by default
Non cluster index create separate list of key values that points towards location of the data in
datapage. It stores data separately. Non cluster index id is less than 1.unique key creates non
cluster index by default.
 Questions (SQL)
28. Is semicolon used after sql? Justify why or why not.
29. What are all the aggregate functions? Always used with select and group by (if using
multiple columns) Max, min, count, Sum, Avg
30. Why do we use where clause?
31. Difference between where and having?
Both WHERE and HAVING are used to filter data in an SQL query. The main difference is that
WHERE is used on non-aggregated values, while HAVING is used on the aggregated ones. The
order of execution is as follows: WHERE – GROUP BY – HAVING. This also means WHERE is
written before GROUP BY, while HAVING comes after GROUP BY.
In practice, WHERE filters data first. This filtered data will be grouped and aggregated, and then
HAVING will filter the grouped and aggregated data
32. Difference between group by and order by?
33. List select, aggregate , group by, having etc in order of execution?
34. How do you filter groups in an SQL query? Having
35. What is the difference between COUNT(*), COUNT(expression), and COUNT(DISTINCT
expression)?
36. What happens to NULLs when you use GROUP BY?
The GROUP BY clause doesn’t ignore NULL values. Therefore, if you use it and there are NULL
values in the column, all the rows with NULL column values will be treated as one group
37. How to Count SQL NULL values in a column? The COUNT() function is used to obtain the
total number of the rows in the result set. When we use this function with the star sign it
count all rows from the table regardless of NULL values
38. AVG() function and SQL NULL values? One point to note about the AVG() function
calculation is that NULL values will not be included in the average calculation.
39. What is the difference between ‘IS NULL’ and ‘= NULL’?
40. Use of count with null values?
COUNT():
Using the COUNT() syntax counts all rows in a table, including those with null values in the
specified column(s). Null values are included in the count. For example, if you have a table with
10 rows, and one of the columns contains 3 null values, COUNT(*) will return 13 as the result.

COUNT(column):
When you use the COUNT(column) syntax, the function counts the number of non-null values in
the specified column. It excludes null values from the count. For example, if you have a table
with 10 rows and one of the columns contains 3 null values, COUNT(column) will return 7 as the
result.

COUNT(DISTINCT column):
The COUNT(DISTINCT column) syntax counts the number of distinct non-null values in the
specified column. It also excludes null values from the count. Duplicate non-null values are only
counted once. Null values are not considered for distinct counting. For example, if you have a
table with 10 rows, one of the columns contains 3 null values, and the non-null values in that
column are [A, B, C, C, D, E, E], COUNT(DISTINCT column) will return 5 as the result.
 Questions (SQL)
41. What are views in columns?
In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are fields from one
or more real tables in the database.
You can add SQL statements and functions to a view and present the data as if the data were
coming from one single table.
A view is created with the CREATE VIEW statement.
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
42. Can you give me some examples of why we would want to use views?

Views can be used for a number of reasons. For example, they can be used to restrict access to
certain data so that only authorized users can see it. Views can also be used to simplify complex queries
by breaking them down into smaller, more manageable pieces. Additionally, views can be used to
provide an alternative way of looking at the data in a database, which can be useful for different types of
users.

43. Difference between Aggregate and Scalar functions?

Aggregate functions:
These functions are used to do operations from the values of the column and a single value is
returned.
AVG()
COUNT()
FIRST()
LAST()
MAX()
MIN()
SUM()
Scalar functions:
These functions are based on user input, these too returns single value.
UCASE()
LCASE()
MID()
LEN()
ROUND()
NOW()
FORMAT()
44. What is a CLAUSE in sql?
A clause in SQL is a built-in function that helps to fetch the required records from a database
table. A clause receives a conditional expression, i.e. a column name or some terms involving the
columns.

45. What is alias in SQL?

 Questions (SQL)
46. What is normalization?
47.What is denormalization?
48.Explain the different types of normalization.
49.What is join and types of join?
A JOIN clause is used to combine rows from two or more tables, based on a related
column between them.
Here are the different types of the JOINs in SQL:
(INNER) JOIN: Returns records that have matching values in both tables
LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records
from the right table
RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched
records from the left table
FULL (OUTER) JOIN: Returns all records when there is a match in either left or right
table

50. How can you join a table to itself?

Another type of join in SQL is a SELF JOIN, which connects a table to itself.
In order to perform a self-join, it is necessary to have at least one column
(say X) that serves as the primary key as well as one column (say Y) that
contains values that can be matched with those in X. The value of Column Y
may be null in some rows, and Column X need not have the exact same
value as Column Y for every row.
51.Explain natural join.
Natural Joins are a type of join that combines tables based on columns that
share the same name and have the same datatype. Ideally, there should be
 Questions (SQL)
a common attribute (column) among two tables in order to perform a
natural join.
52.What is an Equi Join?
Equi Joins are a type of INNER Joins where a join is performed between two
or more tables using a common column between them. Using the equality
sign ( = ), it compares the data in two columns, if the data is the same, it
retrieves it.
53.What is a Non-Equi Join?
A Non-Equi join entails pulling data from multiple tables by using an INNER
join. This type of join matches the columns of two tables based on an
inequality using operators such as <, <=, >, >=, !=, BETWEEN, etc.
54.What makes a Union clause different from a Join clause?
Join: Essentially, joins allow you to combine data into new columns
horizontally. A join clause is an SQL command used in order to combine
records from multiple tables or retrieve data from these tables based on
the existence of a common field (column) between them. In JOIN, the
columns of the joining tables may differ. Here is a visual representation of
how a join looks.
Union: In SQL, the term UNION is used to combine the results of more than
one SELECT statement. Union allows you to combine data into new rows
vertically. UNION requires that all queries have the same number of
columns and order of columns. Here is a visual representation of how a
union looks.
55.Is it required that the Join condition be based on equality?
No, joins can have non-equi conditions as well. Join clauses can be used
with common comparison operators, such as <, <=, >, >=, !=, BETWEEN. For
example, listing records, listing unique pairs, and identifying duplicate
records are just a few situations where non-equi joins can prove to be
useful.
56.State difference between Full Join and Cross Join.
Cross Join: A Cross Join is also referred to as a Cartesian join or Cartesian
product. The result set of a cross join is equal to all of the rows from the
first table multiplied by all of the rows from the second table. It applies to
all columns.
 Questions (SQL)
Full Join: This is also referred to as a full outer join. In a full outer join, all
rows of tables are combined to form a result set. It basically means that a
query would return a result set from both tables, even if they had NULL
values. A result set (joined table) will display NULL values if both tables do
not contain any matching rows.
57.Distinguish between nested subquery, correlated subquery, and join
operation.
Join Operation: A join operation is a binary operation that combines the
data or rows from two or more tables based on a field they have in
common. There are different types of joins, such as INNER JOIN, FULL JOIN,
LEFT JOIN, RIGHT JOIN, etc.

Subquery: A query can be enclosed within another query, so the outer

query is called the main query, and the inner query is called a subquery.
Nested Query: Nested queries execute the inner query first, and only
once. An outer query is then executed based on the results of the inner
query. The inner query is therefore used to execute the outer query.
Correlated Query: The outer query is executed first, and for every row
of the outer query, the inner query is executed. Thus, values from the outer
query are used in the inner query.
58. Why can't this subquery return more than one row?
Subqueries included as columns of a SELECT statement are called "scalar
subqueries". A scalar subquery should be able to produce zero or one row
only since its value (the scalar) will be placed in the returned row of the
result set of the query, where there's room for one value only. Therefore, if
a subquery returns more than a single row, it cannot be used directly as a
SELECT column.

One option is to force it to produce one row at most, maybe using an

aggregation function such as MAX(), MIN(), COUNT(), etc.

Another option is to join the subquery as a "table expression", where there

are no restriction on the number of returned rows.
 Questions (SQL)

59.What Is a Window Function in SQL?

Window functions are SQL functions that operate on a set of records called
the “window” or the “window frame”. The “window” is a set of rows that
are somehow related to the row currently being processed by the query
(e.g. all rows before the current row, 5 rows before the current row, or 3
rows after the current row).
60.What are types of window function?
Window functions can be organized into the following four categories:
aggregate functions, ranking functions, analytic functions, and distribution
functions.
Aggregate : count, min, max, avg, sum
Ranking : Rownumber() , Rank(), denseRank()
ROW_NUMBER() returns the position of the row in the result set.
RANK() ranks rows based on a given value. When two rows are in the same
position, it awards them the same rank and leaves the next position empty
(e.g. 1, 2, 3, 3, 5...).
DENSE_RANK() also ranks rows by a given value, but it does not leave the
next position empty (e.g. 1, 2, 3, 3, 4, 5...).
In the analytic category, the functions LEAD(), LAG() or FIRST_VALUE()
allow us to obtain data from other rows in the same window. LEAD()
returns values from rows below of the current row; LAG() from rows above
the current row. For more details, see our article on LEAD vs LAG.

Finally, in the distribution category there are functions like

PERCENT_RANK() and CUME_DIST() that can obtain percentile rankings or
cumulative distributions

61.Describe the Difference Between Window Functions and Aggregate

Functions.
aggregate functions group multiple rows into a single result row.
window functions produce a result for each individual row.
62.How Do You Define the Window Frame?
Window functions calculate an aggregated result based on a set of records
called the “window” or “window frame”. Window frames are defined by
the OVER() clause.
 Questions (SQL)
An empty OVER() clause means that the window is the entire dataset
63.Describe SQL’s Order of Operations and Window Functions’ Place in This
Order.
The sub-clauses of an SQL SELECT are executed in the following order:
FROM / JOINS>WHERE>GROUP BY>Aggregate
Functions>HAVING>WindowFunctions>SELECT>DISTINCT>UNION /
INTERSECT / EXCEPT>ORDER BY>OFFSET>LIMIT / FETCH / TOP
64.What is “Trigger” in SQL?
A trigger allows you to execute a batch of SQL code when an insert,update
or delete command is run against a specific table as Trigger is said to be the
set of actions that are performed whenever commands like insert, update
or delete are given
65.Difference between truncate and delete?
Truncate is used to delete the complete data from table it is a ddl
command cannot restore data once deleted
Delete is used to particular data , it is a dml command
66.What is a Like operator? Used to search patterns in a string column, mainly
used with wild cards.
Types of wild cards : % - used to match number of characters, _- used to
match single character
67.What are types of operator in sql?
Arithmetic operators
Comparison operators
Logical operators-All,any,and,or,between,in,Like
Set operators – Union, Intersect
68.What are SQL wild cards?
A wildcard character is used to substitute one or more characters in a
string.

Wildcard characters are used with the LIKE operator. The LIKE operator is
used in a WHERE clause to search for a specified pattern in a column.
Wildcards in Ms access and sql server –

Wildcard Characters in MS Access

 Questions (SQL)

Symbo Description Example

l

* Represents zero or more bl* finds bl, black, blue,

characters and blob

? Represents a single h?t finds hot, hat, and

character hit

[] Represents any single h[oa]t finds hot and hat,

character within the but not hit
brackets

! Represents any character h[!oa]t finds hit, but not

not in the brackets hot and hat

- Represents any single c[a-b]t finds cat and cbt

character within the
specified range

# Represents any single 2#5 finds 205, 215,

numeric character 225, 235, 245, 255,
265, 275, 285, and 295

Wildcard Characters in SQL Server

Symbol Description Example

 Questions (SQL)

% Represents zero or more characters bl% finds bl, black, blue,

and blob

_ Represents a single character h_t finds hot, hat, and hit

[] Represents any single character within h[oa]t finds hot and hat,
the brackets but not hit

^ Represents any character not in the h[^oa]t finds hit, but not
brackets hot and hat

- Represents any single character within c[a-b]t finds cat and cbt
the specified range

All the wildcards can also be used in combinations!

LIKE operators with '%' and '_'

Atomicity

The transaction never remains in between either it performs completely or gets discarded
completely in the case of any system or DBMS failure. Read more about Atomicity in DBMS.

Consistency

Data is very precious and it should remain the same before and after the transaction. It shouldn’t
show any inconsistent behaviors with DBMS
 Questions (SQL)
Isolation

All transactions should be isolated from one another, which means every transaction should be
separate from one another there shouldn’t be any interference of one transaction onto another.

Durability

After execution of the entire transaction, data should become permanent on the database. It
should be durable i.e. it should remain there in case of system failure or system crash.

DATABASE :In simple words, a database is the collection of data that is organized in such
a way, so that it becomes easy to retrieve, update and delete that data.
DBMS : DBMS stands for Database Management System; it is the software used to
work or manage the databases. The main work of DBMS is to provide an interface for
the user to manipulate data from the database.
DBMS acts as a middle layer between the user and the actual database.

Types of DBMS:

1. Centralised
2. Decentralised

3. Network
4. Hierarchial
5. NoSql - NoSQL or non-relational databases are the recently popular databases due to their high
scalability and availability. In this type of database, the data is stored in collections and it doesn’t
contain tables like relational databases.
6. RDBMS -The relational database management system is also known as RDBMS. It is one of the
types of DBMS which is widely used for commercial applications. It contains tables in which data
is stored in the form of rows and columns like an excel sheet. Some of the tables possess the
relationship among them and the data is retrieved with the help of join operation.
 Questions (SQL)

 A database schema describes the structure and organization of data in a

database system, while a table is a data set in which the data is organized in to a
set of vertical columns and horizontal rows. The database schema defines the
tables in a database, the columns and their types. In addition the schema also
defines what columns are defined as the primary key of a table.

On the basis Structured data Unstructured data

of

Technology It is based on a relational database. It is based on character and binary data.

Flexibility Structured data is less flexible and There is an absence of schema, so it is more
schema-dependent. flexible.

Scalability It is hard to scale database schema. It is more scalable.

Robustness It is very robust. It is less robust.

Performance Here, we can perform a structured While in unstructured data, textual queries are
query that allows complex joining, possible, the performance is lower than semi-
so the performance is higher. structured and structured data.

Nature Structured data is quantitative, i.e., It is qualitative, as it cannot be processed and

it consists of hard numbers or analyzed using conventional tools.
things that can be counted.

Format It has a predefined format. It has a variety of formats, i.e., it comes in a

variety of shapes and sizes.

Analysis It is easy to search. Searching for unstructured data is more

 Questions (SQL)
difficult.

Types of Subqueries in SQL

A maximum of 255 subquery levels can be nested in a WHERE clause. The FROM clause has no limit in
nesting subqueries. In the real world, we encounter not more than five subqueries. So, 255 is too large
to be set as a limit.

Single Row Subquery

Subqueries that return a single row as an output to their parent query are called single-row subqueries.
Single-row subqueries are used in a SQL SELECT statement with HAVING clause, WHERE clause, or a
FROM clause and a comparison operator. Single-row subqueries are used in the SELECT statement. Let's
see it with an example.

Multiple Row Subquery

Subqueries that return multiple rows as an output to their parent query are called multiple-row
subqueries. Multiple row subqueries can be used in a SQL SELECT statement with a HAVING clause,
WHERE clause, a FROM clause, and a logical operator(ALL, IN, NOT IN, and ANY).

Correlated Subqueries

Subqueries that return multiple columns as output depending on the information obtained from the
parent query are called correlated subqueries. The interdependence of the inner and outer query makes
it complicated to understand.

Nested Subqueries

Subqueries that are inside another subquery are called nested subqueries. Subqueries are executed level
by level. The innermost is executed first, and then the outer ones.

2.Which of the following is true about sub-queries?

A. They execute after the main query executes

B. They execute in parallel to the main query
C. The user can execute the main query and then, if wanted, execute the sub-query
D. They execute before the main query executes.

Answer: D. The sub-query always executes before the execution of the main query.Subqueries
are completed first.The result of the subquery is used as input for the outer query.
4.Which of the following clause is mandatorily used in a sub-query?

A. SELECT
 Questions (SQL)
B. WHERE
C. ORDER BY
D. GROUP BY

Answer: A. A sub-query is just like any other query which has to start with a SELECT clause.
They are contained within an outer query.
7.In which of the following clauses can a sub-query be used?

A. HAVING
B. WHERE
C. FROM
D. All of the above

Answer : All of the above

8.Which of the following single-row operators can be used for writing a sub-query?

A. >=
B. <
C. =
D. All of the above

Answer: D. Single-row operators include =, >, <, >=, <=, and <>
12.What among the following is true about sub-queries?

A. Sub-queries can be written on either side of a comparison operator

B. Parenthesis is not mandatory for sub-queries
C. Single-row sub-queries can use multi-row operators but vice versa is not possible
D. All of the above

Answer: A. Sub queries can be placed on left or right hand side of the comparison operator
depending on the query indentation and usability.

https://www.tutorialspoint.com/sql_certificate/subqueries_to_solve_queries_questions.htm for more

questions on subqueries.