Answer_Tor_Tutorial_1

/*Q-1*/
SELECT*
FROM course;


/*Q-2*/
SELECT Course_No 
FROM course;


/*Q-3*/
select Student_No,Student_Name,Course_No
from student;


/*Q-4*/
SELECT DISTINCT Course_No 
FROM student;


/*Q-5*/
SELECT Student_No,Marks,Marks*1.1 AS ScaledMarks 
FROM result;


/*Q-6*/
SELECT Course_No,Student_Name 
FROM student 
ORDER BY Course_No DESC,Student_Name ASC;


/*Q-7*/
SELECT Student_No,Marks,Marks*1.1 AS ScaledMarks 
FROM result
ORDER BY Student_No,ScaledMarks DESC;


/*Q-8*/
SELECT Student_Name 
FROM student 
WHERE Year_Study=1;


/*Q-9*/
SELECT  Student_No,Student_Name 
FROM student
WHERE Year_Study = 1 AND Date_of_birth="1988/01/17";


/*Q-10*/
SELECT Student_No,Student_Name 
FROM student 
WHERE (Course_No = "C1" or Course_No = "C2")
AND  (Year_study = 1 or Year_study = 2);


--write another methode--


SELECT Student_No,Student_Name 
FROM student
WHERE Course_No IN ("C1","C2")
AND Year_study IN(1 ,2);


/*Q-11*/
SELECT Head
FROM department 
WHERE Head 
LIKE "Dr.L.%" OR head LIKE "Prof.L.%";


/*Q-12*/
SELECT Student_Name 
FROM student
WHERE Student_Name
 LIKE "%.Dias" ;

/*Q-13*/
SELECT Student_Name
FROM student
WHERE Student_Name 
LIKE "%._i%" ;


/*Q-14*/
SELECT Student_No,Student_Name 
FROM student
WHERE RIGHT(Student_No,1)%2 <>0;


/*Q-16*/
SELECT Student_Name FROM student
WHERE Address IS  NULL;


/*Q-17*/
SELECT Student_Name 
FROM student
WHERE Address="Colombo" OR Address="Galle" OR Address="Kandy";


/*Q-18*/
SELECT c.Course_No,s.*
FROM subject s,consists c
WHERE s.Subject_No = c.Subject_No;


/*Q-19*/
SELECT s.Student_No,s.Student_Name 
FROM student AS s,course AS c
WHERE (s.Year_Study > c.Duration) AND (s.Course_No = c.Course_No);


/*Q-20*/
SELECT Student_Name FROM student
WHERE  Year_Study<=3;


/*Q-21*/
SELECT s.Subject_Name,d.Head
FROM subject as s, department as d
WHERE s.Dept_Name=d.Dept_Name;


/*Q-23*/
SELECT COUNT(Student_No) AS Number_of_Student 
FROM student;


/*Q-24*/
SELECT COUNT(DISTINCT Course_No) AS Number_of_Course
FROM student;


/*Q-25*/
SELECT AVG(Marks) 
FROM Result
WHERE Student_No = "ST103" AND Year_Exam= 1;


/*Q-26*/
SELECT Student_No,avg(Marks) FROM result
GROUP BY Student_No,Year_Exam;


/*Q-27*/
SELECT Year_Study,COUNT(Student_No) AS Number_of_Student
FROM Student
WHERE YEAR(Date_of_birth)="1988"
GROUP BY Year_Study;


/*Q-28*/
SELECT Student_No,Year_Exam,Subject_No FROM result
GROUP BY Student_No,Year_Exam HAVING avg(Marks)<40;


/*Q-29*/
SELECT Address,COUNT(Student_No) AS Number_Of_Student 
FROM student 
WHERE Address 
IN('Galle','Kandy','Colombo')
GROUP BY Address  ;


/*Q-30*/
SELECT s.Student_Name,c.Subject_No
FROM student AS s,consists AS c
WHERE c.Course_No=s.Course_No AND c.Subject_No='SU04';


/*Q-31*/
SELECT Student_Name,Course_No
FROM student 
WHERE Course_No<>'C2';


/*Q-33*/
SELECT s.Student_Name,c.Subject_No
FROM student AS s,consists AS c
WHERE c.Course_No=s.Course_No AND (c.Subject_No='SU01' OR c.Subject_No='SU02')
GROUP BY s.Student_Name,c.Subject_No;


/*Q-37*/
SELECT Student_No,Subject_No,Year_Exam
FROM result
WHERE Marks
BETWEEN 50 AND 60;


/*Q-38*/
SELECT Subject_No FROM subject WHERE Dept_Name='Chemistry'
UNION 
SELECT Subject_No FROM consists WHERE Course_No='C2';


/*Q-39*/
UPDATE  Student 
SET Address='Kalutura' 
WHERE Student_no='ST103';


/*Q-40*/
UPDATE result 
SET marks=marks*1.1;


/*Q-41*/
UPDATE result 
SET marks=marks*1.1
WHERE marks BETWEEN 0 AND 25;


/*Q-43*/
UPDATE result 
SET marks=marks*1.1
WHERE Subject_No='SU01' OR Subject_No='SU02';


/*Q-45*/
INSERT INTO student (Student_No,Student_Name,Course_No) 
VALUES('ST109','S.Kethees','C3');


/*Q-48*/
DELETE FROM result
WHERE Year_Exam=2 AND Subject_No='SU03' AND Student_No='S1003';


/*Q-49*/
DELETE FROM result
WHERE Year_Exam=1 ;


/*Q-50*/
DELETE FROM department;


/*Q-51*/
DELETE FROM student
WHERE Year_Study=4;