SQL And DBMS

2023-08-14 1523 words 8 minutes

Contents

*SQL - Structured Query Language.SQL is a programming language used by nearly all relational databases to query, manipulate, and define data, and to provide access control. SQL was first developed at IBM in the 1970s with Oracle as a major contributor, which led to implementation of the SQL ANSI standard, SQL has spurred many extensions from companies such as IBM, Oracle, and Microsoft.

Sql is used to perform CRUD operatioins.
- Creat
- Read
- Update
- Delete

What is DataBase?

Database is a organized collection of structured data.A database is usually controlled by a database management system (DBMS). Together, the data and the DBMS, along with the applications that are associated with them, are referred to as a database system, often shortened to just database.

What is Database Management System?

DBMS serves as a interface betweeen Database and end user or programs, allowing user to retrieve,update and manage how the informations are organized and optimized. DBMS also facilitates oversight and control of databases, enabling a variety of administrative operations such as performance monitoring, tuning, and backup and recovery.

Types Of DataBase

Relational DataBase	Non Relational DataBase
Data stored in table	Data is not stored in table
1) MySQL	1) Nosql
2) Sql server
3) Oracle
4) Postgre Sql

In Table with the help of the Column we can get to know about the structure(Design). In Database we call Structure as Schema
In Table with the help of the row we can get to know about the individual information

Creating our First Database

To Create a Database
```
Syntax -   CREATE DATABASE database_name; (This helps to create a Database)
```
- uppercase and lowercase are acceptable.Its is good way to use uppercase.

To Drop a Database

Syntax -   DROP DATABASE database_name; (This helpz to drop the Database)

To Access the Database

Syntax -   USE database_name;   (This helpz to access the Database)

Creating Our First Table

Creating Table

Syntax -   CREATE TABLE table_name(
           Column_name1 datatype  constraint,
           Column_name2 datatype  constraint);

Inserting Data Into The Table

Syntax -    INSERT INTO table_name  VALUES (eg: 1,shobhit,26);

To Display the Values

Sysntax -   SELECT *  FROM table_name;
            * This symbol  displays all the values

Datatypes in SQL

Now lets dive into different Data types

Datatype	Description	Usage
CHAR	String(0-255) can store character of fixes length	CHAR(50)
VARCHAR	String(0-255) can store character upto fixed length	VARCHAR(50)
BLOB	String(0-65535)can store longer strings	BLOB(1000)
INT	integer(-2147483648) to (2147483647)	INT
TINYINT	integer (-128 to 127)	TINYINT
BIGINT	integer(-9232372036854775808) to (9232372036854775807)	BIGINT
BIT	Can store x- bit values.x can range from 1 to 64	BIT(2)
FLOAT	Stores Decimal numbers	FLOAT
DOUBLE	Decimal number with 24 to 54 digit	DOUBLE
BOOLEAN	boolean values 0 or 1	BOOLEAN
DATE	date in format YYYY-MM-DD	DATE
YEAR	year in 4 digit format ranging from 1901 to 2155	YEAR

Types Of SQL Commands

Data Definition Language ( DDL)
- CREATE
- ALTER
- RENAME
- TRUNCATE
- DROP
Data Query Language (DQL)
- SELECT
Data Manipulation Language (DML)
- INSERT
- UPDATE
- DELETE
Data Control Language (DCL)
- GRANT
- REVOKE
Transaction Control Language (TCL)
- START
- COMMIT
- ROLLBACK

CONSTRAINT

In SQL (Structured Query Language) a constraint is a rule or restriction applied to a table column or a set of columns to ensure data integrity, accuracy, and consistency in a relational database. Constraints help maintain the quality and reliability of data by preventing the insertion, update, or deletion of records that would violate the defined rules. There are several types of constraints in SQL:
- PRIMARY KEY CONSTRAINT:
  - Ensures each row in table has a unique identifier(no duplicate value) and should not contain NULL VALUE .There should be only 1 Primary Key inside the table.
```
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(255));
```
- FOREIGN KEY:
  - Enforces referential integrity by ensuring that values in a column (the foreign key) match values in another table’s primary key column (the referenced table). This constraint helps establish relationships between tables.Can have duplicate value and can have NULL value.
```
CREATE TABLE teacher(
cust_id INT,
FOREIGN KEY (cust_id) REFERENCES Students(StudentID)
);
```
  - When some changes are made in parent key then to change it in child key we use casecade
    - ON DELETE CASCADE
    - ON UPDATE CASECADE
- NOT NULL :
  - Column cannot hava a NULL value.This constraint enforces that every row must have a value for the specified column.
```
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(255) NOT NULL,
Price DECIMAL(10, 2));
```
- UNIQUE :
  - All vlues in column are different and unique across all the row and it allows NULL values.
```
CREATE TABLE Employees (
EmployeeID INT UNIQUE,
LastName VARCHAR(255),
FirstName VARCHAR(255));
```
- CHECK:
  - Allows you to define a condition that must be true for each row in a table.If the condition evaluates to false, the row is not inserted or updated.
