Notes And Questions NCERT Class 12 Computer Science Chapter 1 Database Concepts

Please refer to Database Concepts Class 12 Computer Science Notes and important questions below. The Class 12 Computer Science Chapter wise notes have been prepared based on the latest syllabus issued for the current academic year by CBSE. Students should revise these notes and go through important Class 12 Computer Science examination questions given below to obtain better marks in exams

Database Concepts Class 12 Computer Science Notes and Questions

The below Class 12 Database Concepts notes have been designed by expert Computer Science teachers. These will help you a lot to understand all the important topics given in your NCERT Class 12 Computer Science textbook. Refer to Chapter 1 Database Concepts Notes below which have been designed as per the latest syllabus issued by CBSE and will be very useful for upcoming examinations to help clear your concepts and get better marks in examinations.

• Django- Django isa free and open source high level python web application framework.
• GET request-An HTTP GET request is a way to retrieve information from the given server using a given URL over web.
• POST request-An HTTP POST request is a way to send data to the server using HTML forms over web.
• Django Architecture-Django runs on MVT/MVC architecture. Django architecture components are Models, Views, Templates, Controller.
• Command to install Django- pip install Django
• Command to create a project- django-admin startproject <projectname>
• To start a server- python manage.py runserver
• Command to create a new web application- django-admin startapp<appname>
• MVT-Model is data management components, View is handling logic that links the URLs and templates through URL routing, and Template is responsible for presentation logic for a Django project.   

• Virtualenv -Virtualenv is a useful tool which creates isolated Python Environment that take care of interdependencies and let us develop different applications in isolation.
• CSV (Comma Separated Values). A csv file is a type of plain text file that uses specific structuring to arrange tabular data.

CSV is a common format for data interchange as it is compact, simple and general. Each line of the file is one line of the table. CSV files have .csv as file extension.
import csv with open(‘C:\\data.csv’,’rt’) as f:
data = csv.reader(f) #reader function to generate a reader object for row in data:
print(row)
When we create a project in Django following files are created in project’s folder:

• __init__.py: An empty file that tells Python that this directory should be considered a Python package.
• settings.py: Settings/configuration for this Django project.
• urls.py: The URL declarations for this Django project; a “table of contents” of your Django-powered site.
• wsgi.py: An entry-point for WSGI-compatible web servers to serve your project.

INTRODUCTION:

DBMS is a collection of interrelated data in arranged form and set of programs used to access those data.
Advantages of DBMS:
➣ Control of data redundancy
➣ Data consistency
➣ Sharing of data
➣ Data Integrity
➣ Data isolation
➣ Privacy and Security

DATA MODELS:
Data models describe the structure of the database. There are four data models in DBMS:
1. Relational Data Model
2. Hierarchical Data Model
3. Network Model
4. Object Oriented Data Model

1. Relational Data Model: This database consists of a collection of table. These tables are called relations. A row in a table represents a relationship among a set of values.
➣ Relation: A relation is a table with columns and rows.
➣ Tuple: A row of a relation OR a row of a table.
➣ Domain: A set of values for the columns.
➣ Attribute: Column name of a relation.
➣ Degree: Number of attributes in a table.
➣ Cardinality: Number of tuples OR Number of rows in a table.
➣ View: A virtual table that does not exists but it is derived from other table.

2. Hierarchical Data Model: In this model the data are represented by collection and relationship between data are represented by links. In this model the collection of data are organized as tree.

3. Network Data Model: This is same as the hierarchical model but in this model the collection of data is organized as arbitrary graphs.

4. Object Oriented Data Model: In this model the data and its operations are represented by objects.

NOTE: Some Definitions:-
Record: Collection of attributes
File: Collection of records

KEYS:
A key allows us to identify a set of attributes that distinguish entities from each other.
There are four keys:
1. Primary Key
2. Candidate Key
3. Alternate Key
4. Foreign Key

1. Primary Key: A primary key is a set of one or more attributes that can uniquely identify tuples within the relation. This key does not have duplicate values in the relation. There must be any value for this key, it cannot be NULL.

2. Candidate Key: All attributes combination inside a relation that can serve as a primary key, is called candidate key.

