Database Concept with SQL Commands for grade 12

VKS- LEARNING_HUB
VKS- LEARNING_HUB DATABASE & SQL
DATABASE & SQL

VKS- LEARNING_HUB
DATABASE & SQL
What is Data ??
Data are plain or raw facts.
It is a collection of alphabets, number
symbols , observation, measurement or
even description about some thing in
consideration.
Name Age
Height
Weight
Mobile
No
email
Picture, Pdf, Video etc can also be
considered data
Brand
Model
Size
Colour
Cost
For Example
Mobile Data
Student Data
Name, Age, Height, Weight, Mobile No,
Email etc are some data related to student
Brand, Model , Cost, Size, Colour etc are
some data related to Mobile Phone
When data are processed, organized, structured
or presented in a given context so as to make
them useful, they are called Information.

VKS- LEARNING_HUB
DATABASE & SQL
What is the Database?
A database is an organized collection of structured information, or
data, typically stored electronically in a computer system. A
database is usually controlled by a database management system
(DBMS)
 The DATABASE MANAGEMENT SYSTEM ( DBMS ) is a software
application used to create , manage and administer databases.
 The DBMS also provides the necessary tools required for the design
and development of databases.
 Database Management System is a vital component of most of the
software applications . Whether you are working on a enterprise
software development project or you may be developing a small
website , the database design and development skills are needed in
almost all software applications.
What is DBMS?

VKS- LEARNING_HUB
DATABASE & SQL
Why Database System is used? (Advantages)
Databases reduces Redundancy
It removes duplication of data because data are kept at one
place and all the application refers to the centrally maintained
database.
Database controls Inconsistency
When two copies of the same data do not agree to each other,
then it is called Inconsistency. By controlling redundancy, the
inconsistency is also controlled.
Database facilitate Sharing of Data
Data stored in the database can be shared among several
users.
Database ensures Security
Data are protected against accidental or intentional disclosure
to unauthorized person or unauthorized modification.
Database maintains Integrity
It enforces certain integrity rules to insure the validity
orcorrectness of data. For ex. A date can’t be like 25/25/2000





.

Data Model
Data model describes ‘How data is organized or stored’ in the
database. It may be-
 Relational Data Model
In this model data is organized into Relations or Tables (i.e.
Rows and Columns). A row in a table represents a relationship of
data to each other and also called a Tuple or Record. A column
called is Attribute or Field.
 Network Data Model
In this model, data is represented by collection of records and
relationship among data is shown by Links.
 Hierarchical Data Model
In this model, Records are organized as Trees. Records at top
level is called Root record and this may contains multiple directly
linked children records.
 Object Oriented Data Model
In this model, records are represented as a objects. The
collection of similar types of object is called class.

VKS- LEARNING_HUB
DATABASE & SQL
Database Concepts
DATA MODELS
RELATIONAL MODEL
Relational data model is the primary data model, which is used widely
around the world for data storage and processing. This model is simple
and have all the properties and capabilities required to process data
with storage efficiency.
In the relational model,
• All data must be stored in relations (tables),
• Each relation consists of rows and columns.
• Each relation must have a header and body.
• The header is simply the list of columns in the relation.
• The body is the set of data that actually populates the relation,
organized into rows.

VKS- LEARNING_HUB
DATABASE & SQL
Relational Database: It is a collection of tables / relations. The
software required to handle or manipulate these tables or
relations is known as Relational Database Management System
(RDBMS). Oracle, DB2, Access, MySQL and Visual FoxPro are
some commonly used RDBM.
Eno Name Designation DOJ Salary Mobile
1045 JATIN KHANNA MANAGER FINANCE 2010-07-02 90000 99456972
1072 ALEX JOHN MANAGER IT 2007-04-09 85000 55012016
1032 RACHITA GUPTA SENIOR MANAGER 2009-05-04 78000 66442288
1012 KALPANA BHAT DIRECTOR HRD 2008-10-15 52000 55123765
1049 RUPALI SINGH GENERAL MANAGER 2009-06-10 70000 64192837
1025 AVEEK SHARMA DEPUTY MANAGER 2010-12-07 55000 54876534
1017 KAPIL GARG DEPUTY DIRECTOR 2008-09-03 74000 98283761

VKS- LEARNING_HUB
DATABASE & SQL
Database Concepts
RELATIONAL MODEL - CONCEPT
Tables: In relation data model, relations are saved in the format of
Tables. This format stores the relation among entities. A table has
rows and columns, where rows represent records and columns
represents the attributes.
Tuple: A single row of a table, which contains a single record for that
relation is called a tuple.
Attribute : columns are referred as attributes.
Domain: A pool of values from which the actual values appearing in a
given column are drawn.

VKS- LEARNING_HUB
DATABASE & SQL
Degree Cardinality
Number of Columns in a Table (Relation) Number of Rows in a Table (Relation)

VKS- LEARNING_HUB
DATABASE & SQL
Concept of Keys
In a Relation, each record must be unique i.e. no two identical
records are allowed in the Database. A column or combination of
column which identifies a record called Key of the Table. A key
attribute must have unique (non-repeatable )
value.
Primary Key
A set of one or more column that can uniquely identify a record in
the relation is called Primary Key.
Candidate Key
A Column or group of columns which can be used as primary key
are called Candidate keys, as they are candidate to become as
Primary key.
Alternate Key
A Candidate Key that is not a Primary key is called Alternate key.
Foreign Key
A non-key column whose values are derived from the primary key
of some other table is called Foreign key.





