Database & SQL

 

www.tritechtrainings.com

Database Management System & SQL Blog

A Database is a shared collection of logically related data and description of these data, designed to meet the information needs of an organization

A Database Management System is a software system that enables users to define, create, maintain, and control access to the database. Database Systems typically have high cost and they require high-end hardware configurations.

Data is stored in flat files and can be accessed using any programming language. The file-based approach suffers the following problems:

1. The dependency of a program on the physical structure of data
2. The complex process to retrieve data
3. Loss of data on concurrent access
4. Inability to give access based on record (Security)
5. Data redundancy

Arithmetic Operators

Operator	Symbol	Usage	Result
Addition	+	15 + 5	20
Subtraction	-	15 - 5	10
Multiplication	*	15 * 5	75
Division	/	15 / 5	3

Comparison Operators

Operator	Symbol	Usage	Result
Equal to	=	15 = 5	false
Not equal to	<>	15 <> 5	true
Greater than	>	15 > 5	true
Greater than equal to	>=	15 >= 5	true
Less than	<	15 < 5	false
Less than equal to	<=	15 <= 5	false

There is one important difference between Equal To comparison operators in programming languages and SQL. 

While SQL uses a single '=', programming languages typically use double '=' to distinguish it from the assignment operator.

Operation	Python Operator	SQL Operator
Assignment	=	=
Equality check	==	=

CREATE TABLE statement is used to create a table in a database. Database tables are organized into rows and columns. 

Each table must have a name and can have any number of columns (minimum 1 column is required).

Each column must have a data type which determines the type of values that can be stored. 

CREATE TABLE command will fail if a table is already existing in the database with the same name.

All tables must have a unique name.

DROP TABLE statement is used to remove an existing table from the database.

Ex:

CREATE TABLE Student (
    StudentId INTEGER,
    FName VARCHAR2(10), 
    Gender CHAR(1), 
    Attendance DATE);

DROP TABLE Student;

Constraints

Constraints are typically specified along with the CREATE TABLE statement. Constraints are classified into multiple types based on the number of columns they act upon as well as on the way they are specified.

Various constraints that can be created on database tables are:

• NOT NULL
• PRIMARY KEY
• CHECK
• UNIQUE
• FOREIGN KEY

Table level constraint can be specified after all columns used in the constraint have been defined. It is not necessary to specify them after all columns in the table are defined. Composite constraints can only be specified as table-level constraints.

We can also specify the DEFAULT value for a column. Oracle database does not consider DEFAULT as a constraint.

NOT NULL Constraint

By default, a column can hold NULL values. The NOT NULL constraint enforces a column to NOT accept NULL values. This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.

Primary Key

The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).

Check Constraint

The CHECK Constraint enables a condition to check the value being entered into a record. If the condition evaluates to false, the record violates the constraint and isn't entered the table.

Unique Constraint

The UNIQUE constraint ensures that all values in a column are different. Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint. However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

Foreign Key

A FOREIGN KEY is a key used to link two tables together. A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table. The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.