GE PRACTICAL 2 COMPUTER DEPARTMENT DATABASE
Create a database having three tables to store the details of students of computer department
in your college, as per the given schema.
PERSONAL INFORMATION ABOUT STUDENT (college roll number,, Name of student, Date of
birth, Address, Marks(rounded off to whole number ) in percentage at 10+2, Phone number)
PAPER DETAILS (Paper code, Name of the paper
paper)
STUDENT’S ACADEMIC AND ATTENDENCE DETAILS ((College
College roll number, Paper Code,
Attendence, Marks in home examination).
A] Identify primary and foreign keys. Create the tables and insert at least 5 records in each
table.
CREATE DATABASE COMPUTERDEP;
SHOW DATABASES;
USE computerdep;
SHOW TABLES;
CREATE TABLE STUDENT
(Rollno VARCHAR(10) NOT NULL,
Name VARCHAR(30) NOT NULL,
BirthDate DATE NOT NULL,
Address VARCHAR(60) NOT NULL,
MarksPer INT NOT NULL,
Phn VARCHAR(10),
PRIMARY KEY (Rollno));
SHOW TABLES;
DESCRIBE STUDENT;
CREATE TABLE PaperDetails
(PaperCode INT NOT NULL,
PaperName VARCHAR(45) NOT NULL,
PRIMARY KEY (PaperCode));
SHOW TABLES;
DESCRIBE PaperDetails;
CREATE TABLE StudentDetails
(Rollno
Rollno VARCHAR(10) NOT NULL,
PaperCode INT NOT NULL,
Attendence INT NOT NULL,
MarksH INT,
PRIMARY KEY (Rollno, PaperCode),
FOREIGN KEY (Rollno) REFERENCES STUDENT(Rollno),
FOREIGN KEY (PaperCode) REFERENCES PaperDetails(PaperCode));
SHOW TABLES;
DESCRIBE StudentDetails;
INSERT INTO Student
VALUES('CSC/20/4', 'Sumit', '2002
2002-08-06',
06', 'Ashok vihar New Delhi', 89.6, 9997912056),
('CSC/20/8', 'Shiv', '2001-02-21',
21', 'New Market Gokulpur Noida', 75.5, 8006694954),
('CSC/20/12', 'Kartik', '2002-02-12',
12', 'Manglapuri Mandi Meerut', 94.3, 8215336964),
('CSC/20/14', 'Somya', '2000-06--15', 'Sansad Marg Delhi', 85, 9658742536),
('CSC/20/15', 'Simran', '2001-05--16', 'Mandoli Road Delhi', 92.2, 8633909152),
('CSC/20/16', 'Karan', '2001-03-10',
10', 'Gautam nagar Noida', 80, 9720091238);
SELECT * FROM Student;
INSERT INTO PaperDetails
VALUES(32345201, 'Inroduction to Database Systems'),
(32341102, 'Computer system architecture'), (32341101, 'Programming Fundamentals using
C++'),
(32345104, 'Programming using Python'),
(32346102, 'Discrete Structures');
SELECT * FROM PaperDetails;
INSERT INTO StudentDetails
VALUES('CSC/20/4',32345201, 50, 65),
('CSC/20/8', 32341102, 78, 80),
('CSC/20/12', 32341101, 70, 74),
('CSC/20/14', 32345104, 85, 90),
('CSC/20/15', 32341102, 82, 91),
('CSC/20/16', 32341102, 72, 84),
('CSC/20/12', 32345104, 50, 80),
('CSC/20/8', 32345201, 70,83);
SELECT * FROM StudentDetails;
B] Design a query that will return the records (from the second table) along with the name of
student from the first table, related to students who have more than 75% attendance and
more thann 60% marks in 'Computer system architecture'
SELECT Name, P.PaperCode, P.PaperName
FROM STUDENT AS S, PaperDetails AS P, StudentDetails AS SD
WHERE S.Rollno = SD.Rollno AND P.PaperCode = SD.PaperCode AND Attendence>75 AND
MarksH>60 AND PaperName = 'Computer system architecture'
architecture';
C] List all students who live in “Delhi” and have marks greater than60 in 'Programming
Fundamentals using C++'
SELECT Name
FROM STUDENT AS S, PaperDetails AS P, StudentDetails AS SD
WHERE S.Rollno = SD.Rollno AND P.Pap
P.PaperCode
erCode = SD.PaperCode AND Address LIKE '%Delhi%'
AND PaperName = 'Programming Fundamentals using C++' AND MarksH>60
MarksH>60;
mysql> SELECT Name
->> FROM STUDENT AS S, PaperDetails AS P, StudentDetails AS SD
->> WHERE S.Rollno = SD.Rollno AND P.PaperCode = SD.PaperCode AND Address
LIKE '%Delhi%' AND PaperName = 'Programming Fundamentals using C++' AND
MarksH>60
-> ;
Empty set (0.04 sec)
------to obtain some data – update
UPDATE STUDENTDETAILS
SET PAPERCODE = 32341102
WHERE ROLLNO = 'CSC/20/14' AND PAPER
PAPERCODE = 32345104;
select * from studentdetails;
SELECT Name
FROM STUDENT AS S, PaperDetails AS P, StudentDetails AS SD
WHERE S.Rollno = SD.Rollno AND P.PaperCode = SD.PaperCode AND Address LIKE '%Delhi%'
AND PaperName = 'Programming Fundamentals using C++' AND MarksH>60
UPDATE STUDENTDETAILS
SET PAPERCODE = 32341101
WHERE ROLLNO = 'CSC/20/14' AND PAPERCODE = 32341102;
SELECT Name
FROM STUDENT AS S, PaperDetails AS P, StudentDetails AS SD
WHERE S.Rollno = SD.Rollno
lno AND P.PaperCode = SD.PaperCode AND Address LIKE '%Delhi%'
AND PaperName = 'Programming Fundamentals using C++' AND MarksH>60
E] List the name of student who has got the highest marks in Computer System Architecture.
SELECT Max(MARKSH), Name
FROM STUDENT AS S, PaperDetails AS P, StudentDetails AS SD
WHERE S.Rollno = SD.Rollno AND P.PaperCode = SD.PaperCode AND P.PaperName = 'Computer
system architecture'
