Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- create table student (regno varchar(15), name varchar(20), major varchar(20), bdate date, primary key (regno));
- insert into student (regno, name, major, bdate) values
- ('Inh21ai001', 'a', 'sr', '19930120'),('Inh21ai002', 'b', 'sr', '19930924'),
- ('Inh21ai003', 'c', 'sr', '19931127'),('Inh21ai004', 'd', 'sr', '19930413'),('Inh21ai005', 'e', 'isr', '19940824');
- create table course (courseno int, cname varchar(20), dept varchar(20), primary key (courseno));
- insert into course values (111, 'DS', 'CSE'),(112, 'EC', 'CSE'),
- (113, 'SS', 'ISE'),(114, 'DBMS', 'CSE'),(115, 'SSANNIS', 'ECE'),
- (121, 'OS', 'AIML'),(123, 'SS', 'AIML'),(124, 'DBMS', 'AIML');
- create table text (book_isbn varchar(20), book_title varchar(20), publisher varchar(20), author varchar(20), primary key (book_isbn));
- insert into text (book_isbn, book_title, publisher, author) values
- ('900', 'Database Systems', 'Pearson', 'Schield'),
- ('901', 'Operating Sys', 'Pearson', 'Leland'),
- ('902', 'Circuits', 'Hall India', 'Bob'),
- ('903', 'System Software', 'Peterson', 'Jacob'),
- ('904', 'Scheduling', 'Pearson', 'Patil'),
- ('905', 'Database Systems', 'Pearson', 'Jacob'),
- ('906', 'Database Manager', 'Pearson', 'Bob'),
- ('907', 'Signali', 'Hall India', 'Sumit');
- create table enroll (regno varchar(15), courseno int, sem int, marks int, primary key (regno, courseno), foreign key (regno) references student (regno), foreign key (courseno) references course (courseno));
- insert into enroll (regno, courseno, sem, marks) values
- ('Inh21ai001', 114, 3, 100),('Inh21ai002', 115, 3, 100),
- ('Inh21ai003', 113, 5, 100),('Inh21ai004', 111, 5, 100),('Inh21ai005', 112, 3, 100);
- create table book_adoption (courseno int, sem int(3), book_isbn varchar(20), primary key (courseno, sem, book_isbn), foreign key (courseno) references course (courseno), foreign key (book_isbn) references text (book_isbn));
- insert into book_adoption (courseno, sem, book_isbn) values
- (111, 5, '900'),(111, 3, '903'),(114, 5, '904'),(113, 5, '902'),
- (115, 3, '906'),(121, 5, '903'),(121, 5, '904'),(121, 5, '905');
- select c.courseno, t.book_isbn, t.book_title from course c, book_adoption ba, text t where c.courseno = ba.courseno and ba.book_isbn = t.book_isbn and c.dept = 'AIML' and 2 <= (select count(book_isbn) from book_adoption b where c.courseno = b.courseno) order by t.book_title;
- select distinct c.dept from course c where c.dept in (select c.dept from course c, book_adoption b, text t where c.courseno = b.courseno and b.book_isbn = t.book_isbn and t.publisher = 'Pearson') and c.dept not in (select c.dept from course c, book_adoption b, text t where c.courseno = b.courseno and b.book_isbn = t.book_isbn and t.publisher != 'Pearson');
- Output:
- +----------+-----------+------------------+
- | courseno | book_isbn | book_title |
- +----------+-----------+------------------+
- | 121 | 905 | Database Systems |
- | 121 | 904 | Scheduling |
- | 121 | 903 | System Software |
- +----------+-----------+------------------+
- +------+
- | dept |
- +------+
- | ECE |
- +------+
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
