DBMS PRACTICAL ASSIGNMENTS WITH ANSWERS.pdf

NICE

1. DBMS PRACTICAL ASSIGNMENTS
1. Write SQL queries to perform following database operations
i) Create a database with name SHEKHAWATI_INSTITUTE.
ii) Create a table BCAFIRSTYEAR_RECORDS with following column names ROLL_NUMBER(PRIMARY KEY), FIRST_NAME, LAST_NAME, FATHER_NAME,
MOTHER_NAME, GENDER, CONTACT_NO, EMAIL_ID, AADHAR_NUMBER, DOB.
iii) Insert any 10 records in BCAFIRSTYEAR_RECORDS.
iv) Display BCAFIRSTYEAR_RECORDS table records.
ANS. SQL command for database creation
CREATE DATABASE SHEKHAWATI_INSTITUTE;
SQL command for table creation
CREATE TABLE BCAFIRSTYEAR_RECORDS (ROLL_NUMBER INT PRIMARY KEY, FIRST_NAME VARCHAR (20), LAST_NAME VARCHAR (20), FATHER_NAME
VARCHAR (20), MOTHER_NAME VARCHAR (20), GENDER VARCHAR (10), CONTACT_NO NUMBER (10), EMAIL_ID VARCHAR (20), AADHAR_NUMBER NUMBER (12), DOB
DATE);
SQL command for insert data into database table
(1) INSERT INTO BCAFIRSTYEAR_RECORDS VALUES (1, ‘RAHUL’, ‘KUMAWAT’, ‘RAMGOPAL KUMAWAT’, ‘GITA DEVI’ , ‘MALE’, 7597553336,
‘[email protected]’, 847585758585, ‘12NOV2000’);
(2) INSERT INTO BCAFIRSTYEAR_RECORDS VALUES (2, 'DEEPAK', 'KUMAWAT', 'RAMPAL KUMAWAT', 'NITA DEVI', 'MALE', 7597553337, '[email protected]',
847585758586, '12NOV2002');
(3) INSERT INTO BCAFIRSTYEAR_RECORDS VALUES (3, 'DEEPIKA', 'KUMARI', 'RAMESH KUMAWAT', 'NIKITA DEVI', 'FEMALE', 7597553338, '[email protected]',
847585758587, '12FEB2001');
(4) INSERT INTO BCAFIRSTYEAR_RECORDS VALUES (4, 'NAVYA', 'KUMARI', 'RAGHAV KUMAWAT', 'SITA DEVI', 'FEMALE', 7597553339, '[email protected]',
847585758588, '12JAN1999');
(5) INSERT INTO BCAFIRSTYEAR_RECORDS VALUES (5, 'MADHU', 'SHARMA', 'SANDEEP SHARMA', 'SITA DEVI', 'FEMALE', 7597553339, '[email protected]',
847585758589, '15JAN1998');
(6) INSERT INTO BCAFIRSTYEAR_RECORDS VALUES (6, 'MOHIT', 'SHARMA', 'SANJAY SHARMA', 'SANGITA DEVI', 'MALE', 7597553340, '[email protected]',
847585758590, '15MAY1999');
(7) INSERT INTO BCAFIRSTYEAR_RECORDS VALUES (7, 'NEERAJ', 'SHEKHAWAT', 'JAGDISH SHEKHAWAT', 'SUSHILA DEVI', 'MALE', 7597553341,
'[email protected]', 847585758591, '19MAY1999');
(8) INSERT INTO BCAFIRSTYEAR_RECORDS VALUES (8, 'NIRMAL', 'KUMAWAT', 'JAGDISH K UMAWAT', 'SUSHILA DEVI', 'MALE', 7597553341,
'[email protected]', 847585758592, '19MAY1998');

2. (9) INSERT INTO BCAFIRSTYEAR_RECORDS VALUES (9, 'NIRMALA', 'KUMARI', 'GIRDARI LAL KUMAWAT', 'SULOCHNA DEVI', 'FEMALE', 7597553344,
'[email protected]', 847585758593, '19JUNE1997');
(10) INSERT INTO BCAFIRSTYEAR_RECORDS VALUES (10, 'KAVYA', 'KUMARI', 'BANWARI LAL KUMAWAT', 'DEEPIKA DEVI', 'FEMALE', 7597553344,
'[email protected]', 847585758594, '19JUL1997');

Command for display records of BCAFIRSTYEAR_RECORDS table
SELECT * FORM BCAFIRSTYEAR_RECORDS;
OUTPUT:

3. 2. Write SQL queries to perform following database operations
i) Create a table ADDRESS with following column names AADHAR_NUMBER (PRIMARY KEY),WARD_NO, VILLAGE/CITY, POST_OFFICE, PIN_CODE, TAHSIL,
DISTRICT, STATE .
ii) Insert records in ADDRESS table according to student AADHAR_NUMBER column in BCAFIRSTYEAR_RECORDS table.
iii) Display ADDRESS table records.
ANS. SQL command for ADDRESS table creation
CREATE TABLE ADDRESS (AADHAR_NUMBER NUMBER(12) PRIMARY KEY,WARD_NO INT, VILLAGE_OR_CITY VARCHAR(20), POST_OFFICE VARCHAR(20), PIN_CODE
INT, TAHSIL VARCHAR(20), DISTRICT VARCHAR(20), STATE VARCHAR(20));
SQL command for insert data into database table
(1) INSERT INTO ADDRESS VALUES (847585758585, 12, 'SIKAR', 'SIKAR', 332001, 'SIKAR', 'SIKAR', 'RAJASTHAN');
(2) INSERT INTO ADDRESS VALUES (847585758586, 6, 'SIKAR', 'SIKAR', 332001, 'SIKAR', 'SIKAR', 'RAJASTHAN');
(3) INSERT INTO ADDRESS VALUES (847585758587, 6, 'SIKAR', 'SIKAR', 332001, 'SIKAR', 'SIKAR', 'RAJASTHAN');
(4) INSERT INTO ADDRESS VALUES (847585758588, 3, 'SIKAR', 'SIKAR', 332001, 'SIKAR', 'SIKAR', 'RAJASTHAN');
(5) INSERT INTO ADDRESS VALUES (847585758589, 8, 'SIKAR', 'SIKAR', 332001, 'SIKAR', 'SIKAR', 'RAJASTHAN');
(6) INSERT INTO ADDRESS VALUES (847585758590, 5, 'SIKAR', 'SIKAR', 332001, 'SIKAR', 'SIKAR', 'RAJASTHAN');
(7) INSERT INTO ADDRESS VALUES (847585758591, 9, 'SIKAR', 'SIKAR', 332001, 'SIKAR', 'SIKAR', 'RAJASTHAN');
(8) INSERT INTO ADDRESS VALUES (847585758592, 9, 'SIKAR', 'SIKAR', 332001, 'SIKAR', 'SIKAR', 'RAJASTHAN');
(9) INSERT INTO ADDRESS VALUES (847585758593, 9, 'SIKAR', 'SIKAR', 332001, 'SIKAR', 'SIKAR', 'RAJASTHAN');
(10) INSERT INTO ADDRESS VALUES (847585758594, 9, 'SIKAR', 'SIKAR', 332001, 'SIKAR', 'SIKAR', 'RAJASTHAN');
Command for display records of BCAFIRSTYEAR_RECORDS table
SELECT * FROM ADDRESS;
OUTPUT:

