Basic SQL Fundamental

The SQL was initially called SEQUEL (Structured English Query Language). The acronym SEQUEL was later changed to SQL because "SEQUEL"

Relational Database – The Relational Database uses relations or two dimensional tables to store information.

ER Modeling – An entity relationship (ER) model is an illustration of various entites in a business and the relationships among them.

Entity – A thing of significance about which information needs to be known. Examples are students, departments, employees.

Attribute/Column – Something that describes or qualifies an entity. For example employee entity has emp no, name, job title, hire date, etc. attributes.

Relationship – A named association between entities showing optionality and degree.

Field – A field is the smallest unit in a table.

Record/Row/Tuple – A record is nothing more than a collection of fields or columns horizontally.

Database Objects

Schema – Collection of objects is called Schema. For example table, view, index, sequence.

Table – Logical structures of rows and columns. Or/ Basic storage structures of an RDBMS.

Or/ A table holds a collection of records. A table can have upto 1000 columns and ‘n’ number of rows.

Views – Virtual table which does not contain any data that refers to the base-table.

Index – Pointer for faster retrieval of data.

Sequence – It is a unique sequential number generated by database objects.

Synonyms – Alternative name of the database.

Constraints

Rules which are enforce on the data when we are inserting data in database.

Not Null(C)-  It should not be blank in the columns.

Unique(U)- It should not be repeated value.

Primary Key(P)- It is a combination of not null & unique. And should not be blank & repeated values.

Foreign Key(R)- It is use to define to relates table, we should enter a valid data except null.

Check(C) – It is condition if condition is satisfied we can insert data into database.

SQL Statements

Data Manipulation Language(DML) – It retrieves data from the database, enters new rows, changes existing rows, and removes unwanted rows from tables in the database. It consists of SELECT, INSERT, UPDATE, DELETE, MERGE.

Data Definition Language(DDL) – It sets up, changes and removes data structures from tables. It consists of CREATE, ALTER, DROP, RENAME, TRUNCATE, COMMENT.

Data Control Language(DCL) – It gives of removes access rights to both the Oracle Database and the structures within it. It consists of GRANT and REVOKE.

Transaction Control Language(TCL) – It manages the changes made by the DML statements. Changes to the data can be grouped together into logical transactions. It consists COMMIT, ROLLBACK, SAVEPOINT.

Data Manipulation Language(DML)

Data Manipulation Language(DML) is a core part of SQL. When you want to add, update, or delete data in the database, you execute a DML statement. A collection of DML statements that form a logical unit of work is called a transaction.

Capabilities of SQL Select Statement

The select statement retrieves information from the database.

Capabilities of SQL Select Statement

# Selection- Choose the rows in a table that are returned by a query. Various criteria can be used to restrict the rows that are retrieved.

# Projection- Choose the columns in a table that are returned by a query. Choose as few or as many of the columns as needed.

# Join- Bring together data that is stored in different tables by specifying the link between them. SQL joins are used to display data from multiple tables.

Select Statement Format:

## Select * |{[DISTICT] column | expression [alias],….}

FROM table;

Where DISTICT- All retrieved records are of different values.

Null Values

If a row lacks a data value for a particular column, that value is said to be null or to contain a null. A null is a value that is unavailable, unassigned, unknown, or inapplicable. A null is not the same as a zero or a space. Aero is a number and a space is a character.

Column Alias

An alias renames a column heading. It immediately follow the column names follow by AS.

Insert Statements

It is used to add new rows to a table by using the INSERT statement.

Syntax: INSERT INTO TABLE ([column, ……,.., n] VALUES [value,….., n]);         

Update Statements

It is used to modify the existing rows in a table.

Syntax:

UPDATE TABLE

SET COLUMN = VALUE

WHERE CONDITION;

Delete Statements

It is used to remove existing rows from a table.

Syntax:

DELETE  TABLE;

Row deletion from a table

# Specific rows are deleted if you specify the WHERE clause as

Syntax:

DELETE FROM TABLE

WHERE CONDITION;

It deletes all the rows satisfying the CONDITION.

# All rows in the table are deleted if you omit the WHERE clause as

Syntax:

DELETE FROM TABLE;

It deletes all the rows from the table.

Truncate Statement

It is a data definition language(DDL) statement. It removes all rows from a table, leaving the table empty and the table structure intact.

Syntax: TRUNCATE TABLE