3. Alternate Key: A candidate key that is not the primary key is called an alternate key.

4. Foreign Key: A non-key attribute whose values are derived from the primary key of some other table, is known as foreign key in its current table.

INTRODUCTION TO MySQL:
Introduction:
➣ MySQL stands for My Structured Query Language.
➣ MySQL is freely available, Open source Relational Database Management System (RDBMS).

Features of MySQL:
a. Cost: Freely available
b. Performance: MySQL is fast.
c. Simplicity: MySQL is easy to install.
d. High Performance
e. Data Security: In has powerful mechanism for ensuring only authorized users have access the data.
f. Flexibility

STARTING MySQL:

First you have to install MySQL on your system. Then click on

Start → All Programs → MySQL → MySQL Command Line Client

SQL:-
➣ SQL stands for Structured Query Language.
➣ This is non-procedural Language.
➣ This is the common language for relational database. Means this language is used in MySQL.

Difference between SQL and MySQL:

SQL Elements:
There are four basic elements of SQL:
1. Literal
2. Data Types
3. Nulls
4. Comments

1. Literal: A literal is a fixed data value. This fix value may be numeric or character.

Character Literal: All character literals are enclosed in single quotation mark or double quotation marks.
For example :- ‘a’ , “yogesh” , ‘5’, “xyz5”

2. Data Types:- Data types are rules that define what data may be stored in a column and how that data is actually stored. Data types used in MySQL categorized into four categories:
(i) Numeric
(ii) String
(iii) Date and Time
(iv) Binary

(i) Numeric: This data type stores numbers.

(ii) String: 

(iii) DATE and TIME: 

(iv) Binary Data Type: Stores the data in binary objects, such as graphics, images, multimedia etc.

3. NULL values: If a column in a row has no value, then column is said to be null.
NULLs can appear in a column if the column is not restricted by NOT NULL or Primary Key. You should use a null value when the actual value is not known.
Null and Zero is not equivalent. Any arithmetic expression containing a null always evaluates to null.

4. Comments: Comment is a text that is not executed. There are two types of comments that we use in MySQL.
(i) Single line Comment: Beginning with two hyphens (–) OR beginning with # symbol.
(ii) Multi Line Comment: /*………………………………………..
…………………………………….*/

Difference between char and varchar:  

Classification of SQL commands:
SQL commands are categorized into four sub languages:
(i) Data Definition Language (DDL)
(ii) Data Manipulation Language (DML)
(iii) Transaction Control Language (TCL)
(iv) Data Control Language (DCL)

(i) Data Definition Language (DDL): It consist the commands to create objects such as tables, views, indexes etc. in the database.
COMMANDS: CREATE, ALTER, DROP, RENAME, TRUNCATE

(ii) Data Manipulation Language (DML): It is used for queries. It allows you to perform data manipulation e.g. retrieval, insertion, deletion, modification of data
stored in database.
COMMANDS: SELECT, INSERT, UPDATE, DELETE

(iii) Transaction Control Language (TCL): This language allows you to manage and control the transaction.
COMMANDS: COMMIT, ROLLBACK
• NOTE:- Savepoint is also used in TCL.

(iv) Data Control Language (DCL): This language is used to control data and access to the databases. It is used for protecting the data from unauthorized access.
COMMANDS: GRANT, REVOKE

SQL QUERIES:
In MySQL, to create new tables or query, the user must specify the database. This database is called current database.

To check the available database in MySQL:-
SHOW databases;

To access the database:-
Syntax:- USE database-name;
Example: USE school;
A database has tables. Tables have rows and columns.

To show the available tables in the database:-
SHOW tables;
CREATING DATABASE: In MySQL we can create the databases using CREATE DATABASE statement.
Syntax: CREATE DATABASE <database-name>;
Example: CREATE DATABASE Bank;

OPENING DATABASE:
Syntax: USE <database-name>;
Example: USE Bank;

DROPPING DATABASES: To remove the entire database we use the DROP DATABASE statement.
Syntax: DROP DATABASE <database-name>;
Example: DROP DATABASE Bank;

Creating Tables in MySQL:

Tables are created using CTREATE TABLE command.
A table has rows and columns. The column name must be specified along the data type.
Each table must have at least one column.