5. 3. Write SQL queries to perform following database operations
i) Add 6 new columns in BCAFIRSTYEAR_RECORDS table with name 10TH_ROLLNUMBER, 10TH_TOTAL_MARKS, 10TH_PERCENTAGE, 12TH_ROLLNUMBER,
12TH_TOTAL_MARKS, 12TH_PERCENTAGE.
ii) UPDATE VALUES IN FOLLOWING NEW 7 COLUMNS IN BCAFIRSTYEAR_RECORDS TABLE
iii) DISPLAY RESULT TABLE.
ANS. SQL Command for ADD new columns in BCAFIRSTYEAR_RECORDS table
ALTER TABLE BCAFIRSTYEAR_RECORDS ADD (TENTH_ROLLNUMBER INT, TENTH_TOTAL_MARKS INT, TENTH_PERCENTAGE FLOAT, TWELFTH_ROLLNUMBER INT,
TWELFTH_TOTAL_MARKS INT, TWELFTH_PERCENTAGE FLOAT);
SQL Command for update new columns in BCAFIRSTYEAR_RECORDS table
(1) UPDATE BCAFIRSTYEAR_RECORDS SET TENTH_ROLLNUMBER=12121, TENTH_TOTAL_MARKS=456, TENTH_PERCENTAGE=91.2,
TWELFTH_ROLLNUMBER=13131, TWELFTH_TOTAL_MARKS=432, TWELFTH_PERCENTAGE=72 WHERE ROLL_NUMBER=1 ;
(2) UPDATE BCAFIRSTYEAR_RECORDS SET TENTH_ROLLNUMBER=12122, TENTH_TOTAL_MARKS=450, TENTH_PERCENTAGE=90,
TWELFTH_ROLLNUMBER=13132, TWELFTH_TOTAL_MARKS=430, TWELFTH_PERCENTAGE=71.66 WHERE ROLL_NUMBER=2;
(3) UPDATE BCAFIRSTYEAR_RECORDS SET TENTH_ROLLNUMBER=12123, TENTH_TOTAL_MARKS=340, TENTH_PERCENTAGE=68,
TWELFTH_ROLLNUMBER=13133, TWELFTH_TOTAL_MARKS=450, TWELFTH_PERCENTAGE=75 WHERE ROLL_NUMBER=3 ;
(4) UPDATE BCAFIRSTYEAR_RECORDS SET TENT H_ROLLNUMBER=12124, TENTH_TOTAL_MARKS=340, TENTH_PERCENTAGE=68,
TWELFTH_ROLLNUMBER=13134, TWELFTH_TOTAL_MARKS=456, TWELFTH_PERCENTAGE=76 WHERE ROLL_NUMBER=4 ;
(5) UPDATE BCAFIRSTYEAR_RECORDS SET TENTH_ROLLNUMBER=12125, TENTH_TOTAL_MARKS=467, TENTH_PERCENTAGE=93 .4,
TWELFTH_ROLLNUMBER=13135, TWELFTH_TOTAL_MARKS=567, TWELFTH_PERCENTAGE=94.5 WHERE ROLL_NUMBER=5 ;
(6) UPDATE BCAFIRSTYEAR_RECORDS SET TENTH_ROLLNUMBER=12126, TENTH_TOTAL_MARKS=411, TENTH_PERCENTAGE=82.2,
TWELFTH_ROLLNUMBER=13136, TWELFTH_TOTAL_MARKS=539, TWELFTH_PERCENTAGE=89.84 WHERE ROLL_NUMBER=6;
(7) UPDATE BCAFIRSTYEAR_RECORDS SET TENTH_ROLLNUMBER=12127, TENTH_TOTAL_MARKS=389, TENTH_PERCENTAGE=77.8,
TWELFTH_ROLLNUMBER=13137, TWELFTH_TOTAL_MARKS=423, TWELFTH_PERCENTAGE=70.5 WHERE ROLL_NUMBER=7 ;
(8) UPDATE BCAFIRST YEAR_RECORDS SET TENTH_ROLLNUMBER=12128, TENTH_TOTAL_MARKS=332, TENTH_PERCENTAGE=56.4,
TWELFTH_ROLLNUMBER=13138, TWELFTH_TOTAL_MARKS=470, TWELFTH_PERCENTAGE=78.3 WHERE ROLL_NUMBER=8 ;
(9) UPDATE BCAFIRSTYEAR_RECORDS SET TENTH_ROLLNUMBER=12129, TENTH_TOTAL_MARKS =452, TENTH_PERCENTAGE=90.4,
TWELFTH_ROLLNUMBER=13139, TWELFTH_TOTAL_MARKS=443, TWELFTH_PERCENTAGE=73.83 WHERE ROLL_NUMBER=9 ;
(10) UPDATE BCAFIRSTYEAR_RECORDS SET TENTH_ROLLNUMBER=12130, TENTH_TOTAL_MARKS=423, TENTH_PERCENTAGE=84.6,
TWELFTH_ROLLNUMBER=13140, TWELFTH_TOTAL_MARKS=542, TWELFTH_PERCENTAGE=90.33 WHERE ROLL_NUMBER=10;
Command for display records of BCAFIRSTYEAR_RECORDS table

