What is the purpose of a stored procedure?

Vaibhav Kothia May 26, 2023 0 Comments

A stored procedure is a set of SQL statements that can be stored in a database and reused as a single unit. It is commonly used to encapsulate a set of operations that can be used over and over again in multiple contexts.

For example, a stored procedure could be used to insert data into a table. This procedure could be used every time new data needs to be added to the table, without having to write the same code over and over again. The procedure could be called with a single line of code, which would then execute all the necessary steps to insert the data.

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

Vaibhav Kothia May 26, 2023 0 Comments

A primary key is a special type of unique key that is used to identify a single row in a table. It is usually composed of one or more columns that contain only unique values, and cannot be NULL. For example, a table of employees may have an Employee ID column as its primary key.

A unique key is any combination of columns that contains only unique values. It is used to enforce data integrity and can be composed of one or more columns. For example, a table of customers may have a combination of first and last name columns as its unique key, ensuring that no two customers have the same name.

How do you use the TOP keyword in a SELECT statement?

Vaibhav Kothia May 26, 2023 0 Comments

The TOP keyword is used to limit the number of records returned in a SELECT statement.

For example, if you wanted to select the top 5 records from a table called ‘Customers’, you would use the following query:

SELECT TOP 5 * FROM Customers;

What are the differences between a primary key and a unique key?

Vaibhav Kothia May 26, 2023 0 Comments

A primary key is a field in a table that uniquely identifies each record in the table. It is a combination of a unique index and a not null constraint. For example, a customer table may have a primary key of customer_id.

A unique key is a field in a table that uniquely identifies each record in the table. It does not have to be the primary key, but it must contain unique values. For example, a customer table may have a unique key of email address.

What is the purpose of the FOR XML clause in SQL Server?

Vaibhav Kothia May 26, 2023 0 Comments

The FOR XML clause in SQL Server is used to return query results in XML format. It is useful for applications that require data in XML format.

For example, the following query returns a list of customer names and their respective orders in XML format:

SELECT c.Name, o.OrderNumber
FROM Customers c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID
FOR XML AUTO, ELEMENTS;

The output of this query would be something like this:

John Smith
12345

Jane Doe
54321

What is the purpose of the DDL trigger?

Vaibhav Kothia May 26, 2023 0 Comments

A DDL trigger is a special type of trigger that fires in response to a Data Definition Language (DDL) event. It can be used to audit and control changes to the database schema, such as when a table is modified, or when a user attempts to create or drop a table.

For example, you could create a DDL trigger to log any changes to the database schema, by logging the SQL command that was executed, or by sending an email to the DBA. You could also create a DDL trigger to block certain users from creating or dropping tables, by raising an error when the command is attempted.

What is the purpose of a trigger in Oracle Database?

Vaibhav Kothia May 26, 2023 0 Comments

A trigger is a stored PL/SQL block that is executed automatically when a particular event occurs. Triggers are used to enforce business rules and to maintain data integrity.

For example, you can create a trigger that is fired when an INSERT statement is issued on a particular table. The trigger can check to make sure that the data being inserted meets certain criteria before it is accepted into the table.

What is a schema in Oracle Database?

Vaibhav Kothia May 26, 2023 0 Comments

A schema in Oracle Database is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. For example, a user named “John” would have a schema also named “John”. The schema would contain all of John’s tables, views, sequences, stored procedures, and other database objects.

How do you create a user in Oracle Database?

Vaibhav Kothia May 26, 2023 0 Comments

To create a user in Oracle Database, you need to use the CREATE USER command.

For example:

CREATE USER new_user
IDENTIFIED BY new_password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 10M ON users;

This command will create a new user called “new_user” with a password of “new_password”. It will also assign the user a default tablespace of “users” and a temporary tablespace of “temp”. It will also assign the user a quota of 10MB on the “users” tablespace.

What is the difference between Oracle Database and SQL?

Vaibhav Kothia May 26, 2023 0 Comments

Oracle Database is an enterprise-level relational database management system (RDBMS) developed by Oracle Corporation. It is used to store, manage, and retrieve data for applications. Oracle Database is a powerful and feature-rich database platform that enables organizations to deploy and manage data-driven applications, including online transaction processing (OLTP), data warehousing (DW), and analytics.

SQL (Structured Query Language) is a language used to interact with databases. It is used to create, modify, and query databases. It is used to retrieve, add, delete, and update data stored in the database. For example, an SQL statement can be used to create a table, insert data into the table, update data in the table, or delete data from the table.