Monday, August 7, 2023

PRIMARY KEY vs FOREIGN KEY vs CHECK Constraint vs UNIQUE Constraint vs NOT NULL Constraint

Primary keys, foreign keys, check constraints, unique constraints, and not null constraints are all used to enforce data integrity in a relational database. However, they each serve different purposes.

Primary keys are unique identifiers for rows in a table. They cannot be null and must be unique within the table. A primary key constraint ensures that each row in a table has a unique value in the primary key column. This helps to prevent duplicate data and makes it easier to identify individual rows in the table.

Foreign keys are used to relate two tables together. They ensure that the values in a foreign key column in one table match the values in the primary key column of another table. This helps to maintain referential integrity between the two tables. For example, if you have a table of customers and a table of orders, you could use a foreign key constraint to ensure that the customer ID in the orders table matches the customer ID in the customers table. This would prevent you from creating an order for a customer that does not exist.

Check constraints are used to restrict the values that can be stored in a column. For example, you could use a check constraint to ensure that the values in a column are always positive integers. Check constraints can be used to enforce business rules and to prevent invalid data from being entered into the database.

Unique constraints are similar to primary keys, but they do not have to be unique across the entire table. They only have to be unique within a group of rows. For example, you could use a unique constraint to ensure that each customer ID in a table is unique within a specific state. Unique constraints can be used to prevent duplicate data and to make it easier to identify individual rows in the table.

Not null constraints are used to ensure that a column cannot have a null value. This can be useful for columns that are essential for identifying rows in the table, such as the primary key column. Not null constraints can also be used to enforce business rules, such as ensuring that a customer must have a name.

Constraint	Description
Primary key		A unique identifier for rows in a table. It cannot be null and must be unique within the table. A primary key constraint ensures that each row in a table has a unique value in the primary key column. This helps to prevent duplicate data and makes it easier to identify individual rows in the table.
Foreign key		Used to relate two tables together. It ensures that the values in a foreign key column in one table match the values in the primary key column of another table. This helps to maintain referential integrity between the two tables. For example, if you have a table of customers and a table of orders, you could use a foreign key constraint to ensure that the customer ID in the orders table matches the customer ID in the customers table. This would prevent you from creating an order for a customer that does not exist.
Check constraint		Used to restrict the values that can be stored in a column. For example, you could use a check constraint to ensure that the values in a column are always positive integers. Check constraints can be used to enforce business rules and to prevent invalid data from being entered into the database.
Unique constraint		Similar to primary keys, but they do not have to be unique across the entire table. They only have to be unique within a group of rows. For example, you could use a unique constraint to ensure that each customer ID in a table is unique within a specific state. Unique constraints can be used to prevent duplicate data and to make it easier to identify individual rows in the table.
Not null constraint		Used to ensure that a column cannot have a null value. This can be useful for columns that are essential for identifying rows in the table, such as the primary key column. Not null constraints can also be used to enforce business rules, such as ensuring that a customer must have a name.

Statement	Description
DROP TABLE	Completely removes the table from the database, including the table structure and all data.
TRUNCATE TABLE	Removes all data from the table, but does not remove the table structure.
DELETE	Removes specific rows from a table.

FLASHBACK vs roll back

FLASHBACK vs Roll back

Flashback should be used to undo changes that have been made outside of a transaction, while rollback should be used to undo changes that have been made within a transaction. However, the specific command that you use will depend on the specific situation.

Feature	Flashback	Rollback
Transactional	No	Yes
Can be used to undo changes made outside of a transaction	Yes	No
Can be used to restore the database to a specific point in time	Yes	No
Can be used to restore the database to the most recent checkpoint	Yes	No
More powerful tool for recovering from errors	Yes	No
Can be used to maliciously alter the state of the database	Yes	No

Oracle and SQL are both database management systems (DBMS).