6. SELECT * FROM BCAFIRSTYEAR_RECORDS;
OUTPUT:
R O L
L _ N
UM
B E R
F I R S T _ N A
ME
L A S T _ N A
ME
F A T H E R _
N A M E
M O T H E R _
N A M E
G E N D E
R
C O N T A C T
_ N O E M A I L _ I D
A A D H A R _ N U M B
ER D O B
T E N T H _
R O L L N U
M B E R
T E N T
H _ T O
T A L _
M A R K
S
T E N T
H _ P E
R C E N
T A G E
T W E L F T
H _ R O L L
N U M B E
R
TW
E L F
T H _
T O T
A L _
MA
RKS
T W E
L F T H
_ P E
RCE
N T A
GE
1 RAHUL KUMAWAT
RAMGOPAL
KUMAWAT
GITA DEVI MALE 7597553336
[email protected]
M
847585758585 12-NOV-00 12121 456 91.2 13131 432 72
2 DEEPAK KUMAWAT
RAMPAL
KUMAWAT
NITA DEVI MALE 7597553337
[email protected]
M
847585758586 12-NOV-02 12122 450 90 13132 430 71.66
3 DEEPIKA KUMARI
RAMESH
KUMAWAT
NIKITA DEVI FEMALE 7597553338
[email protected]
OM
847585758587 12-FEB-01 12123 400 80 13133 450 75
4 NAVYA KUMARI
RAGHAV
KUMAWAT
SITA DEVI FEMALE 7597553339
[email protected]
OM
847585758588 12-JAN-99 12124 340 68 13134 456 76
5 MADHU SHARMA
SANDEEP
SHARMA
SITA DEVI FEMALE 7597553339
[email protected]
OM
847585758589 15-JAN-98 12125 467 93.4 13135 567 94.5
6 MOHIT SHARMA
SANJAY
SHARMA
SANGITA
DEVI
MALE 7597553340
[email protected]
OM
847585758590 15-MAY-99 12126 411 82.2 13136 539 89.84
7 NEERAJ
SHEKHAWA
T
JAGDISH
SHEKHAWA
T
SUSHILA
DEVI
MALE 7597553341
[email protected]
M
847585758591 19-MAY-99 12127 389 77.8 13137 423 70.5
8 NIRMAL KUMAWAT
JAGDISH
KUMAWAT
SUSHILA
DEVI
MALE 7597553341
NIRMALK@GM
AIL.COM
847585758592 19-MAY-98 12128 332 56.4 13138 470 78.3
9 NIRMALA KUMARI
GIRDARI
LAL
KUMAWAT
SULOCHNA
DEVI
FEMALE 7597553344
NIRMALAKI@G
MAIL.COM
847585758593 19-JUN-97 12129 452 90.4 13139 443 73.83
10 KAVYA KUMARI
BANWARI
LAL
KUMAWAT
DEEPIKA
DEVI
FEMALE 7597553344
KAVYAKI@GM
AIL.COM
847585758594 19-JUL-97 12130 423 84.6 13140 542 90.33

7. 4. Create two different tables with the name SGI_STUDENTS and SGI_COMPETITION_CLASSES.
SGI_STUDENTS (ROLLNUMBER,FIRST_NAME,LAST_NAME, FATHER_NAME, DATE_OF_BIRTH, COURSE,GENDER, CONTACT)
SGI_COMPETITION_CLASSES (ROLLNUMBER, FIRST_NAME, LAST_NAME, FATHER_NAME, DATE_OF_BITRH, COURSE, GENDER, CONTACT)
i) INSERT 10 RECORDS IN BOTH TABLES.
ii) USE SET OPERATORS FOR FILTER RECORDS FROM BOTH TABLES.
Ans.
COMMAND FOR CTEATE SGI_STUDENTS TABLE
CREATE TABLE SGI_STUDENTS (ROLLNUMBER INT PRIMARY KEY,FIRST_NAME VARCHAR(20),LAST_NAME VARCHAR(20), FATHER_NAME VARCHAR(20),
DATE_OF_BIRTH DATE, COURSE VARCHAR(20),GENDER VARCHAR(6), CONTACT NUMBER);
COMMANDS FOR INSERT 10 RECORDS IN SGI_STUDENTS TABLE
1. INSERT INTO SGI_STUDENTS VALUES(1, 'RITIKA', 'CHOUDHARY', 'VIJAY CHOUDHARY','15MAY1995', 'BCA', 'FEMALE', 8798474849);
2. INSERT INTO SGI_STUDENTS VALUES(2, 'RITIK', 'CHOUDHARY', 'RAMESH CHOUDHARY','20APR1995', 'BCA', 'MALE', 8798474389);
3. INSERT INTO SGI_STUDENTS VALUES(3, 'KAVYA', 'SHARMA', 'VIJAY SHARMA','15MAY2000', 'BA', 'FEMALE', 9049484958);
4. INSERT INTO SGI_STUDENTS VALUES(4, 'NAVYA', 'KUMAWAT', 'RAJESH KUMAWAT','5JUN2005', 'BCA', 'FEMALE', 9890876567);
5. INSERT INTO SGI_STUDENTS VALUES(5, 'YOGESH', 'CHOUDHARY', 'MANOJ CHOUDHARY','15OCT2000', 'BCA', 'FEMALE', 9646474848);
6. INSERT INTO SGI_STUDENTS VALUES(6, 'DEEPAK', 'CHOUDHARY', 'VINOD CHOUDHARY','15NOV2002', 'BCA', 'FEMALE', 9767898767);
7. INSERT INTO SGI_STUDENTS VALUES(7, 'RAMESH', 'CHOUDHARY', 'RADHESHYAM CHOUDHARY','15DEC2003', 'BCA', 'FEMALE', 9928383840);
8. INSERT INTO SGI_STUDENTS VALUES(8, 'SUNDAR', 'JAIN', 'MANISH JAIN','02MAY2006', 'BCA', 'FEMALE', 9028374859);
9. INSERT INTO SGI_STUDENTS VALUES(9, 'RUDRA', 'CHOUDHARY', 'SHYAM CHOUDHARY','06APR2007', 'BCA', 'FEMALE', 8303948594);
10. INSERT INTO SGI_STUDENTS VALUES(10, 'NIKITA', 'SHARMA', 'RAM SHARMA','01MAY2008', 'BCA', 'FEMALE', 9384748494);
SELECT * FROM SGI_STUDENTS;
COMMAND FOR CTEATE SGI_COMPATITION_CLASSES TABLE
CREATE TABLE SGI_COMPETITION_CLASSES(ROLLNUMBER INT, FIRST_NAME VARCHAR(20), LAST_NAME VARCHAR(20), FATHER_NAME VARCHAR(20),
DATE_OF_BITRH DATE, COURSE VARCHAR(20), GENDER VARCHAR(6), CONTACT NUMBER);
COMMANDS FOR INSERT 10 RECORDS IN SGI_COMPETITION_CLASSES TABLE

8. 1. INSERT INTO SGI_COMPETITION_CLASSES VALUES(1, 'RITIKA', 'CHOUDHARY', 'VIJAY CHOUDHARY','15MAY1995', 'RAS', 'FEMALE',
8798474849);
2. INSERT INTO SGI_COMPETITION_CLASSES VALUES(2, 'RITIK', 'CHOUDHARY', 'RAMESH CHOUDHARY','20APR1995', 'SSC', 'MALE',
8798474389);
3. INSERT INTO SGI_COMPETITION_CLASSES VALUES(3, 'KAVYA', 'SHARMA', 'VIJAY SHARMA','15MAY2000', 'POLICE', 'FEMALE', 9049484958);
4. INSERT INTO SGI_COMPETITION_CLASSES VALUES(4, 'NAVYA', 'KUMAWAT', 'RAJESH KUMAWAT','5JUN2005', 'SSC CGL', 'FEMALE',
9890876567);
5. INSERT INTO SGI_COMPETITION_CLASSES VALUES(5, 'YOGESH', 'CHOUDHARY', 'MANOJ CHOUDHARY','15OCT2000', 'SSC GD', 'FEMALE',
9646474848);
6. INSERT INTO SGI_COMPETITION_CLASSES VALUES(6, 'DEEPAK', 'CHOUDHARY', 'VINOD CHOUDHARY','15NOV2002', 'RAS', 'FEMALE',
9767898767);
7. INSERT INTO SGI_COMPETITION_CLASSES VALUES(7, 'RAMESH', 'CHOUDHARY', 'RADHESHYAM CHOUDHARY','15DEC2003', 'SSC', 'FEMALE',
9928383840);
8. INSERT INTO SGI_COMPETITION_CLASSES VALUES(8, 'SUNDARI', 'SHARMA', 'MANISH SHARMA','23MAY2008', 'RAS', 'FEMALE', 8948495859);
9. INSERT INTO SGI_COMPETITION_CLASSES VALUES(9, 'RADIKA', 'MARAN', 'SHYAM MARAN','06APR2004', 'BCA', 'FEMALE', 9485958596);
10. INSERT INTO SGI_COMPETITION_CLASSES VALUES(10, 'DEEPIKA', 'SHARMA', 'RAMLAL SHARMA','11MAR2009', 'BCA', 'FEMALE',
9548596867);

