MNNIT CC 2021-22 Classes

Web Development Class - IV

Web Development Class - IV recording: Here

May 01, 2021

Database

What is Data?
- Data can be defined as a collection of facts and records on which we can apply reasoning or can-do discussion or some calculation.
- Data can exist in form of graphics, reports, tables, text, etc. that represents every kind of information, that allows easy retrieval, updating, analysis, and output of data by systematically organized or structured repository of indexed information.
What is Database?
- Database is a collection of interrelated data.
- Inside a database, the data is recorded in a table which is a collection of rows, columns, etc.
Why do we need database?
- Large volumes of data can be stored in one place.
- Multiple users can read and modify the data at the same time.
- Databases are searchable and sortable, so the data you need can be found quick and easily.
What is DBMS?
- It stands for Database Management Systems.
- Database management system is a software which can be used to manage the data by storing it on to the database and by retrieving and manipulating the data from the database.
Types of Database
- Databases are broadly divided into two types:-
  1. Relational Database - one that stores data in tables.
  2. Non-relational Database - that uses different keys where each key is associated with only one value in a collection. Think of it as a dictionary.
RDBMS Components/Terminologies
- Table or Relation - A table is a collection of data represented in rows and columns. Each table has a name in database.
- Record or Tuple - Each row of a table is known as record. It is also known as tuple.
- Field or Column Name or Attribute - An individual piece of data in a record is known as a field, or attribute.
- Domain - A domain is a set of permitted values for an attribute in table.
- Instance - The data stored in database at a particular moment of time is called instance of database.
- Schema - Design of a database is called the schema. Schema is only a structural view(design) of a database.
- Keys - It is used for identifying unique rows from table. It also establishes relationship among tables.
Types of keys in DBMS
- Primary Key – A primary is a column or set of columns in a table that uniquely identifies tuples (rows) in that table.
- Foreign Key – Foreign keys are the columns of a table that points to the primary key of another table. They act as a cross-reference between tables.

SQL

What is SQL?
- SQL stands for Structured Query Language.
- It is designed for managing data in a relational database management system (RDBMS).
- It is pronounced as S-Q-L or sometime See-Qwell.
- SQL is a database language, it is used for database creation, deletion, fetching rows, and modifying rows, etc.
- SQL is based on relational algebra and tuple relational calculus.
SQL Datatypes
- Data types are used to represent the nature of the data that can be stored in the database table.
- For example, in a particular column of a table, if we want to store a string type of data then we will have to declare a string data type for this column.
- Some of the MySQL datatypes are -
  - CHAR(size) - Used to specify fixed length string. Size can be from 0 to 255 characters.
  - VARCHAR(size) - Used to specify a variable length string. Size can be from 0 to 65535 characters.
  - INT(size) - Used for integer value. The size parameter specifies the max display width that is 255.
  - FLOAT(size, d) - Used to specify a floating point number. Its size parameter specifies the total number of digits. The number of digits after the decimal point is specified by d parameter.
  - DATE - It is used to specify date format YYYY-MM-DD.
- There are many other datatypes present in SQL. You should explore them according to your needs and use-case.
SQL Syntax
- SQL is not case sensitive (Generally SQL keywords are written in uppercase).
- We can place a single SQL statement on one or multiple text lines.
- SQL statements start with any of the SQL commands/keywords like SELECT, etc.
- SQL statements ends with a semicolon (;). It separates two SQL statements.
- Example SQL Statement -
```
SELECT registration_no FROM students;
```
SQL Commands
- SQL commands are instructions.
- They are used to communicate with the database.
- They are also used to perform specific tasks, functions, and queries of data.
- SQL can perform various tasks like create a table, add data to tables, drop the table, modify the table, set permission for users.
- Example - SELECT, INSERT, UPDATE, DELETE, ALTER, DROP etc.
Types of SQL Commands
- There are five types of SQL commands:
  - DDL: Data Definition Language
  - DML: Data Manipulation Language
  - DQL: Data Query Language
  - DCL: Data Control Language
  - TCL: Transaction Control Language
