0% found this document useful (0 votes)
23 views6 pages

XII CS Unit 3 Notes

Uploaded by

sahilmeena3435
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
0% found this document useful (0 votes)
23 views6 pages

XII CS Unit 3 Notes

Uploaded by

sahilmeena3435
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 6

Database is a systematic collection of data.

Databases ALTERNATE KEY is a column or group of columns in a


support storage and manipulation of data. Databases table that uniquely identify every row in that table. A
make data management easy. table can have multiple choices for a primary key but
Need of database: only one can be set as the primary key. All the keys
Accuracy which are not primary key are called an Alternate Key.
Ease of updating data
Security of data FOREIGN KEY is a column that creates a relationship
Data integrity between two tables. The purpose of Foreign keys is to
Advantages: maintain data integrity and allow navigation between
Databases reduce Redundancy two different instances of an entity.
Database controls Inconsistency
Database facilitates Sharing of Data Data Definition Language:
Database ensures Security DDL commands are used for creating databases and
Database maintains Integrity tables. It contains necessary statements for creating,
Database enforce standards manipulating, altering and deleting tables.
1. CREATE (create database and table)
Relation: is a fundamental concept that refers to a table 2. ALTER (alter table)
within a relational database. Each table, or relation, 3. DROP (delete table)
consists of rows and columns, where each row
represents a record and each column represents an Data Manipulation Language:
attribute of the record DML commands are used for manipulating Data.
1. INSERT (insert data in table)
Attribute: Each column in a Table. Attributes are the 2. UPDATE (update data in table)
properties which define a relation. e.g., Rollno, Name 3. DELETE (delete data from table)
etc.
Data type (char(n), varchar(n), int, float, date),
Tuple: It is nothing but a single row of a table, which
contains a single record. CHAR VARCHAR
Used to store character string Used to store variable length
value of fixed length. alphanumeric data.
Domain: It is a collection of values from which the value The maximum this data type can
is derived for a column. The maximum no. of characters hold is up to
the data type can hold is 255 Pre-MySQL5.0.3: 255 characters.
Degree: The total number of attributes which in the characters. Post-MySQL5.0.3: 65535 characters
relation is called the degree of the relation.
Uses static memory allocation. Uses dynamic memory allocation.

Cardinality: The Total number of rows present in the


Constraints
Table/relation.
Constraints are used to limit the type of data that can go
into a table. This ensures the accuracy and reliability of
Keys: key is an attribute or set of an attribute which
the data in the table.
helps you to identify a row (tuple) in a relation (table).
They allow you to find the relation between two tables.
NOT NULL
Keys help you uniquely identify a row in a table by a
- Ensures that a column cannot have a NULL value
combination of one or more columns in that table.
UNIQUE
PRIMARY KEY is a column or group of columns in a table
- Ensures that all values in a column are different
that uniquely identify every row in that table. A table
cannot have more than one primary key. The values in
PRIMARY KEY
the respective columns must be Unique & Not NULL
- A combination of a NOT NULL and UNIQUE. Uniquely
identifies each row in a table
CANDIDATE KEY is a set of attributes that uniquely
identify tuples in a table. The Primary key should be
selected from the candidate keys. Every table must have
at least a single candidate key. A table can have multiple
candidate keys but only a single primary key.
SQL Commands (DDL) SQL Commands (DML)

CREATE DATABASE <NAMEOFDATABASE>; INSERT INTO <TABLENAME>


VALUES (VALUE1, VALUE2, VALUE3, VALUE4);
SHOW DATABASES;
Note: Char, Varchar & Date to mentioned in ‘ ’ quotes
DROP DATABASE <NAMEOFDATABASE>;
UPDATE <TABLENAME>
USE <NAMEOFDATABASE>; SET COLUMNNAME=NEWVALUE
WHERE <CONDITION>;
SHOW TABLES;
Note: Char, Varchar & Date to mentioned in ‘ ’ quotes
CREATE TABLE <NAME OF TABLE>
(COLUMNNAME1 DATATYPE(SIZE), DELETE FROM <TABLENAME>;
COLUMNNAME2 DATATYPE(SIZE), Note: it will delete all rows from table
COLUMNNAME3 DATATYPE(SIZE),
COLUMNNAME4 DATATYPE(SIZE), DELETE FROM <TABLENAME>
); WHERE <CONDITION>;

DESCRIBE <NAME OF TABLE>; SELECT * FROM <TABLENAME>;