SELECT * FROM SGI_COMPETITION_CLASSES;
COMMANDS FOR PERFORM SET OPERATIONS IN THE FOLLOWING TABLES
1. SELECT * FROM SGI_STUDENTS UNION SELECT * FROM SGI_COMPETITION_CLASSES;
2. SELECT * FROM SGI_STUDENTS UNION ALL SELECT * FROM SGI_COMPETITION_CLASSES;
3. SELECT * FROM SGI_STUDENTS INTERSECT SELECT * FROM SGI_COMPETITION_CLASSES;
4. SELECT * FROM SGI_STUDENTS MINUS SELECT * FROM SGI_COMPETITION_CLASSES;

9. OUTPUT:
SGI_STUDENTS TABLE
ROLLNUMBER FIRST_NAME LAST_NAME FATHER_NAME DATE_OF_BIRTH COURSE GENDER CONTACT
1 RITIKA CHOUDHARY VIJAY CHOUDHARY 15-MAY-95 BCA FEMALE 8798474849
2 RITIK CHOUDHARY RAMESH CHOUDHARY 20-APR-95 BCA MALE 8798474389
3 KAVYA SHARMA VIJAY SHARMA 15-MAY-00 BA FEMALE 9049484958
4 NAVYA KUMAWAT RAJESH KUMAWAT 05-JUN-05 BCA FEMALE 9890876567
5 YOGESH CHOUDHARY MANOJ CHOUDHARY 15-OCT-00 BCA FEMALE 9646474848
6 DEEPAK CHOUDHARY VINOD CHOUDHARY 15-NOV-02 BCA FEMALE 9767898767
7 RAMESH CHOUDHARY RADHESHYAM CHOUDHARY 15-DEC-03 BCA FEMALE 9928383840
8 SUNDAR JAIN MANISH JAIN 02-MAY-06 BCA FEMALE 9028374859
9 RUDRA CHOUDHARY SHYAM CHOUDHARY 06-APR-07 BCA FEMALE 8303948594
10 NIKITA SHARMA RAM SHARMA 01-MAY-08 BCA FEMALE 9384748494

SGI_COMPATITION_CLASSES
ROLLNUMBER FIRST_NAME LAST_NAME FATHER_NAME DATE_OF_BITRH COURSE GENDER CONTACT
1 RITIKA CHOUDHARY VIJAY CHOUDHARY 15-MAY-95 RAS FEMALE 8798474849
2 RITIK CHOUDHARY RAMESH CHOUDHARY 20-APR-95 SSC MALE 8798474389
3 KAVYA SHARMA VIJAY SHARMA 15-MAY-00 POLICE FEMALE 9049484958
4 NAVYA KUMAWAT RAJESH KUMAWA T 05-JUN-05 SSC CGL FEMALE 9890876567
5 YOGESH CHOUDHARY MANOJ CHOUDHARY 15-OCT-00 SSC GD FEMALE 9646474848
6 DEEPAK CHOUDHARY VINOD CHOUDHARY 15-NOV-02 RAS FEMALE 9767898767
7 RAMESH CHOUDHARY RADHESHYAM CHOUDHARY 15-DEC-03 SSC FEMALE 9928383840
8 SUNDARI SHARMA MANISH SHARMA 23-MAY-08 RAS FEMALE 8948495859
9 RADIKA MARAN SHYAM MARAN 06-APR-04 BCA FEMALE 9485958596
10 DEEPIKA SHARMA RAMLAL SHARMA 11-MAR-09 BCA FEMALE 9548596867

10. UNION
ROLLNUMBER FIRST_NAME LAST_NAME FATHER_NAME DATE_OF_BIRTH COURSE GENDER CONTACT
1 RITIKA CHOUDHARY VIJAY CHOUDHARY 15-MAY-95 BCA FEMALE 8798474849
1 RITIKA CHOUDHARY VIJAY CHOUDHARY 15-MAY-95 RAS FEMALE 8798474849
2 RITIK CHOUDHARY RAMESH CHOUDHARY 20-APR-95 BCA MALE 8798474389
2 RITIK CHOUDHARY RAMESH CHOUDHARY 20-APR-95 SSC MALE 8798474389
3 KAVYA SHARMA VIJAY SHARMA 15-MAY-00 BA FEMALE 9049484958
3 KAVYA SHARMA VIJAY SHARMA 15-MAY-00 POLICE FEMALE 9049484958
4 NAVYA KUMAWAT RAJESH KUMAWAT 05-JUN-05 BCA FEMALE 9890876567
4 NAVYA KUMAWAT RAJESH KUMAWAT 05-JUN-05 SSC CGL FEMALE 9890876567
5 YOGESH CHOUDHARY MANOJ CHOUDHARY 15-OCT-00 BCA FEMALE 9646474848
5 YOGESH CHOUDHARY MANOJ CHOUDHARY 15-OCT-00 SSC GD FEMALE 9646474848
6 DEEPAK CHOUDHARY VINOD CHOUDHARY 15-NOV-02 BCA FEMALE 9767898767
6 DEEPAK CHOUDHARY VINOD CHOUDHARY 15-NOV-02 RAS FEMALE 9767898767
7 RAMESH CHOUDHARY RADHESHYAM CHOUDHARY 15-DEC-03 BCA FEMALE 9928383840
7 RAMESH CHOUDHARY RADHESHYAM CHOUDHARY 15-DEC-03 SSC FEMALE 9928383840
8 SUNDAR JAIN MANISH JAIN 02-MAY-06 BCA FEMALE 9028374859
8 SUNDARI SHARMA MANISH SHARMA 23-MAY-08 RAS FEMALE 8948495859
9 RADIKA MARAN SHYAM MARAN 06-APR-04 BCA FEMALE 9485958596
9 RUDRA CHOUDHARY SHYAM CHOUDHARY 06-APR-07 BCA FEMALE 8303948594
10 DEEPIKA SHARMA RAMLAL SHARMA 11-MAR-09 BCA FEMALE 9548596867
10 NIKITA SHARMA RAM SHARMA 01-MAY-08 BCA FEMALE 9384748494

