Chp.
6 List
Chp.7 Dictionary
Chp.8 Database Concept
Chp.9 Structured Query Language
11 – Structured Query Language (SQL)
Literal : The literals general refer to a fixed data values , that may be of
character type or numeric literal or others. F
Numeric : 25, -458 , 25.36 , -0.25 etc
Character text type : ‘surat’ , “surat” , ‘F-254’ , ‘452654’ etc.
Date & Time : ‘2024-11-18’ , “2024-04-15”
CREATE TABLE student (grno int, sname char(25), dob date, fees int);
Data Types :
Numeric:
INT up to 11 digits -2147483648 to
TINYINT -128 TO 127
SMALLINT -32,768 to 32,767
BIGINT -9,223,372,036,854,775,808
FLOAT FLOAT(M,D) Decimal position can place 24
( avg float(4,2 ) 99.99
DOUBLE DOUBLE (M,D) Decimal position can place 53
DECIMAL DECIMAL(M,D)
DATE YYYY-MM-DD
DATETIME YYYY-MM-DD HH:MM:SS
TIMESTAMP YYYYMMDDHHMMSS
TIME: HH:MM:SS
TEXT : char, varchar, blob ,text
CHAR : Fixed length fields
VARCHAR : Variable length fields
Char has a fixed size, but varchar has a variable size. Char data type stores
data of fixed length, whereas the Varchar data type stores variable format
data. Varchar data type values are not padded with spaces; char values are
padded with spaces to the specified length.
If char data type size is 25 but you use 5 character then also it take 25 space
But the varchar data type has size 25 and you use 5 character then it take
only 5 space.
Null values : null
Comments : /* bbbbbb*/
Mysql Operators :
Mathematical operators:
+ - * / %
Relational operators
>, <, >=, <=, <>, !=, =
Logical Operators
and , or , not
MYSQL COMMANDS
CREATE DATABASE <database name>
CREATE DATABASE IF NOT EXISTS <database name>
SHOW DATABASES
USE <database name>
CREATE TABLE <table name>
CREATE TABLE IF NOT EXISTS <table name>
SHOW TABLES
CREATE TABLE WITH CONSTRAINTS :
What is constraints : The constraints in MySQL are used to specify the
rules that allows or restricts what values/data will be stored in the
table. They provide a suitable method to ensure data accuracy and
integrity inside the table. It also helps to limit the type of data that will
be inserted inside the table.
Different type of constraints:
a) PRIMARY KEY
b) UNIQUE
c) DEFAULT
d) CHECK
e) NOT NULL
f) FOREIGN KEY
1) Create a table emp with following description & insert two three
rows (enter duplicate values , null value of empno and observe it)
empno int primary key , empname varchar(10) , salary int
2) insert 2 rows only empno
3) Create a table student with following description & insert 5 rows:
grno int primary key, sname varchar(20) not null , city
varchar(15) default value surat, age int (age should be more then 18)
4)insert two rows of grno, sname
5) insert two rows of grno,age ( observe error )
6) insert the value and observe error [115,’renish’, ‘mumbai’, 5]
T1) Create a Table sports:
Sportsid int primary key
Sname varchar size 20
Game varchar size 20 default values ‘Basketball’
dob date
fees decimal for 99999.99 values should be more than 5000
T2) insert the following data into table :
101 Anirudh Volleyball 2nd January 2008 8522
rd
102 Ankush Cricket 3 March 2009 6365.25
103 Kushal 15th August 2010 6500
104 Harshit Tennis 24th October 2011 7400
th
105 Maharshi 17 November 2005 5600
106 Keyur Cricket 15th October 2004 6330.50
rd
107 Rupesh Volleyball 23 December 2007 9700.50
Types of MySQL Constraints : Constraints in MySQL is classified into two
types:
Column Level Constraints: These constraints are applied only to the single
column that limits the type of particular column data.
Table Level Constraints: These constraints are applied to the entire table
that limits the type of data for the whole table.
Task) Create a able marks having rollno, sname, phy,chem,math, total
Define table level constrains to set phy, chem and maths marks should
be more then 100.
CREATE TABLE marks (rollno int, sname varchar(20), phy int, chem int,
maths int, total int, check (phy+chem+math>100) ;
DESCRIBE /DESC : This command is use to display or view the structure of
table including its data type , data size and constraints.
> DESCRIBE employee;
> DESC employee;
INSERT INTO : The insert into command is used to insert or add the values
into table.
> INSERT INTO <table name> VALUES (value, value, value, …) ;
The values of attributes you have to pass in a proper sequence as per the
columns /attributes sequence of table.
> INSERT INTO <table name> (col, col, col, …) VALUES (value, value,
value, …) ;
The values of attributes you have to pass as per the columns /attributes
defined in insert into command.
Ex. Table : student
Grno , sname doj marks
> INSERT INTO student VALUES (1001, ‘Mohit’, ‘ 2004-05-15’,254)
> INSERT INTO student (sname, marks, doj, grno) VALUES (‘ronit’, 350,
‘2004-05-15, 1001) ;
> INSERT INTO student ( grno, sname) VALUES (177, ‘ronit’);
Inserting data from another table.
Table : Student
Table : ssss:
T) Insert all the data from students table to ssss table.
> INSERT INTO ssss SELECT * FROM student;
SELECT : Use to Making simple queries
SELECT * FROM employee :
SELECT ename, salary FROM employee ;
DISTINCT clause
SELECT department FROM employee ;
SELECT DISTINCT department FROM employee ;
WHERE clause
Relational operators <, >, <=, >=, <>, !=, =
Logical Operators and, or, not
q1) Display all information of employee
MySql> SELECT * FROM employee;
q2) Display all information of male employee
q3) Listed all the employee information whose salary is more then 50000
q4) listed all the employee information of HR and Sales department of
employee relation.
q5) Show the all employee information whose department either admin and
production.
q6) Display all the female employee information whose salary is more then or
equal to 50000.
q7) Display name, salary and department of employee , whose salary is more
than 50000 and less than 70000.
Q8) Dzisplay name, salary and department of female employee , whose salary is
more than 50000 and less than 70000.
q9) Display all t0he information of employee except sales department
q10) Display all the female employee of HR department whose salary is more
than equal to 50000;
IN clause
NOT IN clause
BETWEEN clause
NOT BETWEEN clause
q11) Display all information of employee whose department is HR or Admin or
Sales.
Q12) Listed all information of employee except Production and Sales
department.
Q13) Display all information of employee whose salary from 50000 to 70000.
Q14) Write SQL queries to display all the employee information whose date of
join in year 2022 and 2023.
Q15) Listed all the female employee whose date of join before 2022.
Q16) Listed all the male employee whose salary not in 50000 to 70000.
Q17) Display all the employee whose were join in year 2023 for department of
Production and Sales.
LIKE clause : The like clause can use in place of = operator . In Pattern
Matching character only like clause use instead of = operator.
Q18) Display all the employee information of admin department;
MySql> SELECT * from employee where department=”Admin”;
MySql> SELECT * from employee where department LIKE ”Admin”;
Q19) Display all the employee of Production department whose salary more
than or equal 50000.
MySql> SELECT * from employee where department LIKE ”Production” and
Salary >=50000;
Q20) Display name, doj,salary of all female employee , whose date of join in the
year 2024.
MySql> SELECT ename,doj,salary from employee where gender LIKE ”FEMALE”
and doj between ‘2024-01-01’ and ‘2024-12-31’;
Pattern Matches Characters
% : Group of characters
_ (Underscore) : represent to single character
Q1)Display all the employee information whose name begin with n .
Q2) Display all the employee information whose name last character is i.
Q3) Display all the female employee whose name having patel in any
position of name.
Q4) display all the employee information whose name second character ‘ i ’
and salary more then or equal to 50000
Q5) Display all employee information whose name having only 5
characters only.
Q6) Display all the employee information whose date of join in month of
December in any year.
Q7) display employee name, department and salary whose joining year in
2024 and department is Account.
NULL :
IS NULL
IS NOT NULL
Attributes Allice :
Q1) Write Sql queries on the basic of table emp;
a) Display all the information of emp table, whose date of join having
null value.
b) Display ename, doj and design of emp table whose design does not
contain null values.
c) Display all information of emp table with change the attribute name
like empname, dateofJoin, designation and basicsalary.
d) Display ename, salary , DA (75% of salary) from emp table.
e) Display ename, salary, DA (75% of salary), NetSalary (salary+da)
from emp table whose salary does not have null values.
ORDER BY CLAUSE : (Arrange the records in Assending or Descending )
ASC , DESC
Sql> SELECT * FROM emp ORDER BY ename ;
Sql> SELECT * FROM emp ORDER BY ename desc ;
Sql> SELECT ename, doj, salary FROM emp ORDER BY salary desc ;
Sql> SELECT * FROM emp ORDER BY doj;
UPDATE COMMAND : (DML type of sql command)
Sql> UPDAT emp SET salary = 5000 ;
Sql> UPDAT emp SET salary = 5000 ;
DELETE COMMAND : (DML type of sql command)
ALTER COMMAND : (DDL type of Sql command)
Add New Column
Sql> ALTER TABLE emp ADD GRADE CHAR(2);
Sql> ALTER TABLE emp ADD (GRADE CHAR(2));
Sql> ALTER TABLE emp ADD COLUMN GRADE CHAR(2);
Sql> ALTER TABLE emp ADD COLUMN (GRADE CHAR(2) );
Sql> ALTER TABLE emp ADD COLUMN GRADE CHAR(2) FIRST ;
Sql> ALTER TABLE emp ADD COLUMN GRADE CHAR(2) AFTER ENAME;
Modify Column : ( datatype, size , constraints , default values )
Sql> ALTER TABLE emp MODIFY ENAME VARCHAR(35) ;
Sql> ALTER TABLE emp MODIFY COLUMN ENAME VARCHAR(35) ;
Sql> ALTER TABLE emp MODIFY ENAME VARCHAR(35) NOT NULL ;
Sql> ALTER TABLE emp MODIFY SALARY DECIMAL (7,2);
Change Column Name change clause
Sql> ALTER TABLE emp CHANGE ename empname varchar(20) ;
Sql> ALTER TABLE emp CHANGE COLUMN ename empname varchar(20) ;
Sql> ALTER TABLE emp CHANGE sal basicsalary decimal (7,2);
Delete Column drop clause
Sql> ALTER TABLE emp drop column gender ;
Sql> ALTER TABLE emp drop gender ;
Add Primary Key clause
Sql> ALTER TABLE emp ADD PRIMARY KEY (empid);
Remove Primary Key from table Drop primary key clause
Sql> ALTER TABLE emp DROP PRIMARY KEY empid;
Add Constraints to table
Sql> ALTER TABLE emp ADD UNIQUE (ename);
Sql> ALTER TABLE emp MODIFY ename VARCHAR(30) UNIQUE;
Sql> ALTER TABLE emp ALTER salary SET DEFAULT 5000;
Sql> ALTER TABLE emp MODIFY salary int default 5000;
Sql> ALTER TABLE emp ADD GRADE CHAR(2) AFTER ENAME;
Sql> ALTER TABLE emp ADD GRADE CHAR(2) FIRST ENAME;
FIRST ENAME;
LAdd New
3)Create a table emp with empno int and primary key, ename varchar size
20 , salary int with check constraint salary not less than 5000;
Sql> CREATE TABLE EMP (EMPNO INT primary key, ENAME VARCHAR(10),
SALARY INT CHECK (SALARY>5000));
ate & Time: it refers to the storage of the same data multiple
time ( duplicated data )
String : Multiple mismatched copies of same data is
called Data Inconsistence.
Data Isolation : it refers to a situation where data of one file
cannot be mapped to other related file in the
absence of link or mapping.
Data Dependence : multiple files dependence each other , so the
software or application required to update and
maintain all files.
Data Sharing / Security / Control Issue :
All these are absences in Flat file data system.
Database : A Database is an organized collection of structured information,
or data typically stored electronically in a computer system at a
central location.
DBMS ( Data Base Management System) : A DBMS is software system
designed to maintain a database and provide data management
services.
Advantage of DBMS :
It Reduce the data redundancy to a large extent.
It Control data inconsistency to a large extent.
It facilitate sharing of data
It enforce standards.
It can ensure data security
It ensure data Independence.
DBMS Key Concepts :
Database Schema : it is a sketch / skeleton /blueprint of a planned
data . It represents the design of tables, columns,
relations, constrains and relationships that make
up a logically distinct section of database.
Database Instance : It is a snapshot of a database that exists at a
particular time , i.e the data which is stored in the
database at a particular moment of time.
Metadata / (Data dictionary) : The data dictionary is file storing
metadata of the object of database. The Metadata
refer to data about data
Data Constraints : A database constraint is set of rules that define
valid data. It is also enforces to accept the valid
data from user. Like
PRIMARY KEY, NOT NULL, UNIQUE, CHECK,
DEFAULT, FOREIGN KEY
Query : It is type of command that retrieves data from a
database stored on a server.
Data Manipulation : Inserting , updating , deleting data in data base
refer to data manipulation.
Database Engine : It is the underlying software component that a
DBMS uses to create , read, update and delete
data from database.
Database Views : A Database View is a virtual table that provides a
user-friendly representation of data from one or
more underlying tables
Database Model : A database model shows the logical structure of a
database, including the relationships and constraints
that determine how data can be stored and accessed
Hierarchical database model , Relational model , Network model,
Object-oriented database model , Entity-relationship model,
Document model , Entity-attribute-value model , Star schema ,
The object-relational model, which combines the two that make up its name
Relational Database Model : In relational data model the data is
organized into relations ( tables ), tuple (rows) , attributes ( columns )
etc.
Relational Model Terminology: I
Relation : In Relational Database Model relation is table.
That arranged the data in rows and columns
Domain : A domain is a set of values that can be stored in a column
of a database table. A domain is usually defined by a
column's data type, which determines the kind of values
that can be stored in the column. For example, a column
with a data type of INTEGER can only store numeric values.
Tuple : The rows of tables (relation) are generally refereed to
as Tuples
Attributes : The columns of table (relation) are generally to as
attributes
Degree : The number of attributes in a relation determines the
degree of a relation.
Cardinality : The number of tuples (rows) in a relation is called the
cardinality of the relation
Properties of Relation :
Every row is unique.
All of the values present in a column hold the same data type.
Values are atomic.
The columns sequence is not significant.
The rows sequence is not significant.
The name of every column is unique.
Properties of Attributes :
The sequence / Ordering of column is Insignificant.
Each column has a Unique Name
Properties of Row(Tuple) :
Each row is Unique (distinct)
The sequence of Rows is Insignificant.
Keys in a Database :
Primary Key : A Primary key is set of one or more attributes that can
uniquely identify tuple within the relation. The Primary key does not
allowed the Null value & each relation have only one primary key.
Candidate Key : All attributes combination inside a relation that can
serve as primary key are candidate keys as they are candidates for the
primary key position. A relation can have more than one candidate
key.
Alternate Key : A candidate key that is not the primary key is called an
alternate key. Incase two or more candidate keys , only one of them
serves as the primary key. The rest of them are alternate keys .
Foreign Key : A non-key attribute , whose values are derived from
the primary key of some other table. Is known as foreign key in the
current table.
A non primary key of one table , that can serve as the primary key of
another table . The value of non-primary key derived and reference
with the value of another table primary key . That key treat as the
foreign key of current table.
MySQL :
Classification of SQL Statements :
DDL : Data Definition Language
DML : Data Manipulation Language
TCL : Transaction Control Language
DCL : Data Control Language
How to Start MySql
Grno Sname Dob Tot
101 Aaa 2021-04-12 240
102 Bbb 2020-04-06 250
103 Ccc 2014-04-01 450
104 Ddd 2021-05-22 350