Scribd
Upload
10 views6 pages

DBMS Lab Mini Project SQL Queries

Uploaded by

sumitashetty19
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
10 views6 pages

DBMS Lab Mini Project SQL Queries

Uploaded by

sumitashetty19
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

DBMS LAB with Mini Project(21CSL55) 2023

Program - 4
Aim: Introduce concepts of PLSQL and usage on the table.
Program: Program: Consider the schema for College Database:
STUDENT(USN, SName, Address, Phone, Gender)
SEMSEC(SSID, Sem, Sec)
CLASS(USN, SSID)
COURSE(Subcode, Title, Sem, Credits)
IAMARKS(USN, Subcode, SSID, Test1, Test2, Test3, FinalIA)
Write SQL queries to
i. List all the student details studying in fourth semester ‘C’ section.
ii. Compute the total number of male and female students in each semester and in each
section.
iii. Create a view of Test1 marks of student USN ‘1BI15CS101’ in all Courses.
iv. Calculate the FinalIA (average of best two test marks) and update the corresponding table
for all students.
v. Categorize students based on the following criterion:
If FinalIA = 17 to 20 then CAT = ‘Outstanding’
If FinalIA = 12 to 16 then CAT = ‘Average’
If FinalIA< 12 then CAT = ‘Weak’
Give these details only for 8th semester A, B, and C section students.
Schema Diagram:

Artificial Intelligence and Data Science Engineering AY: 2023-24 Page 1


DBMS LAB with Mini Project(21CSL55) 2023

(i) Query for creating table student:


create table student (
usn varchar(10),
sname varchar(15),
address varchar(15),
phone int,
gender varchar(6),
primary key(usn));
Output

(ii) Query for creating table semsec:


create table semsec (
ssid varchar(5),
sem int,
sec varchar(5),
primary key(ssid) );
Output

(iii) Query for creating table class:


create table class (
usn varchar(10),
ssid varchar(5),
primary key(usn),
foreign key(usn) references student(usn) on delete cascade,
foreign key(ssid) references semsec(ssid) on delete cascade );

Artificial Intelligence and Data Science Engineering AY: 2023-24 Page 2


DBMS LAB with Mini Project(21CSL55) 2023

Output

(iv) Query for creating table course:


create table course (
subcode varchar(8),
title varchar(15),
sem int,
credits int,
primary key(subcode) );
Output

(v) Query for creating table IAmarks:


create table IAmarks (
usn varchar(10),
subcode varchar(8),
ssid varchar(5),
test1 int,
test2 int,
test3 int,
finalIA int,
primary key(usn,subcode,ssid),
foreign key(usn) references student(usn) on delete cascade,
foreign key(subcode) references course (subcode),
foreign key(ssid) references semsec(ssid) );

Artificial Intelligence and Data Science Engineering AY: 2023-24 Page 3


DBMS LAB with Mini Project(21CSL55) 2023

Output

(i)Queries for inserting table STUDENT:


insert into STUDENT values('CS101','Arun','ujire','9481235681','Male');
insert into STUDENT values('CS102','Pramya','Mangalore','8945689532','Female');
insert into STUDENT values('CS103','Ravi','Bangalore','9568742361','Male');
insert into STUDENT values('CS104','Vani','Puttur','8945623145','Female');
insert into STUDENT values('CS105','Akshatha','Bantwal','9845632147','Female');
insert into STUDENT values('CS106','Ranjan','Karwar','9485632158','Male');
Sample Output
Select * from student;
(ii)Queries for inserting table SEMSEC:
insert into SEMSEC values('CS4A','4','A');
insert into SEMSEC values('CS4B','4','B');
insert into SEMSEC values('CS4C','4','C');
insert into SEMSEC values('CS8A','8','A');
insert into SEMSEC values('CS8B','8','B');
insert into SEMSEC values('CS8C','8','C');
Sample Output
Select * from SEMSEC;
(iii) Queries for inserting table CLASS:
insert into CLASS values('CS101','CS8A');
insert into CLASS values('CS102','CS8A');
insert into CLASS values('CS103','CS8B');
insert into CLASS values('CS104','CS8C');
insert into CLASS values('CS105','CS4C');
insert into CLASS values('CS106','CS4C');
Sample Output