Oracle is a commercial DBMS that is owned and developed by Oracle Corporation. It is a mature and robust DBMS that is used by many large organizations. Oracle is known for its scalability, performance, and security.

SQL (Structured Query Language) is a programming language that is used to manage data in a database. SQL is a standard language that is supported by all major DBMSs, including Oracle. SQL is used to perform tasks such as creating, querying, updating, and deleting data in a database.

In short, Oracle is a DBMS, while SQL is a programming language that is used to manage data in a database.

Differences Between Some of The Most Popular Oracle Versions:

Version	Release Date	Features
Oracle 12c	2013	In-memory columnar database, JSON support, and pluggable database
Oracle 11g	2007	Real Application Clusters (RAC), Oracle Data Guard, and Partitioning
Oracle 10g	2004	Data Pump, Flashback technology, and Transparent Data Encryption (TDE)
Oracle 9i	2001	XML support, Oracle Streams, and Partitioning
Oracle 8i	1997	Object-Relational features, Oracle Streams, and Partitioning

Feature	SQL Server	SQL
Type	Commercial RDBMS	Programming Language
Owner	Microsoft	ANSI
Scalability	Very scalable	Scalable
Performance	Very high performance	High performance
Security	Very secure	Secure
Supported Platforms	Windows, Linux, macOS	All major platforms
Cost	Commercial license required	Free to use

SQL Server is a commercial RDBMS that is developed and marketed by Microsoft. It is a powerful and scalable database system that is used by many large organizations. SQL Server is known for its high performance, reliability, and security.

User-defined functions (UDFs)

CREATE FUNCTION dbo.Fibonacci (@n INT) RETURNS INT AS BEGIN IF @n = 0 RETURN 0; ELSEIF @n = 1 RETURN 1; ELSE RETURN dbo.Fibonacci(@n - 1) + dbo.Fibonacci(@n - 2); END;

This function takes an integer as input and returns the Fibonacci number for that input. For example, the following query will return the 5th Fibonacci number:

SELECT dbo.Fibonacci(5);

This query will return the value 5.

UDFs can be used to perform a variety of tasks, such as:

Calculating complex formulas
Formatting data
Validating data
Encrypting and decrypting data
Sending email
Making web service calls

UDFs can be very useful for improving the performance and scalability of your database applications. Here are some tips for using UDFs effectively:

Only create UDFs when necessary. If you can avoid creating a UDF, then do so.
Make sure your UDFs are efficient. UDFs should be as efficient as possible to avoid slowing down your database application.
Test your UDFs thoroughly. Make sure your UDFs work as expected for all possible inputs.
Document your UDFs. Document your UDFs so that other developers can understand how to use them.

===========

Cursor is a temporary work area that is used to iterate through a set of data. Cursors can be used to perform complex operations on large datasets, but they can also slow down your database application if they are not used carefully.

DECLARE @cursor CURSOR FOR SELECT * FROM Customers; OPEN @cursor; FETCH NEXT FROM @cursor INTO @customer_id, @customer_name, @customer_address; WHILE @@FETCH_STATUS = 0 BEGIN PRINT @customer_id; PRINT @customer_name; PRINT @customer_address; FETCH NEXT FROM @cursor INTO @customer_id, @customer_name, @customer_address; END; CLOSE @cursor; DEALLOCATE @cursor;

This query declares a cursor named @cursor that selects all rows from the Customers table. The OPEN statement opens the cursor, and the FETCH NEXT statement fetches the first row from the cursor. The WHILE loop iterates through the cursor, printing the values of the customer_id, customer_name, and customer_address columns for each row. The CLOSE statement closes the cursor, and the DEALLOCATE statement deallocates the cursor.

Cursors can be very useful for complex operations on large datasets. However, they can also slow down your database application if they are not used carefully. Here are some tips for using cursors effectively:

Only use cursors when necessary. If you can avoid using a cursor, then do so.
Use a cursor only for a limited number of rows. If you need to iterate through a large number of rows, then consider using a stored procedure or a trigger instead.
Close and deallocate the cursor as soon as you are finished using it. This will free up resources and improve performance.
Use the FETCH NEXT statement instead of the FETCH ALL statement. The FETCH ALL statement will fetch all rows from the cursor into memory, which can slow down your database application.

Stored procedures are a collection of SQL statements that are saved as a single unit. They can be executed by name, which can improve performance by reducing the number of times that the database has to parse and execute SQL statements. Stored procedures can also be used to encapsulate business logic, which can make your code more modular and reusable.

Triggers are special stored procedures that are executed automatically when a specific event occurs, such as a row being inserted, updated, or deleted. Triggers can be used to enforce data integrity, perform complex calculations, or send notifications.

Cursors are temporary work areas that are used to iterate through a set of data. Cursors can be used to perform complex operations on large datasets, but they can also slow down your database application if they are not used carefully.

Differences between stored procedures, triggers, and cursors:

Feature	Stored procedures	Triggers	Cursors
Purpose	To encapsulate business logic and improve performance	To enforce data integrity and perform complex calculations	To iterate through a set of data
Execution	Manual or automatic	Automatic	Manual
Performance	Can improve performance, especially for complex queries	Can slow down performance if not used carefully	Can slow down performance if not used carefully
Portability	Portable, can be used with any relational database	Not portable, only works with SQL Server	Not portable, only works with SQL Server

Realtime Activities DBA

Examples of real-time activities that a DBA might perform:

Monitoring database performance: DBAs use a variety of tools to monitor database performance, such as CPU usage, memory usage, disk I/O, and network traffic. They use this information to identify any potential problems and take corrective action.
Tuning database queries: DBAs use a variety of techniques to tune database queries, such as optimizing the query plan, using indexes, and partitioning tables. This can help to improve the performance of queries and reduce the load on the database.
Backing up and restoring databases: DBAs are responsible for backing up databases on a regular basis. This ensures that the data can be restored in the event of a disaster.
Monitoring database security: DBAs use a variety of tools to monitor database security, such as auditing database activity and configuring permissions. This helps to protect the data from unauthorized access.
Managing database users: DBAs are responsible for creating and managing database users. This includes assigning permissions to users and managing their passwords.
Monitoring database logs: DBAs use database logs to track activity in the database. This information can be used to troubleshoot problems and identify any potential security threats.

RESTORE: The RESTORE statement is used to restore a database from a backup. The following is an example of a RESTORE statement:

SQL

RESTORE DATABASE my_database

FROM DISK = '/path/to/my_database.bak';

This statement will restore the database my_database from the backup file /path/to/my_database.bak.

BACKUP: The BACKUP statement is used to create a backup of a database. The following is an example of a BACKUP statement:

SQL

BACKUP DATABASE my_database

TO DISK = '/path/to/my_database.bak';

This statement will create a backup of the database my_database and save it to the file /path/to/my_database.bak.

ROLLBACK: The ROLLBACK statement is used to roll back a transaction. The following is an example of a ROLLBACK statement:

SQL

ROLLBACK;

This statement will roll back the current transaction. If there are no uncommitted changes, the database will be restored to its state before the transaction started.

The following is an example of a ROLLBACK statement that rolls back a transaction to a specific point in time:

SQL

ROLLBACK TO '2023-08-07 12:00:00';

FLASHBACK a table to a specific point in time

FLASHBACK TABLE my_table TO TIMESTAMP '2023-08-07 12:00:00';

This statement will flashback the table my_table to the state it was in at 12:00:00 on August 7, 2023.

FLASHBACK TABLE my_table TO SCN 1234567890;

This statement will flashback the table my_table to the state it was in at the checkpoint with ID 1234567890.

=========

To check the status of the database:

SQL

SELECT status FROM v$database;

To check the number of users connected to the database:

SQL

SELECT COUNT(*) FROM v$session;

