Chapter 5
Overview
MySQL Revision Tour
❖ Introduction
❖ Relational Data Model
❖ Relational DBMS
❖ Creating, accessing Databases
❖ MySQL Commands
❖ Various MySQL queries
❖ Dropping tables
Introduction
This chapter deals with the basics of MySQL commands and its implementation with tables.
SQL stands for Structured Query Language
--- It is a database application
--- It supports DBMS - Database Management System
--- It maintains the Data in Tabular format
Data: - It is the raw fact about something which does not give complete information
ex- Raj, 101, Com
Record - It is collection of data which gives complete information
ex- Ano Name Stream
101 Raj Com - record
Table: - Collection of Records is known as table.
Database: - Collection of Tables
Database Management System: - Collection of Databases and its management is known as
DBMS
RDBMS - DBMS with tables having relationship is known as Relation DBMS
Table - Stud Entity
Ano Name Stream Age
101 Raj Com 15
102 Anil Sci 16 Tuple
103 Sunil Sci 15
Attributes
Database supports Relational Data Model i.e, Data is in form of tables (Rows & Columns)
Important Terms: -
Column/Attribute - Columns of table is known as Attribute
8/15/2025
Rows / Tuple - Rows of the table is known as Tuple
Relation/Entity - Name of the table is known as Entity
Domain - the pool of values from which actual values are derived is known as Domain
27
Degree - No of columns in a table is known as degree
Cardinality - No of rows in a table is known as cardinality
View - it is a virtual table which gets its data from underlying base table
KEYS
❖ Primary key - a column in a table having unique values and not having blank values is known
as primary key.
❖ Candidate key - columns eligible to become primary key
ex - ano, email, phone
❖ Alternate key - the candidate keys other than primary key
ex- email, phone
❖ Foreign key - it is a column which derives it values from another tables primary key
it maintains a relationship between the two tables.
❖ Composite primary key – combination of two or more columns as primary key is known as
composite primary key
To Open MySQL
Installing the Software for MySQL
❖ Click on start
❖ Open the website [Link] ❖ type MySQL
❖ Click on download ❖ select MySQL command line
❖ Setup the file downloaded ❖ give the password (given at
❖ Keep clicking on next installation time)
❖ Give a password of minimum 8 characters ❖ press enter
❖ Click on install
Note: Server - it is a machine which serves the request of the client
Client - it is a machine which makes request to the server
Classification of SQL Commands
1. DDL (Data Definition Language) ex- Create, Alter, Drop
2. DML (Data Manipulation Language) ex- Select, Insert, Delete, Update
3. TCL (Transaction Control Language) ex- Commit, Savepoint, Rollback
MySQL Datatypes:-
Datatype Description Example
integer(n) stores integers only ano integer(4)
int(n) ano=9999
char(n) Used to store only characters name char(10)
n – no of characters name=’Rahul’
varchar(n) Used to store characters & numbers both address varchar(20)
address=’#10-1-1/5, Defence’
double/float Used to store decimal values price double
price=25.56
date Used to store date in format ‘yyyy-mm-dd’ dob date;
8/15/2025
dob=’1990-05-22’
datetime Used to store date & time both in format doh datetime;
‘yyyy-mm-dd H:i:s’ doh=’2022-08-23 08:11:12’
28
Difference between char & varchar datatype
Char Varchar
it is fixed length datatype it is a variable length datatype
Example - name char(10) Example - name varchar(10)
it allocates 10 bytes fixed to name name = 'Raj'
name = 'Raj' it allocates 3 bytes only
it is faster in processing it is slow in processing
Displaying List of Mysql Databases
show databases; (press enter)
Creating a new database
create database tps12b;
#tps12b is the name of the database
To access/select a database
use tps12b;
TO drop a database
drop database tps12b;
Display list of tables from a database
show tables;
Creating a Table: Example-
Syntax create table sample
create table <table-name> (sno integer(3),
( <col1> <datatype-size> [constraint], name varchar(10));
<col2> <datatype-size> [constraint], Note: Above table is created without
<coln> <datatype-size> [constraint] using constraints
);
To describe the table(to see tables structure) NOTE-
Syntax: - desc <table-name>; To get practice table
google – scott table for mysql
example: - desc sample;
copy and paste the table with data into
the current database
Insert rows to table
insert into sample values (101,'Arjun');
To display all rows of table
8/15/2025
select * from <tablename>
ex-
select * from sample;
29
Constraints in MySQL: -
Constraints: - restriction applied on columns of a table while inserting records is known as
constraints
Types of Constraint:
1. Column Constraint: - applied while creating column of a table
2. Table Constraint: - applied at the end of table creation
Unique – it is used to maintain only unique values, but also allows null values. Ex- phone column
Not Null – does not allow blank values, but allows duplicate. Ex- name column
Primary key – Unique + Not Null. Ex- Ano, Eno
Default – used to specify a default value which is inserted to the column when left blank
Check – used to specify set of values to be inserted from within
Foreign Key – used to specify the relationship between two tables.
Creating Table Using Column Constraints:
Create table student
(ano int(3) primary key,
name varchar(10) not null,
age int(3) check (age>=14 and age<=17),
stream char(10) default ‘Com’
);
To insert rows into a table: -
1st Way (normal method)
Insert into student values (101, ’Amit’, 15, ’Sci’);
2nd way (selected column method)
Insert into student(ano,name,age) values(102, ’Sumit’, 14);
Update Command:
Syntax: update <tab-name> SET <col-name>=<value>, <col-nam>=<value>
[where <condition>];
Example: - Update student set age=17 where ano=101;
To update all rows: update student set age=15;
Delete Command: -
Syntax: - delete from <tabl-name> [where <condition>];
Example: delete from student where ano=103;
To delete all rows of a table: delete from student;
Drop Command:- It is used to drop the database object like table, view, etc…
Syntax: - drop table <tab-name>; or drop database <db-name>;
8/15/2025
Example: - drop table student;
View Name
To Drop View: - drop view stud_view;
View: - It is a virtual table which derives its records from underlying base table.
30
Types of Views: -
Simple View: view created on a single table is known as simple view
Complex View: view created on multiple tables is known as complex view. (not in syllabus)
Creating a simple view
Create view emp_view as (select empno, ename,
job, sal, deptno from emp where deptno=10);
To View the records of the view
Select * from emp_view;
Alter Command: - it is a DDL command used to modify the structure of tables
Adding column
Drop column
modify column
Rename table Rename column(change)
Adding a column:
Syntax: Alter table <tab-name> add <col-name> <dtype> (Size);
Example: Alter table student add phone varchar(10);
Drop a column:
Syntax: Alter table <tab-name> drop <col-name>;
Example: Alter table student drop phone;
Modify a column:
Syntax: Alter table <tab-name> modify <col-name> <dtype>(size);
Example: Alter table student modify name varchar(15) not null;
Rename a column using change:
Syntax: Alter table <tab-name> change <old-col-name> <new-col-name> <dtype>(size);
Example: Alter table student change phone mobile varchar(10) not null;
Rename a table:
Syntax: Alter table <tab-name> rename to <new-tab-name>;
Example: Alter table student rename to stud;
Creating Table Using Foreign Key Constraints:
8/15/2025
create table marks
(mid int(3) primary key,
ano int(3) not null,
sub1 int(3) not null,
31
sub2 int(3) not null,
exam char(10),
foreign key(ano) references stud(ano) );
Inserting NULL values into a table:
Insert into stud values(104,'Roma',16,NULL);
Using where clause: - where clause is used to provide condition on queries
Syntax: select * from <tab-name> where <condition>;
Example: select * from stud where stream=’Sci’;
Using distinct keyword: it is used to display the unique values of a column
Ex: select distinct stream from stud;
Using ALL keyword: it is used to display all values of a particular column
Ex: select all stream from stud;
Using keywords And & Or:
Ex: select * from emp where job=’MANAGER’ and deptno=10;
Select * from emp where job=’MANAGER’ or deptno=10;
Using IN operator: it is used to provide multiple conditions on select query
Ex: Select * from emp where deptno in(10,20);
select * from stud where stream in(‘Com’,’Sci’);
NOT IN: display all students studying in other than Sci and Com
Ex: select * from stud where stream not in(‘Sci’,’Com’);
Using between operator: displays the record in between given range include the range
Ex- select * from emp where sal between 2000 and 3000;
Select * from emp where hiredate between ‘2020-08-10’ and ‘2020-08-22’;
Using like operator: - it is used to match the records based on pattern
Q- Display all employees whose name starts with ‘S’
A- select * from emp where ename like ‘S%‘;
Q- Display all employees whose name ends with ‘H’
A- select * from emp where ename like ‘%H’;
Q- Display all employees whose name has T as 2nd last character
A- select * from emp where ename like ‘%T_’;
Q- Display all employees having name as 4 characters.
A- select * from emp where ename like ‘____’; (4 underscore symbols)
Q- Display all employees having 3rd character of name as ‘L’
8/15/2025
A- select * from emp where ename like ‘__L%’;
Accessing NULL Values (using IS/IS NOT):
32
Q- Display the list of employees not getting commission:
A- select * from emp where comm is NULL;
Q- Display the list of employees getting commission:
A- select * from emp where comm is not NULL;
Cartesian Product:
the product of rows of two tables and sum of columns of two tables is known as cartesian product.
Ex:- if student table have 3 rows, 4 columns and fees table have 4 rows, 5 columns
cartesian product result will be 3x4=12 rows and 4+5=9 columns
Query: select * from stud, fees;
Fetching Records from multiple table:
select [Link], name, paid, dop, balance, age, stream
from stud, fees
where [Link]=[Link];
OR
select [Link],name,paid,dop,balance,age,stream
from stud s, fees f
where [Link]=[Link];
Applying more condition and order by:
select [Link],name,paid,dop,balance,age,stream
from stud s, fees f
where [Link]=[Link] and stream=’Sci’ order by balance;
Fetching Records from Emp & Dept Table:
select empno, ename, [Link], dname, loc,sal
from emp e, dept d
where [Link]=[Link] and [Link] in(10,20)
order by sal desc;
8/15/2025
* * * *
33