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.

How does SQL Server use indexes?

Vaibhav Kothia May 26, 2023 0 Comments

SQL Server uses indexes to quickly locate data without having to search every row in a table every time a query is run. Indexes can be created using one or more columns of a table, providing the basis for both rapid random lookups and efficient access of ordered records.

For example, if you had a table of customer orders, you could create an index on the customer name and order date columns. This would allow you to quickly find all orders for a particular customer, or all orders placed on a particular date.

What is the purpose of the SQL Server Management Studio?

Vaibhav Kothia May 26, 2023 0 Comments

The SQL Server Management Studio (SSMS) is a graphical user interface (GUI) used to manage, configure, and administer all components within Microsoft SQL Server. It provides a comprehensive set of tools for managing, developing, and administering databases and objects within an instance of SQL Server.

For example, SSMS can be used to create and manage databases, tables, views, stored procedures, and functions. It can also be used to manage users and security, as well as to monitor the performance of the SQL Server instance. Additionally, SSMS can be used to develop and debug Transact-SQL scripts, as well as to deploy and configure SQL Server objects.

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 difference between a clustered and a non-clustered index?

Vaibhav Kothia May 26, 2023 0 Comments

A clustered index is a type of index that physically reorders the data in a table when the index is created. This means that when a clustered index is created, the data in the table is physically sorted in the order of the clustered index key. A non-clustered index does not physically reorder the data in a table when the index is created. Instead, a non-clustered index creates a separate structure that contains the index key and a pointer to the data in the table.

For example, if we have a table called ‘Employees’ with the columns ‘EmployeeID’, ‘FirstName’, and ‘LastName’, we could create a clustered index on the ‘EmployeeID’ column. This would cause the table to be physically sorted by the ‘EmployeeID’ column. We could also create a non-clustered index on the ‘LastName’ column. This would create a separate structure that contains the ‘LastName’ column and a pointer to the data in the table. The data in the table would not be physically sorted by the ‘LastName’ column.

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.

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 are the different types of Oracle Database objects?

Vaibhav Kothia May 26, 2023 0 Comments

1. Tables: A table is a collection of related data stored in rows and columns. For example, an Employee table may contain columns for employee ID, name, salary, and department.

2. Views: A view is a virtual table that contains data from one or more tables. For example, a view can be created to display only the employees in the Sales department.

3. Indexes: An index is an optional structure associated with a table to speed up the retrieval of data. For example, an index can be created on the Employee table to quickly look up an employee’s record based on their employee ID.

4. Sequences: A sequence is a database object used to generate unique numeric values. For example, a sequence can be used to generate the next employee ID for a new hire.

5. Synonyms: A synonym is an alias for a database object. For example, a synonym can be created for the Employee table so that it can be referenced using a different name.

6. Stored Procedures: A stored procedure is a set of SQL commands that are stored in the database and can be executed as a single unit. For example, a stored procedure can be used to calculate the total salary for all employees in a given department.

7. Triggers: A trigger is a stored procedure that is executed automatically when a data manipulation event occurs. For example, a trigger can be used to automatically update the salary of an employee when their job title is changed.

How does Oracle Database handle data integrity?

Vaibhav Kothia May 26, 2023 0 Comments

Oracle Database uses a number of features to ensure data integrity, including:

1. Primary Keys: A primary key is a unique identifier for a row in a table. It ensures that each row is uniquely identified and helps to prevent duplicate data.

2. Foreign Keys: A foreign key is a column or group of columns in a table that references a primary key in another table. This helps to ensure that related data is consistent across multiple tables.

3. Constraints: Constraints are used to enforce certain data integrity rules. For example, a NOT NULL constraint prevents null values from being inserted into a column.

4. Triggers: Triggers are code that is executed when certain database events occur. They can be used to check data integrity and enforce business rules.

5. Indexes: Indexes can be used to quickly search for data and ensure that data is stored in a consistent format.

6. Encryption: Data can be encrypted to ensure that it is secure and can only be accessed by authorized users.

How do you create a user in PostgreSQL?

Vaibhav Kothia May 26, 2023 0 Comments

Creating a user in PostgreSQL is a simple process. To create a new user, you use the CREATE USER statement.

For example, to create a new user named ‘joe’ with the password ‘secret’, you would run the following statement:

CREATE USER joe WITH PASSWORD ‘secret’;