DBMS Assignment 1

create table branch(branch_name varchar(40) primary key,
                    branch_city varchar(40),
                    assets int
                );

create table account(account_number int primary key,
                    balance int,
                    branch_name varchar(40),
                    foreign key(branch_name) references branch    
                );

create table customer(customer_name varchar(40) primary key,
                    customer_street varchar(40),
                    customer_city varchar(40)
                );

create table depositor(customer_name varchar(40),
                    account_number int,
                    foreign key(customer_name) references customer,
                    foreign key(account_number) references account
                );

create table loan(loan_number int primary key,
                branch_name varchar(40),
                amount int,
                foreign key(branch_name) references branch
            );

create table borrower(customer_name varchar(40),
                    loan_number int,
                    foreign key(customer_name) references customer,
                    foreign key(loan_number) references loan
                );

alter table branch modify branch_name varchar(50);
alter table account modify branch_name varchar(50);
alter table loan modify branch_name varchar(50);

alter table account add check (balance > 0);

alter table loan add loan_taken_date date default null;

alter table loan modify loan_taken_date default sysdate;

select constraint_name, constraint_type, search_condition from user_constraints where table_name='BORROWER';
alter table borrower drop constraint SYS_C0050963531;

alter table borrower rename column loan_number to loan_id;

alter table customer add aadhar_id int unique;

alter table borrower modify loan_id int not null;

There is no primary key constraint in the borrower table

alter table account rename to bank_account;

select customer_name from customer 
    where customer.customer_city = (
        select branch_city from  branch 
        where branch_name = (
            select branch_name from bank_account
            where account_number = (
                select account_number from depositor
                where customer_name = customer.customer_name
                )
            )
        );

select * from customer 
    where customer_name not in (
        select distinct(customer_name) from borrower
    ); 

select * from customer 
    where customer_name in (
        select customer_name from borrower 
        group by customer_name having count(customer_name) > 3
    ); 

select c1.customer_name, c2.customer_name from customer c1, customer c2 
    where c1.customer_city = c2.customer_city and c1.customer_name <> c2.customer_name; 

select * from customer 
    where length(customer_name) = (select max(length(customer_name)) from customer);

select * from borrower
    where loan_id in (
        select loan_number from loan 
        where branch_name in (
            select branch_name from branch
            where branch_city = 'ahmedabad'
        )
    );

select * from borrower
    where loan_id in (
        select loan_number from loan
        where amount > 5000000
    );

select sum(assets) from branch;

select * from customer 
    where customer_name in (
        select customer_name from depositor
    );

select customer.customer_name, customer.customer_street, depositor.account_number 
    from customer left outer join depositor 
    on customer.customer_name = depositor.customer_name; 

select depositor.customer_name, bank_account.account_number, bank_account.balance
    from bank_account right outer join depositor
    on depositor.account_number = bank_account.account_number;

select account_number from bank_account
    where balance = (
        select min(balance) from bank_account
    );

select account_number from bank_account 
    where balance = (
        select max(balance) from bank_account 
            where balance < (
                select max(balance) from bank_account
            )
    );

select branch.branch_name, branch_city, account_number, customer_name, customer_city 
    from branch, bank_account, customer 
    order by customer_city asc, branch_city desc;

select customer_city, count(*) from customer group by customer_city;

select branch_city, sum(assets) from branch group by branch_city order by sum(assets) desc;

select distinct customer_name, sum(amount)/count(customer_name) from 
     loan right outer join borrower on borrower.loan_id = loan.loan_number group by (customer_name); 

select * from customer 
    where customer_name in (
        select distinct (customer_name) from borrower 
            where customer_name in (
                select customer_name from depositor
            )
    );

select * from customer 
    inner join borrower on customer.customer_name = borrower.customer_name
    inner join loan on loan.loan_number = borrower.loan_id 
    inner join branch on loan.branch_name = branch.branch_name;

select branch_name from branch 
    union 
        select branch_name from bank_account; 

create table department(dept_name varchar(40),
                        building varchar(40),
                        budget int,
                        constraint department_PK_dept_name primary key(dept_name)
                    );