UNION ALL
ROLLNUMBER FIRST_NAME LAST_NAME FATHER_NAME DATE_OF_BIRTH COURSE GENDER CONTACT
1 RITIKA CHOUDHARY VIJAY CHOUDHARY 15-MAY-95 BCA FEMALE 8798474849
2 RITIK CHOUDHARY RAMESH CHOUDHARY 20-APR-95 BCA MALE 8798474389

11. 3 KAVYA SHARMA VIJAY SHARMA 15-MAY-00 BA FEMALE 9049484958
4 NAVYA KUMAWAT RAJESH KUMAWAT 05-JUN-05 BCA FEMALE 9890876567
5 YOGESH CHOUDHARY MANOJ CHOUDHARY 15-OCT-00 BCA FEMALE 9646474848
6 DEEPAK CHOUDHARY VINOD CHOUDHARY 15-NOV-02 BCA FEMALE 9767898767
7 RAMESH CHOUDHARY RADHESHYAM CHOUDHARY 15-DEC-03 BCA FEMALE 9928383840
8 SUNDAR JAIN MANISH JAIN 02-MAY-06 BCA FEMALE 9028374859
9 RUDRA CHOUDHARY SHYAM CHOUDHARY 06-APR-07 BCA FEMALE 8303948594
10 NIKITA SHARMA RAM SHARMA 01-MAY-08 BCA FEMALE 9384748494
1 RITIKA CHOUDHARY VIJAY CHOUDHARY 15-MAY-95 RAS FEMALE 8798474849
2 RITIK CHOUDHARY RAMESH CHOUDHARY 20-APR-95 SSC MALE 8798474389
3 KAVYA SHARMA VIJAY SHARMA 15-MAY-00 POLICE FEMALE 9049484958
4 NAVYA KUMAWAT RAJESH KUMAWAT 05-JUN-05 SSC CGL FEMALE 9890876567
5 YOGESH CHOUDHARY MANOJ CHOUDHARY 15-OCT-00 SSC GD FEMALE 9646474848
6 DEEPAK CHOUDHARY VINOD CHOUDHARY 15-NOV-02 RAS FEMALE 9767898767
7 RAMESH CHOUDHARY RADHESHYAM CHOUDHARY 15-DEC-03 SSC FEMALE 9928383840
8 SUNDARI SHARMA MANISH SHARMA 23-MAY-08 RAS FEMALE 8948495859
9 RADIKA MARAN SHYAM MARAN 06-APR-04 BCA FEMALE 9485958596
10 DEEPIKA SHARMA RAMLAL SHARMA 11-MAR-09 BCA FEMALE 9548596867

INTERACTION
NO RECORD FOUND
MINUS
ROLLNUMBER FIRST_NAME LAST_NAM E FATHER_NAME DATE_OF_BIRTH COURSE GENDER CONTACT
1 RITIKA CHOUDHARY VIJAY CHOUDHARY 15-MAY-95 BCA FEMALE 8798474849
2 RITIK CHOUDHARY RAMESH CHOUDHARY 20-APR-95 BCA MALE 8798474389
3 KAVYA SHARMA VIJAY SHARMA 15-MAY-00 BA FEMALE 9049484958
4 NAVYA KUMAWAT RAJESH KUMAWAT 05-JUN-05 BCA FEMALE 9890876567

12. 5 YOGESH CHOUDHARY MANOJ CHOUDHARY 15-OCT-00 BCA FEMALE 9646474848
6 DEEPAK CHOUDHARY VINOD CHOUDHARY 15-NOV-02 BCA FEMALE 9767898767
7 RAMESH CHOUDHARY RADHESHYAM CHOUDHARY 15-DEC-03 BCA FEMALE 9928383840
8 SUNDAR JAIN MANISH JAIN 02-MAY-06 BCA FEMALE 9028374859
9 RUDRA CHOUDHARY SHYAM CHOUDHARY 06-APR-07 BCA FEMALE 8303948594
10 NIKITA SHARMA RAM SHARMA 01-MAY-08 BCA FEMALE 9384748494

13. 5. Use all join operations on BCAFIRSTYEAR_RECORDS AND ADDRESS table (inner join, left outer join, right outer join, full outer join, cross join).
Ans.
COMMAND FOR INNER JOIN
1. SELECT FIRST_NAME,FATHER_NAME,DOB FROM BCAFIRSTYEAR_RECORDS INNER JOIN ADDRESS ON
BCAFIRSTYEAR_RECORDS.AADHAR_NUMBER=ADDRESS.AADHAR_NUMBER;
COMMAND FOR LEFT JOIN
2. SELECT FIRST_NAME, FATHER_NAME, DOB FROM BCAFIRSTYEAR_RECORDS LEFT JOIN ADDRESS ON
BCAFIRSTYEAR_RECORDS.AADHAR_NUMBER=ADDRESS.AADHAR_NUMBER;
COMMAND FOR RIGHT JOIN
3. SELECT FIRST_NAME, FATHER_NAME, DOB FROM BCAFIRSTYEAR_RECORDS LEFT JOIN ADD RESS ON
BCAFIRSTYEAR_RECORDS.AADHAR_NUMBER=ADDRESS.AADHAR_NUMBER;
COMMAND FOR FULL JOIN
4. SELECT FIRST_NAME, FATHER_NAME, DO B FROM BCAFIRSTYEAR_RECORDS FULL JOIN ADDRESS ON
BCAFIRSTYEAR_RECORDS.AADHAR_NUMBER=ADDRESS.AADHAR_NUMBER;
COMMAND FOR CROSS JOIN
5. SELECT * FROM BCAFIRSTYEAR_RECORDS CROSS JOIN ADDRESS;
OUTPUT:
INNER JOIN
FIRST_NAME FATHER_NAME DOB
RAHUL RAMGOPAL KUMAWAT 12-NOV-00
DEEPAK RAMPAL KUMAWAT 12-NOV-02

14. DEEPIKA RAMESH KUMAWAT 12-FEB-01
NAVYA RAGHAV KUMAWAT 12-JAN-99
MADHU SANDEEP SHARMA 15-JAN-98
MOHIT SANJAY SHARMA 15-MAY-99
NEERAJ JAGDISH SHEKHAWAT 19-MAY-99
NIRMAL JAGDISH KUMAWAT 19-MAY-98
NIRMALA GIRDARI LAL KUMAWAT 19-JUN-97

LEFT JOIN
FIRST_NAME FATHER_NAME DOB
RAHUL RAMGOPAL KUMAWAT 12-NOV-00
DEEPAK RAMPAL KUMAWAT 12-NOV-02
DEEPIKA RAMESH KUMAWAT 12-FEB-01
NAVYA RAGHAV KUMAWAT 12-JAN-99
MADHU SANDEEP SHARMA 15-JAN-98
MOHIT SANJAY SHARMA 15-MAY-99
NEERAJ JAGDISH SHEKHAWAT 19-MAY-99
NIRMAL JAGDISH KUMAWAT 19-MAY-98
NIRMALA GIRDARI LAL KUMAWAT 19-JUN-97
KAVYA BANWARI LAL KUMAWAT 19-JUL-97

