practice.sql

-- Lab 03

-- creating a table with some attributes

create table books
	(name varchar(30) primary key,
	ssid varchar(30));


-- add attribute

alter table books add autho varchar(15);


-- delete a table or drop a table

drop table books;


-- insert into table or insert a record into a table

insert into student values ('1029', 'Gurunadh', 'Comp. Sci.', 100);

-- change a record or update a value 

update student set tot_cred = 80 where id = 1029;

-- delete a record

delete from student where id = 1029;

-- conditional query

update student set tot_cred = tot_cred + 10
	where tot_cred < 100 and dept_name = 'Comp. Sci.';

-- conditional query

select id from teaches 
	where (id in (select id from teaches where year = 2009)) and 
			(id not in (select id from teaches where year <> 2009))

-- toppers in each department

select * from student 
	where (dept_name, tot_cred) in 
	(select dept_name, max(tot_cred)
		from student 
		group by dept_name)

-- conditional

select title from course 
where course_id in (select prereq_id from prereq);

-- conditional

select * from instructor 
where id in (select i_id from advisor 
			where s_id in (select id from student where dept_name = 'Comp. Sci.')
			)

-- conditional

select count(id), dept_name, avg(salary)
from instructor 
group by dept_name

-- insert

insert into table student values ('1029', 'Gurunadh', 'Comp. Sci.', 12);

-- between ascending descending

select * from student where tot_cred between 12 and (select max(tot_cred) from student)
order by tot_cred desc;

-- delete a record from table

delete from student where id = 1029;

-- regular expression

select sum(salary) from instructor
where id in (select i_id from advisor) and name regexp '^k' 

-- string operations concatenations

select concat_ws("_", upper(substr(name, 1, 4)), substr(dept_name, 1, 4)) as name_dept
from student

-- regular expression

-- cross join 
-- cross join everything (new nulls are not possible)

-- outer join 
-- join on some condition and display records with null when they dont match
-- (nulls are possible both sides)

-- inner join 
-- join without nulls on some condition

-- left join
-- join with nulls right side or join with all records on left side

-- right join
-- join with nulls in left side or join with all records on right side


select * from course cross join section
where course.dept_name = 'Comp. Sci.' and section.building regexp 'ta|at|ka' and section.course_id regexp 'cs'


-- conditional

select name, salary 
from instructor
where dept_name = 'Biology' or dept_name = 'Finance'
order by salary desc;

-- null check
 select * from instructor where salary is not null;

 -- concatenation

 select name, concat_ws("",upper(substr(name, 1, 1)), lower(substr(name, 2)),lower(name), lower(name))
 from student;


-- 
select distinct student.name, takes.grade, length(name) as length 
from student natural join takes 
where takes.grade in ('A', 'A+', 'A-')

-- Lab 05

select count( distinct student.id )
	from student join 
		takes join 
			teaches join instructor 
			on teaches.id = instructor.id 
		on takes.course_id = teaches.course_id 
	on student.id = takes.id
	where instructor.id = 10101


select count(distinct student.id) from student 
where id in (
	select id from takes
	where course_id in (
		select course_id from teaches
			where id = 10101
		)
	)

select * from instructor where salary > any 
(select salary from instructor)
and dept_name = 'Biology'


select *
from instructor as I
where (select count(id) from instructor as J where I.salary < J.salary) = 1


select * from student
where (dept_name, tot_cred) in 
(select dept_name, max(tot_cred) from student group by dept_name having count(id) > 1)


-- constraints

domain constraints - 		1. inserting a string at the place of an int
							2. insertinga value which disagree with check
entity integr. constr-		1. inserting null at the place of not null  
							2. inserting null at the place of primary key

referential integr. constr 	1. inserting a non existing attribute

key constraints				1. inserting two records with same primary key


-- backup

mysqldump -u root -p --no-data university > mysql_without_data.sql


mysqldump -u root -p  university > mysql_with_data.sql


create database some_with_data
mysql -u root -p  some_with_data < mysql_with_data.sql

create database some_without_data
mysql -u root -p  some_without_data < mysql_without_data.sql


-- drop database

drop database some_with_data;
drop database some_without_data;


-- join
create view student_advisor as(
select student.name as s_name,
instructor.name as i_name
from student left join 
		(advisor join instructor on advisor.i_id = instructor.id)
	on student.id = advisor.s_id
)

-- conditional
select id, name from instructor
where id in
(select id from teaches
group by id
having count(course_id) > 1)


select host, user from mysql.user

create user 'guru'@localhost;

grant all on *.* to 'guru'@localhost with grant option;