SQLQuery5.sql

--REVISION 

--CREATE TABLE STUDENT
--(
--STUNAME VARCHAR (50) ,
--ADRESS VARCHAR (30),
--CLASS INT,
--BACKGROUND VARCHAR (50)

--)

--CREATE TABLE TEACHER
--(
--NAME VARCHAR (50) ,
--SUBJECT VARCHAR (30),
--CLASS INT,
--SALARY VARCHAR (50)

--)


--INSERT INTO STUDENT  VALUES 
--('AYESHA', 'KARACHI', 2 , 'MEDICAL'),
--('MEHAK', 'KARACHI', 1, 'COMPUTER'),
--('SAMAN', 'AUSTRALIA', 10, 'COMMERCE'),
--('BILAL', 'CANADA', 7 , 'ENGINEER'),
--('BALOGA', 'FIVE STAR', 0 , 'CATNIP')

--INSERT INTO TEACHER VALUES
--('FARHANA', 'SCIENCE', 5 , '5000'),
--('TELGU', 'MATHS', 2 , '5000'),
--('TUNTUN', 'COMPUTER', 10 , '30000'),
--('AMID', 'ECNOMICS', 1 , '6000')


--SELECT * FROM STUDENT 
--SELECT * FROM TEACHER

--SELECT STUNAME AS NAME FROM STUDENT

--SELECT TOP 2 * FROM TEACHER 
--SELECT DISTINCT SALARY FROM TEACHER

--select count (stuname) from student 

--select min(salary) from teacher

--select avg(class) from teacher
select * from teacher


--select name   from teacher 
--where salary <> 5000 OR class = 1

--SELECT NAME FROM TEACHER 
--WHERE NAME LIKE 'T%N'

--SELECT NAME FROM TEACHER
--WHERE SALARY IS  NULL

--SELECT SALARY FROM TEACHER 
--WHERE NAME ='FARHANA' OR NAME='TUNTUN'
--SELECT SALARY FROM TEACHER 
--WHERE NAME IN ('FARHANA','TUNTUN')

--SELECT * FROM EMPLOYEE

--SELECT FIRSTNAME AS NAME FROM EMPLOYEE
--WHERE FIRSTNAME LIKE '%A%'
--ORDER BY NAME DESC

--SELECT COUNT(SALARY) AS RATIO, SALARY  FROM TEACHER
--GROUP BY SALARY

--SELECT * FROM TEACHER 
--SELECT * FROM STUDENT

--SELECT * FROM TEACHER AS TECH FULL JOIN  STUDENT ON TECH.CLASS=STUDENT.CLASS 

--------------------------UNION WILL BE COLOUMN  WISE WHILE JOIN WILL BE ALL TOGETHER ROW WISE 
--SELECT * FROM TEACHER
--UNION 
--SELECT * FROM STUDENT 

--------------------CASE
--select *,
--CASE WHEN NAME LIKE 'T%' THEN 'OKAY'
--     ELSE 'NOT OKAY'
--END AS WELLBEING
--FROM TEACHER

----------------HAVING AS WHERE CAN NOT DO MATHS 
--SELECT AVG(CLASS)  AS AVERAGE FROM TEACHER
--GROUP BY CLASS 
--HAVING AVG(CLASS)<10 
--ORDER BY CLASS DESC
------WHERE AVG(CLASS)<10

SELECT * FROM TEACHER
SELECT * FROM STUDENT 

----COUNTING NO OF STUDENT AND TEACHER 
SELECT COUNT(STUNAME) AS NOOFSTUDENT FROM STUDENT 
SELECT COUNT(NAME) AS NOOFTEACHER FROM TEACHER


SELECT * FROM STUDENT FULL JOIN TEACHER ON STUDENT.CLASS=TEACHER.CLASS 
UPDATE STUDENT 
SET ADRESS='LAHORE'
WHERE STUNAME='MEHAK' AND CLASS=1 

SELECT * FROM TEACHER

DELETE FROM TEACHER
WHERE NAME='AMID'