Syntax:
CREATE TABLE <table-name>(<column-name> <data-type> (size), <column-name>
<data-type> (size), <column-name> <data-type> (size) );

Example:
CREATE TABLE EMPLOYEE(Ecode int(6), Ename varchar(30), Dept varchar(30), city varchar(25), sex char(1), DOB Date, salary float(12,2) );

Viewing a table structure:
DESC <table-name>;
Example: DESC EMPLOYEE;
Or we can write
DESCRIBE EMPLOYEE;

Inserting data into table:
The rows(tuples) are added to relations(tables) using INSERT command.

Syntax:
INSERT INTO <table-name>[<column-name>] VALUES (<value1>, <value2>, ……….);

Example: INSERT INTO EMPLOYEE VALUES(1001, ’Amit’, ’production’, ’Ahemedabad’, ’M’, ’1988-08-22’, 20000.00);

The into clause specifies the target table and the value clause specifies the data to be added to the new row of the table.
While inserting data into tables, following points should be taken care of:
➣ Character data should be enclosed within single quotes.
➣ NULL values are given as NULL, without any quotes.
➣ If no data is available for all the columns then the column list must be included,
following the table name. Example: INSERT INTO EMPLOYEE(Ecode, Ename, salary) VALUES(1001, ‘Amit’, 20000.00);

After inserting the data , we created the following table:

CONSTRAINTS:
A constraint is a condition on a field or on set of fields. There are six types of constraints that we use in MySQL:-
(i) NOT NULL
(ii) DEFAULT
(iii) UNIQUE
(iv) CHECK
(v) PRIMARY KEY
(vi) FOREIGN KEY

(i) NOT NULL Constraint: This constraint ensures that a column cannot have NULL value. By default, a column can hold NULL. If you add a NOT NULL constraint on a column it cannot hold a NULL.

How to apply NOT NULL constraint:-
(a) When we are creating a table:-
Example:-
CREATE TABLE EMPLOYEE (Ecode int(6) NOT NULL, Ename
varchar(30) NOT NULL, Dept varchar(25));
Now Ecode and Ename columns cannot include NULL.

(b) Add NOT NULL constraint in a existing table column:
Example:-
ALTER TABLE EMPLOYEE
MODIFY Ecode int(6) NOT NULL;

(ii) DEFAULT Constraint:- The DEFAULT constraint provides a default value to a column. When a user does not enter the value for a column, automatically the defined default value is inserted into the field.

How to apply default constraint:-
(a) While we are creating a table:-
Example:-
CREATE TABLE EMPLOYEE (Ecode int(6), Ename varchar(30), Salary
float(12,2)
DEFAULT 25000.00);

(b) Add DEFAULT constraint in an existing table column:-
Example:-
ALTER TABLE EMPLOYEE
MODIFY Salary float(12,2) DEFAULT 25000.00;
(iii) UNIQUE Constraint:- It ensures that all values in a column are different.

How to apply unique constraints:-
(a) While we are creating a table:-
Example:-
CREATE TABLE EMPLOYEE (Ecode int(6) UNIQUE, Ename
varchar(30), Dept varchar(25));

(b) Add unique constraint in an existing table column:
Example:-
ALTER TABLE EMPLOYEE
MODIFY Ecode int(6) UNIQUE;
(iv) CHECK constraint: – It make sure that all the values in a column satisfy certain criteria.
How to apply check constraint:-
(a) While we are creating a table:-
Example:-
CREATE TABLE EMPLOYEE (Ecode int(11) CHECK(Ecode>0), Ename varchar(30));

(b) Add check constraint on an existing table column:-
Example:-
ALTER TABLE EMPLOYEE
ADD CHECK(Ecode>0);

(v) Primary Key Constraint: – This is used to uniquely identify a row in a table.
How to apply primary key constraint:-
(a) While creating a table:-
Example:-
CREATE TABLE EMPLOYEE (Ecode int(6) PRIMARY KEY, Ename varchar(30), city varchar(30));
(b) Add primary key constraint on an existing table column:-
Example:-
ALTER TABLE EMPLOYEE
ADD PRIMARY KEY (Ecode);