VKS- LEARNING_HUB
DATABASE & SQL
Primary Key
A primary key is a special relational database table column
(or combination of columns) designated to uniquely identify
all table records.
A primary key’s main features are:
It must contain a unique value for each row of data.
It cannot contain null values.
A Table can have only 1 Primary key
primary key field
Relation - EMPLOYEE

VKS- LEARNING_HUB
DATABASE & SQL
Foreign key field
Primary key field
Relation : Directors
Relation: Movies
Foreign Key
A foreign key (REFERENTIAL INTEGRITY) is a column or group of
columns in a relational database table that provides a link between data in
two tables. It acts as a cross-reference between tables because it
references the primary key of another table, thereby establishing a link
between them.

VKS- LEARNING_HUB
DATABASE & SQL
Candidate Key All the attribute combinations inside a relation that
can serve primary key are candidate keys as they are candidates
for the primary key position.
Alternate Key A candidate key that is not the primary key is called
alternate key.
Eno Name Designation DOJ Salary Mobile
1045 JATIN KHANNA MANAGER FINANCE 2010-07-02 90000 99456972
1072 ALEX JOHN MANAGER IT 2007-04-09 85000 55012016
1032 RACHITA GUPTA SENIOR MANAGER 2009-05-04 78000 66442288
1012 KALPANA BHAT DIRECTOR HRD 2008-10-15 52000 55123765
1049 RUPALI SINGH GENERAL MANAGER 2009-06-10 70000 64192837
1025 AVEEK SHARMA DEPUTY MANAGER 2010-12-07 55000 54876534
1017 KAPIL GARG DEPUTY DIRECTOR 2008-09-03 74000 98283761
Candidate Key
Eno
1045
1072
1032
1012
1049
1025
1017
Mobile
99456972
55012016
66442288
55123765
64192837
54876534
98283761
Eno
1045
1072
1032
1012
1049
1025
1017
Mobile
99456972
55012016
66442288
55123765
64192837
54876534
98283761
Primary Key Alternate Key

VKS- LEARNING_HUB
DATABASE & SQL
Basic Database concepts
Database: Collection of logically related data along with its
description is termed as database.
Tuple: A row in a relation is called a tuple.
Attribute: A column in a relation is called an attribute. It is
also termed as field or data item.
Degree: Number of attributes in a relation is called degree
of a relation.
Cardinality: Number of tuples in a relation is called cardinality
of a relation.

VKS- LEARNING_HUB
DATABASE & SQL
Basic Database concepts
Primary Key: Primary key is a key that can uniquely
identifies the records/tuples in a relation. This key can never
be duplicated and NULL.
Foreign Key: Foreign Key is a key that is defined as a
primary key in some other relation. This key is used to
enforce referential integrity in RDBMS.
Candidate Key: Set of all attributes which can serve as a
primary key in a relation.
Alternate Key: All the candidate keys other than the primary
keys of a relation are alternate keys for a relation.

VKS- LEARNING_HUB
DATABASE & SQL
Introduction to MySQL
MySQL is an Open Source, Fast and Reliable Relational Database
Management System (RDBMS) . It is alternative to many of the
commercial RDBMS. The main features of MySQL are-
Open Source & Free of Cost:
It is Open Source and available free of cost. It is part of LAMP
(Linux, Apache, MySQL, PHP/ Perl/ Python) Open Source group.
Portability:
It can be installed and run on any types of Hardware and OS like
Linux, MS Windows or Mac etc.
Security :
It offers privilege and password system for authorization.
Connectivity
It may connect various types of client using different protocols and
Programming Languages .
Query Language





It uses SQL (Structured Query Language)
is standardized by ANSI.
as query language, which

VKS- LEARNING_HUB
DATABASE & SQL
STRUCTURED QUERY
LANGUAGE
( S Q L )
SQL is a non procedural language that is used to create,
manipulate and process the databases(relations).

VKS- LEARNING_HUB
DATABASE & SQL
Types of SQL Commands
MySQL follows SQL specifications for its commands . These
SQL commands can be categorized as -
Data Definition Language (DDL)
These SQL commands are used to create, alter and delete
database objects like table, views, index etc.
Example : CREATE , ALTER , DROP etc.
Data Manipulation Language (DML)
These commands are used to insert, delete, update and retrieve
the stored records from the table.
Ex. SELECT…., INSERT…, DELETE…, UPDATE…. etc.
Transaction Control Language (TCL)
These commands are used to control the transaction. Ex.
COMMIT, ROLLBACK, SAVEPOINT etc.




VKS- LEARNING_HUB
DATABASE & SQL
DDL – Data Definition Language
DDL commands deals with structure of a database/ table,
which sets up Database, changes or removes data structures
from the database. A DDL command includes the word
‘table’ after the SQL command name. DDL commands
include:
Commands to create database or table
Command to delete database or table
Command to change structure of a table
Eg: Create table, alter table ,drop table

