©Youth Af
S~L- S_ Na m e
Chaman
S_Age
69
S_DOB
2005/06/03
• Structured Query Longuoge Kamla 55 2006/09/24
• Longuoge used to interact with DBMS. Pushpa 23 2009/05/14
• Non Cose Sensitive
• Predefined Syntax l: Code For Table :- Columns I Datotype
Data b ase -t Tables -t Row Doto
Data Type in SQL:
• Int : To store lntergrol values ('1 bytes)
- DATA BAS E ~ • float: To store decimal values ('1 bytes)
§ @ § _ • Dote : Dote format ( YYYY / MM / DD l
• Char: Character ot fixed Length (1-255)
"--
Eab/ej ~
• Vorchor: Variable length
_Gable ] @ ~ Char Varchar
Fixed Length Variable Length
@ § Gab/ej ~ Memory Wastage No Memory Wastage
Char(20) Varchar(20)
-- I
S_Name
Chaman
S_Age
69
S_DOB
2005/06/03
• l•l:I•
•
Kamla 55 2006/09/24
I Pushpa 23 2009/05/14
CREATE INSERT SELECT
I I Q) Create a table named "YOUTHAF" using SQL ?
DROP UPDATE
I CREATE DATABASE BDMOS ;
I
ALTER DELETE
CREATE TABLE YOUTHAF
DDL: Dalo Detinilion Language
S Name Varchar
DML : Dalo Monipulalion Language S_Age Int ,
DQL : Dalo Query Language S_DOB Date
rw1:,1 ·,·
Q) Write a SQL code to insert values in YOUTHAF S_Name S_Age S_D08
Chaman 69 2005/06/03
INSERT INTO YOUTHAF VALUES
( ' Chaman' , 69 , ' 2005/06/03 ' ) ; Kamla 55 2006/09/24
' -
... Pappu 2003/02/16
Cham an 69 2005/06/03
INSERT INTO YOUTHAF VALUES (S_Name, S_DOB)
( ' Pappu' , ' 2003/02/16 ' ) ;
!f!SEln ll'JTO VALUES
I Chnmnn' , 69 ·· 2005/0G/03 · ) , NOTE.:
' Kamla ' , 55 , ' 2006/09/24 ' ) ; Except numerical values everything is enclosed
in quotation marks
S_Name S_Age S_D08
Chaman 69 2005/06/03
Kamla 55 2006/09/24
Relation : Other name of table Q) Cre ate a table "STUD" with CONSTRAINTS :-
Attribute : Columns or fields
Column Datatype Size Constraints
Tuple : Rows or Records
SRollNo Int Primary Key
Degree : No. of rows
SAdmNo Int
Cardinality : No. of columns
SName Varchar 30 NOT NULL
SMarks Int Greater than 40
KEYS -
DOB Date NOT NULL
Primary Key : Unique Identification (Non Empty) Gender Char 1
Candidate Key : Keys which con act as Primary Key City Varchar 20 Default: Delhi
Alternate Key : All Candidate Keys except Primary
CREATE TABLE STUD
Ca ndidate Key Alternate Key/
Primary Key
Sec:ondary Key
Rollno
Admno
I Rollno I I Admno l
SRollNo
SAdmNo
INTEGER
INTEGER
PRIMARY KEY ,
SName VARCHAR (30) NOT NULL ,
SMarks INTEGER CHECK (Smarks>40) ,
DOB DATE NOTNULL ,
Gender CHAR (1)
CREATE TABLE STUD Q) Write the SQL command to remove column "Age".
(
ALTER TABLE STUD
SRollNo INTEGER PRIMARY KEV
' (
SAdmNo INTEGER
DROP AG E;
SName VARCHAR (30) NOT NULL
' );
SMarks INTEGER CHECK (Smarks>40) ,
DOB DATE NOT NULL ,
ALTER TABLE STUD
Gender CHAR (1)
(
City VARCHAR
DROP COLUMN AGE ;
);
);
SRollNo SAdmNo SName Smarks DOB Gender City
SRollNo SAd mNo SName Smarks DOB Gonder City
- -■
Q) Write the SQL command to add new column "Age".
Q) W rite the SQL command to delete table STUD.
I
ALTER TABLE STUD (
DROP TABLE STUD
ADD ( AGE INTEGER);
);
Q) Write SQL command to delete DATABASE BDM OS.
DROP DATABASE BDMOS
J Iii a 1>! □- 0, 0, ~. - , <? , A 8 (I r; ID III llllfaty
The SQL UPDATE Statement
Used to modify t he existing records in a table .
UPDATE table_
i name
SET Coli lvl,H Cl ik·ii'AII Ci:D11u1;P; •«·D•tA
cc
WHERE condition;
CREATE TABLE students
id INT PRIMARY KEY,
name VARCHAR 50 ,
age INT,
marks INT
INSERT INTO students
1 , 'Aman' , 20, 75 ,
'Neha ' , 22, 85 ,
'Rohan' , 21, 90 ;
SELECT * FROM students;
id [Link]• mark5
~,,..~·
Aman 20 75
2 Neha 22 85
3 Rohan 21 90
lhi"'!t~ ~
..
a
" • • , • a
rm Ill
. 0 □, 0 , D, 8 tll l.l~ta"I
=m• .... =""
Aman 20 75
'
2 Neha 22 85
3 21 90
"""'"
SET marks = 95
WHERE id = 2;1
;d •=•
Aman
__ _J ...
20
m"""
75
'
2 Neha 22 95
90
3
"""'" 21
lo.l'lo +
••1i1V!ll
..,
• 0
id II• PRIMARY KEY,
name VARC~, 50 ,
age INT,
marks INT
INSERT INTO students id, name, age, marks VALUES
'Aman ' , 20, 75 , ,
'Neha ' , 22, 85 ,
3, 'Rohan ' , 21, 90 ;
SELECT • FROM students;
('<,),
id [Link] i1!Je m.i,1cs
Aman 20 75
2 Nt'ha 22 as
3 Rohan 21 90
l ', I'-':
SELECT • FROM student i ,e " · □. 0 0 - - t? A El . d' rm - w 1.11,... ,y
DELETE FROM stude nts
WH~E id = 1;
DELETE FROM students
s > 100;
J @ O ", O_ 0, 0, ~ -. t? A. B a rm - W Lllw¥y
DELETE FROM students
WHERE id = 1 ;
DELETE FROM students
WHERE marks> 100;
a
id
3
R";r 6' !
Neha
0
Rohan
,, □, o, ◊,
- ,,• A,---B •
22
23
rm mm«
•oo
103
- Ill LI~
DELETE FROM students
WHERE marks> 100;
DELETE FROM students;
(empty) (l'mply) (empty) (empty)
.J 61 (I 1>, □, 0, 0, -, (} ' ,. B () CD
SELECT + FROM students;
SELECT name, marks FROM students;
, Neha
- Ill L i ~
SELECT • FROM students
WHERE id= 2;
name
Ill r:::J library
SELECT * FROM students
ORDER BY marks ASC;
ago
20
~~
22
23
SELECT * FROM students
ORDER BY marks DESC;
- 18Mli ·t