Q.SQL is useful in databases because it makes managing and working with data easy.
Here are some advantages:
1. Common language: SQL is widely used and works with most databases
2. Easy to use: The commands are simple and similar to English, so it’s easy to
learn.
3. Fast data handling: SQL allows you to quickly find, add, or change data in big
databases.
4. Works everywhere: You can use SQL with different database systems on various
platforms.
5. Keeps data safe and correct: It helps you control who can access the data and
ensures the data stays accurate.
6. Handles big data well: SQL is built to manage large amounts of data efficiently.
These features make SQL a powerful tool for managing and organizing data in
databases.
Q.PL/SQL is a programming language used with Oracle databases that makes SQL
more powerful. Here are some features:
1. Programming Power: It lets you use programming tools like loops and conditions,
not just simple queries.
2. Organized Code: PL/SQL organizes your code into blocks, making it easier to read
and manage.
3. Handles Errors: You can catch and fix errors within your code.
4. Faster Performance: It runs several SQL statements together in one go, making
things faster.
5. Reusable Code: You can create stored procedures (reusable code blocks) to run
whenever you need.
6. Automatic Actions: You can set triggers to automatically perform actions when
data is added or changed.
7. SQL Integration: PL/SQL works perfectly with SQL, so you can easily mix SQL
queries with your code.
8. Security: It helps keep data safe by controlling who can do what with the data.
These features make it easier to build smarter and more efficient database
applications.
Q. Explain the architecture of PLSQL
1. PL/SQL Engine: This is the core part that processes your PL/SQL code. It reads and
runs your code, and interacts with the database.
2. SQL Engine: When your PL/SQL code has SQL queries, it hands them over to the SQL
engine, which does the actual work of fetching or updating data in the database.
3. Memory Management: To make things faster, Oracle saves some processed queries
in shared memory, so the same query doesn’t need to be reprocessed every time.
4. Stored Code: You can save your PL/SQL code in the database as procedures,
functions, or packages, so you can reuse it without writing it again.
5. Error Handling: PL/SQL has a way to catch and handle errors, so you can write code to
handle problems if something goes wrong while it's running.
6. Security: Only authorized users can run certain PL/SQL code, ensuring security
around data access and operations.
In short, PL/SQL combines SQL with programming logic to allow more control, flexibility,
and efficiency when working with an Oracle database.
Q. Explain various data types used in SQL
1. INT: Stores whole numbers (integers), like 1, 2, 100, or -50. It's used for counting or
any numerical data without decimal points.
2. FLOAT / DOUBLE: Stores numbers with decimal points, like 3.14 or 25.99. These are
used when you need more precision, such as for prices or measurements.
3. VARCHAR (n): Stores text (characters), like names or email addresses. You define a
maximum length (n), and it can store anything from a short word to a long sentence,
but not longer than the specified limit.
4. CHAR (n): Similar to VARCHAR, but always takes up the full specified length (n), even
if the text is shorter. If you specify CHAR(10) and store "hello," it will take up 10 spaces
(padded with extra spaces).
5. DATE: Stores a date in the format YYYY-MM-DD, like 2024-10-16. Useful for recording
birthdays, deadlines, etc.
6. DATETIME: Stores both date and time in the format YYYY-MM-DD HH:MM:SS. It's used
when you need to track exact moments, like timestamps for logging events.
Q.What is a Cursor in PL/SQL?
A cursor is like a pointer that allows you to retrieve and manipulate data row by row from a
result set (the data returned by a SQL query). Think of it as a bookmark that keeps track of
where you are in the data, so you can process each row individually.
Imagine you have a list of names on a piece of paper. A cursor is like your finger moving
down the list, pointing to one name at a time so you can read or work with each name
separately.
Types of Cursors in Databases
There are mainly two types of cursors in PL/SQL and databases:
1. Implicit Cursors
2. Explicit Cursors
* 1. Implicit Cursors
Automatic Management: You don’t have to declare or manage them yourself.
Basic Information: They provide simple information about the execution, such as the
number of rows affected.
2. Explicit Cursors
Manual Control: You declare the cursor, open it, fetch each row, and then close it.
Flexibility: Useful for complex data processing where you need to handle each row
individually.
Q.Compare between Static and Dynamic SQL.
What is Static SQL?
Static SQL is when the SQL queries are written directly in your program and do not change.
The query is known in advance (before you run the program).
*Fixed Query: The SQL statement is fixed and doesn’t change during execution.
*Precompiled: The database can optimize the query before running the program.
*Safer: Less prone to SQL injection attacks because the query is set in the code.
*Faster Performance: Since the query is compiled and optimized once, it runs faster.
Q.What is Dynamic SQL?
Dynamic SQL is when the SQL queries are generated or modified at runtime,
meaning the query can change depending on different conditions or inputs
during the program’s execution.
Flexible Query: The SQL statement can change dynamically based on user
input or other conditions.
Built at Runtime: The query is constructed as the program runs, and the
database interprets and executes it on the fly.
More Vulnerable: If not handled properly, dynamic SQL can be vulnerable to
SQL injection attacks.
More Overhead: Since the query is constructed and executed at runtime, it
takes longer because the database must compile and optimize it each time.
Q.What is a Trigger?
Think of a trigger like an alarm system. Just like an alarm goes off when
someone opens a door, a trigger runs automatically when certain actions
happen in the database. You set the trigger to react to certain events, such
as:
Before or after inserting new data into a table.
Before or after updating existing data.
Before or after deleting data.
Types of Triggers:
1. Before Trigger – This runs before the event happens, like checking the
data before inserting it into the table.
2. After Trigger – This runs after the event happens, like logging the change
after data is updated.
3. Instead of Trigger – This can be used to perform a different action instead
of the usual one, for example, to handle changes in a view (a virtual table).
Q.What is a Package?
A package in PL/SQL has two main parts:
1. Package Specification (Spec):
It’s like the interface or a catalog of tools in the package.
It declares all the procedures, functions, variables, etc., that are
available for use.
Other parts of your program can see and use the objects in the
package spec.
2. Package Body:
This contains the actual implementation or the code of the
procedures and functions declared in the package spec.
You can update the code in the package body without affecting
the package spec.
Why Do We Need a Package in a Database?
1. Organization: Packages help organize related procedures and
functions into a single unit, making your code more structured
and easier to manage. For example, all employee-related
functions can be grouped into one package called employee_pkg.
2. Code Reusability: Once you write a package, you can reuse it
throughout your application. This reduces the need to write the
same code again and again.
3. Encapsulation: A package allows you to hide the details of your
code. You can expose only what is necessary through the package
spec, while keeping the actual code in the package body private.
Q. What is Deadlocks?
A deadlock in a database occurs when two or more transactions
are stuck waiting for each other to release resources, preventing
them from proceeding. It’s like a situation where two people are
trying to go through a narrow door at the same time, but both
refuse to step back to let the other pass, so neither can move.
Q. What is Lexical Units ?
lexical units refer to the smallest elements or tokens that are
used when processing a query in a database language like SQL.
They are the basic building blocks of the language, such as:
Keywords (e.g., SELECT, FROM, WHERE)
Identifiers (e.g., table names, column names)
Literals (e.g., numeric values, strings)
Operators (e.g., =, >, AND)
Punctuation symbols (e.g., commas, parentheses)
These units are identified during the first phase of query
processing called lexical analysis. The system breaks down the
user's input into these smaller parts for easier interpretation.
Q. What is Implicit Cursors?
implicit cursor is a type of cursor that is automatically created and
managed by the database whenever an SQL query (like SELECT,
INSERT, UPDATE, or DELETE) is executed. You don't need to
explicitly declare or open it, which makes working with implicit
cursors much simpler for basic tasks.
