Database

Relationship in database 

In a database, a "relationship" refers to the connection between data in different tables, allowing you to link related information together.

Types of relationship 

I. One-to-one relationship:

When one record in a table can only be associated with one record in another table, and vice versa. (Example: A person can only have one passport). 

II. One-to-many relationship:

When one record in a table can be associated with multiple records in another table. (Example: A customer can place multiple orders). 

III. Many-to-many relationship:

When multiple records in one table can be associated with multiple records in another table. (Example: A student can enroll in multiple courses, and a course can have many students). 

Referential integrity

Referential integrity in a RDBMS is a rule that ensures the accuracy and consistency of data relationships between tables.

What do you mean by query?
A query is a request for data or information from a database table or combination of tables. Queries can be used to: 

1. Ask specific questions

2. Insert, update, or delete data

3. Perform calculations

4. Combine data from different tables

What is report?
A report is used to display and print your data in an organized manner. It represent the information in your database to display or distribute a summary of data and provide details about individual records.

What do you mean by Form?
Forms provide a quick and efficient way to modify and insert records into your databases. The forms provide an intuitive, graphical environment that is easily navigated by anyone familiar with standard computer techniques. 

Database Languages 

A database language is a programming language that allows users to interact with a database management system (DBMS) to store, retrieve, and manipulate data.   

Types of Database Languages

1. Data Definition Language (DDL)

It is used to define database structure or pattern. It is used to create schema, tables, indexes etc. in the database. Some major commands are

CREATE: Used to create new database objects like tables, views, indexes, etc. 

ALTER: Modifies the structure of an existing database object, like adding a column to a table. 

DROP: Permanently deletes a database object from the database. 

2. Data Manipulation Language (DML)

DML stands for Data Manipulation Language. It is used for accessing and manipulating data in a database. 

SELECT: Retrieves data from a table based on specified conditions, used to query and view information. 

INSERT: Adds new rows of data to a table. 

UPDATE: Modifies existing data within a table. 

DELETE: Removes rows from a table. 

3.Transaction Control Language (TCL) instructions are used to handle database transactions. These are used to keep track of the modifications that DML statements make.

4. Data Control Language (DCL)

DCL refers to a set of commands used to manage user access and permissions within a database

SQL (Structured Query Language)

Structured Query Language is a standard Database language which is used to create, maintain and retrieve the relational database.

Characteristics of SQL

1. SQL is easy to learn.
2. SQL is used to access data from relational database management systems.
3. SQL can execute queries against the database.
4. SQL is used to describe the data.
5. SQL is used to create a view, stored procedure, function in a database.
6. SQL allows users to set permissions on tables, procedures, and views.

Some important SQL Commonds

SELECT – selects data from the database.
DELETE – removes data from the database.
UPDATE – change data in the database.

TRUNCATE --This command is used to remove all rows from the table

DELETE -It is used to delete some or all of the records.

INSERT INTO – uploads new data into the database.
CREATE TABLE – creates a new table.

RENAME TABLE- used to rename table.

DROP TABLE- used to delete table.

SQL  Statement

1. CREATE TABLE table_name(column1 datatype, column2 datatype, column3 datatype, columnN datatype, PRIMARY KEY( one or more columns ) );

CREATE TABLE  Employees ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), Department VARCHAR(50) );

New “Employees” table created with specified columns.

2. ALTER TABLE Employees ADD Email VARCHAR(100);

 “Email” column added to the “Employees” table.

3. DROP TABLE  Employees;

“Employees” table and its data deleted

4. RENAME TABLE Employee To EMP;

5. TRUNCATE TABLE table_name;

6. UPDATE table_name

SET column 1 = value 1, column 2 = value 2,..., column N = value N 

WHERE CONDITION;

Example – 

UPDATE Employee

SET Address = ‘Chennai’, phone=5321465

WHERE Emp_ID = 2;

7. DELETE FROM table_name

WHERE condition;

Example – 

DELETE FROM Employee

WHERE Address = ‘Pune’;

8. INSERT INTO table_name (column_1 data type, column_2 data type,...,column_N data type)

VALUES (value 1, value 2, value 3,..., value N);

INSERT INTO Employee (Emp_ID int, ‘Emp_Name’ varchar(20), Emp_Age int, ‘Emp_City’ varchar(20), Salary int)

VALUES (1, ‘Satish’, 25, ‘Pune’, 20000);

9. SELECT Emp_ID, Emp_Name FROM Employee;

SELECT * FROM Employee;  (it display all columns)