VKS- LEARNING_HUB
DATABASE & SQL
DML – Data Manipulation Language
DML includes those commands, which deals data stored in a
table. DML is a set of commands that enables users to access
or manipulate data. By data manipulation, we mean:
The retrieval of information stored in a table
The insertion of new row in a table
The deletion of row or rows from a table (not deleting the
column)
The editing of values stored in a column or columns (not
modifying the data type of column)
e.g Select, Insert, Delete, Update.

VKS- LEARNING_HUB
DATABASE & SQL
Database Handling commands in MySQL
Creating a Database.
The following command will create School database in MySQL.

mysql>
CREATE
DATABAS
E
School
;
Opening a database
To open an existing database, following command

is used.
mysql> USE schoo
l
;
Getting listings of database and tables

mysql>
SHOW
mysql>
SHOW
DATABASES
;
TABLES;
Deleting a Database and Table

mysql>
DROP
mysql>
DROP
DATABAS
E
School
;
TABL
E
Student
;
Viewing Table Structure

Selec
t
Shows
curren
tly
database();
the name of
open database
mysql>
DESCRIBE
Student
;

VKS- LEARNING_HUB
DATABASE & SQL
 When you start MySQL for the first time, you have to create a
database. Without creating a database, one cannot create any table.
 Database is created only once, for the first time. For the practical we
will create only one database.
 Every SQL command is terminated by a semi-colon.
Syntax for creating a database
CREATE DATABASE DATABASENAME;
Example:
mysql> CREATE DATABASE class12C;
Syntax for listing all the database
names is SHOW DATABASES;
Example:
mysql> SHOW DATABASES;

VKS- LEARNING_HUB
DATABASE & SQL
After creating the database, we need to use the database.
For the first time, one need to create the database and use
the database. For the next time onwards, one need to use
the database only.
Syntax for using a database is USE DATABASENAME;
Example:
mysql> USE CLASS12C;

VKS- LEARNING_HUB
DATABASE & SQL
Data types of SQL
Just like any other programming language, the facility of
defining data of various types is available in SQL also.
Following are the most common data types of SQL.
1. NUMERIC
2. CHAR
3. DATE

VKS- LEARNING_HUB
DATABASE & SQL
 Numeric Data Types:
 INTEGER or INT – up to 11 digit number without decimal.
 SMALLINT – up to 5 digit number without decimal.
 FLOAT (M,D) or DECIMAL(M,D) or NUMERIC(M,D)
Stores Real numbers upto M digit length (including
decimal places.
e.g. Float (10,2) can store 1234567.89
 Date & Time Data Types:
 DATE - Stores date in YYYY-MM-DD format.
 TIME - Stores time in HH:MM:SS format.
 String or Text Data Type:
 CHAR(Size)
.) with D
A fixed length string up to 255 characters. (default
 VARCHAR(Size)
A variable length string up to 65,535 characters.
is 1)
Char, Varchar, Date and Time values should be enclosed with single (‘ ‘) or
double ( “”) quotes in MySQL.

VKS- LEARNING_HUB
DATABASE & SQL
 Creating Simple Tables:
CREATE TABLE < Table Name>
(<Col name1><data type>[(size)][Constraints],….);
Data types- INTEGER, NUMERIC(P,D), CHAR(n), VARCHAR(n), DATE etc.
Staff table will be identical to Emp table.
CREATE TABLE Staff ( Select empID, ename, pay From Emp);
CREATE TABLE Staff AS ( Select * From Emp);
 Creating Table from Existing Table:
CREATE TABLE <Table name> [AS] (<Select Query>);
Emp
empID ename city pay
mysql> CREATE TABLE Emp
(empID integer, ename
char(30),
city char(25),
pay decimal(10,2));

VKS- LEARNING_HUB
DATABASE & SQL
Integrity Constraints
One of the major responsibility of a DBMS is to maintain the
Integrity of the data i.e. Data being stored in the Database
must be correct and valid.
An Integrity Constraints or Constraints are the rules, condition
or checks applicable to a column or table which ensures
integrity or validity of data.
the
The following constraints
 NOT NULL
are commonly used in MySQL.
 PRIMARY KEY
 UNIQUE
 DEFAULT *
 CHECK *
 FOREIGN KEY *
* Not included in the syllabus (recommended for advanced learning)

VKS- LEARNING_HUB
DATABASE & SQL
Type of Constraints
UNIQUE v/s PRIMARY KEY
 UNIQUE allows NULL values but PRIMERY
KEY does not.
 Multiple column may have UNIQUE
constraints, but there is only one PRIMERY
KEY constraints in a table.
S.N Constraints Description
1 NOT NULL Ensures that a column cannot have NULL value.
2 DEFAULT Provides a default value for a column, when
nothing is given.
3 UNIQUE Ensures that all values in a column are different.
4 CHECK Ensures that all values in a column satisfy certain
condition.
5 PRIMARY KEY Used to identify a row uniquely.
6 FOREIGN KEY Used to ensure Referential Integrity of the data.