Artificial Intelligence and Data Science Engineering AY: 2023-24 Page 4


DBMS LAB with Mini Project(21CSL55) 2023

Select * from CLASS;

(iv) Queries for inserting table COURSE:


insert into COURSE values('15CS41','ACA','4','5 ');
insert into COURSE values('15CS42 ','GTE','4','4');
insert into COURSE values('15CS43','C++','4','3');
insert into COURSE values('10CS81','JAVA','8','4');
insert into COURSE values('10CS82','WEB','8','4');
insert into COURSE values('10CS83','IOT','8','5');
Sample Output
Select * from COURSE;
(v) Queries for inserting table IAmarks:
insert into IAmarks values('CS101','10CS81','CS8A','19','20','18','');
insert into IAmarks values('CS102','10CS81','CS8A','16','15','12','');
insert into IAmarks values('CS103','10CS82','CS8B','09','08','12','');
insert into IAmarks values('CS104','10CS83','CS8C','03','05','08','');
insert into IAmarks values('CS105','15CS41','CS4C','10','14','16','');
insert into IAmarks values('CS106','15CS43','CS4C','13','15','20','');
Sample Output
select * from iamarks;
(i)Write SQL queries to list all the student details studying in fourth semester ‘C’ section.
Query: select [Link],sname,gender,address,phone ,[Link],[Link] from
STUDENT s,SEMSEC sc,CLASS c
where [Link]=[Link] and [Link]=[Link] and [Link]=8 and [Link]=’C’;
Output:

(ii)Write SQL queries to compute the total number of male and female students in each
semester and in each section .
Query: select sem,sec,gender,count(*) as count from STUDENT s, SEMSEC sc, CLASS c
where [Link]=[Link] and [Link]=[Link]
group by(sem,sec,gender);
Output:
(iii)Write SQL queries to create a view of Test1 marks of student USN ‘1BI17CS101’ in all
subjects.

Artificial Intelligence and Data Science Engineering AY: 2023-24 Page 5


DBMS LAB with Mini Project(21CSL55) 2023

Query: create view test1 as ( select usn,test1,subcode from iamarks where usn=’CS101’ );
select * from test1;
Output:
(iv)Write SQL queries to calculate the FinalIA (average of best two test marks) and update
the corresponding table for all students.
Query(a): create view average_finder1 as (select usn,subcode,greatest(test1,test2,test3) as
highest, case when test1<greatest(test1,test2,test3) and test1>least(test1,test2,test3)
then test1 when test2<greatest(test1,test2,test3) and test2>least(test1,test2,test3)
then test2 else test3 end as second_highest from iamarks );
Output:
select * from average_finder1;
Query(b): update IAMARKS a set finalia = ( select (highest+second_highest)/2 from
average_finder1 where [Link] =usn and [Link]= subcode);
Output:
select * from iamarks;
(v) Categorize students based on the following criterion:
If FinalIA = 17 to 20 then CAT = ‘Outstanding’
If FinalIA = 12 to 16 then CAT = ‘Average’
If FinalIA< 12 then CAT = ‘Weak’
Give these details only for 8th semester A, B, and C section students.
Query: select [Link],[Link],[Link],[Link],[Link],
(case when [Link] between 17 and 20 then 'outstanding'
when [Link] between 12 and 16 then 'average'
else 'weak' end) as CATEGORY
from student s, semsec ss, iamarks ia, course c
where [Link] = [Link] and [Link] = [Link] and [Link] = [Link] and [Link] = 8;
Output:

Artificial Intelligence and Data Science Engineering AY: 2023-24 Page 6

