?????

[lliu26]

1. List the name of different cities where applicants belong to.
2. List the different semesters in which students have enrolled.
3. List the distinct InstructorId and courseid from CourseAllocation table.
4. List the names of courses which are not electives.
5. List the names of instructors who have joined before 01-jan-2002
6. List Id of courses whose ending date is before the current system date.
7. List the names of applicants from Mysore or Bangalore.
8. List the name of applicants who do not belong to Mysore.
9. List the name of semester 1 and semester 2 courses which belong to branch B1.
10. List the name of courses which are electives and project marks is equal to 20.
11. List the name of the courses in which project marks is greater than the semester marks.
12. List the details of all the applicants who belong to Bangalore, Mysore or Hydrabad
13. List the details of courses which are taught in semesters 1, 2 or 3.
14. List the studentId who have secured marks between 80 to 100.
15. List the student details in ascending order of their applicantid.
16. List the details of instructors in ascending order of their date of joining.
17. List the details of instructors in descending order of department no and ascending order of Joining date.
18. List the names of instructors starting with ‘R’ and ending with ‘n’
19. List the details of instructors whose date of joining is in the month of March.
20. List the details of applicant whose email id contains a character ‘_’
21. List the applicant who do not have email id in their applicant detail information.
22. List the course Id which has project Marks in its course detail information.



Assignment 1.
CREATE TABLE Applicant (
ApplicantId VARCHAR2(4) CONSTRAINT Applicant_pk PRIMARY KEY,
ApplicantName VARCHAR2(30) CONSTRAINT Applicant_ApplicantName_nnull NOT NULL,
EmailId VARCHAR2(30) CONSTRAINT Applicant_EmailId_uq UNIQUE,
Address VARCHAR2(50),
City VARCHAR2(15));


CREATE TABLE Branch(
BranchId VARCHAR2(2) CONSTRAINT Branch_pk PRIMARY KEY CONSTRAINT Branch_BranchId CHECK

(BranchId LIKE 'B%'),
BranchName VARCHAR2(30) CONSTRAINT Branch_BranchName_nnull NOT NULL);


CREATE TABLE Course(
CourseId VARCHAR2(4) CONSTRAINT Course_pk PRIMARY KEY,
CourseName VARCHAR2(30) CONSTRAINT Course_CourseName_nnull NOT NULL,
Semester NUMBER(1),
BranchId VARCHAR2(2) CONSTRAINT Course_fkey REFERENCES Branch(BranchId),
Elective CHAR(1) CONSTRAINT Course_Elective CHECK(Elective in('Y','N')),
ProjectMarks NUMBER(3),
AssignmentMarks NUMBER(3),
InternalMarks NUMBER(3),
SemesterExamMarks NUMBER(3));


CREATE TABLE Student(
StudentId VARCHAR2(4) CONSTRAINT Student_pk PRIMARY KEY,
ApplicantId VARCHAR2(4) CONSTRAINT Student_fkey REFERENCES Applicant(ApplicantId),
CurrentSemester NUMBER(1),
UserId VARCHAR2(15) CONSTRAINT Student_UserId_uq UNIQUE,
Password VARCHAR2(15) CONSTRAINT Student_Password_nnull NOT NULL);


CREATE TABLE Registration(
StudentId VARCHAR2(4) CONSTRAINT Registration_fkey1 REFERENCES Student(StudentId),
CourseId VARCHAR2(4) CONSTRAINT RegistratIon_fkey2 REFERENCES Course(CourseId),
DateOfExam DATE,
ProjectMarks NUMBER(3),
AssignmentMarks NUMBER(3),
InternalMarks NUMBER(3),
SemesterMarks NUMBER(3),
Grade CHAR(1));


CREATE TABLE Department(
DepartmentId NUMBER(2) CONSTRAINT Department_pk PRIMARY KEY,
DepartmentName VARCHAR2(30) CONSTRAINT Department_DeptName_nnull NOT NULL,
HeadOfDepartment VARCHAR2(4));