```
CREATE TABLE city (
id INT PRIMARY KEY,
city VARCHAR(50),
age INT,
CONSTRAINT age_check CHECK (age >= 18));

        (OR)

CREATE TABLE city(
id INT PRIMARY KEY,
city VARCHAR(50),
age INI CHECK( age >=18));
```
- DEFAULT:
  - Sets default value to the column
```
Salary INT DEFAULT 25000
```
    if the value is not entered then the default value will be set.

CLAUSES IN SQL :

SELECT CLAUSE -The SELECT CLAUSE is used to specify the columns you want to retrieve from a database table.
```
      SELECT column1, column2 FROM table_name;
```
WHERE CLAUSE - WHERE CLAUSE is used to filter rows based on conditions.(used with SELECT)
```
      SELECT column1,column2 FROM table_name
      WHERE condition ;
```
FROM CLAUSE - The FROM CLAUSE specifies the table from which your retrieving data.
```
      SELECT column1,column2 FROM table_name;
```
GROUP BY CLAUSE - To group values that have same values.It collect data from multiple records and group the result by one or more column .Generally we use group by value with some aggregate on functions.
```
      SELECT colm1, COUNT(*)
      FROM table_name
      GROUP BY colm1;
```
HAVING CLAUSE - The HAVING CLAUSE is used to filter the result of GROUP BY value .Similar to where i.e applies some conditions on rows used when we want to apply any condition after GROUP BY. 5 seconds
```
      SELECT COUNT(name),city
      FROM student
      GROUP BY city
      HAVING MAX(marks>90);
```

ORDER BY CLAUSE - The ORDER BY CLAUSE is used to set the result in ascending or descending.Here if you dont mention asc or des by default it takes it arranges in ascending.

       SELECT COUNT(name),city
       FROM student
       GROUP BY city
       ORDERED BY city ASC;

GENERAL ORDER

SELECT col
FROM table_name
WHERE conditon
GROUP BY column
HAVING conditon
ORDER BY column ASC/DSC;

AGGREGATE FUNCTION :

1)MAX()
2)MIN()
3)COUNT()
4)AVG()
5)SUM()

UPDATE : To Update exsisting rows.

UPDATE table_name SET col1=value,col2=vlaue WHERE condition;

(EXAMPLE)

UPDATE student SET grade= “o” WHERE grade =“A”;
DELETE :To Delete the row from the table.

DELETE FROM table_name wHERE Condition
ALTER : To ALTER the column.
1. ADD COLUMN
  
  eg ALTER TABLE table_name ADD COLUMN column_name datatype constraint ;
2. DROP COLUMN
  
  eg ALTER TABLE table_name DROP COLUMN column_name;
3. RENAME TO TABLE
  
  eg ALTER TABLE table_name RENAME TO ew_table_name;
4. CHANGE COLUMN
  
  eg ALTER TABLE table_name CHANGE old_name new_name new datatype new constraint;
5. MODIFY COLUMN
  
  eg ALTER TABLE table_name MOFDIFY col_name new_datatype new_constraint;

TRUNCATE COMMAND : Delets all the data in the table

eg  TRUNCATE TABLE table_name;

Difference between TRUNCATE and DROP

TRUNCATE : Delets the data in the table
DROP     : Delets the whole table.

JOIN in SQL : Join is used to combine rows from two or more table. based on a related column between them.
- TYPES OF JOIN
  1. INNER JOIN
  2. OUTER JOIN
    - LEFT JOIN
    - RIGHT JOIN
    - FULL JOIN
- INNER JOIN
  - Returns all the records thata have matching value in both table.
  SYNTAX:
```
   SELECT column
   FROM table A
   INNER JOIN table B
   ON table A.col_name = table B.col_name;
```
- LEFT JOIN : Returns all the record from the left table and the matched record from the right table.
  
  SYNTAX :
```
   SELECT column
   FROM table A
   LEFT JOIN table B
   ON table A.colname= B.colname2;
```
- RIGHT JOIN :Returns all the record from the right table and the matched record from the left table.
  
  SYNTAX :
```
   SELECT column
   FROM table A
   RIGHT JOIN table B
   ON table A.colname= B.colname;
```
- FULL JOIN : Returns all the records when there is a match between right and left table.
  
  SYNTAX :
```
   SELECT column
   FROM table A
   LEFT JOIN  table B
   ON A.id =B.id

   UNION

   SELECT column
   FROM table A
   RIGHT JOIN table B
   ON A.id= B.id ;
```
  - IN mysql there is no FULL JOIN so we use UNION.
- SELF JOIN : It is a regular join but the table is joined by itself.
  
  SYNTAX :
```
   SELECT column
   FROM table as a    (alias)
   JOIN table as b
   ON a.column = b.column;
```
- UNION : The UNION clause in SQL is used to combine the result sets of two or more SELECT statements into a single result set. It is used when you want to retrieve data from multiple tables or perform multiple queries and combine the results as if they were one result set.
```
   SYNTAX :

       SELECT column FROM table A
       UNION
       SELECT column FROM table B;
```
  TO use : every SELECT should have same no of columns column should have similar
- UNION : Gives unique value
- UNION ALL: Gives DUPLICATE VALUE

SQL SUB QUERIES

A subquery is a SQL query nested inside a larger query.
A subquery may occur in :
- SELECT CLAUSE
- WHERE CLAUSE
- FROM CLAUSE