OR
DESC <NAME OF TABLE>; SELECT * FROM <TABLENAME>
WHERE <CONDITION>;
DROP TABLE <NAME OF TABLE>;
SELECT COLUMN1, COLUMN2 FROM <TABLENAME>;
ALTER TABLE <NAME OF TABLE>;
ADD NEWCOLUMNNAME DATATYPE (SIZE); SELECT COLUMN1, COLUMN2 FROM <TABLENAME>
WHERE <CONDITION>;
ALTER TABLE <NAME OF TABLE>;
DROP COLUMNNAME; SELECT COLUMN2, COLUMN1 FROM <TABLENAME>
WHERE <CONDITION>;
ALTER TABLE <NAME OF TABLE>;
ADD PRIMARY KEY (COLUMNNAME); ALIASING
OR SELECT COLUMN1 AS ‘NEW COLUMN NAME’
ALTER TABLE <NAME OF TABLE>; FROM <TBLENAME>;
MODIFY COLUMNNAME DATATYPE (SIZE) PRIMARY KEY
SELECT COLUMN1 AS ‘NEW COLUMN NAME’
ALTER TABLE <TABLENAME> FROM <TBLENAME>
DROP PRIMARY KEY WHERE <CONDITION>;

ALTER TABLE <NAME OF TABLE>; DISTINCT


ADD UNIQUE (COLUMNNAME); SELECT DISTINCT(COLUMNNAME)
OR FROM <TABLENAME>;
ALTER TABLE <NAME OF TABLE>
MODIFY COLUMNNAME DATATYPE (SIZE) UNIQUE; SELECT DISTINCT(COLUMNNAME)
FROM <TABLENAME>
ALTER TABLE <NAME OF TABLE> WHERE <CONDITION>;
MODIFY COLUMNNAME DATATYPE (SIZE) NOT NULL;
WHERE CLAUSE
Conditions can be set with help of following operators:

Relational operators are: < ; <= ; = ; != or <> ; >= ; >


Logical operators are: AND ; OR ; NOT
Comparison operator for special value NULL: IS
SELECT * FROM <TABLENAME> SELECT * FROM <TABLENAME>
WHERE COLUMN1 = VALUE; WHERE NAME LIKE ‘A%’;

SELECT * FROM <TABLENAME> SELECT * FROM <TABLENAME>


WHERE COLUMN1 < VALUE; WHERE NAME LIKE ‘_ A%’;

SELECT * FROM <TABLENAME> SELECT * FROM <TABLENAME>


WHERE COLUMN1 <= VALUE; WHERE NAME LIKE ‘% A’;

SELECT * FROM <TABLENAME> Aggregate Function


WHERE COLUMN1 >= VALUE; SUM() Returns the sum of the given column.
MIN() Returns the minimum value in the given column.
SELECT * FROM <TABLENAME> MAX() Returns the maximum value in the given column.
WHERE COLUMN1>VALUE; AVG() Returns the Average value of the given column.
COUNT() Returns the total number of values/ records as per
given column.
SELECT * FROM <TABLENAME>
WHERE NOT COLUMN1=VALUE; Null values are excluded while (avg, count) aggregate
OR function is used
WHERE COLUMN1 != VALUE; Consider a table Emp having following records as-
OR Code Name Sal
WHERE COLUMN1 <> VALUE; E1 Mohak NULL
E2 Anuj 4500
SELECT * FROM <TABLENAME> E3 Vijay NULL
WHERE COLUMN1=VALUE AND COLUMN2=VALUE; E4 Vishal 3500
E5 Anil 4000
SELECT * FROM <TABLENAME> SQL Queries Result of query
WHERE COLUMN1=VALUE OR COLUMN2=VALUE; > Select Sum(Sal) from EMP; 12000
> Select Min(Sal) from EMP; 3500
SELECT * FROM <TABLENAME> > Select Max(Sal) from EMP; 4500
WHERE NOT COLUMN1=VALUE; > Select Count(Sal) from EMP; 3
> Select Avg(Sal) from EMP; 4000
Checking NULL > Select Count(*) from EMP; 5
SELECT * FROM <TABLENAME>
WHERE COLUMNNAME IS NULL; Aggregate Functions & Group

Using SUM (<Column>)