VKS- LEARNING_HUB
DATABASE & SQL
Implementing Primary Key Constraints
mysql> CREATE TABLE
Student
( StCod
e
Stname
char(3)
char(20)
PRIMARY Key
NOT NULL,
……………………….
.
);
mysql> CREATE TABLE
Student
( StCod
e
Stname
char(3)
char(20
)
NOT
NOT
NULL
,
NULL
,
Constraint is
defined after
all column
definitions.
………………………..
PRIMARY KEY
(StCode) );
A Composite (multi-column) Primary key can be defined as only a
Table level whereas Single-column Primary key can be defined in
both way i.e. Column level or Table level.
Defining Primary Key at Table Level:
Defining Primary Key at Column Level:

VKS- LEARNING_HUB
DATABASE & SQL
Implementing Constraints in the Table
Column level
constraints are
defined with
column definitions.
Table level
constraints are
defined after all
column definitions.
CREATE TABLE EMP ( Code char(3) NOT
NULL,
Name char(20) NOT NULL,
City varchar(40),
Pay Decimal(10,2),
PRIMARY KEY (Code) );
mysql> CREATE TABLE Student
(StCode char(3) PRIMARY KEY,
Stname char(20) NOT NULL,
StAdd varchar(40),
AdmNo char(5) UNIQUE,
StSex char(1) DEFAULT ‘M’,
StAge integer CHECK (StAge>=5) );

VKS- LEARNING_HUB
DATABASE & SQL
Show Tables command is used to display names of tables of
database.
Example:
mysql> use class12c;
mysql> show tables;
It should be used after Use Database commands
to display the tables of that database.
DESCRIBE <TABLENAME> IS USED TO DISPLAY THE COMPLETE
STRUCTURE OF TABLE INCLUDING FIELDNAME/ TYPE / CONSTRAINTS

VKS- LEARNING_HUB
DATABASE & SQL
Sometimes we need to remove the
database from the backing storage.
Removing a database will delete all
the tables and other files which are
stored under that database will also
be removed.
Syntax for removing a database is
DROP DATABASE DATABASENAME;
Example:
mysql> DROP DATABASE CLASS12A;

VKS- LEARNING_HUB
DATABASE & SQL
Drop Table command deletes
entire the contents of the table
along with the structure of the
table.
Syntax : Drop Table TableName;
Example:
mysql> DROP TABLE TEACHER;

VKS- LEARNING_HUB
DATABASE & SQL
Data Modifications in SQL
After a table has been created using the create table
command, tuples can be inserted into the table, or tuples
can be deleted or modified.
TABLE : STUDENT
Roll_no Name Class Marks City
101 Rohan XI 400 Chennai
102 Aneeta XII 390 Bengaluru
103 Pawan Kumar IX 298 Mysore
104 Rohan IX 376 Mangalore
105 Sanjay VII 240 Mumbai
113 Anju VIII 432 Delhi

VKS- LEARNING_HUB
DATABASE & SQL
Data Modifications in SQL
INSERT Statement
The simplest way to insert a tuple into a table is to use the
insert statement
insert into <table> [(<column i, . . . , column j>)] values
(<value i, . . . , value j>);
INSERT INTO student (Roll_no,Name,Class)
VALUES(101,'Rohan','XI');
INSERT INTO student
VALUES(101,'Rohan','XI',400,‘Chennai‘,200 );
While inserting the record it should be checked that the values
passed are of same data types as the one which is specified for
that particular column.

VKS- LEARNING_HUB
DATABASE & SQL
Inserting Records in a Table
You can insert record in the table by using by using
following DML command.
the
INSERT INTO <Table Name> [<Column list>]
VALUES <list of values>
Suppose a table named STUDENT has been created
the following structure.
with
We can insert a record as follows-
mysql> INSERT INTO Student VALUES
(‘s1’,’Amitabh’, ‘Harivansh’,’1955-10-25’,
mysql> INSERT INTO Student VALUES
‘Mumbai’,
12);
(‘s2’,’Sharukh Khan’, NULL,’1972-5-25’, ‘Delhi’,
10);
mysql> INSERT INTO Student (StID, FName, Name,
Class) VALUES (‘s3’,’Amitabh’, ’Abhishek’, 10);
StID NAME FNAME DOB CITY CLASS

VKS- LEARNING_HUB
DATABASE & SQL
Queries:
To retrieve information from a database we can query the
databases. SQL SELECT statement is used to select rows
and columns from a database/relation
SELECT Command
This command can perform selection as well as projection.
Selection: This capability of SQL can return you the
tuples form a relation with all the attributes.
Projection: This is the capability of SQL to return only
specific attributes in the relation.

VKS- LEARNING_HUB
DATABASE & SQL
Making Simple Queries Using SELECT
The SELECT command of SQL, empower you to make a
request (queries) to retrieve stored records from
database.
The syntax of SQL is given below-
SELECT < [Distinct | ALL] *| column name(s)>
FROM <table(s)>
WHERE <condition>
ORDER BY <column name> [ASC | DESC] ;
Consider the table Student having some records as –
the
StID Name Fname DOB City Class
S1 Amitabh Harivansh Rai 1948-11-10 Allahabad 12
S2 Sharukh Firoz 1970-05-10 Delhi 11
S3 Irphan Akbar 1970-10-05 Jaipur 11
S4 Salman Salim Javed 1972-04-10 Mumbai 10
S5 Abhishek Amitabh 1975-03-12 Mumbai 10

VKS- LEARNING_HUB
DATABASE & SQL
 Selecting all columns
