Expansions:
DBMS- DatabaseManagement System
RDBMS-Relational DatabaseManagement System
SQL-StructuredQueryLanguage
DDL-DataDefinition Language(Create, Alter,Drop)
DML-DataManipulationLanguage(Insert,Update,Delete)
TCL-TransactionControl Language(commit,rollback,savepoint)
CategoriesofSQL-DDL,DML, TCL
Othernames:
Table-Relation
Column-Attribute-Field
Row-Tuple-Record
AggregateFunctions- MultipleRowFunctions
CartesianProduct- Crossjoin
Equi join-Innerjoin
Meanings:
NULL- None
Degree- Numberof Columns
Cardinality-Number of Rows
Data types:
Integer
Float/Decimal (ex: in decimal(10,2), 10isthetotal digitsand2is thenumber of digitsafter decimal
point)
Char/ Varchar (ex: in char(10),10 meansmaximum of 10 charactersallowed)
Date
Date Format: “YYYY-MM-DD”
Constraints:
Unique
Not Null
Primary Key
ForeignKey
Keys:
CandidateKey
AlternateKey
Primary Key
ForeignKey
Aggregate Functions:
Min()
Max()
Sum()
Avg()
Count()
Hint: sum() andavg() acceptsonlynumeric values.
Sum(),avg() andcount()willalwaysgivesinglenumericoutput.
Count(dept) OR count(distinct dept) will alwaysgivesingle numericoutput.
Wildcard characters: (%, _)
Orderofkeywords inquery:
Select, from, where, groupby, having, order by
Hint: Usegroupbywhen thewords(each/wise/that) is in thequestion.
TypesofJoin:
CartesianProduct/CrossJoin
Equi join/Inner Join
Natural Join
1. What is database?
Organizedcollection of logically relateddata.
2. What is DBMS?
Data Base Management System. A computersystem that allows us to manage
databases.
3. What is MySQL?
MySQL is an RDBMS that can run on all platforms.
4. Differentiate MySQL Server andMySQL Client.
Client connects to server and issue queries.
Server listens andresponds to client.
5. Name some common RDBMS.
MySQL, SQLite, Oracle etc.,
6. What is Data Redundancy?
Duplication of Data
7. What is Constraint/Integrity Constraint?
It is a condition or check applicableon a field or set of fields.
8. What is Domain?
Poolof Valuesfromwhichthe actualvaluesappearinginagivencolumnaredrawn.
9. What is Cartesian product?
Cartesian product of two tables Aand B is writtenas AXB. Rows ofboth tables will be
multiplied and columns will beadded.
10. What is ForeignKey?
A foreignkey is usedto set or represent a relationshipbetween two tables in a database.
Its value is derived from theprimary keyattributeof another table.
11. What is Referential Integrity?
It is a system ofrules that ensures relationship between records inrelated tables are valid.
12. Differentiate
i) DDL andDML
DDL:
Data Definition Language
Deals wit hthe structure of the table.
Createalter, drop
DML:
Data ManipulationLanguage
Deals wit hthe values of the table.
Insert, update, delete
ii) Alter andUpdate
Alter:
It is a DDL Command.
Used to alter thestructure of the tablelike adding a newcolumnor removingthe
existingcolumn.
Ex: Alter table student add column result char(5);
Update:
It is a DML Command
Used to updatethevalues in the table.
Ex: Update student set mark=mark+5;
iii) Drop and Delete
Drop:
It is a DDL Command.
Used to dropthetable or database.
Ex: drop table student;
Delete:
It is a DML Command
Used to deletethe rows of a table.
Ex: delete from student;
iv) Char and Varchar
char(n):
stores a fixed length string between 1 and 255 characters
if the value isof smaller length, addsblank spaces
some space is wasted
varchar(n) :
stores a variable length string
no blanksare added even if value is of smaller length
no wastage of space
v) Where andhaving
Where:
Used to giveconditions on individual rows
Aggregate functions cannot be usedwith where clause
Ex: select *from student where marks>40;
Having:
Used to giveconditionon group of rows
Aggregate functions canbe used with having clause
Ex: select class,count(*) from student group by class having count(*)>3;
vi) Groupby and order by
Groupby:
Used to grouptherows basedon the givencolumn.
Havingclause can beused with group by.
Ex: select class, count(*) from student group by class;
Order by:
Used to displaytherecords in ascending or descendingorder basedon the given
column.
Keywords asc or desc is usedwith order by.
Ex:select * from student order bymarks desc;
vii) Primary key andForeign key
Primary key:
Primary key column does not allow repeatedvalues or null values.
A table can haveonly oneprimary key.
A table that contains primary keyacts as a parent table.
Foreignkey:
Foreignkey column allows repeatedvalues and Null values but it allows only the
values of related column in the parents table.
A table can havemore than oneForeign key.
A table that contains Foreign keyacts as a child table.
viii) Primary key andUnique
Primary key:
Columnwith Primary keyconstraint does not allow repeatedvalues or null values.
A table can haveonly oneprimary key.
Unique:
Columnwith Unique constraint does not allowrepeated values nut allows null
values.
In a tablemany columns can haveunique constraint.
ix) Candidate keyand Alternate key
Candidate key:
Columns which uniquely identifies a rowin a table
A table can havemany candidate keys.
Alternate Key:
A candidate key which is not aPrimarykey but can serve as primary key.
Composite Key:
A composite key is made by the combination of two or more columns in a table
that can be used to uniquely identify each row in the table when the columns are
combined uniqueness of a row is guaranteed, but when it is taken individually it
does not guarantee uniqueness.
mysql>create table emp (EID int, DEPT varchar(10),Ename varchar(10),primary
key(EID,DEPT));
Query OK, 0 rows affected (0.06 sec)
mysql> desc emp;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| EID | int(11) | NO | PRI | 0 | |
| DEPT | varchar(10) | NO | PRI | | |
| Ename | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.03 sec)
mysql>insert into emp values(101,"Sales","Ajay");
Query OK, 1 row affected (0.01 sec)
mysql>insert into emp values(101,"Marketing","Guru");
Query OK, 1 row affected (0.00 sec)
mysql>insert into emp values(102,"Marketing","Sai");
Query OK, 1 row affected (0.00 sec)
mysql>insert into emp values(102,"Marketing","Hari");
ERROR 1062 (23000): Duplicate entry '102-Marketing' for key 'PRIMARY'
x) Equi join and Natural join
Equi- join:
The joinin which columns from two tables are compared for equalityby giving
condition
Duplicate columns areshown
Natural Join
The joinin which columns from two tables are compared for equalitywithout giving
condition
No duplicationof columns
xi) Count(*) andcount( column name)s
COUNT(*) returns thecount of all rows in the table, whereas COUNT (column name)
counts the numberof non-NULL values in that column.
Example: Table : EMPL
EMPNO ENAME JOB SAL DEPTNO
8369 SMITH CLERK 2985 10
8499 ANYA NULL 9870 20
8566 AMIR SALESMA 8760 30
N
8698 BINA MANAGER 5643 20
8912 SUR NULL 3000 10
Here select Count(*) from empl gives 5 as output but
select count(job) from empl gives 3 as output .
Hint 1: *comes only with select clause i.e., select * (OR) select count(*)
* Does not come in delete query.
Commonly asked questions:
Correct theerror:
Select* fromstudentwheremark=NULL;
Ans: Select * fromstudent wheremarkisNULL;
Hint1 : Relationaloperatorswill not compareNULL.
if markcolumnhasNULL, thenwheremark<50wil not comparewithNULL .
Hint2 : !=and<> aresame
Hint3: while checkingconditionin samecolumnwheremark >30 and<60 is incorrect
wheremark>30 and mark<60 iscorrect.
Inpythonwecancheckconditionin bothways.
Consider the following tables CARDEN and CUSTOMER and answer the following questions:
a. Identify the degree and cardinality of the CUSTOMER TABLE.
b. What isthe degree and cardinality of CARDEN table?
c. Degree andcardinalityof Cartesianproduct of 2tables.
d. Identify the candidatekeysfrom the CARDEN table.
e. Which field should be made the primary key and which is alternate key? Justify your
Answer.
f. Identify the Foreign key from the CUSTOMER table.
g. Selectingfrom2tables.
Display thecar name, color andcustomer name for thecustomer code1001.
Ans:
a)Degree-3,cardinality-4
b) Degree-6,cardinality-5
c) Rowsof bothtableswill bemultipliedandcolumnswill beadded.
SoDegree( Number of columns)- 6+3=9
Cardinality(Number of Rows)-5x 4=20
d) Candidatekeys: Ccode, Carname
e) Primarykey: Ccode, asit will never repeat andit cannot benull.
Alternate key-Carname
f)Foreignkey:Ccode
g) select CarName,Color,CnamefromCARDEN, CUSTOMER where
CARDEN.Ccode=CUSTOMER.CcodeandCUSTOMER.Code=1001;
or
select CarName, Color, CnamefromCARDENca, CUSTOMER cu whereca.Ccode=cu.Ccodeand
cu.Code=1001;
Python MySQL Connectivity
fechtone()
fetchone() methodfetchesonerow(next row) or asingle recordat atime.
It willreturnNoneif nomore rows/recordsareavailable.
Itsreturn typeis tuple.
fetchall()
fetchall() fetchesalltherowsfromaquery resultset.
If there is norowstofetch in theresultset, giveserror.
Itsreturn typeis list
fetchmany(size)
fetchmany(size)fetches the number ofrowsmentionedin thesizefromaqueryresultset.
Anemptylist isreturnedif there is norowstofetchin theresultset.
Itsreturn typeis list
rowcount
rowcount is areadonly attributeof acursor object.
rowcount returnsthenumberof rowsfetchedfromresultset or thenumber of rowsaffectedinthe
table
bytheDML commands.
is_connected()
It checksforsuccessful connectionbetweenPythonandMySQL
Example:
if mycon.is_connected(): #myconis theconnectionobject
print(“Successfulconnection”)
else:
print(“ConnectionFailure”)
commit()Vsrollback()
commit() is usedtomakethechangespermanent indatabase.
It is usedafter DML commands.
Example:mydb.commit() #mydbis theconnectionobject
rollback() is usedtoundothechangesin thedatabase.
Changesdoneafter commit() will beundone.
Note: autocommit is ONbydefaultinMySQL.
If wesetautocommit asOFF, weshouldcommit explicitlyafter performingDML operations.