To check the top 10 queries that are consuming the most CPU time:

SQL

SELECT sql_id,

executions,

total_elapsed_time,

avg_elapsed_time

FROM v$sql

ORDER BY total_elapsed_time DESC

LIMIT 10;

To check the top 10 tables that are consuming the most disk space:

SQL

SELECT table_name,

sum(bytes) AS total_bytes

FROM dba_data_files

GROUP BY table_name

ORDER BY total_bytes DESC

LIMIT 10;

To check the top 10 users that are consuming the most memory:

SQL

SELECT username,

sum(bytes) AS total_bytes

FROM v$process

GROUP BY username

ORDER BY total_bytes DESC

LIMIT 10;

To check the number of errors that have occurred in the database in the last 24 hours:

SQL

SELECT COUNT(*) FROM dba_errors

WHERE timestamp > SYSDATE - INTERVAL '1 DAY';

To check the current wait events for all users:

SQL

SELECT username,

event,

max_wait_time,

time_waited

FROM v$session_waits;

To check the current wait events for a specific user:

SQL

SELECT username,

event,

max_wait_time,

time_waited

FROM v$session_waits

WHERE username = 'username';

====================

SELECT status FROM v$database;
SELECT COUNT(*) FROM v$session;
SELECT sql_id, executions, total_elapsed_time, avg_elapsed_time FROM v$sql ORDER BY total_elapsed_time DESC LIMIT 10;
SELECT table_name, sum(bytes) AS total_bytes FROM dba_data_files GROUP BY table_name ORDER BY total_bytes DESC LIMIT 10;
SELECT username, sum(bytes) AS total_bytes FROM v$process GROUP BY username ORDER BY total_bytes DESC LIMIT 10;
SELECT COUNT(*) FROM dba_errors WHERE timestamp > SYSDATE - INTERVAL '1 DAY';
SELECT username, event, max_wait_time, time_waited FROM v$session_waits;
SELECT username, event, max_wait_time, time_waited FROM v$session_waits WHERE username = 'username';
SELECT sql_id, event, total_elapsed_time, avg_elapsed_time FROM v$sql WHERE sql_id = 'sql_id';
SELECT count(*) FROM dba_tables WHERE tablespace_name = 'tablespace_name';
SELECT sum(bytes) FROM dba_data_files WHERE tablespace_name = 'tablespace_name';
SELECT username, count(*) FROM v$session WHERE username = 'username';
SELECT sql_id, count(*) FROM v$sql WHERE sql_id = 'sql_id';
SELECT event, count(*) FROM v$session_waits GROUP BY event;
SELECT event, max_wait_time, time_waited FROM v$session_waits WHERE event = 'event';
SELECT sql_id, count(*) FROM v$sql WHERE sql_text LIKE '%keyword%';
SELECT username, count(*) FROM v$session WHERE username LIKE '%keyword%';
SELECT event, count(*) FROM v$session_waits WHERE event LIKE '%keyword%';
SELECT sql_id, event, total_elapsed_time, avg_elapsed_time FROM v$sql WHERE sql_text LIKE '%keyword%';
SELECT username, event, total_elapsed_time, avg_elapsed_time FROM v$session_waits WHERE username LIKE '%keyword%';

Quick Mind Info

Monday, August 7, 2023

URLs for learning SQL server

PRIMARY KEY vs FOREIGN KEY vs CHECK Constraint vs UNIQUE Constraint vs NOT NULL Constraint

FLASHBACK vs roll back

FLASHBACK vs Roll back

Oracle and SQL are both database management systems (DBMS).

Differences Between Some of The Most Popular Oracle Versions:

User-defined functions (UDFs)

Cursor is a temporary work area that is used to iterate through a set of data. Cursors can be used to perform complex operations on large datasets, but they can also slow down your database application if they are not used carefully.

Differences between stored procedures, triggers, and cursors:

Realtime Activities DBA

Realtime Activities DBA