CREATE TABLE Profession(
ProfNo VARCHAR(20),
ProfName VARCHAR(25))
;
INSERT INTO Profession(ProfNo, ProfName)
VALUES("com1","Programm Hangamj"),
("com2","Medeelliin technology"),
("mat1","Heregleenii matematik"),
("ouh1","Niitiin udirdlaga"),
("ouh2","Olon ulsiin hariltsaa");
CREATE TABLE Student(
StudNo VARCHAR(3),
ProfNo VARCHAR(25),
fName Varchar (20),
lName Varchar (20),
sex varchar (1),
birthdate date ,
elsOn int);
INSERT INTO Student(StudNo, ProfNo, fName, lName, sex,birthdate, elsOn)
VALUES("h1","com1","Bat","Bold","M","1999-3-5",2016),
("h2","com1","Dandar","Tulga","M","1998-4-14",2016),
("h3","com2","Ish","Od","M","2000-4-1",2017),
("h4","com2","Tsetseg","Erhes","F","1999-3-23",2017),
("h5","mat1","Dulam","Od","F","2000-7-15",2017),
("h6","mat1","Ohin","Huu","M","1999-1-9",2016),
("o1","ouh1","Enebish","Terbish","M","1999-7-30",2017),
("o2","ouh2","Sod","Bud","F","2001-10-5",2018)
;
Create table Lesson (
LesNo Varchar(5),
Ner varchar(30),
Tailbar varchar(20));
insert into Lesson (LesNo, Ner,Tailbar)
Values ("OS1","Ogogdliin sangiin undes",null),
("AL1","Algorithm",null),
("OS2","Ogogdliin san",null),
("MA2","Heregleenii mate",null),
("MA1","Mate",null),
("MA3","Engineeriin mate",null),
("UL1","Uls tor",null),
("T1","Tuuh",null);
Create table LessonRelation (
ProfNo Varchar(5),
LesNo Varchar(5));
insert into LessonRelation(ProfNo,LesNo)
Values ("com1","AL1"),
("com1","OS1"),
("com2","AL1"),
("com2","OS1"),
("com2","OS2"),
("mat1","MA1"),
("mat1","MA2"),
("mat1","MA3"),
("ouh1","UL1"),
("ouh2","UL1");
Create table ChosenLesson (
StudNo Varchar(5),
LesNo varchar (5),
On1 int ,
uliral varchar(20),
O1 int check (phone_number between 0 and 60),
O2 int check (phone_number between 0 and 40),
O3 int );
insert into ChosenLesson (StudNo,LesNo,On1,uliral,O1,O2,O3)
values ("h6","MA1",2016,"namar",54,20,74),
("h1","T1",2016,"namar",59,38,97),
("h5","MA3",2017,"havar",54,10,64),
("h4","Al1",2017,"namar",50,7,57),
("h4","MA1",2017,"havar",30,30,60),
("h1","OS1",2016,"havar",25,5,30),
("o1","UL1",2017,"namar",54,26,80),
("o2","T1",2018,"havar",50,39,89);
1.
Select ProfName, count(*)
from lessonrelation l
join profession le on [Link]=[Link]
group by [Link]
2. select ProfName, elsOn, count(*)
from student s
join profession p on [Link]=[Link]
group by [Link], elsOn
3.
SELECT*
FROM lesson
WHERE Lesno IN (SELECT lesno
FROM lessonrelation
WHERE profno IN (SELECT profno
FROM student
WHERE fname="Bat"))
4. select lesson.*
from lesson
where LesNo in (select LesNo
from chosenlesson
where StudNo= (select StudNO
from student
where fName like "Bat"))
5. select fName, AVG(O3) as dundajdun
from chosenlesson c
join student s on [Link]=[Link]
where fName like "Bat" and uliral like "namar" and On1=2016
1. select fName,count(*) as too, AVG(O3) As dundajdun
from chosenlesson c
join student s on [Link]=[Link]
group by fName
2. select Ner, Max(O3),Min(O3),Avg(O3)
from chosenlesson c
join lesson l on [Link]=[Link]
group by Ner
3. select distinct student.*
from student
where studNo in (select studNo
from chosenlesson
where O3<60)
4. SELECT lesson.*
FROM lesson
join chosenlesson c on [Link]=[Link]
WHERE [Link] IN( SELECT lesno
FROM chosenlesson
WHERE studno=(SELECT DISTINCT studno
FROM chosenlesson
ORDER BY o3 DESC
LIMIT 1)) and c.on1=2016 and [Link]="namar"
5. delete from chosenlesson
where O1 is null or O2 is null
6. select ner, count(*) as too
from chosenlesson c
join lesson l on [Link]=[Link]
group by [Link]
order by too desc
limit 1;
