ITE 298 adv database

Database

It is simply a collection of structured data that is organized and stored in a way that allows easy access, management, and updating.

Database Management System (DBMS)

It is software that helps you interact with the database to perform operations such as creating, reading, updating, and deleting data.

Relational Database

A relational database is a type of database that organizes data into rows and columns, which collectively form a table where the data points are related to each other.

Table

It is a collection of related data entries and it consists of columns and rows.

Record or Row

It is an individual entry that exists in a table.

Column

It represents a set of data values of a particular type, one for each row of the table.

Field

It is the intersection of a row and a column in a table. It contains the actual value for a specific attribute of a record.

Attribute (in ERD)

In an Entity-Relationship Diagram (ERD), an attribute is a characteristic or property that defines an entity in the model.

Attribute (in Database)

Within the context of a database, the term attribute can mean any property that a database object can possess.

Name (Attribute)

Every column must have a unique name within its table.

Data Type (Attribute)

This defines the kind of data the column can store, such as integers, strings, dates, etc.

Length or Size (Attribute)

For some data types, such as strings, you can specify the maximum length.

Default Value (Attribute)

You can specify a default value the database system uses if no value is explicitly provided when a row is inserted.

Constraints

Constraints are rules that the database system enforces.

NOT NULL (Constraint)

This means the column must always have a value (it can't be NULL).

UNIQUE (Constraint)

This means all values in the column must be unique.

PRIMARY KEY (Constraint)

This means the column is the primary key for the table. Its values uniquely identify each row in the table.

FOREIGN KEY (Constraint)

This indicates that the column is linked to the primary key of another table.

CHECK (Constraint)

This allows you to specify a condition that the values in the column must meet.

Collation

This is a set of rules determining how string data is sorted and compared.

Auto-Increment

This attribute automatically generates a unique number for each row, typically used with a primary key column.

Expressions

Combinations of symbols, operators, values, and functions used to perform calculations, manipulate data, or evaluate conditions.

Null Value

Represents the absence of any value, indicating that the data is unknown or missing.

Blank (Empty) Value

Refers to an empty character or a whitespace character, stored and retrieved like any other character in a text field.

When Does a Column Value Become NULL?

A column value becomes NULL when no value is provided, explicitly set to NULL, or if the column allows NULL.

No Value is Provided

When a row is inserted without explicitly assigning a value to a column, it defaults to NULL.

Explicitly Set to NULL

When an INSERT or UPDATE statement explicitly assigns NULL to the column.

Column Allows NULL

If the column is defined with NULL allowed, it can hold NULL values.

When Does a Column Value Become EMPTY?

A column value becomes empty when explicitly assigned an empty string, user input results in an empty string, or no default value is provided.

Explicitly Assigned an Empty String

When an INSERT or UPDATE statement assigns an empty string.

User Input Results in an Empty String

If the application layer allows saving empty strings as valid input.

No Default Value for a Column

If a column is set to accept empty strings as valid data and no other value is provided.

Structured Query Language (SQL)

A standardized language used to manage and manipulate data within relational databases.

Projection

A certain column from a table is selected to control which columns to display, without filtering rows.

Selection

A subset of rows or records in a table is retrieved once it satisfies a selection condition.

Joining

Data are combined from two or more tables based on one or more common column values.

DDL (Data Definition Language)

Collection of commands required to define the database, e.g., CREATE, ALTER, RENAME, TRUNCATE, DROP.

DQL (Data Query Language)

Retrieves data from tables, e.g., SELECT.

DML (Data Manipulation Language)

Collection of commands required to manipulate the data stored in a database, e.g., UPDATE, INSERT, DELETE.

DCL (Data Control Language)

Collection of commands dealing with user permissions and controls of the database system, e.g., GRANT, REVOKE.

TCL (Transaction Control Language)

Collection of commands required to deal with the transaction of the database, e.g., COMMIT, ROLLBACK, SAVEPOINT.

SELECT statement

The SELECT statement is used to select data from a database.

SELECT statement syntax

SELECT [ALL or * | DISTINCT] column1, column2, ... FROM table_name [WHERE condition] [GROUP BY column1, column2, ...] [HAVING condition] [ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...] [LIMIT number [OFFSET number]];

SELECT clause

SELECT represents the command to retrieve information from the table.

FROM clause

FROM represents the command to identify where the information to gather will come from.

WHERE clause

The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified condition.

GROUP BY clause

The GROUP BY Statement in SQL is used to arrange identical data into groups with the help of some functions.

Aggregate functions

The most commonly used SQL aggregate functions are: MIN() - returns the smallest value within the selected column, MAX() - returns the largest value within the selected column, COUNT() - returns the number of rows in a set, SUM() - returns the total sum of a numerical column, AVG() - returns the average value of a numerical column.

HAVING clause

Allows the filtering of query results based on aggregate functions and groupings.

ORDER BY clause

The ORDER BY statement in SQL is used to sort the fetched data in either ascending or descending according to one or more columns.

LIMIT clause

The LIMIT clause allows you to specify the maximum number of records returned by a query.

Example of SELECT clause

SELECT name, description FROM department_list;

Example of WHERE clause

SELECT * FROM Employees WHERE Position = 'Manager';

Example of GROUP BY clause

SELECT name, SUM(sal) FROM emp GROUP BY name;

Example of HAVING clause

SELECT Department, sum(Salary) as Salary FROM Employee GROUP BY department HAVING Salary >= 70000;

Example of ORDER BY clause

SELECT Roll_no, Name, Address FROM studentinfo ORDER BY 1;

Example of LIMIT clause

SELECT * FROM student LIMIT 3;

Syntax for SELECT with FROM

SELECT column1, column2, ... FROM table_name;

Syntax for GROUP BY

SELECT column1, function_name(column2) FROM table_name GROUP BY column1, column2;

Syntax for HAVING

SELECT column1, function_name(column2) FROM table_name GROUP BY column1, column2 HAVING condition;

Syntax for ORDER BY

SELECT * FROM table_name ORDER BY column_name ASC | DESC;

Syntax for LIMIT in MySQL

SELECT column_name(s) FROM table_name WHERE condition LIMIT number;

Syntax for LIMIT in SQL/MS Access

SELECT TOP number|percent column_name(s) FROM table_name WHERE condition;

Syntax for LIMIT in Oracle 12

SELECT column_name(s) FROM table_name ORDER BY column_name(s) FETCH FIRST number ROWS ONLY;

Aliases

Aliases are the temporary names given to tables or columns for the purpose of a particular SQL query.

Column Aliases

Temporary names for columns in the result set.

Table Aliases

Temporary names for tables used within a query.

Column Alias Syntax

SELECT column_name AS alias_name FROM table_name;

Example of Column Alias

SELECT EmployeeID AS ID FROM Employee; or SELECT EmployeeID AS 'Employee ID' FROM Employee;

column_name

The column name can be defined as the column on which we are going to create an alias name.

alias_name

It can be defined as a temporary name that we are going to assign for the column or table.

AS

It is optional. If you have not specified it, there is no effect on the query execution.

Table Alias Usage

Used when you want to give a table a temporary name for the duration of a query.

Table Alias Example

SELECT c1.CustomerName, c1.Country FROM Customer AS c1, Customer AS c2 WHERE c1.Age = c2.Age AND c1.Country = c2.Country;

SQL AND Operator

Allows you to filter data based on multiple conditions, all of which must be true for the record to be included in the result set.

SQL AND Operator Syntax

SELECT * FROM table_name WHERE condition1 AND condition2 AND ...conditionN;

SQL OR Operator

Displays the records where any one condition is true, i.e. either condition1 or condition2 is True.

SQL OR Operator Syntax

SELECT * FROM table_name WHERE condition1 OR condition2 OR... conditionN;

SQL LIKE Operator

Used with the WHERE clause to search for a specified pattern in a column.

LIKE Operator Case Sensitivity

LIKE operator is case-insensitive by default in most database systems.

LIKE Operator Syntax

SELECT column1, column2, ... FROM table_name WHERE column_name LIKE pattern;

pattern

The pattern to search for, which can include wildcard characters.

BINARY Keyword

Used in MySQL to make the LIKE operator case-sensitive.

Pattern Matching Example

SELECT * FROM products WHERE name LIKE BINARY 'apple%'.

Wildcard %

Represents zero or more characters.

Wildcard _

Represents a single character.

Wildcard []

Represents any single character within brackets.

Wildcard -

Specify a range of characters inside brackets.

Pattern 'a%' Meaning

Match strings that start with 'a'.

Pattern '%a' Meaning

Match strings that end with 'a'.

Pattern 'a__%' Meaning

Match strings that start with 'a' and contain at least 2 more characters.

Pattern '%wow%' Meaning

Match strings that contain the substring 'wow' in them at any position.

Pattern '_wow%' Meaning

Match strings that contain the substring 'wow' in them at the second position.

Pattern '_a%' Meaning

Match strings that contain 'a' at the second position.

SupplierID, Name, and Address

Retrieve SupplierID, Name, and Address from the suppliers table where the supplier name starts with 'Ca'.

Retrieve entire table

Retrieve entire table, where address contains OKHLA.

IN Operator

The IN Operator in SQL is used to specify multiple values/sub-queries in the WHERE clause.

Syntax of IN Operator

SELECT column_name FROM table_name WHERE condition IN (condition_value1, condition_value2 .....);

Example of IN Operator

SELECT * FROM employee WHERE Name IN ('Ram','Suresh');