Pritesh

1.What types of SQL statements or languages (or SQL subsets) do you know?
 Data Definition Language (DDL) – to define and modify the structure of a database.

 Data Manipulation Language (DML) – to access, manipulate, and modify data in a database.

 Data Control Language (DCL) – to control user access to the data in the database and give or revoke
privileges to a specific user or a group of users.

 Transaction Control Language (TCL) – to control transactions in a database.

 Data Query Language (DQL) – to perform queries on the data in a database to retrieve the necessary
information from it.

Give some examples of common SQL commands of each type.

 DDL: CREATE, ALTER TABLE, DROP, TRUNCATE, and ADD COLUMN

 DML: UPDATE, DELETE, and INSERT

 DCL: GRANT and REVOKE

 TCL: COMMIT, SET TRANSACTION, ROLLBACK, and SAVEPOINT

 DQL: – SELECT

2.What is a constraint, and why use constraints?

A set of conditions defining the type of data that can be input into each column of a table.
Constraints ensure data integrity in a table and block undesired actions.

 DEFAULT – provides a default value for a column.

 UNIQUE – allows only unique values.

 NOT NULL – allows only non-null values.

 PRIMARY KEY – allows only unique and strictly non-null values (NOT NULL and UNIQUE).

 FOREIGN KEY – provides shared keys between two or more tables.

3.What is a subquery?
Also called an inner query, a query placed inside another query, or an outer query. A subquery may
occur in the clauses such as SELECT, FROM, WHERE, UPDATE, etc. It's also possible to have a
subquery inside another subquery. The innermost subquery is run first, and its result is passed to the
containing query (or subquery).
 What types of SQL subqueries do you know?

 Single-row – returns at most one row.

 Multi-row – returns at least two rows.

 Multi-column – returns at least two columns.

 Correlated – a subquery related to the information from the outer query.

 Nested – a subquery inside another subquery.

Que .4 Difference between where and having clause

Where clause Having clause

WHERE Clause is used to filter the records from the HAVING Clause is used to filter record from the
table based on the specified condition groups based on the specified condition.
WHERE Clause can be used without GROUP BY HAVING Clause cannot be used without GROUP BY
Clause Clause
We use where clause before group by We use having clause after group by

We cannot use aggregate function in where clause We must have to use aggregate functions

WHERE Clause can be used with SELECT, UPDATE, HAVING Clause can only be used with SELECT
DELETE statement. statement.

Que no .5 Difference between Drop, Truncate and Delete

Delete Drop Truncate

It is a Data Manipulation It is a Data Definition Language It is also a Data Definition

Language Command (DML). Command (DDL) Language Command (DDL).
It is used to delete one or more It is used to drop the whole It is used to delete all the rows of
tuples of a table. table. a relation (table) in one go

Rollback is possible. Rollback is not possible. Rollback is not possible.

We can apply where clause with We can’t apply where clause with We can’t apply where clause with
delete command drop command truncate command.
Syntax: DELETE from WHERE ; Syntax: drop table empinfo3 Syntax: truncate table empct10
Que 6.Explain not null constraint
Not null-- It does not accept nulls value but accepts duplicates

It can be applied on more than 1 column on any table.

Create table CT10INFOCONS2 (cid number (7),cname varchar2(20) not null,cmob varchar2(14),cbill
number(5), ccity varchar2(20));

Que.7 In which order the interpreter executes the common statements in the
SELECT query?
FROM – JOIN – ON – WHERE – GROUP BY – HAVING – SELECT – ORDER BY – LIMIT

Que 8. You have a table “products” with columns “product_id” and

“category.” Find the number of products in each category and display the
category with zero products as well.

 SELECT category, COUNT(*) AS num_products

FROM products
GROUP BY category;

Que 9.Given a table employee in which we have DeptId for each employee
write single query from to move the employees from deptid1 to deptid2 and
move employee from deptid 2 to 1
Employee

ID Name dept id

1. John 1
2. Prashant 2
3. Vishal 1
4. Pritesh 2
Tricky Interview Questions to confuse Candidates

Designed to challenge the solver in you, these questions will assess your problem-solving skills. Use
the table below for refrence and answer the questions accordingly.

 Data Setup: Use the sample tables for these questions.

 Customers Table:
Customer ID Name City
1 John Levi New York
2 Jane Tye Los Angeles
3 Mike Foley Chicago
4 Alice White New York

 Orders Table:
Order ID Customer ID Order Date Order Total
100 1 2023-07-01 100.00
101 2 2023-06-15 50.00
102 3 2023-07-05 150.00
103 1 2023-07-07 75.00
104 4 2023-07-02 200.00
 Que10.Find the total number of orders placed by each customer, excluding
orders placed in June.

 Tricky Aspect: Excluding a specific month required filtering based on the date.
 Answer:
SQL Code:
SELECT c.name, COUNT(*) AS num_orders
FROM Customers c
INNER JOIN Orders o ON c.customer_id = o.customer_id
WHERE MONTH(order_date) <> 6
GROUP BY c.name

Que11.Identify customers who haven't placed any orders.

 Tricky Aspect: Requires using LEFT JOIN and filtering for null values.
 Answer:
SQL Code:
SELECT c.name
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Que.12 Write a query to display the top 2 customers with the most orders in
the last 30 days.
 Tricky Aspect: Requires filtering by date range and using window functions for ranking.
 Answer:
SQL Code:
SELECT c.name, COUNT(*) AS num_orders
FROM Customers c
INNER JOIN Orders o ON c.customer_id = o.customer_id
WHERE order_date >= DATE_SUB (CURDATE)

13.What set operators do you know?

 UNION – returns the records obtained by at least one of two queries (excluding duplicates)

 UNION ALL – returns the records obtained by at least one of two queries (including duplicates)

 INTERSECT – returns the records obtained by both queries

 EXCEPT (called MINUS in MySQL and Oracle) – returns only the records obtained by the first query
but not the second one
Que 14.How many records returns from following table using different join
types

Que15.How do you find all employee who are also manager? Which types of
join used and why?

Using self-join

Select e.name,m.name from employee e ,employee m where e.manager_id= m.emp_id

Que.16 Find all employee names starting with ‘S’ not using like operator
Ans : using INSTR select ename from emp where instr(ename,’S’)=1;

Using SUBSTR select substr (ename 1,1)=’S’;

Write a query for display half or 50% records from table

Select*from emp where rownum<= (select count(*)/2 from emp)

ITIL

What is the Incident Management Process? Can You Provide Examples

IcM, or Incident Management, is an area of IT Service Management (ITSM) that exists in order to
restore normal service operations as soon as possible. Examples of this include a printer that doesn’t
work, disk usage exceeded, or an entire system that has gone down.

What are some steps involved in the working of the Incident management
system? Or Steps for handling the incident
 Acknowledgement  Basic investigation  Creation of incident ticket  Sending communication to
business and end user about the incident  Opening bridge call  Engaging required person 
Investigation and diagnostics  Periodically sending update notification  Escalation if required
(Functional/Hierarchical)  Resolution\Workaround (Server restarts)  Root Cause (Server in hang
status)  Sending final notification to business and customer  Confirming the solution and closing
the ticket\