- Note - For now, focus only on DDL, DML and DQL commands.

Data Definition Languague (DDL)

DDL changes the structure of the table like creating a table, deleting a table, altering a table, etc.

Some commands under DDL -

CREATE - It is used to create a database or a new table in the database.

Syntax (creating database) -

  CREATE DATABASE database_name;

  -- You can use this command to list all databases
  SHOW DATABASES;

Syntax (creating a table) -

  CREATE TABLE TABLE_NAME(COLUMN_NAME DATATYPES[,....]);

  -- You can use this command to list all tables in a database
  SHOW TABLES;

  -- To see structure of a table you can use following command
  DESCRIBE table_name;
  -- OR
  DESC table_name;

Example -

  CREATE TABLE EMPLOYEE(Name VARCHAR(20), Email VARCHAR(100), DOB DATE);

DROP - It is used to delete both the structure and records stored in the table.
- Syntax -
```
  DROP TABLE TABLE_NAME;
```
- Example -
```
  DROP TABLE EMPLOYEE;
```

ALTER - It is used to alter the structure of the database. This change could be either to modify the characteristics of an existing attribute or probably to add a new attribute.

Syntax (add a new column) -

  ALTER TABLE table_name ADD column_name (COLUMN DEFINITION);    

Syntax (modify existing column) -

  ALTER TABLE table_name MODIFY (COLUMN DEFINITION....);  

Examples -

  ALTER TABLE STU_DETAILS ADD (ADDRESS VARCHAR2(20));
  ALTER TABLE STU_DETAILS MODIFY (NAME VARCHAR2(20));  

TRUNCATE - It is used to delete all the rows from the table. The structure/schema of table is preserved.
- Syntax -
```
  TRUNCATE TABLE table_name;  
```
- Example -
```
  TRUNCATE TABLE EMPLOYEE;  
```

Data Manipulation Languague (DML)

DML commands are used to modify the database.
These commands are responsible for all form of changes in the database.

Some commands under DML -

INSERT - It is used to insert data into the row of a table.

Syntax -

  INSERT INTO TABLE_NAME (col1, col2, col3,.... col N) VALUES (value1, value2, value3, ...., valueN);  

Example -

  INSERT INTO students (Name, Subject) VALUES ("Alice", "DBMS");  

UPDATE - This command is used to update or modify the value of a column in the table.

Syntax -

  UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WHERE CONDITION];

Example -

  UPDATE students SET Name = 'Bob' WHERE Student_Id = '3';

DELETE - It is used to remove one or more row from a table.

Syntax -

  DELETE FROM table_name [WHERE condition];

Example -

  DELETE FROM students WHERE Name="Bob";  

Data Query Language (DQL)
- DQL is used to fetch the data from the database.
- DQL uses only one command -
  - SELECT - It is used to select the attribute based on the condition described by WHERE clause.
    - Syntax -
      SELECT expressions FROM TABLES WHERE conditions;
    - Example -
      SELECT emp_name FROM employee WHERE age > 20;

SQL Key Constraints

PRIMARY KEY -

A column or columns is called primary key (PK) that uniquely identifies each row in the table.
When multiple columns are used as a primary key, it is known as composite primary key.
Example -

  -- Primary Key using single column
  CREATE TABLE students  
  (  
      S_Id int NOT NULL,  
      LastName varchar (255) NOT NULL,  
      FirstName varchar (255),  
      Address varchar (255),  
      City varchar (255),  
      PRIMARY KEY (S_Id)  
  )  

  -- Primary Key using multiple columns
  CREATE TABLE students  
  (  
      S_Id int NOT NULL,  
      LastName varchar (255) NOT NULL,  
      FirstName varchar (255),  
      Address varchar (255),  
      City varchar (255),  
      CONSTRAINT pk_StudentID PRIMARY KEY (S_Id, LastName)  
  ) 