If you want to view all columns of the student table, then you
should give the following command-
mysql> SELECT * FROM Student ;
MySQL will display the all records with all columns in the Student table.
* Is used to represent all columns.
S1 Amitabh Harivansh Rai 1948-11-10 Allahabad 12
S2 Sharukh Firoz 1970-05-10 Delhi 11
S3 Irphan Akbar 1970-10-05 Jaipur 11

VKS- LEARNING_HUB
DATABASE & SQL
 Selecting columns
If you want to view only Name and City columns of the student table
mysql> SELECT Name, City FROM Student ;
mysql> SELECT City, Name FROM Student ;
City Name
Allahabad Amitabh
Delhi Sharukh
Jaipur Irphan
Mumbai Salman
Mumbai Abhishek
Name City
Amitabh Allahabad
Sharukh Delhi
Irphan Jaipur
Salman Mumbai
Abhishek Mumbai

VKS- LEARNING_HUB
DATABASE & SQL
 Eliminating Duplicate values in a column
DISTINCT
-
mysql> SELECT City FROM Student ;
Mumbai is repeated
mysql> SELECT DISTINCT City FROM Student ;
Only Unique Cities
are displayed
City
Allahabad
Delhi
Jaipur
Mumbai
City
Allahabad
Delhi
Jaipur
Mumbai
Mumbai

VKS- LEARNING_HUB
DATABASE & SQL
 Doing simple calculations
We can also perform simple calculations with SQL Select command. SQL
provide a dummy table named DUAL, which can be used for this purpose.
mysql> SELECT 4*3 ;
We can also extend this idea with a columns of the existing table.
mysql> SELECT Name, Sal *12 FROM EMP ;
 Using Column Aliases
We can give a different name to a column or expression (Alias) in the
output of a query. Alias for Sal*12
mysql> SELECT Name, Sal*12 AS ‘Annual Salary’ FROM EMP;
mysql> SELECT Name, DOB AS ‘Date of Birth’ FROM Student;
mysql> SELECT 22/7 AS PI FROM Dual;
When Alias name is a single word then ‘ ‘ is not required.

VKS- LEARNING_HUB
DATABASE & SQL
Selecting Specific Rows – WHERE clause

VKS- LEARNING_HUB
DATABASE & SQL
 WHERE <Condition>
We can select specific records by specifying condition
WHERE clause.
with
mysql> SELECT * FROM Student WHERE City=‘Mumbai’;
mysql> SELECT
WHERE
Name, Fname, City from Student
Class >10;
Condition
Name Fname City Class
Amitabh Harivansh Rai Allahabad 12
Sharukh Firoz Delhi 11
Irphan Akbar Jaipur 11

VKS- LEARNING_HUB
DATABASE & SQL
 Relational Operators
We can use the following Relational operators in condition.
=, > , < , >=, <=, <>, IS , LIKE, IN, BETWEEN
 Logical Operators
We can use the following Logical Operators to connect two conditions.
OR , AND , NOT (!)
mysql> SELECT Name, City from Student
WHERE City <> ‘Mumbai’ AND Class>10;
mysql> SELECT * FROM Emp
WHERE Sal >10000 OR Job =‘Manager’;
mysql> SELECT * FROM Student
WHERE NOT Grade=‘A’;

VKS- LEARNING_HUB
DATABASE & SQL
 Specifying Range of Values – BETWEEN Operator
WHERE Sal BETWEEN 5000 AND 10000 ;
The same query can also be written as -
WHERE Sal >= 5000 AND Sal<=10000 ;
Other Logical operators also can be applied-
WHERE NOT Sal BETWEEN 5000 AND 10000 ;
 Specifying List – IN Operator
WHERE Sal IN (5000, 10000) ;
The same query can also be written as –
mysql> SELECT * FROM Emp WHERE Sal = 5000 OR Sal =10000 ;
mysql> SELECT * FROM Student WHERE City IN (‘Mumbai’, ’Kanpur’) ;

VKS- LEARNING_HUB
DATABASE & SQL
 Pattern Matching – LIKE Operator
A string pattern can be used in SQL using the following wild
 % Represents a substring in any length
card
 _ Represents a single character
Example.
‘A%’
‘_ _A’
‘_B%’
‘_ _ _’
represents any string starting with ‘A’ character.
represents any 3 character string ending with ‘A’.
represents any string having second character ‘B’
represents any 3 letter string.
A pattern is case sensitive and can be used with LIKE operator.
mysql>
mysql>
mysql>
SELECT * FROM Student WHERE Name LIKE ‘A%’;
SELECT * FROM Student WHERE Name LIKE ‘%Singh%’;
SELECT Name, City FROM Student
WHERE Class>=9 AND Name LIKE ‘%Kumar%’ ;

VKS- LEARNING_HUB
DATABASE & SQL
 Searching NULL Values – IS Operator
mysql> SELECT * FROM Student WHERE City IS NULL ;
The NOT Operator can also be applied -
mysql> SELECT * FROM Student WHERE City IS NOT
NULL;
 Ordering Query Result – ORDER BY Clause
