Scribd
Upload
27 views5 pages

CB, En, U4cse19639 Dbms Lab3

The document outlines the creation of a database schema for a student enrollment system, including tables for students, enrolled courses, and subjects. It also includes several SQL queries to retrieve information such as student names based on course enrollment and lecturer details. The queries demonstrate various join operations to extract relevant data from the defined tables.

Uploaded by

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

CB, En, U4cse19639 Dbms Lab3

The document outlines the creation of a database schema for a student enrollment system, including tables for students, enrolled courses, and subjects. It also includes several SQL queries to retrieve information such as student names based on course enrollment and lecturer details. The queries demonstrate various join operations to extract relevant data from the defined tables.

Uploaded by

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

[Link].u4cse19639 N.

PRABHAT DATE:21-08-2020

create table student(


id varchar2(5),
name varchar2(20),
PRIMARY KEY (id));
create table enrolledin(
id varchar2(5),
code varchar2(10),
primary key(code),
CONSTRAINT enro foreign key(id) references student(id));
create table subject(
code varchar2(10),
lecturer varchar2(20),
CONSTRAINT sub foreign key(code) references enrolledin(code));
insert into student(id,name) values('1234','joe');
insert into student(id,name) values('4000','hector');
insert into student(id,name) values('2000','ling');
insert into enrolledin(id,code) values('1234','cs1500');
insert into enrolledin(id,code) values('1234','cs1200');
insert into enrolledin(id,code) values('1234','cs2001');
insert into enrolledin(id,code) values('4000','cs3010');
insert into enrolledin(id,code) values('4000','ma3000');
insert into subject(code,lecturer) values('cs1500','curtis');
insert into subject(code,lecturer) values('cs2001','dave');
insert into subject(code,lecturer) values('cs3010','curtis');
insert into subject(code,lecturer) values('cs2001','olivier');
insert into subject(code,lecturer) values('ma3000','roger');
[Link].u4cse19639 [Link] DATE:21-08-2020

Queries:
1)select name from student
join enrolledIn
on [Link] = [Link] and [Link] = 'cs3010';

2)
select code from student join enrolledIn on [Link] = [Link] and
[Link]='hector';
[Link].u4cse19639 [Link] DATE:21-08-2020

3) select lecturer from subject where code = 'cs1500';

4)
select (lecturer) from subject where code = 'cs1500' or code = 'cs3010';

5)
select (name) from student join enrolledIn on [Link] = [Link] and
[Link] = 'cs1200' and [Link] ='cs3010';
[Link].u4cse19639 [Link] DATE:21-08-2020

10)

select (name) from student


join enrolledIn ON [Link] = [Link]
join subject ON [Link] = [Link] and [Link] = 'roger';

11)select (name) from student


join enrolledIn ON [Link] = [Link]
[Link].u4cse19639 [Link] DATE:21-08-2020

join subject ON [Link] = [Link] and [Link] != 'roger';

9)
select name from student join enrolledIn
on [Link] = [Link] and [Link] = 'cs1500';

You might also like