FOREIGN KEY -
- In the relational databases, a foreign key is a field or a column that is used to establish a link between two tables.
- In simple words you can say that, a foreign key in one table used to point to the primary key in another table.
- Example -
```
  CREATE TABLE orders  
  (  
  O_Id int NOT NULL,  
  Order_No  int NOT NULL,  
  S_Id int,  
  PRIMAY KEY (O_Id),  
  FOREIGN KEY (S_Id) REFERENCES Persons (S_Id)  
  ) 
```

SQL Clauses, Functions and Operators
- WHERE Clause -
  - A WHERE clause in SQL is a data manipulation language statement.
  - It filters the records. It returns only those queries which fulfill the specific conditions.
  - It uses some conditional selection like =, >, <, <=, >=, <>.
  - <> means not equal to.
  - Example -
    SELECT s_name FROM students WHERE s_age >= 18;
- AND and OR Clauses -
  - AND and OR clauses can be used to group multiple conditions.
  - Example -
    SELECT s_name, s_age FROM students WHERE (s_age >= 18 AND cpi > 7.0);
- IN Operator -
  - The IN operator allows you to specify multiple values in a WHERE clause.
  - It is a shorthand for multiple OR conditions.
  - Example - Two statements mentioned below have same effect.
```
  -- Without Using IN
  SELECT s_name FROM students WHERE (s_age=18 OR s_age=19 OR s_age=20);
        
  -- Using IN
  SELECT s_name FROM students WHERE s_age IN (18,19,20);
```
  - NOT Operator can also be used with IN.
  - Example -
```
  SELECT s_name FROM students WHERE s_age NOT IN (18,19,20);
```
- BETWEEN Operator -
  - It selects values within a given range. The values can be numbers, text, or dates.
  - It is inclusive i.e. begin and end values areincluded.
  - Example -
```
  SELECT s_name FROM students WHERE s_age BETWEEN 18 AND 20;

  -- Using AND, NOT, IN and BETWEEN
  SELECT * FROM Products
  WHERE Price BETWEEN 10 AND 20
  AND CategoryID NOT IN (1,2,3);
```
- LIKE Clause -
  - The LIKE clause is used to compare a value to similar values using wildcard operators.
  - Two wildcard operators are used -
    - Percentage sign (%) - It represents zero, one or multiple characters.
    - Underscore sign (_) - It represents a single number or character.
  - Example -
```
  -- Find all students whose name start with letter A.
  SELECT * FROM students WHERE s_name LIKE "A%";

  -- Find all employess whose salary end with digit 2.
  SELECT * FROM employees WHERE emp_salary LIKE "%2";

  -- Find all employees whose salary has digit 0 at second and third position from starting.
  SELECT * FROM employees WHERE emp_salary LIKE "_00%";
```
- ORDER BY Clause -
  - It sorts the result-set in ascending or descending order.
  - It sorts the records in ascending order by default. ASC keyword can also be used.
  - DESC keyword is used to sort the records in descending order.
  - Syntax -
    SELECT column1, column2 FROM table_name WHERE condition ORDER BY column1, column2... ASC|DESC;
  - Example - Find all students and sort them in descending order of age.
    SELECT * FROM students ORDER BY s_age DESC;
- COUNT() Function -
  - It is a function that returns the number of rows that matches a specified criterion.
  - NULL values are not counted.
  - Syntax -
    SELECT COUNT(column_name) FROM table_name WHERE condition;
- AVG() Function -
  - It returns the average value of a numeric column.
  - NULL values are ignored.
  - Syntax -
    SELECT AVG(column_name) FROM table_name WHERE condition;
- SUM() Function -
  - It returns the total sum of a numeric column.
  - NULL values are ignored.
  - Syntax -
    SELECT SUM(column_name) FROM table_name WHERE condition;
- GROUP BY Clause -
  - The GROUP BY clause groups rows that have the same values into summary rows, like “find the number of customers in each country”.
  - The GROUP BY clause is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.
  - In a SELECT statement, the GROUP BY clause follows the WHERE clause and precedes the ORDER BY clause.
  - Syntax -
    SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s);
  - Example - List the number of customers in each country.
    SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;
