relational data base management system:

The software used to store, manage, query, and retrieve data stored in a relational database is called a relational database management system (RDBMS). The RDBMS provides an interface between users and applications and the database, as well as administrative functions for managing data storage, access, and performance.

Uses of RDBMS:

Relational data base management systems are frequently used in disciplines such as manufacturing, human resources and banking. The system is also useful for airlines that need to store ticket service and passenger documentation information as well as universities maintaining student databases.

Some examples of specific systems that use RDBMS include IBM, Oracle, MySQL, Microsoft SQLServer and PostgreSQL.

What is SQL in DBMS?

Structured Query Language (SQL) is a standardized programming language that is used to manage relational databases and perform various operations on the data in them.

Types of SQL Statements:

• Data Definition Language (DDL) Statements.
• Data Manipulation Language (DML) Statements.
• Transaction Control Statements.
• Session Control Statements.
• System Control Statement.
• Embedded SQL Statements.

DATABASE IN SQL:

A database in SQL Server is made up of a collection of tables that stores a specific set of structured data. A table contains a collection of rows, also referred to as records or tuples, and columns, also referred to as attributes.

LEARN SQL COMMANDS

CREATE DATABSE:

CREATE DATABASE College;

USE DATABASE:

USE College;

CREATE TABLE:

CREATE TABLE Students(
id INT NOT NULL,
name VARCHAR(30) NOT NULL,
percent INT NOT NULL,
age INT NOT NULL,
gender VARCHAR(20) NOT NULL,
city INT,
courses INT 
);

INPUT IN THE TABLE:

INSERT INTO STUDENTS VALUES
(1, "Ram", 90, 17, "male", 1, 1),
(2, "Sarita", 55, 20, "female", 2, 2),
(3, "Salman", 67, 20, "male", 1, 1),
(4, "Juhi", 45, 21, "female", 3, 3),
(5, "Anil", 34, 19, "male", 1, 3),
(6, "John", 74, 18, "male", 2, 2),
(7, "Shahid", 56, 19, "male", 1, 1);

SHOW THE INPUT DATA:

SELECT * FROM Students;

PRIMARY KEY & FOREIGN KEY:

A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table. The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.

ALTER TABLE Students
ADD PRIMARY KEY (id);

ALTER TABLE Students
ADD FOREIGN KEY (courses) REFERENCES courses(cid);

SOME IMPORTANT COMMANDS:

CREATE DATABASE BANK;

USE BANK;

CREATE TABLE Customers(
cid INT NOT NULL,
cname VARCHAR(30) NOT NULL,
age INT NOT NULL,
gender VARCHAR(20) NOT NULL,
city INT 
);

SELECT * FROM Customers;

INSERT INTO Customers VALUES
(1, "Harry", 23, "male", 1),
(2, "Peter", 24, "male", 2),
(3, "John", 25, "male", 3),
(4, "Lily", 20, "female", 1),
(5, "Sera", 21, "female", 2),
(6, "Logan", 30, "male", 1),
(7, "Liza", 25, "female", 3);

LIKE & REGULAR EXPRESSION:

SELECT * FROM Customers WHERE cname LIKE "j%";
SELECT * FROM Customers WHERE cname REGEXP '^h';
SELECT * FROM Customers WHERE cname REGEXP "r$";
SELECT * FROM Customers WHERE cname REGEXP '[a-h]a';
SELECT * FROM Customers WHERE cname REGEXP 'harry|logan';

ORDER BY:

SELECT * FROM Customers ORDER BY cid DESC;
SELECT * FROM Customers ORDER BY city;

DISTINCT:

SELECT DISTINCT age FROM Customers;

DELETE THE TABLE (DROP):

DROP TABLE Customers;

HERE WE CREATE A TABLE TO TRY SOME OTHER COMMAND:

USE BANK;

CREATE TABLE Employee(
eid INT NOT NULL,
ename VARCHAR(30) NOT NULL,
gender VARCHAR(1) NOT NULL,
salary INT NOT NULL
);

SELECT * FROM Employee;

INSERT INTO Employee VALUES
(1, "John Wick", "m", 10000),
(2, "Harry Poter", "m", 20000),
(3, "Logan Poul", "m", 10000),
(4, "Alan Walker", "f", 10000),
(5, "Arina Grande", "f", 20000),
(6, "Belly Elish", "f", 12000),
(7, "Tom Cruise", "m", 12000);

UPDATE & DELETE COMMAND:

UPDATE Employee
SET salary = 1200
WHERE eid = 1;

DELETE FROM Employee WHERE eid = 7;

INTEGRITY CONSTRAINTS:

SELECT COUNT(eid) FROM Employee;

SELECT COUNT(DISTINCT salary) AS count FROM Employee; 

SELECT MAX(salary) AS salary FROM Employee;

SELECT SUM(salary) AS sum FROM Employee;

SELECT AVG(salary) AS Avg FROM Employee;

VIEW IN SQL:

CREATE VIEW ShowData
AS 
SELECT cs.id, cs.name, cs.percentage, cs.gender, ci.cityname FROM class cs INNER JOIN cities ci
ON cs.city = ci.cid;

SELECT * FROM ShowData;