--SELECT * FROM EMPLOYEE

--SELECT * FROM EMPLOYEE ORDER BY 5 DESC , 4 ASC

--SELECT COUNT (CITY) AS COUNTOFCITY FROM EMPLOYEE GROUP BY CITY

--INSERT INTO EMPLOYEE VALUES

--(8,'ADIL','','MALE','KARACHI'),

--( NULL,'AQEEL','AHMED','MALE','KARACHI')

--SELECT* FROM EMPLOYEE

--INSERT INTO EMPLOYEEDESIGNATION VALUES

--( NULL,2000,'PEON'),

--( 11, 1800,'DIRECTOR'),

--( 12, NULL,'PRODUCER')

--SELECT * FROM EMPLOYEEDESIGNATION

--SELECT * FROM EMPLOYEE

--SELECT * FROM EMPLOYEE LEFT OUTER JOIN EMPLOYEEDESIGNATION ON EMPLOYEE.EMPLOYEEID=EMPLOYEEDESIGNATION.EMPLOYEEID

--SELECT POSITION, SALARY,FIRSTNAME FROM EMPLOYEE FULL JOIN EMPLOYEEDESIGNATION

--ON EMPLOYEE.EMPLOYEEID = EMPLOYEEDESIGNATION.EMPLOYEEID

--SELECT FIRSTNAME , MAX(SALARY)FROM EMPLOYEE INNER JOIN EMPLOYEEDESIGNATION

--ON EMPLOYEE.EMPLOYEEID=EMPLOYEEDESIGNATION.EMPLOYEEID

--WHERE FIRSTNAME<>'BILAL' GROUP BY SALARY ERROR

--SELECT EMPLOYEE.EMPLOYEEID,FIRSTNAME,LASTNAME,SALARY,POSITION

--FROM EMPLOYEE FULL OUTER JOIN EMPLOYEEDESIGNATION

--ON EMPLOYEE.EMPLOYEEID=EMPLOYEEDESIGNATION.EMPLOYEEID WHERE FIRSTNAME<>'BILAL'

--ORDER BY SALARY DESC

--SELECT AVG(SALARY) AS AVERAGE , POSITION FROM EMPLOYEE FULL OUTER JOIN EMPLOYEEDESIGNATION

--ON EMPLOYEE.EMPLOYEEID = EMPLOYEEDESIGNATION.EMPLOYEEID

--WHERE POSITION='MANAGER'

--GROUP BY POSITION

CREATE TABLE WAREHOUSEINFO (FIRSTNAME VARCHAR(50),LASTNAME VARCHAR(50),AGE INT )

CREATE TABLE INFOEMP (FIRSTNAME VARCHAR(50),LASTNAME VARCHAR(50),AGE INT )

--INSERT INTO WAREHOUSEINFO VALUES

--('MEHAK','KAMRAN',30),

--('BALOGA','NALLI',90),

--('OREO','MOTA',20),

--('SMOKY','KHAJOOI',50),

--('JIMIN','TERHA',27)

--INSERT INTO INFOEMP VALUES

--('AYESHA','KAMRAN',50),

--('TEENAGA','HEROSHEMA',80),

--('KKOKIE','MOTA',40),

--('BATAKH','KHOTI',25),

--('TAKLA','HARHA',37)

--SELECT * FROM WAREHOUSEINFO

--UNION ALL

--SELECT * FROM INFOEMP

--ORDER BY FIRSTNAME ASC

--INSERT INTO INFOEMP VALUES

--('AYESHA','KAMRAN',50),

--('TEENAGA','HEROSHEMA',80)

--SELECT FIRSTNAME FROM INFOEMP

--UNION

--SELECT FIRSTNAME FROM EMPLOYEE

--UNION

--SELECT FIRSTNAME FROM WAREHOUSEINFO

--ORDER BY FIRSTNAME ASC

--SELECT FIRSTNAME , LASTNAME ,

--CASE

-- WHEN LASTNAME like '%a%' then 'has a'

-- else 'no a'

--end AS HAVE

--FROM EMPLOYEE

--WHERE LASTNAME IS NOT NULL

SELECT * FROM WAREHOUSEINFO

UNION all

SELECT * FROM INFOEMP

SELECT *,

CASE

WHEN AGE>20 THEN 'YOUNG'

ELSE 'OLD'

END AS YOUNGOROLD

FROM WAREHOUSEINFO

SELECT AVG(AGE) AS AVERAGE FROM WAREHOUSEINFO

GROUP BY AGE

HAVING AVG(AGE) < 100