SELECT * FROM <TABLENAME> This function returns the sum of values in a given column or
WHERE COLUMNNAME IS NOT NULL; expression.
> Select Sum(Sal) from EMP;
> Select Sum(DISTINCT Sal) from EMP;
SELECT * FROM <TABLENAME> > Select Sum (Sal) from EMP where City=‘Jaipur’;
WHERE COLUMN1 IN (VALUE1, VALUE2, VALUE3); > Select Sum (Sal) from EMP Group By City;
> Select Job, Sum(Sal) from EMP Group By Job;
SELECT * FROM <TABLENAME>
WHERE COLUMN1 BETWEEN VALUE1 AND VALUE2; Using MIN (<column>)
This function returns the Minimum value in the given column.
> Select Min(Sal) from EMP;
SELECT * FROM <TABLENAME> > Select Min(Sal) from EMP Group By City;
ORDER BY COLUMNNAME; > Select Job, Min(Sal) from EMP Group By Job;

SELECT * FROM <TABLENAME> Using MAX (<Column>)


ORDER BY COLUMNNAME DESC; This function returns the Maximum value in given column.
> Select Max(Sal) from EMP;
SELECT * FROM <TABLENAME> > Select Max(Sal) from EMP where City=‘Jaipur’;
WHERE NAME LIKE ‘VALUE’; > Select Max(Sal) from EMP Group By City;

Using AVG (<column>)


SELECT * FROM <TABLENAME>
This functions returns the Average value in the given column.
WHERE NAME LIKE ‘A%’; > Select AVG(Sal) from EMP;
> Select AVG(Sal) from EMP Group By City;
Using COUNT (<*|column>) select * from emp, dept where emp.deptno = dept.deptno;
This function returns the number of rows in the given column.
> Select Count ( * ) from EMP;
> Select Count(Sal) from EMP Group By City;
> Select Count(*), Sum(Sal) from EMP Group By Job;

Note: COUNT(*) will count all the rows in the table, including
NULL values. On the other hand, COUNT(column name) will
count all the rows in the specified column while excluding
NULL values

Aggregate Functions & Conditions


You may use any condition on group, if required.
HAVING <condition> clause is used to apply a condition on a
group.
From the above query, we can observe that while doing equi-
> Select Job,Sum(Pay) from EMP join we have to give equality condition on common column of
Group By Job HAVING Sum(Pay)>=8000; both tables so that it picks related records
> Select Job, Sum(Pay) from EMP select * from emp e, dept d where e.deptno = d.deptno;
Group By Job HAVING Avg(Pay)>=7000;
Natural Join
> Select Job, Sum(Pay) from EMP The JOIN in which only one of the identical columns exists is
Group By Job HAVING Count(*)>=5; called Natural Join. It is similar to Equi-join except that
duplicate columns are eliminated in Natural join that would
> Select Job, Min(Pay),Max(Pay), Avg(Pay) from EMP Group otherwise appear in Equi-Join.
By Job HAVING Sum(Pay)>=8000;
Select * from emp NATURAL JOIN dept
Note:- Where clause works in respect of the whole table but In NATURAL JOIN condition the join condition is not required it
Having works on Group only. If Where and Having both are automatically joins based on the common column value
used then Where will be executed first.
Interface of python with an SQL database
Joins: equi-join and natural join Before we connect python program with any database like
A join is a query that combines rows from two or more tables. MySQL we need to build a bridge to connect Python and
In a JOIN query more than one table are listed in the FROM MySQL.
clause.
To provide interface between database and programming
MySQL provides various type of Joining: equi-join and natural language:
join 1. Connection must be established.
2. mysql must be installed on the system
EQUI-JOIN 3. Database and Table also must be already created.
The join, in which columns are compared for equality is called
Equi-Join. In equi-join we put (*) in the select list therefore the import mysql.connector Or
common column will appear twice in the output. import mysql.connector as ms

Here “ms” is an alias, so every time we can use “ms” in place


of “mysql.connector”

To create connection, connect() function is used


Its syntax is:
connect(host=<server_name>,user=<user_name>,passwd=<p
assword>[,database=<database>])

Here server_name means database servername, generally it is


given as “localhost”
User_name means user by which we connect with mysql
generally it is given as “root”
Password is the password of user “root”
Database is the name of database whose data(table) we want
to use