create table student(ID varchar(40),
                    name varchar(40),
                    dept_name varchar(40),
                    tot_cred float,
                    constraint student_PK_ID primary key(ID),
                    constraint student_FK_dept_name foreign key (dept_name) references department
                );                    

create table course(course_id varchar(40),
                    title varchar(40),
                    dept_name varchar(40),
                    credits float,
                    constraint course_PK_course_id primary key (course_id),
                    constraint course_FK_dept_name foreign key (dept_name) references department
                );

create table instructor(ID varchar(40),
                        name varchar(40),
                        dept_name varchar(40),
                        salary int,
                        constraint instructor_PK_ID primary key (ID),
                        constraint instructor_FK_dept_name foreign key (dept_name) references department
                    );

create table advisor(s_id varchar(40),
                    i_id varchar(40),
                    constraint advisor_PK_s_id primary key (s_id),
                    constraint advisor_FK_s_id foreign key (s_id) references student,
                    constraint advisor_FK_i_id foreign key (i_id) references instructor
                );

create table prereq(course_id varchar(40),
                    prereq_id varchar(40),
                    constraint prereq_CK primary key(course_id, prereq_id),
                    constraint prereq_FK_course_id foreign key (course_id) references course,
                    constraint prereq_FK_prereq_id foreign key (prereq_id) references course
                );

create table classroom(building varchar(40),
                    room_no varchar(40),
                    capacity int,
                    constraint classroom_CK primary key (building, room_no)
                );

create table time_slot(time_slot_id varchar(40),
                    day varchar(40),
                    start_time timestamp,
                    end_time timestamp,
                    constraint time_slot_PK_time_slot_id primary key(time_slot_id)
                );

create table section(course_id varchar(40),
                    sec_id varchar(40),
                    semester int,
                    year int,
                    building varchar(40),
                    room_no varchar(40),
                    time_slot_id varchar(40),
                    constraint section_CK primary key(course_id, sec_id, semester, year),
                    constraint section_CFK_classroom foreign key(building, room_no) references classroom,
                    constraint section_FK_time_slot_it foreign key(time_slot_id) references time_slot
                );

create table teaches(ID varchar(40),
                    course_id varchar(40),
                    sec_id varchar(40),
                    semester int,
                    year int,
                    constraint teaches_FK_ID foreign key(ID) references instructor,
                    constraint teaches_CFK_section foreign key(course_id, sec_id, semester, year) references section,
                    constraint teaches_CK primary key(ID, course_id, sec_id, semester, year)
                );

create table takes(ID varchar(40),
                course_id varchar(40),
                sec_id varchar(40),
                semester int,
                year int,
                grade varchar(40),
                constraint takes_FK_ID foreign key(ID) references student,
                constraint takes_CFK_section foreign key(course_id, sec_id, semester, year) references section,
                constraint takes_CK primary key(ID, course_id, sec_id, semester, year)
            );
                

alter table course add check (credits >= 1.5 and credits <= 4.5);

alter table department add check (budget <= 10000);

alter table instructor add check (salary > 0);

alter table course modify credits default 1.5;

alter table classroom modify capacity default 120;

alter table section modify year default extract(year from sysdate);

alter table student add constraint student_UNIQUE_dept_name unique(dept_name);

alter table instructor add constraint instructor_UNIQUE_dept_name unique(dept_name);

alter table classroom modify capacity not null;

alter table teaches modify year not null;

insert into course values('CS-001', 'Weekly Seminar', 'Computer Science', 2);

insert into section values('CS-001', '1', 'Autumn', 2009, null, null, null);

insert into takes
    select ID, 'CS-001', '1', 'Autumn', 2009, null
    from student
    where dept_name = 'Computer Science';

delete from takes
    where sec_id = '1' and ID in (
        select ID from student where name='Chiral'
    );

delete from takes 
    where course_id in (
        select course_id from course where lower(title) like '%database'
    );

alter table prereq drop constraint prereq_FK_course_id;
alter table prereq drop constraint prereq_FK_prereq_id;

alter table prereq drop constraint prereq_CK;

alter table student rename column name to std_name;

alter table instructor drop constraint instructor_UNIQUE_dept_name;

select constraint_name, constraint_type, search_condition from user_constraints where table_name='CLASSROOM';
alter table classroom drop constraint SYS_C0050977229;