0% found this document useful (0 votes)
35 views18 pagesMy SQL
The document discusses various aspects of database management systems (DBMS), focusing on MySQL and SQL commands. It covers topics such as data definition, manipulation, transaction control, and the structure of SQL statements, including commands for creating, altering, and deleting tables. Additionally, it explains the use of constraints, primary keys, foreign keys, and the differences between various SQL commands.
Uploaded by
Kajal GautamCopyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
0% found this document useful (0 votes)
35 views18 pagesMy SQL
The document discusses various aspects of database management systems (DBMS), focusing on MySQL and SQL commands. It covers topics such as data definition, manipulation, transaction control, and the structure of SQL statements, including commands for creating, altering, and deleting tables. Additionally, it explains the use of constraints, primary keys, foreign keys, and the differences between various SQL commands.
Uploaded by
Kajal GautamCopyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
/ 18
1510) Tes
aHigh Cost - DBMS requires vari
rious software, har 1
operating and maintaining the database system, wh he ites is tehly. Intelligen pacg
, which adds cost mle,
b.Database Failure - If Database
Se buena bia @ Is corrupted due to power failure or any other reason
¢.Data Quality - With increa:
ised number of users dire essing data fro ti
there are enormous opportunities for data rane oe
MYSQL
It is free a
ete presccne source Relational Database Management System (RDBMS) that
MySQL databa ry Language(SQL). In MySQL database , information 1s stored in Tables, 4
se can contain many tables at once and store thousands of individual records,
SOL (Structured Query Language)
SQL is a language that enables you to create and operate on relational databases, which ae se
related information stored in tables. .
CLASSIFICATION OF SOL STATEMENTS
‘SQL commands can be mainly divided into following categories:
1. Data Definition Language(DDL) Commands
Commands that allow you to perform task, related to data definition e.g;
- Creating, altering and dropping.
- Granting and revoking privileges and roles.
- Maintenance commands.
2. Data Manipulation Language(DML) Commands E
Commands that allow you to perform data manipulation eg retrieval, Insertion, deletion and
modification of data stored in a database.
3, Transaction Control Language(TCL) Commands
Commands that allow you to manage and control the transactions €-8,
« Making changes to database, permanent
- Undoing changes to database, permanent
+ Creating savepoints
- Setting properties for current transactions.
MySOL ELEMENTS
1. Literals 2. Datatypes 3. Nulls 4. Comments
LITERALS
It refer to a fixed data value. This fixed data value may be of character type oF M'
For example, ‘replay’, “Raj, 8" , ‘306 are all character literals.
umerie 9P®
Numbers not enclosed in quotation marks are numeric literals. Bg: 22, 18 199) te
numeric literals, Numeric literals can either be integer literals ie, without any Geclae real
veal literals Le. with a decimal point eg. 17 is an integer literal but 17.0 and 175
24/06/20 REDMI 13C 24/06/2025 08:23 REDMI 13C 24/06/2025 08:23 REDMI15C 24/06/2025 08:23 1002 z
10g ‘Akash Mi A [35007
: Neela F B2 38965
1005 Sunny M Az. | 30009
1006 Ruby. E AL 45000
1009 Neo F ‘AZ 52000
~ A articular column from a table can be selected by specifying column-names With SEL
command. Eg in above table, if we want to select ECODE and ENAME colin oT
command is: —
SELECT ECODE, ENAME FROM EMPLOYEE ;
E.g.2 in order to select only ENAME, GRADE and GROSS column, the command is:
SELECT ENAME , GRADE , GROSS FROM EMPLOYEE;
We can select particular rows from a table by specifying a condition through WHERE ci
with SELECT statement. E.g. In employee table if we want to select rows where Gendey
then command is
aus alo
is fem,
SELECT * FROM EMPLOYEE WHERE GENDER ="F';
E.g.2. in order to select rows where salary is greater than 48000, then command is:
SELECT * FROM EMPLOYEE WHERE GROSS > 48000 ;
ELIMINATING REDUNDANT DATA ae
The DISTINCT keyword eliminates duplicate rows from the results of a SELECT statement fy
example SELECT GENDER FROM EMPLOYEE ;
Gender
EM
M
F
M
M
F
SELECT DISTINCT(GENDER) FROM EMPLOYEE ;
[ DISTINCT(GENDER) |
sd i
VIEWING STRUCTURE OF A TABLE
: If we want to know the structure of a table, we can use DESCRIBE or DESC
command, as per following syntax: DESCRIBE | DESC ;
e.g. to view the structure of table EMPLOYEE, command is :
DESCRIBE EMPLOYEE ;
OR
DESC EMPLOYEE ;
name
The columns that we select in a query can be given a different name, ie. column alias ™
output purpose. Syntax;
RED eaeiter Sel 24/06/202 5 GELECT AS column a
“noun splay ECODE column ar EMP
SELECT ECODE AS"
AS"EMPLOvEn
CODE" FROM EMPLO
oven;
CONDITION BASED ON.A RANGE
The BETWEEN operator define:
the condition t
range of value
; nclude both lower value and
to display ECODE, ENAME and GRADE of those emplovenwya
‘and $0000, command is: ¢ employees whose
SELECT ECODE, ENAME GRADE FROM EMPLOYEE
WHERE GROSS BETWEEN 40000 AND 50000 ;
that the column vatue
The rang ius tn ead
salary is between 40000
Output will be:
ECODE] ENAME | GRADE.
1001 [Ravi__|e4
1006 [Ruby [at
‘To specify a list of values, IN operator is used. The IN operator selects value that match.
any value ina given list of values. E.g.
SELECT * FROM EMPLOYEE WHERE GRADE IN (A1’,‘A2');
‘ Output will be:
ECODE| ENAME | GENDE | GRADE | GROSS
R
1002_| Akash_| M. AL 35000
1006 _| Ruby FE AL 45000,
1005 [Sunny _|M. AQ 30000
1009 _|Neema | F A2 52000
‘The NOT IN operator finds rows that do not match in the list. Eg,
SELECT * FROM EMPLOYEE
WHERE GRADE NOT IN (1, ‘A2’};
Output will be:
ECODE] ENAME | GENDE | GRADE | GROSS
50000
Too1 | Ravi__|M fins
1004 Be
‘bed using two special
= LIKE operator is used for pattern matching in SQL. Patterns are descr
ildcard characters:
Tpercens() “= The % character matches any’ si
2 underscore(,)~ The._character matches any character:
:
|
tarts with Rin EI
. of employee whose names Ve
cians "SELECT ENAME FROM EMPLOYEE WHERE EN!
24/06/2025 Output will be:
[ENAMET
[Ravi |
[Ruby
48: to display details of employee whose second character in
SELECT * FROM EMPLOYEE WHERE ENAME LIKE ‘0%’;
Output will be;
ECODE] ENAME | GENDE | GRADE | GROSS
R
1004 | Neela FE BZ 38965,
1009 [Neema | F AZ 52000
@g, to display details of employee whose name ends with ‘y’.
SELECT * FROM EMPLOYEE WHERE ENAME LIKE ‘oy’;
Output will be:
ECODE] ENAME | GENDE | GRADE | GROSS
R
7005_| Sunny | M AZ 30000
1006 [Ruby | F AL 45000
SEARCHING FOR NULL
~The NULL value in a column can be searched for ina table using IS NULL in the WHERE
clause. Eg. to list employee details whose salary contain NULL, we use the command
‘SELECT * FROM EMPLOYEE WHERE GROSS IS NULL;
eg
STUDENT
Roll No| Name Marks|
1 ‘ARUN NULL
2 RAVI 56
4 SANJAY NULL |
to display the names of those students whose marks is NULL, we use the command:
SELECT Name
FROM EMPLOYEE
WHERE Marks IS NULL;
Output will be;
Name
‘ARUN
SANJAY
SORTING RESULTS
Whenever the SELECT query is executed , the resulting rows appear in a predecided orée=
The ORDER BY clause allow sorting of query result. The sorting can be done either
ascending or ‘descending order, the default is ascending.
REDMI 13C 24/06/2025 0 11>] ies 24/06/2025 08:23 141e) yes
DELETE FROM ‘stablename> WHERE ;
For example, to remove the deta
DELETE FROM §
LOVEE table whose p
Se Brade,
DELETE FROM
S »
Galoted RET ESPECIIY aly condition with WHERE clause, then all the rows M
: i the table
NS above line will delete all rows from employee table. ™y
The DROP TABLE command lets Pie orang vase: aie 7 tRe
ckiaanttey nand lets you drop a table fro DROP ty,
DROP TABLE ;
8: to drop a table employee, we need to write:
DROP TABLE employee;
Once this command is
Biven, the table name is no longer recognized and no more commands cn,
given on that table.
After this command is executed, all the data in the table along with table structure will be del
leted,
SNO. DELETE COMMAND. DROP TABLE COMMAND
1 Itis a DML command. Itis a DDL Command. 7
2 This command is used to delete only | This command is used to delete all he daa ay
rows of data from a table table along with the structure of the table. The us
isno
longer recognized when this command gets xeows
3 | ‘Syntax of DELETE command is: ‘Syntax of DROP command is:
DELETE FROM DROP TABLE ;
WHERE ;
ALTER TABLE COMMAND
The ALTER TABLE command is used to change definitions of existing tables. (adding columns,
deleting columns etc.). The ALTER TABLE command is used for:
4, adding columns to a table
2. Modifying column-definitions ofa table.
3. Deleting columns ofa table.
4. Adding constraints to table,
5. Enabling/Disabling constraints,
tatype> ;
the EMPLOYEE table, we can write command as:
DDRESS VARCHAR(S0);
Anew column by the name ADDRESS.
will contain NULL value for the sabes
‘© to add a new column ADDRESS to
ALTER TABLE EMPLOYEE ADD Al
TPP r REDMI13C 24/06/2025 08:24 TO ADD PRIMARY KEY CONSTRAINT
ALTER TABLE ADO PRIMARY KEY (Co\wrn sor
#48 to add PRIMARY KEY constraint on column ECODE of table EMPLOYER com
ALTER TABLE EMPLOYEE ADD PRIMARY KEY (BCOOP)
ALTER TABLE ADD FOREIGN KEY (Column name) (HP tH ee. —
‘Table (Primary key of Parent Tal ,
REMOVING CONSTRAINTS
To remove primary key constraint from a table, we use ALTER TABLE commana
ALTER TABLE DROP PRIMARY KEY ;
‘To remove foreign key constraint from a table, we use ALTER TABLE command as
ALTER TABLE DROP FOREIGN KEY ;
= Aconstraint is a condition or check applicable on a field (column) or set of flelds(coltumngy
~ Common types of constraints include:
[ SN] Constraints
a Ensures thata column cannot have NULL value
DEFAULT Providesa default value for a column whersnone is sper
Ensures that all values in a column are different
Makes sure that all values in a column satisfy certain criter=
PRIMARY KEY __| Used to uniquely identify a row in the table =
FOREIGN KEY | Used to ensure referential integrity of the data j
By default, a column can hold NULL. It you not want to allow NULL value im column, ther NOT
NULL constraint must be applied on that column. E.g,
(CREATE TABLE Customer
( SiDinteger NOT NULL,
Last.Name varchar(30) | NOTNULL,
First_Name varchar(30)
i
‘Columns S1D and Last_Name cannot include NULL, while First Name can include NULL.
‘An attempt to execute the following SQL statement,
INSERT INTO Customer VALUES (NULL, ‘Kumar’, ‘Ajay
_ will result in an error because this will cos a
NOT MULL constraint on that columns 5h joing BRL, pbk elere Pe , REDMI13C 24/06/2025 08:24 or
CHHATHCTANE A Cuetanior (BID Titegen Linet Mane varchar( 10)
Hivat, Name varehar(40), PRIMARY KEY (sip) )
1 ve latter way fe owt yout want to gpiely # composite primary key, a
CREATICTANLE Cctoner( — franel titeger NOT NULL SID Integer MOT Nit
Anat, Nanve varehae(30), FirstName varchar(39)
PHIMARY KEY (Hraniel, SID) )
PORKIGN KY. CONSTHAINE
Foreign key lea non key column of # table (eid table) that draws its yay,
Hrhiary hey of anther table( parent table)
The table tn whieh a foreign key ts defined is called a referencing table or ching
A table Lo whieh o furvign key points is called referenced table or parent table,
eS from,
table,
Parent Table
TABLE: STUDENT +
ROLLING, WANE 4}
Primary key
T TARE
7
‘TABLE: SCORE*
Here column Roll_No is a foreign key in table SCORE(Child Table) and it is drawing its
values from Primary key (ROLL.NO) of STUDENT table,(Parent Key).
CREATE TABLE STUDENT
( ROLL.NO integer NOT NULL PRIMARY KEY,
NAME VARCHAR(30), CLASS VARCHAR(3) );
CREATE TABLE SCORE
(_ ROLL_NO integer MARKS integer,
FOREIGN KEY(ROLL,.NO) REFERNCES. STUDENT(ROLL_NO) );
* Foreign key 1s always defined in the child table.
FOREIGN KEY(column name) REFERENCES Parent_Table(PK of Parent Table);
Referencing action with ON DELETE clause determines what to do in case of a DELETE octus
parent table
occurs in the parent table,
Ce
REDMI 13C 24/06/2025 08:
"
Referencing action with ON UPDATE clause determines what to do in case of" 9008 pe This action states that
P CASCADE rent table, then automa og ceet OF UPR
ron arth ces automatically delete op oATE
mec = cade the action tony cci® OF update the mone
NU his action states that ee dana 7
“Aabesa hat if a DEL es
Ment table, then set the foreign ETE or UP
ICTION : Any attemp key column in the ema egerato
OA Pern . pt for DELETE or urate child able to NULL a row from th
& este action rejects the DELETE an in parent table is not allowed,
xe two tables
Customer(customer id, name)
DATE operation for the parent tabl
Customer_sales(transaction id,
ed columns indicate amount, er i
erin cous net inary kerala
wakes shoul take place incase ofa DELETE or UPDATE ge
7 E or UPDATE in the
Ein the parent table
elsCREATE TABLE Customer (
customer_id int Not Null Primary Key
name varchar(30) ) ;
CREATE TABLE Customer_sales
transaction_id Not Null Primary Key
amount int, customer_id int,
FOREIGN KEY(customer_id) REFERENCES Customer (customer_id) ON DELETE NO
‘acTION ON UPDATE NO ACTION );
{¢Distinguish between a Primary Key anda Unique key ina table,
a sun Peary KEY = UNIQUE KEY
i mn having Primary key can't [ Column having Uni
contain NULL value Nui! eee Inique Key can contain
Z| There can be only one primary key inj Many columns can be defined as Uniqu
Table. key
(Distinguish between ALTER Command and UPDATE command of SQL
S.NO, “ALTER COMMAND UPDATE COMMAND
1_ | Itisa DDL Command Ttis a DML command
2, | Itis used to change the definition of Tris used to modify the data’ values present
in the rows of the table.
existing table, i
deleting column, etc.
Syntax for ‘adding col
‘ALTER TABLE