(vi) Foreign Key Constraint:- Whenever two tables are related by a common column, then the related column in the parent table should be either declared a PRIMARY KEY or UNIQUE KEY and the related column in the child table should have FOREIGN KEY constraint.
Syntax:-
Foreign key(<column-to-be-designated-as-foreign-key>) references parent-table
(<primary-key-of parent table>);

Primary key of parent table becomes the foreign key for the child table.

(a) While creating a table:-
Example:-
CREATE TABLE ORDERS (Order_no int PRIMARY KEY, Order_Date Date,
Cust_ID integer, Amount double, foreign key(cust_ID) references
CUSTOMER(cust_ID));

(b) Add foreign key constraint through ALTER table:-
Example:-
ALTER TABLE ORDERS
ADD foreign key (cust_ID) references CUSTOMER(cust_ID);

Dropping a constraint:-
The syntax is:-

Example:
To remove primary key constraint:-
ALTER TABLE EMPLOYEE
DROP PRIMARY KEY;
To remove foreign key constraint:-
ALTER TABLE table1
DROP FOREIGN KEY fk1;
In this query table name is table1 and foreign key constraint is fk1.

ADDING NEW COLUMN IN A TABLE:-
Syntax:-
ALTER TABLE table-name
ADD col-name data-type(size);
Example: Add a new column address in employee table.
ALTER TABLE EMPLOYEE
ADD address varchar(50);

MODIFYING A TABLE COLUMN:
(i) To change the data-type and size of the column, we use the MODIFY command.
(ii) To change the name of the column, we use the CHANGE command.

(i) To change the data-type and size of the column, we use the MODIFY command:-
The syntax is:-
ALTER TABLE table-name
MODIFY(Col_name newdatatype(newsize));
Example:
ALTER TABLE EMPLOYEE
MODIFY city char(30);

(ii) To change the name of the column, we use the CHANGE command:-
ALTER TABLE <table-name>
CHANGE oldcolumnname newcolumnname col-definition;
Example:
ALTER TABLE EMPLOYEE
CHANGE Dept Department varchar(30);

DROPPING A TABLE:
To remove the entire structure of the table completely, we use the DROP TABLE command.
Syntax:
DROP TABLE <table-name>;
Example:
DROP TABLE EMPLOYEE;

MANIPULATING DATA OF A TABLE:-
(i) Retrieving data : SELECT command
(ii) Inserting data : INSERT command
(iii) Deleting data : DELETE command
(iv) Modification : UPDATE command

(i) SELECT Command:- A SELECT command retrieves information from the database.
(ii) INSERT Command:- This command is used to insert the data in table.
NOTE:- We have already discussed about this command.
(iii) DELETE Command:- It means delete the information from the table. This command is used to remove the rows from the table.
➣ Specific rows are deleted when you specify the WHERE clause.
➣ All rows in the table are deleted if you omit the WHERE clause.
The syntax is:
DELETE FROM <table-name>
WHERE <condition> ;
Example:- Delete those rows whose department is production.
Solution: DELETE FROM EMPLOYEE
WHERE dept=’production’;
Example:- Delete all the records of EMPLOYEE table having salary less than 35000.
Solution: DELETE FROM EMPLOYEE
WHERE Salary<35000;

(iv) UPDATE command:- Values of a single column or group of columns can be updated.
The syntax is:-
UPDATE table-name
SET column_name=value
WHERE condition;

Example:- Change the salary of Vikram to 36000.
Solution: UPDATE EMPLOYEE
SET salary=36000
WHERE Ename=’Vikram’;
Example:- Increase every employee salary by 10%.
Solution: UPDATE EMPLOYEE
SET salary=salary+(salary*0.1);

MAKING SIMPLE QUERIES:-
In SQL queries, we use three clauses mostly:-
(i) SELECT:- What to select
(ii) FROM:- Which Table
(iii) WHERE:- Condition to satisfy

(i) SELECT:-
A SELECT command is used to retrieve information from a table.
➣ If you want to select the all columns from a table, then we use the asterisk(*) in SELECT clause.
Example: – SELECT * FROM EMPLOYEE;
➣ To display specific columns of the table by specifying the column names, separated by commas.
Example: – SELECT Ecode, Ename, salary
FROM EMPLOYEE;