import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root", # Example: change table structure (add, edit, remove column
passwd=",database=“school”) of a table)
print(mydb)
import mysql.connector
After successful execution of above statements in python mydb=mysql.connector.connect(host="localhost",user="root",
following out will be displayed passwd="",database= "student")
<mysql.connector.connection.MySQLConnection object at mycursor=mydb.cursor()
0x022624F0> mycursor.execute("alter table emp add (bonus int(3))")
mycursor.execute("desc emp")
import mysql.connector for x in mycursor:
mydb=mysql.connector.connect(host="localhost",user="root", print(x)
passwd="", database="school")
print(mydb) # Example: Insert record in a table
if mydb.is_connected():
print("working!!") import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",
is_connected() function returns true if connection is passwd="",database= "student")
established otherwise false mycursor=mydb.cursor()
“mys” is an alias of package “mysql.connector” eno=int(input("Enter eno"))
“mycon” is connection object which stores connection ename=input("Enter ename")
established with MySQL edept=input("Enter dept")
Connect() functions is used to establish connection with given sal=int(input("Enter salary"))
parameters. sql=("insert into emp (eno, ename, edept, sal)
values(%s,%s,%s,%s )
Cursor object: val=(101,’Neelu’,’HR’,35000)
The MySQLCursor class instantiates objects that can execute mycursor.execute(sql,val)
operations such as SQL statements. Cursor objects interact
with the MySQL server using a MySQLConnection object. or
sql="insert into emp (eno, ename, edept, sal)
Cursor stores all the data as a temporary container of values({},’{}’,’{}’,{})”.format(101,’Neelu’,’HR’,35000)
returned data and we can fetch data one row at a time from mycursor.execute(sql)
Cursor.
mydb.commit()
TO CREATE CURSOR
Cursor_name = connectionObject.cursor() #Example: Search a record
For e.g. mycursor = mycon.cursor()
import mysql.connector
TO EXECUTE QUERY mydb=mysql.connector.connect(host="localhost",user="root",
We use execute() function to send query to connection passwd="",database="student")
Cursor_name.execute(query) mycursor=mydb.cursor()
For e.g. mycursor.execute(‘select * from emp’) nm=input("enter name")
mycursor.execute ("select * from emp where ename=' "+nm+"
#Example: (creating database) ' ")
for x in mycursor:
import mysql.connector print (x)
mydb=mysql.connector.connect(host="localhost",user="root",
passwd="") #Example: Delete a record
mycursor=mydb.cursor()
mycursor.execute("create database if not exists school") import mysql.connector
mycursor.execute("show databases") mydb=mysql.connector.connect(host="localhost",user="root",
for x in mycursor: passwd="",database= "student")
print(x) mycursor=mydb.cursor()
mycursor.execute("delete from emp where eno=100")
#Example: (creating table) mydb.commit()

import mysql.connector #Example: Update a record


mydb=mysql.connector.connect(host="localhost",user="root",
passwd="",database="student") import mysql.connector
mycursor=mydb.cursor() mydb=mysql.connector.connect(host="localhost",user="root",
mycursor.execute("create table emp(eno int(3) ,ename passwd="",database="student")
varchar(20),dept varchar(10)), sal int(4)") mycursor=mydb.cursor()
mycursor.execute("update emp set sal=1000 where eno=101")
mydb.commit()
To extract data from cursor following functions are used:

fetchall(): it will return all the record in the form of list of


tuples.
fetchone(): it return one record from the result set. i.e. first
time it will return first record, next time it will return second
record and so on. If no more record it will return None
fetchmany(n): it will return n number of records. if no more
record it will return an empty tuple.
rowcount: it will return number of rows retrieved from the
cursor so far.

import mysql.connector as mys


mycon=mys.connect(host="localhost",user="root",passwd="",
database="student")
mycursor=mycon.cursor()
mycursor.execute('select * from emp')
mydata=mycursor.fetchall()
nrec=mycursor.rowcount
print('Total records fetch:',nrec)
for row in mydata:
print(row)

import mysql.connector as mys


mycon=mys.connect(host="localhost",user="root",passwd="",
database="student")
mycursor=mycon.cursor()
mycursor.execute('select * from emp')
mydata=mycursor.fetchone()
nrec=mycursor.rowcount
print('Total records fetch:',nrec)
print(mydata)
mydata=mycursor.fetchone()
nrec=mycursor.rowcount
print('Total records fetch:',nrec)
print(mydata)

import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",
passwd="",database= "student")
mycursor=mydb.cursor()
mycursor.execute("select * from emp")
row=mycursor.fetchone()
while row is not None:
print(row)
row = mycursor.fetchone()

import mysql.connector as mys


mycon=mys.connect(host="localhost",user="root",passwd="",
database="student")
mycursor=mycon.cursor()
mycursor.execute('select * from emp')
mydata=mycursor.fetchmany(3)
nrec=mycursor.rowcount
print('Total records fetch:',nrec)
for row in mydata:
print(row)

You might also like