Scribd
Upload
10 views2 pages

Dbms STD

The document outlines the creation of a database named 'students2' with tables for students, teachers, subjects, and grades. It includes SQL commands for inserting data into these tables and various select queries to retrieve specific information. The queries demonstrate how to filter students and teachers based on grades and subjects taught.

Uploaded by

gangareddy368
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
10 views2 pages

Dbms STD

The document outlines the creation of a database named 'students2' with tables for students, teachers, subjects, and grades. It includes SQL commands for inserting data into these tables and various select queries to retrieve specific information. The queries demonstrate how to filter students and teachers based on grades and subjects taught.

Uploaded by

gangareddy368
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

create database students2;

use students2;

CREATE TABLE students1(sid int not null, name text not null,primary key(sid));

CREATE TABLE teachers1(tid int not null,name text not null, primary key(tid));

CREATE TABLE subjects1(subid int not null,name text not null,primary key(subid));

CREATE TABLE grades(

studentID int not null references students1(sid),

teacherID int not null references teachers1(tid),

subjectID int not null references subjects1(subid),

grade varchar(3),

primary key(studentID, teacherID, subjectID)

);

INSERT INTO students1 (sid, name) VALUES(1, 'Simon');

INSERT INTO students1 (sid, name) VALUES(2, 'Alvin');

INSERT INTO students1 (sid, name) VALUES(3, 'Theo');

INSERT INTO students1 (sid, name) VALUES(4, 'Brittany');

INSERT INTO students1 (sid, name) VALUES(5, 'Jenette');

INSERT INTO students1 (sid, name) VALUES(6, 'Elenor');

INSERT INTO students1 (sid, name) VALUES(7, 'Stu');

select * from Students1;

INSERT INTO teachers1 (tid, name) VALUES (1, 'Washington');

INSERT INTO teachers1 (tid, name) VALUES (2, 'Adams');

INSERT INTO teachers1 (tid, name) VALUES (3, 'Jefferson');

INSERT INTO teachers1 (tid, name) VALUES (4, 'Lincoln');


select * from teachers1;

INSERT INTO subjects1 (subid, name) VALUES (1, 'History');

INSERT INTO subjects1 (subid, name) VALUES (2, 'dbms');

INSERT INTO subjects1 (subid, name) VALUES (3, 'SF');

select * from subjects1;

INSERT INTO grades (studentID, teacherID, subjectID, grade) VALUES (1, 2, 1, 'A');

INSERT INTO grades (studentID, teacherID, subjectID, grade) VALUES (1, 2, 2, 'B');

INSERT INTO grades (studentID, teacherID, subjectID, grade) VALUES (7, 4, 3, 'C+');

INSERT INTO grades (studentID, teacherID, subjectID, grade) VALUES (7, 3, 2, 'F');

INSERT INTO grades (studentID, teacherID, subjectID, grade) VALUES (6, 2, 1, 'B+');

INSERT INTO grades (studentID, teacherID, subjectID, grade) VALUES (2, 4, 3, 'C');

INSERT INTO grades (studentID, teacherID, subjectID, grade) VALUES (3, 4, 3, 'B');

--names--

select* from students1 order by name;

--adams--

select * from students1 where sid in(select distinct studentid from grades where teacherid=(select
tid from teachers1 where name="adams"));

--dbms--

select * from teachers1 where tid in(select teacherid from grades where subjectid=(select subid
from subjects1 where name="dbms"));

--not taught teacher--

select * from teachers1 where tid not in(select distinct teacherid from grades);

select *from teachers1 t where exists(select *from grades g where t.tid=g.teacherid);

--no student--

select * from students1 where sid not in(select studentid from grades);

You might also like