(ii) FROM:-
A FROM clause, specifies the table name that contains the columns.

(iii) WHERE:-
A WHERE clause, specifies the condition.
Syntax:- SELECT column_name
FROM table_name
WHERE condition;

SOME IMPORTANAT POINTS:-
➣ SQL statements are not case sensitive.
➣ To end the SQL command, we write the semicolon(;) at the end of a line followed by <ENTER>.

• Selecting All Columns:- To select all the columns, we use asterisk (*) in SELECT statement.
Example:- SELECT *
FROM EMPLOYEE;

• Selecting Specific Columns:- To display the specific columns of the table, write columns name, separated by commas.
Example:- SELECT Ecode, Ename, salary
FROM EMPLOYEE;

• Eliminating redundant data:- The DISTINCT keyword eliminates duplicate rows from the results. DISTINCT is used in SELECT statement.
Example:- SELECT DISTINCT(Dept)
FROM EMPLOYEE;

ALL keyword:-
SQL allows us to use the keyword ALL to specify explicitly that duplicates are not removed.
Example: SELECT ALL Dept
FROM EMPLOYEE;

Arithmetic Operations:-
The SELECT clause may also contain arithmetic expressions involving the operators +, – , * and / operating on constants or attributes.
Example:- Find the new salary of every employee increased by 25%.
SELECT Ename,salary,salary*0.25
FROM EMPLOYEE;

COLUMN ALIAS:- You can change a column heading by using a column alias.
Example:- SELECT Ename as Name
FROM EMPLOYEE;

Examples of Queries:-

1. List the name and department of those employees where department is production.
Solution:- SELECT Ename, Dept
FROM EMPLOYEE
WHERE Dept=’production’;

2. Find the name and salary of those employees whose salary is more than 20000.
Solution:- SELECT Ename, salary
FROM EMPLOYEE
WHERE salary > 20000;

3. Display the name of those employees who live in New Delhi.
Solution:- SELECT Ename, city
FROM EMPLOYEE
WHERE city=’New Delhi’;

4. List the name of female employees in EMPLOYEE table.
Solution:- SELECT Ename
FROM EMPLOYEE
WHERE sex=’F’;

5. Display the name and department of those employees who work in surat and salary is greater than 25000.
Solution:- SELECT Ename, Dept
FROM EMPLOYEE
WHERE city=’surat’ and salary > 25000;
Or we can write this query in another way:
Solution:- SELECT Ename, Dept
FROM EMPLOYEE
WHERE city=’surat’ && salary > 25000;

6. Display the name of those female employees who work in Mumbai.
Solution:- SELECT Ename
FROM EMPLOYEE
WHERE sex=’F’ and city=’Mumbai’;

7. Display the name of those employees whose department is marketing or RND.
Solution:- SELECT Ename
FROM EMPLOYEE
WHERE Dept=’marketing’ OR Dept=’RND’;

8. List the name of employees who are not males.
Solution:- SELECT Ename, Sex
FROM EMPLOYEE
WHERE sex!=’M’;

QUERIES FOR SPECIAL OPERATORS:-
(i) BETWEEN :- Between two values
(ii) IN :- Match a value in the list
(iii) LIKE :- Match a character pattern
(iv) IS NULL :- Value is null.

(i) BETWEEN :-
Example:- Find the name and salary of those employees whose salary is
between 35000 and 40000.
Solution:-
SELECT Ename, salary
FROM EMPLOYEE
WHERE salary BETWEEN 35000 and 40000;
Or we can write this query in another way:
SELECT Ename, salary
FROM EMPLOYEE
WHERE salary>35000 and salary<40000;

(ii) IN :-
Example:- Find the name of those employees who live in guwahati, surat or jaipur city.
Solution:-
SELECT Ename, city
FROM EMPLOYEE
WHERE city IN(‘Guwahati’,’Surat’,’Jaipur’);