Common questions

Powered by AI

To calculate and update the FinalIA scores, the following steps are performed: First, create a view using the SELECT statement with the GREATEST and CASE WHEN functions to determine the highest and second highest test scores. The view, named "average_finder1," helps identify these values without altering the original data. Next, update the IAMARKS table using the UPDATE SET statement, where FinalIA is set as the average of the highest and second highest scores for each student. This update directly modifies FinalIA entries in the IAMARKS table .

The computation of the total number of male and female students in each semester and section utilizes SQL aggregate functions, specifically the COUNT function. The SQL query involves a SELECT statement including the COUNT function grouped by the semester, section, and gender fields. Joined tables ensure data integrity across the STUDENT, SEMSEC, and CLASS tables, while the GROUP BY clause facilitates aggregation by distinct combinations of the specified columns .

Challenges in updating FinalIA values may include ensuring concurrency control to prevent data anomalies, maintaining database consistency, and managing performance overheads due to lock contention. These can be mitigated by using transaction isolation levels to control interactions between concurrent operations, employing efficient indexing to accelerate update operations, and using batch processing techniques to minimize lock contention .

Normalizing the database schema is essential to eliminate redundancy, streamline data storage, and enhance query performance. For the College Database, normalization likely involves applying the 1st, 2nd, and 3rd normal forms. The 1st normal form ensures each table has no repeating groups or arrays, the 2nd normal form eliminates partial dependency on non-key attributes, and the 3rd normal form eradicates transitive dependency, ensuring that all data is dependent on only primary keys .

When creating a database schema for a college management system, considerations include ensuring proper entity relationships, implementing primary and foreign keys, and ensuring that cascading actions are defined for data integrity. For example, in the College Database, tables such as STUDENT, SEMSEC, CLASS, COURSE, and IAMARKS must be carefully designed with primary keys (like USN for STUDENT) and foreign keys with ON DELETE actions to maintain relational integrity. Schema must also be normalized to prevent redundancy and ensure scalability .

The ON DELETE CASCADE option in foreign key constraints ensures referential integrity by automatically deleting rows in the child table when corresponding rows in the parent table are deleted. This is significant as it prevents orphan records, maintaining consistency between related tables. In the college database schema, such as the CLASS table, if a student record is deleted from the STUDENT table, any dependent records in the CLASS table are also removed, ensuring the database remains clean and reliable .

The SQL query used to list student details in the fourth semester 'C' section involves a SELECT statement combined with JOIN operations. It selects relevant student details from the STUDENT, SEMSEC, and CLASS tables. The JOIN condition is fulfilled by matching the USN and SSID fields across these tables, ensuring the correct association of students with their semester section information. The query is structured with a WHERE clause that filters data to include only fourth semester and 'C' section students .

The concept of views is applied to handle Test1 marks by creating a query-defined virtual table using the CREATE VIEW statement. This view, named "test1," contains the USN, Test1 mark, and subcode for the student with a specific USN. Views are beneficial in SQL because they provide a way to simplify complex queries, enhance security by restricting access to underlying data, and offer a means to represent data in different perspectives without altering the actual tables .

Students in the 8th semester are categorized into 'Outstanding,' 'Average', or 'Weak' based on their FinalIA scores. If the FinalIA score is between 17 and 20, they are categorized as 'Outstanding.' If it is between 12 and 16, they are 'Average,' and if it is less than 12, they are categorized as 'Weak.' The SQL query uses a CASE WHEN clause to assign the category based on the FinalIA scores .

Effectively inserting data into large tables such as IAMARKS can be achieved through strategies like batching inserts, using prepared statements to reduce parsing overhead, and performing bulk uploads via SQL utilities that leverage batch processing. Indexing key fields can enhance retrieval performance post-insertion. Additionally, ensuring the database is properly tuned and using transaction management techniques like committing transactions at optimal points can further improve insertion efficiency .

You might also like