A query result can be orders in ascending (A-Z) or descending
order as per any column. Default is Ascending order.
(Z-A)
mysql> SELECT * FROM Student ORDER BY City;
To get descending order use DESC key word.
mysql> SELECT * FROM Student ORDER BY City DESC;
mysql> SELECT Name, Fname, City FROM Student
Where Name LIKE ‘R%’ ORDER BY Class;

VKS- LEARNING_HUB
DATABASE & SQL
Deleting Records from the Table
You can delete all
the following DML
DELETE FROM
or selected record(s) from the table by
command.
<Table Name> [WHERE <Condition>]
using
This command will
delete all records…
mysql
>
DELET
E
FRO
M
Studen
t
;
mysql
>
mysql
>
DELET
E
DELET
E
FRO
M
FRO
M
Studen
t
Studen
t
WHER
E
WHER
E
City=‘Mumbai’
;
Class >=11 ;
 You can recall (Undelete) records by giving ROLLBACK command.
mysql> ROLLBACK ;
 You can ssue COMMIT command to record the changes permanently.
mysql> COMMIT;

VKS- LEARNING_HUB
DATABASE & SQL
Modifying Records in the Table
You can modify the values of columns of all or selected
records in the table by using the following DML command.
UPDATE <Table Name> SET <Column> = <Expression>
[WHERE <Condition>]
mysql
>
UPDAT
E
Studen
t
SET Class
=10
;
mysql
>
UPDAT
E
Studen
t
SET FName=
CONACT(‘Mr.’,
FName’
)
;
mysql
>
UPDAT
E
Emp
SET
Sal = Sal+
(Sal*10/100);
mysql
>
UPDAT
E
Emp
SET
Sal = Sal+
(Sal*10/100)
WHERE Sal <=10000;
UPDATE Emp SET City =
‘Dehradun’ WHERE CITY IS
NULL;
mysql
>

VKS- LEARNING_HUB
DATABASE & SQL
Modifying Table Structure
You can alter (modify) the structure of existing table by
using ALTER TABLE…. Command of MySQL.
You can do the following with the help of ALTER
TABLE.. Command.
the
Add a new Column or Constraints
Modifying existing column (name, data type,
etc.)
Delete an existing column or Constraints

 size

ALTER TABLE <Table Name>
ADD|MODIFY|DROP| <Column Definition(s)>
You can add/Delete/Modify multiple columns with single ALTER
Command.

VKS- LEARNING_HUB
DATABASE & SQL
Modifying Table Structure cont..
 Adding new column
ADD <Column>[<data type> <size>][<Constraints>]
mysql> ALTER
mysql> ALTER
Modifying
TABLE Student ADD (TelNo Integer);
TABLE Student ADD (Age Integer DEFAUL
10);
Existing Column
MODIFY <Column>[<data type> <size>] [<Constraints>]
mysql> ALTER
mysql> ALTER
Removing
TABLE Student MODIFY Name
VARCHAR(40);
TABLE Emp MODIFY (Sal DECIMAL
(10,2));
Column & Constraints
ALTER TABLE <Table Name>DROP
<Column name> |<Constraints>
mysql> ALTER TABLE Student DROP TelNo;
mysql> ALTER TABLE Emp DROP JOB, DROP
Pay;

VKS- LEARNING_HUB
DATABASE & SQL
Modifying Table Constraints
 Adding new column and Constraints
ADD <Column>[<data type> <size>][<Constraints>]
mysql
>
mysql
>
mysql
>
mysql
>
mysql
>
ALTE
R
ALTE
R
ALTE
R
ALTE
R
ALTE
R
TABL
E
TABL
E
TABL
E
TABL
E
TABL
E
Studen
t
Studen
t
ADD
ADD
Sal
(TelNo Integer);
(Age Integer CHECK
(Age>=5)); Number(8,2)
DEFAULT 5000 ;
Emp
Emp
Emp
ADD
ADD
ADD
PRIMARY KEY (EmpID);
PRIMARY KEY
(Name,DOB);
 Modifying Existing Column and Constraints
MODIFY <Column>[<data type> <size>] [<Constraints>]
mysql
>
mysql
>
mysql
ALTE
R
ALTE
R
ALTE
TABL
E
TABL
E
TABL
Student MODIFY Name
VARCHAR(40);
Emp MODIFY (Sal DEFAULT
4000 ); Emp MODIFY (EmpName
NOT NULL);

VKS- LEARNING_HUB
DATABASE & SQL
Modifying Table Constrains cont..
 Removing Column & Constraints
ALTER
DROP
TABLE <Table Name>
<Column name> |<Constraints>
mysql
>
mysql
>
ALTE
R
ALTE
R
TABL
E
TABL
E
Student DROP TelNo;
Emp DROP JOB, DROP
Pay;
mysql
>
ALTE
R
TABL
E
Student DROP PRIMARY
KEY;
 Changing Column Name of Existing Column
CHANGE <Old name><New Definition>
mysql> ALTER TABLE Student
CHANGE Name Stname Char(40);

VKS- LEARNING_HUB
DATABASE & SQL
SELECT Command
SELECT name, fees, Sec FROM studentdetails WHERE
Sec=“c”;
The above command display only name, fees & Sec of
those records whose Sec is C