CREATE TABLE Instructor(
InstructorId VARCHAR2(4) CONSTRAINT Instructor_pk PRIMARY KEY CONSTRAINT Instructor_InstructorId

CHECK(InstructorId LIKE 'I%'),
InstructorName VARCHAR2(30) CONSTRAINT Instructor_IsctName_nnull NOT NULL,
DateOfJoining Date,
DepartmentId NUMBER(2) CONSTRAINT Instructor_fkey REFERENCES Department(DepartmentId));


CREATE TABLE CourseAllocation(
AllocationId NUMBER(4) CONSTRAINT Allocation_pk PRIMARY KEY,
CourseId VARCHAR2(4) CONSTRAINT Allocation_fkey1 REFERENCES Course(CourseId),
InstructorId VARCHAR2(4) CONSTRAINT Allocation_fkey2 REFERENCES Instructor(InstructorId),
StartDate Date,
EndDate Date ,
CONSTRAINT Allocation_Date CHECK(EndDate >= StartDate));


CREATE TABLE Hostel(
HostelId VARCHAR2(10),
Roomno NUMBER(3),
StudentId VARCHAR2(4) CONSTRAINT Hostel_fkey REFERENCES Student(StudentId),
HostelFee NUMBER(6) CONSTRAINT Hostel_HostelFee CHECK(HostelFee > 0),
CONSTRAINT Hostel_pk PRIMARY KEY(HostelId,Roomno));


CREATE TABLE Attendance(
StudentId VARCHAR2(4) CONSTRAINT Attendance_fkey1 REFERENCES Student(StudentId),
CourseId VARCHAR2(4) CONSTRAINT Attendance_fkey2 REFERENCES Course(CourseId),
TotalLecturedays NUMBER(3) CONSTRAINT Attendance_TotalLecturedays CHECK(TotalLecturedays > 0),
NoOfDaysOresent NUMBER(3));


Applicant Data:

insert into Applicant values('A001','Raj','[e-mail]','2nd Street,Hebbal','Mysore');
insert into Applicant values('A002','Vadi','[e-mail]','4th Street,Vijayangar','Bangalore');
insert into Applicant values('A003','Sam','[e-mail]','5th Street,Edappalli','Cochin');
insert into Applicant values('A004','Suraj','[e-mail]','1st Main,Rabindra Nagar','Kolkata');
insert into Applicant values('A005','Lakshmi','','2nd Main,Allipuram','Vizag');
insert into Applicant values('A006','Sandra','[e-mail]','3rd Corss,RS Puram','Coimbatore');
insert into Applicant values('A007','Vivek','[e-mail]','5th Main,Coimbed','Chennai');
insert into Applicant values('A008','Vikas','[e-mail]','2nd Main,Jayalakhmipuram','Bangalore');
insert into Applicant values('A009','Bipin','','2nd Cross,Siddarth Nagar','Mysore');
insert into Applicant values('A010','Gopi','[e-mail]','2nd Street,Green Market','Delhi');
insert into Applicant values('A011','Sandy','[e-mail]','9th Cross,Seshadripuram','Bangalore');
insert into Applicant values('A012','Lilly','[e-mail]','9th Main,Banjara Hills','Hyderabad');
insert into Applicant values('A013','Rose','','11th Cross,Kadavanthara','Cochin');
insert into Applicant values('A014','Megha','[e-mail]','10th Lane,Vashi','Mumbai');
insert into Applicant values('A015','Henry','[e-mail]','2nd street,Silk Board','Bangalore');
insert into Applicant values('A016','Joel','[e-mail]','4th street,Vijayanagar','Mysore');


Branch Data:

insert into Branch values('B1','Information Science');
insert into Branch values('B2','Computer Science');
insert into Branch values('B3','Electronics');
insert into Branch values('B4','Electrical');
insert into Branch values('B5','Mechanical');
insert into Branch values('B6','Civil');