Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts

Friday, January 12, 2024

SQL Indexes | Types of indexes | Creating an Index | Dropping an Index | OLAP | LOCKS | Basics of Oracle SQL | SQL

 

INDEXES

What is an Index?

  • An index is a schema object.
  • Is used by the Oracle server to speed up the retrieval of rows by using a pointer.
  • Can reduce disk I/O by using a rapid path access method to locate data quickly. 
  •  Is independent of the table it indexes. 
  •  Is used and maintained automatically by the Oracle server.

Note: When you drop a table, corresponding indexes are also dropped.

LISTAGG Funcation | Basics of Oracle SQL | Oracle SQL | SQL

 

Listagg

What is the Listagg?

The LISTAGG function is used to aggregate a set of string values within a group into a single string by appending the string-expression values based on the order that's specified in the 'WITHIN GROUP' clause.

As a single-set aggregate function, LISTAGG operates on all rows and returns a single output row.

SQL Sequences | Nextval | Currval | Modifying and Dropping a Sequence | Basics of Oracle SQL | Oracle SQL | SQL

 Sequences

A sequence is a user created database object that can be shared by multiple users to generate unique integers.
A typical usage for sequences is to create a primary key value, which must be unique for each row.
The sequence is generated and incremented (or decremented) by an internal Oracle routine.
Sequence numbers are stored and generated independently of tables. Therefore, the same sequence can be used for multiple tables.
A sequence Speeds up the efficiency of accessing sequence values when cached in memory
A sequence Replaces application code.
 

SQL Views | Types Of Views | Simple View | Complex View | Materialized View | Inline View

Views

What is a View?

  •    A view is a logical table based on a table or another view.4
  •    A view contains no data of its own but is like a window through which data from tables can          be viewed or changed.
  •    The tables on which a view is based are called base tables. The view is stored as a SELECT                 statement in the data dictionary.
  •     We can present logical subsets or combinations of data by creating views of tables.

SQL Clauses | Group By Clause | Having Clause | Order By Clause | Distinct Clause | With Clause | Asc | Desc | Oracle SQL | SQL

 CLAUSES

Clauses:

•       Group by Clause

•       Having Clause

•       Order by Clause

•       Distinct Clause

•       With Clause

SQL Sub-Queries| Types of Sub-Queries | Non-correlated Sub-Query | Correlated Sub-Query| Exists | Non-Exists | Oracle SQL | SQL

 Sub Queries

Using a Sub-query :

Main Query: Which employees have salaries greater than Abel’s salary?

Subquery: What is Abel’s salary?

 Sub-query syntax :

The subquery (inner query) executes once before the main query.

The result of the subquery is used by the main query (outer query).

SQL Loader | Basics of Oracle SQL | Oracle SQL | SQL


SQL Loader

SQL*Loader allows you to load data from an external file into a table in the database. It can parse many delimited file formats such as CSV, tab-delimited, and pipe-delimited.

SQL*Loader provides the following methods to load data:

•       Conventional path loads – construct INSERT statements from the contents of the input data file based on the predefined specification and execute the inserts.

•       Direct path loads – creates data blocks in Oracle database block format from the data file and directly write the data block to the database. This way is much faster than the conventional path but subject to some restrictions.

•       External table loads – create an external table for the data stored in the data file and execute INSERT statements to insert the data from the data file into the target table. The external table loads support parallel loading if data file is big enough.

 

SQL Pesudo Columns | Rowid |Rownum | Oracle SQL | SQL

 

Pseudo Columns

  1. ROWID:

•       ROWID is nothing but the physical memory location on which that data/row is stored. ROWID basically returns address of row.

•       ROWID uniquely identifies row in database.

•       ROWID is combination of data object number, data block in data file, position of row and datafile in which row resides.

SQL Synonyms | Synonyms | Private Synonym | Public Synonym | SQL | Oracle SQL

 

Synonyms

Creating and Dropping Synonyms

•       Creating a synonym eliminates the need to qualify the object name with the schema and provides you with an alternative name for a table, view, sequence, procedure, or other objects.

Syntax:

                CREATE [PUBLIC] SYNONYM synonymname

                FOR object;

SQL Constraints | NOTNULL | UNIQUE | PRIMARY KEY | FOREIGN KEY | On Delete Cascade | CHECK | Basics of Oracle SQL | Oracle SQL | SQL

 Constraints
What are Constraints?
Constraints enforce rules at the table level.
Constraints prevent the deletion of a table if there are dependencies.
The following constraint types are valid:
      Constraint                                Description
     NOT NULL             :     Specifies that the column cannot contain a null value.

     UNIQUE                 :     Specifies a column or combination of columns whose values must be unique
                                           for  all rows in the table.

     PRIMARY KEY        :     Uniquely identifies each row of the table.

     FOREIGN KEY         :     Establishes and enforces a foreign key relationship between  the column and
                                            a column of the referenced table..

     CHECK                     :     Specifies a condition that must be true.

Thursday, January 11, 2024

SQL Joins | Equi Join | Inner Join | Non-equi Join | Outer Joins | Self Joins |

 

Joins

What are Joins?

A join is a query that combines rows from two or more tables, views, or materialized views.

Oracle Database performs a join whenever multiple tables appear in the FROM clause of the query.

The select list of the query can select any columns from any of these tables.

If any two of these tables have a column name in common, then you must qualify all references to these columns throughout the query with table names to avoid ambiguity.

Operators | Arithmetic Operators | Relational operators | Logical Operators | Special Operators | Extended Comparisons| Clause | AND| OR | NOT | BETWEEN | IN | LIKE | IS NULL

OPERATORS 

Arithmetic Operators:

•       Arithmetic Operators are used in number datatype column.

      • Addition(+).
      • Subtraction (-).
      • Division(/).
      • Multiplication(*).
      • Modulus(%).
  • We can also use Arithmetic operator in “WHERE” conditions.

Set Operators | Union | Union All | Intersect | Minus | Oracle SQL

Set Operators

Set operators are used to combine the results of two or more SELECT queries. These operators allow you to perform set operations on the result sets, such as union, intersection, and difference. The main set operators in Oracle SQL are:

UNION:

 

  • Combines the results of two SELECT statements, removing duplicate rows.

Multi Row Functions | Aggregate functions | Types of Multiple Row Functions in Oracle | Avg | Count | Max | Min | Sum

Multi Row Functions / Aggregate Functions

Multiple Row Functions in Oracle

 SQL Functions:

What is a function?

Functions are similar to operators in that they manipulate data items and return a result. Functions differ from operators in the format of their arguments. This format enables them to operate on zero, one, two, or more arguments:

Syntax: function (argument, argument, ...)

SQL Languages | Data Definition Language (DDL) | Data Manipulation Language (DML) | Data Control Language (DCL) | Transaction Control Language (TCL) | Data Retrieval Language (DRL) | Data Query Language (DQL)

 SQL Languages

•       Data Definition Language (DDL)

•       Data Manipulation Language (DML)

•       Data Control Language (DCL)

•       Transaction Control Language (TCL)

•       Data Retrieval Language (DRL) / Data Query Language (DQL)