VKS- LEARNING_HUB
DATABASE & SQL
ORDER BY Clause
ORDER BY clause is used to display the result of a query in a specific
order(sorted order).default order is Ascending
The sorting can be done in ascending or in descending order. It should
be kept in mind that the actual data in the database is not sorted but
only the results of the query are displayed in sorted order.
SELECT name, fees FROM studentdetails ORDER BY name;
The above query returns name and fees columns of table student
sorted by name in increasing/ascending order.
SELECT name, fees FROM studentdetails ORDER BY name DESC;
It displays all the records of table student ordered by city in descending
order.

VKS- LEARNING_HUB
DATABASE & SQL
SELECT with Aggregate functions or SQL function (DML)
COUNT() Counts the number of rows
SUM() Finds the sum of values in the column (only
applicable for Numeric type)
AVG() Finds the average of values in the column
(only applicable for Numeric type)
MAX() Finds the maximum value in the column
MIN() Finds the minimum value in the column

VKS- LEARNING_HUB
DATABASE & SQL
Select Count(Roll) From Studentdetails;
Select Sum(fees) From Studentdetails;
Select AVG(fees) From Studentdetails;

VKS- LEARNING_HUB
DATABASE & SQL
Select Max(fees), Min(fees) From Studentdetails;
Select Max(dob), Min(dob) From Studentdetails;
Select Max(Name), Min(Name) From Studentdetails;