RIGHT JOIN
FIRST_NAME FATHER_NAME DOB
RAHUL RAMGOPAL KUMAWAT 12-NOV-00
DEEPAK RAMPAL KUMAWAT 12-NOV-02
DEEPIKA RAMESH KUMAWAT 12-FEB-01

15. NAVYA RAGHAV KUMAWAT 12-JAN-99
MADHU SANDEEP SHARMA 15-JAN-98
MOHIT SANJAY SHARMA 15-MAY-99
NEERAJ JAGDISH SHEKHAWAT 19-MAY-99
NIRMAL JAGDISH KUMAWAT 19-MAY-98
NIRMALA GIRDARI LAL KUMAWAT 19-JUN-97
- - -

FULL JOIN
FIRST_NAME FATHER_NAME DOB
RAHUL RAMGOPAL KUMAWAT 12-NOV-00
DEEPAK RAMPAL KUMAWAT 12-NOV-02
DEEPIKA RAMESH KUMAWAT 12-FEB-01
NAVYA RAGHAV KUMAWAT 12-JAN-99
MADHU SANDEEP SHARMA 15-JAN-98
MOHIT SANJAY SHARMA 15-MAY-99
NEERAJ JAGDISH SHEKHAWAT 19-MAY-99
NIRMAL JAGDISH KUMAWAT 19-MAY-98
NIRMALA GIRDARI LAL KUMAWAT 19-JUN-97
KAVYA BANWARI LAL KUMAWAT 19-JUL-97
- - -

CROSS JOIN
ROLL_NUMBER FIRST_NAME FATHER_NAME VILLAGE_OR_CITY
1 RAHUL RAMGOPAL KUMAWAT SIKAR
1 RAHUL RAMGOPAL KUMAWAT SIKAR

16. 1 RAHUL RAMGOPAL KUMAWAT SIKAR
1 RAHUL RAMGOPAL KUMAWAT SIKAR
1 RAHUL RAMGOPAL KUMAWAT SIKAR
1 RAHUL RAMGOPAL KUMAWAT SIKAR
1 RAHUL RAMGOPAL KUMAWAT SIKAR
1 RAHUL RAMGOPAL KUMAWAT SIKAR
1 RAHUL RAMGOPAL KUMAWAT SIKAR
1 RAHUL RAMGOPAL KUMAWAT SIKAR
2 DEEPAK RAMPAL KUMAWAT SIKAR
2 DEEPAK RAMPAL KUMAWAT SIKAR
2 DEEPAK RAMPAL KUMAWAT SIKAR
2 DEEPAK RAMPAL KUMAWAT SIKAR
2 DEEPAK RAMPAL KUMAWAT SIKAR
2 DEEPAK RAMPAL KUMAWAT SIKAR
2 DEEPAK RAMPAL KUMAWAT SIKAR
2 DEEPAK RAMPAL KUMAWAT SIKAR
2 DEEPAK RAMPAL KUMAWAT SIKAR
2 DEEPAK RAMPAL KUMAWAT SIKAR
3 DEEPIKA RAMESH KUMAWAT SIKAR
3 DEEPIKA RAMESH KUMAWAT SIKAR
3 DEEPIKA RAMESH KUMAW AT SIKAR
3 DEEPIKA RAMESH KUMAWAT SIKAR
3 DEEPIKA RAMESH KUMAWAT SIKAR
3 DEEPIKA RAMESH KUMAWAT SIKAR
3 DEEPIKA RAMESH KUMAWAT SIKAR
3 DEEPIKA RAMESH KUMAWAT SIKAR
3 DEEPIKA RAMESH KUMAWAT SIKAR
3 DEEPIKA RAMESH KUMAWAT SIKAR
4 NAVYA RAGHAV KUMAWAT SIKAR

17. 4 NAVYA RAGHAV KUMAWAT SIKAR
4 NAVYA RAGHAV KUMAWAT SIKAR
4 NAVYA RAGHAV KUMAWAT SIKAR
4 NAVYA RAGHAV KUMAWAT SIKAR
4 NAVYA RAGHAV KUMAWAT SIKAR
4 NAVYA RAGHAV KUMAWAT SIKAR
4 NAVYA RAGHAV KUMAWAT SIKAR
4 NAVYA RAGHAV KUMAWAT SIKAR
4 NAVYA RAGHAV KUMAWAT SIKAR
5 MADHU SANDEEP SHARMA SIKAR
5 MADHU SANDEEP SHARMA SIKAR
5 MADHU SANDEEP SHARMA SIKAR
5 MADHU SANDEEP SHARMA SIKAR
5 MADHU SANDEEP SHARMA SIKAR
5 MADHU SANDEEP SHARMA SIKAR
5 MADHU SANDEEP SHARMA SIKAR
5 MADHU SANDEEP SHARMA SIKAR
5 MADHU SANDEEP SHARMA SIKAR
5 MADHU SANDEEP SHARMA SIKAR
6 MOHIT SANJAY SHARMA SIKAR
6 MOHIT SANJAY SHARMA SIKAR
6 MOHIT SANJAY SHARMA SIKAR
6 MOHIT SANJAY SHARMA SIKAR
6 MOHIT SANJAY SHARMA SIKAR
6 MOHIT SANJAY SHARMA SIKAR
6 MOHIT SANJAY SHARMA SIKAR
6 MOHIT SANJAY SHARMA SIKAR
6 MOHIT SANJAY SHARMA SIKAR
6 MOHIT SANJAY SHARMA SIKAR

18. 7 NEERAJ JAGDISH SHEKHAWAT SIKAR
7 NEERAJ JAGDISH SHEKHAWAT SIKAR
7 NEERAJ JAGDISH SHEKHAWAT SIKAR
7 NEERAJ JAGDISH SHEKHAWAT SIKAR
7 NEERAJ JAGDISH SHEKHAWAT SIKAR
7 NEERAJ JAGDISH SHEKHAWAT SIKAR
7 NEERAJ JAGDISH SHEKHAWAT SIKAR
7 NEERAJ JAGDISH SHEKHAWAT SIKAR
7 NEERAJ JAGDISH SHEKHAWAT SIKAR
7 NEERAJ JAGDISH SHEKHAWAT SIKAR
8 NIRMAL JAGDISH KUMAWAT SIKAR
8 NIRMAL JAGDISH KUMAWAT SIKAR
8 NIRMAL JAGDISH KUMAWAT SIKAR
8 NIRMAL JAGDISH KUMAWAT SIKAR
8 NIRMAL JAGDISH KUMAWAT SIKAR
8 NIRMAL JAGDISH KUMAWAT SIKAR
8 NIRMAL JAGDISH KUMAWAT SIKAR
8 NIRMAL JAGDISH KUMAWAT SIKAR
8 NIRMAL JAGDISH KUMAWAT SIKAR
8 NIRMAL JAGDISH KUMAWAT SIKAR
9 NIRMALA GIRDARI LAL KUMAWAT SIKAR
9 NIRMALA GIRDARI LAL KUMAWAT SIKAR
9 NIRMALA GIRDARI LAL KUMAWAT SIKAR
9 NIRMALA GIRDARI LAL KUMAWAT SIKAR
9 NIRMALA GIRDARI LAL KUMAWAT SIKAR
9 NIRMALA GIRDARI LAL KUMAWAT SIKAR
9 NIRMALA GIRDARI LAL KUMAWAT SIKAR
9 NIRMALA GIRDARI LAL KUMAWAT SIKAR
9 NIRMALA GIRDARI LAL KUMAWAT SIKAR

