SQL programs for practical exam
SQL Prog1
1. CREATE TABLE student (Rollno number(5), sname VARCHAR2(15),Lang int(3),
Eng int(3), s1 int(3), s2 int(3), s3 int(3), s4 int(3));
2. INSERT INTO student VALUES(1010, ‘RAJ’, 89, 97, 98, 99, 86, 95)
INSERT INTO student VALUES (1026, ‘KIRAN’, 67, 62, 72, 86, 72, 62)
INSERT INTO student VALUES (1042, ‘ANAND’, 78, 87, 92, 82, 72, 76)
INSERT INTO student VALUES (1250, ‘RAM’, 72, 86, 72, 62, 87, 68)
INSERT INTO student VALUES (5212, ‘VIJAYA’, 46, 58, 86, 92, 72, 62)
3. Desc student;
4. Alter table student ADD(TOTAL number(3), PERCENTAGE number(5,2));
5. update student set total = Lang+Eng+s1+s2+s3+s4;
6. update student set percentage = total/6;
7. select sname, percentage from student where percentage >=60;
8. select sname, percentage from student where percentage between 60 and 85;
9. select sname, percentage from student order by percentage desc;
10.Select * from student;
SQL Prog2
1. CREATE TABLE BESCOM (RRNO int(10) , NAME CHAR(25) NOT
NULL,BILLDATE
DATE, UNITS int(4));
2. INSERT INTO BESCOM VALUES('E1120', 'RAJKUMAR', '5-may-2024', 250)
INSERT INTO BESCOM VALUES ('E2210', 'KIRAN', '26-Mar-2024', 178)
INSERT INTO BESCOM VALUES (‘E1450’, ‘ANAND’, ‘15-April-2024’, 56)
INSERT INTO BESCOM VALUES (‘E2126’, ‘RAMA’, ‘08-May-2024’, 782)
INSERT INTO BESCOM VALUES (‘E1562’, ‘MANJULA’, ‘2-May-2024’, 562)
3. desc bescom;
4. alter table bescom add (BILLAMT number(10,2));
5. update bescom set billamt = 100 + units * 7.50 where units <100;
6. update bescom set billamt = 100 + 100*7.50+(units-100)*8.50 where units >100;
7. SELECT MAX(billamt), MIN(billamt), SUM(billamt) FROM BESCOM;
8. Select RRNO,Units,BILLAMT from bescom order by RRNO;
9. select * from bescom;
SQL Prog3
1 .CREATE TABLE student1 (Rollno int(5), Sname CHAR(15), DOB date, Gender
CHAR(1), Comb CHAR(4), Class varCHAR(2));
2. insert into student1 values (25003, 'Harshitha', '30-December-2004', 'F', 'PCMB','2A');
3 .insert into student1 values (25010, 'Amogh', '30-May-2010', 'M', 'PCMC','2A');
4 .insert into student1 values (25234, 'Puneeth', '15-March-2008', 'M', 'PCMC','2B');
5 .insert into student1 values (25789, 'Likhitha', '20-August-2011', 'F', 'PCMC','2D');
6. insert into student1 values (25567, 'Vihana', '30-May-2010', 'F', 'CEBA','2A');
7. select * from student1 where combn='pcmb' and combn='ceba';
8. select distinct(combn) from student1;
9. select max(sname),class from student group by sname order by class;
10. Select * from student where month(dob)=6;
11. SELECT gender, COUNT(*) FROM student1 GROUP BY gender;
SQL Prog4
[Link] TABLE Library (Title VARCHAR(75) NOT NULL, Author VARCHAR(60), Year int(4),
Category VARCHAR(25), Price float(7,2), Qty int(4));
2. insert into Library values('Data Structure', 'Channakeshava', 2019, 'Oxford University', 750.00, 12);
insert into Library values('C Programs', 'Gundu Rao', NULL, 'Computer Science', 700.00, 6);
insert into Library values('Computer Network', 'Swapan Kumar', 2019, 'Textbook', 450.00, 4);
insert into Library values('Python', 'Joseph Joyner', 2016, 'AI', 200.00, 7);
insert into Library values('Data Science', 'Uday', 2021, 'Data Science', 550.00, 2);
4. alter table library add(amount float(8,2));
5. update library set amount = price * qty;
6. select price from library where price between 400 and 900;
7. Select * from Library;
8. select Author from library where author like 'C%' or author like 'D%';
9. select Title,year,,category from library where category like '%science%';
10. select year,price from library where year>2010 and price<750;
*************************All the Best*******************