create table Student
	(
		regno varchar(20) primary key not null,
		name varchar(20),
		major varchar(20),
	    bdate date
    );

create table Course
	(
		cno int primary key not null,
		cname varchar(20),
		dept varchar(20)
	);

create table Enroll
	(
		regno varchar(20),
		cno int,
		sem int,
		marks int,
		primary key(regno,cno),
		foreign key (cno) references Course(cno),
		foreign key (regno) references Student(regno)
	);

create table Text
	(
		book_isbn int primary key not null,
		book_title varchar(20),
		publisher varchar(20),
		author varchar(20)
	);

create table Book_Adoption
	(
		cno int,
		sem int,
		book_isbn int,
		primary key(cno,book_isbn),
		foreign key (cno) references Course(cno),
		foreign key (book_isbn) references Text(book_isbn)
	);
	2: Produce a list of text books (include Course#, Book-ISBN, Book-title) in the alphabetical order for
courses offered by the  ̳CS‘ department that use more than two books.


mysql>select c.cno, t.book_isbn, t.book_title
	from course c, book_adoption b, text t
	where c.cno=b.cno and t.book_isbn=b.book_isbn and c.dept='cs'
	and c.cno in ( select cno
	from book_adoption
	group by cno
	having count(*)>2)
	order by c.cno, t.book_title;

3: List any department that has all its adopted books published by a specific publisher.p


mysql>select distinct c.dept
	from course c
	where c.dept in
	( select c.dept
	from course c,book_adoption b,text t
	where c.cno=b.cno
	and t.book_isbn=b.book_isbn
	and t.publisher='sapna')
	and c.dept not in
	(select c.dept
	from course c,book_adoption b,text t
	where c.cno=b.cno
	and t.book_isbn=b.book_isbn
	and t.publisher!='sapna');
insert into Student values("1jt14cs003","abhinav","cs",'1996-06-11');
insert into Student values("1jt14ae004","abhiram","ae",'1996-05-03');
insert into Student values("1jt14ec046","ajay","cs",'1995-02-26');
insert into Student values("1jt14cvo38","anirudh","civ",'1997-12-22');
insert into Student values("1jt14me056","sharad","me",'1997-10-30');


insert into Course values(1,"networks","ec");
insert into Course values(2,"simulation","ae");
insert into Course values(3,"survey","civ");
insert into Course values(4,"coding","cs");
insert into Course values(5,"hydraulics","me");
insert into Course values(6,"programing","cs");
insert into Course values(7,"data structures","cs");

insert into Enroll values("1jt14ec046",6,1,50);
insert into Enroll values("1jt14ae004",3,3,89);
insert into Enroll values("1jt14cs003",1,3,45);
insert into Enroll values("1jt14cvo38",2,7,73);
insert into Enroll values("1jt14me056",4,5,90);

insert into Text values(111,"comp net","tmg","garcia");
insert into Text values(222,"algo des","pearson","levitin");
insert into Text values(333,"automata","pearson","yash");
insert into Text values(444,"data str","sapna","tbaum");
insert into Text values(555,"algo2","pearson","levitin");
insert into Text values(777,"networks","pearson","garcia");

insert into Book_Adoption values(1,5,111);
insert into Book_Adoption values(6,3,777);
insert into Book_Adoption values(2,1,555);
insert into Book_Adoption values(4,7,333);
insert into Book_Adoption values(3,5,222);
insert into Book_Adoption values(4,7,111);
insert into Book_Adoption values(6,3,444);
insert into Book_Adoption values(7,5,555);





<?php
$servername ="localhost";
$username="root";
$password="jit123";
$dbname="db4";

$conn= new mysqli($servername,$username,$password,$dbname);

if($conn->connection_error)
    {
        die("CONN failed :" . $conn->connect_error);
    }
else
    {
        $sqlquery="select * from author";
        $result = $conn->query($sqlquery);
        if($result->num_rows>0)
        {echo "<table align=\"center\"border=1> <tr> <th> authorid </th> <th> name</th><th>city</th><th>country</th></tr>";
            while($row=$result->fetch_assoc())
            {

                echo "<tr><td>$row[authorid]</td><td>$row[name]</td><td>$row[city]</td><td>$row[country]</td></tr>";

            }
            echo "</table>";
        }
        else echo "empty set";

    }


$conn->close();
?>