9/7

## Database

- Organized collection of structured information or data.

- Database is a collection of entity(also called as table).

- Entity is a collection of records.

## Types of database

1) Local database (installed in PC)

2) Web database  (host, server side)

## Python support database

1) MySQL

2) Microsoft SQL server

3) Mongo DB

4) SQL lite 

## Database Library 

1) PySQL

2) PyMongo

## Database Terminology

1) field-represent the record

ex. name,age,etc.

## Types of fields

1) Single value field - accept only one value at a time  ex. aadhaar card, pancard

2) Multi-value field- ex.name(firstname, lastname), country(city,state)

3) Null value field(blank) ex. mobile no.

## Tuple - complete info is called tuple

roll     name      city 

1           A         ngp

2           B        delhi

3           C     

## Database Management

- Key is used to manage database.

- Key is a special type of field for used to manage all records.

1) Primary key

2) Unique key

3) Foreign key

1) Primary key:-

- No repeat

- No blank/null

ex. rollno.

2) Unique key:-

- No repeat

- Accept null values

ex. mobile no., aadhaar card no.

3) Foreign key:-

- Used in complex table which has more than 20 columns.

- In foreign key the complex table is divide into small table

- Every small table uses one common primary key

- All table communicate with the help of this primary key is called Foreign key

## Database Management Commands(Queries)

- Query is a predefined command

1) insert query

2) update

3) delete

4) select

10/7

#### Database Queries

## Query Category:-

1) Non query

2) Query

1) Non query - reflect the database

ex. insert, update, delete

2) Execute query - not reflect the database(read table record)

ex. select 

STUDENT TABLE

 roll         name          age

 101          amit           24

 102         mohan         23

 103          sumit          27

 104          priya          30

 105          reena          26

## insert query

1) All values

2) Particular column

1) All values

insert into table_name values(101,'amit',24)

2) Particular column - if only age and name is given

insert into table_name(name,age) values('mohan',23)

## Database clause

- Generally used for condition

1) WHERE clause - used with select, update, dlt

2) BETWEEN clause

3) GROUPBY clause

4) ORDERBY clause

EXAMPLES:-

1) Display all the record from student table where age>18 

select * from student where age>18

2) Display all records from student where rollno.=101

select * from student where roll=101

3) Display all records from student where age is between 18 to 25

select * from student where age between 18 and 25;

4) Display only name and age from student 

select name,age from student where name='priya';

output:- priya|28

5) Display all records from student order by name

select roll,name,age from student order by age asc 

NOTE:- desc - descending order

               asc -  ascending order

6) Display name from student group by name (display repeated records one time)

select name from student group by name

7) Display all records from student where age>18 and age<25

select * from student where age>18 and age<25

8) Delete records from student where roll=101

delete from student where roll=101

9) Update the name of student where roll=101 

update student set name='ramesh' where roll=101 

(did not give any output)

NOTE:- alter command used for table columns

10) Create a table query

create table student(roll int(20), name varchar(20), age int(5))

## Aggregate function

- Direct operates the query 

ex. sum(), avg(), min(), max() - used with select query

ex.1) select sum(amt) from student 

2) select avg(amt) from student

  

3) select min(amt) from student 

4) select max(amt) from student 

TASK:- 

## Create a table from your choice and generate 15 questions queries with ans.

employee

empid                empname                 empage               empphn                empsal

               

   101                     Rohit                        25                       23456                   12,000/-

   102                     Priya                        27                       24598                    17,000/-

   103                     Tushar                      30                                                     25,000/-

   104                     Prerna                      25                       26579                    16,000/-

   105                     Mohan                     29                       29871                    20,000/-

   106                     Akash                      28                       20913                     17,000/-

   107                     Pooja                       24                       26795                     22,000/-

  

Queries:-

1) Create table query.

create table employee (empid int(10) primary key , empname varchar(30) not null, empage int(10) not null, empphn int(20), empsal int(10) not null );

2) Delete any one record from employee table.

delete from employee where id=105

3) Insert record in employee table.

-  INSERT INTO employee VALUES (102, 'Reena', 30, 24578, 20000);

-  insert INTO employee VALUES (103, 'Sumit', 24, 29017, 13000);

-  insert into employee(empid, empname, empage, empphn, empsal) values(108, 'Sonam', 28, 28974,          17,000)

4) Alter table query.

alter table employee add empadd varchar(50)

5) Display all records. 

select * from employee;

6) Drop query.

drop table employee

7) Truncate query. (deletes the record of table but does not delete its structure)

truncate table employee

8) Rename table query.

alter table employee rename to empdetails

9) Between query.

select * from employee where empage between 20 and 35

10) and query.

select empname, empage from employee where empsal > 15,000 and empid=105

11) or query.

select * from employee where empid=103 or empid=105

12) IS NULL query.

select * from employee where empphn is null

(not executed)

13) Order by query.

select * from employee order by empname

14) Group by query.

select * from employee group by empsal

15) sum() query.

select sum(empsal) from employee

14/7

## JOIN QUERY

1) Using two tables.

2) It used to table object.

ex.

ordertable

orderid      custid       orderdate      city

 101          201           11/7              MP

 102          202           12/7              UP

 103          203           13/7             Punjab

 104          204           14/7            Maharashtra

 105          205           15/7             Kerala

customertable

custid       custname        country       city

 202          Amit            India              UP

 111           John           America         Georgia

 112          Rohit           UK                London

 113          Tina            India              Delhi

NOTE:-  Table Object :- table_name.table_column to retrive data

## INNER JOIN QUERY:-

select ordertable.orderid,customertable.custname,ordertable.orderdate from ordertable inner join customertable on ordertable.custid=customertable.custid

OUTPUT:-

orderid     custname      orderdate 

 102         Amit            12/7    

## OUTER JOIN:- (priority will be given to outer table/second table)

select ordertable.orderid,customertable.custname,ordertable.orderdate from customertable outer join ordertable on customertable.custid=ordertable.custid

## Union Query

select city from customertable UNION ALL select city from ordertable ORDER BY city

OUTPUT:-

city

 MP

 UP

 .

.

.

.

## Like query:-

- To match string or character like query is used

ex1. 

select * from customertable where name LIKE 'a%'  

output:-

will display all records who starts with 'a'

ex2. 

select * from customertable where name LIKE '%a' 

output:-

end with 'a' 

ex3.

select * from customertable where name LIKE '%India%' 

output:-

match India on any position (whole record will be displayed.)

TASK:-

Search online query editor and execute all queries.