19. 9 NIRMALA GIRDARI LAL KUMAWAT SIKAR
10 KAVYA BANWARI LAL KUMAWAT SIKAR
10 KAVYA BANWARI LAL KUMAWAT SIKAR
10 KAVYA BANWARI LAL KUMAWAT SIKAR
10 KAVYA BANWARI LAL KUMAWAT SIKAR
10 KAVYA BANWARI LAL KUMAWAT SIKAR
10 KAVYA BANWARI LAL KUMAWAT SIKAR
10 KAVYA BANWARI LAL KUMAWAT SIKAR
10 KAVYA BANWARI LAL KUMAWAT SIKAR
10 KAVYA BANWARI LAL KUMAWAT SIKAR
10 KAVYA BANWARI LAL KUMAWAT SIKAR
100 rows








6. Write SQL queries to perform following database operations
i) Create a BCAFIRSTYEARSTUDENTS_MARKS table with following columns (ROLL_NUMBER,FIRST_NAME, LAST_NAME, FATHER_NAME, DBMS_MARKS,
OS_MARKS, ENGLISH_MARKS, CA_AND_ O_MARKS).
ii) Insert 5 students records in BCAFIRSTYEARSTUDENTS_MARKS table.
iii) Display BCAFIRSTYEARSTUDENTS_MARKS table data with total_marks and percentage.

20. Ans.
COMMAND FOR CREATE BCAFIRSTYEARSTUDENTS_MARKS TABLE
CREATE TABLE BCAFIRSTYEARSTUDENTS_MARKS (ROLL_NUMBER INT PRIMARY KEY, FIRST_NAME VARCHAR(20), LAST_NAME VARCHAR(20), FATHER_NAME VARCHAR(20),
DBMS_MARKS FLOAT, OS_MARKS FLOAT, ENGLISH_MARKS FLOAT, CA_AND_O_MARKS FLOAT, );
COMMANDS FOR INSERT 5 RECORDS IN BCAFIRSTYEARSTUDENTS_MARKS TABLE
1. INSERT INTO BCAFIRSTYEARSTUDENTS_MARKS(ROLL_NUMBER, FIRST_NAME, LAST_NAME, FATHER_NAME, DBMS_MARKS, OS_MARKS,
ENGLISH_MARKS, CA_AND_O_MARKS) VALUES(1, 'RAHUL', 'KUMAWAT', 'GHANSHYAM KUMAWAT', 89,78,90,60);
2. INSERT INTO BCAFIRSTYEARSTUDENTS_MARKS(ROLL_NUMBER, FIRST_NAME, LAST_NAME, FATHER_NAME, DBMS_MARKS, OS_MARKS,
ENGLISH_MARKS, CA_AND_O_MARKS) VALUES(2, 'DEEPAK', 'YADAV', 'GANPAT YADAV',90 ,88,90,97);
3. INSERT INTO BCAFIRSTYEARSTUDENTS_MARKS(ROLL_NUMBER, FIRST_NAME, LAST_NAME, FATHER_NAME, DBMS_MARKS, OS_MARKS,
ENGLISH_MARKS, CA_AND_O_MARKS) VALUES(3, 'SHYAM', 'CHOUDHARY', 'GYARSILAL CHOUDHARY', 78,67,78,56);
4. INSERT INTO BCAFIRSTYEARSTUDENTS_MARKS(ROLL_NUMBER, FIRST_NAME, LAST_NAME, FATHER_NAME, DBMS_MARKS, OS_MARKS,
ENGLISH_MARKS, CA_AND_O_MARKS) VALUES(4, 'SAPNA', 'CHOUDHARY', 'SHYAMLAL CHOUDHARY', 93,94,90,89);
5. INSERT INTO BCAFIRSTYEARSTUDENTS_MARKS(ROLL_NUMBER, FIRST_NAME, LAST_NAME, FATHER_NAME, DBMS_MARKS, OS_MARKS,
ENGLISH_MARKS, CA_AND_O_MARKS) VALUES(5, 'CHANDA', 'YADAV', 'MAHESH YADAV', 81,78,56,67);
COMMAND FOR DISPLAY TABLE DATA WITH TOTAL_MARKS
SELECT ROLL_NUMBER, FIRST_NAME, LAST_NAME, FATHER_NAME, DBMS_MARKS, OS_MARKS, ENGLISH_MARKS, CA_AND_O_MARKS,
DBMS_MARKS+OS_MARKS+ENGLISH_MARKS+CA_AND_O_MARKS AS TOTAL_MARKS FROM BCAFIRSTYEARSTUDENTS_MARKS;
COMMAND FOR DISPLAY TABLE DATA WITH PERCENTAGE
SELECT ROLL_NUMBER, FIRST_NAME, LAST_NAME, FATHER_NAME, DBMS_MARKS, OS_MARKS, ENGLISH_MARKS, CA_AND_O_MARKS,
(DBMS_MARKS+OS_MARKS+ENGLISH_MARKS+CA_AND_O_MARKS)/4 AS PERCENTAGE FROM BCAFIRSTYEARSTUDENTS_MARKS;
OUTPUT:
BCAFIRSTYEARSTUDENTS_MARKS WITH TOTAL_MARKS
ROLL_NUMBE FIRST_NAM LAST_NAM FATHER_NAM DBMS_M ARK OS_MARK ENGLISH_MARK CA_AND_O_MAR TOTAL_M ARK

21. R E E E S S S KS S
1 RAHUL KUMAWAT
GHANSHYAM
KUMAWAT
89 78 90 60 317
2 DEEPAK YADAV GANPAT YADAV 90 88 90 97 365
3 SHYAM CHOUDHARY
GYARSILAL
CHOUDHARY
78 67 78 56 279
4 SAPNA CHOUDHARY
SHYAMLAL
CHOUDHARY
93 94 90 89 366
5 CHANDA YADAV MAHESH YADAV 81 78 56 67 282

BCAFIRSTYEARSTUDENTS_MARKS WITH PERCENTAGE
ROLL_NUMBE
R
FIRST_NAM
E
LAST_NAM
E
FATHER_NAM
E
DBMS_M ARK
S
OS_MARK
S
ENGLISH_MARK
S
CA_AND_O_MARK
S
PERCENTAG
E
1 RAHUL KUMAWAT
GHANSHYAM
KUMAWAT
89 78 90 60 79.25
2 DEEPAK YADAV GANPAT YADAV 90 88 90 97 91.25
3 SHYAM CHOUDHARY
GYARSILAL
CHOUDHARY
78 67 78 56 69.75
4 SAPNA CHOUDHARY
SHYAMLAL
CHOUDHARY
93 94 90 89 91.5
5 CHANDA YADAV MAHESH YADAV 81 78 56 67 70.5