VKS- LEARNING_HUB
DATABASE & SQL
Select Count( Distinct Sec From Studentdetails;
Select Distinct Sec From Studentdetails;

VKS- LEARNING_HUB
DATABASE & SQL
GROUP BY Clause
The GROUP BY clause can be used in a SELECT statement
to collect data across multiple records and group the results
by one or more columns.
The syntax for the GROUP BY clause is:
SELECT column1, column2, ... column_n,
aggregate_function (expression)
FROM table
WHERE conditions
GROUP BY column;

VKS- LEARNING_HUB
DATABASE & SQL
GROUP BY Clause
aggregate_function can be a function such as SUM,
COUNT, MAX, MIN, AVG etc.
Select Sec, sum(Fees) From Studentdetails Group By Sec;
Select Sec count(*) From Studentdetails Group by Sec;

VKS- LEARNING_HUB
DATABASE & SQL
Select Sec, Max(fees), Min(fees) From Studentdetails
GROUP BY Sec;

VKS- LEARNING_HUB
DATABASE & SQL
HAVING Clause
The HAVING clause is used in combination with the GROUP
BY clause. It can be used in a SELECT statement to filter
the records that a GROUP BY returns.
The syntax for the HAVING clause is:
SELECT column1, column2, ... column_n,
aggregate_function (expression)
FROM table
WHERE predicates
GROUP BY column1, column2, ... column_n
HAVING condition1 ... condition_n;

VKS- LEARNING_HUB
DATABASE & SQL
HAVING Clause
Select Sec, Sum(fees) From Studentdetails GROUP BY
Sec Having Sum(Fees)>22000;

VKS- LEARNING_HUB
DATABASE & SQL
While both WHERE and HAVING clauses are used for filtering data in SQL
queries, they serve different purposes and are applied at different
stages of query execution. Understanding their distinctions is crucial for
writing efficient and effective SQL queries.

VKS- LEARNING_HUB
DATABASE & SQL
DELETE Command
To delete the record fro a table SQL provides a delete
statement. General syntax is:-
DELETE FROM <table_name> [WHERE <condition>];
DELETE FROM studentdetails Where Sec=‘c’;
This command deletes all those records whose sec is ‘C’
NOTE: It should be kept in mind that while comparing
with the string type values lowercase and uppercase
letters are treated as different.

VKS- LEARNING_HUB
DATABASE & SQL
UPDATE Command
To update the data stored in the data base, UPDATE
command is used. Increase Fees of all the students by 100.
UPDATE studentdetails SET Fees = Fees + 100;

VKS- LEARNING_HUB
DATABASE & SQL
Foreign key constraint A column in one table refer to a
Primary key of another table. Or to put another way, a
column in table obtains its value from Primary key of
another table.

VKS- LEARNING_HUB
DATABASE & SQL
create table empdata (code int primary key, name char (10), mob int);
insert into empdata values (199801, 'SUNIL', 93661430);
insert into empdata values (200305, 'REETA', 55789215);
insert into empdata values (201010, 'TUSHAR', 69785310);
insert into empdata values (202102, 'ANITHA', 98452301);
create table empsal (code int, desig char(20), bsal float,
foreign key (code) references empdata (code));
insert into empsal values (200305, 'GEN MANAGER', 175000.0);
insert into empsal values (202102, 'DEP MANAGER', 155000.0);
insert into empsal values (199801, 'SNR MANAGER', 165000.0);
insert into empsal values (201010, 'JNR MANAGER', 145000.0);
insert into empsal values (200123, 'AST MANAGER', 145000.0);

VKS- LEARNING_HUB
DATABASE & SQL
Roll SName Class
1 Akash XII
4 Dibakar XII
7 Rohini XI
10 Rishi XI
5 Deepa XI
TCode Class TName
102 XII Ms. Rinee
309 XII
Mr.
Tanmoy
270 XI Mr. Alice
Cartesian Product (Binary Operator): It operates on two or more relations
and is denoted by X. For example Cartesian product of two relations R1 and
R2 is represented by R1 X R2. The degree of the new relation (R1 X R2) is
equal to sum of degrees of relation R1 and relation R2. The cardinality of the
new relation (R1 X R2) is product of cardinality of relation R1 and cardinality
of relation R2.
SQL Cartesian Product

VKS- LEARNING_HUB
DATABASE & SQL
Roll SName Class TCode TName
1 Akash XII 102 Ms. Rinee
1 Akash XII 309 Mr. Tanmoy
1 Akash XII 270 Mr. Alice
4 Dibakar XII 102 Ms. Rinee
4 Dibakar XII 309 Mr. Tanmoy
4 Dibakar XII 270 Mr. Alice
7 Rohini XI 102 Ms. Rinee
7 Rohini XI 309 Mr. Tanmoy
7 Rohini XI 270 Mr. Alice
10 Rishi XI 102 Ms. Rinee
10 Rishi XI 309 Mr. Tanmoy
10 Rishi XI 270 Mr. Alice
5 Deepa XI 102 Ms. Rinee
5 Deepa XI 309 Mr. Tanmoy
5 Deepa XI 270 Mr. Alice
Select Roll,SName,Class,TCode,TName From Student,Teacher

VKS- LEARNING_HUB
DATABASE & SQL
Union (Binary Operator): It operates on two or more relations
and is indicated by U. For example, R1 U R2 represents
union operation between two relations R1 and R2. The
degree of the relation new relation (R1 U R2) is equal degree
of relation R1 (or degree of relation R2). The cardinality of the
new relation (R1 U R2) is sum of cardinality of relation R1
and cardinality of relation R2. Two relations are compatible
for union operation when:
Degree of relation R1 = Degree of relation R2 Corresponding
attributes of the two relations (R1 and R2) must have a
common domain

VKS- LEARNING_HUB
DATABASE & SQL
Name Marks Grade
Fatima 69.5 C1
Faraz 78.0 B2
Gauri 95.5 A1
Rekha 89.0 A2
Esha 67.5 C2
Atul 88.5 B1
Name Marks Grade
Fatima 69.5 C1
Faraz 78.0 B2
Gauri 95.5 A1
Name Marks Grade
Atul 89.0 A2
Esha 67.5 C2
Rekha 88.5 B1
CS12A (R1) U CS12B (R2)
Relation: CS12A (R1) Relation: CS12B (R2)

VKS- LEARNING_HUB
DATABASE & SQL
A JOIN clause is used to combine rows from two or
more tables, based on a related column between
them.
MySQL JOINS are used with SELECT statement. It
is used to retrieve data from multiple tables. It is
performed whenever you need to fetch records from
two or more tables.
As per Syllabus we will do following joins
Cross Join or Cartesian product
Natural Join
Equi-Join

VKS- LEARNING_HUB
DATABASE & SQL
CROSS JOIN is used to combine all possibilities of the two or
more tables and returns the result that contains every row
from all contributing tables.
The CROSS JOIN is also known as CARTESIAN JOIN, which
provides the Cartesian product of all associated tables.
The Cartesian product can be explained as all rows present in
the first table multiplied by all rows present in the second
table.
MySQL CROSS JOIN Syntax
The CROSS JOIN keyword is always used with the SELECT
statement and must be written after the FROM clause. The
following syntax fetches all records from both joining tables:
SELECT * FROM table1 CROSS JOIN table2;
SELECT * FROM table1 JOIN table2;
SELECT * FROM table1 , table2;

VKS- LEARNING_HUB
DATABASE & SQL
MySQL CROSS JOIN Syntax
The CROSS JOIN keyword is always used with the
SELECT statement and must be written after the FROM
clause. The following syntax fetches all records from
both joining tables:
SELECT * FROM table1 CROSS JOIN table2;
SELECT * FROM table1 JOIN table2;
SELECT * FROM table1 , table2;

VKS- LEARNING_HUB
DATABASE & SQL
When we combine rows of two or more tables based on a
common column between them, this operation is called joining.
A natural join is a type of join operation that creates an implicit
join by combining tables based on columns with the same
name and data type.
Table produced through natural join will have only one common
col name.
In Natural join,rows will be paired by matching the values stored
in the common column.
Natural Join
•There is no need to specify the column names to join.
•The resultant table always contains unique columns.
•It is possible to perform a natural join on more than two
tables

VKS- LEARNING_HUB
DATABASE & SQL
Equi-join This is almost similar to Natural join but only difference
is two or more tables can be joined using column from the tables
where the name of the columns in the table could be different.
Also, the new table contains repeated columns used to link the
tables.
Equi-join can be performed as:
select * from student, teacher where student.cla=teacher.cla;
OR
select * from student s, teacher t where s.cla=t.cla;
OR
select * from student as s, teacher as t where s.cla=t.cla;
OR,
select * from student join teacher on student.cla=teacher.cla;
OR,
select * from student s join teacher t on s.col=t.col;
OR,
select * from student as s join teacher as t on s.col=t.col;

Database Concept with SQL Commands for grade 12

More Related Content

Similar to Database Concept with SQL Commands for grade 12

More from Vinod Srivastava

Recently uploaded

Database Concept with SQL Commands for grade 12