- HAVING Clause -
  - The HAVING clause was added to SQL because the WHERE clause cannot be used with aggregate functions.
  - Syntax -
    SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);
  - Example - List the number of customers in each country (of Asia only). Only include countries with more than 5 customers.
    SELECT COUNT(CustomerID), Country FROM Customers WHERE Continent="Asia" GROUP BY Country HAVING COUNT(CustomerID) > 5;
SQL NULL Values
- A field with a NULL value is a field with no value.
- If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.
- A NULL value is different from a zero value or a field that contains spaces.
- A field with a NULL value is one that has been left blank during record creation.
- Testing for NULL values -
  - It is not possible to test for NULL values with comparison operators, such as =, <, or <>.
  - We will have to use the IS NULL and IS NOT NULL operators instead.
  - Syntax -
```
  -- IS NULL
  SELECT column_names
  FROM table_name
  WHERE column_name IS NULL;

  -- IS NOT NULL
  SELECT column_names
  FROM table_name
  WHERE column_name IS NOT NULL;
```
SQL Aliases
- SQL aliases are used to give a table, or a column in a table, a temporary name.
- Aliases are often used to make column names more readable.
- An alias only exists for the duration of that query.
- An alias is created with the AS keyword.
- Syntax -
```
-- Column Name Alias
SELECT column_name AS alias_name
FROM table_name;

-- Table Name Alias
SELECT column_name(s)
FROM table_name AS alias_name;

-- OR
    
SELECT column_name(s)
FROM table_name alias_name;
```
SQL Join
- A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
- Matching is always done based on the related column.
- Look at Orders table -
- Look at the Customers table -
- In both these tables, we can clearly see that CustomerID is the related column.

Types of SQL Joins -

(INNER) JOIN -

Returns records that have matching values of related column in both tables.

Syntax -

  SELECT column_name(s)
  FROM table1
  INNER JOIN table2
  ON table1.column_name = table2.column_name;

LEFT (OUTER) JOIN -

Returns all records from the left table, and the matched records from the right table.

Syntax -

  SELECT column_name(s)
  FROM table1
  LEFT JOIN table2
  ON table1.column_name = table2.column_name;

RIGHT (OUTER) JOIN -

Returns all records from the right table, and the matched records from the left table.

Syntax -

  SELECT column_name(s)
  FROM table1
  RIGHT JOIN table2
  ON table1.column_name = table2.column_name;

FULL (OUTER) JOIN -

Returns all records when there is a match in either left or right table.

Syntax -

  SELECT column_name(s)
  FROM table1
  FULL OUTER JOIN table2
  ON table1.column_name = table2.column_name
  WHERE condition;

SELF JOIN -

A self join is a regular join, but the table is joined with itself.

Example -

  SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
  FROM Customers A, Customers B
  WHERE A.CustomerID <> B.CustomerID
  AND A.City = B.City
  ORDER BY A.City;

Explore Yourself
- Other SQL Datatypes
- SQL LIMIT
- SQL UNION
- SQL CHECK
- SQL NATURAL JOIN
- SQL CROSS JOIN
Content Contributors
- Kshitiz Srivastava
- Harshit Garg
Materials
- W3Schools SQL Tutorial (Highly Recommended)
- SQL Cheatsheet
- For those of you who prefer reading books, MySQL Tutorial
- SQL Video Tutorial (English)

This site is open source. Improve this page.

MNNIT CC 2021-22 Classes

Web Development Class - IV

Web Development Class - IV recording: Here

May 01, 2021

Database

What is Data?

What is Database?

Why do we need database?

What is DBMS?

Types of Database

RDBMS Components/Terminologies

Types of keys in DBMS

SQL

What is SQL?

SQL Datatypes

SQL Syntax

SQL Commands

Types of SQL Commands

Data Definition Languague (DDL)

Data Manipulation Languague (DML)

Data Query Language (DQL)

SQL Key Constraints

SQL Clauses, Functions and Operators

SQL NULL Values

SQL Aliases

SQL Join

Types of SQL Joins -

Explore Yourself

Content Contributors

Materials