create database demo


create schema student_info;


create table student_info.student(roll int primary key, name varchar(20), course_id int );

create table course(course_id int primary key, course_name varchar(20) not null)



alter table student 
add constraint fk_student foreign key(course_id) 
references course(course_id)
on delete cascade


insert into course values(101, 'rdbms')
insert into course values(102, 'wpl')

insert into course(course_id) values(104)

insert into student values(1, 'arvin', 101)
insert into student values(2, '' , 102)


delete from course where course_id = 101


check whether tuple deleted from student or not due to on delete cascade effect


select count(roll) from student


select sum(roll) from student

// with alternate name to result set

select sum(roll) as summation from student

select count(distinct course_id) from student


// group by 
select count(distinct course_id) from student

select count(roll), course_id from student group by course_id
having course_id > 101

select count(roll), course_id from student group by course_id
having count(roll) > 1

// course that is not taken by student

Select * from course 
Where not exists
(select * from student where student.course_id = course.course_id)



Subqueries and Co-related queries


-- select students who learn css

select roll from student s where s.course_id IN
(select course_id from course where cname = 'css')

-- select students who dont learn css

select roll from student s where s.course_id NOT IN
(select course_id from course where cname = 'css')

-- select course for which students exist
select * from course where exists
(select course_id from student where student.course_id = course.course_id)

-- select course for which students dont exist

select * from course where not exists
(select course_id from student where student.course_id = course.course_id)

-- select students who have taken courses

SELECT * FROM STUDENT s WHERE course_id IN 
(SELECT course_id FROM course c WHERE s.course_id = c.course_id);

select count(roll), course_id from student
group by course_id
having count(roll) > 1

-- copying data from original table to another

INSERT INTO course_bkp
   SELECT * FROM course 
   WHERE course_id IN (SELECT course_id 
   FROM course) ;

-- copying data from course and only those entries for which students have opted

INSERT INTO course_bkp
   SELECT * FROM course 
   WHERE course_id IN (SELECT course_id 
   FROM student) ;


-- joins
-- inner joins

SELECT student.roll, course.course_id, course.cname FROM student
INNER JOIN course ON student.course_id = course.course_id 

insert into student values(103,'lmn')

SELECT student.roll, student.name, course.course_id, course.cname FROM student
LEFT OUTER JOIN course ON student.course_id = course.course_id 

SELECT student.roll, student.name, course.course_id, course.cname FROM student
RIGHT OUTER JOIN course ON student.course_id = course.course_id 

SELECT student.roll, student.name, course.course_id, course.cname FROM student
RIGHT OUTER JOIN course ON student.course_id = course.course_id 


SELECT * FROM student NATURAL JOIN course


-- cross join - each row with every other row

SELECT * FROM student CROSS JOIN course


-- cross join with where clause- acts as inner join

SELECT * FROM student CROSS JOIN course
where student.course_id = course.course_id

-- view - create and modify

create or replace view student_info as
select name from student

-- display view
select * from student_info


-- PostgreSQL (true up to at least 9.4) doesn't currently support 
removing a column with CREATE OR REPLACE VIEW.

/*The new query must generate the same columns that were generated by the existing 
view query (that is, the same column names in the same order and with the same data types), 
but it may add additional columns to the end of the list.
*/


create or replace view student_info as
select roll, name from student

-- adding data to view, gets added to actual table student 

insert into student_info(roll,name)values(105,'jkl');

-- allows to add null values
insert into student_info(roll,name)values(106,'');


-- view with check option is on
create or replace view student_info as
select roll, name from student
where name is NOT NULL
with check option


-- add data to view 

insert into student_info(roll)values(110);

or

insert into student_info values(110,null);


-- throw an error
not allowed to add null values 



-- assert keyword

do $$
declare  
 employee_count integer;
begin
 select count(*)
 into employee_count
 from employees;
 assert employee_count > 0, 'Check for Empty Employee Table';
end$$;