SQL DAY 3 08-09-2021.sql

select * from employees
order by salary;

select * from employees
order by salary desc;

select * from employees
order by salary  desc,first_name asc;

select * from employees
order by first_name asc,salary desc;

#AGGREGATE FUNCTION:-

/*   WAQ TO DISPLAY THE AVG SALARY OF THE EMPLOYEES IN THE COMPANY*/
SELECT avg(SALARY)
FROM employees;

/*
5.WAQ TO DISPLAY THE TOTAL NUMBER OF EMPLOYESS WORKING IN THE COMPANY?
*/
SELECT COUNT(EMPLOYEE_ID)
FROM employees;

SELECT count(*) FROM employees;

SELECT COUNT(COMMISSION_PCT)
FROM EMPLOYEES;

#6. WAQ TO DISPLAY THE MIN SALARY DRAWN,MAXIMUM SALARY DRAWN AND AVERAGE SALARY DRAWN IN THE COMPANY.
select min(SALARY) AS MINIMUM_SALARY,max(SALARY) AS MAXIMUM_SALARY,AVG(SALARY) AS AVERAGE_SALARY
FROM EMPLOYEES;

#7. WAQ TO DISPLAY THE TOTAL NUMBER OF EMPLOYEES WORKING IN EACH DEPARTMENT IN THE COMPANY
SELECT department_id,COUNT(employee_id)
FROM employees
group by department_id;

SELECT count(distinct department_id)
FROM EMPLOYEES;

SELECT department_id,employee_id
FROM employees;

SELECT MAX(SALARY) FROM employees
WHERE department_id=10;

/*
#8.WAQ TO DISPLAY THE TOTAL NUMBER OF EMPLOYEES WORKING IN EACH DEPARTMENT UNDER EACH JOB_ID IN THE COMPANY
*/
select department_id,job_id,count(employee_id)
FROM EMPLOYEES 
group by department_id,job_id;

#9.WAQ TO DISPLAY THE TOTAL AMOUNT OF MONEY TO BE BLOCKED AS BUDGET FOR PROVIDING SALARY TO ALL THE EMPLOYEES IN EVERY DEPARTMENT.
DESC employees;
SELECT department_id,sum(SALARY) AS BUDGET
FROM employees
group by department_id;

/*STAEMENT SKELETON
SELECT...4TH EXECUTION
FROM...1ST EXECUTION FIRST
WHERE...2ND EXECUTION
GROUP BY...3RD EXECUTION
ORDER BY...5TH EXECUTION
LIMIT...6TH EXECUTION

#FROM-WHERE-GROUP BY-SELECT-ORDER BY-LIMIT
*/
#FILTERING:-WHERE-NORMAL/HAVING-AGGREGATE
#Q10.WAQ TO DISPLAY THE TOTAL NUMBER OF EMPLOYEES WORKING IN EACH DEPARTMENT IN THE COMPNAY.DISPLAY THE DEPARTMENT WHERE THERE ARE MORE THAN 10 EMPLOYESS WORKING.
/*SOLUTION*/
SELECT DEPARTMENT_ID,count(employee_id)
FROM employees
group by department_id;# BUT ANSWER HAVE NOT ARRIVED

#WHERE-FILTERING ONLY NORMAL VALUE,BUT FOR AGGREGATE 'WHERE' CANNOT BE USED SO WE USE 'HAVING'
/* NOW WE USE HAVING*/
SELECT department_id,COUNT(employee_id)
FROM employees
group by department_id
HAVING COUNT(employee_id)>10;

SELECT department_id,COUNT(employee_id)
FROM employees
group by department_id
HAVING COUNT(employee_id)>10 AND SUM(SALARY)>200000;

/*
FOREIGN KEY

-COLUMN1 TABLE WHICH IS REFERENCING THE PRIMARY KEY OF THE OTHER TABLE
-IT HELPS US IDENTIFY THE RELATIONSHIP B/W 2 TABLES/ENTITES
-THE TABLE WITH FOREIGN KEY IS CALLED CHILD TABLE.
-THE TABLE WHOSE PRIMARY KEY WE ARE REFERENCING IS CALLED PARENT TABLE.
FOREIGN KEY WILL ALLOW THE VALUES WHICH ARE PRESENT IN THE PRIMARY KEY OF THE OTHER TABLE AND NULL

CREATE TABLE TABLE_NAME 
(
COLNAME1 DATATYPE CONSTRAINTNAME,
COLNAME2 DATATYPE CONSTRAINTNAME,
COLNAME3 DATATYPE CONSTRAINTNAME,
,
,
FOREIGN KEY(COLNAME) REFERENCES PARENT_TABLE_NAME(PRIMARY KEY)
);


ALTER TABLE TABLE_NAME ADD FOREIGN KEY(COLNAME) REFERENCES PARENT_TABLE_NAME(PK_COL_NAME);


*/

/*
#Q11.WAQ 
CREATE A COURSE TABLE AS CID PK,CNAME,DESCRIPTION
STUDENT_DETAILS-SID PK,SNAME,PHNO,EMAILID,COURSE_ID,ADDRESS
#NOTE:COURSE ID FROM THE STUDENT_DETAILS TABLE SHOULD ACTAS FOREIGN KEY AND LINK TO THE COURSE TABLE.
*/
USE chn_jul21;
CREATE table COURSE
( 
cid int,
cname varchar(20),
description varchar(50),
PRIMARY KEY (cid)
);
create table studentdetails
(
sid int primary key ,
sname varchar(50),
phno varchar(20),
emailid varchar(50),
address varchar(50),
cid int,
foreign key(cid) REFERENCES course(cid)
);
describe studentdetails;
describe COURSE;

select e.employee_id,e.first_name,e.last_name,e.department_id,d.department_name
from employees e
join departments d
on e.department_id=d.department_id;