(iii) LIKE :-
% :- It represents any sequence of zero or more characters.
_ :- Represents any single character.
Example:- Display the name of those employees whose name starts with ‘M’.
Solution:-
SELECT Ename
FROM EMPLOYEE
WHERE Ename LIKE ‘M%’;
Example:-Display the name of those employees whose department name ends with ‘a’.
Solution:-
SELECT Ename
FROM EMPLOYEE
WHERE Dept LIKE ‘%a’;
Example:- List the name of employees whose name having ‘e’ as the second character.
Solution:-
SELECT Ename
FROM EMPLOYEE
WHERE Ename LIKE ‘_e%’;

(iv) IS NULL :-
Example:- List the name of employees not assigned to any department.
Solution:-
SELECT Ename
FROM EMPLOYEE
WHERE Dept IS NULL;

IFNULL( ) function:-
If you want to substitute null with a value in the output, you can use IFNULL( ) function.
Syntax:-
IFNULL(<column-name>, value to be substitute)
Example:-
SELECT Ename, IFNULL(salary, “5000”)
FROM EMPLOYEE;

ORDER BY clause:-
You can sort the result in a specific order using ORDER BY clause. The sorting can be done either in ascending or descending order. The default order is ascending.
Example:- Display the list of employees in descending order of employee code.
Solution:-
SELECT *
FROM EMPLOYEE
ORDER BY ecode DESC;
Example:- Display the employee code, name in ascending order of salary.
Solution:-
SELECT Ecode, Ename, salary
FROM EMPLOYEE
ORDER BY salary asc;
Suppose that we wish to list the entire EMPLOYEE relation in descending order of salary.
If several employees have the same salary, we order them in ascending order by employee
code. We express this query in SQL as follows:-
SELECT *
FROM EMPLOYEE
ORDER BY salary desc, Ecode asc;

AGGREGATE FUNCTIONS:

Aggregate functions are functions that take a collection of values as input and return a single value. SQL offers five types of aggregate functions:-
(i) Avg( ) :- To findout the average
(ii) Min( ) :- Minimum value
(iii) Max( ) :-Maximum value
(iv) Sum( ) :-To calculate the total
(v) Count( ) :- For counting

NOTE: – The input to sum ( ) and avg( ) must be a collection of numbers, but the other functions can operate on non-numeric data types e.g.string.

Find the average salary of the employees in employee table.
SELECT avg(salary)
FROM EMPLOYEE;
In some circumstance, we would like to apply the aggregate function not only to a single set of tuples, but also to a group of sets of tuples. We specify this wish in SQL using the group by clause.
The attributes given in the group by clause are used to form groups.
Example: – Find the average salary at each department.
Solution: – SELECT Dept, avg(salary)
FROM EMPLOYEE
group by Dept;
Output for this query

Find the minimum salary in EMPLOYEE table.
Solution:-
SELECT min(salary)
FROM EMPLOYEE;

Find the minimum salary of a female employee in EMPLOYEE table.
Solution:-
SELECT Ename, min(salary)
FROM EMPLOYEE
WHERE sex=’F’;

Find the maximum salary of a male employee in EMPLOYEE table.
Solution:-
SELECT Ename, max(salary)
FROM EMPLOYEE
WHERE sex=’M’;

Find the total salary of those employees who work in Guwahati city.
Solution:-
SELECT sum(salary)
FROM EMPLOYEE
WHERE city=’Guwahati’;

Find the total salary of all employees in EMPLOYEE relation.
Solution:-
SELECT sum(salary)
FROM EMPLOYEE;

Find the number of tuples in the EMPLOYEE relation.
Solution:-
SELECT count(*)
FROM EMPLOYEE;

Count the number of employees who work in RND department.
Solution:-
SELECT count(*)
FROM EMPLOYEE
WHERE Dept=’RND’;

JOINS:
An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.
Consider two tables, CUSTOMER and ORDERS:

Types of Joins:
1. Cartesian Product
2. Equi Join
3. Natural Join

1. Cartesian product: It is known as cross join. The number of tuples in new relation is equal to product of number of tuples of the two tables on which Cartesian product is performed.
SELECT Name, Amount
FROM CUSTOMER, BORROWER;
**Cartesian product is formed when no join conditions exist.

2. Equi Join : Columns are compared for equality.
There are 4 types of equi join:
a. INNER JOIN
b. LEFT JOIN
c. RIGHT JOIN
d. FULL JOIN