7. Apply filters on BCAFIRSTYEAR_RECORDS table with the help of order by, group by and having clauses.
Ans.
ORDER BY
1. select * from BCAFIRSTYEAR_RECORDS ORDER BY fIRST_NAME ASC;
2. select * from BCAFIRSTYEAR_RECORDS ORDER BY fIRST_NAME DESC;

22. GROUP BY
1. SELECT COUNT(ROLL_NUMBER), GENDER FROM BCAFIRSTYEAR_RECORDS GROUP BY GENDER;
HAVING
1. SELECT COUNT(ROLL_NUMBER), GENDER FROM BCAFIRSTYEAR_RECORDS GROUP BY GENDER HAVING SUM(TENTH_TOTAL_MARKS)>2040;
OUTPUT:
ORDER BY ASC
ROLL_NUMBER FIRST_NAME
2 DEEPAK
3 DEEPIKA
10 KAVYA
5 MADHU
6 MOHIT
4 NAVYA
7 NEERAJ
8 NIRMAL
9 NIRMALA
1 RAHUL

ORDER BY DESC
ROLL_NUMBER FIRST_NAME
1 RAHUL
9 NIRMALA
8 NIRMAL
7 NEERAJ

23. 4 NAVYA
6 MOHIT
5 MADHU
10 KAVYA
3 DEEPIKA
2 DEEPAK

GROUP BY
COUNT(ROLL_NUMBER) GENDER
5 MALE
5 FEMALE

HAVING
COUNT(ROLL_NUMBER) GENDER
5 FEMALE



8. Apply filters on BCAFIRSTYEAR_RECORDS table with the help of between and like logical operators.
Ans.
COMMAND FOR BETWEEN OPERATOR
1. SELECT ROLL_NUMBER, FIRST_NAME, TENTH_PERCENTAGE from BCAFIRSTYEAR_RECORDS WHERE ROLL_NUMBER BETWEEN 3 AND 8;
LIKE (SECOND LATTER A)

24. 1. SELECT ROLL_NUMBER, FIRST_NAME, TENTH_PERCENTAGE from BCAFIRSTYEAR_RECORDS WHERE FIRST_NAME LIKE '_A%';
OUTPUT:
BETWEEN
ROLL_NUMBER FIRST_NAME TENTH_PERCENTAGE
3 DEEPIKA 80
4 NAVYA 68
5 MADHU 93.4
6 MOHIT 82.2
7 NEERAJ 77.8
8 NIRMAL 56.4

LIKE
ROLL_NUMBER FIRST_NAME TENTH_PERCENTAGE
1 RAHUL 91.2
4 NAVYA 68
5 MADHU 93.4
10 KAVYA 84.6

9. Use aggregate functions for select data from BCAFIRSTYEAR_RECORDS table.
Ans.
COMMAND FOR MIN FUNCTION
1. SELECT MIN(TENTH_PERCENTAGE) FROM BCAFIRSTYEAR_RECORDS;
COMMAND FOR MAX FUNCTION

25. 2. SELECT MAX(TENTH_PERCENTAGE) FROM BCAFIRSTYEAR_RECORDS;
COMMAND FOR COUNT FUNCTION
3. SELECT COUNT(ROLL_NUMBER) FROM BCAFIRSTYEAR_RECORDS;
COMMAND FOR SUM FUNCTION
4. SELECT SUM(TENTH_TOTAL_MARKS) FROM BCAFIRSTYEAR_RECORDS;
COMMAND FOR AVG FUNCTION
5. SELECT AVG(TENTH_TOTAL_MARKS) FROM BCAFIRSTYEAR_RECORDS;
OUTPUT:
MIN
MIN(TENTH_PERCENTAGE)
56.4

MAX
MAX(TENTH_PERCENTAGE)
93.4

COUNT
COUNT(ROLL_NUMBER)
10

SUM

26. SUM(TENTH_TOTAL_M ARKS)
4120

AVG
AVG(TENTH_TOTAL_M ARKS)
412









10. Use all relational operators for select data form BCAFIRSTYEAR_RECORDS table.
Ans.
1. SELECT ROLL_NUMBER, FIRST_NAME, TENTH_PERCENTAGE FROM BCAFIRSTYEAR_RECORDS WHERE TENTH_PERCENTAGE=80;
2. SELECT ROLL_NUMBER, FIRST_NAME, TENTH_PERCENTAGE FROM BCAFIRSTYEAR_RECORDS WHERE TENTH_PERCENTAGE<>80;
3. SELECT ROLL_NUMBER, FIRST_NAME, TENTH_PERCENTAGE FROM BCAFIRSTYEAR_RECORDS WHERE TENTH_PERCENTAGE>80;
4. SELECT ROLL_NUMBER, FIRST_NAME, TENTH_PERCENTAGE FROM BCAFIRSTYEAR_RECORDS WHERE TENTH_PERCENTAGE<80;
5. SELECT ROLL_NUMBER, FIRST_NAME, TENTH_PERCENTAGE FROM BCAFIRSTYEAR_RECORDS WHERE TENTH_PERCENTAGE> =80;

27. 6. SELECT ROLL_NUMBER, FIRST_NAME, TENTH_PERCENTAGE FROM BCAFIRSTYEAR_RECORDS WHERE TENTH_PERCENTAGE<=80;
OUTPUT:
=
ROLL_NUMBER FIRST_NAME TENTH_PERCENTAGE
3 DEEPIKA 80

<>
ROLL_NUMBER FIRST_NAME TENTH_PERCENTAGE
1 RAHUL 91.2
2 DEEPAK 90
4 NAVYA 68
5 MADHU 93.4
6 MOHIT 82.2
7 NEERAJ 77.8
8 NIRMAL 56.4
9 NIRMALA 90.4
10 KAVYA 84.6

>
ROLL_NUMBER FIRST_NAME TENTH_PERCENTAGE
1 RAHUL 91.2
2 DEEPAK 90
5 MADHU 93.4
6 MOHIT 82.2
9 NIRMALA 90.4

28. 10 KAVYA 84.6

<
ROLL_NUMBER FIRST_NAME TENTH_PERCENTAGE
4 NAVYA 68
7 NEERAJ 77.8
8 NIRMAL 56.4

>=
ROLL_NUMBER FIRST_NAME TENTH_PERCENTAGE
1 RAHUL 91.2
2 DEEPAK 90
3 DEEPIKA 80
5 MADHU 93.4
6 MOHIT 82.2
9 NIRMALA 90.4
10 KAVYA 84.6

<=
ROLL_NUMBER FIRST_NAME TENTH_PERCENTAGE
3 DEEPIKA 80
4 NAVYA 68
7 NEERAJ 77.8
8 NIRMAL 56.4