a. INNER JOIN: Returns all rows when there is at least one match in BOTH tables.
This joining is known as simple join. You can use JOIN keyword also in the place of INNER JOIN. Both will give same result.
Syntax:
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

Example:
SELECT CUST_NAME, ORDER_ID
FROM CUSTOMER
INNER JOIN ORDERS
ON
CUSTOMER.CUST_ID=ORDER.CUST_ID;

b. LEFT JOIN: Return all rows from the left table, and the matched rows from the right table

c. RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table

d. FULL JOIN: Return all rows when there is a match in ONE of the tables

3. Natural Join: Only one of the identical columns exists.
** The equi join and Natural Join are equivalent except that duplicate columns are eliminated in the Natural Join.

MySQL database connectivity with Python:
• Install python
• Install MySQL
• Install MySQL Driver using following command: (In Command Prompt):
pip install mysql-connector
Note: Make sure your computer is connected with internet.

• To verify, whether the connector is properly installed or not, open python shell and type the following command:
>>>import mysql.connector
>>>

If the command successfully runs (without any error), then the MySQL connector is successfully installed.

• Now, open MySQL and check the current user, by typing the following command in MySQL:
SELECT current_user( );

• Connect MySQL database with python. For this, open Python IDLE and write the following code in python file.

CODE:

import mysql.connector
demodb=mysql.connector.connect(host=”localhost”,user=”root”, passwd=”computer”)
print(demodb)

If you get the following output, then the connection made successfully.

OUTPUT:

• After making successful connection between python and MySQL, now create a
database in MySQL through python. For that, write the following code in python:
import mysql.connector
demodb = mysql.connector.connect(host=”localhost”, user=”root”, passwd=”computer”)
democursor=demodb.cursor( )
democursor.execute(“CREATE DATABASE EDUCATION”)
• After successful execution of the following code, check in MySQL, whether EDUCATION database has been created or not. for that, write the following command in MySQL:

• If you want to check the created database through python, write the following python code to show the present databases in MySQL.

Here, we can see that EDUCATION database has been created.
Create a table in database:
CODE:
import mysql.connector
demodb = mysql.connector.connect(host=”localhost”, user=”root”,
passwd=”computer”, database=”EDUCATION”)
democursor=demodb.cursor( )
democursor.execute(“CREATE TABLE STUDENT (admn_no int primary key,
sname varchar(30), gender char(1), DOB date, stream varchar(15), marks float(4,2))”)

To verify the table created or not, write the following code in python:
import mysql.connector
demodb = mysql.connector.connect(host=”localhost”, user=”root”,
passwd=”computer”, database=”EDUCATION”)
democursor = demodb.cursor( )
democursor.execute (“show tables”)
for i in democursor:
print(i)

Insert the data in the table:

import mysql.connector
demodb = mysql.connector.connect(host=”localhost”, user=”root”,
passwd=”computer”, database=”EDUCATION”)
democursor=demodb.cursor( )
democursor.execute(“insert into student values (%s, %s, %s, %s, %s, %s)”, (1245,
‘Arush’, ‘M’, ‘2003-10-04’, ‘science’, 67.34))
demodb.commit( )

Fetch the data from table:
import mysql.connector
demodb = mysql.connector.connect(host=”localhost”, user=”root”,
passwd=”computer”, database=”EDUCATION”)
https://pythonschoolkvs.wordpress.com Page 33
democursor=demodb.cursor( )
democursor.execute(“select * from student”)
for i in democursor:
print(i)

OUTPUT:

Update the record:
import mysql.connector
demodb = mysql.connector.connect(host=”localhost”, user=”root”,
passwd=”computer”, database=”EDUCATION”)
democursor=demodb.cursor( )
democursor.execute(“update student set marks=55.68 where admn_no=1356”)
demodb.commit( )

Delete a record:
import mysql.connector
demodb = mysql.connector.connect(host=”localhost”, user=”root”,
passwd=”computer”, database=”EDUCATION”)
democursor=demodb.cursor( )
democursor.execute(“delete from student where admn_no=1356”)
demodb.commit( )